当前位置： 首页 > article >正文

MySQL-慢SQL解析及调试分析思路

article 2025/2/21 19:40:23

MySQL-慢SQL解析及调试分析思路

一、原SQL举例

SELECT 'activation' AS DATA_TYPE,
       TCB.CUSTOMER_BUSINESS_ID AS customerBusinessId,
       TCB.POTENTIAL_CUSTOMERS_ID as potentialCustomerId,
       TCB.CREATED_AT as createdAt,
       TCB.CLUE_TYPE as clueType,
       TCB.CLUE_STATUS as clueStatus,
       TCB.CLUE_SOURCE_ID as clueSourceId,
       CS.CLUE_LEVEL as clueLevel,
       CI.FIRST_ID AS inteBrand,
       CI.SECOND_ID AS inteCarSeries,
       CI.THIRD_ID AS inteCarType,
       CI.FOURTH_ID AS inteYearStyle,
       CI.MATERIAL_ID AS INTE_COLOUR,
       CI.FIRST_ID as firstId,
       CI.SECOND_ID as secondId,
       CI.THIRD_ID as thirdId,
       CI.FOURTH_ID as fourthId,
       CI.FIVE_ID as fiveId,
       TCB.TEMPERATURE as temperature,
       TCB.INITAIL_LEVEL as initailLevel,
       TCB.INTENT_LEVEL as intentLevel,
       TCB.CREATED_BY as createdBy,
       TCB.MANUFACTURER_ISSUE AS IS_DOWN,
       TCB.MANUFACTURER_ISSUE_DATE AS ISSUED_DATE,
       TCB.CONSULTANT_TELE as consultantTele,
       TCB.MARKET_ACTIVITY as marketActivityId,
       TCB.CONSULTANT as consultant,
       TCB.REMARK as remark,
       tba.COMMIT_TIME AS applyDate,
       tba.AUDIT_DATE,
       (
       case
         when TCB.SHOP_NUMBER>0 THEN 10041001
         ELSE 10041002
       END) AS isArrived,
       (
       case
         when TCB.TEST_DRIVE_TIME>0 THEN 10041001
         ELSE 10041002
       END) AS isTestDrive,
       (
       CASE
         WHEN TCB.CLUE_STATUS=70001001 THEN 0
         ELSE 1
       END) AS IS_AUDITED
  FROM tt_customer_business TCB
  -- FORCE INDEX (`IDX_TCB_ETL_02`)
  LEFT join tt_business_approval tba on tba.CUSTOMER_BUSINESS_ID=TCB.CUSTOMER_BUSINESS_ID and tba.BUSINESS_TYPE in(70821001,70821002) and tba.AUDIT_RESULT=70641001 and tba.AUDIT_STATUS=10041001
  LEFT JOIN TM_CLUE_SOURCE CS ON TCB.CLUE_SOURCE_ID=CS.ID
  LEFT JOIN TT_CLUE_INTENT CI ON TCB.CUSTOMER_BUSINESS_ID=CI.CUSTOMER_BUSINESS_ID AND CI.IS_MAIN_INTENT=10041001
 WHERE TCB.IS_DELETED=0
   AND tcb.OWNER_CODE='CDC'
   AND TCB.CLUE_STATUS in(70001015,70001003)
   AND TCB.INTENT_LEVEL=15191006
 GROUP BY TCB.CUSTOMER_BUSINESS_ID
 ORDER BY tba.AUDIT_DATE desc
 LIMIT 0,10

二、SQL 解析

这段 SQL 查询的目的是从多个表中提取数据，特别是围绕客户业务数据进行筛选和聚合。

