MariaDB中count(*)用索引但select *未用合适索引的优化求助

执行count(*) from table1 WHERE name like 'done%'时，name索引是覆盖索引（仅需统计行数，无需回表取全量数据），因此优化器选择使用该索引；而执行select *时，若用name索引则需要回表获取所有列数据——由于符合条件的行数（913万）接近总数据量的一半（1826万），优化器评估回表的IO成本高于全表扫描，最终选择全表扫描。

以下是无需修改查询语句的可行方案：

1. 创建覆盖索引 #

创建以name开头、包含所有表列的覆盖索引，这样select *可直接从索引中获取所有数据，无需回表，优化器会优先选择该索引：

CREATE INDEX idx_name_covering ON table1 (name, idsite, date1, date2, period, ts_archived, value);

注：InnoDB中主键列会自动包含在二级索引中，因此无需手动添加idarchive。

2. 更新统计信息 #

若优化器依赖的统计信息不准确，可能导致成本判断错误。执行以下语句更新表的统计信息，帮助优化器做出更合理的选择：

ANALYZE TABLE table1;

3. 调整优化器成本参数 #

通过调整成本权重，让优化器更倾向于使用索引而非全表扫描：

• 增大全表扫描的成本权重（让全表扫描看起来更昂贵）：

  SET GLOBAL read_rnd_cost = 4; -- 默认值为2，可根据实际测试调整
  

• 降低索引扫描的成本权重：

  SET GLOBAL read_cost = 1; -- 默认值为1，若当前值更高可调整
  

注：参数调整需结合实际业务场景测试，避免影响其他查询。

4. 修改主键顺序（需评估影响） #

若业务允许，可将联合主键调整为(name, idarchive)，这样主键索引本身就是以name开头的有序索引，select *时可直接利用主键索引进行范围扫描，无需回表：

ALTER TABLE table1 DROP PRIMARY KEY, ADD PRIMARY KEY (name, idarchive);

此方案需评估对其他依赖原主键的查询、业务逻辑的影响，谨慎操作。

5. 调整优化器开关 #

启用prefer_ordering_index开关，让优化器优先选择能提供有序结果的索引（即使成本略高）：

SET GLOBAL optimizer_switch = 'prefer_ordering_index=on';

该开关会让优化器更倾向于使用索引扫描，而非全表扫描后排序，适用于此类范围查询场景。

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