高性能MySQL学习笔记（四）

1. 索引基础

索引（在 MySQL 中也叫作“键（key）”）是存储引擎用于快速找到记录的一种数据结构。“最优”的索引会比“好的”索引性能要好两个数量级，当然，糟糕的索引在大数据量下同样会导致性能的急剧下降。
如果来描述 MySQL 中的索引呢？就好像你在看一本书，如果你想找到特定主体，一般都会先去看“目录”，找到对应页码。在 MySQL 中，存储引擎用类似的方法使用索引，其先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。

mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5

如果在 actor_id 列上有索引，则 MySQL 将使用该索引找到 actor_id 为5的行。MySQL 先在索引上按值进行查找，然后返回所有包含该值得数据行。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为 MySQL 只能高效地使用索引的最左前缀列。

2. 索引类型

2.1 B-Tree 索引

我们使用术语“B-Tree”， 是因为 MySQL 在 CREATE TABLE 和其他语句中也使用该关键字。底层存储引擎可能使用不同的存储结构，NDB 集群存储引擎内部使用了 T-Tree 这种结构的索引，但名字是 BTREE；InnoDB 则使用的是 B+Tree。
存储引擎以不同的方式使用 B-Tree 索引，各有优劣。例如：MyISAM 使用前缀压缩技术使得索引更小，但 InnoDB 则按照原数据格式进行存储；MyISAM 索引通过数据的物理位置引用被索引的行，而 InnoDB 则根据主键引用被索引的行。B-Tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。InooDB 索引工作图：
图1：建立在 B-Tree 结构（从技术上说是 B+Tree）上的索引
B-Tree 索引之所以能够加快访问数据的速度是因为避免了全表扫描。索引是顺序组织存储的，所以很适合查找范围数据。

2.2 可以使用 B-Tree 索引的查询类型

B-Tree 索引适合用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于最左前缀的查找。

全值匹配

全值匹配指的是和索引中的所有列进行匹配。

匹配最左前缀

只使用索引的第一列。

匹配列前缀

只匹配某一列的值得开头部分。

匹配范围值

查找索引列上的范围值。

精确匹配某一列并范围匹配另一列

第一列是精确匹配，第二列是范围匹配

值访问索引的查询

B-Tree 通常可以支持“只访问索引的查询”，即查询只需要访问索引，无需访问数据行。（覆盖索引）

索引除了按值查找外，还可以用于查询中的 ORDER BY 操作。

B-Tree 索引限制

• 如果不是按照索引的最左列还是查找,则无法使用索引。
• 不能跳过索引中的列。
• 如果查询中有某个列的范围查询，则右边所有列都无法使用索引优化查找。

  在性能优化的时候，可能需要使用相同列但顺序不同的索引来满足不同类型的查询需求。

为什么会有最左匹配原则？ ** 最左匹配原则都是针对联合索引来说的，所以我们先来了解一下联合索引的原理。 索引的底层是一颗 B+树，那么联合索引的话也应该是一颗 B+树，只不过联合索引的键值数量不是一个，而是多个。构建一颗 B+树只能根据一个值来构建，因此数据库依据联合索引最左的字段来构建 B+树。加入创建一个（a，b）的联合索引，那么它的索引树是这样的。 可以看到 a 的值时有顺序的，1,1,2,2,3,3，而 b 的值是没有顺序的，1,2,1,3,1,2。 所以 b = 2 这种查询条件没有办法利用索引，因为联合索引首先是按 a 排序的， b 是无序的。

同时我们还可以发现，在 a 值相等时，b 的值又是按顺序排列的。所以最左匹配原则遇上范围查询就会停止查询，剩下的字段都无法使用索引。例如 a = 1 and b = 2 ，a，b字段都可以使用索引。因为 a 值在确定的情况下 b 是相对有序的。 而 a > 1 and b = 2，a 字段可以匹配上索引，但 b 值不可以，因为 a 值是一个范围，在这个范围中 b 是无序的。

哈希索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，储存引擎都会对所有的索引列计算一个哈希码（hash code）。哈希所以将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

mysql> SELECT lname FROM testhash WHERE fname = 'Peter';

假象哈希函数返回值：

哈希索引的数据结构：

MySQL 先计算 ‘Peter’的哈希值，并且使用该值寻找对应的记录指针。因为f('Peter')=8784，所以 MySQL 在索引中查找 8784， 可以找到指向第三行的指针，最后一步是比较第三行的值是否为 ‘Peter’，以确保就是要查找的行。

哈希索引限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据不是按照索引值顺序存储的，无法用于排序。
• 哈希索引不支持部分索引列匹配查找。
• 哈希值只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同操作）

