left join on 筛选条件崩溃记录

场景:

        多字段关联时,  使用and连接多条件 和concat拼接参数后连接

        3表数据: i=28w e=1w c=13w

        

索引:

        i:  w_id和f_id   NORMAL

        c: w_id和f_id   NORMAL

sql:

        方式1:

SELECT
	i.id,
    i.w_id,
    i.f_id,
	c.avatar,
	c.name,
	IF( c.memo IS NULL OR length( c.memo ) = 0, i.memo, c.memo ) memo,
	e.detail,
	i.`code` 
FROM
	i
	LEFT JOIN e ON e.CUSTOMER_ID = i.id
	LEFT JOIN c ON ( i.w_id = c.w_id AND i.f_id = c.f_id ) 
WHERE
	i.intent IN ( 'A', 'B' ) 
	AND i.CREATED_TIME > '2023-02-01' 
	AND i.CREATED_TIME < '2023-07-06' 
	AND i.DELETED = 0 
ORDER BY
	i.CREATED_TIME DESC 
	LIMIT 50

修改为

        方式2:

SELECT
	i.id,
    i.w_id,
    i.f_id,
	c.avatar,
	c.name,
	IF( c.memo IS NULL OR length( c.memo ) = 0, i.memo, c.memo ) memo,
	e.detail,
	i.`code` 
FROM
	i
	LEFT JOIN e ON e.CUSTOMER_ID = i.id
	LEFT JOIN c ON CONCAT(i.w_id, i.f_id) = CONCAT(c.w_id,c.f_id)
WHERE
	i.intent IN ( 'A', 'B' ) 
	AND i.CREATED_TIME > '2023-02-01' 
	AND i.CREATED_TIME < '2023-07-06' 
	AND i.DELETED = 0 
ORDER BY
	i.CREATED_TIME DESC 
	LIMIT 50

崩溃经过:

方式1生产环境查询时长超过120s, 最终导致数据库连接池连接数耗尽, 最终服务假死