数据库优化一般要执行以下几个步骤
- 观察，至少跑1天，看看生产的慢SQL情况
- 开启慢查询日志，设置阀值，并将它抓取出来
- explain+慢SQL分析
- show profile查询SQL在MySQL服务器里面执行的细节和生命周期
- 数据服务器的参数调优
永远小表驱动大表
当B表的数据小于A表的数据集时，用in由于exists。
select * from A where id in (select id from B)
当B表的数据大于A表的数据集时，用exists由于in。
select * from A where exists (select 1 from B where B.id = A.id)
ORDER BY关键字优化
- ORDER BY子句，尽量使用index方式排序，避免使用fileSort方式排序
ORDER BY语句使用索引最左前列
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列 - 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
- 如果不在索引列上，filesort有两种算法：1.单路排序2.双路排序
索引为key 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 d /*d不是索引的一部分*/
WHERE a in(...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/
提高ORDER BY速度
- ORDER BY时select*是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是：
1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序
1.2 两种算法的数据都可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是单路排序算法的风险会更大一些，所以要提高sort_buffer_size - 尝试提高sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然要根据系统的能力去提高，因为这个参数针对每个进程 - 尝试提高max_length_for_sort_data
提高这个参数，会增加用改进算法的概率。但是如果设太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用效率
GROUP BY关键字优化
- GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- WHERE高于HAVING，能卸载WHERE限定的条件就不要用HAVING限定了
