mysql or 不走索引的情况

最新推荐文章于 2025-10-07 13:58:42 发布

原创最新推荐文章于 2025-10-07 13:58:42 发布 · 1k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#java

mysql 专栏收录该内容

3 篇文章

订阅专栏

博客探讨了一条复杂的SQL查询在全表扫描下效率低下的问题。通过拆分查询并使用UNION，实现了利用索引来提高查询性能。文章详细展示了优化前后的执行计划，并强调了正确使用索引在数据库查询优化中的重要性。

直接上sql

SELECT
	t3.`id`,
	t3.img,
	t4.`name` AS className,
	t1.`category_sku_code` AS skuCode 
FROM
	mj_goods t1
	LEFT JOIN mj_category_sku t2 ON t2.tenant_id = 63 
	AND t2.`id` = t1.`category_sku_id`
	LEFT JOIN mj_category t3 ON t3.tenant_id = 63 
	AND t3.id = t2.category_id
	LEFT JOIN mj_category_class t4 ON t4.tenant_id = 63 
	AND t3.`class_id` = t4.`id` 
WHERE
	t1.tenant_id = 63 
	AND (t1.category_sku_code = ('SMKM5892-LPD蓝色L') 
	
	or t1.line_goods_num = ('SMKM5892-LPD蓝色L')
	or t3.num IN ('SMKM5892-LPD蓝色L') )

其中,category_sku_code ,line_goods_num ，num都分别建了索引,执行计划如下

执行计划
可以看到，全表扫描了
如果拆开分别查询，再联合，这样就会使用索引

SELECT
	t3.`id`,
	t3.img,
	t4.`name` AS className,
	t1.`category_sku_code` AS skuCode 
FROM
	mj_goods t1
	LEFT JOIN mj_category_sku t2 ON t2.tenant_id = 63 
	AND t2.`id` = t1.`category_sku_id`
	LEFT JOIN mj_category t3 ON t3.tenant_id = 63 
	AND t3.id = t2.category_id
	LEFT JOIN mj_category_class t4 ON t4.tenant_id = 63 
	AND t3.`class_id` = t4.`id` 
WHERE
	t1.tenant_id = 63 
	AND t1.category_sku_code IN ( 'SMKM5892-LPD蓝色L' ) UNION
SELECT
	t3.`id`,
	t3.img,
	t4.`name` AS className,
	t1.`line_goods_num` AS skuCode 
FROM
	mj_goods t1
	LEFT JOIN mj_category_sku t2 ON t2.tenant_id = 63 
	AND t2.`id` = t1.`category_sku_id`
	LEFT JOIN mj_category t3 ON t3.tenant_id = 63 
	AND t3.id = t2.category_id
	LEFT JOIN mj_category_class t4 ON t4.tenant_id = 63 
	AND t3.`class_id` = t4.`id` 
WHERE
	t1.tenant_id = 63 
	AND t1.line_goods_num IN ( 'SMKM5892-LPD蓝色L' ) UNION
SELECT
	t1.`id`,
	t1.img,
	t2.`name` AS className,
	t1.num AS skuCode 
FROM
	mj_category t1
	LEFT JOIN mj_category_class t2 ON t2.tenant_id = 63 
	AND t1.`class_id` = t2.`id` 
WHERE
	t1.tenant_id = 63 
	AND t1.num IN ( 'SMKM5892-LPD蓝色L' )

执行计划如下
在这里插入图片描述