为何MySQL已建channel索引仍执行全表扫描？及ANALYZE TABLE必要性

问题背景 #

现有三张表结构如下：

testdata1: id (主键)                      -> 1000000 行
testdata2: id (主键), channel (已创建索引) -> 10000 行
testdata3: id (主键)                      -> 1000 行

执行以下查询时，发现testdata2被执行全表扫描：

explain format=tree 
select * 
from testdata1 
inner join testdata2 on testdata1.id = testdata2.channel 
inner join testdata3 on testdata2.channel = testdata3.id 
where testdata1.id < 100;

初始执行计划：

EXPLAIN: -> Nested loop inner join  (cost=8014.20 rows=9984)
    -> Nested loop inner join  (cost=4519.80 rows=9984)
        -> Table scan on testdata2  (cost=1025.40 rows=9984)
        -> Filter: ((testdata1.id < 100) and (testdata1.id = testdata2.`channel`))  (cost=0.25 rows=1)
            -> Single-row index lookup on testdata1 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)
    -> Filter: (testdata2.`channel` = testdata3.id)  (cost=0.25 rows=1)
        -> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)

1. 为何MySQL未使用testdata2(channel)列的索引？ #

MySQL优化器依赖表的统计信息选择最优执行计划。如果testdata2的统计信息过时或不准确，优化器会误判成本：

• 从初始执行计划可见，优化器预估扫描testdata2会返回9984行（接近全表10000行），它认为大部分channel值都能匹配后续关联条件，因此判断全表扫描的成本低于索引查找。
• 实际场景中，符合testdata1.id < 100的channel值数量极少，本该走索引，但错误的统计信息让优化器做出了错误选择。

更新情况 #

执行analyze table testdata2后，MySQL成功使用了channel索引，更新后的执行计划：

EXPLAIN: -> Nested loop inner join  (cost=241.34 rows=156)
    -> Nested loop inner join  (cost=186.85 rows=156)
        -> Filter: (testdata1.id < 100)  (cost=20.09 rows=99)
            -> Index range scan on testdata1 using PRIMARY over (id < 100)  (cost=20.09 rows=99)
        -> Index lookup on testdata2 using a_temp_index (channel=testdata1.id), with index condition: (testdata1.id = testdata2.`channel`)  (cost=1.53 rows=2)
    -> Filter: (testdata2.`channel` = testdata3.id)  (cost=0.25 rows=1)
        -> Single-row index lookup on testdata3 using PRIMARY (id=testdata2.`channel`)  (cost=0.25 rows=1)

2. 创建索引后是否必须执行analyze table命令？ #

不是必须，但特定场景下建议执行：

• MySQL创建索引后，通常会自动收集基础统计信息，但如果表数据量大、数据分布不均，或创建索引后发生大量数据变更，统计信息可能不够精准。
• 当优化器生成错误执行计划（如本案例的全表扫描）时，analyze table可强制更新统计信息，让优化器基于准确的数据分布选择更优方案。
• 日常操作中，若创建索引后查询性能符合预期，无需手动执行；仅当出现执行计划异常时，再考虑用该命令更新统计信息。

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