MySQL消除排序有多重要！

最新推荐文章于 2024-08-16 16:22:51 发布

深圳gg

最新推荐文章于 2024-08-16 16:22:51 发布

阅读量1.4k

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/stevendbaguo/article/details/79086365

版权

mysql 专栏收录该内容

54 篇文章 1 订阅

订阅专栏

这条SQL是查出来所有的数据，然后对所有的数据进行排序，最后取出50条数据。

SELECT ... FROM IC_RC_CONTROL_COMMAND c,

IC_OM_DM_CHARGING_PILE cp,
ic_rc_control_command_item ci
WHERE c.`CONTROL_COMMAND_ID` = ci.`CONTROL_COMMAND_ID`
AND cp.CHARGING_PILE_CODE = ci.CHARGING_PILE_CODE
AND c.`COMMAND_DOWN_TIME` >= STR_TO_DATE(CONCAT('2018-01-17',' 00:00:00'),'%Y-%m-%d %H:%i:%s')
AND c.`COMMAND_DOWN_TIME` <= STR_TO_DATE(CONCAT('2018-01-17',' 23:59:59'),'%Y-%m-%d %H:%i:%s')
ORDER BY c.`COMMAND_DOWN_TIME` DESC
LIMIT 50,50;
id select_type TABLE TYPE KEY key_len ref ROWS Extra
------ ----------- ------ ------ --------------------- ------- ---------------------- ------ ----------------------------------------------
1 SIMPLE cp ALL (NULL) (NULL) (NULL) 4290 USING WHERE; USING TEMPORARY; USING filesort
1 SIMPLE ci ref idx_oper_command_time 99 cp.CHARGING_PILE_CODE 72 USING WHERE
1 SIMPLE c eq_ref PRIMARY 98 ci.CONTROL_COMMAND_ID 1 USING WHERE
执行耗时 : 5.073 sec
传送时间 : 0.002 sec
总耗时 : 5.075 sec

优化后的SQL消除了排序。
SELECT ... FROM
(SELECT COMMAND_DOWN_TIME,
OPERATE_TYPE,
COMMAND_USER_NAME,
operate_desc,
CONTROL_COMMAND_ID
FROM IC_RC_CONTROL_COMMAND c
WHERE c.`COMMAND_DOWN_TIME` >= STR_TO_DATE(CONCAT('2018-01-17',' 00:00:00'),'%Y-%m-%d %H:%i:%s')
AND c.`COMMAND_DOWN_TIME` <= STR_TO_DATE(CONCAT('2018-01-17',' 23:59:59'),'%Y-%m-%d %H:%i:%s')
ORDER BY c.`COMMAND_DOWN_TIME` DESC
) cc, IC_OM_DM_CHARGING_PILE cp,
ic_rc_control_command_item ci
WHERE cc.`CONTROL_COMMAND_ID` = ci.`CONTROL_COMMAND_ID`
AND cp.CHARGING_PILE_CODE = ci.CHARGING_PILE_CODE
LIMIT 50,50;
id select_type TABLE TYPE KEY key_len ref ROWS Extra
------ ----------- ---------- ------ ----------------------- ------- ----------------------- ------ ---------------
1 PRIMARY cp ALL (NULL) (NULL) (NULL) 4290 USING WHERE
1 PRIMARY ci ref idx_oper_command_time 99 cp.CHARGING_PILE_CODE 72 USING WHERE
1 PRIMARY <derived2> ref <auto_key0> 98 ci.CONTROL_COMMAND_ID 10 (NULL)
2 DERIVED c RANGE INDEX_COMMAND_DOWN_TIME 6 (NULL) 3330 USING INDEX CONDITION
执行耗时 : 0.065 sec
传送时间 : 0.010 sec
总耗时 : 0.075 sec