Noob
0%

MySQL中的索引

发表于 更新于 分类于 ，

MySQl中，索引在存储引擎层实现而不是服务器层，

B-Tree索引

B-Tree（参考B-Tree，B+Tree）对索引列是按顺序组织存储的，所以很适合查找范围数据、全键值或键前缀查找。索引对多个值进行排序的依据是CREATETABLE语句中定义索引的顺序。B-Tree索引有以下限制：

  1. 如果不是按照索引的最左列开始查找，则无法使用索引
  2. 不能跳过索引中的列
  3. 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。

哈希索引

哈希索引（Hash Index）基于哈希表实现，只有精确匹配所有列的查询才有效。哈希索引的结构非常紧凑，也使得哈希索引的速度非常快。但是，哈希索引也有它的限制：

  1. 哈希索引只包含哈希值和行指针，不存储字段值。所以，不能使用索引中的值来避免读取行。
  2. 哈希索引不是按照索引值顺序存储的，无法用于排序
  3. 哈希索引也不支持部分索引列匹配查找
  4. 哈希索引只支持等值比较查询
  5. 当出现冲突时，需要遍历桶中的所有行指针，性能会有所下降

索引策略

独立的列

如果查询中的列不是独立的，MySql就不会使用索引，独立的列是指索引列不能使表达式的一部分。例如，SELECT id FROM user WHERE id + 1 = 5就无法使用id上的索引。

前缀索引与索引选择性

索引很长的字符串列，会让索引大且慢。可以通过插入一个hash列模拟哈希索引，或者可以只索引开始的部分字符，这样可以大大节约索引空间，提高索引效率。但这样会降低索引选择性，索引选择性是指不重复的索引值（也称为基数，cardinality）和数据表中的记录总数的比值。索引的选择性越高则查询效率越高。前缀索引可以使索引更小更快，但是MySQl无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

多列索引

在多个列上建立独立的单独索引大部分情况下并不能提高MySQL的查询性能。在5.0之前的版本，MySQL只能使用其中某一个单列索引，在5.0以后的版本中，查询能够同时使用多个单列索引扫描并将结果合并。有三种合并：OR条件联合（Union）、AND条件相交（Intersection）以及联合与相交的组合。索引合并有时候是一种优化，但更多时候说明了索引建的很糟糕：

  1. 当服务器对多个索引做相交操作时（多个And条件），通常说明需要一个包含所有相关列的多列索引，而不是多个独立的单独索引。
  2. 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU、内存资源。

可以通过optimizer_switch关闭索引合并功能，也可以使用IGNORE_INDEX提示让优化器忽略某些索引。

索引顺序

经验法则：将选择性最高的列放到索引最前面。该法则在一定条件下适用，但是需要综合考虑，最重要的是要尽量避免随机IO和排序。查询性能也和查询条件的值分布有关，可以根据那些运行频率最高的查询来调整索引列的顺序。

聚簇索引

聚簇索引是一种数据存储方式，InnoDB的聚簇索引实际上是在同一个数据结构中保存了B-Tree索引和数据行。叶子页中包含了行的全部数据，节点页中只包含索引项。

InnoDB通过主键聚集数据，如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的非空索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面的记录，包含相邻键值的页面可能会相距甚远。聚集数据的优点：

  1. 相关数据保存在一起，减少随机IO次数
  2. 聚簇索引将索引和数据保存在同一个B树中，从聚簇索引中获取数据通常比在非聚簇索引中查找更快。

聚簇索引的缺点：

  1. 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
  2. 在基于聚簇索引的表中插入新行，或者更新索引列时可能导致页分裂（page split）问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，页分裂会导致表占用更多的磁盘空间。
  3. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致存储不连续时。
  4. 通过二级索引（叶节点中存储主键值而非聚簇索引叶节点中的数据行）查找行时，存储引擎先找到二级索引的叶节点获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行，需要两次B树查找。

MyISAM 与InnoDB的数据分布

MyISAM中的主键索引与其他列索引并没有什么不同，都是基于B+树，非叶节点中存储键值，页节点中存储指向数据行的指针。

InnoDB中的索引也采用B+树组织，不过叶节点中存储的是整个数据行以及事务ID、用于事务和MVCC的回滚指针。InnoDB的二级索引的叶节点中存储的不是行指针，而是主键值，并以此作为指向行的指针从聚簇索引中查找完整数据。这种策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。

覆盖索引

如果一个索引包含所有需要查询字段的值，该索引就成为覆盖索引。在索引中满足查询的成本一般比查询行要小得多。覆盖索引必须覆盖索引列的值，MySQL只能使用B树索引做覆盖索引。

MySQL查询优化器在执行查询前会判断是否有一个索引能够进行覆盖，索引无法覆盖查询的情况有两种：

  1. 没有任何索引能够覆盖这个查询，这种情况下，如果WHERE条件中的列有索引可以覆盖，MySQL可以过滤之后再读取需要的数据行。
  2. MySQL不能在索引中执行某些LIKE操作 ，MySQL能够执行最左前缀匹配的LIKE查询，但是如果是通配符开头的LIKE查询，存储引擎就无法做比较。

压缩索引

MyISAM使用前缀压缩来减少索引大小，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节树和剩余的不同后缀部分。例如第一个值“perform”则第二个值“performance”，压缩后第二个值的实际存储值是“7，ance”。随机压缩可以让更多的索引放入内存，对于IO密集型操作，性能更高。由于大量的值依赖于前面的值，随机查找性能降低，CPU密集型应用性能降低。

索引优化

  1. 当多次出现对多个索引做相交操作（有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
  2. 设计索引顺序时要尽量避免随机IO，当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。
  3. 索引本身有一定的开销，不是越多越好。例如性别只有两个值，在性别字段建索引对查询速度的提升不高，但是更新时会降低性能。
  4. 复合索引中，只要有一列含有NULL，那么这一列对复合索引无效。在设计数据库时，尽量避免让字段的默认值为NULL。
  5. 使用前缀索引，例如一个CHAR（255）的列，如果前10或20个字段内多数值是唯一的，那么就不需要对整列进行索引。
  6. 在列上进行运算（参与运算或者作为函数参数）将导致索引失效，应尽量避免。

参考资料
《高性能MySQL（第三版）》