碎碎念
第二篇分享了一下MySQL中一些关键的监控项及相关查询SQL,貌似不少小伙伴门对这一块挺感兴趣,不过比较郁闷的是,阅读量和转发量不算低了，小伙伴们难道就是舍不得点个赞嘛,最近比较忙,趁着下班时间和周末,今天,第三篇来了,该篇是关于SQL优化的一些小案例,来自于日常工作,分享给你们,希望对你们有帮助。
时间类函数
案例说明
在生产中,经常会遇到根据时间进行筛选的查询,例如查找10分钟前还在线的游戏玩家,查找前一天的银行流水,对应到数据库,则需要关注的是SQL如何实现,在MySQL中,提供了不少时间类的函数,这些函数可以帮助你快速实现你的需求,但是,凡事有利有弊,函数用好了可以事半功倍,如果使用不当则可能会导致性能问题。因为不少函数会导致索引失效,从而全表扫描。分享之前遇到的两个案例,这里简化表结构,模拟生产环境,毕竟数据安全还是很重要滴。
第一个案例是需要查找满足create_time字段对应的日期等于当前时间日期的前一天日期的记录。最开始业务侧是使用date_format函数实现的逻辑,表数据量较大,create_time字段上也创建了索引,但是实际查询时候发现查询很慢。通过查看执行计划,实际上并没有使用索引,走的是全表扫描,大数据量下慢也就不足为奇了。这里为什么没有走索引呢,和SQL中使用的两个函数有关,date_format()函数和sysdate()函数,那么如何使得该查询走索引呢,可以使用date_sub()函数和now()函数代替之前使用的函数。改写之后,可以使用索引,效率不止提升一点，详细的如下:
mysql> show create table test_time\G;
*************************** 1. row ***************************
Table: test_time
Create Table: CREATE TABLE `test_time` (
`id` varchar(64) NOT NULL,
`name` varchar(16) NOT NULL,
`city` varchar(64) DEFAULT NULL,
`version` varchar(32) DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_createtime` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain SELECT * from test_time WHERE date_format(test_time.create_time,'%Y%m%d') = date_format(SYSDATE()- INTERVAL 1 day,'%Y%m%d'); +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_time | NULL | ALL | NULL | NULL | NULL | NULL | 980356 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * from test_time WHERE date_format(test_time.create_time,'%Y%m%d') = date_format(now()- INTERVAL 1 day,'%Y%m%d'); +----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_time | NULL | ALL | NULL | NULL | NULL | NULL | 980356 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * from test_time WHERE test_time.create_time between DATE_SUB(now(), INTERVAL TIME_TO_SEC(CURTIME())+86400 SECOND) and DATE_sub(now(), interval TIME_TO_SEC(CURTIME()) SECOND);
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_time | NULL | range | idx_createtime | idx_createtime | 6 | NULL | 5310 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * from test_time WHERE test_time.create_time between SUBDATE(now(), INTERVAL TIME_TO_SEC(CURTIME())+86400 SECOND) and DATE_sub(now(), interval TIME_TO_SEC(CURTIME()) SECOND);
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_time | NULL | range | idx_createtime | idx_createtime | 6 | NULL | 5310 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * from test_time WHERE test_time.create_time between SUBDATE(SYSDATE(), INTERVAL TIME_TO_SEC(CURTIME())+86400 SECOND) and DATE_sub(SYSDATE(), interval TIME_TO_SEC(CURTIME()) SECOND);
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_time | NULL | ALL | NULL | NULL | NULL | NULL | 980356 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
上面是第一个案例,接下来继续展开第二个案例的分享,需要查找满足login_start字段对应的时间早于当前时间减去十分钟或者log_start字段为null值的记录。最开始业务侧是使用timestampdiff()函数实现的逻辑,同样地,表数据量较大,login_start字段上也创建了索引,历史总是惊人的一致,这次也同样没有使用索引,走的是全表扫描,这次没有走索引与SQL中使用的timestampdiff()函数有关。那么如何使得该查询走索引呢,同样可以使用date_sub()函数代替之前使用的函数,改写之后,自然也可以使用索引。 详细的如下:
mysql> show create table login_time\G;
*************************** 1. row ***************************
Table: login_time
Create Table: CREATE TABLE `login_time` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`city` varchar(64) DEFAULT NULL,
`login_start` datetime DEFAULT NULL,
`login_end` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_login_start` (`login_start`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from login_time where (TIMESTAMPDIFF(SECOND,login_start,now()) > 600 or login_start is null);
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | login_time | NULL | ALL | idx_login_start | NULL | NULL | NULL | 993059 | 100.00 | Using where |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from login_time where login_start<DATE_SUB(now(),INTERVAL 600 second) or login_start is null;
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | login_time | NULL | ALL | idx_login_start | NULL | NULL | NULL | 993059 | 50.00 | Using where |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from login_time force index (idx_login_start) where (TIMESTAMPDIFF(SECOND,login_start,now()) > 600 or login_start is null);
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | login_time | NULL | ALL | idx_login_start | NULL | NULL | NULL | 993059 | 100.00 | Using where |
+----+-------------+------------+------------+------+-----------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> explain select * from login_time force index (idx_login_start) where login_start<DATE_SUB(now(),INTERVAL 600 second) or login_start is null;
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | login_time | NULL | range | idx_login_start | idx_login_start | 6 | NULL | 496529 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from login_time force index (idx_login_start) where login_start is null;
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | login_time | NULL | ref | idx_login_start | idx_login_start | 6 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from login_time where TIMESTAMPDIFF(SECOND,login_start,now()) > 600 ;
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | login_time | NULL | ALL | NULL | NULL | NULL | NULL | 993059 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from login_time where login_start is null;
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | login_time | NULL | ref | idx_login_start | idx_login_start | 6 | const | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from login_time force index (idx_login_start) where login_start<DATE_SUB(now(),INTERVAL 600 second) ;
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | login_time | NULL | range | idx_login_start | idx_login_start | 6 | NULL | 496529 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-----------------+-----------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
上面通过两个生产案例,介绍了date_format()函数和timestampdiff()函数的性能问题。也给出了平替函数和优化方案,下次遇到类似的问题,你就可以参考上面的方法和思路进行优化,赶快记录收藏下来吧。
SQL优化一则
案例说明
在实际工作中,发现很多开发人员和初级DBA同学对SQL优化还是没有一个清晰的思路,只知道SQL慢,我的SQL之前很快,我的SQL实际执行很快,但是在业务中就很慢,排除网络和链路问题,单单从数据库方面,SQL为什么慢?如何排查？如何优化,许多人却是一头雾水,结合前段时间生产上遇到的一个SQL案例,从问题到SQL优化流程结束给出完整的参考。
业务侧反馈有一条SQL查询比较慢,需要4-5s时间,直接导致前段界面点击卡顿,用户体验感差,从业务侧拿到SQL后便开始排查起来。为了防止对业务产生影响,先手动在从库执行了查询,发现实际执行很快,于是尝试再在主库执行,相同的SQL在主库执行却很慢：
表结构:
mysql> show create table test_warn\G;
*************************** 1. row ***************************
Table: test_warn
Create Table: CREATE TABLE `test_warn` (
`uuid` bigint(64) NOT NULL,
`test_uuid` bigint(64) DEFAULT NULL,
`warn_time` datetime DEFAULT NULL,
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_done` int(1) NOT NULL DEFAULT '0' COMMENT '是否处理：0，未处理；1，已处理；2，暂不处理',
`done_info` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '处理情况',
`operator` bigint(64) DEFAULT NULL COMMENT '处理人',
`done_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '处理时间',
`operator_name` varchar(255) DEFAULT NULL COMMENT '处理人姓名',
PRIMARY KEY (`uuid`) USING BTREE,
KEY `test_uuid` (`test_uuid`) USING BTREE,
KEY `warn_time` (`warn_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
#从库:
mysql> SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----------------+---------------------+---------------------+
| uuid | testUuid | warnTime |
+----------------+---------------------+---------------------+
| 51153087684823 | 8533508206888227922 | 2024-01-08 05:05:48 |
+----------------+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | test_warn | NULL | ref | test_uuid | test_uuid | 9 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
#主库:
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | test_warn | NULL | index | test_uuid | warn_time | 6 | NULL | 5 | 10.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----------------+---------------------+---------------------+
| uuid | testUuid | warnTime |
+----------------+---------------------+---------------------+
| 51153087684823 | 8533508206888227922 | 2024-01-08 05:05:48 |
+----------------+---------------------+---------------------+
1 row in set (5.16 sec)
可以看到上面从库和主库的执行计划是不一样的,实际执行的耗时差别也很大,时间相差了一个量级,很显然主库的执行计划是有问题的,MySQL在选择索引时选择错误了,错误的原因一般是由于统计数据不准确导致,我们知道MySQL是基于CBO(代价计算算法)来决定使用哪个索引,亦或是全表扫描,如果统计数据不准确,可能就会造成误判。这个我们简单知道就行,那至于为什么慢呢,可以通过profile查看执行该SQL时具体哪个环节慢。以便能下一步排查,如下:
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn force index(warn_time) WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----------------+---------------------+---------------------+
| uuid | testUuid | warnTime |
+----------------+---------------------+---------------------+
| 51153087684823 | 8533508206888227922 | 2024-01-08 05:05:48 |
+----------------+---------------------+---------------------+
1 row in set (4.46 sec)
mysql> show profiles;
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | 4.45400750 | SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn force index(warn_time) WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5 |
+----------+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.002179 |
| checking permissions | 0.000103 |
| Opening tables | 0.000092 |
| init | 0.000112 |
| System lock | 0.000033 |
| optimizing | 0.000041 |
| statistics | 0.000086 |
| preparing | 0.000045 |
| Sorting result | 0.000007 |
| executing | 0.000004 |
| Sending data | 4.449916 |
| end | 0.001135 |
| query end | 0.000051 |
| closing tables | 0.000027 |
| freeing items | 0.000063 |
| logging slow query | 0.000093 |
| cleaning up | 0.000024 |
+----------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql> set profiling=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)
可以看出,SQL执行时99%的时间花费在sending data阶段,一般而言,sending data耗时长有这几个原因,大数据量传输,服务器负载高,缓冲区空间不足,网络问题。而这里明显是大数据量传输问题,虽然只查询5条记录,但是实际上从执行计划来看是index索引扫描,MySQL会扫描大量数据,生产上该表数据量2500万+,自然会费事很长。
另外,对于该SQL,使用test_uuid索引实际执行会很快。但是看执行计划时,会发现Extra列显示Using filesort,这里会进行排序,虽然查询已经很快了,能不能更快一些呢,除了对MySQL参数优化,可以创建联合索引,当然,创建联合索引后别忘了删除冗余索引,如下：
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
| 1 | SIMPLE | test_warn | NULL | ref | test_uuid | test_uuid | 9 | const | 1 | 100.00 | Using index condition; Using filesort |
+----+-------------+-----------+------------+------+---------------+-----------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table test_warn add index combine(test_uuid,warn_time);
Query OK, 0 rows affected (3.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | test_warn | NULL | ref | test_uuid,combine | combine | 9 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
上面就是一般场景下SQL优化的通用流程了,当然,还有更深入的这里就暂时不介绍了,对于大部分同学来说这个已经够用了,如果整个流程排查下来,问题还是没有解决,不妨求助专业DBA同学,另外，SQL优化是一门庞大的学问,需要日积月累,我自己的话也在一点点学习,可以每次遇到好的案例记录下来,坚持下来,你也是优化大师。
row_number()函数
案例说明
日常生产中,你一定遇到过这样的需求。某张表需要删除大量数据,由于业务数据增长没做好合理预估和规划,前期对某张表设计不合理,导致单表的数据量越来越大,而这些数据中有部分本质上是无用的。一方面导致查询性能问题,另外一方面会浪费磁盘空间,这时候肯定是需要做数据清理的。对于数据清理,根据待删除的数据量与总数据量的比值来说,可以采用不同策略:
场景一:待删除数据量大于总数据量的半数以上,选取新建临时表,插入需保留数据至临时表,新旧表替换的方式。
场景二:待删除数据量小于总数据量的半数,直接删除需删除的数据。
场景三:待删除数据量约等于总数据量的半数,此时直接删除或新建临时表替换都可。
无论是插入需保留数据还是删除待删除数据,一次性删除大量数据都不是一个明智的做法,一方面在可能会影响数据库性能,另外一方面可能会超过InnoDB表的锁表大小。如同官网描述:If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table.这里就引出今天的话题了,根据主键分批删除,分批删除很简单,但是如何分批是一个问题,假设每次删除十万条记录,我咋知道每隔十万条记录的值是啥呢,如果你的MySQL版本不低于MySQL8.0.x版本，row_number()函数可以很好的帮你解决这个问题。不只是帮你找出区间值，还能帮你写好SQL。
mysql> set @batch_count=100000;select min(id) as batch_min_id,max(id) as batch_max_id,count(*) as record_count from (select test_rownum.id,row_number()over(order by test_rownum.id) as row_num from test_rownum) tmp group by floor((tmp.row_num - 1) / @batch_count) order by batch_min_id ;
Query OK, 0 rows affected (0.00 sec)
+--------------+--------------+--------------+
| batch_min_id | batch_max_id | record_count |
+--------------+--------------+--------------+
| 800001 | 900000 | 100000 |
| 900001 | 1000000 | 100000 |
| 1000001 | 1100000 | 100000 |
| 1100001 | 1200000 | 100000 |
| 1200001 | 1300000 | 100000 |
| 1300001 | 1400000 | 100000 |
| 1400001 | 1500000 | 100000 |
| 1500001 | 1600000 | 100000 |
| 1600001 | 1700000 | 100000 |
| 1700001 | 1800000 | 100000 |
| 1800001 | 1900000 | 100000 |
| 1900001 | 2000000 | 100000 |
+--------------+--------------+--------------+
12 rows in set (1.22 sec)
先简单展示一下row_numer()函数的作用吧,上面按照每个区间10万条记录的区间进行拆分,展示每个区间的最小id值和最大id值。其中id为表主键。
对于使用自增列作为主键的表,如果需要分批删除数据,可以参考如下自动生成SQL语句。另外,温馨提示,如果只需要保留源表少量数据,采用临时表交换的方式分批导入数据,如果业务侧对该表有实时写入,需要注意的是需要在最后一次分批导入数据后根据业务写入量预留部分auto_increment值,可通过alter table xxx auto_increment=xxx;修改auto_increment值。
mysql> show create table test_rownum\G;
*************************** 1. row ***************************
Table: test_rownum
Create Table: CREATE TABLE `test_rownum` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`product_id` varchar(64) DEFAULT NULL,
`version` varchar(32) DEFAULT NULL,
`result` int NOT NULL,
`create_time` datetime DEFAULT NULL,
`task_id` varchar(50) DEFAULT NULL COMMENT '任务关联uuid',
PRIMARY KEY (`id`),
KEY `idx_taskid` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000001 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> set @batch_count=100000;select min(id) as batch_min_id,max(id) as batch_max_id,count(*) as record_count,concat("delete from test_rownum where id between "
,min(id)," and ",max(id),";") as sql_context from (select test_rownum.id,row_number()over(order by test_rownum.id) as row_num from test_rownum) tmp group by floor((tmp.row_num - 1) / @batch_count) order by batch_min_id;
Query OK, 0 rows affected (0.00 sec)
+--------------+--------------+--------------+---------------------------------------------------------------+
| batch_min_id | batch_max_id | record_count | sql_context |
+--------------+--------------+--------------+---------------------------------------------------------------+
| 1 | 100000 | 100000 | delete from test_rownum where id between 1 and 100000; |
| 100001 | 200000 | 100000 | delete from test_rownum where id between 100001 and 200000; |
| 200001 | 300000 | 100000 | delete from test_rownum where id between 200001 and 300000; |
| 300001 | 400000 | 100000 | delete from test_rownum where id between 300001 and 400000; |
| 400001 | 500000 | 100000 | delete from test_rownum where id between 400001 and 500000; |
| 500001 | 600000 | 100000 | delete from test_rownum where id between 500001 and 600000; |
| 600001 | 700000 | 100000 | delete from test_rownum where id between 600001 and 700000; |
| 700001 | 800000 | 100000 | delete from test_rownum where id between 700001 and 800000; |
| 800001 | 900000 | 100000 | delete from test_rownum where id between 800001 and 900000; |
| 900001 | 1000000 | 100000 | delete from test_rownum where id between 900001 and 1000000; |
| 1000001 | 1100000 | 100000 | delete from test_rownum where id between 1000001 and 1100000; |
| 1100001 | 1200000 | 100000 | delete from test_rownum where id between 1100001 and 1200000; |
| 1200001 | 1300000 | 100000 | delete from test_rownum where id between 1200001 and 1300000; |
| 1300001 | 1400000 | 100000 | delete from test_rownum where id between 1300001 and 1400000; |
| 1400001 | 1500000 | 100000 | delete from test_rownum where id between 1400001 and 1500000; |
| 1500001 | 1600000 | 100000 | delete from test_rownum where id between 1500001 and 1600000; |
| 1600001 | 1700000 | 100000 | delete from test_rownum where id between 1600001 and 1700000; |
| 1700001 | 1800000 | 100000 | delete from test_rownum where id between 1700001 and 1800000; |
| 1800001 | 1900000 | 100000 | delete from test_rownum where id between 1800001 and 1900000; |
| 1900001 | 2000000 | 100000 | delete from test_rownum where id between 1900001 and 2000000; |
+--------------+--------------+--------------+---------------------------------------------------------------+
20 rows in set (2.73 sec)
注:新旧表替换流程:
1.新建临时表:
create table table_xxx_tmp like table_xxx;
2.分批多次进行插入:
参考上述查询语句,假定查询时最大自增id值为2000000;
假定插入完成后最大自增id值为2000100,且考虑预留部分，手动修改auto_increment值
alter table table_xxx_tmp auto_increment=2000500;(需要根据业务写入量预估,这一步很重要,否则后续会有主键冲突问题)
3.新旧表进行交换
rename table table_xxx to table_xxx_old, table_xxx_tmp to table_xxx;
4.补齐少量数据:
insert into table_xxx select * from table_xxx_old where id >2000000;
对于主键不是自增列的表,通过row_number()函数查询区间并生成SQL的方式与主键为自增列的表一样,更方便的是,不需要单独对auto_increment值进行处理:
mysql> show create table test_rownum\G;
*************************** 1. row ***************************
Table: test_rownum
Create Table: CREATE TABLE `test_rownum` (
`id` bigint NOT NULL,
`name` varchar(16) NOT NULL,
`product_id` varchar(64) DEFAULT NULL,
`version` varchar(32) DEFAULT NULL,
`result` int NOT NULL,
`create_time` datetime DEFAULT NULL,
`task_id` varchar(50) DEFAULT NULL COMMENT '任务关联uuid',
PRIMARY KEY (`id`),
KEY `idx_taskid` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> set @batch_count=100000;select min(id) as batch_min_id,max(id) as batch_max_id,count(*) as record_count,concat("delete from test_rownum where id between "
,min(id)," and ",max(id),";") as sql_context from (select test_rownum.id,row_number()over(order by test_rownum.id) as row_num from test_rownum) tmp group by floor((tmp.row_num - 1) / @batch_count) order by batch_min_id;
Query OK, 0 rows affected (0.00 sec)
+---------------------+---------------------+--------------+---------------------------------------------------------------------------------------+
| batch_min_id | batch_max_id | record_count | sql_context |
+---------------------+---------------------+--------------+---------------------------------------------------------------------------------------+
| 868571136530 | 461948964224356509 | 100000 | delete from test_rownum where id between 868571136530 and 461948964224356509; |
| 461951352096880978 | 922934842509711129 | 100000 | delete from test_rownum where id between 461951352096880978 and 922934842509711129; |
| 922940295645036359 | 1383352709836729925 | 100000 | delete from test_rownum where id between 922940295645036359 and 1383352709836729925; |
| 1383353257636820548 | 1841688088863500510 | 100000 | delete from test_rownum where id between 1383353257636820548 and 1841688088863500510; |
| 1841691291387823299 | 2303787088001516517 | 100000 | delete from test_rownum where id between 1841691291387823299 and 2303787088001516517; |
| 2303789917399161177 | 2765601049852870532 | 100000 | delete from test_rownum where id between 2303789917399161177 and 2765601049852870532; |
| 2765601717349932688 | 3224173421281849062 | 100000 | delete from test_rownum where id between 2765601717349932688 and 3224173421281849062; |
| 3224180493095976573 | 3683717212408374309 | 100000 | delete from test_rownum where id between 3224180493095976573 and 3683717212408374309; |
| 3683717527362173888 | 4145828953283714040 | 100000 | delete from test_rownum where id between 3683717527362173888 and 4145828953283714040; |
| 4145830389784473671 | 4605977383427027736 | 100000 | delete from test_rownum where id between 4145830389784473671 and 4605977383427027736; |
| 4606003722504467354 | 5067737626676024121 | 100000 | delete from test_rownum where id between 4606003722504467354 and 5067737626676024121; |
| 5067744432991130832 | 5527963714153508932 | 100000 | delete from test_rownum where id between 5067744432991130832 and 5527963714153508932; |
| 5527964131358999475 | 5989878877583643864 | 100000 | delete from test_rownum where id between 5527964131358999475 and 5989878877583643864; |
| 5989880843190824064 | 6452757446106629723 | 100000 | delete from test_rownum where id between 5989880843190824064 and 6452757446106629723; |
| 6452759838682498065 | 6914387548649376269 | 100000 | delete from test_rownum where id between 6452759838682498065 and 6914387548649376269; |
| 6914389571893375169 | 7375959492834697179 | 100000 | delete from test_rownum where id between 6914389571893375169 and 7375959492834697179; |
| 7375960179605996936 | 7836995500800987380 | 100000 | delete from test_rownum where id between 7375960179605996936 and 7836995500800987380; |
| 7837008126187787791 | 8298982712386516372 | 100000 | delete from test_rownum where id between 7837008126187787791 and 8298982712386516372; |
| 8298984833028339834 | 8762497456807289648 | 100000 | delete from test_rownum where id between 8298984833028339834 and 8762497456807289648; |
| 8762497478115379708 | 9223371503385543939 | 100000 | delete from test_rownum where id between 8762497478115379708 and 9223371503385543939; |
+---------------------+---------------------+--------------+---------------------------------------------------------------------------------------+
20 rows in set (3.39 sec)
row_number()函数的小妙用就到此结束,建议如果遇到以上场景查询时在从库进行查询,以免对主库性能产生影响,生产上任何不当的操作都可能会触发一场灾难。因此做任何操作前需要思考风险,作为数据从业人员,尤其DBA,我们需要对自己执行的命令负责,清楚明白自己执行这一条命令会产生怎样的结果,而不是稀里糊涂执行。row_number()函数更多可参考官网 https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_row-number。
案例之外
案例说明
我们需要明白的是，我们每次处理问题,如果只是仅仅限于问题本身,那可能就辜负了问题,某一方面也限制了自己的成长,一个智慧的人,他一定能透过表象看清本质,善于归纳,解决一类问题。这是最快的成长捷径,也是能走得最远的路。上面的几个问题案例,在案例之外,我们又能收获哪些呢？
知识点一:SYSDATE()函数和NOW()函数的差别在于SYSDATE是返回执行的值，是不确定的,NOW()是返回执行开始时的值,是确定的,SYSDATE()无法走索引,NOW()可以走索引:
mysql> select now(),sleep (5), now();
+---------------------+-----------+---------------------+
| now() | sleep (5) | now() |
+---------------------+-----------+---------------------+
| 2024-05-16 09:30:14 | 0 | 2024-05-16 09:30:14 |
+---------------------+-----------+---------------------+
1 row in set (5.00 sec)
mysql> select SYSDATE(),sleep (5), SYSDATE();
+---------------------+-----------+---------------------+
| SYSDATE() | sleep (5) | SYSDATE() |
+---------------------+-----------+---------------------+
| 2024-05-16 09:30:50 | 0 | 2024-05-16 09:30:55 |
+---------------------+-----------+---------------------+
1 row in set (5.00 sec)
知识点二:除了DATE_SUB()函数,DATE_ADD()函数,SUBDATE()函数,ADDDATE()函数,记住他们,他们是四兄弟,都是可以走索引滴。
mysql> select DATE_SUB("2024-05-16 19:30:00",interval -1 day);
+-------------------------------------------------+
| DATE_SUB("2024-05-16 19:30:00",interval -1 day) |
+-------------------------------------------------+
| 2024-05-17 19:30:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select DATE_ADD("2024-05-16 19:30:00",interval -1 day);
+-------------------------------------------------+
| DATE_ADD("2024-05-16 19:30:00",interval -1 day) |
+-------------------------------------------------+
| 2024-05-15 19:30:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select SUBDATE("2024-05-16 19:30:00",interval -1 day);
+------------------------------------------------+
| SUBDATE("2024-05-16 19:30:00",interval -1 day) |
+------------------------------------------------+
| 2024-05-17 19:30:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select ADDDATE("2024-05-16 19:30:00",interval -1 day);
+------------------------------------------------+
| ADDDATE("2024-05-16 19:30:00",interval -1 day) |
+------------------------------------------------+
| 2024-05-15 19:30:00 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIME_TO_SEC('19:30:00');
+-------------------------+
| TIME_TO_SEC('19:30:00') |
+-------------------------+
| 70200 |
+-------------------------+
1 row in set (0.01 sec)
mysql> SELECT SEC_TO_TIME(3600);
+-------------------+
| SEC_TO_TIME(3600) |
+-------------------+
| 01:00:00 |
+-------------------+
1 row in set (0.00 sec)
知识点三:对于or关键字,一定条件下是可能走一到多个索引的，即单索引或索引合并,但是记住一点,只要有一个分支无法使用索引,就会导致整个or语句不走索引,此时你的执行计划中possible_keys列可能不为空。
mysql> show create table test_or\G;
*************************** 1. row ***************************
Table: test_or
Create Table: CREATE TABLE `test_or` (
`id` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
`city` varchar(16) DEFAULT NULL,
`country` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_city` (`city`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from test_or where name='Amy Peters' or name='Joe Rodriguez';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_or | NULL | range | idx_name | idx_name | 66 | NULL | 32 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_or where name='Amy Peters' or city='Ann Hill';
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | test_or | NULL | index_merge | idx_name,idx_city | idx_name,idx_city | 66,67 | NULL | 30 | 100.00 | Using union(idx_name,idx_city); Using where |
+----+-------------+---------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_or where name='Amy Peters' or country='praesentium natus sequi.';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | test_or | NULL | ALL | idx_name | NULL | NULL | NULL | 942834 | 19.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
知识点四:执行计划中type列显示index有两种情况: 情况一:索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，则只扫描索引树。在这种情况下，Extra列显示Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。 情况二:使用从索引中读取数据来执行全表扫描，以按索引顺序查找数据行。使用索引不会显示在"Extra"列中。
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | test_warn | NULL | ref | test_uuid,combine | combine | 9 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT uuid as uuid, test_uuid as testUuid, warn_time as warnTime,is_done as isdone FROM test_warn WHERE test_uuid = 8533508206888227922 ORDER BY warn_time DESC limit 5;
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test_warn | NULL | ref | test_uuid,combine | combine | 9 | const | 1 | 100.00 | Using where |
+----+-------------+-----------+------------+------+-------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
不积跬步,无以至千里。不积小流,无以成江海。案例之外,延伸开来,收获的更多,思考一下,你在阅读前面案例的时候,有没有对这些问题有过疑惑并去实践验证了呢。
今日份总结
本篇的案例小分享就到此结束了。从关于时间类型的两个SQL案例开始，穿插一则SQL优化的整体流程，授人以鱼不如授人以渔,还有数据清理时MySQL8.0实例可以用到的一个实用窗口函数,最后,源于案例,又不止于案例,提炼升华几个知识点,最后,祝小伙伴们周末愉快。(ps:有时间就会及时更新案例的，如果你也热爱学习，也可以后台联系我，彼此就多了一个朋友，哈哈。)
温馨提示
原文作者：DBA修行之路；如有侵权，请联系管理员删除。
下一篇：
MySQL中强烈推荐你开启的监控项
请登录后发表评论
登录
全部评论
