Mysql索引的實現原理分析

本篇文章為大家展示了Mysql索引的實現原理分析，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

　　一：Mysql原理與慢查詢

　　MySQL憑借著出色的性能、低廉的成本、豐富的資源，已經成為絕大多數互聯網公司的首選關系型數據庫。雖然性能出色，但所謂“好馬配好鞍”，如何能夠更好的使用它，已經成為開發工程師的必修課，我們經常會從職位描述上看到諸如“精通MySQL”、“SQL語句優化”、“了解數據庫原理”等要求。我們知道一般的應用系統，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現性能問題，遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，所以查詢語句的優化顯然是重中之重。

　　本人從13年7月份起，一直在美團核心業務系統部做慢查詢的優化工作，共計十余個系統，累計解決和積累了上百個慢查詢案例。隨著業務的復雜性提升，遇到的問題千奇百怪，五花八門，匪夷所思。本文旨在以開發工程師的角度來解釋數據庫索引的原理和如何優化慢查詢。

　　一個慢查詢引發的思考

　　select count(*) from task where status=2 and operator_id=20839 and operate_time>1371169729 and operate_time<1371174603 and type=2; 　　系統使用者反應有一個功能越來越慢，于是工程師找到了上面的SQL。 并且興致沖沖的找到了我，“這個SQL需要優化，給我把每個字段都加上索引” 我很驚訝，問道“為什么需要每個字段都加上索引?” “把查詢的字段都加上索引會更快”工程師信心滿滿 “這種情況完全可以建一個聯合索引，因為是最左前綴匹配，所以operate_time需要放到最后，而且還需要把其他相關的查詢都拿來，需要做一個綜合評估。” “聯合索引?最左前綴匹配?綜合評估?”工程師不禁陷入了沉思。 多數情況下，我們知道索引能夠提高查詢效率，但應該如何建立索引?索引的順序如何?許多人卻只知道大概。其實理解這些概念并不難，而且索引的原理遠沒有想象的那么復雜。 　　二：索引建立

　　1. 主鍵索引

　　primary key() 要求關鍵字不能重復，也不能為null,同時增加主鍵約束 主鍵索引定義時，不能命名

　　2. 唯一索引

　　unique index() 要求關鍵字不能重復，同時增加唯一約束

　　3. 普通索引

　　index() 對關鍵字沒有要求

　　4. 全文索引

　　fulltext key() 關鍵字的來源不是所有字段的數據，而是字段中提取的特別關鍵字

　　關鍵字：可以是某個字段或多個字段，多個字段稱為復合索引

