MySQL查询性能优化

1. 为什么查询速度慢

一个查询是由许多子任务组成的，每个子任务都会消耗一定的时间。优化一个查询，其实就是要优化其子任务。

• 一个查询的生命周期步骤：

  1. 客户端发送一条查询给MySQL服务器

  2. MySQL服务器先检查查询缓存

     • 如果命中缓存，则立刻返回存储在查询缓存中的结果给客户端
     • 否则，进行下一阶段

  3. 解析器进行SQL解析，生成解析树

  4. 预处理器验证权限，确认合法的解析树。

  5. 查询优化器根据解析树进行优化，生成对应的查询执行计划

  6. 查询执行引擎根据查询优化器生成的查询执行计划，调用存储引擎的API接口执行查询

  7. 存储引擎从数据文件中查询相应数据返回给查询执行引擎

  8. 查询执行引擎将存储引擎的API接口返回的结果进行处理

     • 将结果存入查询缓存

     • 将结果返回给客户端

• 查询花费时间的地方包括：

  • 网络

  • CPU计算

  • 生成统计信息

  • 生成执行计划

  • 锁等待（互斥等待）

  • 向底层存储引擎检索数据的调用操作

    • 内存操作
    • CPU操作
    • 内存不足时导致的I/O操作

  • 上下文切换

  • 系统调用

• 遇到的问题可能包括：
  • 不必要的额外操作
  • 某些操作被额外地重复了很多次
  • 某些操作执行的太慢

• 优化查询子任务的思路：
  • 消除其中一些子任务
  • 减少子任务的执行次数
  • 让子任务运行更快

2. 优化数据访问

查询性能低的最基本原因是访问数据太多，可以从两个步骤分析：

1. 确认应用程序是否在检索大量超过需要的数据

   • 访问了太多的行
   • 访问了太多的列

2. 确认MySQL服务器是否分析大量超过需要的数据行

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

• 查询不需要的记录

  查询大量结果然后舍弃不需要的。

  解决方法：查询后面加LIMIT。

• 多表关联时返回全部列

  解决方法：只查询需要的列。

• 总是取出全部列

  解决方法：若没有缓存则避免使用SELECT *。

• 重复查询相同的数据

  如用户评论需要查询用户头像URL。

  解决方法：将数据缓存，需要的时候从缓存中获取。

2. MySQL是否在扫描额外的记录

在确定查询只返回需要的数据后，需要查看查询为了返回结果是否扫描了过多的数据。

衡量查询开销的指标：

• 响应时间

  • 服务时间

  • 排队时间

    • I/O
    • 锁等待（行锁、表锁）

  判断响应时间是否是合理的值（快速上限估计法）

• 扫描的行数和反回的行数

• 扫描的行数和返回的类型

  • 在EXPLAIN语句中的type列反应了访问类型（速度从慢到快、扫描的行数从多到少）：

    • 全表扫描
    • 索引扫描
    • 范围扫描
    • 唯一索引查询
    • 常数引用

  • 一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

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

  • 如果发现查询需要扫瞄大量数据但只返回少数的行，通常可以尝试以下技巧：

    • 使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无需回表获取对应的行就可以返回结果。
    • 改变库表结构，比如使用单独的汇总表。
    • 重写复杂的查询，让MySQL优化器能够以更优化的方式执行查询。

3. 重构查询的方式

• 将查询转化另一种写法返回相同的结果，但性能更好。

• 修改应用代码，用另一种方式完成查询。

  • 在设计查询时考虑是否将一个复杂的查询拆分成多个简单的查询。（视情况而定）

  • 切分查询

    • 删除大量数据时会锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但很重要的查询。（一次删除10000行数据比较高效，能减少删除时锁持有时间）

  • 分解关联查询

    可以每次对一个表进行一次单表查询，然后将结果在应用程序中进行关联。

    1. 优势：

       • 让缓存更高效。

         许多应用程序可以方便地缓存单表查询对应的结果对象。对于MySQL的查询缓存，如果关联中的某个表发生变化，那么无法使用查询缓存；拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。

       • 将查询分解后，执行单个查询可以减少锁的竞争。

       • 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

       • 查询本身效率也可能会有所提升。

         使用IN()代替关联查询，可以让MySQL按照ID顺序进行查询，这样可能比随机的关联更高效。

       • 可以减少冗余记录的查询。

         在应用层做关联查询，意味着对某条记录应用之需要查询一次；在数据库中做关联查询，可能需要重复地访问一部分数据。这样的重构可能会减少网络和内存的消耗。

       • 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。（某些场景中哈希关联效率很高）

    2. 场景：

       • 当应用能够方便地缓存单个查询结果的时候。
       • 当可以将数据分布到不同的MySQL服务器上的时候。
       • 当能够使用IN()的方式代替关联查询的时候。
       • 当查询中使用同一个数据表的时候。

4. 查询执行

使用SHOW FULL PROCESSLIST命令查看当前状态：

• Sleep

  线程正在等待客户端发送新的请求。

• Query

  线程正在执行查询或者正在将结果发送给客户端。

• Locked

  在MySQL服务器层，该线程正在等待表锁。

• Analyzing and statistics

  线程正在收集存储引擎的统计信息，并生成查询的执行计划。

