MySQL 数据库--SQL语句优化

explain查询和分析sql

开发中，为满足一业务功能，使用mysql书写sql时，一条sql往往有多种写法，那么我们就需要选择执行效率比较高的sql。 
因此要比较分析sql的执行过程，且同一条sql我们要比较选择使用最优索引。 
通过explain命令可以得到。

先整体了解下SQL查询的基本执行过程：

1）应用通过MySQL API把查询命令发送给MySQL服务器，然后被解析

2）检查权限、MySQL optimizer进行优化，经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划（query 
plan），并可以被缓存

3）如果存在索引，那么先扫描索引，如果数据被索引覆盖，那么不需要额外的查找，如果不是，根据索引查找和读取对应的记录

4）如果有关联查询，查询次序是扫描第一张表找到满足条件的记录，按照第一张表和第二张表的关联键值，扫描第二张表查找满足条件的记录，按此顺序循环
5）输出查询结果，并记录binary logs

Explain返回信息：

id：id是一组数字，表示查询中执行select子句或操作表的顺序，如果id相同，则执行顺序从上至下，如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。

id列为null的就表是这是一个结果集，不需要使用它来进行查询。

 

select_type：

simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。

primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个。

subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery

dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响。

derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select。

union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。

dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响

union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null。

 

table：

显示的查询表名，如果查询使用了别名，那么这里显示的是别名。

如果不涉及对数据表的操作，那么这显示为null。

如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。

如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

 

type：

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL。

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描。

eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref。

ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。

ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。

unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值。

index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。

index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能都不如range。

index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。

all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

 

possible_keys：查询可能使用到的索引都会在这里列出来。

 

key：查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

 

key_len：用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

 

ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

 

rows：这里是执行计划中估算的扫描行数，不是精确值。

 

extra：这个列可以显示的信息非常多，有几十种，常用的有：

distinct：在select部分使用了distinc关键字

no tables used：不带from字句的查询或者From dual查询。

使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。

using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中。

using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。

using_union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集

using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集

using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。

using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition

using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。

firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个

loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

filtered：使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。

 

通过explain可以得到如下结论：

　　①使用索引比未使用索引，扫描的行数更少查询速度更快；

　　②在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”时，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

　　③使用多列索引时，只有查询条件中使用了该索引中的第一个索引字段时，索引才会被使用。

　　注：create index index_age_sex on user(age,sex);  age为第一个索引；

　　④查询语句只有OR关键字时，如果OR前后的两个条件列都是索引时，查询中将使用索引。只要OR前后有一个条件的列不是索引，那么查询中将不使用索引。

　　注： 1：where 语句里面如果带有or条件, myisam表能用到索引，innodb不行；2：必须所有的or条件都必须是独立索引；

　　⑤经过普通运算或函数运算后的索引字段不能使用索引。

但是，经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。

以上结论来自如下测试：

