程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> MySQL教程:用索引優化ORDER BY

MySQL教程:用索引優化ORDER BY

編輯:更多數據庫知識

關於建立索引的幾個准則:

1、合理的建立索引能夠加速數據讀取效率,不合理的建立索引反而會拖慢數據庫的響應速度。

2、索引越多,更新數據的速度越慢。

3、盡量在采用MyIsam作為引擎的時候使用索引(因為MySQL以BTree存儲索引),而不是InnoDB。但MyISAM不支持Transcation。

4、當你的程序和數據庫結構/SQL語句已經優化到無法優化的程度,而程序瓶頸並不能順利解決,那就是應該考慮使用諸如memcached這樣的分布式緩存系統的時候了。

5、習慣和強迫自己用EXPLAIN來分析你SQL語句的性能。

一個很容易犯的錯誤:

不要在選擇的欄位上放置索引,這是無意義的。應該在條件選擇的語句上合理的放置索引,比如where,order by。

例子:

1
SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;

上面這個語句,你在id/title/content上放置索引是毫無意義的,對這個語句沒有任何優化作用。但是如果你在外鍵cat_id上放置一個索引,那作用就相當大了。

幾個常用ORDER BY語句的MySQL優化:

1、ORDER BY + LIMIT組合的索引優化。如果一個SQL語句形如:

1
2
SELECT [column1],[column2],.... FROM [TABLE] ORDER BY
 [sort] LIMIT [offset],[LIMIT];

這個SQL語句優化比較簡單,在[sort]這個欄位上建立索引即可。

2、WHERE + ORDER BY + LIMIT組合的索引優化,形如:

1
2
SELECT [column1],[column2],.... FROM [TABLE] WHERE
 [columnX] = [value] ORDER BY [sort] LIMIT[offset],[LIMIT];

這個語句,如果你仍然采用第一個例子中建立索引的方法,雖然可以用到索引,但是效率不高。更高效的方法是建立一個聯合索引(columnX,sort)

3、WHERE + IN + ORDER BY + LIMIT組合的索引優化,形如:

1
2
SELECT [column1],[column2],.... FROM [TABLE] WHERE
 [columnX] IN ([value1],[value2],...) ORDER BY[sort] LIMIT [offset],[LIMIT];

這個語句如果你采用第二個例子中建立索引的方法,會得不到預期的效果(僅在[sort]上是using index,WHERE那裡是using where;using filesort),理由是這裡對應columnX的值對應多個。

這個語句怎麼優化呢?我暫時沒有想到什麼好的辦法,看到網上有便宜提供的辦法,那就是將這個語句用UNION分拆,然後建立第二個例子中的索引:

1
2
3
4
5
6
7
SELECT [column1],[column2],.... FROM [TABLE] WHERE
 [columnX]=[value1] ORDER BY [sort] LIMIT[offset],[LIMIT] 
UNION 
SELECT [column1],[column2],.... FROM [TABLE] WHERE [columnX]=[value2] ORDER BY
 [sort] LIMIT[offset],[LIMIT] 
UNION 
……

但經驗證,這個方法根本行不通,效率反而更低,測試時對於大部分應用強制指定使用排序索引效果更好點

4、不要再WHERE和ORDER BY的欄位上應用表達式(函數),比如:

1
SELECT * FROM [table] ORDER BY YEAR(date) LIMIT 0,30;

5、WHERE+ORDER BY多個欄位+LIMIT,比如

1
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

對於這個語句,大家可能是加一個這樣的索引(x,y,uid)。但實際上更好的效果是(uid,x,y)。這是由MySQL處理排序的機制造成的。

以上例子你在實際項目中應用的時候,不要忘記在添加索引後,用EXPLAIN看看效果。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved