Mysql索引原理及查询优化

Mysql查询变慢了，你可能最先想到的就是，索引加了没？

1. 基本概念

• 聚簇索引：具有一下两种特性的索引：

  • 使用记录主键值的大小进行记录和页的排序
    1. 页内的记录是按照主键的大小顺序排成一个单向链表
    2. 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
    3. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
  • 叶子节点存储的是完整的用户记录（完整指的是这个记录中存储了所有列的值（包括隐藏列））

  InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

• 二级索引：具有一下特点（例如使用列 column）

  • 使用记录column列的大小进行记录和页的排序
    • 页内的记录是按照column列的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中记录的column列大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的column列大小顺序排成一个双向链表。
  • B+树的叶子节点存储的并不是完整的用户记录，而只是column列+主键这两个列的值。(回表的理论关注这里)，如果需要查询column列和主键以外的列，这里就需要根据聚簇索引（主键）来返回查询的列
  • 目录项记录中不再是主键+页号的搭配，而变成了column列+页号的搭配。

• 联合索引：本质上也是一个二级索引，只是以多个列作为索引值

• 列的基数：列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2, 5, 8, 2, 5, 8, 2, 5, 8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。

2. 索引的优劣势

• 优势：
  • 加快查询速度
• 劣势
  • 空间上的代价：需要额外的存储页存储索引
  • 时间上的代价：对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引

3. 创建索引的优化

• 聚簇索引（主键）
  • 方案：主键能够做到自增，或者以递增的形式生成
  • 原因：主键不自增会造成记录在页的链表中间插入，造成页面分裂和记录移位，插入性能很差
• 二级索引：
  • 只为用于搜索、排序、分组的列创建索引
  • 最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好
  • 尽量让索引值占用的字节数小，在一页中可以存放更多的索引，减少磁盘I/O可以加快查询速度
  • 不要创建重复索引，以列column 作为第一位的联合索引和 只有一列column 作为索引的用处相同，不需要重复创建

4. 索引查询的优化

索引的查询优化主要是针对联合索引，联合索引比较特殊，比如以 a,b,c三列作为联合索引的列，页的记录先按照a排序，再按照b排序，再按照c排序，那么对于联合索引的查询，不需要根据列a查询、排序、分组的话，联合索引其实也没有很大的用户

1. 若需要通过索引查询，索引生效的条件是 存在 a,存在b，存在c，否则无效
2. 左前缀法则，如果使用like进行模糊查询，%一定不要放在最左边，否则索引失效
3. 使用联合索引，asc和desc不要混用，否则索引失效
4. 让索引在比较表达式中单独存在，比如 a /2 > 4 优化成a > 4 *2 ,否则索引失效

5. 其他概念

5.1 回表

回表的产生：二级索引中只存储部分数据，二级索引，聚簇索引，如果需要返回其他列，必然需要再通过聚簇索引去查询所需要返回的记录

回表为什么慢：通过二级索引查询到的聚簇索引不是有序的，这就造成再通过聚簇索引查询用户记录时不是顺序I/O而是随机I/O,随机I/O的速度非常慢。所以当返回的数据较多时，可能比全表扫描还慢（全表扫描是顺序I/O）

5.2 覆盖索引

为了彻底告别回表操作带来的性能损耗，我们建议：最好在查询列表里只包含索引列。也就是覆盖索引

这也是为什么鼓励不适用 *