閱讀本文大概需要 5 分鐘。
來源:http://cnblogs.com/songwenjie/p/9410009.html
本文主要討論MySQL索引的部分知識。將會從MySQL索引基礎、索引優化實戰和資料庫索引背後的數據結構三部分相關內容,下面一一展開(本文圖片可點開放大)。
此部分用於測試索引創建的user表的結構如下:
1. 什麼是索引?
我們需要知道索引其實是一種數據結構,其功能是幫助我們快速匹配查找到需要的數據行,是資料庫性能優化最常用的工具之一。其作用相當於超市裡的導購員、書本里的目錄。
主鍵索引 PRIMARY KEY:它是一種特殊的唯一索引,不允許有空值。一般是在建表的時候同時創建主鍵索引。注意:一個表只能有一個主鍵。
唯一索引 UNIQUE:唯一索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須唯一。可以通過ALTER TABLE table_name ADD UNIQUE (column);創建唯一索引:
可以通過ALTER TABLE table_name ADD UNIQUE (column1,column2);創建唯一組合索引:
普通索引 INDEX:這是最基本的索引,它沒有任何限制。可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引:
組合索引 INDEX:即一個索引包含多個列,多用於避免回表查詢。可以通過ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);創建組合索引:
全文索引 FULLTEXT:也稱全文檢索,是目前搜索引擎使用的一種關鍵技術。可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引:
索引一經創建不能修改,如果要修改索引,只能刪除重建。可以使用
DROP INDEX index_name ON table_name;刪除索引。
1、索引相關的重要概念
user表的總行數是5,gender列的基數是2,說明gender列裡面有大量重複值,name列的基數等於總行數,說明name列沒有重複值,相當於主鍵。
查詢滿足性別為0(男)的記錄數:
那麼返回記錄的比例數是:
同理,查詢name為swj的記錄數:
返回記錄的比例數是:
現在問題來了,假設name、gender列都有索引,那麼SELECT * FROM user WHERE gender = 0; SELECT * FROM user WHERE name = swj;都能命中索引嗎?
SELECT * FROM user WHERE gender = 0;沒有命中索引,注意filtered的值就是上面我們計算的返回記錄的比例數。
SELECT * FROM user WHERE name = swj;命中了索引index_name,因為走索引直接就能找到要查詢的記錄,所以filtered的值為100。
因此,返回表中30%內的數據會走索引,返回超過30%數據就使用全表掃描。當然這個結論太絕對了,也並不是絕對的30%,只是一個大概的範圍。
Handler_read_key:如果索引正在工作,Handler_read_key的值將很高。
前導模糊查詢不能命中索引:
非前導模糊查詢則可以使用索引,可優化為使用非前導模糊查詢:
3)數據類型出現隱式轉換的時候不會命中索引,特別是當列類型是字元串,一定要將字元常量值用引號引起來。
EXPLAIN SELECT * FROM user WHERE name=1;
4)複合索引的情況下,查詢條件不包含索引列最左邊部分(不滿足最左原則),不會命中符合索引。
user表索引詳情:
根據最左原則,可以命中複合索引index_name:
注意,最左原則並不是說是查詢條件的順序:
而是查詢條件中是否包含索引最左列欄位:
5)union、in、or都能夠命中索引,建議使用in。
in:
or:
查詢的CPU消耗:or>in>union。
因為or後面的條件列中沒有索引,那麼後面的查詢肯定要走全表掃描,在存在全表掃描的情況下,就沒有必要多一次索引掃描增加IO訪問。
負向條件不能命中緩存:
可以優化為in查詢,但是前提是區分度要高,返回數據的比例在30%以內:
8)範圍條件查詢可以命中索引。範圍條件有:<、<=、>、>=、between等。
ALTER TABLE user ADD INDEX index_age (age);
範圍條件查詢可以命中索引:
範圍列可以用到索引(聯合索引必須是最左前綴),但是範圍列後面的列無法用到索引,索引最多用於一個範圍列,如果查詢條件中有兩個範圍列則無法全用到索引:
如果是範圍查詢和等值查詢同時存在,優先匹配等值查詢列的索引:
8)資料庫執行計算不會命中索引。
EXPLAIN SELECT * FROM user WHERE age+1>24;
計算邏輯應該盡量放到業務層處理,節省資料庫的CPU的同時最大限度的命中索引。
因為status欄位是索引列,所以直接從索引中就可以獲取值,不必回表查詢:
當查詢其他列時,就需要回表查詢,這也是為什麼要避免SELECT*的原因之一:
10)建立索引的列,不允許為null。
IS NULL可以命中索引:
IS NOT NULL不能命中索引:
雖然IS NULL可以命中索引,但是NULL本身就不是一種好的資料庫設計,應該使用NOT NULL約束以及默認值。
·END·
程序員的成長之路
路雖遠,行則必至