【总结】MySQL索引总结

该文章为知识总结的文章，如果是初学者，建议先从专栏学习：数据库专栏

一、为什幺要用索引？

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
3. 帮助服务器避免排序和临时表。
4. 将随机IO变为顺序IO
5. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

二、索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

三、使用索引的注意事项？

• 在经常需要搜索的列上，可以加快搜索的速度；

• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

• 在使用InnoDB时使用与业务无关的自增主键作为主键

• 避免索引失效

四、索引什么时候会失效？

• 有or关键字必须所有字段全有索引;

• 复合索引未用左列字段;

• like以%开头

• where中索引列有运算

• where中索引列使用了函数

• 如果mysql觉得全表扫描更快时（数据少）

五、最左前缀原则是什么？

主要针对的聚合索引是否生效，假如有一个聚合索引ABC：

• 最左边的列必须要用到，比如这个A列
• 中间是不能断的，如只查询了A和C列，只会用到C的索引
• 遇到范围后，终止，比如where条件是这样的，where A = x and B >y and C = z，这个时候只会用到AB索引，无法使用C索引

六、MySQL索引的主要数据结构

1. 哈希索引

​ 对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引

• 因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询
• 当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的
• 哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

2. B+树索引

• 为磁盘或其他直接存取的辅助设备而设计的平衡二叉树

• 所有的记录节点都是按照键值的大小顺序存放在同一层的叶节点

• 同时最后一层叶字节点之间存在指针，这样可以保证其是连续的数据排列

​

B+树相对于B树的区别？

• 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了
• 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
• 所有的叶子节点形成了一个有序链表，更加便于查找。

B+树与红黑树的比较？

• 更少的查找次数：平衡树查找操作的时间复杂度和树高 h 相关，O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多，查找的次数也就更多。
• 利用磁盘预读特性：为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入

B+数树和B-树的区别？

• B+树相当于B-树的变种，主要差异在于B+树数据都保存在叶子节点，同时叶子节点之间形成了链表

• B+树查询时间复杂度固定是logn，B-树查询复杂度最好是 O(1)

• B+树相邻接点的指针可以大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找

MyISAM和InnoDB实现BTree索引方式的区别？

• MyISAM：B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

• InnoDB：其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。

七、为什么索引能提高查询速度？

MySQL底层每16k的数据为一页，一页中有多个记录值。

如果我们写select * from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页
2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

使用了索引其实就是将无序的数据变成相对有序，其实底层结构就是B+树，B+树作为树的一种实现，时间复杂度近似为O(logn)，能够让我们很快地查找出对应的记录。

八、索引都有哪些类型？

1. 主键索引(Primary Key)

• 数据表的主键列使用的就是主键索引。

• 一张数据表有只能有一个主键，并且主键不能为null，不能重复。

• 在mysql的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段，如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。

2. 二级索引(辅助索引)

二级索引又称为辅助索引，是因为二级索引的叶子节点在InnoDB中存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置；在MyISAM中存储的数据是地址。

1. 唯一索引(Unique Key) ：唯一索引也是一种约束。**唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表允许创建多个唯一索引。**建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
2. 普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和NULL。
3. 前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小， 因为只取前几个字符。
4. 全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6之前只有MYISAM引擎支持全文索引，5.6之后InnoDB也支持了全文索引。

3. 聚集索引

聚集索引即索引结构和数据一起存放的索引。InnoDB存储引擎中主键索引属于聚集索引，B+树的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

优点：

• 聚集索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。

缺点：

• 依赖于有序的数据 ：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。
• 更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，可能涉及自旋操作维护平衡， 而且况聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

4. 非聚集索引

非聚集索引即索引结构和数据分开存放的索引，比如说MyISAM中的索引保存的指向数据文件的指针。

优点：

• 更新代价比聚集索引要小：非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的

缺点：

• 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

非聚集索引一定回表查询吗(覆盖索引)?

• 不一定。
• 虽然MYISAM的主键索引确实需要回表， 因为它的主键索引的叶子节点存放的是指针。但是如果SQL查的就是主键呢?
• 或者想要查询的字段刚好建立了索引，查到对应的字段直接返回就行了，无需回表查询

5. 覆盖索引

• 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作
• 假如索引是AB列，想要查询出来的列是AB+主键列，则此时是覆盖索引；而想要查询出来的列是ABC列，C的数据不在索引中，就需要回表。所以切忌使用Select *

总结：覆盖索引的优化及限制

优点：

1、索引项通常比记录要小，所以MySQL访问更少的数据。

2、索引都按值得大小存储，相对于随机访问记录，需要更少的I/O。

3、数据引擎能更好的缓存索引，比如MyISAM只缓存索引。

4、覆盖索引对InnoDB尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引包含查询所需的数据，就不再需要在聚集索引中查找了。

限制：

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。

2、Hash和full-text索引不存储值，因此MySQL只能使用BTree。

3、不同的存储引擎实现覆盖索引都是不同的，并不是所有的存储引擎都支持覆盖索引。

4、如果要使用覆盖索引，一定要注意SELECT列表值取出需要的列，不可以SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

九、索引创建的原则

最左前缀原则：

• 虽然我目前的Mysql版本较高，好像不遵守最左前缀原则，索引也会生效。 但是我们仍应遵守最左前缀原则，以免版本更迭带来的麻烦。

选择合适的字段

1. 索引字段的数据应该尽量不为NULL，因为对于数据为NULL的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为NULL，建议使用默认值。
2. 我们创建索引的字段应该是查询操作非常频繁的字段，而不是增删改比较多的。
3. 被作为WHERE条件查询的字段，应该被考虑建立索引。
4. 经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

需要注意的地方

• 注意避免冗余索引
• 考虑在字符串类型的字段上使用前缀索引代替普通索引，前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。