• 如果出现很多哈希冲突的时候，因为要遍历链表，所以性能可能会有很大损耗。

  自适应哈希索引

  InnoDB 引擎有一个特殊的功能叫做“自适应哈希索引（Adaptive hash index）”。当 InnoDB 注意到某些索引值被使用的非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，会让 B-Tree 索引也具有哈希索引的一些优点。

  空间数据索引（R-Tree）

  MyISAM 表支持的空间索引，可以用作地理数据存储。这类所以无需前缀查询。空间索引会从所有维度来索引数据。

  全文索引

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

  3. 索引的优点

  索引可以让服务器快速定位到表的制定位置，B-Tree 索引按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作；B-Tree 会将相关的列值都存储在一起。因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成去全部查询。

• 索引大大减少了服务器需要扫描的数据量

• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机 I/O 变为顺序 I/O

使用索引是最好的解决方案吗？

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。 对于非常小的表，大部分情况下简单的全表扫描更高效。 对于中到大型的表，索引就非常有效。 对于特大型的表，建立和使用索引的代价非常大。这种情况则可以使用分区技术，直接区分查询需要的一组数据。

4. 高性能索引策略

4.1 独立的列

索引列不能是表达式一部分，也不能是函数的参数。常见错误如下：

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

4.2 前缀索引和索引选择性

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T 到 1 之间。索引的选择性越高则查询效率越高。因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。 举个栗子：假设一个表中有100行数据，该列的值中有80个不同的值，那么该列的索引的选择性就是0.8

对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引。如何选择合适的索引在于选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择接近于索引整个列。计算合适的前缀长度其中一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。
这个例子中如果前缀的选择性能够接近 0.031 基本上就可用了。可以再一个查询中针对不同前缀长度进行计算，这对于达标非常有用。

查询显示前缀长度到7的时候，在增加前缀长度，选择性提升的幅度已经很小了。
创建前缀索引：

ALERT TABLE sakila.city_demo ADD KEY(city(7))

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

4.3 多列索引

很多人对于索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引或者按照错误的顺序创建多列索引。在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。MySQL 5.0以后版本引入了一种叫“索引合并”（index merge） 的策略，一定程度上可以使用表桑的多个单列索引来定位指定的行。索引合并策略有时候是一种优化的结果，但实际上更多时候效果可能会更糟糕。如果在 EXPLAIN 中看到有索引合并，应该好好检查一些查询和表的结构，看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能。也可以使用 IGNORE INDEX 提示让优化器忽略掉某些索引。

4.4 选择合适索引列顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何会更好地满足排序和分组的需要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面只是用于优化 WHERE 条件的查找。

以下面的查询为例：

mysql> SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

是创建一个（staff_id，customer_id）索引还是应该点到一下顺序？可以跑一些查询来确定在这个表中值的分布情况，并确定那个列的选择性更高。

mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment
************************** 1.row ***********************************
 SUM(staff_id = 2): 7992
 SUM(customer_id = 584): 30

根据前面的经验法则，应该将索引列 customer_id 放到前面，因为对应条件值的 customer_id 数量更小。
我们再来看看对于这个 customer_id 的条件之，对应的 staff_id 列的选择性如何：

mysql> SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584
************************** 1.row ***********************************
SUM(staff_id = 2): 17

mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
         > COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
     > COUNT(*)
     > FROM payment
************************** 1.row *************************************
   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
                 COUNT(*): 16049

customer_id 的选择性更高，所以答案是将其作为索引列的第一列。尽管选择性和基数的经验法值得去研究和分析，但一定别忘了 WHERE 子句中的排序、分组和范围条件等其他因素。

4.5 聚簇索引

“聚簇”表示数据行和相邻的键值紧凑地存储在一起。叶子页包含了行全部数据，但是节点也只包含了索引列。如下图，图片中索引列包含的是整数值：

在 InnoDb 中是通过主键聚集数据，也就是图中“被索引的列”就是主键列。如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键作为聚簇索引。
优点

• 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据也就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree 中
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点

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

为什么二级索引需要两次索引查找？ ** 二级索引保存的“行指针”实际上是行的主键值，而不是行的物理位置。这意味着要先找到二级索引叶子结点对应的主键值，然后再拿主键值去聚簇索引中找到对应的行。两次 B-Tree 查找而不是一次。对于 InnoDB ，自适应哈希索引能够减少这样的重复工作。

MyISAM 主键分布：

InnoDB 主键分布：

