Mysql数据库（1）—索引

索引是什么？

索引是帮助MySQL高效获取数据的排好序的数据结构。常见的索引数据结构包括：

1. 二叉树
2. 红黑树
3. Hash表
4. B-Tree

mysql索引分类

• 按逻辑结构分类：B+ tree索引、Hash索引、Full-text索引。
• 按物理存储分类：

（1）聚簇索引（主键索引）

​ 聚簇索引是一棵B+树，这棵B+数的叶子节点中保存了所有的记录数据，在innoDB中，一张表只能有一个聚簇索引，这就是主键索引。

（2）二级索引

​ 二级索引也是一颗B+树，但是这棵B+树的叶子节点中保存的是索引项和主键值，不会保存记录的实际数据。

• 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
• 按字段个数分类：单列索引、联合索引。

联合索引也是一颗B+树

mysql索引B+树的原理

B/B+树是一种典型的外存排序方式：

1. B+树是B树的一种进化，B树是一种多叉平衡排序二叉树，引入多叉，就可以减少对磁盘IO的次数，再简单讲述下B树的结构，每个结点中都有关键词、关键词所指向的指针，还有指向子结点的指针。针对每次查询操作，因为他是一颗排序树，所以他能够以最短的路径找到需要查询的关键词，找到对应的数据。
2. 现在再来讲B+树就容易理解了，他是为了解决IO次数还是太多，就是说还需要减少B树的层次，外加需要减轻中间结点的磁盘容量，所以B+树和B树最大的区别是他将所有真正指向文件的指针保存在叶子结点中，而非叶子结点他只是保存指向文件的指针，这样就能减轻占用磁盘大小，同时增加每个结点中关键字的个数，保证每个结点中关键词的个数和子树一致，这样就能减少树的层次。同时B+树的叶子节点前后也是通过指针联系起来的。

B树和B+树区别：

1. B树中间节点也保存数据，B+树数据保存在叶子节点，使得B+树有数据冗余，即节点中的数可能多次出现。
2. B+树的叶子节点前后形成链路，这样便于范围查找。
3. B+树的节点中保存的数据个数和出度一致，B树保存的数据比出度少一，使得B+节点中能保存更多的数据，\降低了树的高度**，减少了磁盘IO的次数。

和AVL、B树、Hash索引相比，选择B+树结构作为索引的原因？

1. B+树作为平衡树，其查询的时间效率为O(logN)，由于叶子节点中还有数据，相较于B树，可以减少再次进行磁盘IO的次数；
2. 和平衡二叉树AVL相比，AVL是二叉树，所以树的高度较高，进行磁盘IO的次数比B+树更多，而数据库查询的主要瓶颈就是在磁盘IO上；
3. 和B树相比，由于B+树的叶子节点直接指向节点数据，可以快速进行数据访问，同时叶子节点间是有序的，能够进行范围查找；
4. 和哈希表的索引结构相比，B+树的叶子节点是有序排列的，可以进行范围查找，这个在哈希表等结构中无法实现，需要做全表扫描，时间开销非常大；

Mysql聚簇索引和非聚簇索引

聚簇索引是索引和数据在一起的，一个表中只有一个聚簇索引，就是主键索引。非聚簇索引则索引和数据分开保存。默认的innoDB支持聚簇索引，MYISAM不支持聚簇索引。聚簇索引和非聚簇索引都是B+树的一种实现。

1. 聚簇索引：聚簇索引是innoDB的索引方式，是主键索引的实现方式，索引和记录都保存在叶子节点中。如果没有主键索引，就用第一个非空唯一索引替代，如果还是没有就用表的隐含主键替代。
2. 非聚簇索引：非聚簇索引在innoDB中和MYISAM存储引擎中实现的逻辑稍有不同。在innoDB中非聚簇索引中保存的是索引项和主键值，找到索引项后再通过主键值回表到聚簇索引来找到真正的数据；在innoDB中非聚簇索引保存的是索引项和其指向的真正数据，可以理解为MyISAM存储引擎中索引文件和数据文件是分离的。

MySQL索引的最左前缀原则

就是在建立组合索引的时候，最频繁、区分度最高的字段放在最左侧；建立联合索引的B+树也是按照多字段的先后顺序新建的，如果where条件最左不是联合索引最左字段，则不会走到联合索引中去；

总结：联合索引的最左匹配原则，在遇到范围查询（如>、<）的时候，就会停止匹配，也就是范围查询前的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

mysql回表机制

mysql回表就是根据一个非主键的字段进行查询，第一次查询到的结果只是查询字段的索引和主键字段，需要再次根据主键查主键索引，这种过程成为回表。

mysql覆盖索引机制

将需要查询的字段和条件字段建立组合索引，这样根据查询条件在组合索引中就能查到响应字段，就不需要回表了。

mysql索引下推机制

针对条件语句中有多个条件的情况，在没有索引下推的情况下，会先根据最左索引查询处理的记录进行回表操作，再根据主键去继续满足下一个条件的记录，存在多次回表操作。

而有索引下推的情况是根据最左索引查询到了记录，会继续根据二级索引查询是否满足下一个条件，如果满足则返回，不满足则继续查询。

举例说明：就有点像查询成语字典，索引下推是根据首字查询到首汉字开头的成语，再在字典正文中继续查找，就不需要再回头看目录了。

mysql的主键一定是自增的吗？

mysql的主键不一定是自增的。主键的形式可以自己选择，但一般会选择自增字段作为主键，原因如下：

1. 自增字段长度小，节省空间。
2. 自增字段都是在索引末尾进行插入，减少了从中间插入导致的页分裂等情况。
3. 自增字段保障了主键唯一性，不存在主键冲突的可能。

mysql一定要设置主键吗？不设置主键会怎么样？

mysql不是一定要设置主键，如果使用innodb的存储引擎，表的主键索引是聚簇索引，如果没有设置主键，则会用第一个非空的唯一索引做主键索引，如果不存在非空索引，则会自动生成一个隐含row_id做主键索引。

数据库索引的优势和劣势？

优势：

1. 通过索引查找，可以减少全表扫描，减少IO次数，提升查询效率。
2. 索引可以进行范围查找，可以进行排序和分组等操作。

劣势：

1. 创建过多索引会占用过度磁盘空间；
2. 创建过多的索引，在进行成本计算的时候也会消耗性能；

索引怎么建？如何优化？

1. 在区分度高的字段上建立索引。

2. 建立组合索引的时候考虑是否会形成索引覆盖，这样会减少回表的次数。

3. 建立组合索引时，遵循最左匹配原则。将查询频率比较高，区分度比较大的字段放在最左侧。

4. 为作为查询条件、进行排序、进行分组操作的字段建立索引，比如where、group by、order by字段建立索引。

5. 索引字段作为条件查询时，不要进行函数计算，这样不会命中索引。

   索引失效的情况：

6. 查询条件中有计算和函数处理。

7. 查询条件的字段非联合索引最左字段。

   本文由博客一文多发平台 OpenWrite 发布！