常见优化方法及慢查询

exist 和 in

select ...from table where exist (子查询)；

select ....from table where 字段 in (子查询);

如果主查询的数据集大，则使用In 效率高 如果子查询的数据集大，则使用exist 效率高

exist用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False exist 指定一个子查询，检测行的存在。

order by 优化

using filesort (额外的一次排序) 有两种算法：双路排序、单路排序（根据IO的次数）

Mysql4.1之前 默认 使用 双路排序：扫描2次磁盘（1：从磁盘读取排序字段，对排序字段进行排序 2：扫描其他字段）

-----------IO较消耗性能

Mysql4.1之后 默认 使用 单路排序： 只读取一次（全部字段），在buffer中进行排序（一个缓冲区） 但也有一定的隐患

------如果数据量特别大，则无法将所有字段的数据 一次性读取完毕，因此会进行“分片读取，多次读取”；

------单路排序 比 双路排序 会占用更多的buffer；

--------可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024 单位byte

提高order by 查询的策略：

1. 选择使用单路、双路；调整buffer的容量大小；
2. 避免select * ... 最好能确定查询的字段
3. 复合索引 不要跨列使用，避免using filesort
4. 保证全部的排序字段 排序的一致性（都是升序 或 降序）

SQL排查 -- 慢查询日志 Mysql提供的一种日志记录，用于记录mysql响应时间超过阀值的sql语句（超过10秒）

慢查询日志默认是关闭的： 建议是开发调试 打开 ； 最终部署 关闭

检查是否开启 慢查询日志： show variables like '%slow_query_log%' ;

临时开启：

set global show_query_log = 1; -----在内存中开启

exit

service mysql restart

永久开启：

/ect/my.cnf 中追加配置

vi /etc/my.cnf 打开文件

[mysqld] 在这个文件后面追加两句话 如下：

slow_query_log = 1

slow_query_log_file = /var/lib/mysql/localhost-slow.log

慢查询阀值：

show variables like '%long_query_time%' ; 查看

临时设置阀值：

set global long_query_time = 5; ---设置完毕后，重新登陆有效

永久设置阀值：

/ect/my.cnf 中追加配置

vi /etc/my.cnf 打开文件

[mysqld] 在这个文件后面追加两句话 如下：

long_query_time = 5;

(1)

--------查询超过阀值的SQL： show global status like '%slow_queries%'

--------慢查询的sql被记录在了日志中，因此可以通过日志 查看具体的慢SQL

cat /var/lib/mysql/localhost-slow.log

(2)

-------通过mysqldumpslow工具查看慢SQL，可以通过一些过滤条件，快速查找出需要定位的慢SQL

mysqldumpslow --help

s: 排序方式 r: 逆序 l: 锁定时间 g: 正则匹配模式

----获取返回记录最多的3个sql

mysqldumpslow -s r -t 3 /var/lib/mysql/localhos-slow.log

----获取访问次数最多的3个sql

mysqldumpslow -s c -t 3 /var/lib/mysql/localhos-slow.log

----按照时间排序，前10条包含lefr join查询语句的SQL

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhos-slow.log