创建高性能索引（高性能MySQL读书笔记）

索引基础

索引是在存储引擎层，而不是在服务层。

索引的类型

B-Tree索引

使用B树数据结构来存储数据（实际上很多存储引擎使用的是B+树，即每一个叶子结点都包含指向下一个叶子节点的指针，从而方便叶子结点的范围索引）。

MyISAM	InnoDB
使用前缀压缩技术使得索引更小	按照源数据格式进行存储
通过数据的物理位置引用被索引的行	根据主键引用被索引的行

B树通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。

索引生效的情况：

原则	描述
全值匹配	查找列和索引中的所有列进行匹配
匹配最左前缀
匹配列前缀	匹配某一列的开头部分
匹配范围值	可用于查找范围
精确匹配某一列并范围匹配另外一列	例如：第一列精确全值匹配，第二列范围匹配
只访问索引的查询	覆盖索引，查询只需要访问索引，而无需访问数据行

索引的限制：

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

• 不能跳过索引中的列。

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

• 通配符开头的LIKE查询，只能提取数据行的值而不是索引值来比较。

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。只有Memory引擎显示支持哈希索引（支持非唯一哈希索引，哈希值相同索引会以链表的方式存放多个记录指针到同一个哈希条目中）。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（一个较小的值），不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避 免读取行。

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

• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B). 上建立哈希索引,如果查询只有数据列A,则无法使用该索引。

