MySQL索引

四、索引

索引概述、索引结构、索引分类、索引语法、SQL性能分析、索引使用、索引设计原则

1. 索引概述

1. 索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

2. 优势：提高数据检索的效率，降低数据库IO成本，通过索引排序，降低数据的排序成本，降低cpu的消耗

   缺点：索引也是占数据空间的，索引大大提高了查询效率但同时降低了更新表的速度，insert,update,delete

2.索引结构

索引是在存储引擎层实现的，不同的存储引擎有不同的结构

1. B+Tree索引，最常见的索引类型，大部分引擎都支持B+树索引

2. Hash索引，底层数据结构是用哈希表实现的，只有精确匹配索引列的查询有效，不支持范围查询，

3. R-tree空间索引，空间索引是MySAM引擎的特殊引用类型，主要用于地理空间数据类型

4. Full-text全文索引，是一种通过建立倒排索引，快速匹配文档格式。类似于Luncene，Solr，es

基本所有的存储引擎都支持b+树

1. B-Tree(多路平衡查找树)，每个节点可以有多个子节点，指针个数比 节点个数多1.

   达到节点个数中间节点会向上分裂，（5阶每个节点最多存储4个key）

2. B+Tree，每个节点都在叶子节点中，叶子节点之间形成一个单向链表，上面非叶子节点只是起到索引作用

   在MySQL中索引结构对B+Tree进行了优化，增加一个指向相邻叶子节点的链表指针，提高区间访问性能

   键值，数据，指针，页/块（16k）

   

3. Hash索引结构和HashMap底层原理一样，只能用于查询，一次检索就可以了，效率高于B+Tree，支持Hash索引的是Memory，而InnoDB具有自适应哈市功能。

4. 为什么InnoDB存储引擎选择使用B+Tree索引结构？

   1. 二叉树顺序插入会形成链表结构，红黑树虽然有所优化，但本质还是二叉树，

   2. 相对于二叉树，在一定数据的情况下，B+Tree层级更少，搜索效率高，

   3. 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，同样保存大量的数据，只能增加树的高度，导致性能降低，

   4. 相对Hash索引，B+Tree支持范围匹配及排序操作，hash只能用于单次查询。

3.索引分类

1. 主键索引：针对于表中主键创建的索引，默认自动创建，只能有一个，primary

2. 唯一索引：避免同一列中某个数据列中的重复值，可以有多个，unique

3. 常规索引：快速定位特定数据，可以有多个

4. 全文索引：全文索引是查找文本中的关键字，而不是索引中的值，可以有多个，fulltext

1. 聚集索引：将数据与索引放到了一块，索引结构的叶子节点保存了行数据，（比如id对应的行数据都存在在聚集索引叶子节点上，可以根据id查到一行的数据）必须有，而且只有一个

2. 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，（name字段作为二级索引，其叶子节点存储是每个字段对应的id，然后再根据id查询）可以存在多个，

   聚集索引选用规则：

   如果存在主键，主键索引就是聚集索引。

   如果不存在主键，将使用第一个唯一索引（unique）作为聚集索引

   如果没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

   InnoDB主键索引的B+Tree高度为多高呢？

   假设：一行数据的大小是1k，一页中可以存16行这样的数据。InnoDB的指针占用6个字节空间，主键即使为bigint，占用字节数为8。

   高度为2：根节点，n * 8 + ( n + 1) * 6 = 16 * 1024 ,n约等1170，数据个数：1171 * 16 = 18736

   高度为3：1171 * 1171 * 16 = 21939856