MySQL只能使用定义索引的最左边部份

实验环境：MySQL 5.7.17

1、表结构如下，在id,name,tx列建立主键

mysql> show create table txtx;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                          |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| txtx  | CREATE TABLE `txtx` (  `id` int(11) NOT NULL,  `name` char(2) NOT NULL,  `tx` char(3) NOT NULL,  `id1` int(11) DEFAULT NULL,  PRIMARY KEY (`id`,`name`,`tx`)) ENGINE=InnoDB DEFAULT CHARSET=gbk |+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

2、查看执行计划

mysql> explain select * from txtx where id=1 and id1 =1 and tx='tx';+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+|  1 | SIMPLE      | txtx  | NULL       | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |    33.33 | Using where |+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

从上面执行计划可以看出，尽管查询在where语句中使用了id与tx列，但只使用了id列，原因是MySQL只能使用定义索引的最左边部分。