老程序员博客

编程生涯的最后一舞

当前位置：首页 > 技术 > 正文内容

MySQL查询性能调优实战：从190秒优化至1秒的完整方案

访客 技术 2026年7月28日 1

近期处理了一起数据库性能故障，涉及一套主从架构的MySQL实例。该实例每日凌晨都会触发主从同步延迟告警，这意味着在该时间点执行主从切换将会面临漫长的等待时间，系统需要额外消耗资源来追赶延迟数据。深入排查后发现，造成该问题的核心原因是数据库中存在大量执行时间过长的SQL查询（通常定义为执行时间超过1秒的查询）。

进一步分析发现，应用层每日定时执行的数据清理任务——即删除一个月前的历史数据——是导致慢查询频发的主要根源。

问题分析

借助pt-query-digest工具对近期的mysql-slow.log进行深度分析：

pt-query-digest --since=148h mysql-slow.log | less

分析结果显示：

  • 过去一周累计记录慢查询执行时长达到25403秒
  • 单条最慢SQL执行时长为266秒
  • 平均每个慢SQL执行时长为5秒
  • 平均扫描行数高达1766万行

针对arrival_record表的查询统计：

  • SELECT操作产生超过4万次慢查询，平均响应时间4秒
  • DELETE操作执行6次，平均响应时间高达258秒

典型的慢查询SQL模式如下：

select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0

该查询最多扫描了5600万行数据，平均扫描行数为172万行，显然扫描行数过多是导致执行时间过长的直接原因。

执行计划分析

explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0;

执行计划输出：

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: arrival_record
partitions: NULL
type: ref
possible_keys: IXFK_arrival_record
key: IXFK_arrival_record
key_len: 8
ref: const
rows: 32261320
filtered: 3.70
Extra: Using index condition; Using where

虽然该查询使用了索引IXFK_arrival_record，但估算的扫描行数超过3000万行，效率极低。

索引结构审视

show index from arrival_record;

+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name            | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| arrival_record | 0          | PRIMARY             | 1            | id           | A         | 107990720   | NULL     | NULL   |      | BTREE      |         |               |
| arrival_record | 1          | IXFK_arrival_record | 1            | product_id   | A         | 1344        | NULL     | NULL   |      | BTREE      |         |               |
| arrival_record | 1          | IXFK_arrival_record | 2            | station_no   | A         | 22161       | NULL     | NULL   | YES  | BTREE      |         |               |
| arrival_record | 1          | IXFK_arrival_record | 3            | sequence     | A         | 77233384    | NULL     | NULL   |      | BTREE      |         |               |
| arrival_record | 1          | IXFK_arrival_record | 4            | receive_time | A         | 65854652    | NULL     | NULL   | YES  | BTREE      |         |               |
| arrival_record | 1          | IXFK_arrival_record | 5            | arrival_time | A         | 73861904    | NULL     | NULL   | YES  | BTREE      |         |               |
+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

表结构关键信息：

  • 表中记录数超过1亿条
  • 仅有一个复合索引IXFK_arrival_record
  • product_id字段的基数极低（1344），选择性很差，不适合作为查询的主要过滤条件
  • 查询条件中的receive_time字段具有较高的基数和良好的选择性

当前查询条件为：

where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0

根据索引左前缀原则，该查询仅能使用复合索引的第一个字段product_id，而该字段选择性极差，导致扫描行数巨大。

流量捕获分析

利用tcpdump工具捕获针对该表的实际查询语句：

tcpdump -i bond0 -s 0 -l -w - dst port 3316 | strings | grep select | egrep -i 'arrival_record' >/tmp/select_arri.log

提取查询条件：

IFS_OLD=$IFS
IFS=$'\n'
for i in `cat /tmp/select_arri.log`; do echo ${i#*'from'}; done | less
IFS=$IFS_OLD
arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=17 and arrivalrec0_.station_no='56742'
arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='S7100'
arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='V4631'

通过分析发现，实际查询的WHERE条件包含product_id、station_no和sequence三个字段，可以充分利用复合索引IXFK_arrival_record的前三个字段。

DELETE语句分析

数据删除操作的统计：平均扫描行数达1.1亿行，平均执行时间262秒。

删除语句示例：

delete from arrival_record where receive_time < STR_TO_DATE('2019-02-23', '%Y-%m-%d')

执行计划：

explain select * from arrival_record where receive_time < STR_TO_DATE('2019-02-23', '%Y-%m-%d')
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: arrival_record
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 109,501,508
filtered: 33.33
Extra: Using where

该删除语句未使用任何索引，导致全表扫描，执行效率极低。

优化方案

索引重构策略

  1. 删除原有的复合索引IXFK_arrival_record
  2. 新建复合索引idx_product_id_sequence_station_no，覆盖product_id、sequence、station_no三个字段
  3. 为receive_time字段单独创建索引idx_receive_time

测试环境验证

在测试实例上验证优化效果。首先查看表的基本信息：

du -sh /datas/mysql/data/3316/cq_new_cimiss/arrival_record*
12K /datas/mysql/data/3316/cq_new_cimiss/arrival_record.frm
48G /datas/mysql/data/3316/cq_new_cimiss/arrival_record.ibd

select count() from cq_new_cimiss.arrival_record;
+-----------+
| count()   |
+-----------+
| 112294946 |
+-----------+

表空间统计：

SELECT
    table_name,
    CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),'M') AS dbdata_size,
    CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),'M') AS dbindex_size,
    CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),'G') AS table_size(G),
    AVG_ROW_LENGTH,table_rows,update_time
