JAVA|慢SQL语句优化 JAVA|java

对慢SQL语句优化一般可以按下面几步思路：开启慢查询日志，设置超过几秒为慢SQL语句，抓取慢SQL语句；通过explain查看执行计划，对慢SQL语句分析；创建索引并调整语句，再查看执行计划，对比调优结果。

文章图片

参数 slow_query_log ：表示是否开启慢查询日志。语句“set global slow_query_log=on”临时开启慢查询日志，如果想关闭慢查询日志只需要执行“set global slow_query_log=off ”即可。

参数slow_query_log_file：当使用文件存储慢查询日志时（log_output设置为“FILE”或者“FILE,TABLE”时），指定慢查询日志存储于哪个日志文件中，默认的慢查询日志文件名为“主机名-slow.log”，慢查询日志的位置为datadir参数所对应的目录位置。另外，在MySQL 5.7.2之后，如果
设置了慢日志是写到文件里，就需要设置log_timestamps（默认是UTC时间，比我们晚8小时，需要设置为系统时间log_timestamps=SYSTEM）来控制写入到慢日志文件里面的时区（该参数同时影响general日志和error日志）。
参数long_query_time ：表示“多长时间的查询”被认定为“慢查询”，默认值为10秒，表示超过10秒的查询被认定为慢查询。语句“set long_query_time=5”表示现在起所有执行时间超过1秒的SQL都将被记录到慢查询文件中。
参数log_queries_not_using_indexes ：表示如果运行的SQL语句没有使用到索引，是否也被当作慢查询语句记录到慢查询日志中，OFF表示不记录，ON表示记录。
参数log_throttle_queries_not_using_indexes ：当log_queries_not_using_indexes设置为ON时，没有使用索引的查询语句也会被当作慢查询语句记录到慢查询日志中。使用log_throttle_queries_not_using_indexes可以限制这种语句每分钟记录到慢查询日志中的次数，因为在生产环境中有可能有很多没有使用索引的语句，此类语句频繁地被记录到慢查询日志中，可能会导致慢查询日志快速不断地增长，管理员可以通过此参数进行控制。

慢查询日志中给出了账号、主机、运行时间、锁定时间、返回行等信息，然后根据这些信息来分析此SQL语句哪里出了问题。当开始使用慢查询功能后，可能随着慢查询日志越来越大，通过vi或cat命令不能很直观地查看慢查询日志，这时就可以使用MySQL内置的mysqldumpslow命令来进行分析。

利用explain分析查询语句
在工作中，我们用于捕捉性能问题最常用的就是打开慢查询，定位执行效率差的SQL。当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描还是索引扫描，这些都需要通过explain去完成。explain命令是查看优化器如何决定执行查询的主要方法，从而知道MySQL如何处理SQL语句以及查询语句是否走了合理的索引。
使用explain，只需要在查询中的select关键字之前增加explain这个词即可，MySQL会在查询上设置一个标记，当执行查询时返回关于在执行计划中每一步的信息，而不是执行它

文章图片

（1）id：反映的是表的读取顺序或查询中执行select子句的顺序。
① id相同，执行顺序是由上至下的。
② id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行。
【JAVA|慢SQL语句优化】③ id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
（2）select_type：表示select的类型，主要用于区别普通查询、联合查询、子查询等复杂查询。
① simple：简单的select查询，查询中不包含子查询或union。
② primary：查询中若包含任何复杂的子部分，最外层查询标记为primary。
③ subquery：select或where列表中的子查询。
④ derived（衍生）：在from列表中包含的子查询，MySQL会递归执行这些子查询，把结果放在临时表里。
⑤ union：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived。
⑥ union result：union后的结果集。
（3）table：显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是第几步执行的结果的简称。
（4）type：对表的访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。常见的访问类型有ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）。
① ALL：Full Table Scan，MySQL将遍历全表以找到匹配的行。
② index:：Full Index Scan，index与ALL的区别为index类型只遍历索引树。
③ range：索引范围扫描，返回一批只检索给定范围的行，使用一个索引来选择行，一般就是在where语句中出现between、< 、>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
④ ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以应该属于查找和扫描的混合体。
⑤ eq_ref：类似ref，区别在于使用的索引是唯一索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者unique key作为关联条件。
⑥ const、system：当MySQL对查询某部分进行优化并转换为一个常量时，使用这些类型访问。如果查询条件用到常量，那么通过索引一次就能找到，常在使用primary key或unique的索引中出现。system是const类型的特例，当查询的表只有一行的情况下使用。
⑦ NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
（5）possible_keys：指出MySQL能使用哪个索引在该表中找到行，查询涉及的字段上若存在索引，则该索引将被列出，但不一定会被查询使用。
（6）key：显示MySQL实际决定使用的索引，如果没有选择索引，则显示是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX或者IGNORE INDEX。查询中若使用了覆盖索引（select后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中。
（7）key_len：显示索引中使用的字节数。
（8）ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
（9）rows：显示MySQL根据表统计信息以及索引选用的情况，估算找到所需的记录要读取的行数。
（10）Extra：该列包含MySQL解决查询的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
① Using where：不用读取表中所有信息，仅通过索引就可以获取所需数据，发生在对表的全部请求列都是同一个索引部分的时候，表示MySQL服务器将在存储引擎检索行后再进行过滤。
② Using temporary：表示MySQL需要使用临时表来存储结果集，MySQL在对查询结果排序时使用临时表，常见于排序（order by）和分组查询（group by）。
③ Using filesort：当Query中包含order by操作而且无法利用索引完成的排序操作称为“文件排序”，创建索引时会对数据先进行排序，出现using filesort一般是因为order by后的条件导致索引失效，最好进行优化。
④ Using join buffer：表明使用了连接缓存，比如说在查询的时候，多表join的次数非常多，就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值，应该注意，根据查询的具体情况可能需要添加索引来改进。
⑤ Using index：只使用索引树中的信息，而不需要进一步搜索读取实际的行来检索表中的列信息。相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率好。覆盖索引：select后的数据列只从索引就能取得，不必读取数据行，且与所建索引的个数（查询列小于等于索引个数）、顺序一致。如果要用覆盖索引，就要注意select的列只取需要用到的列，不用select *，同时如果将所有字段一起做索引会导致索引文件过大，性能会下降。
⑥ Using Index Condition：表示进行了ICP优化。
总结一下针对explain命令生成执行计划：首先关注查询类型type列，如果出现all关键字，代表全表扫描，没有用到任何index；再看key列，如果key列是NULL，代表没有使用索引；然后看rows列，该列数值越大意味着需要扫描的行数越多，相应耗时越长；最后看Extra列，要避免出现Using filesort或Using temporary这样的字眼，这是很影响性能的。