MySQL查询的优化

1、查询截取优化

SQL调优过程：

1. 观察，至少跑1天，看看生产的慢SQL情况。
2. 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
3. explain + 慢SQL分析。
4. show profile。
5. 运维经理 or DBA，进行SQL数据库服务器的参数调优。

总结：

• 慢查询的开启并捕获
• explain + 慢SQL分析
• show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
• SQL数据库服务器的参数调优。

1.1、in 和 exists（小表驱动大表）

在查询的优化中永远小表驱动大表。

举例说明：

for(int i=5;.......)
{
     for(int j=1000;......)
     {}
}

如果小的循环在外层，对于数据库连接来说就只连接5次，进行5000次操作；但如果1000在外，则需要进行1000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

• 当B表的数据集小于A表的数据集时，用in优于exists。

即B表的数据集小于A表的数据集时，用 select ... from A where ... in (selct ... from B) 的语法。如下：

select * from A where id in (select id from B);
等价于:
for select id from B
for select * from A where A.id = B.id

• 当A表的数据集小于B表的数据集时，用exists优于in

即A表的数据集小于B表的数据集时，用 select ... from A where exists (selct ... from B where ...) 的语法。如下：

select * from A where exists (select 1 from B where B.id = A.id)
等价于：
for select * from A
for select * from B where B.id = A.id

如上所述，在查询中永远遵循小表驱动大表的原则。

1.2、order by 优化

MySQL支持二种方式的排序：FileSort和Index

• Index效率高，它指MySQL扫描索引本身完成排序
• FileSort方式效率较低。应尽量使用Index方式排序，避免使用FileSort方式排序。

当 ORDER BY满足以下情况时，会使用Index方式排序：

1. ORDER BY语句遵循索引的最左前缀原则，并且中间兄弟不能断。
2. where 子句和 order by 子句拼起来符合最左前缀，并且 where 子句中的条件是常量

当出现以下情况时，会使用 FileSort 排序：

1. 不满足最左前缀原则
2. 多字段之间的排序规则不一致，比如a是降序，b是升序。只要索引的字段的排序规则都一样，不管是升序还是降序，都能使用 Index 排序。
3. 用于排序的字段不属于索引

举例说明，假设给表 tblA 建索引 tblA(age, birth)，下面的 order by 语句遵循最左前缀原则，所以能使用 Index 方法进行排序，如下：

下面 where 和 order by 子句都不遵循最左前缀原则，所以只能使用 FileSort 方式进行排序，如下：

或者，where 子句和 order by 子句拼起来符合最左前缀，并且 where 子句中的条件是常量，此时排序也会使用 Index 进行排序的，如下：

1.3、group by优化

Group By 的优化和 order by差不多，group by 实质上是先排序后进行分组，也需遵照索引的最佳左前缀。

where高于having，能写在where限定的条件就不要去having限定了。

2、慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

通过慢查询日志文件可以查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟就算慢SQL，我们就可以收集到超过5秒的sql，结合 explain 进行全面分析。

2.1、开启慢查询日志

默认情况下，MySQL数据库并没有开启慢查询日志的记录，需要我们手动来设置这个参数。

我们可以通过以下命令来查看是否有开启慢查询日志：

-- 查看日志是否开启
SHOW VARIABLES LIKE '%slow_query_log%';

查询结果如下：

可以看到当前是未开启的，并且可以看到慢查询日志文件的目录是在 /var/lib/mysql/192-slow.log 下。该文件是系统默认指定的，格式为：host_name-slow.log。

要想开启慢查询日志，可通过如下命令进行操作：

-- 开启慢查询日志
set global slow_query_log=1  -- 该命令只对当前数据库生效

要想永久生效，需要修改配置文件 my.cnf，在 [mysqld] 下增加或修改参数 slow_query_log 和 slow_query_log_file ，然后需重启MySQL服务器，修改如下：

slow_query_log =1
slow_query_log_file=/var/lib/manongyanjiuseng-slow.log

不过一般我们也不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

2.2、设置慢SQL时间阈值

判定某条 SQL 是否算是慢 SQL，是否会放到慢查询日志文件上，这个是由参数 long_query_time 控制的，默认情况下，long_query_time 的值为10秒。

SQL 的执行时间只有大于 long_query_time 的值时才会被记录，小于或者等于时并不会被记录。

可通过以下命令查看时间阈值：

SHOW VARIABLES LIKE 'long_query_time%';

可通过以下命令设置时间阈值：

set global long_query_time=3;   -- 设置为 3 秒

设置完后，需要重启一下命令行窗口或者打开新窗口才可以看到设置后的效果。

也可以在配置文件中设置阈值，配置如下：

#[mysqld]下配置:
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE;

2.3、查询慢SQL

可通过以下命令来查询当前系统中有多少条慢查询记录：

show global status like '%Slow_queries%';

我们可以通过 select sleep(xx秒); 命令来模拟一个执行时间为 xx 秒的 SQL，以此来验证上面的命令。如下：

2.4、使用 mysqldumpslow 工具分析慢SQL

在生产环境中，如果要手工地去分析日志，查找、分析SQL，这显然是个体力活，由此 MySQL 提供了日志分析工具mysqldumpslow，我们可以通过该工具来分析慢 SQL 。

mysqldumpslow 命令的参数如下：

• -s, 是sort的意思，表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒序；
• -t, 是top n的意思，即为返回前面多少条的数据；
• -g, 是grep的意思，后边可以写一个正则匹配模式，大小写不敏感的；

例子：

• mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log：得到返回记录集最多的10个SQL 
• mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log：得到访问次数最多的10个SQL
• mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log：得到按照时间排序的前10条里面含有左连接的查询语句

另外建议在使用这些命令时结合│和more 使用，不然可能会出现数据量多大不断刷屏的情况，mysqldumpslow -s r-t 10 /ar/lib/mysql/atguigu-slow.log | more