以下是对该 SQL 的解析和优化调试分析：

  1. SELECT 列表：

    • 从 tt_customer_business 表中提取了客户业务相关字段，如 CUSTOMER_BUSINESS_ID、POTENTIAL_CUSTOMERS_ID 等。

    • 从 tt_business_approval 表中提取了 COMMIT_TIME 和 AUDIT_DATE。

    • 从 TM_CLUE_SOURCE 表中提取了 CLUE_LEVEL。

    • 从 TT_CLUE_INTENT 表中提取了多个意图相关字段，如 FIRST_ID、SECOND_ID 等。

    • 使用了多个 CASE 表达式来生成字段 isArrived、isTestDrive 和 IS_AUDITED。

  2. FROM 和 JOIN：

    • 主表是 tt_customer_business（TCB）。

    • 左连接 (LEFT JOIN) tt_business_approval（tba），条件是：

      • tba.CUSTOMER_BUSINESS_ID = TCB.CUSTOMER_BUSINESS_ID

      • tba.BUSINESS_TYPE 在 (70821001, 70821002) 中。

      • tba.AUDIT_RESULT = 70641001 和 tba.AUDIT_STATUS = 10041001。

    • 左连接 TM_CLUE_SOURCE（CS），按 CLUE_SOURCE_ID 匹配。

    • 左连接 TT_CLUE_INTENT（CI），按 CUSTOMER_BUSINESS_ID 匹配，并且 IS_MAIN_INTENT = 10041001。

  3. WHERE 条件：

    • TCB.IS_DELETED = 0：过滤未删除的记录。

    • TCB.OWNER_CODE = 'CDC'：过滤特定所有者的记录。

    • TCB.CLUE_STATUS 在 (70001015, 70001003) 中：过滤特定状态。

    • TCB.INTENT_LEVEL = 15191006：过滤特定意图级别。

  4. 分组（GROUP BY）：

    • 按 TCB.CUSTOMER_BUSINESS_ID 分组，但没有聚合函数，这可能导致非标准行为（MySQL 中分区查询的隐式排序）。

  5. 排序（ORDER BY）：

    • 按 tba.AUDIT_DATE 降序排序。

  6. 分页（LIMIT）：

    • 提取前 10 条记录。

三、优化调试分析

1. 性能瓶颈分析

  • 索引缺失：

    • 主表 tt_customer_business 上没有为 IS_DELETED、OWNER_CODE、CLUE_STATUS 和 INTENT_LEVEL 创建索引，导致过滤效率低下。

    • 左连接的表 tt_business_approval 和 TT_CLUE_INTENT 上的连接字段（如 CUSTOMER_BUSINESS_ID）可能没有索引，导致连接效率低下。

  • 不必要的列计算：

    • CASE 表达式中的逻辑虽然简单，但如果数据量大，多次调用可能会增加计算成本。

  • 分组和排序的组合：

    • GROUP BY 和 ORDER BY 同时出现时，MySQL 可能需要额外的临时表或文件排序操作，这会增加内存和 I/O 开销。

2. 优化建议

以下是针对该 SQL 的优化建议：

（1）添加索引

  • 在 tt_customer_business 表上添加组合索引：

    sql复制

    ALTER TABLE tt_customer_business
ADD INDEX idx_is_deleted_owner_clue_status_intent_level (
  IS_DELETED,
  OWNER_CODE,
  CLUE_STATUS,
  INTENT_LEVEL
);

  • 在 tt_business_approval 表上添加组合索引：

    sql复制

    ALTER TABLE tt_business_approval
ADD INDEX idx_customer_business_id_business_type_audit_result_status (
  CUSTOMER_BUSINESS_ID,
  BUSINESS_TYPE,
  AUDIT_RESULT,
  AUDIT_STATUS
);

  • 在 TT_CLUE_INTENT 表上为 CUSTOMER_BUSINESS_ID 和 IS_MAIN_INTENT 添加索引：

    sql复制

    ALTER TABLE TT_CLUE_INTENT
ADD INDEX idx_customer_business_id_is_main_intent (
  CUSTOMER_BUSINESS_ID,
  IS_MAIN_INTENT
);

（2）优化查询逻辑

  • 避免使用 GROUP BY 和 ORDER BY 的组合，可以通过覆盖索引查询减少不必要的排序。例如：

    sql复制

    CREATE INDEX idx_tba_audit_date ON tt_business_approval (CUSTOMER_BUSINESS_ID, AUDIT_DATE);

  • 将查询拆分为多个子查询，先过滤数据，再进行连接。例如：

    sql复制

    WITH FilteredTCB AS (
  SELECT
    TCB.CUSTOMER_BUSINESS_ID,
    TCB.POTENTIAL_CUSTOMERS_ID,
    ... -- 其他需要的字段
  FROM tt_customer_business TCB
  WHERE
    TCB.IS_DELETED = 0
    AND TCB.OWNER_CODE = 'CDC'
    AND TCB.CLUE_STATUS IN (70001015, 70001003)
    AND TCB.INTENT_LEVEL = 15191006
)
SELECT /* 主查询逻辑 */
FROM FilteredTCB
LEFT JOIN ...;

（3）避免全表扫描

  • 检查表的大小和数据分布，如果数据量大，可以考虑分区表或垂直拆分表。

  • 如果某些条件是硬编码的值（如 70821001、70641001），可以将这些值提取为枚举类型或常量。

