建表语句 :
CREATE TABLE `t` (
`c1` char(2) NOT NULL DEFAULT '',
`c2` char(2) NOT NULL DEFAULT '',
`c3` char(2) NOT NULL DEFAULT '',
`c4` char(2) NOT NULL DEFAULT '',
`c5` char(2) NOT NULL DEFAULT '',
KEY `test_index` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('1','2','3','4','5');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('a','c','df','d','f');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('d','x','d','d','x');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('s','s','df','','d');
测试中发现:
EXPLAIN
SELECT * FROM t WHERE c1 = '1' AND c2 = "2" AND c3 LIKE '3%' AND c4 = '2'
这种语句 4列都可以用到索引这是为啥呢?
我 看《高性能mysql》中说 “如果某个列是范围查询 他后面的列都无法使用索引优化”求解~~~
CREATE TABLE `t` (
`c1` char(2) NOT NULL DEFAULT '',
`c2` char(2) NOT NULL DEFAULT '',
`c3` char(2) NOT NULL DEFAULT '',
`c4` char(2) NOT NULL DEFAULT '',
`c5` char(2) NOT NULL DEFAULT '',
KEY `test_index` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('1','2','3','4','5');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('a','c','df','d','f');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('d','x','d','d','x');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('s','s','df','','d');
测试中发现:
EXPLAIN
SELECT * FROM t WHERE c1 = '1' AND c2 = "2" AND c3 LIKE '3%' AND c4 = '2'
这种语句 4列都可以用到索引这是为啥呢?
我 看《高性能mysql》中说 “如果某个列是范围查询 他后面的列都无法使用索引优化”求解~~~
通过看这个key_len的长度
是用到了