MySQL联合索引最左匹配范例
参考文章：http://blog.jobbole.com/24006/ 创建示例表。
示例表来自MySQL官方文档： https://dev.mysql.com/doc/employee/en/
CREATE TABLE titles (
emp_no INT NOT NULL,
title VARCHAR(50) NOT NULL,
from_date DATE NOT NULL,
to_date DATE,
PRIMARY KEY (emp_no,title,from_date)
) ; 导入测试数据 load_titles.dump
https://github.com/datacharmer/test_db 索引情况
mysql> show index from titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comme
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------
| titles | 0 | PRIMARY | 1 | emp_no | A | 0 | NULL | NULL | | BTREE |
| titles | 0 | PRIMARY | 2 | title | A | 0 | NULL | NULL | | BTREE |
| titles | 0 | PRIMARY | 3 | from_date | A | 0 | NULL | NULL | | BTREE |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+------
3 rows in set (0.00 sec) 情况一.WHERE条件中全部列都在索引中
不管WHERE的顺序如何执行计划都可以完整用到索引。
mysql> desc SELECT * FROM titles WHERE emp_no='' AND title='Senior Engineer' AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND emp_no='' AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' AND emp_no='';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | titles | NULL | const | PRIMARY | PRIMARY | 159 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' AND title in('Senior Engineer','Staff','Assistant Engineer') AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no in('','','','') AND title in('Senior Engineer','Staff','Assistant Engineer') AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 12 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' AND title='Senior Engineer' AND from_date>='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | range | PRIMARY | PRIMARY | 159 | NULL | 1 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 情况二.WHERE条件和ORDER BY中全部列都在索引中，索引最左列在WHERE条件中，
不管ORDER BY是ASC还是DESC，执行计划都可以完整用到索引。 mysql> desc SELECT * FROM titles WHERE emp_no='' AND title='Senior Engineer' order by from_date;
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 156 | const,const | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' AND title='Senior Engineer' order by from_date desc;
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 156 | const,const | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' order by title,from_date;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' order by title,from_date desc;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE emp_no='' order by title desc,from_date desc;
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | titles | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 情况三.当WHERE条件和ORDER BY中全部列都在索引中，索引最左列不在WHERE条件中而是在ORDER BY中
执行计划会走index。 mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' order by emp_no,from_date;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' order by emp_no desc,from_date desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' order by emp_no,title;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' order by emp_no desc,title desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 10.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26' order by emp_no;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' order by emp_no;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE title='Senior Engineer' AND from_date='1986-06-26' order by emp_no desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles WHERE from_date='1986-06-26' AND title='Senior Engineer' order by emp_no desc;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 1.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec) 情况四.当WHERE条件无过滤，索引列全在ORDER BY中
1.当order by 中ASC和DESC顺序和索引一致执行计划会走INDEX，如果不一致则会走全表扫描。 mysql> desc SELECT * FROM titles order by emp_no,title,from_date;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
| 1 | SIMPLE | titles | NULL | index | NULL | PRIMARY | 159 | NULL | 441772 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.01 sec) mysql> desc SELECT * FROM titles order by emp_no,title desc,from_date desc;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles order by emp_no desc,title,from_date;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec) mysql> desc SELECT * FROM titles order by emp_no,title desc,from_date desc;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | titles | NULL | ALL | NULL | NULL | NULL | NULL | 441772 | 100.00 | Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)