第5章 创建高性能的索引（二）

切实体会到想真正掌握到技术，一定要看书加自己手敲代码测试

5.3 高性能的索引策略

5.3.1 独立的列

独立的列，是指索引不能是表达式的一部分，也不能是函数的参数。比如：select actor_id from sakila.actor where actor_id + 1 = 5; MySQL无法自动解析这个方程式，因此不会使用索引。

5.3.2 前缀索引和索引选择性

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

前缀索引是一种使索引更小、更快的有效方法，但另一方面也有缺点：MySQL无法使用前缀索引做ORDER BY 和GROUP BY，也无法使用前缀索引做覆盖扫描。

5.3.3 多列索引

MySQL5.0和更新版本引入了一种叫做“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕

• 当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 更重要的是，优化器不会把这些计算到“查询成本”（cost）中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接走全表扫描。

如果在EXPLAIN中看到有索引合并，应该好好检查一下查询和表的结构。 也可以通过参数optimizer_switch来关闭索引合并功能

5.3.4 选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列 当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

5.3.5 聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。
因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引。

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

聚集的数据有一些重要的优点：

• 可以把相关数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引也有一些缺点：

• 聚簇数据最大程度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没有什么优势了
• 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
• 二级索引（非聚簇索引）可能比想象的要更大，因为在二级索引的叶子节点包含了引用列的主键列。
• 二级索引访问需要两次索引查找，而不是一次。

为什么二级索引需要两次索引查找？答案在于二级索引中保存的“行指针”的实质。要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。

InnoDB的数据分布

聚簇和非聚簇表对比图

在InnoDB表中按主键顺序插入行，最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

使用了UUID聚簇索引的表，缺点如下：

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
• 因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新地行分配空间。页分裂会导致移动大量数据，一次插入最少需要修改三个页而不是一个页。
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。

顺序地主键什么时候会造成更坏地结果？对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显地争用。主键地上界会成为“热点”。因为所有地插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个可能是AUTO_INCREMENT锁机制

5.3.6 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询地字段地值，我们称之为“覆盖索引”

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大地减少数据访问量。
• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型地范围查询会比随机从磁盘读取每一行数据地I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。

索引覆盖查询还有很多陷阱可能会导致无法实现优化。没有任何索引能覆盖了所有的列。MySQL不能在索引中执行LIKE操作。

5.3.7 使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；

5.3.8 压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提供性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。
压缩块使用更少地空间，代价是某些操作可能更慢。

5.3.9 冗余和重复索引

重复索引是指在相同地列上按照相同地顺序创建地相同类型地索引。应该避免这样创建重复索引，发现以后也应该立即移除。

5.3.10 未使用的索引

有两个工具可以帮助定位未使用的索引。最简单有效的办法是在Percona Server 或者MariaDB中打开suerstates服务器变量，然后让服务器正常运行一段时间，再通过查询INFORMATION_SCHEMA.INDEX_STATISTICS就能查到每个索引的使用频率。
另外，可以使用Percona Toolkit中的pt-index-usage，该工具可以读取查询日志，并对日志中的每条查询进行EXPLAIN操作，然后打印出关于索引和查询的报告。

5.3.11 索引和锁

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

5.4 索引案例学习

设计一个在线约会网站，很多列，支持组合搜索

5.4.1 支持多种过滤条件

考虑表上所有的选项。

5.4.2 避免多个范围条件

5.4.3 优化排序

对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。
另一个较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行

5.5 维护索引和表

维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.5.1 找到并修复损坏的表

可以通过REPAIR TABLE命令来修复损坏的表，也可通过一个不做任何操作（no-op）的ALTER操作来重建表

5.5.2 更新索引统计信息

5.5.3 减少索引和数据的碎片

5.6 总结

在选择索引和编写利用这些索引的查询时，有如下三个原则始终需要记住：
1.当行访问是很慢的。
2.按顺序访问范围数据是很快的
3.索引覆盖查询是很快的

如何判断一个系统创建的索引是合理的呢？一般来说，我们建议按响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是有太多回表查询那些不在索引中的列的操作