索引选择性与前缀索引

发表于 | 分类于

善用索引更要慎用索引

万能药？

在一般情况下，建立索引可以加快查询速度，那么是不是只要是查询语句需要，就在相应字段建上索引？

当然不是。索引虽然加快了查询速度，但是你得为此付出代价：

  • 索引文件会消耗存储空间
  • 索引会加重插入、删除、修改记录的负担
  • MySQL在运行时也要消耗资源维护索引

因此，索引并不是越多越好，一般两种情况下不建议建索引：

  • 表记录比较少，没必要建索引，让查询做全表扫描就好了。
  • 索引的选择性较低。

索引的选择性

所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：

Index Selectivity = Cardinality / #T

显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

看一个例子：

1
2
3
4
5
6
7
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的选择性不足0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。

前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

假设employees表只有一个索引，那么如果我们想按名字搜索一个人，就只能全表扫描了：

1
2
3
4
5
6
7
EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果频繁按名字搜索员工，这样显然效率很低，因此我们可以考虑建索引。有两种选择，看下两个索引的选择性：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;

+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+


SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

第一种显然选择性太低，第二种虽然选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如，看看其选择性：

1
2
3
4
5
6
7
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

选择性还不错，但离0.9313还是有点距离，那么把last_name前缀加到4：

1
2
3
4
5
6
7
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+

这时选择性已经很理想了，而这个索引的长度只有18，比短了接近一半，我们把这个前缀索引 建上：

1
2
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

此时再执行一遍按名字查询，比较分析一下与建索引前的结果：

1
2
3
4
5
6
7
8
SHOW PROFILES;

+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

性能的提升是显著的，查询速度提高了120多倍。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。