索引优化

索引基础

类型

B-TREE

InnoDB实际上用的是B+TREE

B-TREE和B+TREE的区别

1. B+TREE中只有叶子节点会带有指向记录的指针（ROWID），而B树则所有节点都带有，在内部节点出现的索引项不会再出现在叶子节点中。

2. B+树中所有叶子节点都是通过指针连接在一起，而B树不会。

B+TREE的优点

1. 非叶子节点不会带上ROWID，这样，一个块中可以容纳更多的索引项，一是可以降低树的高度。二是一个内部节点可以定位更多的叶子节点。

2. 叶子节点之间通过指针来连接，范围扫描将十分简单，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。

B-TREE的优点

对于在内部节点的数据，可直接得到，不必根据叶子节点来定位。

使用场景

由于BTREE是按顺序存储，所以很适合查询范围数据，B-TREE索引适用于全键值，键值范围或键前缀查找（只适用于根据最左前缀查找）
- 全值匹配，和索引中的所有列进行匹配
- 匹配最左前缀，只使用索引第一列
- 匹配列前缀，可以只匹配某一列的值的开头部分（只使用索引第一列）
- 匹配范围值，只使用索引的第一列
- 精确匹配某一列并范围匹配另一列，左列精确匹配，到范围匹配列停止
- 只访问索引的查询，查询只需要访问索引，不需要访问数据行

因为B-TREE中的节点是有序的，所以索引还可以用于ORDER BY操作

限制

• 如果不按索引的最左列开始查找，则无法使用索引。
• 不能跳过索引中的列
• 如果查询中有范围查询，则其右边所有列都无法使用索引

哈希索引

• 只有Memory引擎显示支持哈希索引
• 不是按值顺序存储，无法用于排序
• 不支持部分匹配查询，因为是使用索引列的全部内部计算哈希值的
• 只支持等值比较查询，不支持任何范围查询

其它索引

空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储

全文索引

一种特殊类型的索引，它查找的是文本中的关键字

索引的优点

• 减少了服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 将随机I/O变为顺序I/O

高性能索引策略

独立的列

• 索引不能是表式的一总价，也不能是函数的参数
• 简化WHERE条件，始终将索引列单独放在比较符号的一侧

前缀索引和索引选择性

• 前缀索引应该足够长，以使前缀索引的选择性接近于索引的整个列
• 前缀索引能使索引变得更小，更快，但MySQL无法使用前缀索引做ORDER BY和GROUP BY操作，也无法使用前缀索引做覆盖扫描

索引的选择性是指，不重复的索引值（cardinality）和数据表总数（#T）的比值，范围从I/#T到1之间，选择性越高，查询效率越高，唯一索引的选择性为1

多列索引

在每个列独立的建立索引，并在WHERE中使用了这些列的情况下，MySQL会使用一种叫“索引合并”的策略，查询是能够同时使用这两个单列索引进行扫描，并将结果合并。MySQL会使用这类技术优化复杂查询

索引合并策略有时候是一种优化的结果，但实际上更多时间说明了表上的索引建得糟糕
- 当服务器对多个索引做相关操作时（多个AND条件），通常说明需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
- 当服务器需要对多个索引做联合时（多个OR条件），通常需要耗费大量的CPU和内存资源在算法的胡村、排序和合并操作上
- 如果在EXPLAIN中看到有索引合并，应该优化查询或表结构
- 可以通过参数optimizer_switch关闭索引合并功能

索引列顺序

• 不考虑排序和分组时，将选择性最高的列放在前面

聚簇索引

聚簇索引是一种数据存储方式，InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行，当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。下表只能有一个聚簇索引（因为无法同时把数据行存在在两个不同的地方）

叶子页包含了行的全部数据，但是节点页只包含了索引列

• MySQL内建的存储引擎不支持指聚簇索引，InnoDB默认通过主键聚集数据。
• 如果没有定义主键，InnoDB会选择一个唯的非空索引代替，如果也没有，会隐式的定义一个主键作为聚簇索引

优点

• 把相关的数据保存在一起，只需要从磁盘读取少数的数据页就可以获取全部数据
• 数据访问更快，因为索引和数据保存在同一个B-Tree中
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

• 插入速度严重依赖于插入顺序
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动的新的位置
• 插入新行或更新导致需要移动行的时候，可能会有页分裂(page split)的问题
  

当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会产该页分裂成两个页来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间

可能导致全表扫描变慢

二级索引（非聚簇索引）可能比想象中大，因为二级索引的叶子节点中包含了引用行的主键列

二级索引访问需要两次索引查找
二级索引叶子节点保存的是行的主键值，而不是指向行的物理位置的指针

InnoDB中，聚簇索引的每个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。二级索引的每个叶子节点都包含了索引列和主键值

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称之为“覆盖索引”。- 索引条目通常远小于数据行大小，如果只需要读取索引，MySQL会极大的减小数据访问量- 因为索引是按顺序存储的，所以对于I/O密集型的范围查询，会比随机从磁盘读取每一行数据的I/O要少得多- 由于InnoDB中的聚簇索引，二级索引的叶子节点中保存了行的主键值，如果二级索引能够覆盖查询，就可以避免对主键索引的二次查询

使用索引扫描来做排序

MySQL有两种方式生成有序结果：通过排序操作或者按索引顺序扫描- 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引对结果做排序

- 如果查询关联多张表，只有当ORDER BY子句引用的字段全部为第一个表时，才能够使用索引做排序

索引和锁

• 索引可以让查询锁定更少的行。InnoDB只在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量
• InnoDB在二级索引上使用共享锁，但访问主键索引需要排他锁