linq 后臺代碼：主要含義： 就是先根據PersonID分組查詢，去掉重復的PersonID。 然后在這個結果集里面。根據PersonID，AlarmStatus升序，AlarmRecordID降序，獲取最新的AlarmRecordID。 然后兩個合并為結果集。轉為mysql 的SQL語句為 SELECT t.PersonID,(SELECT AlarmRecordID FROM AlarmRecord WHERE OrganizationID = 4 AND IsDeleted = 0 AND PersonID = t.PersonID ORDER BY AlarmStatus ASC,AlarmRecordID DESC LIMIT 1) AS AlarmRecordIDFROM AlarmRecord t WHERE t.OrganizationID = 4 AND t.IsDeleted = 0 GROUP BY t.PersonID我想優化標記為紅色部分的SQL，請問怎么優化？
1 回答
繁華開滿天機
TA貢獻1816條經驗 獲得超4個贊
首先修改sql :
SELECT t.PersonID,(SELECT AlarmRecordID FROM AlarmRecord WHERE PersonID = t.PersonID ORDER BY AlarmStatus ASC,AlarmRecordID DESC LIMIT 1) AS AlarmRecordIDFROM AlarmRecord t WHERE t.OrganizationID = 4 AND t.IsDeleted = 0 GROUP BY t.PersonID
通過創建索引來優化
CREATE INDEX `idx_AlarmRecord_PersonID` ON `AlarmRecord`(`PersonID`, `AlarmStatus`);
通過創建索引，查詢會快很多。如果不通過創建索引，有什么好的方法嗎？
添加回答
舉報
0/150
提交
取消