mysql查询截取分析

MySQL 慢查询 一般分析过程：

至少跑一天，观察，看生产的慢SQL情况。
开启慢查询日志，设置阈值，如超过5秒就是慢查询，将其抓取下来。
explain SQL 分析
show profile
DBA 或运维 进行SQL服务器参数调优

查询优化

永远小表驱动大表

永远小表驱动大表，因为先查小表可以得到一些接下来查询的过滤条件，再查大表时可以根据这些过滤条件用上索引等内容增加整体查询速度。

IN和EXISTS 可以相互替代，要根据主从表的大小决定使用哪个：

select * from tb1 where tb1.id in (select id from tb2);
select * from tb1 where exists (select 1 from tb2 where tb1.id=tb2.id);

主表比子表大用 IN ，相反用 EXISTS

因为 IN是子表驱动主表，EXISTS是主表驱动子表。

IN：
先进行子查询，根据结果去到主表匹配满足条件的行。由于索引等的存在，如果子查询结果较少，通常较快。
EXISTS：
对主查询的数据做loop循环，每次loop循环放到子查询中做条件验证，根据结果（True或False）决定主查询这条记录是否保留。

order by 关键字优化

order by 子句，尽量使用 Index 方式排序，避免使用 filesort 方式排序。
Index效率高，它指 mysql 扫描索引本身完成排序。

order by 满足两种情况，会使用Index 方式排序：

order by 语句使用索引最佳左前缀法则
使用 where 子句与 order by 子句条件组合满足索引最佳左前缀法则

案例：
已知有复合索引 (age,birth)

order by 语句使用索引最佳左前缀法则 ，使用 index 方式排序。

order by 子句条件组合不满足索引最佳左前缀法则 ，使用 filesort 方式排序。
虽然 order by 条件组合顺序满足最佳左前缀法则 ，但一个升序一个降序，不能使用 index方式排序。

filesort 排序

如果不在索引列上，filesort 有两种排序算法

mysql4.1 之前使用双路排序：
两次扫描磁盘，最终得到数据，先取出order by排序的 列，放入Buffer中，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从磁盘中读取对应的数据输出。

mysql4.1 之后使用单路排序：
从磁盘读取查询需要的所有列，按照order by 列在Buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取磁盘。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每行都保存在内存中。

单路排序可能的问题：
单路排序要把所有数据一次都读取出来，如果读取的数据超过了sort_buffer 的容量，则需要排完后再取，从而造成多次IO，反而得不偿失。
提高 order by 速度

除了索引的影响，还需考虑：

不要使用 select * ，只使用查询需要的字段，影响如下：
1.1 、当query 字段大小总和小于 max_length_for_sort_data ,而且排序字段不是 TEXT|BLOB 类型时，会用单路排序，否则会用老的多路排序。
1.2、 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 temp 文件进行排序，导致多次IO ,但是用单路排序算法风险更大（一次读取全部数据，占空间多），所以要提高 sort_buffer_size。

尝试提高 sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

尝试提高 max_length_for_sort_data
提高这个参数，会增加用改进算法即单路排序的概率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

小总结

MySQL两种排序方式：文件排序（filesort）或扫描有序索引排序（index）
MySQL能为排序与查询使用相同的索引
group by 关键字优化

group by 实质是先排序后进行分组，遵循索引的最佳左前缀法则。

当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer _size 参数设置。

以上两点同 order by

注意 ：where 高于 having ，能写在 where 限定的条件就不要写到 having中

慢查询日志
是什么

MySQL的慢查询日志是 MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值（默认为10秒）的SQL，会被记录到慢查询日志中。
对收集的慢查询SQL用explain 进行全面分析。
默认情况，MySQL慢查询日志记录功能是关闭的，需要手动设置开启。如果不是调优需要，不建议开启，因为它本身会消耗性能。
支持将日志记录写入文件中。

怎么用
查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%'

开启

set global slow_query_log=1;

使用此命令开启慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。
如果要永久生效，就必须修改配置文件 my.cnf ,配置如下参数后，重启。

slow_query_log=1
slow_query_log_file=/var/lib/mysql/mycomputerName-slow.log

查看记录阈值

SHOW VARIABLES LIKE ‘long_query_time%’

默认为10秒

set global long_query_time=3;

可以使用命令修改，也可以在 my.cnf 参数里面修改。

修改后需要重新连接或新开一个会话才能通过 SHOW VARIABLES LIKE ‘long_query_time%’看到修改值改变。或者用下面命令可直接看到修改后的值:

SHOW global VARIABLES LIKE ‘long_query_time%’

假如运行时间正好等于 long_query_time 的情况，并不会被记录下来。需要大于这个值才会被记录。
查看当已经生成了几条慢查询记录

SHOW GLOBAL status LIKE ‘%Slow_queries%’