• 哈希索引只支持等值比较查询,包括=、IN()、<=> (注意 <>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price > 100.

• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却有相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要,完全可以关闭该功能。

思路：

在B树的基础上创建一个伪哈希索引。还是使用B树进行查找，但是使用哈希值而不是键本身进行索引查找。需要在查询的where中手动指定哈希函数。

如果出现哈希冲突，应该在where中指定常量值。

空间数据索引（R-Tree）

MyISAM支持空间索引，可以用做地理数据存储，和B树索引不同，这类索引无须前缀查询。

全文索引

查找的是文本中的关键字，而不是直接比较索引中的值。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST 操作，而不是普通的WHERE条件操作。

索引的优点

B树索引是顺序存储数据，将相关的列值存储在一起。索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询。

• 大大减少了服务器需要扫描的数据量，如果是InnoDB会减少行锁和间隙锁锁定的行数。

• 帮助服务器避免文件排序（ORDER BY，GROUP BY，文件排序会使用临时表来辅助排序）和临时表（UNION，FROM子查询）。

  关联查询时：

  • ORDER BY子句中的所有列都来自关联的第一个表，在处理第一个表的时候就会进行文件排序。

  • 除第一种情况之外，将关联的结果存放到一个临时表中，然后在所有的关联都结束后，在进行文件排序。

  生成临时表的方式：

  • UNION

  • GROUP BY

  • 文件排序：ORDER BY、DISTINCT

  • FROM子查询，多表关联

  结果集会自动按照分组的字段进行排序。可能会导致文件排序，可以使用ORDER BY NULL，让MySQL不再进行问及那排序。

• 索引可以将随机I/O变为顺序I/O（但是回表仍有可能造成随机I/O，这也是覆盖索引不会回表的好处）。

“三星系统"：

• 索引将相关的记录放到一起则获得一星;

• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星 ;

• 如果索引中的列包含了查询中需要的全部列则获得“三星”。

高性能的索引策略

独立的列

“独立的列”：索引列不能是表达式，也不能是函数的参数。

前缀索引和索引选择性

索引选择性：不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高。

如果索引很长的字符列，会让索引变得大且慢。

解决方法：

1. 模拟哈希索引，在哈希冲突的时候可能需要执行常量值。

2. 前缀索引：索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

选择前缀索引的方式：

1. 前缀的选择性接近完整列的选择性。

2. 计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

前缀索引的创建方式：

alter table city_demo add key(city(7));

前缀索引的优缺点：

使索引更小、更快。但是MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

多列索引

“索引合并”：可使用表上的多个单列索引来定位指定的行。OR：union，AND：intersection。

如果在EXPLAIN中看到有索引合并，应该好好检查下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_ switch 来关闭索引合并功能。也可以使用IGNORE INDEX 提示让优化器忽略掉某些索引。

选择合适的索引列顺序

如果不考虑排序和分组时，将选择性最高的列放在索引的最前列。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

当表有聚簇索引时，他的数据行的全部数据实际上存放在索引的叶子页中，结点页只包含索引列。“聚簇”表示数据行和相邻的简直紧凑地存储在一起。因为无法将数据行放在两个不同的地方，所以一个表只有一个聚簇索引。（覆盖索引可以模拟多个聚簇索引的情况）

InnoDB聚簇索引的选择：

1. 通过主键聚集数据。

2. 选择一个唯一的非空索引替代。

3. 隐式定义一个主键来作为聚簇索引。

聚簇的数据的优点：

InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能会相聚甚远。

• 可以把相关数据保存在一起，减少磁盘I/O。

• 数据访问更快。聚簇索引将索引和数据保存在同一个B树中。

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

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序。按照主键的顺序插入时加载数据到InnoDB表中的速度最快的方式。

  如果不是按照主键顺序加载数据，在加载完成后最好使用OPTIMIZE TABLE重新组织表。

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

• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂(page split)”的问题。当行的主键值要求必须将这一行插入 到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。

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

• 二级索引包含了引用行的主键列。二级索引访问需要两次索引查找，而不是一次。InnoDB有自适应哈希索引减少这样的重复工作，

InnoDb和MyISAM的数据分布对比

MyISAM：

• 按照数据插入的顺序存储在磁盘上。

  

• 索引的叶子结点包含了数据行的物理地址。二级索引不会因为查找数据行而进行二次索引查找。

  主键索引：叶子结点中存储行指针

  

  二级索引：叶子结点中存储行指针

  

InnoDB：

• 聚簇索引：

  不是像MyISAM需要独立的行存储，聚簇索引“就是”表。聚簇索引的每一个叶子节点都包含了主键值（主键是列前缀索引也会包含完整的主键，左前缀主键在最后）、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。

  

• 二级索引：

  二级索引的叶子系欸但中存储的不是“行指针”，而是主键值。

  优点：InnoDB在移动时无须更新二级索引中的这个指针。

  缺点：使用主键值当作指针会让二级索引占用更多的空间。

  

比较MyISAM和InnoDB保存数据和索引的区别：

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

最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用。随机的聚簇索引插入数据耗时长，并且占用的空间大（页分裂和碎片等）。

聚簇索引顺序插入索引值：

每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认时页大小的15/16，留出空间用于修改），下一条记录就会写入新的页中，避免了页分裂。

顺序的主键什么时候造成更坏的结果？

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_ INCREMENT 锁机制;如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_ autoinc_ Lock_ mode 配置。

聚簇索引随机插入索引值：

• 缺点

  1. 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。

  2. 因为写入是乱序的，InnoDB 不得不频繁地做页分裂操作，以便为新的行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。

  3. 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

覆盖索引

如果一个索引包含所有需要查询的字段的值，那就是“覆盖索引”。查询只需扫描索引而无须回表（回表基本上会产生随机I/O，因为主键值不是按照一定的顺序）。

EXPLAIN的Extra列可以看到“Using index”。

MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。

优点：

• 索引条目小于数据行大小，只需要读取索引，大大减少了数据访问量。对缓存的负载非常重要，因为响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，索引小可以更容易全部放入内存中（MyISAM能压缩索引变得更小）。

• 索引按照列值顺序存储（单个页内），对于I/O密集型地范围查询会比随机从磁盘中读取每一行数据地I/O少得多。OPTIMIZE TABLE命令使得索引完全顺序排列。

• MyISAM在内存中只缓存索引，数据依赖于操作系统来缓存，访问数据需要系统调用。

• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB 的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

使用索引扫描来做排序

MySQL生成有序的结果的方式：排序操作、按索引顺序扫描。EXPLAIN出来的type列的值为“index”。

扫描索引本身是很快的，但是如果索引不能覆盖查询的全部列，那么每扫描一条索引记录都回表查询一次，这是随机I/O。

SELECT的列和索引列不必相同或是包含的关系，也可以使用索引扫描排序。

使用索引扫描来做排序的条件：

• 索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序，正序）都一样。

• 查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引来排序。

• 需要满足索引的最左前缀要求。或前导列为常量时。

前缀压缩索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中。默认只压缩字符串。

压缩索引块的方法：

先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储即可。

压缩索引块的优缺点：

压缩块使用更少的空间，因为每个值的压缩前缀都依赖前面的值，所以MyISAM在查询时无法在索引块使用二分查找而只能从头开始扫描。正序ASC还好，但是倒序DESC在块中查找某一行的操作都要扫描之前的索引块。

冗余和重复索引

在相同列上创建多个索引，需要被单独维护，并且优化器在查询的时候也需要逐个进行考虑，影响性能。

重复索引：

在相同的列上按照相同的顺序创建的相同类型的索引。

冗余索引：

创建了已经存在索引的前缀索引（满足最左前缀），并且类型相同。

索引和锁

Extra中的“Using where”：MySQL服务器将存储引擎返回行以后再应用where过滤条件。

索引可以让查询锁定更少的行。InnoDB的行锁虽然效率高，占用内存少，但是锁定行的时候仍然会带来额外的开销。其次，锁定超过需要的行会增加锁征用减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能减少InnoDB的访问行数，从而减少锁的数量。

如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后， MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了: InnoDB已经锁住了这些行，到适当的时候才释放。在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期MySQL版本中，InnoDB只有在事务提交后才能释放锁。

如果不能使用索引查找和锁定行的话，MySQL做全表扫描并锁住所有的行，无论需不需要。

InnoDB在二级索引上使用共享(读)锁，但访问主键索引需要排他(写)锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE 比LOCK IN SHARE MODE或非锁定查询要慢很多。

索引案例学习

• 将范围查询使用多个等值操作进行替换。

  in：多个等值操作

  大于、小于等：范围查询

• 延迟关联：通过覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。

  #已有索引(sex,rating)
  select <cols> from profiles where sex = 'M' ORDER BY rating LIMIT 100000,10;
  # 使用延迟关联
  select <cols> from profiles join (
    select <primary key cols> from profiles where x.sex='M' ORDER BY rating limit 100000,10
  ) as x using(<primary key cols>);

   

维护索引和表

check table能够找出大多数的表和索引的错误。

repait table修复损坏的表。

InnoDB引擎通过抽样的方式来计算统计信息。

减少索引和数据的碎片化

B树索引的碎片化：

B树索引可能会碎片化，碎片化的索引可能会以很差或无序的方式存储在磁盘。

B树需要随机磁盘访问才能定位到叶子页。如果叶子页在物理上是顺序且紧密的，对于范围查询和索引覆盖扫描等操作来说，速度可能会降低很多倍。

表的数据存储碎片化：

• 行碎片（MyISAM）

  数据行被存储为多个地方的多个片段中。

• 行间碎片

  逻辑上顺序的页，或者行在磁盘上不是顺序存储的。对全表扫描和聚簇索引扫描之类的操作有很大的影响。

• 剩余空间碎片

  数据页中有大量的空余空间。会导致服务器读取大量不需要的数据，从而造成浪费。

 

可以使用OPTIMIZE TABLE来进行优化。

总结

1. 单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。

2. 按顺序访问范围数据是很快的,这有两个原因。第一,顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多(特别是对机械硬盘)。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无须再做排序和将行按组进行聚合计算了。

3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问是很慢的。