MySQL查询优化之ORDER BY

数据库表uc_user_baseinfo中含有55万条数据，进行如下实验：

不使用ORDER BY情况下的执行时间

SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 0,10;执行成功，当前返回：[10]行，耗时：[109ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 10,10;执行成功，当前返回：[10]行，耗时：[110ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 100,10;执行成功，当前返回：[10]行，耗时：[110ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 1000,10;执行成功，当前返回：[10]行，耗时：[109ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 10000,10;执行成功，当前返回：[10]行，耗时：[115ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 100000,10;执行成功，当前返回：[10]行，耗时：[150ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 200000,10;执行成功，当前返回：[10]行，耗时：[191ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 300000,10;执行成功，当前返回：[10]行，耗时：[231ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 400000,10;执行成功，当前返回：[10]行，耗时：[271ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' limit 500000,10;执行成功，当前返回：[10]行，耗时：[311ms.]

使用ORDER BY DESC情况下的执行时间

SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 0,10;执行成功，当前返回：[10]行，耗时：[104ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 10,10;执行成功，当前返回：[10]行，耗时：[122ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 100,10;执行成功，当前返回：[10]行，耗时：[104ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 1000,10;执行成功，当前返回：[10]行，耗时：[105ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 10000,10;执行成功，当前返回：[10]行，耗时：[111ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 100000,10;执行成功，当前返回：[10]行，耗时：[169ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 200000,10;执行成功，当前返回：[10]行，耗时：[236ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 300000,10;执行成功，当前返回：[10]行，耗时：[300ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 400000,10;执行成功，当前返回：[10]行，耗时：[366ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id desc limit 500000,10;执行成功，当前返回：[10]行，耗时：[445ms.]

使用ORDER BY ASC情况下的执行时间

SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 0,10;执行成功，当前返回：[10]行，耗时：[106ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 10,10;执行成功，当前返回：[10]行，耗时：[105ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 100,10;执行成功，当前返回：[10]行，耗时：[105ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 1000,10;执行成功，当前返回：[10]行，耗时：[137ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 10000,10;执行成功，当前返回：[10]行，耗时：[109ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 100000,10;执行成功，当前返回：[10]行，耗时：[152ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 200000,10;执行成功，当前返回：[10]行，耗时：[202ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 300000,10;执行成功，当前返回：[10]行，耗时：[259ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 400000,10;执行成功，当前返回：[10]行，耗时：[300ms.]SELECT id from `uc_user_baseinfo` WHERE `area_code` ='020' ORDER BY id asc limit 500000,10;执行成功，当前返回：[10]行，耗时：[466ms.]

结论

能不用ORDER BY的就不要用ORDER BY,尽量利用MySQL自增ID做查询。