Mysql联合索引最左匹配原则

2019-01-17 本文已影响142人王小杰at2019

[toc]

为什么使用联合索引

以联合索引(a,b,c)为例

建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。
覆盖(动词)索引。同样的有联合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通过遍历索引取得数据，而无需读表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select * from table where a = 1 and b =2 and c = 3,假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w 条数据，然后再回表从100w条数据中找到符合b=2 and c= 3的数据，然后再排序，再分页；如果是复合索引，通过索引筛选出1000w *10% *10% *10%=1w，然后再排序、分页，哪个更高效，一眼便知

1. 示例表结构

CREATE TABLE `test_user` (
  `name`     varchar(20) DEFAULT NULL,
  `province` int(11)     DEFAULT NULL,
  `sex`      varchar(20) DEFAULT NULL,
  `birthday` int(11)     DEFAULT NULL,
  `phone`    double      DEFAULT NULL
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

2. 执行计划说明

以下全部详细解析explain各个属性含义：

+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+

各属性含义：

id: 查询的序列号
select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询
- SIMPLE：查询中不包含子查询或者UNION
- 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
- 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
table: 输出的行所引用的表
type: 访问类型
- ALL: 扫描全表
- index: 扫描全部索引树
- range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
- ref: 使用非唯一索引或非唯一索引前缀进行的查找
- （eq_ref和const的区别：）
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
- const, system: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。system是const类型的特例，当查询- 的表只有一行的情况下，使用system。
- NULL: 不用访问表或者索引，直接就能得到结果，如select 1 from test where 1
key: 显示MySQL实际决定使用的索引。如果没有索引被选择，是NULL
key_len: 使用到索引字段的长度
注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref: 显示哪个字段或常数与key一起被使用
rows: 这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的
Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。
Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。

3. 执行计划分析

mysql> explain select *
    ->         from test_user
    ->         where name = '张三';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 63      | const |   11 |    10.00 | Using index condition |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref         | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 72      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where  phone = '13546294373' and province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where     province = '532130' and phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4753980 |     1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select *
    ->         from test_user
    ->         where name = '张三' and phone = '13546294373'  and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys                       | key                                 | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test_user | NULL       | ref  | test_user_name_phone_province_index | test_user_name_phone_province_index | 77      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

4. 现象总结

对于联合索引

CREATE INDEX test_user_name_phone_province_index
  ON test_user (name, phone, province);

在查询中我们能用到的索引的是

name
name phone
name phone province

对于其他顺序,或者其他字段我们不能使用该联合索引,这个就是mysql联合索引原则

Mysql联合索引最左匹配原则

为什么使用联合索引

1. 示例表结构

2. 执行计划说明

3. 执行计划分析

4. 现象总结

猜你喜欢

热点阅读