前缀索引和索引选择性

有时候为存储较长数据的列增加完整长度索引，会浪费不少空间，不是一个很好的方式，从索引的选择性考虑(索引选择性指不重复的索引值和数据的总数比值)，索引的选择性越高则查询效率越高，所以可以考虑选择列的前缀作为索引，以满足查询性能。
TEXT、BLOB类型列如果要设置索引，必须使用前缀索引，MySQL不允许索引这些类型列的完整长度。

举个栗子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select count(*) as count,first_name from employees group by first_name order by count desc limit 10;
+-------+-------------+
| count | first_name  |
+-------+-------------+
|   295 | Shahab      |
|   291 | Tetsushi    |
|   279 | Elgin       |
|   278 | Anyuan      |
|   276 | Huican      |
|   275 | Make        |
|   272 | Sreekrishna |
|   272 | Panayotis   |
|   271 | Hatem       |
|   270 | Vitali      |
+-------+-------------+

从上面数据可以看出first_name出现的次数为290-270之间，可以选取最左前缀，先选取前缀长度2，来看下结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select count(*) as count,LEFT(first_name,2) from employees group by first_name order by count desc limit 10;
+-------+--------------------+
| count | LEFT(first_name,2) |
+-------+--------------------+
|   433 | Sh                 |
|   405 | Te                 |
|   379 | El                 |
|   333 | An                 |
|   321 | Hu                 |
|   255 | Ma                 |
|   241 | Sr                 |
|   236 | Pa                 |
|   151 | Ha                 |
|   133 | Vi                 |
+-------+--------------------+

上面数据是选择长度为2的前缀，出现次数增加，所以增加前缀长度，直到近似完整列比值，但选取到多长才合适，那就是选取近似完整列的选择性比值，如下计算：

1
2
3
4
5
6
7
mysql> 
SELECT COUNT(DISTINCT first_name)/COUNT(*) from employees;
+-------------------------------------+
| COUNT(DISTINCT first_name)/COUNT(*) |
+-------------------------------------+
|                              0.0043 |
+-------------------------------------+

0.0043就是整列的选择性比值，也是前缀长度选取的近似参考值。
分别计算一下不同长度的前缀比值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select 
count(distinct left(first_name,1))/count(*) as str1,
count(distinct left(first_name,2))/count(*) as str2,
count(distinct left(first_name,3))/count(*) as str3,
count(distinct left(first_name,4))/count(*) as str4,
count(distinct left(first_name,5))/count(*) as str5, 
count(distinct left(first_name,6))/count(*) as str6, 
count(distinct left(first_name,7))/count(*) as str7 
from employees;
+--------+--------+--------+--------+--------+--------+--------+
| str1   | str2   | str3   | str4   | str5   | str6   | str7   |
+--------+--------+--------+--------+--------+--------+--------+
| 0.0001 | 0.0006 | 0.0022 | 0.0033 | 0.0038 | 0.0041 | 0.0042 |
+--------+--------+--------+--------+--------+--------+--------+

可以看出当前缀长度选取到6、7时，比值没有大幅度变化，所以针对示例表的first_name列前缀长度选取为6比较合适。

只参考平均选择性还是不够的，还需要考虑数据分布是否均匀，比如选取前缀长度为4时，Vier出现的次数不到100和其他数据出现次数差距太大，那么此时选择性就很差。

前缀索引的好处是可以使索引更小，但其缺点是MySQL中前缀索无法应用在ORDER BY和GROUP BY上，也无法用其做覆盖扫描，合理使用前缀索引可以大大提高查询效率。

多列索引

在业务处理中，可能会使用or关键字来查询，在MySQL低版本由于不能同时使用多列索引，有的优化建议尽量不要在索引列上使用or，会导致全表扫描，建议使用union或union all改写sql，如下：

1
select * from employees where emp_no=10017 OR birth_date=1958-07-06;

优化为：

1
select * from employees where emp_no=10017 UNION ALL select * from employees where birth_date=1958-07-06 AND emp_no <>10017;

在MySQL5.0或更高版本中，加入了index merge策略，MySQL可以同时扫描多个单列索引，并将结果进行合并。有三种表现：or条件的联合，AND条件的相交，组合前两种情况的联合或相交，可以通过EXPLAIN来看一下。
需要新的数据，创建新表和插入新数据：

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE users (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id INT NOT NULL DEFAULT 0,
  status TINYINT UNSIGNED NOT NULL DEFAULT 0,
  user_type TINYINT UNSIGNED NOT NULL DEFAULT 0,
  username VARCHAR(20),
  ... other columns here ...
  PRIMARY KEY(user_id),
  INDEX `parent_id` (parent_id),
  INDEX `status` (status),
  INDEX `user_type` (user_type)
) ENGINE=InnoDB;

用EXPLAIN查看执行计划可以看到，type列的类型为index_merge，Extra列Using intersect(user_type,status,parent_id)的嵌套操作。

1
2
3
4
5
6
7
8
9
10
11
12
mysql>EXPLAIN SELECT user_id FROM user WHERE user_type=2 AND status=1 AND parent_id=0 AND user_id > 2938575 ORDER BY user_id LIMIT 1;
*************************** 1. row ***************************
                       id: 1
              select_type: SIMPLE
                    table: users
                     type: index_merge
            possible_keys: PRIMARY,parent_id,status,user_type
                      key: user_type,status,parent_id
                  key_len: 2,2,4
                      ref: NULL
                     rows: 5108
                    Extra: Using intersect(user_type,status,parent_id); Using where; Using index; Using filesort

虽然MySQL的index merge策略可以优化一些复杂查询，但出现这种情况也说明了索引设计的并不好，可以尝试优化表结构。

还有条优化建议是”尽量在WHERE条件中的列都建立索引“，那是不是按照该优化建议为WHERE条件中的每个列都建立索引，就可以解决查询性能问题？显然不是，在多条件查询时，索引列的顺序也是需要注意的。

索引的顺序

1
select count(*) from employees.employees where first_name='Khatoun' and last_name='Tyugu';

上面是first_name=’Khatoun’和last_name=’Tyugu’的组合条件查询，如何对这两个条件列设计索引顺序，我们先单独查询一下各自的记录数：

1
2
3
4
5
6
mysql> select count(*) from employees.employees where first_name='Khatoun';
+----------+
| count(*) |
+----------+
|      247 |
+----------+

1
2
3
4
5
6
mysql> select count(*) from employees.employees where last_name='Tyugu';
+----------+
| count(*) |
+----------+
|       88 |
+----------+

可以看到，last_name=’Tyugu’的记录要少，那是不是一定要把last_name列作为第一索引？按照上一节内容，从选择性上来看一下：

1
2
3
4
5
6
7
mysql> select count(distinct first_name)/count(*) as fir,count(distinct last_name)/count(*) as las,count(*) from employees.employees;
+--------+--------+----------+
| fir    | las    | count(*) |
+--------+--------+----------+
| 0.0013 | 0.0055 |   300024 |
+--------+--------+----------+
1 row in set (0.33 sec)

根据计算得出，last_name的选择性比较高，所以将其作为索引列的第一列，first_name列作为索引的第二列：

1
mysql> ALTER TABLE employees.employees ADD KEY(last_name,first_name);

一些简单的查询可以根据选择性来确定哪列作为索引以及索引的顺序，但一些复杂的查询，还要考虑WHERE中排序、分组、范围查询等因素，综合考虑后，设计出最符合查询需求的索引。