SQL血的教训--故障汇总(更新到2023年12月28日)

 
1，每次查询的数据要有限制
     2013年1月  产品独立数据库，由于多条SQL每次查询数据超过几千条，有些超过10万条数据未分页，造成应用服务器CPU有时持续100%。

2，禁止循环调用SQL

    2011年5月  程序调用Sys_User查询，每分钟执行超过3千次，造成服务器CPU持续100%

3，禁止使用递归方法调用SQL；

    方法内如出现数据异常，极易造成查询死循环

4，禁止每次写入大量数据
    2011年7月  深圳库导入订单的存储过程中加入一条insert语句，每次insert超过100万条，几个小时将深圳数据库服务器磁盘写满，业务无法运行

5，禁止在事务中使用循环加入缓存；
    2011年4月  批量更新Sys_user用户后，又在事务里循环加入缓存，造成事务运行太长，严重影响系统性能。

6，必须在语句的具体字段要明确列出

  2011年3月  查询Product表数据，每次查询出全部字段，包括不需要的产品详细描述信息，造成系统网卡流量暴高。

7，禁止使用业务逻辑大的事务
    2011年4月开始，前台客户最长等待需要超过30S完成下单，查询发现下单整个操作包在一个巨大的事务中，严重影响客户下单。

8，禁止使用SQL游标Cursor；

    游标使用不当，会严重影响系统性能

9，Select查询放到事务外

    2011年6月  某条insert语句很慢，检查发现取表的Sequence语句放在insert的事务里，在高并发下，失败率很高。

10， 禁止一次删除大量数据
     2012年11月  Customer_LoginLog表由于前台写入暴增，造成当天数据超过1500万条，SQL归档执行了6个小时未删除完，影响业务操作超过6个小时。

11，禁止主键删除再新建

    2016年，Coupon表数据，有1亿多条数据，查看其主键有大量碎片，当时不想用online的方式重建，还是想先删除，后创建。因为当时是早上4点多，

开发人员说基本没人使用， 就没有影响，就先删除主键，结果删除后，没想到系统有大量查询coupon的请求，而主键没有，数据库运行很慢，kill后一直有，

后来没办法，禁用这个连coupon表的账户，等主键创建成功后，再启用账户

12，禁止用系统登录触发器

    2017年9月，因为黑客攻击，想在数据库做个监控，对凌晨1-7点间登录数据库的做个监控，用了SQL Server的系统登录触发器，记录登录信息，结果触发器先建好，记录信息的表没有建，导致 登录触发器审计失败，这个失败导致，其他用户登录时也会触发这个系统登录触发器，也报登录失败，登录不了，自己试了其他4台备机，也都登录不了，前台，后台和wms程序全部报错，查了网上资料也没找到合适的方法，程序一直在报错，这时突然发现，已经登录同步实时从库（8.10），虽然不能登录，

    但是他已经登录了，这时我看，可以使用，我这时立刻删除刚建的触发器，执行AlwaysON切换，把数据库从主库切到实时备库。  经历了20多分钟的重大事故终于解决。

  事后发现有2个解决办法，1，备份master库，出现异常时还原master库   2，CMD最小化登录数据库删除触发器

     我这边发现就是还有一个登录好的实时备库，进去删除。

     因为这20多分钟经历太痛苦，一直不想去模拟2种处理方案，因为后续也不会用登录系统触发器。 

13，MySQL的从库Offline模式

     2020年9月 ， 清理异常数据，重设为online模式 

14，MySQL的内核bug，导致十几从库回放binlog报错

      2020年9月 ， 把主库的MySQL的binlog改成ROW模式，设置成FULL 

15， 云Redis，突然被云商下调带宽到正常，导致带宽打满

      扩容Redis的带宽 

16，云Redis，晚上6点开始，CPU持续80%左右

       关闭和重启异常的IP的程序 

