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只能使用定義索引的最左邊部分。