mysql8.0千万数据量性能优化指南（三）：查询优化

参考：
官方文档：优化sql语句 https://dev.mysql.com/doc/refman/8.0/en/statement-optimization.html

一、一条查询语句的执行流程

在处理查询任务时，了解查询的执行流程有助于优化其性能，如果能减少每个流程中不必要的操作，那么查询时间也会提高很多。本篇主要针对执行查询计划部分优化 。
通常来说，一条查询语句的执行过程包括以下几个步骤：
1. 客户端发送查询
客户端通过连接器（如 JDBC、ODBC 或 MySQL 原生连接器）向 MySQL 服务器发送一个 SQL 查询。

2. 解析和预处理
MySQL 服务器接收到查询后，首先对查询进行解析。解析器将查询转换成解析树，并检查语法错误。

3. 查询优化器
解析树被传递给查询优化器。优化器负责生成最优的查询执行计划。它会考虑各种可能的执行路径，并选择成本最低的一条。优化器会考虑以下因素：使用索引、表的连接顺序、子查询优化、其他优化技术（如分区修剪、索引覆盖等）。

4. 查询执行计划生成
优化器生成查询执行计划后，将其传递给查询执行引擎。

5. 查询执行引擎
执行引擎根据生成的执行计划执行查询。它负责：访问存储引擎、从表中读取数据、执行连接、过滤、排序和分组操作、生成最终的结果集。

6. 返回结果给客户端
执行引擎将结果集返回给客户端。如果结果集很大，MySQL 会逐步返回数据，以避免内存占用过多。

7. 关闭连接（如果需要）
查询执行完成后，客户端可以选择关闭连接。

二、优化特定查询

优化特定查询可以显著提高数据库的性能。以下是一些常见的查询优化方法和具体示例。

1. 优化 COUNT()

COUNT() 函数有两种主要作用：统计某列值的数量和统计行数。

• COUNT(列名或者列的表达式)：统计这个表达式有值的行数。
• COUNT(*)：直接统计行数。

简单优化：
如果想统计同一列不同值的数量，建议将所有统计写在一个查询中，而不是分开多个查询。这样可以减少数据库扫描的次数，提高效率。

示例：

SELECT COUNT(IF(color = 'blue', 1, NULL)) AS blue_count,
       COUNT(IF(color = 'red', 1, NULL)) AS red_count
FROM table_name;

使用近似值：
在一些业务场景中，精确的统计值并不重要，可以使用近似值来代替，减少资源消耗。

• 使用 EXPLAIN 估算行数：

  EXPLAIN SELECT * FROM table_name;
  

  通过 rows 字段估算总行数。

缓存 COUNT：
如果需要精确的统计值并且要求快速响应，可以自己维护 COUNT 值，存到数据库表中或者缓存中。然而，这会增加系统复杂性。

• 示例：使用触发器维护计数，当user表插入或删除时更新user_counts表：

  CREATE TABLE user_counts (
      id INT PRIMARY KEY,
      count INT NOT NULL
  );
  
  DELIMITER //
  
  CREATE TRIGGER user_insert AFTER INSERT ON user
  FOR EACH ROW
  BEGIN
      UPDATE user_counts SET count = count + 1 WHERE id = 1;
  END //
  
  CREATE TRIGGER user_delete AFTER DELETE ON user
  FOR EACH ROW
  BEGIN
      UPDATE user_counts SET count = count - 1 WHERE id = 1;
  END //
  
  DELIMITER ;
  

2. 优化 LIMIT OFFSET

使用 LIMIT OFFSET 进行分页操作时，当偏移量很大时，性能会显著下降。一个优化方法是使用覆盖索引查出所需的行，然后再做连接操作查出所需的数据。

示例：

-- 假设有一个带有索引的表 users
-- 索引 (id)
SELECT id FROM users ORDER BY id LIMIT 10000, 10;

-- 使用主键连接获取完整数据 使用索引(id)
SELECT users.*
FROM users
JOIN (
    SELECT id FROM users ORDER BY id LIMIT 10000, 10
) AS temp USING (id);

3. 优化 JOIN

确保 ON 或者USING的列上有索引。在创建索引时考虑连接顺序，在被连接表上索引。
示例：

CREATE INDEX idx_b_c ON B(c);

SELECT A.*, b.* 
from A LEFT JOIN B ON A.c = B.c 

三、其他通用优化方法

1. 不要请求不必要的行和列
避免使用 SELECT *，只选择需要的列，减少数据传输量。

2. 不要重复查询相同数据
将常用的数据缓存到应用层，避免重复查询。

3. 衡量一个大查询还是多个简单查询
有时将一个复杂查询分解为多个简单查询可以更高效。

4. 分解复杂的连接查询
将复杂的连接查询分解为多个步骤，可以减少中间结果集的大小，提高性能。