請教一下，如下SQL陳述句如何優化，當前執行速度非常慢，直接導致超時拋例外了。
MySQL 8.0
SELECT a.Id,a.BatteryCode,a.CreateTime,a.ModuleCode,a.BindTime,
(SELECT CreateTime FROM tb_bend1info where BatteryCode=a.BatteryCode order by id desc limit 1) as bend1,
(SELECT SealTempSet1 FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal1,
(SELECT SealTempSet2 FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal2,
(SELECT SealTimeSet FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal3,
(SELECT CreateTime FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal4,
(SELECT CreateTime FROM tb_bend2info where BatteryCode=a.BatteryCode order by id desc limit 1) as bend2,
(SELECT CreateTime FROM tb_stickinfo where BatteryCode=a.BatteryCode order by id desc limit 1) as stick,
(SELECT Voltage FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv1,
(SELECT Resistance FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv2,
(SELECT TestResult FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv3,
(SELECT CreateTime FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv4,
(SELECT TrimPoint1 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim1,
(SELECT TrimPoint2 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim2,
(SELECT TrimPoint3 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim3,
(SELECT TrimPoint4 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim4,
(SELECT TrimLengthA FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trimLengthA,
(SELECT TrimLengthB FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trimLengthB,
(SELECT CreateTime FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim5,
(SELECT CreateTime FROM tb_plasmaparam where BatteryCode=a.BatteryCode order by id desc limit 1) as plasma
FROM (SELECT * FROM TB_BatteryInfo where BatteryCode <>'') a where a.BatteryCode like '%ABC%'" and a.CreateTime>=@StartTime and a.CreateTime<=@EndTime
uj5u.com熱心網友回復：
所有表，BatteryCode欄位加索引。SELECT a.Id,a.BatteryCode,a.CreateTime,a.ModuleCode,a.BindTime,
(SELECT CreateTime FROM tb_bend1info where BatteryCode=a.BatteryCode order by id desc limit 1) as bend1,
(SELECT SealTempSet1 FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal1,
(SELECT SealTempSet2 FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal2,
(SELECT SealTimeSet FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal3,
(SELECT CreateTime FROM tb_sealtest where BatteryCode=a.BatteryCode order by id desc limit 1) as seal4,
(SELECT CreateTime FROM tb_bend2info where BatteryCode=a.BatteryCode order by id desc limit 1) as bend2,
(SELECT CreateTime FROM tb_stickinfo where BatteryCode=a.BatteryCode order by id desc limit 1) as stick,
(SELECT Voltage FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv1,
(SELECT Resistance FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv2,
(SELECT TestResult FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv3,
(SELECT CreateTime FROM tb_ocvtest where BatteryCode=a.BatteryCode order by id desc limit 1) as ocv4,
(SELECT TrimPoint1 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim1,
(SELECT TrimPoint2 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim2,
(SELECT TrimPoint3 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim3,
(SELECT TrimPoint4 FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim4,
(SELECT TrimLengthA FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trimLengthA,
(SELECT TrimLengthB FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trimLengthB,
(SELECT CreateTime FROM tb_triminfo where BatteryCode=a.BatteryCode order by id desc limit 1) as trim5,
(SELECT CreateTime FROM tb_plasmaparam where BatteryCode=a.BatteryCode order by id desc limit 1) as plasma
FROM TB_BatteryInfo a where a.BatteryCode like '%ABC%'" and a.CreateTime between @StartTime and @EndTime
uj5u.com熱心網友回復：
除了 除了所有表BatteryCode 欄位都加上索引外 TB_BatteryInfo 建一個 CreateTime ,BatteryCode 的聯合索引。另外，最后from 的子查詢不要。 直接放到外面來。同上一位 FROM TB_BatteryInfo a where a.BatteryCode like '%ABC%'" and a.CreateTime between @StartTime and @EndTime
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/202849.html
標籤：MySQL
上一篇：取當前時間前一天的資料
下一篇：GH2132高溫合金