《高性能 MySQL》笔记——第5章 创建高性能的索引（二）

声明：

本博客是本人在学习《高性能 MySQL》后整理的笔记，旨在方便复习和回顾，并非用作商业用途。

本博客已标明出处，如有侵权请告知，马上删除。

5.3 高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。现在我们一起来看看如何真正地发挥这些索引的优势。

高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断地学习。接下来的几个小节将帮助读者理解如何高效地使用索引。

5.3.1 独立的列

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

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

mysql> SELECT actor_id FROM actor WHERE actor_id + 1 = 5;

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

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

SELECT date_col FROM actor WHERE TO_DAYS(CURRENT_DATE) – TO_DAYS(date_col) <= 10;

5.3.2 前缀索引和索引的唯一性

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

一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？

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

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

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

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

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。在示例数据 Sakila 没有合适的例子，所以我们从表 city 生成一个示例表 city_demo，这样就有足够的数据用来演示：

mysql> CREATE TABLE city_demo (city VARCHAR(50) NOT NULL);
Query OK, 0 rows affected

mysql> INSERT INTO city_demo(city) SELECT city from city;
Query OK, 600 rows affected
Records: 600  Duplicates: 0  Warnings: 0

重复执行下面的 SQL 五次：

mysql> INSERT INTO city_demo(city) SELECT city FROM city_demo;
Query OK, 600 rows affected (0.03 sec)
Records: 600  Duplicates: 0  Warnings: 0

执行下面 SQL 随机分布数据：

mysql> UPDATE city_demo SET city = (SELECT city FROM city ORDER BY RAND() limit 1);
Query OK, 19171 rows affected
Rows matched: 19200  Changed: 19171  Warnings: 0

现在我们有了示例数据集。数据分布当然不是真实的分布，因为我们使用了 RAND()，所以不同的人的结果各不相同，但这个并不重要。首先，我们找到最常见的城市列表：

mysql> SELECT COUNT(*) as cnt, city FROM city_demo GROUP BY city ORDER BY cnt DESC LIMIT 10;
+-----+-----------+
| cnt | city      |
+-----+-----------+
|  72 | London    |
|  48 | Baiyin    |
|  48 | Jinzhou   |
|  48 | Nador     |
|  47 | Isesaki   |
|  47 | Elista    |
|  47 | Garland   |
|  47 | Linz      |
|  46 | Compton   |
|  45 | Shikarpur |
+-----+-----------+
10 rows in set

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

mysql> SELECT COUNT(*) as cnt, LEFT(city,3) AS</