sql 大資料查詢慎用 order by

標籤：一個   style   測試   技術   大數   image   _id   需要   時間   

今天在SQL 查詢中一個表中查詢花了至少20秒時間，資料為620000行，sql語句如下：

測試一：使用order by 

單單只是查詢0,10行資料，耗時27.888s

select a.id,a.county_id,a.county_name,a.town_id,a.town_name,a.village_id,a.village_name,b.province as province_name,b.name as city_name from place a left join city b on a.city_id=b.code where a.id is not null order by a.village_id asc limit 0, 10

測試二：不使用order by

select a.id,a.county_id,a.county_name,a.town_id,a.town_name,a.village_id,a.village_name,b.province as province_name,b.name as city_name from place a left join city b on a.city_id=b.code where a.id is not null limit 0, 10

同樣是查詢0,10行資料，耗時0.084s

 

以上僅供參考，資料更大時需要進行更精細的最佳化

sql 大資料查詢慎用 order by