该图显示了整个表，而不是只有索引。因为在 InnoDB 中，聚簇索引“就是”表，不像 MyISAM 那样需要独立的行存储。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和 MVCC 的回滚指针以及所有的剩余列。InnoDB 的二级索引的叶子节点中存储的是主键值，并以此为指向行的“指针”。这样的策略减少了出现行移动或者数据分页时二级索引的维护工作。InnoDB 在移动行时无需更新二级索引中的“指针”。下图是 InnoDB 中的二级索引分布：


对于在 InnoDB 引擎中索引的选择，如果表中没有什么数据需要聚集，则可以定义一个代理键（surrogate key）作为主键，这种主键的数据和应用无关，最简单的方法是使用 AUTO_INCREMENT 自增列。最好避免随机的（不连续且值得分布范围非常大）聚簇索引，特别是对于 I/O 密集型的应用。使用 UUID 作为聚簇索引则会很糟糕：它是的聚簇索引的插入变得完全随机，最坏的情况使得数据没有任何聚集特性。
经过测试使用 UUID 主键插入不仅花费的时间长，而且索引占用的空间也更大。一方面是因为主键字段更长，另一方面是因为页分裂和碎片造成的。

因为主键的值时顺序的，所以 InnoDB把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子时页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照上述方式加载，主键页就会近似的被顺序记录填满。
如果使用 UUID 聚簇索引插入数据：

因为新值不一定比之前插入的大，所以 InnoDB无法简单地总是把新行插入到索引的最后，通常是已有数据的中间位置，并且分配空间。下面是总结的一些缺点：

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

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

关于 INNODB_AUTOINC_LOCK_MODE 参数

MySQL VERSION<= 5.1.22 MySQL 的 “INSERT-LIKE”语句（包括 INSERT，INSERT…SELECT，REPLACE，REPLACE…SELECT，and LOAD DATA）会在执行整个语句的过程中使用一个 AUTO-INC 锁，将表锁住，直到整个语句结束（并非是事务结束）。 因此在使用 INSERT…SELECT、INSERT…VALUES(…),VALUES(…)时，LOAD DATA 等耗费时间较长的操作时，会将整个表锁住，而阻塞其他的“INSERT-LIKE”、UPDATE 等语句。所以推荐使用程序将这些语句分成多条语句，一个一个插入，减少单一时间的锁表时间。 MySQL VERSION > 5.1.22 引入了参数 innodb_autoinc_lock_mode，通过这个参数控制 MySQL 的锁表逻辑。

1. “INSERT-like”

INSERT、INSERT…SELECT、REPLACE、REPLACE…SELECT、and LOAD DATA、INSERT…VALUES(), VALUES() innodb_autoinc_lock_mode = 0 传统模式 为了兼容而保留这种模式，表级锁定，并发性较差

1. “Simple inserts”

通过分析 INSERT 语句可以确定插入数量的 INSERT 语句，INSERT、INSERT … VALUES(),VALUE() innodb_autoinc_lock_mode = 1 连续模式 新版本中默认方式，推荐使用，并发行相对较高，同一条 INSERT 语句中新插入的自增长 id 都是连续的。 “分析语句，获得要插入的数量，然后一次分配足够的 auto_increment id，只会锁住整个分配过程”

1. “Bulk inserts”

分析SQL，不能确定插入数量的 INSERT 语句，INSERT … SELECT，REPLACE…SELECT，LOAD DATA … … “因为不能确定插入的数量，因此使用和以前的模式相同的表级锁定”

1. “Mixed-mode inserts”

不确定是否需要分配auto_increment id，一般是下面两种情况 INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);

INSERT … ON DUPLICATE KEY UPDATE … … “分析语句获得最坏情况下需要插入的数量，然后一次性分配足够的 auto_increment id 只锁分配过程” 该方式下回分配过多 id 而导致“浪费”

4.6 覆盖索引

如果一个索引包含所有需要查询的字段值，我们就成为“覆盖索引”。（不会产生回表查询动作）覆盖索引必须要存储索引列的值。MySQL 只能使用 B-Tree 索引做覆盖索引，因为哈希索引、空间索引和全文索引等都不存储索引的值。覆盖索引能够极大地提高性能，覆盖索引能够提供以下好处。

• 索引条目数远小于数据行大小，如果只读取索引，那 MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要。
• 在单个页内索引是按照列值顺序存储的，所以对于I/O密集型的范围查询比随机从磁盘读取每一行数据的I/O要少得多。
• 在 InnoDB 中的聚簇索引，会因为在二级索引中的叶子节点保存的可以满足覆盖查询，那么可以避免对主键索引的二次查询。

索引覆盖陷阱
因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列，则不会执行索引覆盖。
MySQL 中不能在索引中执行 LIKE 操作。MySQL 能在索引中做最左前缀匹配的 LIKE 比较。如果是通配符开头的 LIKE 查询，存储引擎就无法做比较匹配。

