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='10001' 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='10001' 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='10001';+----+-------------+--------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------+| 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='10001' 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('10061','10062','10063','10064') 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='10001' 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='10001' 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='10001' 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='10001' 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='10001' 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='10001' 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)