往期文章
docker+nginx实现一个简单的负载均衡
docker+ovs+pipework配置容器ip互通
docker搭建zookeeper集群
docker搭建hadoop集群
docker搭建spark集群
docker搭建hbase集群
被迫入门PHP，真香！
牛哇，PHP这个开发框架真的好香！
- 1. 引言
- 2. 慢sql出现的背景
- 2.1 我遇到的情况
- 2.2 识别慢查询
- 2.3 查询分析和优化(重点)
- 3.慢sql问题归纳
- 4.解决慢sql优化方案选择
- 方案一：联表改为MySQL单表查询
- 方案二：改为ES查询
- 5.解决优化慢sql
- 6.优化成果
- 7.总结反思
- 总结一下具体优化步骤吧：
- 反思和改进：
- 索引失效有哪些？
- 查询优化神器 – explain命令
1. 引言
慢 SQL 查询是数据库中常见的性能障碍，可能导致系统响应缓慢甚至服务不可用。
刚入职的时候，同事就提醒过我，涉及三四张表的时候，数据量大，尽量不用连表查询，用单表。我最近还真的是遇到了。因为联表查询导致引发的慢sql。
当然，排查和解决慢 SQL 查询问题流程应该是每个后端开发的必备之路。这里以亲身经历总结一波~
2. 慢sql出现的背景
2.1 我遇到的情况
- 背景：2023.11.10 周五运维这边收到异常监控严重报警
您有待处理严重告警
告警严重度：严重
告警状态：触发sls告警
...
- 影响：mysql内存使用率达72.80，MySQL的cpu使用率达99.85
- 4条慢sql查询响应时间达229.428秒左右
2.2 识别慢查询
- 通过运维导出的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问题归纳
- 问题一：四张表进行连表数据量大，连表查询导致耗时长。颗粒标签表达一千多万条，颗粒表100多w，资源表200多w。
- 问题二：模糊查询导致索引失效，没有命中索引。
- 问题三：联表查询导致笛卡尔体积增加，查询时间耗时长。
同时MySQL联表查询导致笛卡尔积问题可能会带来严重的性能和数据错误问题：
- 性能问题：笛卡尔积会导致结果集数量大幅增加，占用更多的内存和磁盘空间。当数据量巨大时，这可能导致查询时间大幅增加，甚至导致数据库服务器性能下降。
- 数据错误：由于笛卡尔积导致了结果集中包含了不正确的数据组合，可能会影响业务逻辑和数据准确性。例如，在进行统计或计算时，错误的数据组合会导致错误的计算结果。
- 资源消耗：大量的笛卡尔积结果集可能会消耗数据库服务器的资源，包括 CPU、内存和磁盘空间，从而影响整个数据库系统的稳定性和性能。
- 网络传输成本：对于大量笛卡尔积结果，如果需要通过网络传输到客户端，会增加网络开销和传输时间。
4.解决慢sql优化方案选择
方案一：联表改为MySQL单表查询
- 优点：
- 简化处理：使用 MySQL 单表查询可以减少复杂性，特别是如果查询不需要多个表的数据。
- 已有基础：如果你的系统已经在 MySQL 上运行，并且对其进行了优化，继续使用单表查询可能更为顺畅。
- 较低学习成本：不需要引入新的技术或系统，减少了学习和迁移成本。
- 缺点：
- 限制性：单表查询可能限制了查询的复杂性和灵活性，尤其在需要跨多个表进行复杂联合查询时。
- 性能瓶颈：在大数据量或需要复杂计算的情况下，可能会出现性能瓶颈。
方案二：改为ES查询
- 优点：
- 高性能：Elasticsearch 专注于搜索和分析，对于复杂的全文搜索或分析需求提供了高性能。
- 分布式支持：具备横向扩展能力，能够处理大规模数据和并发查询。
- 搜索功能：适用于复杂的搜索功能，比如全文搜索、聚合查询等。
- 缺点：
- 学习成本：可能需要学习新的技术和工具，增加了迁移和开发的学习成本。
- 数据同步与维护：引入 Elasticsearch 需要额外的数据同步和维护工作，可能增加了系统复杂性。
选择方案一：理由是，方案二的Elasticsearch 需要进行数据备份和同步等。目前是还没进行处理的，后续可以改进用elasticsearch查询。
目前是后台系统并发量屈指可数，可以满足当前业务需求的，如果是前台并发大的话，和对于长久的方案选择还是用es的。
转向 MySQL 单表查询。这种方法确实在处理简单查询时更为直接和方便，尤其是在当前紧急情况下，避免了引入额外的数据备份和同步工作。
优点是：
- 简化流程：MySQL 单表查询不需要你处理额外的数据同步和备份任务。这意味着你可以专注于优化数据库结构和查询语句，提高查询性能而不必处理额外的复杂性。
- 已有基础：如果你的系统已经建立在 MySQL 基础上，并且已经对其进行了优化和维护，那么继续使用 MySQL 可能更为顺畅。你可能已经有了熟练的数据库管理员和开发团队，他们对 MySQL 的工作原理和最佳实践非常熟悉。
- 技术迁移成本低：转向 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了。
同时，这样操作的好处是：
- 简化查询：单表查询避免了多个表之间的联接，简化了查询语句的复杂性和执行过程。
- 减少数据集大小：单表查询通常会减少结果集的大小，因为它们不会产生笛卡尔积，避免了不必要的数据组合。
- 提高查询性能：单表查询往往比联表查询更快速，特别是当联表查询涉及大量数据时，单表查询更有效率。
- 降低资源消耗：单表查询可能会减少数据库服务器的资源消耗，包括 CPU、内存和磁盘空间。
- 简化维护：单表查询使数据查询和维护更为简单，不涉及多表联接和复杂的连接条件。
7.总结反思
MySQL 慢查询优化是一个不断迭代的过程，包含多个步骤和策略。同时并不是所有东西都往高级技术去靠，还得结合具体业务场景。脱离业务，谈技术也是纸上谈兵。这也是为什么没有选择es查询而选择单表优化MySQL进行简单化。
- 尽量采取单表查询，如果是要联表，要根据小表连大表的原则进行连接。
- 业务需求可能会不断变化，需要根据新需求和数据特性不断对慢sql进行调整优化策略。
总结一下具体优化步骤吧：
- 识别慢查询：首先要能够准确定位到慢查询，使用 MySQL 的慢查询日志或性能监控工具来捕获潜在的慢查询语句。
- 分析查询执行计划：使用
EXPLAIN或其他查询分析工具来查看查询的执行计划，确认是否使用了索引、优化了执行路径。 - 优化查询语句：根据执行计划的分析结果，重写查询语句以提高效率，减少数据扫描和不必要的操作。
- 优化索引：确保表的索引被正确地设计和使用，合理地创建、删除或更新索引以加速查询。
- 定期维护：定期执行数据库维护任务，包括索引重建、统计信息更新等，保持数据库性能。
反思和改进：
- 查询日志和监控：审查慢查询日志和监控数据，了解优化前后的性能变化。
- 查询重构：如果某些查询无法通过索引优化，考虑重构查询，分解成更简单、更优化的查询。
- 版本更新和配置调整：时刻关注 MySQL 的版本更新和配置调整，新版本可能提供了更优化的查询优化器或者新特性。
- 持续学习和分享：保持学习和了解数据库优化的最新趋势和技术，分享经验和知识，与同事交流合作。
- 综合策略：不同场景可能需要不同的优化策略，持续地评估和调整优化策略，以适应不同的应用场景和需求。
最后，来一下经典的八股文hhhhh
八股文
索引失效有哪些？
MySQL索引可能在以下情况下失效：
- 不使用索引字段进行查询：如果查询条件中没有使用到索引字段，MySQL 可能会放弃使用索引而进行全表扫描。
SELECT * FROM table_name WHERE non_indexed_column = 'value'; - LIKE 查询的模糊匹配：如果在
LIKE查询中使用通配符在搜索模式的开头，索引可能失效。SELECT * FROM table_name WHERE indexed_column LIKE '%value'; - 函数包装索引字段：如果在索引字段上使用了函数，索引可能失效。
SELECT * FROM table_name WHERE DATE_FORMAT(date_column, '%Y-%m-%d') = '2023-01-01'; - 对索引字段进行运算：如果对索引字段进行运算，MySQL 可能无法使用索引。
SELECT * FROM table_name WHERE indexed_column + 1 = 10; - 数据类型不匹配：在进行比较时，如果查询条件的数据类型与索引字段的数据类型不匹配，可能导致索引失效。
SELECT * FROM table_name WHERE indexed_column = 10; -- 如果 indexed_column 是字符串类型 - 过滤结果过于宽泛：如果查询结果集占据大部分表的数据行，MySQL 可能选择全表扫描而不使用索引。
SELECT * FROM table_name WHERE indexed_column > 0; -- 过滤结果太宽泛 - 表数据量较小：对于较小的表，MySQL 可能会选择不使用索引而进行全表扫描。
- 隐式数据类型转换：当查询的数据类型与字段的数据类型不匹配时，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 | 附加信息 |
- id：这是查询的序号，显示查询中每个子查询的唯一标识符。如果查询包含子查询，每个子查询都会有一个唯一的 ID。同一个查询的不同部分使用相同的 ID。
- select_type：这表示查询的类型，包括以下几种可能的值：
- SIMPLE：简单查询，不包含子查询或 UNION。
- PRIMARY：主查询（外层查询）。
- SUBQUERY：子查询。
- DERIVED：派生表，使用了子查询的结果。
- UNION：UNION 中第二个 SELECT 开始的查询。
- UNION RESULT：UNION 的结果。
- table：显示与查询相关的表名。如果查询涉及多个表，则可能会显示多个表名，以逗号分隔。
- partitions：（在使用
EXPLAIN PARTITIONS时）显示查询涉及的分区。 - type：表示连接类型或访问类型。这是优化器选择的主要算法，用于访问表中的行。一些常见的值包括：
- system：表只有一行（通常是系统表），这是最快的连接类型。
- const：使用主键或唯一索引时，只匹配一行。
- ref：使用非唯一索引来查找匹配的行。
- range：使用索引范围扫描，返回一定范围内的行。
- index：全索引扫描，比较慢，但比全表扫描快。
- all：全表扫描，性能最差。
- possible_keys：显示可能用于此查询的索引，但不一定实际使用。
- key：实际用于查询的索引。
- key_len：表示用于索引的字节数。
- ref：显示索引的哪一列被使用（如果有的话）。
- rows：估计查询将扫描的行数。
- filtered：表示优化器过滤结果集的百分比。
- Extra：提供有关查询的额外信息，例如是否使用了临时表、使用了索引提示、使用了文件排序等。
参考文献：
- 美团技术团队：https://tech.meituan.com/2014/06/30/mysql-index.html
- explain：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
声明：文中观点不代表本站立场。本文传送门：http://eyangzhen.com/374367.html