（4）利用覆盖索引

  • 确保查询使用覆盖索引，即查询所需的所有字段都包含在索引中。例如，在 tt_business_approval 表上为 CUSTOMER_BUSINESS_ID 和 AUDIT_DATE 创建索引，可以直接在索引中完成排序。

（5）测试和监控

  • 使用 EXPLAIN 分析查询执行计划，确保优化后的查询使用了正确的索引。

  • 监控查询的性能指标，如查询时间、I/O 次数和内存使用情况。

优化后的 SQL 示例

以下是优化后的 SQL 查询示例：

sql复制

SELECT TCB.CUSTOMER_BUSINESS_ID AS customerBusinessId,
       TCB.POTENTIAL_CUSTOMERS_ID AS potentialCustomerId,
       TCB.CREATED_AT AS createdAt,
       TCB.CLUE_TYPE AS clueType,
       TCB.CLUE_STATUS AS clueStatus,
       ... -- 其他字段
       (
         CASE
           WHEN TCB.SHOP_NUMBER > 0 THEN 10041001
           ELSE 10041002
         END
       ) AS isArrived,
       (
         CASE
           WHEN TCB.TEST_DRIVE_TIME > 0 THEN 10041001
           ELSE 10041002
         END
       ) AS isTestDrive,
       (
         CASE
           WHEN TCB.CLUE_STATUS = 70001001 THEN 0
           ELSE 1
         END
       ) AS IS_AUDITED
FROM tt_customer_business TCB
LEFT JOIN tt_business_approval tba
  ON tba.CUSTOMER_BUSINESS_ID = TCB.CUSTOMER_BUSINESS_ID
  AND tba.BUSINESS_TYPE IN (70821001, 70821002)
  AND tba.AUDIT_RESULT = 70641001
  AND tba.AUDIT_STATUS = 10041001
LEFT JOIN TM_CLUE_SOURCE CS
  ON TCB.CLUE_SOURCE_ID = CS.ID
LEFT JOIN TT_CLUE_INTENT CI
  ON TCB.CUSTOMER_BUSINESS_ID = CI.CUSTOMER_BUSINESS_ID
  AND CI.IS_MAIN_INTENT = 10041001
WHERE TCB.IS_DELETED = 0
  AND TCB.OWNER_CODE = 'CDC'
  AND TCB.CLUE_STATUS IN (70001015, 70001003)
  AND TCB.INTENT_LEVEL = 15191006
ORDER BY tba.AUDIT_DATE DESC
LIMIT 0, 10;

总结

通过以下优化措施可以显著提升查询性能：

  1. 添加合适的索引。

  2. 避免全表扫描和不必要的列计算。

  3. 利用覆盖索引和合理的查询逻辑。

  4. 监控查询性能并根据实际情况调整。

四、扩展-如何减少SQL查询中的计算成本？

减少 SQL 查询中的计算成本可以从多个方面入手，以下是一些有效的优化策略：

1. 合理使用索引

索引是数据库中用于快速查询的一种数据结构。以下是一些使用索引的建议：

  • 建立合适的索引：

    • 在经常用于 WHERE、JOIN、ORDER BY 和 GROUP BY 的列上创建索引。例如，如果经常查询 SELECT * FROM users WHERE city = 'New York';，则可以在 city 列上建立索引。

    • 使用组合索引（复合索引），其顺序应遵循过滤条件（WHERE）、排序（ORDER BY）和分组（GROUP BY）的优先级。例如，CREATE INDEX idx_user ON users(city, age); 可以加速 WHERE city = ? AND age > ? 的查询。

  • 避免过度索引：

    • 索引会增加写操作（如插入、更新、删除）的开销，因为每次写操作都需要更新索引。因此，不要为不常用的列建立索引。

    • 避免对查询不频繁的列或数据量小的表建立索引。

  • 使用覆盖索引：

    • 覆盖索引是指查询的所有列都可以通过索引来满足，而无需访问表数据。例如，如果有一个索引 (user_id, email)，查询 SELECT user_id, email FROM users WHERE user_id = 1; 可以直接通过索引获取数据，无需访问表。

2. 优化查询语句

  • 避免使用 SELECT *：

    • 使用特定列名代替 SELECT *，可以减少返回的数据量，降低网络传输和内存压力。例如，SELECT id, name FROM users WHERE age > 30; 比 SELECT * FROM users WHERE age > 30; 效率更高。

  • 使用子查询代替连接：

    • 在某些情况下，使用子查询可以减少中间结果集的大小，从而降低计算成本。例如，SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');。

  • 合理使用函数和表达式：

    • 避免在 WHERE 子句中使用函数或表达式（如 date(column) 或 column + 1），这会导致数据库无法使用索引。例如，将查询 SELECT * FROM events WHERE DATE(event_time) = '2024-01-01'; 改为 SELECT * FROM events WHERE event_time >= '2024-01-01' AND event_time < '2024-01-02';，可以利用 event_time 列上的索引。

