MySQL删除千万级数据量导致的慢查询优化

有人删了千万级的数据，结果导致频繁的慢查询。

线上收到大量慢查询告警，于是检查慢查询的SQL，发现不是啥复杂SQL，这些SQL主要针对一个表，基本都是单行查询，看起来应该不会有慢查询。这种SQL基本上都是直接根据索引查找出来的，性能应该极高。

是否可能慢查询不是SQL问题，而是MySQL生产服务器的问题？特殊情况下，MySQL出现慢查询还真不是SQL问题，而是他自己生产服务器的负载太高，导致SQL语句执行慢。比如现在MySQL服务器的

磁盘I/O负载高，每秒执行大量高负载的随机I/O，但磁盘本身每秒能执行的随机I/O有限，导致正常SQL在磁盘执行时，若跑一些随机IO，你的磁盘太忙，顾不上你了，导致你本来很快的一个SQL，要等很久才能执行完毕，这时就可能导致正常SQL也变成慢查询。

也许网络负载高，导致你一个SQL语句要发到MySQL，光是等待获取一个和MySQL的连接，都很难，要等很久或MySQL自己网络负载太高，带宽打满，带宽打满后，你一个SQL也许执行很快，但其查出来的数据返回给你，网络都送不出去，也会变成慢查询。

若CPU负载过高，也会导致CPU过于繁忙去执行别的任务，没时间执行你的SQL。

所以慢查询不一定是SQL本身导致，若觉得SQL不应该会慢查询，结果他那个时间段跑这个SQL 就是慢，应排查当时MySQL服务器的负载，尤其看看磁盘、网络及 CPU 的负载，是否正常。

当某个离线作业瞬间大批量把数据往MySQL里灌入的时，他一瞬间服务器磁盘、网络以及CPU的负载会超高。

此时你一个正常SQL执行下去，短时间内一定会慢查询，类似问题，优化手段更多是控制你导致MySQL负载过高的那些行为，比如灌入大量数据，最好在业务低峰期灌入，别影响高峰期的线上系统运行。

但看了下MySQL服务器的磁盘、网络以及CPU负载，一切正常，似乎也不是这问题导致。看起来无解了？

慢 SQL 的头两步排查手段：

这两种办法都不奏效之后，第三步：用MySQL profilling工具去细致的分析SQL语句的执行过程和耗时。

这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析

打开profiling，使用

接着MySQL就会自动记录查询语句的profiling信息。此时若执行show profiles，就会给你列出各种查询语句的profiling信息，会记录下来每个查询语句的query id，所以你要针对你需要分析的query找到对他的query id，我们当时就是针对慢查询的那个SQL语句找到了query id。

然后针对单个查询语句，看其profiling信息，使用show profile cpu, block io for query xx，这里的xx是数字，此时就可以看到具体的profile信息。

除了cpu以及block io以外，还能指定去看这个SQL语句执行时候的其他各项负载和耗时。

会给你展示出来SQL语句执行时候的各种耗时，比如磁盘IO的耗时，CPU等待耗时，发送数据耗时，拷贝数据到临时表的耗时等，SQL执行过程中的各种耗时都会展示。

检查该SQL语句的profiling信息后，发现问题，其Sending Data耗时最高，几乎使用1s，占据SQL执行耗时的99%！其他环节耗时低可以理解，毕竟这种简单SQL执行速度真的很快，基本就是10ms级别，结果跑成1s，那肯定Sending Data就是问题根源！

这Sending Data在干啥呢？

MySQL官方释义：为一个SELECT语句读取和处理数据行，同时发送数据给客户端的过程，简单来说就是为你的SELECT语句把数据读出来，同时发送给客户端。

但这过程为啥这么慢？profiling确实是提供给我们更多的线索了，但似乎还是没法解决问题。但已经捕获到异常关键点，就是Sending Data的耗时很高！

接着：

看innodb存储引擎的一些状态，此时发现一个奇怪的指标：history list length，值特别高，达到上万。

MVCC就是多个事务在对同一个数据， 有人写，有人读，此时可以有多种隔离级别，对一个数据有个多版本快照链条，才能实现MVCC和各种隔离级别。

所以当你有大量事务执行时，就会构建这种undo多版本快照链条，此时history list length就会很高。然后在事务提交后，会有一个多版本快照链条的自动purge清理机制，清理了，该值就会降低。一般该值不应过高，所以注意到第二个线索：history list length过高，即大量的undo多版本链条数据没有清理。推测可能有的事务长时间运行，所以其多版本快照不能被purge清理，进而导致history list length过高。

经过这俩线索推测，在大量简单SQL变成慢查询时，SQL因为Sending Data环节异常，耗时过高；同时此时出现一些长事务长时间运行，大量的频繁更新数据，导致有大量undo多版本快照链条，还无法purge清理。

