mysql优化之慢查询分析+explain命令分析+优化技巧总结

分析慢查询

1.查看慢SQL是否启用，查看命令：show variables like 'log_slow_queries'; 

如果结果为ON则是开启了，如果为OFF则表示禁用了。

2.开启慢查询命令：set global log_slow_queries = on;    

3.查看是否开启：show variables like 'log_slow_queries'; 

4.查看慢查询参数，即设置超过多少秒的查询归为了慢查询。参数为：long_query_time，查询命令： show global  variables like 'long_query_time';

5.mysql默认时间为10秒，即10秒及以上的查询被归为了慢查询。我们的实际项目中根本就不可能这么包容你，所以得提供查询效率优化sql，让程序更快的执行。

6.这里设置时间为1秒，即超过1秒就会被认为慢查询。设置命令：set global long_query_time =1;用命令设置的，会立即生效，不用重启mysql服务。但重启mysql服务后就会失效

7.查看设置的时间， show global variables like 'long_query_time';即可看到现在已经变为1秒了

8.查看慢查询存放日志，命令： show variables like 'slow_query_log_file';

去相应目录下查看即可。

mysql explain命令分析

通过分析可得到一下结果：

• 表的读取顺序

• 数据读取操作的操作类型

• 哪些索引可以使用

• 哪些索引被实际使用

• 表之间的引用

• 每张表有多少行被优化器查询

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

id相同，执行顺序由上到下。

id不同，如果是子查询，id的序号会递增，id值越大，越先被查询

id相同不同，同时存在。如果相同，代表是同一组，从上往下执行。在所有组中，id值越大，优先级别越高，越先执行。

select_type

查询类型：普通查询、联合查询、子查询等值有六种。

1.SIMPLE：简单的select查询，查询总不包含子查询或者union查询

2.PRIMARY：查询中包含复杂子部分。最外层查询为PRIMARY

3.SUBQUERY：在select或where列表中包含了子查询

4.DERIVED：在FROM列表中包含的子查询白标记为DERIVED（派生），Mysql会递归执行这些子查询。将结果放到临时表

5.UNION：若第二个select出现了union后，则会被标记为UNION.若union包含在from子句的子查询中，外层的会被标记为DERIVED

6.UNION RESULT ：从union表获取结果的select。

table

显示是哪张表

type

type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。

system：表只有一行记录（等于系统表），是const的特例类型

const：表示通过一次索引就找到了结果

eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引。

ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

range：只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

index：全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

ALL：全表扫描

注：一般来说，需保证查询至少达到range级别，最好能达到ref。

possible_keys

显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。key=idx_deptid表示在实际查询的过程中进行了索引的全扫描。

key_len

表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref

显示关联的字段。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

rows

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

filtered：百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

Extra

Using filesort：表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为&ldquo;文件排序&rdquo;。出现Using filesort就非常危险了，在数据量非常大的时候几乎&ldquo;九死一生&rdquo;。出现Using filesort尽快优化sql语句。

Using temporary：使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，&ldquo;十死无生&rdquo;，急需优化。将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，&ldquo;十死无生&rdquo;。

Using index：表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。

如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

Using index>Using filesort（九死一生）>Using temporary（十死无生）

总结

explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息。

①id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。

②type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。

③key，实际被使用的索引列。

④ref，关联的字段，常量等值查询，显示为const，如果为连接查询，显示关联的字段。

⑤Extra，额外信息，使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。

着重关注上述五个字段信息，对日常生产过程中调优十分有用。

优化的手段

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

5.in 和 not in 也要慎用，否则会导致全表扫描。能用betwen，就不要用in

6.模糊查询也会全表扫描

7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

8.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

9.不要在 where 子句中的&ldquo;=&rdquo;左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

10.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11.不要写一些没有意义的查询，如需要生成一个空表结构。

12.很多时候用 exists 代替 in 是一个好的选择。

13.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用

14.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。 

15.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。   

16.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 

17.任何地方都不要使用 select * from t ，用具体的字段列表代替&ldquo;*&rdquo;，不要返回用不到的任何字段。

18.避免频繁创建和删除临时表，以减少系统表资源的消耗。

19.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。    

20.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

21.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。    

22.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

23.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

24.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。

在结果集中包括&ldquo;合计&rdquo;的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

25.尽量避免大事务操作，提高系统并发能力。

26.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

来源：https://blog.csdn.net/wangyunzhao007/article/details/108187642