user表： 独立索引：id、name 联合索引：age && sex	user_noindex表： 无任何索引列；
CREATE TABLE `user` (   `id` int(11) NOT NULL,   `name` varchar(30) NOT NULL,   `age` int(11) NOT NULL,   `sex` tinyint(4) NOT NULL,   `isDeleted` tinyint(4) NOT NULL,   PRIMARY KEY (`id`),   UNIQUE KEY `id_unidx` (`id`) USING BTREE,   UNIQUE KEY `name_unidx` (`name`) USING BTREE,   KEY `index_age_sex` (`age`,`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;	CREATE TABLE `user_noindex` (   `id` int(11) NOT NULL,   `name` varchar(30) NOT NULL,   `age` int(11) DEFAULT NULL,   `sex` tinyint(4) DEFAULT NULL,   `isDeleted` tinyint(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

【1】索引对查询的影响-----加索引和不加索引的对比-----使用索引扫描的更少查询更快

　　　   语句1：explain SELECT * FROM `user` where name = 'name6';

　　语句2：explain SELECT * FROM `user_noindex` where name = 'name6';

结果集	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
语句1	1	SIMPLE	user	const	name_unidx	name_unidx	32	const	1	null
语句2	1	SIMPLE	user_noindex	ALL	null	null	null	null	10	Using where

【2】索引对查询的影响-----加索引----使用和未使用索引的对比-----在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”时，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。　　

　　语句1：explain SELECT * FROM `user` where name like '%name6';

　　语句2：explain SELECT * FROM `user` where name like '%name6%';

　　语句3：explain SELECT * FROM `user` where name like 'name6%';

结果集	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
语句1	1	SIMPLE	user	ALL	null	null	null	null	10	Using where
语句2	1	SIMPLE	user	ALL	null	null	null	null	10	Using where
语句3	1	SIMPLE	user	range	name_unidx	name_unidx	32	const	1	null

 

【3】索引对查询的影响-----加索引----使用和未使用索引的对比-----多列索引是在表的多个字段创建一个索引。只有查询条件中使用了这个字段中的第一个字段时，索引才会被使用。

　　语句1：explain SELECT * FROM `user` where age  = '19';

　　语句2：explain SELECT * FROM `user` where sex  = '1';

　　语句3：explain SELECT * FROM `user` where sex = '1' and age  = '19';

结果集	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
语句1	1	SIMPLE	user	ref	index_age_sex	index_age_sex	4	const	1	null
语句2	1	SIMPLE	user	ALL	null	null	null	null	10	Using where
语句3	1	SIMPLE	user	ref	index_age_sex	index_age_sex	5	const,const	1	null

　　【4】索引对查询的影响-----加索引----使用和未使用索引的对比-----查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引时，查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

　　语句1：explain SELECT * FROM `user` where (age  = '19' OR isDeleted = '0'); 

　　语句2：explain SELECT * FROM `user` where (sex = '1' OR age  = '19');  -- 联合索引

　　语句3：explain SELECT * FROM `user` where (name = 'name1' OR id  = '1'); -- 独立索引

　　-- alter table user engine = innodb;

结果集	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
语句1	1	SIMPLE	user	ALL	index_age_sex	null	null	null	10	Using where
语句2	1	SIMPLE	user	ALL	index_age_sex	null	null	null	10	Using where
语句3	1	SIMPLE	user	ref	PRIMARY,id_unidx,name_unidx	index_age_sex	null	null	10	Using where

　　-- alter table user engine = myisam;

结果集	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
语句1	1	SIMPLE	user	ALL	index_age_sex	null	null	null	10	Using where
语句2	1	SIMPLE	user	ALL	index_age_sex	null	null	null	10	Using where
语句3	1	SIMPLE	user	index_merge	PRIMARY, id_unidx, name_unidx	name_unidx, PRIMARY	32,4	null	2	Using union (name_unidx,PRIMARY);  Using where

　　很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。在MySQL中可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快。

 

limit查询的优化

 

优化limit查询 .

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题

　　select film_id,description from sakila.film order by title limit 50,5; #title列不是索引也不是主键

优化步骤1:使用有索引的列或主键进行order by操作

　　select film_id ,description from sakila.film order by film_id limit 50,5;  #把索引film_id进行order by操作，但仍然扫描了55行。

优化步骤2:记录上次返回的主键,在下次查询时用 主键过滤(避免了数据量大时扫描过多的记录)

　　select film_id ,description from sakila.film where film_id >55 and film_id<=60 order by film_id limit 1,5 #知道查询的范围为55~60，否则会扫描60行，使用主键过滤where film_id >55 and film_id<=60后只扫描了5行,避免了数据量大时扫描过多的记录。

 

优化limit查询2。使用这种方式要求主键是按顺序排序，如果中间有断层，则返回的数据行数不符，例如在film_id 55~60中少了一行，则排序是要排5行，则就会出错。