MySQL索引（2）

一、索引基础

1. B-Tree索引

<1> 所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

<2> 顺序组织存储，很适合查找范围数据，效率会非常高。

<3> 可以有效使用B-Tree索引的查询：全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另一列、只访问索引的查询，还可以用于查询中的order by和group by操作。

<4> B-Tree索引的限制：

　　如果不是按照索引的最左列开始查找，则无法使用索引；

　　不能跳过索引中的列；

　　如果查询中有某个列的查询范围，则其右边所有列都无法使用索引优化查询。

2. 哈希索引

<1> 基于哈希表实现，只对精确匹配索引所有列的查询才有效。

<2> 对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

<3> 在mysql中只有memory引擎显式支持哈希索引。Memory引擎支持非唯一哈希索引，如果多个哈希列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

<4> 哈希索引只需存储哈希值和对应的行数据地址指针，所以索引的结构十分紧凑，使得哈希索引查找的速度非常快。

<5> 哈希索引限制：

　　哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行，即无法使用哈希索引进行索引覆盖查询。

　　哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。

　　不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。

　　只支持等值比较查询（=、IN、<=>），不支持任何范围查询。

　　出现哈希冲突时需要遍历链表中所有的行指针，逐行进行比较直到找到所有符合条件的行。冲突很多的话一些索引维护操作的代价也会很高。

3. 创建自定义哈希索引

　　如果存储引擎不支持哈希索引，可以在B-Tree索引基础上创建一个伪哈希索引。还是使用B-Tree进行查找，但是使用哈希值而不是键本身进行索引查找。这在字段值是很长的字符串的时候，可以很好地提升性能。

　　可以选择CRC32或FNV64作为哈希函数（哈希值为整数），不要使用SHA1和MD5，因为这两个函数产生的哈希值是非常长的字符串。

　　可以使用触发器来维护哈希值。

　　为避免哈希冲突，使用哈希索引进行查询的时候，必须在where条件中带入哈希值和对应列的值。

 

二、索引优点

1. 大大减少了服务器需要扫描的数据量。

2. 可以帮助服务器避免排序和临时表。

3. 可以将随机I/O变为顺序I/O。

　　评价一个索引是否适合某个查询的“三星系统”：索引将相关的记录放到一起；索引中的数据顺序和查询中的排列顺序一致；索引中的列包含了查询中需要的全部列。

　　然而，索引并不总是最好的解决方案，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效；对于中到大型表，索引就非常有效；但对于特大型表，建立和使用索引的代价增大，需要考虑使用分区、块级别元数据技术等其他技术。

 

三、高性能的索引策略

1. 独立的列

　　如果查询中的列不是独立的，mysql就不会使用索引，“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

2. 前缀索引和索引选择性

<1> 前缀索引可以节约索引空间，提高索引效率。

<2> 使用前缀索引会降低索引的选择性（不重复的索引值和数据表记录总数的比值，索引的选择性越高则查询效率越高，唯一索引的选择性是1，这是性能最好的），所以要选择足够长的前缀，但又不能太长，太长会占用太多存储空间，所以需要对最常见值列表进行分析来决定前缀的合适长度。可以将平均选择性：Select count(distinct left(name,length))/count(*) from ...; 与完整列选择性进行对比得出最适合的前缀长度。另外，数据分布不均匀时也需要考虑最坏的情况。

<3> 前缀索引无法使用于order by和group by，也无法做覆盖扫描。

3. 多列索引选择合适的索引列顺序

　　多列索引的索引列顺序选择通常都要考虑如何更好满足查询、排序、分组的需要。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，当然也需要结合值的分布考虑，可能需要根据那些运行频率最高的查询来调整索引列的顺序。

4. 聚簇索引

<1> 当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。（叶子页包含了行的全部列数据，但是节点页只包含了索引列）

<2> 一个表只能有一个聚簇索引。（覆盖索引可以模拟多个聚簇索引的情况）

<3> 不是所有的存储引擎都支持聚簇索引。

<4> 一些数据库服务器允许选择哪个索引作为聚簇索引，但mysql的存储引擎并不支持。InnoDB通过主键聚集数据，如果没有定义主键，则选择一个唯一的非空索引代替。如果没有这样的索引，就隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

<5> 聚簇索引的优点：

　　可以把相关的数据保存在一起。

　　数据访问更快。

　　使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

<6> 聚簇索引缺点：

　　聚簇数据最大限度地提高了I/O密集型应用的性能，但是如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。

　　插入速度严重依赖于插入顺序。

　　更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的列移动到新的位置。

　　基于聚簇索引的表在插入新行或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题，导致表占用更多的磁盘空间。

　　聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

　　二级索引（非聚簇索引）可能要更大，因为在二级索引的叶子节点包含了引用行的主键列。

　　二级索引访问需要两次索引查找。（对于InnoDB，自适应哈希索引能减少这样的重复工作）

InnoDB和MyISAM的数据分布对比：

<1> MyISAM按照数据插入的顺序存储在磁盘上。

<2> MyISAM中主键索引和其他索引在结构上没什么不同，主键索引就是一个名为primary的唯一非空索引。

<3> 聚簇索引的每一个叶子节点都包含了主键、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。

