索引

独立的列

我们经常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有的索引。
如果查询中的列不是独立的，则 MySQL 就不会使用索引。"独立的列"是指索引列不能是表达式的一部分，也不能是函数的参数

例如，下面这个查询无法使用 actor_id 列的索引:

1	`SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;`

凭肉眼很容易看出 WHERE 中的表达式其实等价于 actor_id = 4，但是 MySQL 无法自动解析这个方程式。这完全是用户行为。
我们应该养成简化 WHERE 条件的习惯，始终将索引列单独放在比较符号的一侧

下面是另一个常见的错误:

1	`SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;`

前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
索引的选择性是指，不重复的索引值(也称为基数，cardinality)和数据表的记录总数(#T)的比值，范围从 1 / #T 都 1 之间。
索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

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

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节约空间)。
前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

首先，我们找到最常见的城市列表:

前缀索引sql1

注意到，上面每个值都出现了 45 - 65 次。现在查找到最频繁出现的城市前缀，先从 3 个前缀字母开始:

3个前缀字母

每个前缀都比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。
然后我们增加前缀长度，直到这个前缀的选择性接近完整列的选择性。经过实验后发现前缀长度为 7 时比较合适:

7个前缀字母

计算合适的前缀长度的另外一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性。

计算完整列的选择性

通常来说(尽管也有例外情况)，这个例子中如果前缀的选择性能够接近 0.031，基本上就可用了。
可以在一个查询中针对不同前缀长度进行计算，这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性:

统计选择性

查询显示当前缀长度到达 7 的时候，再增加前缀长度，选择性提升的幅度已经很小了

只看平均选择性是不够的，也有例外的情况，需要考虑最坏情况下的选择性。
平均选择性会让你认为前缀长度为 4 或者 5 的索引已经足够了，但如果数据分布很不均匀，可能就会有陷阱。
如果观察前缀为 4 的最长出现城市的次数，可以看到明显不均匀:

长度为4的前缀不均匀

如果前缀是 4 个字节，则最长出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性更低。
如果有比这个随机生成的示例更真实的数据，就更有可能看到这种现象。
例如在真实的城市名上建一个长度为 4 的前缀索引，对于以 "San" 和 "New" 开头的城市的选择性就会非常糟糕，因为很多城市都以这两个词开头

在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引:

1	`ALTER TABLE sakila.city_demo ADD KEY (city(7));`

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

一个常见的场景是针对很长的十六进制唯一 ID 使用前缀索引。
例如使用 vBulletin 或者其他基于 MySQL 的应用在存储网站的会话(SESSION)时，需要在一个很长的十六进制字符串上创建索引。
此时如果采用长度为 8 的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

有时候后缀索引(suffix index)也有用途(例如，找到某个域名的所有电子邮件地址)。
MySQL 原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。

选择合适的索引列顺序

我们遇到的最容易引起困惑的问题就是索引列的顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要

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

对于如何选择索引的列顺序有一个经验法则: 将选择性最高的列放到索引最前列。这个建议有用吗？
在某些场景可能有帮助，但通常不如避免随机 IO 和排序那么重要，考虑问题需要更全面(场景不同则选择不同，没有一个放之四海而皆准的法则。这里只是说明，这个经验法则可能没有你想象的重要)

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找。
在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。
然而，性能不只是依赖于所有索引列的选择性(整体基数)，也和查询条件的具体值有关，也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。
可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高

以下面的查询为例:

1	`SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;`

是应该创建一个(staff_id, customer_id) 索引还是应该颠倒一下顺序？
可以跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。
先用下面的查询预测一下，看看各个 WHERE 条件的分支对应的数据基数有多大:

SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G

SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30

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

1
2
3

SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G

SUM(staff_id = 2): 17

这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。
如果按上述办法优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期

如果是从诸如 pt-query-digest 这样的工具的报告中提取 "最差" 查询，那么再按上述办法选定的索引顺序往往是非常高效的。
如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数的选择性，而不是某个具体查询:

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment\G

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

customer_id 的选择性更高，所以答案是将其作为索引列的第一列:

1	`ALTER TABLE payment ADD KEY(customer_id, staff_id)`

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。
例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为 "guset"，在记录用户行为的会话(session)表和其他记录用户活动的表中 "guest" 就成为了一个特殊用户 ID。
一旦查询涉及这个用户，那么和对于正常用户的查询就大不同了，因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。
一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。
这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

这实际上是一个非常典型的问题，任何的异常用户，不仅仅是那些用于管理应用的设计糟糕的账号会有同样的问题；
那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的系统账号同样的问题。

下面是一个我们遇到过的真实案例，在一个用户分享购买商品和购买经验的论坛上，这个特殊表上的查询运行得非常慢:

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) AND (userId = 1288826) AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC

这个查询看似没有建立合适的索引，所以客户咨询我们是否可以优化。EXPLAIN 的结果如下:

id: 1
select_type: SIMPLE
table: Message
type: ref
key: ix_groupId_userId
key_len: 18
ref: const,const
rows: 1251162
Extra: Using where

MySQL 为这个查询选择了索引(groupId, userId)，如果不考虑列的基数，这看起来是一个非常合理的选择。
但如果考虑一下 userID 和 groupID 条件匹配的行数，可能就会有不同的想法了:

SELECT COUNT(*), SUM(groupId = 10137),
SUM(userId = 1288826), SUM(anonymous = 0)
FROM Message\G

count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934

从上面的结果来看符合组(groupId)条件几乎满足表中的所有行，符合用户(userId)条件的有 130 万条记录 -- 也就是说索引基本上没什么用。
因为这些数据是从其他应用中迁移过来的，迁移的时候把所有的信息都赋予了管理员组的用户。
这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询。

从这个小案例可以看到经验法则和推论在多数情况是有用的，但要注意不要假设平均情况下的性能也能代表特殊情况下的性能，特殊情况可能会摧毁整个应用的性能。

最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定要记住别忘了 WHERE 子句中的排序、分组和范围条件等其他因素，这些因素可能对查询的性能造成非常大的影响。

覆盖索引

通常大家都会根据查询的 WHERE 条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是 WHERE 条件部分。
索引确实是一种查找数据的高效方式，但是 MySQL 也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。
如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为“覆盖索引”

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表，会带来多少好处:

索引条目通常远小于数据行大小，所以如果只需要读取索引，那 MySQL 就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。
覆盖索引对于 I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中(这对于 MyISAM 尤其正确，因为 MyISAM 能压缩索引以变得更小)
因为索引是按照列值顺序存储的(至少在单个页内是如此)，所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。
对于某些存储引擎，例如 MyISAM 和 Percona XtraDB，甚至可以通过 OPTIMIZE 命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问
一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景
由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子结点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。
另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引

当发起一个被索引覆盖的查询(也叫做索引覆盖查询)时，在 EXPLAIN 的 Extra 列可以看到 "Using index" 的信息。
例如，表 sakila.inventory 有一个多列索引(store_id, film_id)。MySQL 如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示:

EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

id: 1
select_type: SIMPLE
table: inventory
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4673
Extra: Using index

索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。
假设索引覆盖了 WHERE 条件中的字段，但不是整个查询涉及的字段。如果条件为假(false)，MySQL 5.5 和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