为何SQL查询未使用第一张表索引？如何优化无WHERE/LIMIT的查询

原始查询语句 #

Explain 
select SQL_NO_CACHE p.id as product_id, 
      p.price_amount, 
      p.is_male, 
      p.is_female, 
      p.is_accessories, 
      p.is_contact_lens, 
      c.id as color_id, 
      b.name as brand, 
      p.name, 
      c.color, 
      c.code, 
      c.pohoda_sku, 
      c.on_stock, 
      c.not_send_to_kiosks, 
      (p.is_active && !p.is_archive && c.is_active && !c.is_archive) as is_active, 
      c.is_visible, 
      c.abbr 
from product_colors as c 
left join products as p 
      on p.id = c.product_id 
left join brands as b 
      on p.brand_id = b.id 

order by c.on_stock desc

执行计划截图 #

问题 #

1. 为何该查询未使用第一张表（product_colors）的索引？
2. 该查询在phpMyAdmin中耗时0.0708秒，但在网页端执行耗时超过1秒。不想添加WHERE或LIMIT子句，能否优化该查询？或者是否与php.ini等配置中的内存限制有关？

解答 #

一、未使用product_colors索引的原因 #

• 全表扫描成本更低：查询无WHERE过滤条件，需返回product_colors所有行。若表数据量不大，优化器会判定全表扫描的开销比用索引更小（避免索引回表的额外成本）。
• 缺少合适的覆盖索引：即便on_stock有索引，由于查询需要返回该表多个列，单一的on_stock索引无法覆盖所有查询列，优化器会放弃使用（否则频繁回表读取数据反而更慢）。
• 排序需求未被索引覆盖：order by c.on_stock desc若没有对应索引，数据库会先全表扫描再排序（可能触发filesort），这也是优化器选择全表扫描的原因之一。

二、phpMyAdmin与网页端耗时差异的原因 #

• 结果集处理方式不同：phpMyAdmin默认分页显示结果（仅加载前几十行），而网页端通常一次性取出所有结果并做业务处理（如数据遍历、前端渲染），这部分额外处理时间会拉高总耗时，并非查询本身执行变慢。
• 内存限制的影响：php.ini的memory_limit过小可能导致处理大结果集时频繁触发内存回收，甚至报错，但单纯耗时增加更可能是结果集传输、后端/前端处理的问题，而非内存限制直接导致。

三、不添加WHERE/LIMIT的优化方案 #

1. 创建覆盖索引
   针对product_colors表创建包含排序字段和所有查询所需列的覆盖索引，让数据库直接从索引取数，避免回表和额外排序：

   CREATE INDEX idx_product_colors_covering ON product_colors (on_stock DESC)
   INCLUDE (id, color, code, pohoda_sku, not_send_to_kiosks, is_active, is_archive, is_visible, abbr, product_id);
   

   注：若MySQL版本不支持INCLUDE，可将所有列加入索引列（但索引体积会变大，需权衡）。

2. 确保关联表索引有效

   • 确认products.id、brands.id为自带索引的主键。
   • 若products.brand_id无索引，创建索引：

     CREATE INDEX idx_products_brand_id ON products (brand_id);
     

3. 网页端性能优化

   • 在后端实现逻辑分页，分批获取和处理数据，避免一次性加载所有结果。
   • 前端采用虚拟滚动等方式处理大量数据，减少DOM操作开销。

4. 数据库配置调优

   • 检查MySQL的sort_buffer_size、read_buffer_size配置，确保足够大以避免排序时使用磁盘临时表。
   • 若结果集确实很大，适当调高php.ini的memory_limit（如设置为256M或512M），避免内存不足影响处理速度。

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