4.7 使用索引来做排序

MySQL 可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务。当索引的列顺序和 ORDER BY 子句顺序完全一致，并且所有列的排序方向都一样，MySQL 才能使用索引来对结果做排序。如果查询需要关联多表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。

4.8 冗余和重复索引

MySQL 允许在相同列上创建多个索引，但需要单独维护重复的索引，这会影响性能。创建了索引（A，B），再创建索引（A）就是冗余索引。将索引扩展为（A， ID）其中 ID 是主键，对于 InnoDB 来说主键列已经包含在二级索引中了，所以这个也是冗余的。

4.9 索引和锁

索引可以让查询锁定更少的行。InnoDB 只有在访问行的时候才会对其加锁，而索引能够减少 InnoDB 访问的行数，从而减少锁的数量。但只有当 InnoDB 在存储引擎层能够过滤掉所有需要的行时才有效。
即使使用了索引，InnoDB 也可能锁住一些不需要的数据。InnoDB 在二级索引上使用读锁，但访问主键索引需要写锁。这写出了使用覆盖索引的可能性，并且使得 SELECT FOR UPDATE 比 LOCK IN SHARE MODE 或非锁定查询要慢得多。

4.10 支持多种过滤条件

假设要设计一个在线约会网站，用户表有很多列，包括国家、地区、城市、性别、眼睛颜色等。

我们需要看看那些列拥有很多不同的取值，哪些列在 WHERE 子句中出现得最频繁。在有更多不同值的列上创建索引选择性会更好，因为可以让 MySQL 更有效地过滤掉不需要的行。
country 列的选择性通常不高，但可能很多查询都会用到。sex 列的选择性肯定很低，但也会在很多查询中用到。所以考虑到使用的频率，还是建议在创建不同索引组合的时候将（sex，country）列作为前缀。为什么？主要有两个理由：第一点，如前所述几乎所有的查询都会用到 sex 列。前面层提到，几乎每一个查询都会用到 sex 列。甚至会把网站设计成每次都只能按某一种性别搜索用户，即便是没有使用 sex 列，也可以通过在查询中新增 AND SEX IN (‘m’, ‘f’) 来让 MySQL 选择该索引。这样写并不会过滤任何行，但必须加上这个列的条件，MySQL 才能匹配索引的最左前缀。如果列有太多不同的值，就会让 IN( ) 列表太长，这样做就不行了。
设计索引时要考虑表上的所有选项，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询效率，那么应该想一下是否能优化原来的查询。应该同时找到优化查询和索引以找到最佳的平衡。
在设计中要尽可能的将需要做范围查询的列放在索引的后面，以便优化器使用尽可能多的索引列。前面提到的在索引中加入更多的列，并通过 IN( ) 的方式覆盖那些不在 WHERE 子句中的列。但这种技巧也不能滥用，否则可能会带来麻烦。因为每额外增加一个 IN( ) 条件，优化器需要做的组合都将以指数形式增大，最终可能会极大地降低查询性能，比如以下例子：

WHERE eye_color   IN ('brown', 'blue', 'hazel')
    AND hair_color  IN ('black', 'red', 'blonde', 'brown')
  AND sex         IN ('M', 'F')

优化器则会转化成 4 x 3 x 2 = 24 种组合，执行计划检查 WHERE 子句中所有的 24 中组合。对于 MySQL 来说，24 种组合并不是很夸张，但如果组合数达到上千个则需要特别小心。

4.11 避免多个范围条件

从 EXPLAIN 的输出很难区分 MySQL 是要查询范围值，还是查询列表值。对于范围查询条件，MySQL 无法再使用范围列后面的其他索引列了，但是对于“多个等值查询条件”则没有这个限制。

4.12 优化排序

使用文件排序对于小数据集是很快的，对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。可以创建（SEX, RATING）索引用于下面的查询：

SELECT <COLS> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 10;

这个查询使用了 ORDER BY 和 LIMIT，如果没有索引的话会很慢。
即使有索引，如果用户界面上需要翻页，并且翻页翻到比较靠后时查询也可能非常慢。比如：

SELECT <COLS> FROM profiles WHERE sex = 'M' ORDER BY rating LIMIT 100000, 10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL 需要花费大量的时间来扫描需要丢弃的数据。反范式化、预先计算和缓存可能是解决这类查询的仅有策略。优化这类索引的另一个比较好的策略是使用延迟关联。这可以减少 MySQL 扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用（sex, rating）索引进行排序和分页。

