MySQL查询性能优化总结

MySQL查询性能优化总结

查询执行路径

1. 客户端发送一条查询给MySQL服务器
2. 服务器先检查缓存，如果命中了缓存，则立刻返回缓存中的结果，否则进行下一阶段
3. 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
5. 将结果返回给客户端

优化数据访问

不要向数据库请求不需要的数据，例如多余的数据行、多余的字段、多表关联返回所有列、多次取重复数据。

MySQL使用如下三种方式应用WHERE条件，从好到坏以此为：

• 在索引中使用WHERE条件过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录，直接从索引中过滤不需要的记录并返回命中的结果，这是在MySQL服务层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件(在Extra列中出现Using Where)。这在MySQL服务层完成，MySQL需要先从数据表读出记录然后过滤。

重构查询方式

• 将复杂查询拆分成多个不同功能的简单查询。现在不用受限于网络通信、带宽等因素，且MySQL的连接和断开都是轻量级的，所以运行多个小查询已经很容易，但具体业务还是要多实践。

• 切分查询。将一个大的查询切分成每个查询功能都一样的小查询。例如定期清楚大量数据时，如果用大查询则可能需要一次锁住很多数据、占满整个事物日志、耗尽系统资源、阻塞很多小但重要的查询，这时我们则可以切分下大SQL。

  -- 一次执行大的查询
  DELETE FROM notes WHERE createdAt < DATE_SUB(NOW(), INTERVAL 3 MONTH)
  -- 多次执行同样功能的小查询,可以使用存储过程写循环，也可以在业务中做循环，下面示例是在业务中循环的
  -- 这样分批次删除，则可以减轻服务器的压力
  const rows_affected = 0
  do{
  rows_affected = do_query("DELETE FROM notes WHERE createdAt < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
  } WHERE rows_affected > 0
  

• 分解关联查询。很多对性能要求较高的应用都会对关联应用进行分解。

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

  可以分解成下面的SQL。

  SELECT * FROM tag WHERE tag='mysql';
  SELECT * FROM tag_post WHERE tag_id=1234;
  SELECT * FROM post WHERE post.id IN (123,456,234,789);
  

  分解的好处：

  • 让缓存的效率更高，许多应用程序可以缓存单表查询结果对象
  • 执行单个查询，可以减少锁的竞争
  • 在应用层关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
  • 查询本身效率也可能会提升
  • 可以减少冗余记录的查询

查询执行基础

1. MySQL客户端/服务器通信协议

   客户端与服务器是“半双工”形式，在任意时刻，要么由S向C发送数据，要么由C向S发送数据，这两个动作不能同时发生。这也导致当C向S发送超长查询语句时，需要关注max_allowed_packet参数，S向C响应大量数据时，不仅数据库服务器需要占用大量时间计算和大量内存来保存结果，一次性返回给系统服务器，还会占用系统服务器的大量内存，而且C必须接收完，才能再释放这条查询所占用的资源，所以通常的做法是再应用系统中通过流查询，一部分一部分的接收数据。

查询优化

1. 关联子查询，WHERE后IN和EXISTS的选择，NOT IN和 NOT EXISTS的选择

   外表数据集大，内表查询数据集小，使用IN，因为MySQL5.7默认200条数据内，IN会使用索引

   外表数据集小，内表查询数据集大，使用EXISTS，MySQL的嵌套循环优化优势更大

   NOT IN不会使用索引，NOT EXISTS子查询会使用到索引，无论外表大还是内表大NOT EXISTS效率都比NOT IN高。但世事无绝对，且关乎MySQL版本问题，遇到和类问题，还是需要多手动测试。

2. UNION的限制

   如果UNION的各个子句能够根据LIMIT只取部分结果集，或者希望先排好序再合并结果集。我们可以先排好各个子句的顺序并且取限制条数，而不用先合并多个子句，再排序取LIMIT条数。这样可以避免UNION生成一个很大的数据集中间表。

   -- 如果actor表有1000条数据，customer有1000条数据，则会生成2000条临时表的数据，但我们却只需要20条数据
   (
   SELECT first_name, last_name
   FROM actor 
   ORDER BY last_name
   )
   UNION ALL
   (
   SELECT first_name, last_name
   FROM customer 
   ORDER BY last_name
   )
   LIMIT 20
   

   减少临时表数据的SQL

   -- 这样临时表就只用存储40条数据了
   (
   SELECT first_name, last_name
   FROM actor 
   ORDER BY last_name
   LIMIT 20
   )
   UNION ALL
   (
   SELECT first_name, last_name
   FROM customer 
   ORDER BY last_name
   LIMIT 20
   )
   LIMIT 20
   

上面只是知识点的梳理，后期项目中遇到典型的优化案例，我会持续更新进来。