MySQL前缀索引和索引选择性

1、我们怎么合理使用前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

但这种做法会带来新的问题，降低索引的选择性。

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

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长以便节约空间。前缀的选择，要可以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的"基数"应该接近于完整的列的"基数"。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

下面的示例采用了mysql官方提供的示例数据库：示例数据库下载地址

将示例数据库导入(这里是Windows下的数据库)

C:\Users\MBJ20180710S\Downloads\web\mysql-8.0.17-winx64\bin>mysql -u root -p <  C:\Users\MBJ20180710S\Downloads\sakila-db\sakila-schema.sql
Enter password: *****

C:\Users\MBJ20180710S\Downloads\web\mysql-8.0.17-winx64\bin>mysql -u root -p < C:\Users\MBJ20180710S\Downloads\sakila-db\sakila-data.sql
Enter password: *****

在示例数据库sakila中并没有合适的例子，所以我们从表city中生成一个示例表：

mysql> use sakila;
Database changed

mysql>  create table city_demo (city varchar(50) not null);
Query OK, 0 rows affected (0.06 sec)

mysql>  insert into city_demo (city) select city from city;
Query OK, 600 rows affected (0.01 sec)
Records: 600  Duplicates: 0  Warnings: 0

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

mysql> update city_demo set city = ( select city from city order by rand() limit 1);
Query OK, 1196 rows affected (1.20 sec)
Rows matched: 1200  Changed: 1196  Warnings: 0

因为这里使用了rand（）函数，所以你我的数据会有所不同，但这并不影响我们理解下面的实验，查看一下最常见的城市列表：

mysql> select count(*) as cnt,city from city_demo group by city order by cnt desc limit 10;
+-----+-------------+
| cnt | city        |
+-----+-------------+
|   9 | NDjamna     |
|   8 | Okayama     |
|   6 | Funafuti    |
|   6 | Clarksville |
|   6 | Touliu      |
|   6 | Batna       |
|   6 | Laiwu       |
|   6 | Czestochowa |
|   6 | Basel       |
|   5 | Okinawa     |
+-----+-------------+
10 rows in set (0.00 sec)

上面每个城市的值都出现了5-9次。现在查找到频繁出现的城市前缀，先从4个前缀字母开始，然后增加，5个，6个：

mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  12 | Sant |
|   9 | NDja |
|   8 | Okay |
|   8 | Toul |
|   7 | Iwak |
|   7 | Sala |
|   6 | Base |
|   6 | Funa |
|   6 | Clar |
|   6 | Laiw |
+-----+------+
10 rows in set (0.00 sec)

mysql> select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+-------+
| cnt | pref  |
+-----+-------+
|   9 | NDjam |
|   8 | Okaya |
|   6 | Touli |
|   6 | Funaf |
|   6 | Clark |
|   6 | South |
|   6 | Batna |
|   6 | Laiwu |
|   6 | Czest |
|   6 | Basel |
+-----+-------+
10 rows in set (0.00 sec)

mysql> select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+--------+
| cnt | pref   |
+-----+--------+
|   9 | NDjamn |
|   8 | Okayam |
|   6 | Funafu |
|   6 | Clarks |
|   6 | Touliu |
|   6 | Batna  |
|   6 | Laiwu  |
|   6 | Czesto |
|   6 | Basel  |
|   5 | Okinaw |
+-----+--------+
10 rows in set (0.00 sec)

通过上面改变不同前缀长度发现，当前缀长度增加时，这个前缀的选择性就接近完整列的选择性了。前缀长度为6时，与完整列选择性是一样的。所以我们这里选择前缀长度为6是合适的。
当然还有另外更方便的选择合适前缀长度的方法，那就是计算完整列的选择性，并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性：

mysql> select count(distinct city) / count(*) from city_demo;
+---------------------------------+
| count(distinct city) / count(*) |
+---------------------------------+
|                          0.4400 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select count(distinct left(city,4))/count(*) as a1,
    -> count(distinct left(city,5))/count(*) as a2,
    -> count(distinct left(city,6))/count(*) as a3
    -> from city_demo;
+--------+--------+--------+
| a1     | a2     | a3     |
+--------+--------+--------+
| 0.4183 | 0.4308 | 0.4367 |
+--------+--------+--------+
1 row in set (0.00 sec)

当索引前缀为6时的基数是0.4367，接近完整列选择性0.4400。

找到了合适的前缀长度，下面创建前缀索引：

mysql> alter table city_demo add key (city(6));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index in city_demo\G;
*************************** 1. row ***************************
        Table: city_demo
   Non_unique: 1
     Key_name: city
 Seq_in_index: 1
  Column_name: city
    Collation: A
  Cardinality: 524
     Sub_part: 6
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

mysql> explain select count(*) from city_demo where city='Clarksville';
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city_demo | NULL       | ref  | city          | city | 20      | const |    6 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。