MySQL调优

哪些sql需要优化

要优化sql，首先要知道哪些sql需要优化，两种场景：

• 在开发阶段直接进行优化；
• 在生产阶段通过慢查询日志分析；

下面说下慢查询方式，有以下关键几步：

1. 开启慢查询：set global slow_query_log = on
2. 指定慢查询日志目录：set global slow_query_log_file='/var/lib/mysql/slow.log'
3. 指定慢查询时间阈值：set global long_query_time = 0.3
4. 分析工具：mysqldumpslow -s -r -t 10 slow-mysql.log
5. 查看慢查询日志相关参数：show variables like 'slow_query%';

创建索引

找出需要优化的SQL后，我们使用explain关键字分析SQL执行计划，如果结果里key是null，说明这条SQL没有使用索引，那我们可以为它创建索引；

创建索引优先选择覆盖索引（通过索引就可以查询到所有要查询的字段），覆盖索引可以减少回表的次数，从而减少树的搜索次数，提高查询效率，如果explain分析结果中Extra字段中有Using Index说明使用了覆盖索引；

另一个就是联合索引，遵循最左前缀原则，我们可以通过调整联合索引的顺序来少维护索引；

索引下推
而且MySQL5.6对联合索引做了索引下推优化，它可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，来减少回表次数；

本来根据索引拿到一个个主键，再判断条件，索引下推直接判断索引的字段值是否满足条件，减少获取到的主键，就减少了回表次数；

MRR（Multi-Range Read Optimization）

如果我们使用的存储介质是机械硬盘的话，还可以对索引再做一个优化，因为机械硬盘是很怕随机读写的；

所以我们可以将Mysql的MRR打开，它可以在回表之前，把要查询的Id在一个buffer中做一个排序操作，将原来的随机操作转换成顺序操作；如果explain分析结果中Extra字段中有Using MRR说明开启了MRR；

开启MRR：set optimizer_switch='mrr=on'

普通索引

还有一点是：如果是写多读少的场景，并且我们的业务代码已经保证了数据的唯一性，那我们可以考虑使用普通索引；

因为普通索引可以使用change buffer的，change buffer可以把我们的写操作给缓存下来，在我们读的时候，再进行一个merge的操作，可以提高数据写入的速度和内存命中率；

sql语句优化

还有就是，如果这个SQL已经走索引了，查询效率还是很慢，我们就要考虑是不是我们的SQL写的有问题，比如：

• 我们可能在索引列上使用了表达式或函数。
• 或者是使用了Not in或<>（不等于），因为它们不能使用索引，可以使用left join替代not in括号里的查询子句。
• 又或者是使用like %开头的模糊查询，也是不能使用索引的，可以使用INSTR(str,'substr')方法替代like。

还有就是特定场景的优化，比如说：

• 对大批量数据的修改，我们可以分批处理，防止大事务。
• 对一些数据统计的操作使用汇总表，将要统计的数据进行定时汇总并记录到表中，以备后续查询使用。