一.测试数据
MyISAM:
CREATE TABLE `test` ( `id` int(10) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `name` char(255) CHARACTER SET gbk NOT NULL, `time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE, KEY `name_time` (`name`,`time`) ) ENGINE=MyISAM;
INSERT INTO test VALUES ('1', '14', 'test', '1513338971');
INSERT INTO test VALUES ('2', '15', 'jack', '1513338980');
INSERT INTO test VALUES ('3', '16', 'home2', '1513338980');
InnoDB:
CREATE TABLE `test2` ( `id` int(10) NOT NULL AUTO_INCREMENT, `uid` int(11) NOT NULL, `name` char(255) CHARACTER SET gbk NOT NULL, `time` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`) USING BTREE, KEY `name_time` (`name`,`time`) ) ENGINE=InnoDB
INSERT INTO test2 VALUES ('1', '14', 'test', '1513338971');
INSERT INTO test2 VALUES ('2', '15', 'jack', '1513338980');
INSERT INTO test2 VALUES ('3', '16', 'home2', '1513338980');
二.实例分析,只有where的情形下
1.为什么要有最左前缀限制?
对于多列索引,最左前缀是必须的.否者mysql无法使用索引,因为符合索引是按第一列,第二列,第三列...一次来排序的.不符合最左前缀原则,mysql将放弃使用索引.
如:
mysql> explain select * from test where time=1513338971; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
mysql> explain select * from test2 where time=1513338971; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
2.AND
对于where中的and,只要and两边有一边的字段有索引,就能用到索引.
mysql> explain select * from test where time=1513338971 and name='jack'; +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ | 1 | SIMPLE | test | ref | name_time | name_time | 515 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where time=1513338971 and name='jack'; +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ | 1 | SIMPLE | test2 | ref | name_time | name_time | 515 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+-----------+---------+-------------+------+-------------+ 1 row in set (0.00 sec)
3.OR
对于OR,只要有一个字段没有索引,就不会用到索引
mysql> explain select * from test where name='jack' or time=1513338971; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test | ALL | name_time | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where name='jack' or time=1513338971; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | ALL | name_time | NULL | NULL | NULL | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
三.Order(不带where条件)
(一).单列索引
1.除非强制走索引或覆盖索引,否者myisam不会用到索引,而进行额外排序(Extra列中出现using filesort)
mysql> explain select * from test order by id desc; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
myisam是非聚集索引,优化器认为排序完后还需要具体的去读取数据行,所以干脆放弃了使用索引,也就无法索引扫描排序.除非是覆盖索引,否则优化器都不会用到索引,而进行额外排序.
2.InnoDB则可以用到索引,只扫描索引就可以排序(type列出现index,表明使用了索引排序)
mysql> explain select * from test2 order by id desc; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | test2 | index | NULL | PRIMARY | 4 | NULL | 3 | | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
因为是聚集索引,索引和数据是在一起的,优化器认为不必再去查找数据.所以会用到主键索引.
(二).复合索引
1.覆盖索引情况下,只要排序字段中排序方向一致,两者均可用到索引扫描排序.反向不一致,就不会用到索引排序.
mysql> explain select name,time from test order by name asc,time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | test | index | NULL | name_time | 515 | NULL | 3 | Using index | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
mysql> explain select name,time from test2 order by name asc,time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+ | 1 | SIMPLE | test2 | index | NULL | name_time | 515 | NULL | 3 | Using index | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
2.非覆盖索引情况下,即便方向一致,两者均需要去进行额外排序,而且优化器对两者均不进行任何索引优化
mysql> explain select * from test order by name asc,time asc; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
mysql> explain select * from test2 order by name asc,time asc; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
可以强制让其走索引
mysql> explain select * from test force index(name_time) order by name asc,time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+ | 1 | SIMPLE | test | index | NULL | name_time | 515 | NULL | 3 | | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+ mysql> explain select * from test2 force index(name_time) order by name asc,time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+ | 1 | SIMPLE | test2 | index | NULL | name_time | 515 | NULL | 3 | | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-------+
方向不一致就不用去说了.无论如何都会进行一次额外排序的.
四.Where+Order
(一).单列索引
1.只要where中有单列索引,一定会用到索引的.
mysql> explain select * from test where uid=14 order by id desc; +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | test | ref | uid | uid | 4 | const | 1 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where uid=14 order by id desc; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | test2 | ref | uid | uid | 4 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
但是不同之处在于,myisam的非聚集索引,在排序时还需要数据行取数据,所以会用到额外排序.而innodb因为聚集索引,所以不会进行额外排序.(索引肯定是排好序的)
2.where中没有索引,order中有索引,也不会走索引
mysql> explain select * from test where time=14 order by uid desc; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where time=14 order by uid desc; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | test2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
where中没有索引,order中有索引,也不会走索引
(二).复合索引
仅讨论where列和order列构建的复合索引
mysql> explain select * from test where name='jack' order by time asc; +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | test | ref | name_time | name_time | 510 | const | 1 | Using where | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where name='jack' order by time asc; +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ | 1 | SIMPLE | test2 | ref | name_time | name_time | 510 | const | 1 | Using where | +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
只有where列是一个常量,才使用索引扫描.否则将是额外排序.
mysql> explain select * from test where name>'jack' order by time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ | 1 | SIMPLE | test | range | name_time | name_time | 510 | NULL | 2 | Using where; Using filesort | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select * from test2 where name>'jack' order by time asc; +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ | 1 | SIMPLE | test2 | range | name_time | name_time | 510 | NULL | 1 | Using where; Using filesort | +----+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
mysql每次只能使用一个索引,无论是单例索引还是符合索引.
只有用到索引,才有可能用到索引扫描(一句废话)