MySQL优化 -- SQL及索引优化（一）

一、如何发现有问题的SQL？

使用MySQL慢查询日志对有效率问题的SQL进行监控：

查看是否有开启慢查询日志记录：
show variables like 'slow_query_log';
设置没用到索引的查询都记录：
set global log_queries_not_using_indexes=on;
开启慢查询日志记录：
set global slow_query_log=on;
查看日志相关设置，例如日志存放位置：
show VARIABLES like '%log%';

慢查询日志包含的内容：

select * from city;
# Time: 2020-04-05T07:08:10.574255Z
执行SQL的主机信息：
# User@Host: root[root] @ localhost [::1]  Id:   137
SQL的执行信息：
# Query_time: 0.001917  Lock_time: 0.000480 Rows_sent: 15  Rows_examined: 307
SQL的执行时间：
SET timestamp=1586070490;

二、慢查询日志的分析工具

1、mysqldumpslow工具：
MySQL自带的分析工具，优点是简单使用，将日志信息一条一条分拣出来。缺点是输出的信息内容较少。

查询前三条(滚动查看）：
mysqldumpslow -t 3 日志路径 | more

2、pt-query-digest工具：
好处就是能分析总的sql情况，还可以看出每个查询的次数，时间等信息。

输出到文件：
pt-query-digest slow-log > slow_log.report
输出到数据库表：
pt-query-digest slow.log -review \ h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review \
--create-reviewtable \ --review-history t= hostname_slow

结果分析，第一部分：显示日志的时间范围，总的sql数量和不同的sql数量。

第二部分：响应时间占比，sql执行次数。

第三部分：具体每条查询sql分析。

三、如何通过慢查询日志发现有问题的SQL？

1、查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询。
2、IO大的SQL
注意pt-query-digest分析中的Rows examine选项。
3、未命中索引的SQL
注意pt-query-digest分析中Rows examine 和 Rows Send的对比。

四、如何分析SQL查询？

使用explain查询SQL的执行计划。

explain返回各列的含义：

table：显示关于哪张表。
type：这是重要的列，显示连接使用了何种类型。从最好到最差，分别是const,eq_reg,ref,range,index,all。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
key：实际使用的索引，如果为null，则没有使用索引。
key_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好。
ref：显示索引的哪一列被使用，如果可能的话，是一个常数。
rows：MySQL认为必须检测的用来返回请求数据的行数。
extra列需要注意的返回值：
Using filesort：看到这个，查询就需要优化了。MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
Using temporary：看到这个，查询就需要优化了。MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by上。