Mysql索引的類型和優缺陷詳解。本站提示廣大學習愛好者：（Mysql索引的類型和優缺陷詳解）文章只能為提供參考，不一定能成為您想要的結果。以下是Mysql索引的類型和優缺陷詳解正文
索引是一種特別的文件(InnoDB數據表上的索引是表空間的一個構成部門)，它們包括著對數據內外一切記載的援用指針。
注：
[1]索引不是全能的！索引可以加速數據檢索操作，但會使數據修正操作變慢。每修正數據記載，索引就必需刷新一次。為了在某種法式上填補這一缺點，許 多SQL敕令都有一個DELAY_KEY_WRITE項。這個選項的感化是臨時禁止MySQL在該敕令每拔出一條新記載和每修正一條現有以後連忙對索引進 行刷新，對索引的刷新將比及全體記載拔出/修正終了以後再停止。在須要把很多新記載拔出某個數據表的場所，DELAY_KEY_WRITE選項的感化將非 常顯著。
[2]別的，索引還會在硬盤上占用相當年夜的空間。是以應當只為最常常查詢和最常常排序的數據列樹立索引。留意，假如某個數據列包括很多反復的內 容，為它樹立索引就沒有太年夜的現實後果。
從實際上講，完整可認為數據內外的每一個字段分離建一個索引，但MySQL把統一個數據內外的索引總數限制為16個。
1. InnoDB數據表的索引
與MyISAM數據表比擬，索引對InnoDB數據的主要性要年夜很多。在InnoDB數據表上，索引對InnoDB數據表的主要性要在很多。在 InnoDB數據表上，索引不只會在搜刮數據記載時施展感化，照樣數據行級鎖定機制的苊、基本。”數據行級鎖定”的意思是指在事務操作的履行進程中鎖定正 在被處置的個體記載，不讓其他用戶停止拜訪。這類鎖定將影響到(但不限於)SELECT…LOCK IN SHARE MODE、SELECT…FOR UPDATE敕令和INSERT、UPDATE和DELETE敕令。
出於效力方面的斟酌，InnoDB數據表的數據行級鎖定現實產生在它們的索引上，而不是數據表本身上。明顯，數據行級鎖定機制只要在有關的數據表有一個合 適的索引可供鎖定的時刻能力施展效率。
2. 限制
假如WEHERE子句的查詢前提裡有不等號(WHERE coloum != …)，MySQL將沒法應用索引。
相似地，假如WHERE子句的查詢前提裡應用了函數(WHERE DAY(column) = …)，MySQL也將沒法應用索引。
在JOIN操作中(須要從多個數據表提取數據時)，MySQL只要在主鍵和外鍵的數據類型雷同時能力應用索引。
假如WHERE子句的查詢前提裡應用比擬操作符LIKE和REGEXP，MySQL只要在搜刮模板的第一個字符不是通配符的情形下能力應用索引。好比說， 假如查詢前提是LIKE ‘abc%'，MySQL將應用索引；假如查詢前提是LIKE ‘%abc'，MySQL將不應用索引。
在ORDER BY操作中，MySQL只要在排序前提不是一個查詢前提表達式的情形下才應用索引。(固然如斯，在觸及多個數據表查詢裡，即便有索引可用，那些索引在加速 ORDER BY方面也沒甚麼感化)
假如某個數據列裡包括很多反復的值，就算為它樹立了索引也不會有很好的後果。好比說，假如某個數據列裡包括的淨是些諸如”0/1″或”Y/N”等值，就沒 有需要為它創立一個索引。
通俗索引、獨一索引和主索引
1. 通俗索引
通俗索引(由症結字KEY或INDEX界說的索引)的獨一義務是加速對數據的拜訪速度。是以，應當只為那些最常常湧現在查詢前提(WHERE column = …)或排序前提(ORDER BY column)中的數據列創立索引。只需有能夠，就應當選擇一個數據最整潔、最緊湊的數據列(如一個整數類型的數據列)來創立索引。
2. 獨一索引
通俗索引許可被索引的數據列包括反復的值。好比說，由於人有能夠同名，所以統一個姓名在統一個”員工小我材料”數據內外能夠湧現兩次或更屢次。
假如能肯定某個數據列將只包括彼此各不雷同的值，在為這個數據列創立索引的時刻就應當用症結字UNIQUE把它界說為一個獨一索引。這麼做的利益：一是簡 化了MySQL對這個索引的治理任務，這個索引也是以而變得更有用率；二是MySQL會在有新記載拔出數據表時，主動檢討新記載的這個字段的值能否曾經在 某個記載的這個字段裡湧現過了；假如是，MySQL將謝絕拔出那條新記載。也就是說，獨一索引可以包管數據記載的獨一性。現實上，在很多場所，人們創立唯 一索引的目標常常不是為了進步拜訪速度，而只是為了不數據湧現反復。
3. 主索引
在後面曾經重復屢次強調過：必需為主鍵字段創立一個索引，這個索引就是所謂的”主索引”。主索引與獨一索引的獨一差別是：前者在界說時應用的症結字是 PRIMARY而不是UNIQUE。
4. 外鍵索引
假如為某個外鍵字段界說了一個外鍵束縛前提，MySQL就會界說一個外部索引來贊助本身以最有用率的方法去治理和應用外鍵束縛前提。
5. 復合索引
索引可以籠罩多個數據列，如像INDEX(columnA, columnB)索引。這類索引的特色是MySQL可以有選擇地應用一個如許的索引。假如查詢操作只須要用到columnA數據列上的一個索引，便可以使 用復合索引INDEX(columnA, columnB)。不外，這類用法僅實用於在復合索引中分列在前的數據列組合。好比說，INDEX(A, B, C)可以當作A或(A, B)的索引來應用，但不克不及當作B、C或(B, C)的索引來應用。
6. 索引的長度
在為CHAR和VARCHAR類型的數據列界說索引時，可以把索引的長度限制為一個給定的字符個數(這個數字必需小於這個字段所許可的最年夜字符個數)。這 麼做的利益是可以生成一個尺寸比擬小、檢索速度卻比擬快的索引文件。在絕年夜多半運用裡，數據庫中的字符串數據年夜都以各類各樣的名字為主，把索引的長度設置 為10~15個字符曾經足以把搜刮規模減少到很少的幾條數據記載了。
在為BLOB和TEXT類型的數據列創立索引時，必需對索引的長度做出限制；MySQL所許可的最年夜索引長度是255個字符。
全文索引
文本字段上的通俗索引只能加速對湧現在字段內容最後面的字符串(也就是字段內容開首的字符)停止檢索操作。假如字段裡寄存的是由幾個、乃至是多個單詞組成 的較年夜段文字，通俗索引就沒甚麼感化了。這類檢索常常以LIKE %word%的情勢湧現，這對MySQL來講很龐雜，假如須要處置的數據量很年夜，呼應時光就會很長。
這類場所恰是全文索引(full-text index)可以年夜顯身手的處所。在生成這類類型的索引時，MySQL將把在文本中湧現的一切單詞創立為一份清單，查詢操作將依據這份清單去檢索有關的數 據記載。全文索引便可以隨數據表一同創立，也能夠等往後有需要時再應用
上面這條敕令添加：
ALTER TABLE tablename ADD FULLTEXT(column1, column2)
有了全文索引，便可以用SELECT查詢敕令去檢索那些包括著一個或多個給訂單詞的數據記載了。上面是這類查詢敕令的根本語法：
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘word1′, ‘word2′, ‘word3′)
下面這條敕令將把column1和column2字段裡有word1、word2和word3的數據記載全體查詢出來。
注解：InnoDB數據表不支撐全文索引。
查詢和索引的優化
只要當數據庫裡曾經有了足夠多的測試數據時，它的機能測試成果才有現實參考價值。假如在測試數據庫裡只要幾百條數據記載，它們常常在履行完第一條查詢敕令 以後就被全體加載到內存裡，這將使後續的查詢敕令都履行得異常快–不論有無應用索引。只要當數據庫裡的記載跨越了1000條、數據總量也跨越了 MySQL辦事器上的內存總量時，數據庫的機能測試成果才成心義。
在不肯定應當在哪些數據列上創立索引的時刻，人們從EXPLAIN SELECT敕令那邊常常可以取得一些贊助。這其實只是簡略地給一條通俗的SELECT敕令加一個EXPLAIN症結字作為前綴罷了。有了這個症結 字，MySQL將不是去履行那條SELECT敕令，而是去對它停止剖析。MySQL將以表格的情勢把查詢的履行進程和用到的索引(假如有的話)等信息列出 來。
在EXPLAIN敕令的輸入成果裡，第1列是從數據庫讀取的數據表的名字，它們按被讀取的前後次序分列。type列指定了本數據表與其它數據表之間的聯系關系 關系(JOIN)。在各類類型的聯系關系關系傍邊，效力最高的是system，然後順次是const、eq_ref、ref、range、index和 All(All的意思是：對應於上一級數據內外的每筆記錄，這個數據內外的一切記載都必需被讀取一遍–這類情形常常可以用一索引來防止)。
possible_keys數據列給出了MySQL在搜刮數據記載時可選用的各個索引。key數據列是MySQL現實選用的索引，這個索引按字節盤算的長 度在key_len數據列裡給出。好比說，關於一個INTEGER數據列的索引，這個字節長度將是4。假如用到了復合索引，在key_len數據列裡還可 以看到MySQL詳細應用了它的哪些部門。作為普通紀律，key_len數據列裡的值越小越好(意思是更快)。
ref數據列給出了聯系關系關系中另外一個數據內外的數據列的名字。row數據列是MySQL在履行這個查詢時估計會從這個數據內外讀出的數據行的個數。row 數據列裡的一切數字的乘積可讓我們年夜致懂得這個查詢須要處置若干組合。
最初，extra數據列供給了與JOIN操作有關的更多信息，好比說，假如MySQL在履行這個查詢時必需創立一個暫時數據表，就會在extra列看到 using temporary字樣。
以上就是本文的全體內容，願望對年夜家的進修有所贊助，也願望年夜家多多支撐。
