如何不修改php.ini不设超时限制优化SQL避免执行超时错误

核心问题定位 #

你这条语句超时的核心原因是隐式笛卡尔积连接+全表扫描+冗余运算，即使加了LIMIT，数据库也需要先完成全表关联、过滤、分组、排序后才能截取前12条，性能消耗极高。

具体优化步骤 #

• 1. 修正连接逻辑，替换隐式连接为LEFT JOIN #

  原语句用逗号分隔两张表属于隐式内连接，会先生成两张表的笛卡尔积（总记录数=A表行数*B表行数）再过滤，数据量过万就会直接超时。
  结合你的业务逻辑（找符合格式的原图，对应有优化图/未生成优化图的记录），改为以ojm_pages_articles_photos为主表的左连接：

  LEFT JOIN `ojm_images_optimized` 
  ON `ojm_pages_articles_photos`.`url_articles_photo` = `ojm_images_optimized`.`url_image_original`
  

  原条件里的OR ojm_images_optimized.url_image_optimized = ''完全多余，左连接后未匹配到优化记录的行对应优化表字段为NULL，后续直接判断优化表主键 IS NULL OR url_image_optimized != ''即可，避免交叉匹配。

• 2. 去掉冗余运算 #

  DISTINCT和GROUP BY功能重复，保留一个即可，直接删除DISTINCT关键字即可减少一次去重运算。

• 3. 优化后缀匹配逻辑，避免全表扫描 #

  原语句用LIKE '%jpeg'这类后缀模糊查询，无法命中普通索引，会触发全表扫描：
  方案1（推荐）：新增photo_suffix varchar(10)字段存储图片后缀，设置字段排序规则为utf8mb4_general_ci（大小写不敏感），给该字段加索引，查询时直接用photo_suffix IN ('jpeg','jpg','jpe','png','bmp')过滤。
  方案2（兼容旧表不新增字段）：如果是MySQL 8.0+/MariaDB 10.2+，可以建函数索引：

  CREATE INDEX idx_photo_url_suffix ON ojm_pages_articles_photos (RIGHT(url_articles_photo, 4));
  

  查询时用RIGHT(url_articles_photo,4) IN ('.jpeg','.jpg','.JPEG','.JPG','.jpe','.png','.PNG','.bmp')过滤，可命中函数索引。

• 4. 新增必要索引 #

  给以下字段加索引，避免全表扫描：

  -- 优化表关联字段索引
  CREATE INDEX idx_opt_original_url ON ojm_images_optimized(url_image_original);
  -- 原图表排序字段是主键默认有索引，后缀索引按上面的方案加即可
  

  如果排序逻辑固定，可以加联合索引进一步优化：

  CREATE INDEX idx_opt_sort ON ojm_images_optimized(url_image_original, url_image_optimized, optimized_is_smaller);
  

• 5. 优化分组和排序逻辑 #

  原排序条件里ojm_images_optimized.optimized_is_smaller='Oui'可以改为CASE WHEN optimized_is_smaller='Oui' THEN 1 ELSE 0 END DESC，语义更清晰，同时如果分组和排序字段能命中索引，可以避免数据库生成临时表和文件排序。

改写后参考SQL #

SELECT 
  p.`id_pages_articles_photos`, 
  p.`url_articles_photo`, 
  p.`id_ojm_peoples` AS `id_ojm_peoples_uploader` 
FROM `ojm_pages_articles_photos` p
LEFT JOIN `ojm_images_optimized` o
ON p.`url_articles_photo` = o.`url_image_original`
WHERE 
  -- 按后缀过滤，这里用方案1的字段为例，换成方案2的RIGHT条件也可
  p.`photo_suffix` IN ('jpeg','jpg','jpe','png','bmp')
  AND (o.`url_image_original` IS NOT NULL OR o.`url_image_optimized` IS NULL)
GROUP BY o.`url_image_optimized`, p.`url_articles_photo`
ORDER BY 
  p.`id_pages_articles_photos` DESC, 
  CASE WHEN o.`optimized_is_smaller`='Oui' THEN 1 ELSE 0 END DESC
LIMIT 0,12

内容的提问来源于stack exchange，提问作者Man Of God