慢 SQL 查询是数据库中常见的性能障碍,可能导致系统响应缓慢甚至服务不可用。
刚入职的时候,同事就提醒过我,涉及三四张表的时候,数据量大,尽量不用连表查询,用单表。我最近还真的是遇到了。因为联表查询导致引发的慢sql。
当然,排查和解决慢 SQL 查询问题流程应该是每个后端开发的必备之路。这里以亲身经历总结一波~
背景:2023.11.10 周五运维这边收到异常监控严重报警
您有待处理严重告警
告警严重度:严重
告警状态:触发sls告警
...
影响:mysql内存使用率达72.80,MySQL的cpu使用率达99.85
通过运维导出的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%'
有经验的可以排查看到这是涉及四张表,然后来了一个模糊查询!
模糊查询会引发什么问题?(索引失效)
带着问题往后走~
分析慢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
等
四张表的数据量
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();
发现是用户在通过标签搜索资源的时候,进行了模糊查询。
同时MySQL联表查询导致笛卡尔积问题可能会带来严重的性能和数据错误问题:
选择方案一:理由是,方案二的Elasticsearch 需要进行数据备份和同步等。目前是还没进行处理的,后续可以改进用elasticsearch查询。
目前是后台系统并发量屈指可数,可以满足当前业务需求的,如果是前台并发大的话,和对于长久的方案选择还是用es的。
转向 MySQL 单表查询。这种方法确实在处理简单查询时更为直接和方便,尤其是在当前紧急情况下,避免了引入额外的数据备份和同步工作。
优点是:
改为单表查询优化。
// 标签搜索
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];
}
通过单表查询优化之后,观察监控一段时间发现没有出现慢sql了。
同时,这样操作的好处是:
MySQL 慢查询优化是一个不断迭代的过程,包含多个步骤和策略。同时并不是所有东西都往高级技术去靠,还得结合具体业务场景。脱离业务,谈技术也是纸上谈兵。这也是为什么没有选择es查询而选择单表优化MySQL进行简单化。
EXPLAIN
或其他查询分析工具来查看查询的执行计划,确认是否使用了索引、优化了执行路径。最后,来一下经典的八股文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关键词等等。
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 | 附加信息 |
EXPLAIN PARTITIONS
时)显示查询涉及的分区。参考文献:
END