前缀索引和索引长度的取舍

时间：10-11来源：作者：点击数：21

1 背景

有时候我们需要在字符类型的字段上建设索引，但是如果该字段的值都普遍比较大的话，会让索引变得大而且慢。

根据我们之前的了解，每个磁盘块（disk）存储的内容是有限的（InnoDB存储引擎中页的大小为16KB），如果一个页中可以存储的索引记录越多，那么查询效率就会提高，因为查找次数、查找深度会变少。

但是索引整个字符列会让我们索引内容特别大，会导致单个页存储的索引记录减少，就需要更多的页去存储，B+Tree的树深度变深，查询效率大幅降低。

比较有效的一种办法是指定索引的字段长度，比如可以索引开始的部分字符，这样可以大大节约索引空间（每个页），从而提高索引效率。

# enp_name 如果值普遍太长会导致索引空间爆棚
create index idx_emp_empname on emp(emp_name)

# 可能合适的做法
create index idx_emp_empname on emp(emp_name(5))

2 关于索引选择性

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

以下图为例，字段emp_name的索引长度设置为4。如果不设置长度，一旦有大量长字符串元素，会占据大量的空间，而单个Disk的空间是有限的。

就会导致树的层级很高，搜索的IO次数变多，索引性能降低。

★说明：#T 指数据表的记录总数

★ 参考书籍《高性能MySQL》的5.3.2小节的第3段：一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

3 如何计算索引的选择性

3.1 分析

提供一个具有500w数据的雇员表emp，准备在雇员姓名 emp_name 字段在做索引。

emp_name有些值还是挺长的，如果全部拿来做索引，那势必导致索引表很庞大。我们先来检索下这个emp_name，看看有没有什么特征。

1  mysql> select distinct emp_name from emp;
2  +----------+
3  | emp_name |
4  +----------+
5  |  ali_user |
6  |  brandy_user |
7  |  cancys_user |
8  |  davide_user |
9  |  elasne_user |
10 |  finest_user |
11 |  ......... |
12 |  gagnannan_user |
13 |  halande_user |
14 |  ivil_user |
15 |  kikol_user |
16 |  Cecilia_user |
17 |  Abigail_user |
18 |  Blanche_user |
19 |  Elizabeth_user |
20 |  ....... |
21 +----------+
22 4999850 row in set

从上面的数据进行分析，可以得到以下结论：

索引长度肯定不能太短（比如2或者3），前6个字符的区分度非常高，如果选择2或者3，会扫出大量的数据，增加计算成本。
不能不设置索引的长度，后缀 _user 是一致的，没有任何的区分度优势，反而会占用很多无效的存储空间，增加 I/O次数。
目前看，最合适的可能是6或者7，前几个字符的区分度很高，这只是推断，需要用算法判定。

3.2 前缀索引的长度的判断方法

判断的算法：

select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;

可以套入emp表进行计算，如下：

SELECT
    count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
    count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
    count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
    count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
    count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
FROM
    emp

3.3 添加前缀索引

下面是添加带有索引长度的前缀索引语法：

ALTER TABLE t_name ADD KEY (c_name[(lenth)]);

测试emp_name最合适的长度，因为empname的长度基本维持在6个字符左右，少数量超过6长度，所以指定empname索引长度时6是最建议的

mysql>  SELECT
     count(DISTINCT LEFT(emp_name, 3)) / count(*) AS sel3,
     count(DISTINCT LEFT(emp_name, 4)) / count(*) AS sel4,
     count(DISTINCT LEFT(emp_name, 5)) / count(*) AS sel5,
     count(DISTINCT LEFT(emp_name, 6)) / count(*) AS sel6,
     count(DISTINCT LEFT(emp_name, 7)) / count(*) AS sel7
 FROM
     emp;
+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   |
+--------+--------+--------+--------+--------+
| 0.0012 | 0.0076 | 0.0400 | 0.1713 | 0.1713 |
+--------+--------+--------+--------+--------+
1 row in set

我们可以使用不同的长度来测试检索效率

当长度为2的时候，匹配度低于 0.0012，检索效率自然比较慢

mysql> create index idx_emp_empname on emp(emp_name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  (1.793 sec)

当长度为6的时候，匹配度低于 0.1713，检索效率就比较高

mysql> create index idx_emp_empname on emp(emp_name(6));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from emp where emp_name='LsHfFJA';
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| id      | empno   | emp_name | job     | mgr | hiredate            | sal  | comn | depno |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
| 4582071 | 4582071 | LsHfFJA | SALEMAN |   1 | 2021-01-23 16:46:03 | 2000 | 400  |   106 |
+---------+---------+---------+---------+-----+---------------------+------+------+-------+
1 row in set  （0.003 sec）