3. 数据分区和分片

  • 数据分区：

    • 将表数据分成多个物理分区，可以加速对特定分区数据的查询。例如，按日期分区（PARTITION BY RANGE (created_at)）可以快速查找指定日期范围内的数据。

  • 数据分片：

    • 将数据分布在多个数据库实例或表中，通过特定的分片键（如用户 ID）来访问数据，从而降低单个实例的查询负载。例如，使用一致性哈希算法将用户数据分片到多个服务器上。

4. 缓存机制

  • 数据库缓存：

    • 利用数据库的查询缓存机制（如 MySQL 的 Query Cache），将频繁查询的数据缓存到内存中，避免重复查询数据库。需要注意的是，MySQL 8.0 已经移除了 Query Cache，可以考虑其他缓存方式。

  • 应用级缓存：

    • 在应用程序中使用缓存（如 Redis 或 Memcached），将查询结果缓存起来，当数据未发生变化时，直接从缓存中读取数据，减少数据库的访问次数。

5. 数据压缩

  • 表压缩：

    • 对数据量大的表进行压缩（如 MySQL 的 InnoDB 压缩表），可以减少存储空间的占用和 I/O 开销，从而提高查询性能。

  • 列存储：

    • 使用列存储的数据库（如 ClickHouse 或 Amazon Redshift），可以对列进行压缩和索引，大幅提高查询性能，尤其适用于数据仓库和分析场景。

6. 查询优化工具

  • 使用执行计划：

    • 使用 EXPLAIN（MySQL）或 EXPLAIN PLAN（Oracle）查看查询的执行计划，分析查询的性能瓶颈。例如，可以发现全表扫描、不合理的索引使用等问题，并据此调整查询或索引。

  • 查询优化器：

    • 合理配置数据库的查询优化器参数，如 MySQL 的 optimizer_switch，可以调整优化器的行为，选择更高效的查询执行计划。

7. 数据库硬件和架构

  • 增加内存：

    • 数据库的内存资源对查询性能至关重要。增加数据库服务器的内存，可以容纳更多的数据到缓存中，减少磁盘 I/O。

  • 优化存储：

    • 使用高速存储设备（如 SSD）或分布式存储系统（如 Ceph），可以提高数据的读写速度，降低查询的 I/O 成本。

  • 分布式数据库：

    • 对于大规模数据，采用分布式数据库（如 TiDB 或 ShardingSphere）和分布式存储（如 Hadoop 的 HDFS），可以将数据分布在多个节点上，通过并行查询提高性能。

8. 定期维护数据库

  • 更新统计信息：

    • 定期更新数据库的统计信息（如表的行数、列的分布），以便查询优化器能够生成更准确的执行计划。

  • 清理过期数据：

    • 删除或归档不再需要的旧数据，可以减少表的大小，提高查询性能。

  • 索引维护：

    • 定期重建索引（如 MySQL 的 ALTER TABLE ... REBUILD INDEX）以消除索引碎片，维护索引的性能。

通过以上方法，可以有效减少 SQL 查询中的计算成本，提高数据库的整体性能。


http://www.kler.cn/a/552931.html

相关文章：

  • 可变列二维数组【C语言】
  • 内网常见问题处理
  • java数据结构_优先级队列（堆）_6.1
  • 开源元搜索引擎SearXNG：使用Docker详细搭建部署与使用
  • 【OS安装与使用】part4-ubuntu22.04安装anaconda
  • 【R语言】绘图
  • ONNX Runtime 与 CUDA、cuDNN 的版本对应
  • “三次握手”与“四次挥手”：TCP传输控制协议连接过程
  • 在Kubernetes上部署DeepSeek-R1进行高效AI推理
  • C#```
  • 一文读懂Docker之Docker Compose
  • 论文笔记-WSDM2024-LLMRec
  • 02.19 构造函数
  • MYSQL数据库特殊查询-INFORMATION_SCHEMA
  • 鉴源实验室·智能网联汽车协议数据传输安全分析与防护
  • Word Embeddings
  • 【Linux进程概念】—— 操作系统中的“生命体”，计算机里的“多线程”
  • VSCode 中 Git 添加了多个远端，如何设置默认远端
  • QT C++ new QTableWidgetItem 不需要删除指针
  • Leetcodehot100(链表篇)