MySQL索引的那些事儿~

前缀索引

MySQL有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，下面举例说明如何选取索引前缀。
–查找最常见的城市列表，发现每个值都出现45-65次

mysql> select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
+-----+--------------------+
| cnt | city               |
+-----+--------------------+
|  61 | London             |
|  49 | Mandaluyong        |
|  49 | Citt del Vaticano  |
|  47 | Jelets             |
|  47 | La Romana          |
|  47 | Mysore             |
|  46 | Ezeiza             |
|  46 | Valle de la Pascua |
|  46 | Brindisi           |
|  45 | Linz               |
+-----+--------------------+

mysql> select count(distinct left(city,3))/count(*) as sel3,
    -> count(distinct left(city,4))/count(*) as sel4,
    -> count(distinct left(city,5))/count(*) as sel5,
    -> count(distinct left(city,6))/count(*) as sel6,
    -> count(distinct left(city,7))/count(*) as sel7,
    -> count(distinct left(city,8))/count(*) as sel8
    -> from citydemo;
+--------+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   | sel8   |
+--------+--------+--------+--------+--------+--------+
| 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 | 0.0310 |
+--------+--------+--------+--------+--------+--------+

此时发现取前7个字段和取前8个字段结果已经没有差异了，所以可以创建前缀索引

alter table citydemo add key(city(7));

注：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：MySQL无法使用前缀索引做order by 和 group by

索引扫描排序

MySQL有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描
已知rental表在rental_date,inventory_id,customer_id上有rental_date的索引
只有当索引的列顺序和order by子句的顺序完全一致,mysql才能够完全使用索引来对结果进行排序

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customer_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+

导致不使用索引进行排序的情况有：
引用了不在索引中的列：

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,staff_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+

不满足最左匹配原则：

mysql> explain select rental_id,staff_id from rental order by customer_id,inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 16008 |   100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

通过前面索引列定位为范围：

mysql> explain select rental_id,staff_id from rental where rental_date>'2005-05-25' order by rental_date,inventory_id;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | rental | NULL       | ALL  | rental_date   | NULL | NULL    | NULL | 16008 |    50.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+

查询使用了两种不同的排序方向

mysql> explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id desc,customer_id;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+
|  1 | SIMPLE      | rental | NULL       | ref  | rental_date   | rental_date | 5       | const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+---------------------------------------+

索引失效

列举会导致索引失效的几种情况

• MySQL预估使用全表扫描要比使用索引快
• 组合索引，查询条件不满足最左匹配原则
• 采用like左模糊查询
• 强制类型转换(这点容易忽视)

mysql> explain select * from user where phone = '13333333333';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_1         | idx_1 | 36      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

mysql> explain select * from user where phone = 13333333333;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_1         | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+