[Mysql] mysql前缀索引详解

1、使用前缀索引注意事项：

1)、列类型为char，varchar，binary，varbinary可以创建前缀索引；
2)、列类型为blob，text必须创建前缀索引，而且只有innodb，myisam，blackhole的存储引擎前缀索引才生效；
3)、前缀限制是以字节为单位的，但是在create table，alter table，create index语句中索引指定的前缀长度的值为：非二进制类型的（cahr varchar，text）的字符串数据量、二进制类型的（binary，varbinary，blob）的字节数量。当用多字节字符串集为非二进制字符串列创建前缀索引长度时，需要考虑这一点。
是否支持前缀索引和前缀的长度跟存储引擎有关系。例如，innodb引擎表前缀索引支持767字节长度，如果开启innodb_large_prefix参数，支持3072字节长度。myisam存储引擎表，前缀长度为1000字节。NDB引擎不支持前缀索引。

show variables like 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+

4)、以mysql5.7.17为例，如果一个指定的索引前缀长度超过最大列数据类型大小，create index时会如下处理索引：
对于非唯一索引，或者一个错误发生(严格sqlmode开启)，或者索引长度降低到最大列数据类型大小并产生一个警告信息(如果strcit sql mode没有开启)

show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+

严格模式是指将SQL_MODE变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。
对于唯一索引，不管sqlmode模式是何种，都会直接报错，因为降低索引长度会插入非唯一的条目不满足唯一性需求。

5)、创建前缀索引语句：

CREATE INDEX part_of_name ON customer (name(10));

如果列中的名字在前10个字节中通常是不同的，查询性能不应该慢于创建整个name列索引的查询。另外使用列前缀索引使索引文件更小，能够节省更多的磁盘空间并且增加插入速度。

2、下面是根据网上提供的方法做的测试，参考链接

1)、插入测试数据

create table music(
name varchar(30)
);

insert into music values('BITE'),('There for you'),('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

insert into music values ('Scarborough Fair'),('Shape of You'),('Marvin Gaye'),('Pretty Girl'),('Pretty Boy'),('Walk Away'),('YOUTH'),('Paris');

2)、查看完整列索引选择性

root@db 03:31:  [exchange_market]> select count(distinct name) / count(*) from music;
+---------------------------------+
| count(distinct name) / count(*) |
+---------------------------------+
|                          0.5556 |
+---------------------------------+

3)、查找合适的前缀索引长度

select count(distinct left(name,1))/count(*) as sel1, count(distinct left(name,2))/count(*) as sel2, count(distinct left(name,3))/count(*) as sel3, count(distinct left(name,4))/count(*) as sel4 from music;
+--------+--------+--------+--------+
| sel1   | sel2   | sel3   | sel4   |
+--------+--------+--------+--------+
| 0.3889 | 0.5000 | 0.5000 | 0.5000 |
+--------+--------+--------+--------+

4)、可以看到当前缀长度为2是已经接近完整列索引选择性,添加前缀索引

alter table music add index music_index(name(2));
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

show index from music;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| music |          1 | music_index |            1 | name        | A         |           9 |        2 | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

5)、查询索引是否被正常应用：

select * from music where name like 's%';
+------------------+
| name             |
+------------------+
| Scarborough Fair |
| Scarborough Fair |
| Shape of You     |
| Shape of You     |
+------------------+
4 rows in set (0.07 sec)

explain select * from music where name like 's%';
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | music | NULL       | range | music_index   | music_index | 11      | NULL |    4 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)