2023-一条慢sql引发的思考啊啊啊啊啊啊啊啊啊啊

MySQL
发布日期:   2023-11-15
更新日期:   2024-04-13
文章字数:   4.7k
阅读时长:   17 分
阅读次数:  

[TOC]

一条慢sql引发的思考

1. 引言

慢 SQL 查询是数据库中常见的性能障碍，可能导致系统响应缓慢甚至服务不可用。

刚入职的时候，同事就提醒过我，涉及三四张表的时候，数据量大，尽量不用连表查询，用单表。我最近还真的是遇到了。因为联表查询导致引发的慢sql。

当然，排查和解决慢 SQL 查询问题流程应该是每个后端开发的必备之路。这里以亲身经历总结一波~

2. 慢sql出现的背景

2.1 我遇到的情况

  1. 背景：2023.11.10 周五运维这边收到异常监控严重报警

    您有待处理严重告警
告警严重度：严重
告警状态：触发sls告警
...

  2. 影响：mysql内存使用率达72.80，MySQL的cpu使用率达99.85

  1. 4条慢sql查询响应时间达229.428秒左右

2.2 识别慢查询

  1. 通过运维导出的excel表发现慢sql。还可以通过数据库日志或性能监控工具找到，发现执行时间长或资源消耗大的查询语句。

    SELECT distinct D.id FROM wx_label A 
INNER JOIN wx_label_element_map as B on A.id = B.label_id 
INNER JOIN wx_element_info as C on C.element_id = B.element_id 
INNER JOIN wx_resource_v2 as D on D.relate_id = C.id  
WHERE D.is_del = 0 
AND D.relate_type = 2 
AND A.name LIKE '%Why_Do_We_Celebrate_Thanksgiving%'

有经验的可以排查看到这是涉及四张表，然后来了一个模糊查询！

模糊查询会引发什么问题？（索引失效）

带着问题往后走~

2.3 查询分析和优化(重点)

分析慢sql查询执行计划，确认是否使用了索引和最佳执行路径。

EXPLAIN SELECT distinct D.id FROM wx_label A 
INNER JOIN wx_label_element_map as B on A.id = B.label_id 
INNER JOIN wx_element_info as C on C.element_id = B.element_id 
INNER JOIN wx_resource_v2 as D on D.relate_id = C.id  
WHERE D.is_del = 0 
AND D.relate_type = 2 
AND A.name LIKE '%Why_Do_We_Celebrate_Thanksgiving%';

explain排查关键信息

通过explain执行sql之后，抓住关键字段type，possible_keys，key，rows等

  • D表的type是all，全表扫描，性能最差。
  • possible_keys：可以选择的索引。
  • key：命中索引。D表没有命中索引。
  • rows：扫描行数。

四张表的数据量

SELECT COUNT(*) FROM wx_label; 			   -- 6W  	标签
SELECT COUNT(*) FROM wx_label_element_map; -- 1325W	标签-颗粒中间表
SELECT COUNT(*) FROM wx_element_info;      -- 117W	颗粒表
SELECT COUNT(*) FROM wx_resource_v2;	   -- 278W 	资源表

定位业务代码段

根据慢sql的特征定位具体业务代码段。