FROM
    information_schema.tables
WHERE table_schema = 'cq_new_cimiss' and table_name='arrival_record';
+----------------+-------------+--------------+------------+----------------+------------+---------------------+
| table_name     | dbdata_size | dbindex_size | table_size(G) | AVG_ROW_LENGTH | table_rows | update_time        |
+----------------+-------------+--------------+------------+----------------+------------+---------------------+
| arrival_record | 18,268.02M  | 13,868.05M   | 31.38G       | 175            | 109155053  | 2019-03-26 12:40:17 |
+----------------+-------------+--------------+------------+----------------+------------+---------------------+

磁盘占用48G，表实际大小31G，存在约17G的碎片空间，主要由历史删除操作导致。

数据备份与恢复

使用mydumper进行并行压缩备份：

user=root
passwd=xxxx
socket=/datas/mysql/data/3316/mysqld.sock
db=cq_new_cimiss
table_name=arrival_record
backupdir=/datas/dump_$table_name
mkdir -p $backupdir
nohup echo `date +%T` && mydumper -u $user -p $passwd -S $socket -B $db -c -T $table_name -o $backupdir -t 32 -r 2000000 && echo `date +%T` &

备份耗时52秒，备份文件仅1.2G（原始表占用48G），压缩比极高。

开始备份于：2019-03-26 12:46:04
........
备份完成于：2019-03-26 12:46:56

du -sh /datas/dump_arrival_record/
1.2G  /datas/dump_arrival_record/

将备份数据复制到测试节点：

scp -rp /datas/dump_arrival_record root@10.230.124.19:/datas

多线程数据导入：

time myloader -u root -S /datas/mysql/data/3308/mysqld.sock -P 3308 -p root -B test -d /datas/dump_arrival_record -t 32

实际导入耗时：126分42秒

索引变更操作

使用在线DDL进行索引删除和重建。首先删除外键约束（如不删除，外键关联的复合索引将无法删除）：

nohup bash /tmp/ddl_index.sh &
2019-04-04-10:41:39 开始停止 mysqld_3308
2019-04-04-10:41:41 开始复制数据目录
2019-04-04-10:46:53 启动 mysqld_3308
2019-04-04-10:46:59 在线 DDL 开始执行
2019-04-04-11:20:01 索引删除成功

2019-04-04-11:20:02 索引添加开始
2019-04-04-11:40:30 索引添加成功

优化后的执行计划：

explain select count(*) from arrival_record where receive_time < STR_TO_DATE('2019-03-10', '%Y-%m-%d')
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: arrival_record
partitions: NULL
type: range
possible_keys: idx_receive_time
key: idx_receive_time
key_len: 6
ref: NULL
rows: 7540948
filtered: 100.00
Extra: Using where; Using index

查询现在可以有效利用索引加速，扫描行数从原来的上亿行降低至754万行。

生产环境实施

由于采用主从架构，应用连接VIP。索引变更使用在线DDL方式，依次在从库执行（不记录binlog），完成主从切换后，在新从库执行相同操作。

function log_message() {
    local message="$*"
    echo -e "$(date +%F-%T)  ${message}"
}

function validate_result(){
    if [ "$1" != "0" ];then
        log_message "$2"
        echo "exit 1"
        exit 1
    fi
}

log_message "停止从库复制"
mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -e"stop slave"
validate_result "$?" "停止从库失败"

log_message "开始在线DDL"
mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -e"set sql_log_bin=0;select now() as ddl_start;ALTER TABLE $db_.`${table_name}` DROP FOREIGN KEY FK_arrival_record_product,drop index IXFK_arrival_record,add index idx_product_id_sequence_station_no(product_id,sequence,station_no),add index idx_receive_time(receive_time);select now() as ddl_stop" >>${log_file} 2>&1
log_message "在线DDL完成"
log_message "重建外键约束"
mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -e"set sql_log_bin=0;ALTER TABLE $db_.${table_name} ADD CONSTRAINT _FK_${table_name}_product FOREIGN KEY (product_id) REFERENCES cq_new_cimiss.product (id) ON DELETE NO ACTION ON UPDATE NO ACTION;" >>${log_file} 2>&1
validate_result "$?" "添加外键失败"
log_message "外键约束添加完成"

log_message "启动从库复制"
mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -e"start slave"
validate_result "$?" "启动从库失败"

