MariaDB 5.5.68下如何阻止阻塞MyISAM的有毒SELECT查询？

一、从根源优化查询与索引 #

1. 创建针对性复合索引 #

当前查询执行计划显示走了PRIMARY索引（全表扫描），这是大表慢查询的核心原因。针对你的查询条件（subproviderid=1 + logdate范围）和排序需求，创建复合索引：

CREATE INDEX idx_subprovider_logdate ON tbllog (subproviderid, logdate);

如果想进一步减少回表开销，可创建包含logid的覆盖索引：

CREATE INDEX idx_subprovider_logdate_logid ON tbllog (subproviderid, logdate, logid);

创建后，查询会通过复合索引快速定位符合条件的行，执行计划的type会变为range，扫描行数大幅降低，查询时间缩短后自然不会成为阻塞表的有毒查询。

2. 调整查询逻辑缩小扫描范围 #

如果业务允许，可拆分查询为子查询先获取目标logid，再关联取数据：

SELECT t.*
FROM tbllog t
JOIN (
    SELECT logid
    FROM tbllog
    WHERE subproviderid=1 
      AND logdate BETWEEN '2021-01-01 00:00:00' AND '2022-10-25 00:00:00'
    ORDER BY logid LIMIT 500
) tmp ON t.logid = tmp.logid
ORDER BY t.logid;

子查询利用复合索引快速筛选出前500个logid，再关联主表仅读取需要的行，避免全表扫描。

二、执行前识别并拦截有毒查询 #

1. 用EXPLAIN预估查询成本 #

对用户提交的查询先执行EXPLAIN，通过扫描行数判断是否为潜在慢查询：

EXPLAIN SELECT * FROM tbllog WHERE logdate BETWEEN '2021-01-01 00:00:00' AND '2022-10-25 00:00:00' AND subproviderid=1 ORDER BY logid LIMIT 500;

若rows字段返回值远大于500（比如超过10万），说明查询会扫描大量数据，可直接拒绝执行并提示用户缩小日期范围。MariaDB 5.5的EXPLAIN预估虽有误差，但对大表全表扫描场景的判断足够准确。

2. 设置查询超时限制 #

MariaDB 5.5虽无max_execution_time参数，但可在应用层控制查询超时（比如通过驱动设置超时时间），一旦查询执行超过阈值就中断，避免长时间阻塞表。同时可调整myisam_sort_buffer_size参数，限制查询占用的内存资源。

三、缓解MyISAM的阻塞特性 #

1. 开启并发插入 #

设置concurrent_insert=2，允许MyISAM在表无空洞（无删除数据）时，INSERT与SELECT并发执行：

SET GLOBAL concurrent_insert = 2;

若表存在删除产生的空洞，定期执行OPTIMIZE TABLE tbllog;整理表空间，恢复并发插入能力。

2. 拆分大表 #

按LOGDATE进行分表（比如按年/月拆分），用户查询时仅访问对应时间段的分表，大幅减少单表数据量。例如创建tbllog_2021、tbllog_2022，查询时根据日期范围自动路由到对应分表。

关于InnoDB切换的补充 #

虽然你暂不考虑切换，但可以快速评估利弊：

• 弊端：大表转换会锁表（可分批次或用工具在线转换），InnoDB需要更多内存（需调整innodb_buffer_pool_size）；
• 优势：行级锁彻底解决SELECT阻塞INSERT的问题，支持事务与崩溃恢复，数据安全性更高。长远来看是更彻底的解决方案。

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