// 标签搜索
if (!empty($label)) {
    $whereLabel['D.is_del'] = 0;
    $whereLabel['D.relate_type'] = 2;
    $whereLabel['A.name'] = ['like', sprintf("%%%s%%", $label)];
    $labelModel   = LabelModel::getInstance();
    $labelResList =  $labelModel->field('distinct D.id')
                                ->alias('A')
                                ->join('wx_label_element_map as B on A.id = B.label_id', 'INNER')
                                ->join('wx_element_info as C on C.element_id = B.element_id', 'INNER')
                                ->join('wx_resource_v2 as D on D.relate_id = C.id', 'INNER')
                                ->where($whereLabel)
                                ->select();

发现是用户在通过标签搜索资源的时候，进行了模糊查询。

3.慢sql问题归纳

  1. 问题一：四张表进行连表数据量大，连表查询导致耗时长。颗粒标签表达一千多万条，颗粒表100多w，资源表200多w。
  2. 问题二：模糊查询导致索引失效，没有命中索引。
  3. 问题三：联表查询导致笛卡尔体积增加，查询时间耗时长。

同时MySQL联表查询导致笛卡尔积问题可能会带来严重的性能和数据错误问题：

  1. 性能问题：笛卡尔积会导致结果集数量大幅增加，占用更多的内存和磁盘空间。当数据量巨大时，这可能导致查询时间大幅增加，甚至导致数据库服务器性能下降。
  2. 数据错误：由于笛卡尔积导致了结果集中包含了不正确的数据组合，可能会影响业务逻辑和数据准确性。例如，在进行统计或计算时，错误的数据组合会导致错误的计算结果。
  3. 资源消耗：大量的笛卡尔积结果集可能会消耗数据库服务器的资源，包括 CPU、内存和磁盘空间，从而影响整个数据库系统的稳定性和性能。
  4. 网络传输成本：对于大量笛卡尔积结果，如果需要通过网络传输到客户端，会增加网络开销和传输时间。

4.解决慢sql优化方案选择

方案一：联表改为MySQL单表查询

  • 优点：
    1. 简化处理：使用 MySQL 单表查询可以减少复杂性，特别是如果查询不需要多个表的数据。
    2. 已有基础：如果你的系统已经在 MySQL 上运行，并且对其进行了优化，继续使用单表查询可能更为顺畅。
    3. 较低学习成本：不需要引入新的技术或系统，减少了学习和迁移成本。
  • 缺点：
    1. 限制性：单表查询可能限制了查询的复杂性和灵活性，尤其在需要跨多个表进行复杂联合查询时。
    2. 性能瓶颈：在大数据量或需要复杂计算的情况下，可能会出现性能瓶颈。

方案二：改为ES查询

  • 优点：
    1. 高性能：Elasticsearch 专注于搜索和分析，对于复杂的全文搜索或分析需求提供了高性能。
    2. 分布式支持：具备横向扩展能力，能够处理大规模数据和并发查询。
    3. 搜索功能：适用于复杂的搜索功能，比如全文搜索、聚合查询等。
  • 缺点：
    1. 学习成本：可能需要学习新的技术和工具，增加了迁移和开发的学习成本。
    2. 数据同步与维护：引入 Elasticsearch 需要额外的数据同步和维护工作，可能增加了系统复杂性。

选择方案一：理由是，方案二的Elasticsearch 需要进行数据备份和同步等。目前是还没进行处理的，后续可以改进用elasticsearch查询。

目前是后台系统并发量屈指可数，可以满足当前业务需求的，如果是前台并发大的话，和对于长久的方案选择还是用es的。

转向 MySQL 单表查询。这种方法确实在处理简单查询时更为直接和方便，尤其是在当前紧急情况下，避免了引入额外的数据备份和同步工作。

优点是：

  1. 简化流程：MySQL 单表查询不需要你处理额外的数据同步和备份任务。这意味着你可以专注于优化数据库结构和查询语句，提高查询性能而不必处理额外的复杂性。
  2. 已有基础：如果你的系统已经建立在 MySQL 基础上，并且已经对其进行了优化和维护，那么继续使用 MySQL 可能更为顺畅。你可能已经有了熟练的数据库管理员和开发团队，他们对 MySQL 的工作原理和最佳实践非常熟悉。
  3. 技术迁移成本低：转向 Elasticsearch 可能需要一定的学习和迁移成本。通过先使用 MySQL 单表查询，你可以在业务稳定的同时逐步学习和准备迁移到 Elasticsearch。

5.解决优化慢sql

改为单表查询优化。

// 标签搜索
if (!empty($label)) {
    $labelWhere = ['name'=>['like', "{$label}%"]];
    $labelIds = LabelModel::getInstance()->where($labelWhere)->getField('id', true);
    if(empty($labelIds)) {
        $this->ajaxReturn(['code' => 0, 'message' => '搜索无结果', 'data' => []]);
    }

    $labelMapWhere = ['label_id'=>['in', $labelIds]];
    $eleIds = LabelElementMapModel::getInstance()->where($labelMapWhere)->getField('element_id', true);
    if(empty($eleIds)) {
        $this->ajaxReturn(['code' => 0, 'message' => '搜索无结果', 'data' => []]);
    }

    $eleInfoWhere = ['element_id'=>['in', $eleIds]];
    $eleInfoIds = ElementInfoModel::getInstance()->where($eleInfoWhere)->getField('id', true);
    if(empty($eleInfoIds)) {
        $this->ajaxReturn(['code' => 0, 'message' => '搜索无结果', 'data' => []]);
    }
    
    $labelResWhere = [
        'relate_type'  => ResourceV2Model::RELATE_TYPE_ELEMENT_INFO,
        'relate_id'    => ['in', $eleInfoIds],
        'product_line' => $productLine,
        'is_del'       => 0,
    ];
    $labelResIds = ResourceV2Model::getInstance()->where($labelResWhere)->getField('id', true);
    if(empty($labelResIds)) {
        $this->ajaxReturn(['code' => 0, 'message' => '搜索无结果', 'data' => []]);
    }

    $where['A.id'] = ['IN', $labelResIds];
}

6.优化成果

通过单表查询优化之后，观察监控一段时间发现没有出现慢sql了。

同时，这样操作的好处是：

  1. 简化查询：单表查询避免了多个表之间的联接，简化了查询语句的复杂性和执行过程。
  2. 减少数据集大小：单表查询通常会减少结果集的大小，因为它们不会产生笛卡尔积，避免了不必要的数据组合。
  3. 提高查询性能：单表查询往往比联表查询更快速，特别是当联表查询涉及大量数据时，单表查询更有效率。
  4. 降低资源消耗：单表查询可能会减少数据库服务器的资源消耗，包括 CPU、内存和磁盘空间。
  5. 简化维护：单表查询使数据查询和维护更为简单，不涉及多表联接和复杂的连接条件。

7.总结反思

MySQL 慢查询优化是一个不断迭代的过程，包含多个步骤和策略。同时并不是所有东西都往高级技术去靠，还得结合具体业务场景。脱离业务，谈技术也是纸上谈兵。这也是为什么没有选择es查询而选择单表优化MySQL进行简单化。

  1. 尽量采取单表查询，如果是要联表，要根据小表连大表的原则进行连接。
  2. 业务需求可能会不断变化，需要根据新需求和数据特性不断对慢sql进行调整优化策略。

总结一下具体优化步骤吧：

  1. 识别慢查询：首先要能够准确定位到慢查询，使用 MySQL 的慢查询日志或性能监控工具来捕获潜在的慢查询语句。
  2. 分析查询执行计划：使用 EXPLAIN 或其他查询分析工具来查看查询的执行计划，确认是否使用了索引、优化了执行路径。
  3. 优化查询语句：根据执行计划的分析结果，重写查询语句以提高效率，减少数据扫描和不必要的操作。
  4. 优化索引：确保表的索引被正确地设计和使用，合理地创建、删除或更新索引以加速查询。
  5. 定期维护：定期执行数据库维护任务，包括索引重建、统计信息更新等，保持数据库性能。

反思和改进：

  1. 查询日志和监控：审查慢查询日志和监控数据，了解优化前后的性能变化。
  2. 查询重构：如果某些查询无法通过索引优化，考虑重构查询，分解成更简单、更优化的查询。
  3. 版本更新和配置调整：时刻关注 MySQL 的版本更新和配置调整，新版本可能提供了更优化的查询优化器或者新特性。
  4. 持续学习和分享：保持学习和了解数据库优化的最新趋势和技术，分享经验和知识，与同事交流合作。
  5. 综合策略：不同场景可能需要不同的优化策略，持续地评估和调整优化策略，以适应不同的应用场景和需求。

最后，来一下经典的八股文hhhhh

八股文

索引失效有哪些？

MySQL索引可能在以下情况下失效：

  1. 不使用索引字段进行查询：如果查询条件中没有使用到索引字段，MySQL 可能会放弃使用索引而进行全表扫描。

    SELECT * FROM table_name WHERE non_indexed_column = 'value';

  2. LIKE 查询的模糊匹配：如果在 LIKE 查询中使用通配符在搜索模式的开头，索引可能失效。

    SELECT * FROM table_name WHERE indexed_column LIKE '%value';

  3. 函数包装索引字段：如果在索引字段上使用了函数，索引可能失效。

    SELECT * FROM table_name WHERE DATE_FORMAT(date_column, '%Y-%m-%d') = '2023-01-01';

  4. 对索引字段进行运算：如果对索引字段进行运算，MySQL 可能无法使用索引。

    SELECT * FROM table_name WHERE indexed_column + 1 = 10;

  5. 数据类型不匹配：在进行比较时，如果查询条件的数据类型与索引字段的数据类型不匹配，可能导致索引失效。

    SELECT * FROM table_name WHERE indexed_column = 10; -- 如果 indexed_column 是字符串类型

  6. 过滤结果过于宽泛：如果查询结果集占据大部分表的数据行，MySQL 可能选择全表扫描而不使用索引。

    SELECT * FROM table_name WHERE indexed_column > 0; -- 过滤结果太宽泛

  7. 表数据量较小：对于较小的表，MySQL 可能会选择不使用索引而进行全表扫描。

  8. 隐式数据类型转换：当查询的数据类型与字段的数据类型不匹配时，MySQL 可能会进行隐式转换，导致索引失效。

    SELECT * FROM table_name WHERE indexed_column = '1'; -- indexed_column 是整数类型

explain是面试常用的八股文了。面试官喜欢问的慢sql如何排查优化，explain关键词等等。

查询优化神器 - explain命令

msql官网：explain https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

EXPLAIN适用于 SELECT、 DELETE、 INSERT、 REPLACE和 UPDATE语句。

EXPLAIN返回语句中使用的每个表的一行信息 SELECT。它按照 MySQL 在处理语句时读取表的顺序列出了输出中的表。这意味着MySQL从第一个表中读取一行，然后在第二个表中找到匹配的行，然后在第三个表中找到匹配的行，依此类推。当所有表都处理完毕后，MySQL 输出选定的列并回溯表列表，直到找到有更多匹配行的表。从此表中读取下一行，并继续处理下一个表。

EXPLAIN 输出列
id 标识符SELECT_
select_type 方式SELECT_
table 输出行的表
partitions 匹配的分区
type 连接类型
possible_keys 可以选择的索引
key 实际选择的索引
key_len 所选密钥的长度
ref 列与索引的比较
rows 估计要检查的行数
filtered 按表条件过滤的行的百分比
Extra 附加信息
  1. id：这是查询的序号，显示查询中每个子查询的唯一标识符。如果查询包含子查询，每个子查询都会有一个唯一的 ID。同一个查询的不同部分使用相同的 ID。
  2. select_type：这表示查询的类型，包括以下几种可能的值：
    • SIMPLE：简单查询，不包含子查询或 UNION。
    • PRIMARY：主查询（外层查询）。
    • SUBQUERY：子查询。
    • DERIVED：派生表，使用了子查询的结果。
    • UNION：UNION 中第二个 SELECT 开始的查询。
    • UNION RESULT：UNION 的结果。
  3. table：显示与查询相关的表名。如果查询涉及多个表，则可能会显示多个表名，以逗号分隔。
  4. partitions：（在使用 EXPLAIN PARTITIONS 时）显示查询涉及的分区。
  5. type：表示连接类型或访问类型。这是优化器选择的主要算法，用于访问表中的行。一些常见的值包括：
    • system：表只有一行（通常是系统表），这是最快的连接类型。
    • const：使用主键或唯一索引时，只匹配一行。
    • ref：使用非唯一索引来查找匹配的行。
    • range：使用索引范围扫描，返回一定范围内的行。
    • index：全索引扫描，比较慢，但比全表扫描快。
    • all：全表扫描，性能最差。
  6. possible_keys：显示可能用于此查询的索引，但不一定实际使用。
  7. key：实际用于查询的索引。
  8. key_len：表示用于索引的字节数。
  9. ref：显示索引的哪一列被使用（如果有的话）。
  10. rows：估计查询将扫描的行数。
  11. filtered：表示优化器过滤结果集的百分比。
  12. Extra：提供有关查询的额外信息，例如是否使用了临时表、使用了索引提示、使用了文件排序等。

参考文献：

怎么定义慢查询

定义慢查询通常是根据数据库中执行查询的时间长短来设定一个阈值。这个阈值可以根据你的系统性能和应用需求进行调整。

在数据库中，比如 MySQL，可以通过以下步骤来定义慢查询：

  1. 设置慢查询阈值：

    • 使用数据库管理工具或通过 SQL 命令修改数据库的 long_query_time 参数。这个参数表示执行时间超过多少秒的查询被认定为慢查询。
    SET GLOBAL long_query_time = 1; -- 将慢查询的阈值设置为 1 秒

  2. 启用慢查询日志：

    • 开启慢查询日志功能，以便记录超过设定阈值的查询。
    • 修改 MySQL 配置文件中的 slow_query_log 参数为 ON。
    slow_query_log = ON
    • 还可以指定慢查询日志的位置和名称，以及慢查询的阈值：
    confCopy codeslow_query_log_file = /path/to/slow_query.log
long_query_time = 1

  3. 分析慢查询日志：

    • 查询记录在慢查询日志中的查询语句，找出执行时间超过阈值的查询，并对其进行分析优化。
    • 使用工具或通过命令查看慢查询日志，找出潜在的性能问题。

请记住，在生产环境中，启用慢查询日志可能会对性能产生影响。因此，在启用它之前，请考虑到系统资源和需求，并确保定期分析日志并优化查询。

如何保证不停服情况下，来进行历史数据迁移，以及新数据入库，还有分表：：：：：：先双写，在双读，新旧根据时间戳判断；

其实就是一个问题： 如何不停服进行分表

如何去效验，新旧表数据是否全部同步好了呢：：：：这个没什么好办法，全量校验脚本多跑几次

慢sql 优化，从几个方面来说吧

  1. 一个角度是从sql本身，通过执行计划，来优化，比较多的就是索引啊 sql编写之类的

  2. 从业务角度来说，定位是否是复杂sql，有没有必要，或者可以通过拆解来处理

  3. 进行分库分表，不过分库分表并不是单纯为了查询来做的，而是更多的是数据存储来说，分库分表后其实对于很多业务来说查询反而变得非常难，同时考虑对已有数据进行动态的分库操作是一件比较麻烦的事，可以通过binlog监听来先对原来的数据进行路由分发，形成新的库，之后再逐步切换，

  4. 对于慢sql多数是查询场景，其实这时候更多的应该考虑 查询和业务的分离，尝试做读模型治理，效果比较好

redis 分布式锁，redis 挂了的情况下，其他先不论，先让幂等兜，如果幂等也兜不住，那就让数据库的唯一性约束键兜

单库单表-转为分库分表

  1. 双写，新的DB进行写入，然后原来的db还是读和写
  2. 双读，新的db进行读写，原来的db进行读
  3. 撤掉原来的db，在新的db进行读写
  4. 如果原来数据读取不到，就将降级到原来的db进行读取

单库单表-分库分库

文章作者: 千羽
文章链接: https://nateshao.gitlab.io/2023/11/15/2023-yi-tiao-man-sql-yin-fa-de-si-kao/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 千羽 !
MySQL
赏
评论
  目录