因为发现有大量的更新语句在活跃，而且有那种长期活跃的长事务一直在跑而没有结束，问了下系统负责人，在后台跑了个定时任务：他居然开了一个事务，然后在一个事务里删除上千万数据，导致该事务一直在运行。

这种长事务的运行会导致你删除时，仅只是对数据加了一个删除标记，事实上并没有彻底删除。此时你若和长事务同时运行的其它事务里再查询，他在查询时可能会把那上千万被标记为删除的数据都扫描一遍。因为每次扫描到一批数据，都发现标记为删除了，接着就会再继续往下扫描，所以才导致一些查询语句很慢。

那为何你启动一个事务，在事务里查询，凭什么就要去扫描之前那个长事务标记为删除状态的上千万的垃圾数据？讲道理，那些数据都被删了，跟你没关系了呀，你可以不去扫描他们 嘛！

而问题症结在于，那个 删除千万级数据的事务是个长事务 ！即当你启动新事务查询时，那个删除千万级数据的长事务一直在运行，它是活跃的！结合MVCC的Read View机制，当你启动一个新事务查询时，会生成一个Read View。你的新事务查询时，会根据ReadView去判断哪些数据可见及可见的数据版本号，因为每个数据都有个版本链条，有时你能可见的仅是这个数据的一个 历史 版本。

所以正是因为该长事务一直在运行，还在删除大量数据，而且这些数据仅是逻辑删除，所以此时你新开事务的查询还是会读到所有逻辑删除数据，也就会出现千万级的数据扫描，导致了慢查询！

所以禁止在业务高峰期运行那种删除大量数据的语句，因为这可能导致一些正常的SQL都变慢查询，因为那些SQL也许会不断扫描你标记为删除的大量数据，好不容易扫描到一批数据，结果发现是标记为删除的，于是继续扫描下去，导致慢查询！

直接kill那个正在删除千万级数据的长事务，所有SQL很快恢复正常。此后，大量数据清理全部放在凌晨执行，那个时候就没什么人使用系统了，所以查询也很少。

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL软件采用了双授权政策（本词条“授权政策”），它分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。由于其社区版的性能卓越，搭配PHP和Apache可组成良好的开发环境。

详情参考百度百科

http://baike.baidu.com/link?url=xd_51kxcU-gv-aBBrvgUQuPRQsv-Iuzn5HxiBUReoxthAvvzkI0oayCfZdggiVVR

1) 功能强大

MySQL 中提供了多种数据库存储引擎，各引擎各有所长，适用于不同的应用场合，用户可以选择最合适的引擎以得到最高性能，可以处理每天访问量超过数亿的高强度的搜索 Web 站点。MySQL5 支持事务、视图、存储过程、触发器等。

2) 支持跨平台

MySQL 支持至少 20 种以上的开发平台，包括 Linux、Windows、FreeBSD 、IBMAIX、AIX、FreeBSD 等。这使得在任何平台下编写的程序都可以进行移植，而不需要对程序做任何的修改。

3) 运行速度快

高速是 MySQL 的显著特性。在 MySQL 中，使用了极快的 B 树磁盘表（MyISAM）和索引压缩；通过使用优化的单扫描多连接，能够极快地实现连接；SQL 函数使用高度优化的类库实现，运行速度极快。

4) 支持面向对象

PHP 支持混合编程方式。编程方式可分为纯粹面向对象、纯粹面向过程、面句对象与面向过程混合 3 种方式。

5) 安全性高

灵活和安全的权限与密码系统，允许基本主机的验证。连接到服务器时，所有的密码传输均采用加密形式，从而保证了密码的安全。

6) 成本低

MySQL 数据库是一种完全免费的产品，用户可以直接通过网络下载。

7) 支持各种开发语言

MySQL 为各种流行的程序设计语言提供支持，为它们提供了很多的 API 函数，包括 PHP、ASP.NET、Java、Eiffel、Python、Ruby、Tcl、C、C++、Perl 语言等。

8) 数据库存储容量大

MySQL 数据库的最大有效表尺寸通常是由 *** 作系统对文件大小的限制决定的，而不是由 MySQL 内部限制决定的。InnoDB 存储引擎将 InnoDB 表保存在一个表空间内，该表空间可由数个文件创建，表空间的最大容量为 64TB，可以轻松处理拥有上千万条记录的大型数据库。

9) 支持强大的内置函数

PHP 中提供了大量内置函数，几乎涵盖了 Web 应用开发中的所有功能。它内置了数据库连接、文件上传等功能，MySQL 支持大量的扩展库，如 MySQLi 等，可以为快速开发 Web 应用提供便利。

---