　　建表：creat table student( stu_id int unsigned not null auto_increment, name varchar(32) not null default '', phone char(11) not null default '', stu_code varchar(32) not null default '', stu_desc text, primary key ('stu_id'), //主鍵索引 unique index 'stu_code' ('stu_code'), //唯一索引 index 'name_phone' ('name','phone'), //普通索引，復合索引 fulltext index 'stu_desc' ('stu_desc'), //全文索引) engine=myisam charset=utf8; 更新：alert table student add primary key ('stu_id'), //主鍵索引 add unique index 'stu_code' ('stu_code'), //唯一索引 add index 'name_phone' ('name','phone'), //普通索引，復合索引 add fulltext index 'stu_desc' ('stu_desc'); //全文索引 刪除：alert table sutdent drop primary key, drop index 'stu_code', drop index 'name_phone', drop index 'stu_desc';

　　三：淺析explain用法

　　有什么用?

　　在MySQL中，當數據量增長的特別大的時候就需要用到索引來優化SQL語句，而如何才能判斷我們辛辛苦苦寫出的SQL語句是否優良?這時候explain就派上了用場。

　　怎么使用?

　　explain + SQL語句即可 如：explain select * from table;

　　相信第一次使用explain參數的朋友一定會疑惑這一大堆參數究竟有什么用呢?筆者搜集了一些資料，在這兒做一個總結希望能夠幫助大家理解。

　　參數介紹

　　id

　　如果是子查詢，id的序號會遞增，id的值越大優先級越高，越先被執行

　　select_type

　　查詢的類型，主要用于區別普通查詢、聯合查詢、子查詢等的復雜查詢 SIMPLE:簡單的select查詢，查詢中不包含子查詢或者UNION PRIMARY:查詢中若包含任何復雜的子部分，最外層查詢則被標記為PRIMARY(最后加載的那一個 ) SUBQUERY:在SELECT或WHERE列表中包含了子查詢 DERIVED:在FROM列表中包含的子查詢被標記為DERIVED(衍生)Mysql會遞歸執行這些子查詢，把結果放在臨時表里。 UNION:若第二個SELECT出現在UNION之后，則被標記為UNION;若UNION包含在FROM字句的查詢中，外層SELECT將被標記為:DERIVED UNION RESULT:從UNION表獲取結果的SELECT type

　　顯示查詢使用了何種類型

　　從最好到最差依次是System>const>eq_ref>range>index>All(全表掃描) 一般來說至少達到range級別，最好達到ref

　　System:表只有一行記錄，這是const類型的特例，平時不會出現(忽略不計)const:表示通過索引一次就找到了,const用于比較primary key或者unique索引，因為只匹配一行數據，所以很快。如將主鍵置于where列表中，MySQL就能將該查詢轉換為一個常量。

　　eq_ref:唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。

　　ref：非唯一索引掃描，返回匹配某個單獨值的行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而它可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體range：只檢索給定范圍的行，使用一個索引來選擇行。

　　key列顯示使用了哪個索引，一般就是在你的where語句中出現了between、<、>、in等的查詢。這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某一點，而結束于另一點，不用掃描全部索引。index:FULL INDEX SCAN,index與all區別為index類型只遍歷索引樹。這通常比all快，因為索引文件通常比數據文件小。

　　extra

　　包含不適合在其他列中顯示但十分重要的額外信息 包含的信息： **(危險!)**Using

　　filesort:說明mysql會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，MYSQL中無法利用索引完成的排序操作稱為“文件排序” **(特別危險!)**Using

　　temporary:使用了臨時表保存中間結果，MYSQL在對查詢結果排序時使用臨時表。常見于排序order by 和分組查詢 group by Using

　　index:表示相應的select操作中使用了覆蓋索引，避免訪問了表的數據行，效率不錯。如果同時出現using

　　where，表明索引被用來執行索引鍵值的查找;如果沒有同時出現using where，表明索引用來讀取數據而非執行查找操作。

　　possible_keys

　　顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上若存在索引，則該索引將被列出， 但不一定被查詢實際使用

　　key

　　實際使用的索引，如果為NULL，則沒有使用索引。查詢中若使用了覆蓋索引，則該索引僅出現在key列表中，key參數可以作為使用了索引的判斷標準

　　key_len

　　:表示索引中使用的字節數，可通過該列計算查詢中索引的長度，在不損失精確性的情況下，長度越短越好，key_len顯示的值為索引字段的最大可能長度，并非實際使用長度，即key_len是根據表定義計算而得，不是通過表內檢索出的。

　　ref

　　顯示索引的哪一列被使用了，如果可能的話，是一個常數。哪些列或常量被用于查找索引上的值。

　　rows

　　根據表統計信息及索引選用情況，大致估算出找到所需記錄所需要讀取的行數

　　四：慢查詢優化

　　關于MySQL索引原理是比較枯燥的東西，大家只需要有一個感性的認識，并不需要理解得非常透徹和深入。我們回頭來看看一開始我們說的慢查詢，了解完索引原理之后，大家是不是有什么想法呢?先總結一下索引的幾大基本原則

　　建索引的幾大原則

　　1.最左前綴匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到范圍查詢(>、<、between、like)就停止匹配，比如a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。

　　2.=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式

　　3.盡量選擇區分度高的列作為索引,區分度的公式是count(distinct col)/count(*)，表示字段不重復的比例，比例越大我們掃描的記錄數越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度就是0，那可能有人會問，這個比例有什么經驗值嗎?使用場景不同，這個值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄

　　4.索引列不能參與計算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(’2014-05-29’); 5.盡量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可

　　回到開始的慢查詢

　　根據最左匹配原則，最開始的sql語句的索引應該是status、operator_id、type、operate_time的聯合索引;其中status、operator_id、type的順序可以顛倒，所以我才會說，把這個表的所有相關查詢都找到，會綜合分析;

　　比如還有如下查詢

　　select * from task where status = 0 and type = 12 limit 10;

　　select count(*) from task where status = 0 ;

　　那么索引建立成(status,type,operator_id,operate_time)就是非常正確的，因為可以覆蓋到所有情況。這個就是利用了索引的最左匹配的原則

　　查詢優化神器 - explain命令

　　關于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網explain-output，這里需要強調rows是核心指標，絕大部分rows小的語句執行一定很快(有例外，下面會講到)。所以優化語句基本上都是在優化rows。

　　慢查詢優化基本步驟

　　0.先運行看看是否真的很慢，注意設置SQL_NO_CACHE 1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高 2.explain查看執行計劃，是否與1預期一致(從鎖定記錄較少的表開始查詢) 3.order by limit 形式的sql語句讓排序的表優先查 4.了解業務方使用場景 5.加索引時參照建索引的幾大原則

　　6.觀察結果，不符合預期繼續從0分析

　　五：最左前綴原理與相關優化

　　高效使用索引的首要條件是知道什么樣的查詢會使用到索引，這個問題和B+Tree中的“最左前綴原理”有關，下面通過例子說明最左前綴原理。

　　這里先說一下聯合索引的概念。在上文中，我們都是假設索引只引用了單個的列，實際上，MySQL中的索引可以以一定順序引用多個列，這種索引叫做聯合索引，一般的，一個聯合索引是一個有序元組，其中各個元素均為數據表的一列，實際上要嚴格定義索引需要用到關系代數，但是這里我不想討論太多關系代數的話題，因為那樣會顯得很枯燥，所以這里就不再做嚴格定義。另外，單列索引可以看成聯合索引元素數為1的特例。

　　以employees.titles表為例，下面先查看其上都有哪些索引：

　　SHOW INDEX FROM employees.titles;

　　+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

　　| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |

　　+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

　　| titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |

　　| titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |

　　| titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |

　　| titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |

　　+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

　　從結果中可以到titles表的主索引為，還有一個輔助索引。為了避免多個索引使事情變復雜(MySQL的SQL優化器在多索引時行為比較復雜)，這里我們將輔助索引drop掉：

　　ALTER TABLE employees.titles DROP INDEX emp_no;

　　這樣就可以專心分析索引PRIMARY的行為了。

　　情況一：全列匹配。

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　很明顯，當按照索引中所有列進行精確匹配(這里精確匹配指“=”或“IN”匹配)時，索引可以被用到。這里有一點需要注意，理論上索引對順序是敏感的，但是由于MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引，例如我們將where中的條件順序顛倒：

　　EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　| 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |

　　+----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

　　效果是一樣的。

　　情況二：最左前綴匹配。

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

　　| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

　　當查詢條件精確匹配索引的左邊連續一個或幾個列時，如或，所以可以被用到，但是只能用到一部分，即條件所組成的最左前綴。上面的查詢從分析結果看用到了PRIMARY索引，但是key_len為4，說明只用到了索引的第一列前綴。

　　情況三：查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供。

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　此時索引使用情況和情況二相同，因為title未提供，所以查詢只用到了索引的第一列，而后面的from_date雖然也在索引中，但是由于title不存在而無法和左前綴連接，因此需要對結果進行掃描過濾from_date(這里由于emp_no唯一，所以不存在掃描)。

　　如果想讓from_date也使用索引而不是where過濾，可以增加一個輔助索引，此時上面的查詢會使用這個索引。除此之外，還可以使用一種稱之為“隔離列”的優化方法，將emp_no與from_date之間的“坑”填上。

　　首先我們看下title一共有幾種不同的值：

　　SELECT DISTINCT(title) FROM employees.titles;

　　+--------------------+

　　| title |

　　+--------------------+

　　| Senior Engineer |

　　| Staff |

　　| Engineer |

　　| Senior Staff |

　　| Assistant Engineer |

　　| Technique Leader |

　　| Manager |

　　+--------------------+

　　只有7種。在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來填補這個“坑”從而形成最左前綴：

　　EXPLAIN SELECT * FROM employees.titles

　　WHERE emp_no='10001'

　　AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')

　　AND from_date='1986-06-26';

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　這次key_len為59，說明索引被用全了，但是從type和rows看出IN實際上執行了一個range查詢，這里檢查了7個key。看下兩種查詢的性能比較：

　　SHOW PROFILES;

　　+----------+------------+-------------------------------------------------------------------------------+

　　| Query_ID | Duration | Query |

　　+----------+------------+-------------------------------------------------------------------------------+

　　| 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|

　　| 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |

　　+----------+------------+-------------------------------------------------------------------------------+

　　“填坑”后性能提升了一點。如果經過emp_no篩選后余下很多數據，則后者性能優勢會更加明顯。當然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。

　　情況四：查詢條件沒有指定索引第一列。

　　EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　由于不是最左前綴，索引這樣的查詢顯然用不到索引。

　　情況五：匹配某列的前綴字符串。

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |

　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

　　此時可以用到索引，但是如果通配符不是只出現在末尾，則無法使用索引。(原文表述有誤，如果通配符%不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個前綴)

　　情況六：范圍查詢。

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer'; 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　范圍列可以用到索引(必須是最左前綴)，但是范圍列后面的列無法用到索引。同時，索引最多用于一個范圍列，因此如果查詢條件中有兩個范圍列則無法全用到索引。 　　EXPLAIN SELECT * FROM employees.titles 　　WHERE emp_no < '10010' 　　AND title='Senior Engineer' 　　AND from_date BETWEEN '1986-01-01' AND '1986-12-31'; 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　可以看到索引對第二個范圍索引無能為力。這里特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區分范圍索引和多值匹配，因為在type中這兩者都顯示為range。同時，用了“between”并不意味著就是范圍查詢，例如下面的查詢： 　　EXPLAIN SELECT * FROM employees.titles 　　WHERE emp_no BETWEEN '10001' AND '10010' 　　AND title='Senior Engineer' 　　AND from_date BETWEEN '1986-01-01' AND '1986-12-31'; 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where | 　　+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+ 　　看起來是用了兩個范圍查詢，但作用于emp_no上的“BETWEEN”實際上相當于“IN”，也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹慎地區分多值匹配和范圍匹配，否則會對MySQL的行為產生困惑。 　　情況七：查詢條件中含有函數或表達式。

　　很不幸，如果查詢條件中含有函數或表達式，則MySQL不會為這列使用索引(雖然某些在數學意義上可以使用)。例如：

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　| 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |

　　+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

　　雖然這個查詢和情況五中功能相同，但是由于使用了函數left，則無法為title列應用索引，而情況五中用LIKE則可以。再如：

　　EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |

　　+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

　　顯然這個查詢等價于查詢emp_no為10001的函數，但是由于查詢條件是一個表達式，MySQL無法為其使用索引。看來MySQL還沒有智能到自動優化常量表達式的程度，因此在寫查詢語句時盡量避免表達式出現在查詢中，而是先手工私下代數運算，轉換為無表達式的查詢語句。

　　索引選擇性與前綴索引

　　既然索引可以加快查詢速度，那么是不是只要是查詢語句需要，就建上索引?答案是否定的。因為索引雖然加快了查詢速度，但索引也是有代價的：索引文件本身要消耗存儲空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在運行時也要消耗資源維護索引，因此索引并不是越多越好。一般兩種情況下不建議建索引。

　　第一種情況是表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了。至于多少條記錄才算多，這個個人有個人的看法，我個人的經驗是以2000作為分界線，記錄數不超過 2000可以考慮不建索引，超過2000條可以酌情考慮索引。

　　另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性(Selectivity)，是指不重復的索引值(也叫基數，Cardinality)與表記錄數(#T)的比值：

　　Index Selectivity = Cardinality / #T

　　顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。例如，上文用到的employees.titles表，如果title字段經常被單獨查詢，是否需要建索引，我們看一下它的選擇性：

　　SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;

　　+-------------+

　　| Selectivity |

　　+-------------+

　　| 0.0000 |

　　+-------------+

　　title的選擇性不足0.0001(精確值為0.00001579)，所以實在沒有什么必要為其單獨建索引。

　　有一種與索引選擇性有關的索引優化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用。

　　從圖12可以看到employees表只有一個索引，那么如果我們想按名字搜索一個人，就只能全表掃描了：

　　EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

　　+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

　　| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

　　+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

　　| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |

　　+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

　　如果頻繁按名字搜索員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建或，看下兩個索引的選擇性：

　　SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;

　　+-------------+

　　| Selectivity |

　　+-------------+

　　| 0.0042 |

　　+-------------+

　　SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

　　+-------------+

　　| Selectivity |

　　+-------------+

　　| 0.9313 |

　　+-------------+

　　顯然選擇性太低，選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法?可以考慮用first_name和last_name的前幾個字符建立索引，例如，看看其選擇性：

　　SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

　　+-------------+

　　| Selectivity |

　　+-------------+

　　| 0.7879 |

　　+-------------+

　　選擇性還不錯，但離0.9313還是有點距離，那么把last_name前綴加到4：

　　SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

　　+-------------+

　　| Selectivity |

　　+-------------+

　　| 0.9007 |

　　+-------------+

　　這時選擇性已經很理想了，而這個索引的長度只有18，比短了接近一半，我們把這個前綴索引 建上：

　　ALTER TABLE employees.employees

　　ADD INDEX first_name_last_name4 (first_name, last_name(4));

　　此時再執行一遍按名字查詢，比較分析一下與建索引前的結果：

　　SHOW PROFILES;

　　+----------+------------+---------------------------------------------------------------------------------+

　　| Query_ID | Duration | Query |

　　+----------+------------+---------------------------------------------------------------------------------+

　　| 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

　　| 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |

　　+----------+------------+---------------------------------------------------------------------------------+

　　性能的提升是顯著的，查詢速度提高了120多倍。

　　前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index(即當索引本身包含查詢所需全部數據時，不再訪問數據文件本身

　　六：InnoDB的主鍵選擇與插入優化

　　在使用InnoDB存儲引擎時，如果沒有特別的需要，請永遠使用一個與業務無關的自增字段作為主鍵。

　　經常看到有帖子或博客討論主鍵選擇問題，有人建議使用業務無關的自增主鍵，有人覺得沒有必要，完全可以使用如學號或身份證號這種唯一字段作為主鍵。不論支持哪種論點，大多數論據都是業務層面的。如果從數據庫索引優化角度看，使用InnoDB引擎而不使用自增主鍵絕對是一個糟糕的主意。

上述內容就是Mysql索引的實現原理分析，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。