升级2.0.13后大表join小表变慢
下面是查询sql,gsms_msg_ticket_sms是表，其他表都是小表，耗时需要2s左右
如果单查gsms_msg_ticket_sms表，只需要0.5s左右
执行计划，使用的是Shuffle Join
SELECT
*
FROM
gsms_msg_ticket_sms AS t
JOIN gsms_user AS u on u.id = t.user_id
LEFT JOIN gsms_user AS e ON u.enterprise_id = e.id
LEFT JOIN gsms_user AS sale ON e.saleman_id = sale.id
LEFT JOIN gsms_user_ext ext ON ext.enterprise_id = e.id
LEFT JOIN gsms_user AS service ON ext.customer_service_id = service.id
LEFT JOIN gsms_user AS success ON ext.customer_success_id = success.id
LEFT JOIN gsms_user AS support ON ext.technical_support_id = support.id
LEFT JOIN gsms_carrier_channel AS c ON c.id = t.channel_id
WHERE
1 = 1
AND t.id IN (1275031788812894210,1275031780030023683,1275031775093327876,1275031770693502978,1275031773868589065,1275031773872783361,1275031772203450368,1275031766729883653,1275031750699253762,1275031747222175749
)
AND t.post_time >= '2024-08-19 00:00:00'
AND t.post_time <= '2024-08-19 23:59:59'
AND t.enterprise_id = 234316
AND (( t.total > 1 AND t.number != 0 ) OR t.total = 1 )
ORDER BY
t.post_time DESC,
t.frame_id DESC,
t.phone,
t.ticket_id ASC,
t.number ASC;
强制使用broadcast join
速度就能达到0.5s
执行计划：使用的是broadcastjoin 类型
SELECT
*
FROM
gsms_msg_ticket_sms AS t
JOIN [broadcast]gsms_user AS u on u.id = t.user_id
LEFT JOIN [broadcast]gsms_user AS e ON u.enterprise_id = e.id
LEFT JOIN [broadcast]gsms_user AS sale ON e.saleman_id = sale.id
LEFT JOIN [broadcast]gsms_user_ext ext ON ext.enterprise_id = e.id
LEFT JOIN [broadcast]gsms_user AS service ON ext.customer_service_id = service.id
LEFT JOIN [broadcast]gsms_user AS success ON ext.customer_success_id = success.id
LEFT JOIN [broadcast]gsms_user AS support ON ext.technical_support_id = support.id
LEFT JOIN [broadcast]gsms_carrier_channel AS c ON c.id = t.channel_id
WHERE
1 = 1
AND t.id IN (1275031788812894210,1275031780030023683,1275031775093327876,1275031770693502978,1275031773868589065,1275031773872783361,1275031772203450368,1275031766729883653,1275031750699253762,1275031747222175749
)
AND t.post_time >= '2024-08-19 00:00:00'
AND t.post_time <= '2024-08-19 23:59:59'
AND t.enterprise_id = 234316
AND (( t.total > 1 AND t.number != 0 ) OR t.total = 1 )
ORDER BY
t.post_time DESC,
t.frame_id DESC,
t.phone,
t.ticket_id ASC,
t.number ASC;
在1.2.7.1下没有使用broadcast执行sql,速度也是0.5s,查看执行计划
也是使用的是broadcast
请问这个情况怎么处理？
