【详解】MySQL查询性能优化

1. 为什么查询会慢

• 如果把查询当作一个任务，那么它是由一系列子任务完成的
• 如果要优化查询，要么减少子任务，要么减少子任务的执行次数，让子任务更快

使用 Explain 进行分析

• Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询、子查询等
• key : 使用的索引
• rows : 扫描的行数

2.慢查询基础：优化数据访问

• 最基本的问题是：访问的数据太多，不可避免的需要筛选很多数据

步骤

1. 确认应用程序是否检索了大量超过需要的数据。可能返回了过多的行或者列
2. 确认MySQL服务器是否在分析大量超过需要的数据

2.1 是否向数据库请求了不需要的数据

• 有时候查询的数据可能超过了需要，最后被抛弃

案例一：查询不需要的列

• 在页面查询了100条记录，但是页面只能显示10条
• 最有效的方法就是使用 LIMIT

案例二：多表关联返回全部的列
案例三：总是取出全部列

• 避免使用select *

案例四：执行重复的查询

• 很多操作可能会反复的查询数据库的数据
• 最好的操作时查询后，将数据缓存下来，需要的时候从缓存取

2.2 MySQL是否在扫描额外的记录

• 可以检查是否扫描的行数大于返回的行数，这个时候需要考虑使用覆盖索引

3. 重构查询的方式

3.1 切分大查询

• 一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);


rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

• 这样将一次性的删除分担到多次上，减少系统的压力

3.2 分解关联查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。（拼接的工作交给应用程序来做）
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';


# 更改后
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

4. 查询优化器

4.1 查询优化器的工作

• 重新定义关联表的顺序：连接表的顺序可能会改变
• 将外连接转换为内连接：当外连接等价为内连接时
• 优化COUNT()、MIN()、MAX()：索引会帮助优化
• 提前终止查询：如果发现一些特殊条件会终止查询直接返回
• 对列表IN（）比较：如果大量数据查询时，利用的二分查找，查询速度的是O（logn）

4.2 局限性

关联子查询

• 子查询会遍历外表，而不是优先计算子查询
• 建议将子查询变成左外连接，但是不要使用内查询
  
  索引合并优化
• 将多个组合索引优化成一个会提高性能

5. 总结