请问这个 MYSQL 的语句，我应该如何优化或者建索引

SELECT
	pni.parent_name,
	ni.name,
	dt.data_time,
	dt.value_a,
	dt.value_b
FROM
	data_table dt
LEFT JOIN rel_table rt ON
	dt.data_id = rt.rel_id
LEFT JOIN name_info ni ON
	rt.rel_b_id = ni.rel_b_id
LEFT JOIN parent_name_info pni ON
	ni.parent_id = pni.parent_id
WHERE
	dt.data_time > ${dayTime}
	AND dt.data_time <= date_add(${dayTime},INTERVAL 1 DAY)
	AND pni.parent_id IN (
	SELECT
		parent_id
	FROM
		parent_list)

mysql 的版本是 5.7 ，现在的情况是这个语句查询不出来。数据表日期范围内的数据大概是不到 1W 条，rel 表中的数据不到 200 条。 ni 和 pni 这两个表中的数据可能有几十万条。

19 条回复 • 2024-04-10 23:44:37 +08:00

encro

214 天前

explain 结果出来看下再说

encro

214 天前

dt 的 parnet_id 加 data_time 建立组合索引。

Richared

214 天前

左关联会关联所有数据，形成大表再筛选，先筛选再关联减少连接数。你这数据量还没到扒着看的程度

me1onsoda

214 天前

以我浅薄的知识，恐怕没有什么好办法。我可能会把 pni.parent_id IN 这个 clause 放到表连接中，提高过滤率

kwater

214 天前

IN (
SELECT
parent_id
FROM
parent_list)

这个必须改 join ，否则你可能在某些人的偏执下会失去工作 😈

leon1900

214 天前

parent_list 多少条数据

ukipoi

214 天前

@encro
结果是这个。

id select_type `table` partitions `type` possible_keys `key` key_len `ref` `rows` filtered Extra
1 SIMPLE rt ALL rel_id,data_id 187 100 Using where
1 SIMPLE ni ref rel_id rel_id 111 test.rt.rel_id 1 100
1 SIMPLE dt ref data_id,data_time data_id 110 test.rt.data_id 232 3.73 Using where
1 SIMPLE pni ALL 96014 10 Using where; Using join buffer (Block Nested Loop)
1 SIMPLE <subquery2> eq_ref <auto_key> <auto_key> 110 test.pni.parent_id 1 100
2 MATERIALIZED parent_list index parent_id parent_id 110 185 100 Using index

ukipoi

214 天前

@leon1900
200 条不到

qiqw

214 天前

关注

ukipoi

214 天前

@Richared 我试着把条件都写在 join 的表里了，最终结果大概是
A （ 9000 ） B （ 200 ） C （ 200 ） D(50) 这 4 个表关联，还是出不来结果
我看这 4 个表做全关联要 180W 数据了，mysql 会这样么。
主要是之前用的都是 oracle ，自己数据库也不太行，oracle 关联好像可以直接出数据。

Felix0504

214 天前

@ukipoi #7 你的 rt 和 pni 都是全表扫描，rt 数据量小也就算了，pni 数据量大，全表扫描肯定耗时，parent_id 加索引了吗

elkfnmoyu

214 天前

3 楼说的对，子查询里 data_time 先筛选 data_table 数据，然后再用子查询关联其他表

leon1900

214 天前

pni.parent_id 没索引吗

ukipoi

214 天前

@Felix0504 没有单独的 parent_id 的索引，我加了结果就能出来了，谢谢！

Richared

214 天前

@ukipoi 加个分页吧。

Richared

214 天前

@ukipoi 这样的问题，首先考虑减小关联数，然后是否是全量返回，数据量大小多大，最后看你的条件走没走索引，尤其大表。你把条件写里边就是一个一个的简单查询，这个判断走不走索引，该怎么建立索引就简单多了，照着操作就行了。基本都能解决。

fxjson

214 天前

如果是我的话，首先 data_time 添加索引，
其次 date_add(${dayTime},INTERVAL 1 DAY)去掉使用 date_add 函数，
其次，子查询在应用中单独 sql 查询出结果然后用 in(xxx,xxx),
最后看看 on 字段是否建立了对应的索引

最后根据 explain 看结果进行分析是否有改进，如果上面几步都改了，sql 大概率不会有其他问题了

encro

214 天前

@ukipoi
#7

前有人提到过，就是 pni.parent_id 的问题，rows 96014 就它最大，且没有利用索引。
给 pni.parent_id 建立索引就解决了。
基本所有关联外键都需要建立索引的。

aliveyang

213 天前

pni.parent_id IN ()既然有这个条件了, LEFT JOIN 是不是没必要了