含索引与非索引字段的SELECT语句偶发耗时过长原因排查

问题背景 #

我有一张约3000万行的MyISAM表，通常查询耗时仅5-6ms，但偶尔有请求耗时会超过60秒。

表结构 #

CREATE TABLE `table` (
  id int(11) NOT NULL,
  A int(11) NOT NULL,
  B varchar(32) NOT NULL,
  C tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `table`
  ADD PRIMARY KEY (id),
  ADD KEY A (A),
  ADD KEY B (B);

索引A的基数为200万，索引B的基数为1000。

查询语句 #

SELECT * FROM `table`
WHERE A = someAvalue
    AND B = 'some B value'
    AND C = 0
ORDER BY id DESC
LIMIT 1;

执行计划 #

id|select_type|table|type|possible_keys|key|key_len|ref  |rows|Extra 
1 |SIMPLE     |table|ref |A,B          |B  |34     |const|1   |Using index condition; Using where; Using filesort 

注：本示例中的key_len和rows无参考意义。

异常现象 #

• 去掉查询条件中的AND C = 0时，查询耗时恢复正常。
• 当索引A和B无匹配值时，该查询会出现耗时暴涨的情况。

原因解析 #

• 索引选择导致扫描行数过载：执行计划显示MySQL选了索引B，但索引B的基数仅1000，意味着每个B值对应约3万行数据（3000万/1000）。加上C=0这个非索引条件后，MySQL得先通过索引B找出所有匹配B值的行，再逐行回表读数据检查C字段是否符合要求。如果当前B值对应的行里，符合C=0的记录极少甚至没有，MySQL必须扫完这几万行才能确定结果，大表上的IO开销直接拉满。

• 排序操作的额外成本叠加：查询要求ORDER BY id DESC LIMIT 1，带C=0条件时，MySQL需要收集所有符合A、B、C条件的行的id，再排序后取第一条。要是符合条件的记录很少，就得扫描大量行来凑候选数据；而去掉C=0条件时，能更快定位到符合A、B的行，排序的数据集极小，甚至可以提前终止扫描。

• MyISAM表锁的放大影响：MyISAM用的是表级锁，查询过程中如果有其他写操作（比如INSERT、UPDATE），会直接阻塞查询。尤其是在扫描大量行的时候，锁等待的概率大幅提升，耗时会被进一步拉长。

• 无匹配值时的极端扫描：当A和B的组合完全没有匹配数据时，MySQL还是会通过索引B扫完所有对应B值的行，逐行检查A和C的条件，等于要遍历3万行才能确认无结果，这种全量扫描自然会让耗时暴涨到几十秒。

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