索引是什么
是存储引擎用于找到数据的一种数据结构。
索引的性能
在数据量小的时候,一个坏的索引往往作用没有那么明显,但是在数据量比较大的时候一个坏的索引和好的索引有巨大的区别。
在查询优化的时候应该首先考虑索引优化。这个是最简单的,也是效果最好。
索引的执行流程
索引 => 索引值 => 数据行
mysql> explain select first_name from actor where actor_id = 5; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | actor | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
通过explian可以看到where条件后面是使用了主键索引。
多列索引
如果是多列组成的索引,那么在使用索引的时候要考虑索引的顺序。
多列索引有如下原则
- 最左匹配原则,如果缺失了索引的最左侧列,那么索引不生效
- 中间不可中断原则,如果中间定义的索引没有在where条件里面体现,那么后面的字段过滤将不会用到索引
- 范围中断原则,只要使用了范围查询,那么其右侧的索引都无法正常使用
多列索引的验证
建表语句
create table person ( A INT(10) not null, B INT(10) not null, C INT(10) not null, version VARCHAR(20) not null ); create index A on person (A, B, C);
填充数据的存储过程
delimiter // // CREATE DEFINER=`dev`@`%` PROCEDURE `insert_person`(IN item int) BEGIN DECLARE counter INT; SET counter = item; WHILE counter >= 1000 DO INSERT INTO person VALUES(left(counter, 2),left(counter,3), counter,counter ); SET counter = counter - 1; END WHILE; END; // delimiter ; call insert_person(100000);
数据详情
mysql> select count(*) from person; +----------+ | count(*) | +----------+ | 100000 | +----------+ 1 row in set (0.04 sec)
场景分析
一、索引生效
1. 符合多列索引生效的三个原则,全部用上了索引
(a) 只对A做等值查询
mysql> explain select * from person where A = 12; +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
可以看到是用到了A的索引的。
(b)只对A做范围查询,并且结果集覆盖索引的时候。
mysql> explain select A,B,C from person where A > 13; +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 4 | NULL | 49555 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
(c)对A,B做等值查询
mysql> explain select A,B,C from person where A = 13 and b = 129; +----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 8 | const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
(d) 对A做等值,B做范围
mysql> explain select A,B,C from person where A = 13 and b > 129; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 1100 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
(e) ABC等值查询
mysql> explain select A,B,C from person where A = 12 and b = 129 and c = 1294; +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 12 | const,const,const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+------+---------+-------------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
(f) 对AB做等值,C范围查询
mysql> explain select A,B,C from person where A = 12 and b = 129 and c > 1294; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 12 | NULL | 105 | 100.00 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
二、索引部分生效
满足最左原则,但是不满足中间不中断或者范围中断。
(a) 对A做等值,C做等值或者是范围
mysql> explain select * from person where A = 12 and c = 1230; +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 10.00 | Using index condition | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where A = 12 and c >1230; +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 33.33 | Using index condition | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
(b)对A做等值,B做范围,C做等值或者范围,B做了范围查询之后,无法对C正常使用索引
mysql> explain select * from person where A = 12 and b > 123 and c >1230; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 8 | NULL | 660 | 33.33 | Using index condition | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where A = 12 and b = 123 and c >1230; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 12 | NULL | 109 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
(c) 对A做范围查询,BC做等值或者范围,那么BC将不会使用索引
mysql> explain select A,B,C from person where A > 12 and b > 123 and c >1230; +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | person | NULL | range | A | A | 4 | NULL | 49555 | 11.11 | Using where; Using index | +----+-------------+--------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
需要注意的是这个地方ABC只是普通的索引如果是select * from,那么将不会使用索引。因为还有一个字段不再索引持有的数据上。
三、多列索引失效:
(a)查询条件中不包含最左列
mysql> explain select * from person where b = 123 and c >1230; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 3.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where b = 123 -> ; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where b >123; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where c =123; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 10.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from person where c >123; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
(b) 含有最左列,但是索引的列带条件
mysql> explain select * from person where a + 1 =12; +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ## 从哪里带条件哪里就不开始使用索引 mysql> explain select * from person where a =12 and b + 1 = 123; +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | person | NULL | ref | A | A | 4 | const | 1100 | 100.00 | Using index condition | +----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
四、order by使用索引
(a) order by 字段的顺序和table中多列索引定义的顺序一样。
mysql> explain select * from person1 order by a,b, c; +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | person1 | NULL | ALL | NULL | NULL | NULL | NULL | 98999 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select a,b,c from person1 order by a,b, c; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
order by 不使用索引
(a) order by多个字段,每个字段都是独立的索引
mysql> create table person2 select * from person1; Query OK, 99001 rows affected (0.68 sec) Records: 99001 Duplicates: 0 Warnings: 0 mysql> alter table add key(a); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add key(a)' at line 1 mysql> alter table add index_a key(a); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index_a key(a)' at line 1 mysql> alter table add index key(a); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index key(a)' at line 1 mysql> alter table add index index_a key(a); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index index_a key(a)' at line 1 mysql> alter table add index index_a(a); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add index index_a(a)' at line 1 mysql> alter table person2 add index index_a(a); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table person2 add index index_b(b); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from person2 order by a,b; +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ | 1 | SIMPLE | person2 | NULL | ALL | NULL | NULL | NULL | NULL | 99110 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+----------------+ 1 row in set, 1 warning (0.00 sec)
(b) order by语句中同时含有desc和asc
mysql> explain select a,b,c from person1 order by a desc ; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select a,b,c from person1 order by a desc ,b asc; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
(c) order by 里面有计算字段
mysql> explain select a,b,c from person1 order by a desc ,b + 1 -> ; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
(d) orderby 里面顺序和索引的顺序不同
mysql> explain select a,b,c from person1 order by b,a; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ | 1 | SIMPLE | person1 | NULL | index | NULL | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using filesort | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec)
(e) group by 和orderby 的顺序不同
mysql> explain select a,b,c from person1 group by a,b,c order by a,b; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ | 1 | SIMPLE | person1 | NULL | index | index_A_B_C | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select a,b,c from person1 group by a,b,c order by b,a; +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+ | 1 | SIMPLE | person1 | NULL | index | index_A_B_C | index_A_B_C | 21 | NULL | 98999 | 100.00 | Using index; Using temporary; Using filesort | +----+-------------+---------+------------+-------+---------------+-------------+---------+------+-------+----------+----------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
上面的查询顺序一样,没有filesort,下面的不一样,就用了
(f) join查询的时候order只能按照主键的来排序,否则就会失效
mysql> explain select * from person3 t1 left join person4 t2 on t1.version = t2.version where t1.version = 1200 order by t1.version; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | PRIMARY | PRIMARY | 22 | NULL | 101 | 10.00 | Using where | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------------+ 2 rows in set, 3 warnings (0.00 sec) mysql> explain select * from person3 t1 left join person4 t2 on t1.version = t2.version where t1.version = 1200 order by t2.version; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 101 | 10.00 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+----------------------------------------------+ 2 rows in set, 3 warnings (0.00 sec) mysql> show create table person3; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | person3 | CREATE TABLE `person3` ( `A` varchar(10) DEFAULT NULL, `B` int(50) NOT NULL, `C` int(50) NOT NULL, `version` varchar(20) NOT NULL, PRIMARY KEY (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create table person4; +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | person4 | CREATE TABLE `person4` ( `A` varchar(10) DEFAULT NULL, `B` int(50) NOT NULL, `C` int(50) NOT NULL, `version` varchar(20) NOT NULL, PRIMARY KEY (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> explain select * from person3 t1 left join person4 t2 on t1.version = t2.version order by t1.version; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | index | NULL | PRIMARY | 22 | NULL | 101 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> explain select * from person3 t1 left join person4 t2 on t1.version = t2.version order by t2.version; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 101 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+---------------------------------+ 2 rows in set, 1 warning (0.00 sec) ## 不加where也是一样的。
优化此种情况
mysql> explain select * from person3 t1 left join person4 t2 on t1.version = t2.version join(select version from person3 order by a desc) a_order on t1.version = a_order.version; +----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 101 | 100.00 | NULL | | 1 | SIMPLE | person3 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY | PRIMARY | 22 | dev.t1.version | 1 | 100.00 | NULL | +----+-------------+---------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+ 3 rows in set, 1 warning (0.00 sec)