关于索引的设计与使用（第20期索引设计前缀索引）
关于索引的设计与使用（第20期索引设计前缀索引）MySQL 基本上大部分存储引擎都支持前缀索引，目前只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/VARCHAR、TEXT/BLOB、BINARY/varBINARY。前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？大致有以下 3 种方法：能否不拆分字段，又能避免太多重复值的冗余？我们今天讨论一下前缀索引。
这里主要介绍 MySQL 的前缀索引。从名字上来看，前缀索引就是指索引的前缀，当然这个索引的存储结构不能是 HASH，HASH 不支持前缀索引。
先看下面这两行示例数据：
你是中国人吗？是的是的是的是的是的是的是的是的是的是的
确定是中国人？是的是的是的是的是的是的是的是的是的是的
这两行数据有一个共同的特点就是前面几个字符不同，后面的所有字符内容都一样。那面对这样的数据，我们该如何建立索引呢？
大致有以下 3 种方法：
- 拿整个串的数据来做索引 这种方法来的最简单直观，但是会造成索引空间极大的浪费。重复值太多，进而索引中无用数据太多，无论写入或者读取都产生极大资源消耗。
- 将字符拆开，将一部分做索引 把数据前面几个字符和剩余的部分字符分拆为两个字段 r1_prefix，r1_other，针对字段 r1_prefix 建立索引。如果排除掉表结构更改这块影响，那这种方法无疑是最好的。
- 把前面 6 个字符截取出来的子串做一个索引
能否不拆分字段，又能避免太多重复值的冗余？我们今天讨论一下前缀索引。
前缀索引前缀索引就是基于原始索引字段，截取前面指定的字符个数或者字节数来做的索引。
MySQL 基本上大部分存储引擎都支持前缀索引，目前只有字符类型或者二进制类型的字段可以建立前缀索引。比如：CHAR/VARCHAR、TEXT/BLOB、BINARY/varBINARY。
举个简单例子，表 t1 有两个字段，针对字段 r1 有两个索引，一个是基于字段 r1 的普通二级索引，另外一个是基于字段r1的前缀索引。
10px;">`<localhost|mysql>showcreatetablet1\G
***************************1\.row***************************
Table:t1
CreateTable:CREATETABLE`t1`(
`id`bigintunsignedNOTNULLAUTO_INCREMENT
`r1`varchar(300)DEFAULTNULL
PRIMARYKEY(`id`)
KEY`idx_r1`(`r1`)
KEY`idx_r1_p`(`r1`(6))
)ENGINE=InnoDBAUTO_INCREMENT=32755DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci
1rowinset(0.00sec)
<localhost|mysql>SELECTCOUNT(*)fromt1;
----------
|count(*)|
----------
|24576|
----------
1rowinset(0.04sec)
下面分别是表 t1 只有 idx_r1 和 idx_r1_p 的表空间文件大小，很明显，前缀索引非常有优势。
#idx_r1
root@debian-ytt1:/var/lib/mysql/3306/ytt#du-sh
26M.
#idx_r1_p
root@debian-ytt1:/var/lib/myzsql/3306/ytt#du-sh
20M.
接下来查询以 sample 关键词开头的记录条数。
<localhost|mysql>selectcount(*)fromt1wherer1like'sample%';
----------
|count(*)|
----------
|4|
----------
1rowinset(0.00sec)
对应的执行计划。可以看出，MySQL 选择了体积较小的前缀索引 idx_r1_p。
<localhost|mysql>explainselectcount(*)fromt1wherer1like'sample%'\G
***************************1\.row***************************
id:1
select_type:SIMPLE
table:t1
partitions:NULL
type:range
possible_keys:idx_r1 idx_r1_p
key:idx_r1_p
key_len:27
ref:NULL
rows:4
filtered:100.00
Extra:Usingwhere;Usingindex
1rowinset 1warning(0.00sec)`</pre>
那这里可以看到，为何会选择用 r1(6) 来做前缀，而不是 r1(5) 或者其他的？下面的 SQL 语句列出了所有基于关键词 sample 的可选值，SQL 1 - SQL 6 基于关键词的前缀长度不同。
SQL 1 - SQL 6 的前缀长度依次为 6 - 1 个字符。
#SQL1
selectcount(*)fromt1wherer1like'sample%'；
#SQL2
selectcount(*)fromt1wherer1like'sampl%'；
#SQL3
selectcount(*)fromt1wherer1like'samp%'；
#SQL4
selectcount(*)fromt1wherer1like'sam%'；
#SQL5
selectcount(*)fromt1wherer1like'sa%'；
#SQL6
selectcount(*)fromt1wherer1like's%';
那可否设计一个合适的前缀索引来让以上 6 条 SQL 的执行都能够达到最优呢？答案是肯定的。前提是计算出在当前记录下，被索引字段每个前缀对比整个字段的分散比率值，也叫前缀索引的可选择性（索引字段的可选性，我有另外一篇文章专门介绍），这个值选择的合适与否，直接影响到前缀索引的运行效率。
以下把字段 r1 可选择性查出来，结果为 0.0971，之后只需要计算每个前缀对应的数据分散比率是否和这个值相等或者无限接近即可。
<localhost|mysql>SELECTTRUNCATE(COUNT(DISTINCTr1)/COUNT(r1) 4)'taotal_pct'FROMt1;
------------
|taotal_pct|
------------
|0.0971|
------------
1rowinset(0.13sec)
为了找到最合适的索引前缀长度， 我写了一个简单的函数，用来依次返回字段 r1 每个前缀长度的数据分散比率。函数 func_calc_prefix_length 返回一个 JSON 对象，对象的 KEY 和 VALUE 分别记录了前缀长度以及对应的分散比率。
DELIMITER$
USE`ytt`$
DROPFUNCTIONIFEXISTS`func_calc_prefix_length`$
CREATEDEFINER=`ytt`@`%`FUNCTION`func_calc_prefix_length`()RETURNSJSON
BEGIN
DECLAREv_total_pctDECIMAL(20 4);
DECLAREv_prefix_pctDECIMAL(20 4);
DECLAREv_resultJSONDEFAULT'[]';
DECLAREiTINYINTDEFAULT1;
SELECTTRUNCATE(COUNT(DISTINCTr1)/COUNT(r1) 4)INTOv_total_pctFROMt1;
label1:LOOP
SELECTTRUNCATE(COUNT(DISTINCTLEFT(r1 i))/COUNT(r1) 4)INTOv_prefix_pctFROMt1;
SETv_result=JSON_ARRAY_APPEND(v_result '/pre> JSON_OBJECT(i v_prefix_pct));
IFv_prefix_pct>=v_total_pctTHEN
LEAVElabel1;
ENDIF;
SETi=i 1;
ENDLOOP;
RETURNv_result;
END$
DELIMITER;
调用下这个函数：
<localhost|mysql>SELECTfunc_calc_prefix_length()ASprefix_length\G
***************************1\.row***************************
prefix_length:[{"1":0.0003} {"2":0.0005} {"3":0.0008} {"4":0.0013} {"5":0.0093} {"6":0.0971}]
1rowinset(0.32sec)
函数结果汇总了每个不同的前缀对应的数据分散比率。由此数据可以看到，在当前数据的分布范围内，前缀为 6 是最合适的，6 最接近于字段 r1 的全部数据分布比率。所以以上 SQL 1 - SQL 6 都可以基于前缀为 6 的索引很好的运行。
执行下 SQL 6，
<localhost|mysql>selectcount(*)fromt1wherer1like's%';
----------
|count(*)|
----------
|29|
----------
1rowinset(0.00sec)
那前缀索引有没有可能用于如下 SQL？
#SQL7
selectcount(*)fromt2wherer1like'%sample'；
表 t2 和表 t1 结构一致，数据分布有些不同。针对 SQL 7 这样的查询，过滤条件左边是通配符 %，没有具体的值，此时无法使用索引，SQL 7 只能全表扫描，查询时间 0.1 秒。
<localhost|mysql>selectcount(*)fromt2wherer1like'%sample';
----------
|count(*)|
----------
|4|
----------
1rowinset(0.10sec)
查看下 sample 为后缀的表记录样例。
<localhost|mysql>select*fromt2wherer1like'%sample'limit1\G
***************************1\.row***************************
id:14
r1:mysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmysqlmyssample
1rowinset(0.13sec)
针对此种情形，有两种优化方法：
第一，可以把数据按照后缀做一个拆分，后缀部分单独为一个字段，然后给这个字段加一个索引。除了要加字段，此方法很完美~建一个表 t3，把表 t2 的数据导进去。
CREATETABLE`t3`(
`id`bigintunsignedNOTNULLAUTO_INCREMENT
`r1`varchar(300)DEFAULTNULL
`suffix_r1`varchar(6)DEFAULTNULL
PRIMARYKEY(`id`)
KEY`idx_suffix_r1`(`suffix_r1`)
)ENGINE=InnoDB
<localhost|mysql>insertintot3selectid r1 right(r1 6)fromt2;
QueryOK 24576rowsaffected(19.05sec)
Records:24576Duplicates:0Warnings:0
再次执行 SQL 7，查询瞬间出来结果。
<localhost|mysql>selectcount(*)fromt3wheresuffix_r1='sample';
----------
|count(*)|
----------
|4|
----------
1rowinset(0.00sec)
第二，可以把数据反转过来后建立前缀索引查询记录。对表 t2 克隆一张表 t4。
<localhost|mysql>insertintot4selectid reverse(r1)fromt2;
QueryOK 24576rowsaffected(5.25sec)
Records:24576Duplicates:0Warnings:0
查询关键词进行反转查询，
<localhost|mysql>selectcount(*)fromt4wherer1like'elpmas%';
----------
|count(*)|
----------
|4|
----------
1rowinset(0.00sec)
再看下查询计划，走了前缀索引。不过这样的缺点是查询记录的时候需要在 SQL 层处理记录数据，加上反转函数。
<localhost|mysql>explainselectcount(*)fromt4wherer1like'elpmas%'\G
***************************1.row***************************
id:1
select_type:SIMPLE
table:t4
partitions:NULL
type:range
possible_keys:idx_r1_p
key:idx_r1_p
key_len:27
ref:NULL
rows:4
filtered:100.00
Extra:Usingwhere;Usingindex
1rowinset 1warning(0.00sec)
总结
今天大致讲了下 MySQL 前缀索引的定义规则以及简单使用场景，欢迎大家批评指正。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
