MySQL索引总结

索引结构

mysql有三种索引结构，包括B+树、哈希索引和全文索引，这里只关注B+树

1. 为什么不用二叉树，例如红黑树等数据结构？
   因为二叉树父节点至多有两个子节点，所有层级很深。层级越深，那么读取数据时的io次数就越多。TreeMap使用红黑树，因为是内存操作，io会非常高效，而MySQL数据存储到磁盘上，io次数越多，那么检索性能就会越低。

2. B树的作用
   B树的出现，就是为了解决二叉树频繁io的问题，B树属于多路查找树，相对于二叉树层级更少，那么io次数自然就更少。

3. B+树
   B树解决了频繁io的问题，但是对于范围查询很吃力，所有B+树在继承了B树矮胖的优点的同时，让叶子结点持有数据，而非叶子节点只持有关键字，同时叶子节点数据形成一个有序链表，天然支持范围查询。由于每次数据查找都要到叶子节点才能找到数据，那么B+树的查询就更稳定。

聚集索引与非聚集索引

聚集索引

• 只有InnoDB支持聚集索引，聚集索引就是一个叶子结点对应的就是表的一行数据。可见，聚集索引里包含了表的所有数据，所以聚集索引顺序也决定了表数据的物理顺序；
• 可见InnoDB必须有主键，否则无法创建聚集索引，无法管理数据；
• 自增主键的好处：我们通常都用自增主键，应为自增主键可以再B+树中顺序添加，新增数据无需对B+树进行旋转之类的操作，可以保证数据插入的效率；

非聚集索引

• InnoDB除了主键索引，其他都是非聚集索引，非聚集索引叶子结点上存放的是行数据的主键，可见查询的时候需要两次查询。首先查询非聚集索引，拿到数据主键，然后用主键在聚集索引中查询拿到行数据。这里就会涉及到回表和索引覆盖的概念，下面再阐述。
• MyIsam只有非聚集索引，数据会单独存放，索引的叶子节点存放的是数据的物理地址，可通过物理地址直接拿到数据，不存在二次查询。

回表与索引覆盖

• 回表：针对于InnoDB，首先查询非聚集索引，拿到数据主键，然后用主键在聚集索引中查询拿到行数据。这就是回表；
• 索引覆盖： 索引覆盖可以避免回表。也就是说在索引树中已经拿到了所需数据的全部，就不用再去聚集索引中查询数据。例如：联合索引包括name，pid。我们查询的时候select name pid from table，就不用再回表。而使用select * from table就会回表从而降低查询性能。可见在写SQL的时候要养成好习惯，只查询需要的字段。

索引的好处与坏处

好处

• 提高检索性能
• InnoDB行级锁就是锁住索引，对于未创建索引的字段，无法使用行级锁，升级为表锁

坏处

• 降低增改删的性能，因为要重建索引

索引创建的注意事项

• where，join等关键字后面的字段可以创建索引，order by会使用主键索引，其他无效；
• 查询非常频繁的字段；
• 更新频率大于查询频率的字段不应该创建索引；
• 数据值很少或者重复数据很多的字段不应该创建索引；

索引生效与失效

生效

• =或范围查询
• 联合索引满足最左匹配原则
• like ‘a%’

失效

• like ‘%a’
• 联合索引中使用范围查询><，会导致范围查询后面的字段索引失效
• 对索引字段进行运算
• or、NOT、!=、<>、NOT IN、NOT LIKE可能导致索引失效

优化器

• mysql的查询优化器会对SQL进行一定的优化，综合考虑是否使用索引，如果全表扫描的性能优于索引，则不会使用索引。具体需要深入研究。

最左匹配原则

• a,b,c三个字段创建联合索引，等于拥有了a,ab,abc三个索引，除此之外的组合，索引都不会生效；
• ab和abc的顺序可以随便写，因为优化器会进行优化；
• 范围查询会导致其后的索引失效。