排查并优化order_list表高频SELECT *查询导致的资源耗尽问题

一、紧急止损：先恢复服务可用性 #

• 批量终止恶意连接：执行SHOW FULL PROCESSLIST找到所有执行SELECT * FROM order_list的线程ID，用KILL [线程ID];批量终止（可写脚本批量处理，比如结合awk提取ID）。如果是MySQL 8.0+，可使用PROCESSLIST表查询后批量杀：

  SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE info LIKE '%SELECT * FROM order_list%';
  

• 临时限制查询并发：调整MySQL参数innodb_thread_concurrency设置合理的并发线程数（比如设为CPU核心数的2倍），避免恶意查询占满资源；同时调小max_user_connections限制单个数据库用户的并发连接数。
• 临时限制IP访问：通过iptables或服务器安全组，将触发大量请求的来源IP临时拉黑；如果是CloudFront回源导致，检查CloudFront的缓存规则是否失效，临时调高缓存TTL减少回源。

二、定位查询来源：找到问题根源 #

• 开启MySQL全量查询日志：临时开启general_log记录所有查询，同时开启log_hostname=ON和log_connections=ON，日志中会包含请求的来源IP、数据库用户名、查询语句及线程ID，对应PROCESSLIST就能定位来源。注意：全量日志会占用大量磁盘空间，定位到问题后立即关闭。

  SET GLOBAL general_log = ON;
  SET GLOBAL general_log_file = '/var/log/mysql/general.log';
  

• 分析连接属性：在SHOW FULL PROCESSLIST中重点看Host和User字段：
  • 如果是集中的几个IP，大概率是攻击源或异常爬虫；
  • 如果是CloudFront的IP，说明缓存未生效，请求直接回源，需检查CloudFront的缓存策略、缓存键设置；
  • 如果是特定数据库用户，检查该用户的权限是否泄露，是否被第三方应用滥用。
• 排查代码与隐藏调用：
  • 全局搜索代码仓库（包括前端JS、后端代码、定时脚本）中的order_list相关查询，注意AJAX异步请求、第三方嵌入脚本（如统计、广告）、ORM框架的懒加载逻辑是否触发意外查询；
  • 检查服务器定时任务（crontab -l），是否有脚本在批量执行该查询；
  • 排查是否有其他应用（如测试环境、监控脚本）连接到生产数据库执行该查询。
• 网络抓包分析：用tcpdump抓取MySQL端口（默认3306）的数据包，分析查询的来源IP和请求上下文：

  tcpdump -i any port 3306 -w mysql_traffic.pcap
  

  抓取Web服务器端口（80/443）的数据包，查看哪些请求触发了数据库查询，尤其是隐藏的API接口请求。

三、长期防护与优化：避免问题复发 #

数据库层面 #

• 限制查询权限：给数据库用户配置最小权限，禁止非必要用户访问order_list表；严格禁止SELECT *写法，只允许查询业务所需字段，针对常用查询添加合适的索引。
• 缓存优化：用Redis/Memcached等缓存层缓存order_list的查询结果，设置合理的缓存过期时间，减少直接查询数据库的次数；避免缓存大面积失效导致的雪崩效应。
• 连接配置优化：调整wait_timeout和interactive_timeout参数，缩短空闲连接的存活时间；开启max_connections的合理上限，防止连接数耗尽。
• 数据拆分：随着数据量增长，可按订单创建时间或订单ID对order_list进行分表，降低单表查询压力。

防护层面 #

• 完善CloudFront配置：确保首页及可缓存内容的缓存规则生效，设置合理的TTL；开启CloudFront WAF，添加速率限制规则（如单IP每分钟最多100次请求），拦截恶意流量。
• Web服务器限流：在Nginx/Apache中配置速率限制，比如Nginx的limit_req_zone模块，限制单IP的请求频率，防止大量请求压垮后端。
• 隐藏数据库端口：通过安全组或防火墙，禁止MySQL端口（3306）对外暴露，只允许Web服务器的IP访问数据库。

代码层面 #

• 添加查询日志：在应用层给所有数据库查询添加日志，记录查询语句、调用栈、来源IP、请求URL，方便后续快速定位问题。
• 检查缓存逻辑：验证缓存失效策略，避免因缓存键错误、过期时间过短导致的频繁回源查询；添加缓存击穿防护（如互斥锁）。

内容的提问来源于stack exchange，提问作者adit