MySQL查询优化

小表驱动大表，类似嵌套循环

in 和exists

select * from tb_emp e where e.dept_id in (select id from tb_dept d);
#以上查询语句中，先执行in后面的子查询，所以当tb_dept的记录比tb_emp少时，性能更优，这就是所谓小表驱动大表

select * from tb_emp e exists(select 1 from tb_dept d where e.dept_id=d.id);
#以上查询语句中，先从tb_emp中查询，所以当tb_emp的记录比tb_dept少时，性能更优，这就是所谓小表驱动大表，子查询中的 1  可以替换成 * 或者 ‘X’ 等任意符号。

order by关键字优化

MySQL两种排序方式：文件排序（using file sort）和扫描有序索引排序（using index），第二种性能更好。

MySQL能为排序和查询使用相同的索引

复合索引 a_b_c(a,b,c)

order by能使用索引最前缀
– order by a
– order by a,b
– order by a,b,c
– order by a desc,b desc,c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引
– where a=const order by b,c
– where a=const and b=const order by c
– where a=const and b>const order by b,c

不能使用索引进行排序
– order by a asc,b desc,c desc （排序方式不一致）
– where g=const order by b,c (丢失a索引)
– where a=const order by c (丢失b索引)
– where a=const order by a,d (d不是索引的一部分)
– where a in (…) order by b,c (in是范围查询)

当无法使用索引列时，增大max_length_for_data参数的设置+增大sort_buffer_size参数的设置

group by关键字优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀原则，它的优化基本和order by一致。

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

慢查询日志

慢查询日志指的是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间==超过(严格大于)==阀值的语句，具体运行时间long_query_time（默认为10秒）值的SQL，则会被记录到慢查询日志中。

默认情况下MySQL数据库没有开启慢查询日志，需要手动来设置这个参数，因为开启慢查询日志多少都会带来一定的性能影响，慢查询日志支持将日志记录写入文件。

查看慢查询日志参数：
SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启：set global slow_query_log=1;此设置只针对当前有效

如果要永久生效需要修改MySQL的 my.cnf 配置文件
在[mysqld]下增加或修改参数
slow_query_log=1
slow_query_log_file=/var/lib/mysql/***.log
重启MySQL

如果日志文件名没有给出，系统默认给出一个缺省文件
host_name-slow.log