执行耗时统计：

  • DDL开始：2019-04-08 11:17:36
  • DDL完成：2019-04-08 11:45:13（耗时约28分钟）
  • 外键添加完成：2019-04-08 12:33:48

优化效果验证

验证优化后的执行计划：

explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 14:00:00' and '2019-03-25 15:00:00' and receive_spend_ms>=0
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: arrival_record
partitions: NULL
type: range
possible_keys: idx_product_id_sequence_station_no,idx_receive_time
key: idx_receive_time
key_len: 6
ref: NULL
rows: 291448
filtered: 16.66
Extra: Using index condition; Using where

所有查询均能利用索引加速，扫描行数大幅减少。

删除操作优化效果：

delete from arrival_record where receive_time < STR_TO_DATE('2019-03-10', '%Y-%m-%d')

该删除语句利用索引删除超过300万条记录，耗时从原来的262秒降低至77秒，性能提升超过3倍。

批量删除优化策略

针对大表数据删除，建议采用小批量方式避免对主库造成过大压力。应用层已优化为每10分钟执行一次数据删除（每次执行时间约1秒），主从延迟告警未再出现。

另一种批量删除方案：按主键顺序每次删除20000条记录

# 获取符合时间条件的最大主键ID
SELECT MAX(id) INTO @max_delete_id FROM `arrival_record` WHERE receive_time<'2019-03-01';

# 按主键顺序逐步扫描小批量删除数据
DELETE FROM arrival_record WHERE id<@max_delete_id LIMIT 20000;
SELECT ROW_COUNT();

# 循环执行直至删除完成
DELETE FROM arrival_record WHERE id<@max_delete_id LIMIT 20000;
SELECT ROW_COUNT();
# 程序休眠0.5秒后继续

经验总结

  • 对于超大型表，除了关注查询响应时间外，还需考虑表维护成本（DDL操作耗时、删除历史数据耗时）
  • 进行大表DDL操作时，应根据表的具体情况（并发访问、外键约束等）选择合适的变更方式
  • 从大表中删除大量数据时，应采用小批量删除策略，有效降低主库负载和主从延迟风险
标签: MySQL
返回列表

上一篇：C++ Primer 第八章：IO 库核心要点解析

没有最新的文章了...

相关文章

Linux crontab 详解

1) crontab 是什么cron 是 Linux 的定时任务守护进程；crontab 是用来编辑/查看“按时间周期执行命令”的表（cron table）。常见两类：用户 crontab：每个用户一份（crontab -e 编辑）系统级 crontab / cron.d：可指定执行用户（/etc/crontab、/etc/cron.d/*）2) crontab 时间...

富文本里可以允许的 HTML 属性

一、所有标签默认允许的安全属性（极少）class        （可选）id           （通常建议禁用）title️ 注意：id 容易被滥用做锚点注入，很多系统直接禁用class 允许的话最好只允许固定前缀（如 editor-*）二、a 标签允许属性<a href="" t...

Mac 安装 Node.js 指南

方法一：通过官网安装包（最简单，适合初学者）如果你只是想快速安装并开始使用，这是最直接的方法。访问 Node.js 官网。页面会显示两个版本：LTS (Recommended For Most Users)：长期支持版，最稳定。建议选这个。Current：最新特性版，包含最新功能但可能不够稳定。下载 .pkg 安装包并运行。按照安装向导点击“下一步”即可完成。方法二：使用 Homebrew 安装（...

Dom\HTML_NO_DEFAULT_NS 的副作用：自动加闭合标签

在使用Dom\HTMLDocument时，Dom\HTML_NO_DEFAULT_NS 将禁止在解析过程中设置元素的命名空间, 此设置是为了与DOMDocument向后兼容而存在的。当使用它时，已知的一个副作用就是：自动加闭合标签例如 </img> 为什么会这样？当你使用：Dom\HTML_NO_DEFAULT_NS文档会变成 无命名空间模式，此时内部更接近 XML...

Laravel 事件和监听器创建

在 Laravel 中，使用 Artisan 命令创建 Events（事件） 和 Listeners（监听器） 是非常高效的。你可以通过以下几种方式来实现：1. 手动创建单个 Event如果你只想创建一个事件类，可以使用 make:event 命令：Bashphp artisan make:event UserRegistered执行后，文件将生成在 app/Even...

自定义域名解析神器 dnsmasq

什么是 dnsmasq？dnsmasq 是一个轻量级、功能强大的网络服务工具，专为小型和中等规模网络设计。它是一个综合的网络基础设施解决方案[1]。dnsmasq 能做什么？功能说明应用场景DNS 转发与缓存将 DNS 查询转发到上游服务器（ISP、Google DNS 等），并在本地缓存结果加快 DNS 查询速度，减少外部 DNS 流量本地 DNS解析本地网络设备的主机名，无需编辑&n...

发表评论

访客

◎欢迎参与讨论，请在这里发表您的看法和观点。

Copyright © agingcoder.cn

Powered By Z-BlogPHP. Theme by TOYEAN.