声明:
本博客是本人在学习《高性能 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 pref FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT