为何表c、countries、currencies未使用主键索引？能否优化该查询？

一、为什么c、countries、currencies未使用主键索引？ #

这是MySQL优化器基于数据量与查询成本做出的选择：

• 表c：执行计划显示仅2行数据，全表扫描的成本远低于通过主键索引查找的成本（索引需要额外IO定位数据，小表全扫更直接高效），因此优化器选择全表扫描+BNL（块嵌套循环）join。
• 表countries（494行）和currencies（281行）：数据量不算大，结合驱动表返回的关联字段值分布（比如s表返回的country_id分散度高），优化器判断用join buffer缓存驱动表数据后，全表扫描被驱动表进行匹配的成本，低于通过主键索引逐行查找的成本，因此选择全表扫描+BNL join。另外如果表的统计信息过时，也会影响优化器的判断。

二、查询优化方案 #

1. 更新表统计信息 #

先让优化器获取准确的数据分布，可能自动调整执行计划：

ANALYZE TABLE c, countries, currencies;

2. 检查关联字段类型一致性 #

确认以下关联字段的数据类型、字符集、排序规则完全一致，类型不匹配会直接导致索引失效：

• s.country_id 和 countries.country_id
• countryperiods.currency_id 和 currencies.currency_id
• sic.c_id 和 c.c_id

3. 优化索引设计 #

• 针对sic表：查询过滤条件包含sic.c_id = '1'，关联条件是sic.s_id = s.s_id，建议创建组合索引：

  CREATE INDEX idx_sic_cid_sid ON sic(c_id, s_id);
  

  该索引可直接过滤出c_id=1的行，同时满足与s表的关联需求，减少后续关联的数据量，还能规避Using temporary和Using filesort（若排序字段来自sic）。
• 针对s表：WHERE条件中的OR逻辑容易导致索引失效，建议拆分查询为UNION：

  SELECT * FROM `s` 
  LEFT JOIN `users` AS `iu` ON iu.user_id = s.s_iu_id 
  LEFT JOIN `users` AS `uu` ON uu.user_id = s.uu_id 
  LEFT JOIN `sic` ON sic.s_id = s.s_id 
  LEFT JOIN `users` AS `ppu` ON ppu.user_id = sic.pure_price_user_id 
  LEFT JOIN `users` AS `spu` ON spu.user_id = sic.spu_id 
  LEFT JOIN `us` ON f_user_id = 8714 AND users.f_s_id = s.s_id 
  LEFT JOIN `c` ON c.c_id = sic.c_id 
  LEFT JOIN `countries` ON countries.country_id = s.country_id 
  LEFT JOIN `countries` AS `countries2` ON countries2.country_id = countries.parent_id 
  LEFT JOIN `continents` ON continents.continent_id = countries.continent_id 
  LEFT JOIN `countryperiods` ON countryperiods.countryPeriod_id = s.countryPeriod_id 
  LEFT JOIN `currencies` ON currencies.currency_id = countryperiods.currency_id 
  LEFT JOIN `serrations` AS `s1` ON s.serration_width_id = s1.id 
  LEFT JOIN `serrations` AS `s2` ON s.serration_height_id = s2.id 
  LEFT JOIN `colors` ON colors.color_id = s.color_id 
  LEFT JOIN `colors` AS `c2` ON c2.color_id = s.background_id 
  LEFT JOIN `colors` AS `c3` ON c3.color_id = s.color2_id 
  WHERE s.fsis_id = '141518' 
    AND sic.c_id = '1' 
    AND s.s_enabled = 1 
    AND s.s_exists = 1
  UNION
  SELECT * FROM `s` 
  LEFT JOIN `users` AS `iu` ON iu.user_id = s.s_iu_id 
  LEFT JOIN `users` AS `uu` ON uu.user_id = s.uu_id 
  LEFT JOIN `sic` ON sic.s_id = s.s_id 
  LEFT JOIN `users` AS `ppu` ON ppu.user_id = sic.pure_price_user_id 
  LEFT JOIN `users` AS `spu` ON spu.user_id = sic.spu_id 
  LEFT JOIN `us` ON f_user_id = 8714 AND users.f_s_id = s.s_id 
  LEFT JOIN `c` ON c.c_id = sic.c_id 
  LEFT JOIN `countries` ON countries.country_id = s.country_id 
  LEFT JOIN `countries` AS `countries2` ON countries2.country_id = countries.parent_id 
  LEFT JOIN `continents` ON continents.continent_id = countries.continent_id 
  LEFT JOIN `countryperiods` ON countryperiods.countryPeriod_id = s.countryPeriod_id 
  LEFT JOIN `currencies` ON currencies.currency_id = countryperiods.currency_id 
  LEFT JOIN `serrations` AS `s1` ON s.serration_width_id = s1.id 
  LEFT JOIN `serrations` AS `s2` ON s.serration_height_id = s2.id 
  LEFT JOIN `colors` ON colors.color_id = s.color_id 
  LEFT JOIN `colors` AS `c2` ON c2.color_id = s.background_id 
  LEFT JOIN `colors` AS `c3` ON c3.color_id = s.color2_id 
  WHERE s.fsis_id = '141518' 
    AND sic.c_id = '1' 
    AND s.s_iu_id = '8714' 
    AND s.s_exists = 1
  ORDER BY `c_number` ASC, `cn_prepend` ASC, `cn_append` ASC 
  LIMIT 10000;
  

  同时给s表创建两个针对性索引：

  CREATE INDEX idx_s_fsis_exists_enabled ON s(fsis_id, s_exists, s_enabled);
  CREATE INDEX idx_s_fsis_exists_iuid ON s(fsis_id, s_exists, s_iu_id);
  

  让每个子查询都能高效利用索引过滤数据。

4. 避免SELECT * #

只查询实际需要的字段，减少数据传输与内存开销，还能为覆盖索引的创建提供可能（把查询字段加入索引，避免回表操作）。

5. 调整join buffer参数（可选） #

若系统内存充足，且这类BNL join频繁出现，可适当调大join_buffer_size参数（比如从默认256K调整到1M），但不要过度调大，避免内存资源浪费。

6. 优化排序逻辑 #

确认ORDER BY的c_number、cn_prepend、cn_append所属表，若属于sic表，将这些字段加到之前创建的idx_sic_cid_sid索引中，变成(c_id, s_id, c_number, cn_prepend, cn_append)，实现索引排序，消除Using temporary和Using filesort，这对性能提升效果明显。

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