MYSQL order by排序與索引關係總結
MySQL InnoDB B-Tree索引使用Tips
前段時間處理一個生產問題,匹配結果的時候訪問超時,想了很多辦法優化哪個匹配結果的sql語句,但是發現效果甚微,最後發現是索引的問題,在網上查了很久找到了這片文章,所以轉載了一下,分享給大家。
這裡主要討論一下InnoDB B-Tree索引的使用,不提設計,只管使用。B-Tree索引主要作用於WHERE和ORDER BY子句。這裡討論的均在MySQL-Server-5.1.42測試
CREATE TABLE `friends` ( `ID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `uid`bigint(20) UNSIGNED NOT NULL DEFAULT '0', `fuid` bigint(20) UNSIGNED NOT NULL DEFAULT'0', `fname` varchar(50) NOT NULL DEFAULT '', `fpicture` varchar(150) NOT NULL DEFAULT'', `fsex` tinyint(1) NOT NULL DEFAULT '0', `status` tinyint(1) NOT NULL DEFAULT '0',PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; ALTER TABLE`friends` ADD INDEX uid_fuid (uid, fuid);
1.如果索引了多列,要遵守最左字首法則。所謂最左前列,指的是查詢從索引的最左前列開始,並且不跳過索引中的列。
第2條語句,從索引的第二列開始查詢,使用索引失敗,導致MySQL採用ALL訪問策略,即全表查詢.在開發中,應該儘量避免全表查詢。
2.當MySQL一旦估計檢查的行數可能會”太多”,範圍查詢優化將不會被使用。
第2條語句使用了全表查詢,它與第1條語句唯一的區別在於需要檢查的行數遠遠多於第1條語句。在應用中,可能不會碰到這麼大的查詢,但是應該避免這樣的查 詢出現: select uid from users where registered < 1295001384
3.索引列不應該作為表示式的一部分,即也不能在索引列上使用函式
第2和3條語句都有使用表示式,索引派不上用場。
4.儘量借用覆蓋索引,減少select * from …語句使用
第1句Extra中使用了Using index表示使用了覆蓋索引。第3句也使用了覆蓋索引,雖然ID不在索引uid_fuid索引列中,但是InnoDB二次索引(second index)葉子頁的值就是PK值,不同於MyISAM。Extra部分的Using index表示應用了索引,不要跟type中的index混淆。第2句沒有使用覆蓋索引,因為fsex不在索引中。
5.ORDER BY子句,儘量使用Index方式排序,避免使用FileSort方式排序
MySQL支援二種方式的排序,FileSort和Index,後者效率高,它指MySQL掃描索引本身完成排序。FileSort方式效率較低。ORDER BY滿足以下情況,會使用Index方式排序:
a)ORDER BY 語句使用索引最左前列。參見第1句
b)使用Where子句與Order BY子句條件列組合滿足索引最左前列。參見第2句.
以下情況,會使用FileSort方式的查詢
a)檢查的行數過多,且沒有使用覆蓋索引。第3句,雖然跟第2句一樣,order by使用了索引最左前列uid,但依然使用了filesort方式排序,因為status並不在索引中,所以沒辦法只掃描索引。
b)使用了不同的索引,MySQL每回只採用一個索引.第4句,order by出現二個索引,分別是uid_fuid和聚集索引(pk)
c)對索引列同時使用了ASC和DESC。 通過where語句將order by中索引列轉為常量,則除外。第5句,和第6句在order by子句中,都出現了ASC和DESC排序,但是第5句卻使用了filesort方式排序,是因為第6句where uid取出排序需要的資料,MySQL將其轉為常量,它的ref列為const。
d)where語句與order by語句,使用了不同的索引。參見第7句。
e)where語句或者ORDER BY語句中索引列使用了表示式,包括函式表示式。參見第8,9句
f)where 語句與ORDER BY語句組合滿足最左字首,但where語句中使用了條件查詢。查見第10句,雖然where與order by構成了索引最左有綴的條件,但是where子句中使用的是條件查詢。
g)order by子句中加入了非索引列,且非索引列不在where子句中。
h)order by或者它與where組合沒有滿足索引最左前列。參見第11句和12句,where與order by組合,不滿足索引最左前列. (uid, fsex)跳過了fuid
i)當使用left join,使用右邊的表欄位排序。參見第13句,儘管user.uid是pk,依然會使用filesort排序。
6.慎用left join語句,避免建立臨時表 使用left join語句的時候,避免出現建立臨時表。儘量不要用left join,分而治之。非要使用的時候,要詢問自己是不是真要必須要使用。
7.高選擇性索引列。 儘量使用高選擇性的過引來過濾資料。高選擇性指Cardinality/#T越接近1,選擇性越高,其中Cardinality指表中索引列不重複值(行)的總數。PK和唯一索引,具有最高的選擇性,即1。推薦可選性達到20%以上。
這裡有二個索引可供使用,而MySQL選擇PRIMARY,是因為它具有更高的選擇性。
8.謹防where子句中的OR。where語句使用or,且沒有使用覆蓋索引,會進行全表掃描。應該儘量避免這樣OR語句。儘量使用UNION代替OR
第1句雖然使用了索引,但是查行時間依然不可以恭維,mysql要檢查的行很多,但是返回的行卻很少.Extra中的using where表示需要通過where子句扔棄不需要的資料行。
9.LIMIT與覆蓋索引 limit子句,使用覆蓋索引時比沒有使用覆蓋索引會快很多
轉自:http://my.oschina.net/longniao/blog/110384?fromerr=dKzaJdu1
相關文章
- MySQL利用索引優化ORDER BY排序語句MySql索引優化排序
- 【SQL 學習】排序問題之order by與索引排序SQL排序索引
- MySQL order by 排序結果不正確MySql排序
- mysql關於聚集索引、非聚集索引的總結MySql索引
- MySql ORDER BY索引是否失效MySql索引
- mysql索引總結MySql索引
- MySQL Order BY 排序過程MySql排序
- MySQL索引——總結篇MySql索引
- 【開發總結】order by 為什麼沒有走索引?索引
- Mysql索引結構與索引原理MySql索引
- MySQL 5.7 ORDER BY排序的優化MySql排序優化
- mysql索引使用經驗總結MySql索引
- mysql總結筆記 -- 索引篇MySql筆記索引
- MySQL 索引知識點總結MySql索引
- 主鍵與主鍵索引的關係索引
- MySQL:關於排序order by limit值不穩定的說明(1)MySql排序MIT
- 關於order by中的資料排序排序
- MySQL distinct 和 order by 排序混淆的替代方案MySql排序
- MySql Order By 多個欄位 排序規則MySql排序
- mysql按照漢字拼音進行order by排序MySql排序
- MySQL 索引和 SQL 調優總結MySql索引
- MySQL系列:索引失效場景總結MySql索引
- C#與C++型別對應關係總結C#C++型別
- Java與Mysql鎖相關知識總結JavaMySql
- ORDER對查詢結果進行排序排序
- MYSQL order by排序導致效率低小優化MySql排序優化
- MySQL按指定順序排序(order by field的使用)MySql排序
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL-覆蓋索引總結筆記MySql索引筆記
- 關於Mysql索引的資料結構MySql索引資料結構
- 索引總結索引
- 關於MySQL InnoDB表的二級索引是否加入主鍵的總結MySql索引
- oracle 學習總結篇三:SCN號與資料庫關閉的關係Oracle資料庫
- UML類圖幾種關係的總結
- mysql關於mysql.server的總結MySqlServer
- mysql關於variable的總結MySql
- mysql相關問題總結MySql
- MySql相關語句總結MySql