<4> InnoDB二级索引的叶子节点存储的不是“行指针”而是主键值，并以此作为指向行的“指针”。好处：InnoDB在移动行时无须更新二级索引中的这个“指针”，坏处：二级索引占用更多的空间。

在InnoDB表中按主键顺序插入行：

　　使用InnoDB时应该尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行，否则会导致已经刷到磁盘上的目标页被重新读取到内存中、页分裂、数据碎片等情况。需要optimize table来重建表并优化页的填充。

　　对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用，可能需要考虑重新设计表或应用，或者更改innodb_autoinc_lock_mode配置。

5. 覆盖索引

　　索引包含了所有需要查询的字段的值，无须读取数据行。

覆盖索引优点：

<1> MySql会极大地减少数据访问量。

<2> 对于I/O密集型的范围查询会比随机从磁盘中读取每一行数据的I/O要少得多。

<3> 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此访问数据需要一次系统调用，使用覆盖索引就不需要系统调用了。

<4> 如果InnoDB的二级索引为覆盖索引，则可以避免对主键索引的二次查询。

注意：不是所有类型的索引都可以成为覆盖索引，也不是所有存储引擎都支持覆盖索引。MySql只能使用B-Tree索引做覆盖索引。

索引覆盖查询：

　　MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。（就算没有where子句也可以使用索引覆盖查询）Explain出来的“extra”列为“using index”说明查询使用了覆盖索引。

使用索引扫描来做排序：

　　如果Explain出来的“type”列为“index”说明使用了索引扫描来做排序。按索引顺序读取数据的速度通常要比顺序地全表扫描慢，因为如果索引不能覆盖查询所需的全部列，每扫描一条索引记录就要回表查询一次对应的行，这基本上都是随机I/O。

　　MySQL可以使用同一个索引既满足排序又用于查找行。用于排序的索引需要满足以下条件：

<1> 索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样。

<2> 如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。

<3> order by子句引用的字段需要满足索引的最左前缀的要求。有一种情况order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，例如where子句或join子句中对这些列指定了常量。

压缩（前缀压缩）索引：

　　MyISAM存储引擎使用前缀压缩来减少索引大小，从而让更多的索引可以放入内存中，提高性能。具体方法：先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把相同前缀的字节数和剩余的不同后缀部分存储起来即可。

　　压缩索引的缺点是：myiSAM查找时无法在索引块使用二分查找而只能从头开始扫描，order by desc时倒序扫描更慢。

　　可以在create table语句中指定pack_keys参数来控制索引压缩的方式。

冗余和重复索引：

　　重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个进行考虑，重复索引会影响性能。

　　MySQL的唯一限制和主键限制都是通过索引实现的。

　　如果创建了索引(A,B)，再创建索引(A)就是冗余索引。不同类型的索引不算冗余。应该尽量扩展已有的索引而不是创建新索引，除非扩展之后会对性能有不好的影响。

索引和锁：

<1> 索引可以让查询锁定更少的行。

<2> 即使使用了索引，InnoDB也可能锁住一些不需要的数据。

<3> InnoDB在二级索引上使用共享读锁，但访问主键索引需要排它锁，这消除了使用覆盖索引的可能性，并且使得select for update比lock in share mode或非锁定查询要慢很多。

 

四、索引优化策略

1. 有些列虽然选择性低，但如果在where子句中使用频率很高的话也应该创建索引，或将其作为多列索引的前缀列。

2. 就算在查询中没有某个列的限制，也可以加上这个列的判断，这样mysql才能匹配索引的最左前缀。如：sex in (‘m’,’f’)，但也不能滥用，每增加一个In条件优化器需要做的组合都将以指数形式增加，降低查询性能。

3. Mysql查询只能使用索引的最左前缀，直到遇到第一个范围条件列，所以尽可能将需要做范围查询的列放在索引的后面，以便优化器能使用尽可能多的索引列。（用IN来来代替范围条件）

4.除了or查询，MySQL查询每次最多只使用一个索引，因为相对于全表扫描和只使用一个索引，去分析两个或两个以上的索引二叉树更加耗时。对于查询：select col from table where col1=** and col2=*** and col3=****，建立复合索引(col1,col2,col3)来进行查询的性能是最好的。

 

五、维护索引和表

1. check table：检查索引和表的错误。

2. repair table：修复损坏的表。如果存储引擎不支持可以通过一个不做任何操作的alter命令来重建表。

3. analyze table：重新生成索引统计信息。

　　MySQL的查询优化器会通过records_in_range()和info()这两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。每种存储引擎实现索引统计信息的方式不同：Memory引擎不存储索引统计信息，myisam将索引统计信息存储在磁盘中，InnoDB通过随机的索引访问进行评估并将统计信息存储在内存中。InnoDB在打开某些information_schema表，或使用show table status和show index，或在mysql客户端开启自动补全功能的时候都会触发索引统计信息的更新。

4. optimize table/导出再导入：减少索引和数据的碎片。如果存储引擎不支持可以通过不做任何操作的alter命令来重建表。

 

最后推荐一篇博文：MySQL索引背后的数据结构及算法原理，这篇博文中结合数据结构和计算机磁盘读写原理详细分析了数据库系统为何使用Btree作为索引数据结构，以及MySQL聚簇索引和非聚簇索引的实现，如何高性能使用索引的策略，是我阅读过写得最好的文章了，强力推荐！！！