实验环境：MySQL 5.7.17
1、表结构如下，在id,name,tx列建立主键
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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、查看执行计划
|
1
2
3
4
5
6
7
|
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只能使用定义索引的最左边部分。
本文转自 corasql 51CTO博客，原文链接：http://blog.51cto.com/corasql/1913142，如需转载请自行联系原作者