SELECT <cols> FROM profiles INNER JOIN (
    SELECT <primary key cols> FROM profiles
  WHERE x.sex = 'M' ORDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols>)

5. 维护索引和表

即使使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。主要有三个主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

5.1 找到并修复损坏的表

表损坏（corruption）对于 MyISAM 存储引擎是系统崩溃导致的。其他引擎也会由于硬件问题、MySQL 本身的缺陷或者操作系统的问题导致索引损坏。
损坏的索引会导致查询返回错误的结果或者出现一些莫须有的主键冲突等问题。可以尝试运行 CHECK TABLE 来检查是否发生了表损坏。可以使用 REPAIRE TABLE 命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，也可以通过一个不做任何操作（no-op）的 ALERT 操作来重建表。
如果 InnoDB 引擎的表出现了损坏，那么一定是发生了严重的错误，需要立刻调查一下原因。InnoDB 一般是不会出现损坏，如果发生了损坏一般是数据库的硬件问题例如内存或磁盘问题，或者是由于数据库管理员的错误例如在 MySQL 外部操作了数据文件，或是 InnoDB 的缺陷。

5.2 更新索引统计信息

MysQL 的查询优化器会通过两个 API 来了解存储引擎的索引值的分布信息，以决定如何使用索引。第一个 API 是 records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。在 MyISAM 中该接口返回精确值，对于 InnoDB 引擎则是一个估算值。
InnoDB 会在表首次打开，或者执行 ANALYZE TABLE ，亦或表的大小发生非常大的变化（大小变化超过十六分之一或者新插入了20亿行都会触发）的时候计算索引的统计信息。
InnoDB 在打开某些 INFORMATION_SCHEMA 表， 或者使用 SHOW TABLE STATUS 和 SHOW INDEX ，亦或在 MySQL 客户端开启自动补全功能时都会触发索引统计信息的更新。如果服务器上有大量的数据，这可能就是个很严重的问题，尤其是当I/O比较慢的时候。客户端或者监控程序触发索引信息采样更新时可能会导致大量的锁。只要 SHOW INDEX 查看索引统计信息，就一定会触发统计信息的更新。可以关闭 innodb_stats_on_metadata 参数来避免上面提到的问题。
一旦关闭索引统计信息的自动更新，那么就需要周期性地使用 ANALYZE TABLE 来手动跟新。否则，索引统计信息就永远不变。如果数据分布发生大的变化，可能会出现一些很糟糕的执行计划。

5.3 减少索引和数据的碎片

B-Tree 索引可能会碎片化，这会降低查询效率。碎片化的索引可能会以很差或者无序的方式存储到磁盘上。
根据设计，B-Tree 需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。然而，如果叶子页在物理分布上是**顺序且密集**的，那么查询的性能就会更好。否则，对于范围查询、索引覆盖扫描等操作来说，速度可能会降低很多倍。

数据存储的碎片化

• 行碎片（Row fragmentation）

数据行被存储成多个地方的多个片段中，即使查询只从索引中访问一行记录，行碎片也会导致性能下降。

• 行间碎片（Intra-row fragmentation）

行间碎片是指逻辑上的数据页，或者行在磁盘上不是顺序存储的。行间碎片对诸如全表扫描和聚簇索引扫描之类的操作有很大影响，因为这些操作原本能够从磁盘上顺序存储的数据中获益。

• 剩余空间碎片（Free space ）

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

对于 MyISAM 表，这三类碎片都有可能发生。对于 InnoDB 不会出现短小的行碎片，InnoDB 会移动短小的行并重写到一个片段中。可以通过 OPTIMIZE TABLE 或者导出再导入的方式来重新整理数据。对于 InnoDB 可以通过删除然后再重新创建索引的方式来消除索引的碎片化。

6.总结

MySQL 大多数情况下都会使用B-Tree索引，在合适的场景中使用索引将大大提高查询的相应时间。
选择索引和编写利用的这些索引特性的查询时，有如下三个原则始终要记住：
单行访问很慢，特别是在机械硬盘存储中（SSD的随机I/O要快很多）。使用索引可以创建位置引用以提升效率。
顺序访问数据速度快的原因

1. 顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多（特别是机械硬盘）。
2. 如果服务器能够按需要顺序读取顺序，那么就不需要额外的排序操作，并且 GROUP BY 查询也无须再做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就不需要再回表查找行。这样避免了大量的单行访问。

编写查询语句时应该尽可能选择合适的索引以避免单行查找，尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。
如何判断一个系统创建的索引是合理的呢？找到消耗最长时间的查询或者那些给服务器带来最大压力的查询，然后检查这些查询的schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是有太多的回表查询那些不在索引中的列的操作。