mysql使用索引最佳化order排序，mysqlorder

mysql使用索引最佳化order排序，mysqlorder

 個人原創地址：http://www.phpthinking.com/archives/433

在資料庫中我們一般都會對一些欄位進行索引操作，這樣可以提升資料的查詢速度，同時提高資料庫的效能像order by ,group by前都需要索引。

先我們要注意一下
1>mysql一次查詢只能使用一個索引。如果要對多個欄位使用索引，建立複合索引。
2>在ORDER BY操作中，MySQL只有在排序條件不是一個查詢條件運算式的情況下才使用索引。
關於索引一些說法

MySQL索引通常是被用於提高WHERE條件的資料行匹配或者執行連接操作時匹配其它表的資料行的搜尋速度。
MySQL也能利用索引來快速地執行ORDER BY和GROUP BY語句的排序和分組操作。
通過索引最佳化來實現MySQL的ORDER BY語句最佳化：
1、ORDER BY的索引最佳化。如果一個SQL語句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]這個欄位上建立索引就可以實現利用索引進行order by 最佳化。
2、WHERE + ORDER BY的索引最佳化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一個聯合索引(columnX,sort)來實現order by 最佳化。
注意：如果columnX對應多個值，如下面語句就無法利用索引來實現order by的最佳化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
3、WHERE+ 多個欄位ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)實現order by的最佳化,比建立(x,y,uid)索引效果要好得多。
MySQL Order By不能使用索引來最佳化排序的情況
* 對不同的索引鍵做 ORDER BY ：(key1,key2分別建立索引)
SELECT * FROM t1 ORDER BY key1, key2;
* 在非連續的索引鍵部分上做 ORDER BY：(key_part1,key_part2建立聯合索引;key2建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
* 同時使用了 ASC 和 DESC：(key_part1,key_part2建立聯合索引)
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
* 用於搜尋記錄的索引鍵和做 ORDER BY 的不是同一個：(key1,key2分別建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
* 如果在WHERE和ORDER BY的欄位上應用運算式(函數)時，則無法利用索引來實現order by的最佳化
SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
MySQL支援很多資料類型，選擇合適的資料類型儲存資料對效能有很大的影響。

通常來說，可以遵循以下一些指導原則：
(1)越小的資料類型通常更好：越小的資料類型通常在磁碟、記憶體和CPU緩衝中都需要更少的空間，處理起來更快。
(2)簡單的資料類型更好：整型資料比起字元，處理開銷更小，因為字串的比較更複雜。在MySQL中，應該用內建的日期和時間資料類型，而不是用字串來儲存時間；以及用整數資料型別儲存IP地址。
(3)盡量避免NULL：應該指定列為NOT NULL，除非你想儲存NULL。在MySQL中，含有空值的列很難進行查詢最佳化，因為它們使得索引、索引的統計資訊以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值。

MySql查詢排序問題

0 單表不加排序的話，預設使用主鍵的順序，這是因為在磁碟資料區塊中存放時按主鍵順序儲存；無主鍵則按在磁碟資料區塊中的順序（這也是插入資料時的順序）；
1 多表不加排序的話，而且沒有group by子句的話，順序是一個左表的主鍵順序，然後又是一遍左表的順序，類似以下：
左表主鍵1 右表主鍵1
左表主鍵2 右表主鍵1
左表主鍵1 右表主鍵2
左表主鍵2 右表主鍵2
當有group by 則會打亂，因為group by 會執行時內部會使用到記憶體排序或臨時檔案排序（這點從explain中就可看到）。
2 由上，自己決定是否要order by吧，不過不使用order by一般達不到業務要求吧。
想辦法最佳化order by 吧。
 
MySQL資料庫最佳化（七）：MySQL怎使用索引

　　索引用於快速找到特定一些值的記錄。如果沒有索引，MySQL就必須從第一行記錄開始讀取整個表來檢索記錄。表越大，資源消耗越大。如果在欄位上有索引的話，MySQL就能很快決定該從資料檔案的哪個位置開始搜尋記錄，而無須尋找所有的資料。如果表中有1000條記錄的話，那麼這至少比順序地讀取資料快100倍。注意，如果需要存取幾乎全部1000條記錄的話，那麼順序讀取就更快了，因為這樣會使磁碟搜尋最少。
　　大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B樹方式儲存。只有空間類型的欄位使用R樹儲存，MEMORY (HEAP)表支援雜湊索引。
　　字串預設都是自動壓縮首碼和尾碼中的空格。
　　通常，如下所述幾種情況下可以使用索引。雜湊索引(用於 MEMORY 表)的獨特之處在後面會討論到。
　　想要儘快找到匹配 WHERE 子句的記錄。
　　根據條件排除記錄。如果有多個索引可共選擇的話，MySQL通常選擇能找到最少記錄的那個索引。
　　做表串連查詢時從其他表中檢索記錄。
　　想要在指定的索引欄位 key_col 上找到它的 MIN() 或 MAX() 值。最佳化程式會在檢查索引的
　　key_col 欄位前就先檢查其他索引部分是否使用了 WHERE key_part_# = constant 子句。這樣的話，
　　MySQL會為 MIN() 或 MAX() 運算式分別單獨做一次索引尋找，並且將它替換成常數。當所有的運算式都被替換成常數後，查詢就立刻返回。如下：
　　SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
　　對錶作排序或分組，當在一個可用的最左首碼索引上做分組或排序時(如 ORDER
　　BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。
　　有些時候，查詢可以最佳化使得無需計算資料就能直接取得結果。當查詢使用表中的一個數字型欄位，且這個欄位是索引的最左部分，則可能從索引樹中能很快就取得結果：
　　SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
　　假設有如下 SELECT 語句：
　　如果在 col1 和 col2 上有一個多欄位索引的話，就能直接取得對應的記錄了。