mysql 8.0.21-8.0.26 优化了子查询一些操作？，提升蛮大的

select
CASE
     WHEN (`ticket`.num IS NOT NULL) THEN 'web'
     WHEN (`ticket`.num IS NULL) THEN (SELECT CASE
                                                   WHEN (`channel`.`NAME` = '渠道 1')
                                                   THEN 'app'
                                                   WHEN (`channel`.`NAME` = '渠道 2')
                                                   THEN '小程序'
                                                   ELSE '其他' END AS channel
       FROM `channel`,
            `sreq`
       WHERE `channel`.`chanid` = `S_SRV_REQ`.`chanid`
         AND `ticket`.req_id = `sreq`.`REQ_ID`
       LIMIT 1) END AS channel
from ticket where ...;

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-optimizer:~:text=%E5%B0%9D%E8%AF%95%E5%AF%B9%E4%BB%BB%E4%BD%95-,%E5%85%B7%E6%9C%89,-%E5%AD%90%E5%8F%A5%E7%9A%84%E6%9F%A5%E8%AF%A2
sql 大概这样，脱敏了下字段信息
之前用的 mysql8.0.20 里面的子查询没有用到索引，走了全表,升级了 8.0.26 走索引了，一模一样的的表结构，只是升级了 mysql 版本，性能提升 10 几倍了，从 6s 到 0.6s
看了下文档貌似是 8.0.21 优化了子查询？

channel

ticket

then

when

9 replies • 2022-10-29 08:58:23 +08:00