17，云Redis的内存持续增长

      在key里加上日期，key不停增长，内存暴涨，删除无用的key，同时修改程序 

18，云Redis的CPU高峰期，CPU突破60%

    查询最耗时的命令，将hget命令一次取一个，改成mget命令一次取多个key，CPU下降

19，MySQL高并发曝高，JAVA网关冲曝

     原查询redis改到MySQL，业务高峰导致MySQL的CPU曝高后，程序有3次重试，大量连接冲击网关，网关假死，线上程序大面积故障

  将大量的查询改到Redis，同时去掉程序的show columns from 表的命令

 20，MySQL从库一直延迟

      凌晨大量的更新数据，从库有简单SQL查询导致延迟很大，批量kill掉卡住的SQL，不延迟后提升从库内存从32G升级到64G      

 21，错误SQL执行全表更新

       亚马逊云MySQL执行的错误SQL，由于SQL太长没注意SQL有错误，执行后全表数据更新，发现是sql-mode设置问题，导致错误SQL能执行成功。

  发现执行错误后，通过亚马逊云的实例数据回滚，再通过数据导出还原。

 

22，大量长事务无法提交定位（2023年4月）

     2023年3月，朋友新上线的订单系统，在阿里云RDS MySQL8.0上有大量未提交的事务，通过查询事务语句，又没有具体的执行SQL，能定位到具体服务，但服务业务代码太多，通过SQL全审计和慢日志都无法定位到具体程序方法， 后来有人发现执行事务开始时间有规律，就是每个事务的开始时间都是每分钟执行。后来和研发等沟通关闭xxl-job的相关的订单，3个每分钟job调用订单相关服务，发现数据库未事务不再增加，再看这3个服务，定位未提交事务的程序具体位置。

     以前出现这样未提交事务，一般通过下列SQL语句查出未提交的事务：

select timestampdiff(SECOND,trx_started,now()) diffsecond,b.host,b.user,concat('kill ',b.ID,' ;') killsql,b.db,
IFNULL(b.INFO,a.trx_query) runsql
from information_schema.innodb_trx a left join
information_schema.PROCESSLIST b on a.trx_mysql_thread_id=b.ID
-- where timestampdiff(SECOND,trx_started,now()) >=20  ;
order by 1 desc limit 50;  

     

     上面查询后，结果处理方法：

      1， 有SQL语句： 通过SQL去程序定位到方法

      2， 没有SQL语句： 通过下列命令去具体IP（172.17.16.137）的机器执行下列命令：

netstat -atpln | grep 46154   根据host的端口查看pid
ps -ef|grep 10935       这个10935就是上面查出的pid，查看对应的服务

      3，如果有日志ELK或CAT监控，可以Kill进程，去ELK日志或者CAT根据，出错，IP和端口（如172.17.16.137和46154) 反查执行的进程

     这次故障处理，通过SQL事务的开始时间找到规律，定位到xxl-job引起，也是第一次用到，故障处理有时真是考验细心和耐心。

 23 ，只读从库有未提交事务，加字段不成功，导致程序无法连接腾讯云MySQL从库 （2023年12月28日）

      上周在主库加了一个字段，数据不到百万条，执行比较快，没有发现问题，结果周末发现程序连接其中一个腾讯云只读RO组的数据库连不上，但通过Navicat 是可以连，研发一直在找问题，也问我数据库有做什么调整，我这几天也没调整数据库配置，

后来周一来，这问题还是有，通过22的查询长事务一查，发现从库5有未提交的事务，有2个SQL时间还很长   

  kill掉上面的长事务后，发现执行的加字段语句才开始执行：

   执行完成加语句的SQL，系统能连该只读从库了。 腾讯云的技术支持解释：”ddl操作需要获取mdl锁，如果没有获取到mdl锁会一直处于waiting metadata lock并且阻塞后续对该表的操作“，只读从库未提交的只读事务，是能影响DDL获取mdl锁。