查询性能优化
理解查询执行的过程
- 客户端发送一条查询给服务器
- 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一个阶段
- 服务器进行SQL解析、预处理，再由优化器生成对应的执行计划
- MySQL根据优化计划，调用存储引擎的API来执行查询
- 将结果返回给客户端
优化数据访问
① 确认应用程序是否在检索大量超过需要的数据；
② 确认MySQL服务器层是否在分析大量超过需要的数据行。
是否向数据库请求了不需要的数据
- 查询不需要的记录: 有时候我们查询数据并不需要查询所有的数据，这个时候可以在查询后加上LIMIT，以限制查询结果的数目。
- 查询所有列: 取出所有列，会让优化器无法完成索引覆盖扫描这类优化，还会带来额外的I/O、内存和CPU消耗。应避免
SELECT *的写法，只取出需要使用到的列。 - 重复查询相同的数据: 当需要重复查询相同数据时，应将相同的数据缓存起来，这样可以提高系统性能。
MySQL是否在扫描额外的记录
判断MySQL查询开销的三个重要指标：
- 响应时间
- 扫描的行数
- 返回的行数
它们大致反映了MySQL在内部执行查询时需要访问多少数据，并大概推算出查询运行的时间。
需要注意，响应时间是服务时间和等待时间之和。
重构查询
- 合适地选择一次重复的查询还是多个简单的查询
- 将较大的语句切分为小的语句多次执行，例如，删除很多的数据记录，可以拆分为多次小的删除（两次删除相隔一段时间，也可以降低服务器压力）
- 将关联查询分解为多个单表查询。可以提高缓存效率、减少锁的竞争等，很多时候将关联查询放到应用程序中将会更加高效
特定类型查询的优化
优化COUNT()
- COUNT()用于统计某一个列值的数量。并且要求列值是非空的（不统计NULL）。使用COUNT()统计行数时，最好使用
COUNT(*)，性能比较好，意义清晰。 - MyISAM存储引擎的
COUNT(*)并不总是非常快的，只有当没有任何where条件时，才会非常快，因为此时不需要实际地去计算结果的行数。有时可以简单地优化一下，例如：需要查询所有ID大于5的城市数，可以用全部行数减去ID<=5的城市数，从而缩小需要扫描的记录行数。
优化关联查询
- 确保ON或者USING字句中的列上有索引。
- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列。
优化子查询
- 子查询尽量使用关联查询代替
优化LIMIT分页
当LIMIT的偏移量很大时，例如LIMIT 1000, 20，这时MySQL需要查询1020条记录然后只返回20条数据，这样代价很高。
- 解决方法是：尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作，再返回所需要的列。
- 另一个解决方法是：使用书签保存上次取记录的位置，下次从这个位置开始即可，例如：
SELECT * FROM user LIMIT 20，如果前面语句返回的是1~20，下一页的查询则为：SELECT * FROM user WHERE user_id>20 LIMIT 20，该做法可行是因为主键user_id是单调递增的。
优化UNION查询
- 除非确实需要消除重复行，否则一定要使用UNION ALL。因为单独使用UNION，MySQL会对整个临时表的数据做唯一性检查，造成不必要的性能消耗。