• Copying to tmp table [on disk]

  线程正在执行查询，并且将其结果都复制到一个临时表中：

  • GROUP BY操作
  • 文件排序操作
  • UNION操作

  on disk标记表示MySQL正在将一个内存临时表放到磁盘上。

• Sorting result

  线程正在对结果集进行排序。

• Sending data

  多种情况：

  • 线程可能在多个状态之间传送数据
  • 在生成结果集
  • 在向客户端返回数据

5. 优化特定的查询类型

1. 优化COUNT()查询类型

• 最好使用COUNT(*)

• MyISAM在没有任何WHERE条件下使用COUNT(*)才最快

• 简单的优化：

  • 查找所有ID大于5的城市：

    SELECT COUNT(*) FROM world.city WHERE ID > 5;
    

    通过SHOW STATUS的结果可以看到该查询需要扫瞄大量的行数据。进行优化：

    SELECT (SELECT COUNT(*) FROM world.city) - COUNT(*) FROM world.city WHERE ID <= 5;
    

    这样可以大大减少需要扫描的行数，因为查询优化阶段会将其中的子查询直接当成一个常数来处理。

  • 在同一查询中统计同一个列的不同值得数量：

    SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR NULL) AS red FROM items;
    

• 使用近似值

  • 某些业务场景不需要完全精确的COUNT值，此时可以执行EXPLAIN使用近似值来代替。
  • 尝试删除DISTINCT这样的约束来避免文件排序。.

• 更复杂的查询

  • MySQL层面能做的只有索引覆盖扫描。
  • 可以考虑更改应用架构
  • 可以增加Memcached这样的外部缓存系统。

• 原则：快速、精确、简单实现，三者永远只能满足其二，必须舍掉其中一个。

2. 优化关联查询

• 确保ON或者USING子句中的列上有索引。

  在创建索引时就要考虑到关联的顺序。当A表和B表用c列关联时，如果优化器关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。

• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列。

  这样MySQL才有可能使用索引来优化这个过程。

• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方。

  因为普通关联的地方可能会变成笛卡尔积，不同类型的关联可能会生成不同的结果。

3. 优化子查询

​ 5.6版本以前尽可能使用关联查询。

4. 优化GROUP BY和DISTINCT

• 使用索引是最有效的优化方式。

• 当无法使用索引时：

  GROUP BY使用两种策略：

  • 使用临时表做分组
  • 使用文件排序做分组

  可以通过使用SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照希望的方式运行。

• 如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。

  下面的查询效率不会很好：

  SELECT actor.first_name, actor.last_name, COUNT(*) 
  FROM sakila.film_actor 
  INNER JOIN sakila.actor 
  USING(actor_id) 
  GROUP BY actor.first_name, actor.last_name;
  

  改写成下面的写法效率会更高：

  SELECT actor.first_name, actor.last_name, COUNT(*) 
  FROM sakila.film_actor 
  INNER JOIN sakila.actor 
  USING(actor_id) 
  GROUP BY film_actor.actor_id;
  

  要注意SQL_MODE可能设置禁止在SELECT中直接使用非分组列，此时会用到子查询，导致创建和填充临时表，而子查询中创建的临时表是没有任何索引的。

  SELECT actor.first_name, actor.last_name, c.cnt 
  FROM sakila.actor 
  INNER JOIN (
    SELECT actor_id, COUNT(*), AS cnt 
    FROM sakila.film_actor 
    GROUP BY actor_id 
  ) AS c USING(actor_id);
  

  当使用GROUP BY时，结果集会自动按照分组字段进行排序。如果不关心排序，可使用GROUP BY NULL让MySQL不进行排序。

5. 优化LIMIT分页

问题：偏移量大时会抛弃前面大量的数据。

解决方法：

• 在页面中限制分页的数量

• 优化大偏移量的性能（尽可能使用索引覆盖扫描，而不是查询所有列，然后根据需要关联操作再返回所需的列）

  比如：

  SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
  

  • 通过延迟关联改写为：

  SELECT film_id, description 
  FROM sakila.film 
  INNER JOIN (
    SELECT film_id FROM sakila.film 
    ORDER BY title LIMIT 50, 5
  ) AS lim USING(film_id);    
  

  • 有时也可以将LIMIT查询转换成已知位置查询，让MySQL通过范围扫描获得到对应的结果。如果在一个位置列上有索引，并且预先计算出了边界值，上面的查询就可以改写成：

  SELECT film_id, description 
  FROM sakila.film 
  WHERE position 
  BETWEEN 50 AND 54 
  ORDER BY position;
  

  • 如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。

    首先获取第一组结果：

    SELECT * FROM sakila.rental 
    ORDER BY rental_id DESC LIMIT 20;
    

    假设上面查询返回的主键为10029到10010，那后面的查询就可以从10010开始：

    SELECT * FROM sakila.rental 
    WHERE rental_id < 10010 
    ORDER BY rental_id DESC LIMIT 20;
    

    用这种方式无论翻页到多么后面性能都会非常好。

  • 使用预先计算的汇总表

  • 关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。

6. 优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。

• 需要手动将WHERE、LIMIT、ORDER BY等子句写到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。

• 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这回导致整个临时表的数据做唯一性检查，代价非常高。