一、索引的特點(diǎn)

當(dāng)MySQL單表記錄數(shù)過大時(shí)，增刪改查性能都會(huì)急劇下降。MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。除非單表數(shù)據(jù)未來會(huì)一直不斷上漲，否則不要一開始就考慮拆分，拆分會(huì)帶來邏輯、部署、運(yùn)維的各種復(fù)雜度。一般以整型值為主的表在千萬級(jí)以下，字符串為主的表在五百萬以下是沒有太大問題的，而事實(shí)上很多時(shí)候MySQL單表的性能依然有不少優(yōu)化空間，甚至能正常支撐千萬級(jí)以上的數(shù)據(jù)量。

索引優(yōu)勢(shì)和劣勢(shì)：

• 優(yōu)勢(shì)： 大大減少了服務(wù)器需要掃描的數(shù)據(jù)量，可以幫助服務(wù)器避免排序和臨時(shí)表，實(shí)現(xiàn)快速檢索，將隨機(jī)I/O變成順序I/O，減少I/O次數(shù)，加快檢索速度；根據(jù)索引分組和排序，可以加快分組和排序；
• 劣勢(shì)： 索引本身也是表，因此會(huì)占用存儲(chǔ)空間，一般來說，索引表占用的空間的數(shù)據(jù)表的1.5倍；索引表的維護(hù)和創(chuàng)建需要時(shí)間成本，這個(gè)成本隨著數(shù)據(jù)量增大而增大；構(gòu)建索引會(huì)降低數(shù)據(jù)表的修改操作（刪除，添加，修改）的效率，因?yàn)樵谛薷臄?shù)據(jù)表的同時(shí)還需要修改索引表；創(chuàng)建索引時(shí)需要對(duì)表加鎖，因此實(shí)際操作中需要在業(yè)務(wù)空閑期間進(jìn)行。

二、索引類型

Mysql目前主要有以下幾種索引類型：FULLTEXT，HASH，BTREE，RTREE。

1.FULLTEXT

即為全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以創(chuàng)建全文索引。

全文索引并不是和MyISAM一起誕生的，它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對(duì)文本的模糊查詢效率較低的問題。

FULLTEXT（全文）索引，僅可用于MyISAM和InnoDB

• 對(duì)于較大的數(shù)據(jù)集，把數(shù)據(jù)添加到一個(gè)沒有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把數(shù)據(jù)添加到一個(gè)已經(jīng)有FULLTEXT索引的表快。
• 5.6版本前的MySQL自帶的全文索引只能用于MyISAM存儲(chǔ)引擎，如果是其它數(shù)據(jù)引擎，那么全文索引不會(huì)生效。5.6版本之后InnoDB存儲(chǔ)引擎開始支持全文索引。
• 在MySQL中，全文索引支隊(duì)英文有用，目前對(duì)中文還不支持。5.7版本之后通過使用ngram插件開始支持中文。
• 在MySQL中，如果檢索的字符串太短則無法檢索得到預(yù)期的結(jié)果，檢索的字符串長度至少為4字節(jié)，此外，如果檢索的字符包括停止詞，那么停止詞會(huì)被忽略。

2.HASH

哈希索引用索引列的值計(jì)算該值的hashCode，然后在hashCode相應(yīng)的位置存執(zhí)該值所在行數(shù)據(jù)的物理位置，因?yàn)槭褂蒙⒘兴惴?，因此訪問速度非常快，但是一個(gè)值只能對(duì)應(yīng)一個(gè)hashCode，而且是散列的分布方式。由于HASH的唯一（幾乎100%的唯一）及類似鍵值對(duì)的形式，很適合作為索引。

HASH索引可以一次定位，不需要像樹形索引那樣逐層查找,因此具有極高的效率。但是，這種高效是有條件的，即只在“=”和“in”條件下高效，對(duì)于范圍查詢、排序及組合索引仍然效率不高。

3.BTREE

BTREE(B+TREE)索引就是一種將索引值按一定的算法，存入一個(gè)樹形的數(shù)據(jù)結(jié)構(gòu)中（二叉樹），每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。由于BTREE非葉子節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)（data），因此所有的數(shù)據(jù)都要查詢至葉子節(jié)點(diǎn)，而葉子節(jié)點(diǎn)的高度都是相同的，因此所有數(shù)據(jù)的查詢速度都是一樣的。這是MySQL里默認(rèn)和最常用的索引類型。

4.RTREE

RTREE在MySQL很少使用，僅支持geometry數(shù)據(jù)類型，支持該類型的存儲(chǔ)引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對(duì)于BTREE，RTREE的優(yōu)勢(shì)在于范圍查找。

三、索引種類

• 普通索引：僅加速查詢。
• 唯一索引：加速查詢 + 列值唯一（可以有null）。
• 主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個(gè)。
• 組合索引：多列值組成一個(gè)索引，專門用于組合搜索，其效率大于索引合并，遵循“最左前綴”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當(dāng)于建立了col1,col1col2,col1col2col3三個(gè)索引，而col2或者col3是不能使用索引的。
• 全文索引：對(duì)文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索。

四、索引的使用策略

1.什么時(shí)候要使用索引？

主鍵自動(dòng)建立唯一索引；經(jīng)常作為查詢條件在WHERE或者ORDER BY；語句中出現(xiàn)的列要建立索引；作為排序的列要建立索引；查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引高并發(fā)條件下傾向組合索引；用于聚合函數(shù)的列可以建立索引，例如使用了max(column_1)或者count(column_1)時(shí)的column_1就需要建立索引。

2.什么時(shí)候不要使用索引？

經(jīng)常增刪改的列不要建立索引；有大量重復(fù)的列不建立索引；表記錄太少不要建立索引。只有當(dāng)數(shù)據(jù)庫里已經(jīng)有了足夠多的測(cè)試數(shù)據(jù)時(shí)，它的性能測(cè)試結(jié)果才有實(shí)際參考價(jià)值。如果在測(cè)試數(shù)據(jù)庫里只有幾百條數(shù)據(jù)記錄，它們往往在執(zhí)行完第一條查詢命令之后就被全部加載到內(nèi)存里，這將使后續(xù)的查詢命令都執(zhí)行得非?？?ndash;不管有沒有使用索引。只有當(dāng)數(shù)據(jù)庫里的記錄超過了1000條、數(shù)據(jù)總量也超過了MySQL服務(wù)器上的內(nèi)存總量時(shí)，數(shù)據(jù)庫的性能測(cè)試結(jié)果才有意義。

3.索引失效的情況?

在組合索引中不能有列的值為NULL，如果有，那么這一列對(duì)組合索引就是無效的；在一個(gè)SELECT語句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；LIKE操作中，’%aaa%'不會(huì)使用索引，也就是索引會(huì)失效，但是’aaa%'可以使用索引；在索引的列上使用表達(dá)式或者函數(shù)會(huì)使索引失效，例如：

select * from table where ceate_time > unix_timestamp(curdate());

將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致索引失效而進(jìn)行全表掃描，因此我們可以改成當(dāng)前時(shí)間由程序作為參數(shù)傳入：

select * from table where ceate_time > 1524561911;

• 其它通配符同樣，也就是說，在查詢條件中使用正則表達(dá)式時(shí)，只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引；
• 在查詢條件中使用不等于，包括<符號(hào)、>符號(hào)和！=會(huì)導(dǎo)致索引失效。特別的是：如果對(duì)主鍵索引使用！=則不會(huì)使索引失效，如果對(duì)主鍵索引或者整數(shù)類型的索引使用<符號(hào)或者>符號(hào)也不會(huì)使索引失效。（不等于，包括<符號(hào)、>符號(hào)和！，如果占總記錄的比例很小的話，也不會(huì)失效）；
• 在查詢條件中使用IS NULL或者IS NOT NULL會(huì)導(dǎo)致索引失效；
• 字符串不加單引號(hào)會(huì)導(dǎo)致索引失效。更準(zhǔn)確的說是類型不一致會(huì)導(dǎo)致失效，比如字段mobile是字符串類型的，使用WHERE mobile=99999 則會(huì)導(dǎo)致失敗，應(yīng)該改為WHERE mobile=‘99999’；
• 在查詢條件中使用OR連接多個(gè)條件會(huì)導(dǎo)致索引失效，除非OR鏈接的每個(gè)條件都加上索引，這時(shí)應(yīng)該改為兩次查詢，然后用UNION ALL連接起來；
• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否則索引失效。特別的是：如果排序的是主鍵索引則select * 也不會(huì)導(dǎo)致索引失效；
• 盡量不要包括多列排序，如果一定要，最好為這隊(duì)列構(gòu)建組合索引。

4.mysql查詢優(yōu)化?

字段:

• 盡量使用TINYINT、SMALLINT、MEDIUM_INT作為整數(shù)類型而非INT，如果非負(fù)則加上UNSIGNED；
• VARCHAR的長度只分配真正需要的空間；
• 使用枚舉或整數(shù)代替字符串類型；
• 盡量使用TIMESTAMP而非DATETIME；
• 單表不要有太多字段，建議在20以內(nèi)；
• 避免使用NULL字段，很難查詢優(yōu)化且占用額外索引空間；
• 用整型來存IP。

索引：

• 索引并不是越多越好，要根據(jù)查詢有針對(duì)性的創(chuàng)建，考慮在WHERE和ORDER BY命令上涉及的列建立索引，可根據(jù)EXPLAIN來查看是否用了索引還是全表掃描；
• 應(yīng)盡量避免在WHERE子句中對(duì)字段進(jìn)行NULL值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；
• 值分布很稀少的字段不適合建索引，例如“性別”這種只有兩三個(gè)值的字段；
• 字符字段只建前綴索引；
• 字符字段最好不要做主鍵；
• 不用外鍵，由程序保證約束；盡量不用UNIQUE，由程序保證約束；
• 使用多列索引時(shí)主意順序和查詢條件保持一致，同時(shí)刪除不必要的單列索引。

查詢sql：

• 可通過開啟慢查詢?nèi)罩緛碚页鲚^慢的SQL；
• 不做列運(yùn)算：SELECT id WHERE age + 1 = 10，任何對(duì)列的操作都將導(dǎo)致表掃描，它包括數(shù)據(jù)庫教程函數(shù)、計(jì)算表達(dá)式等等，查詢時(shí)要盡可能將操作移至等號(hào)右邊；
• sql語句盡可能簡(jiǎn)單：一條sql只能在一個(gè)cpu運(yùn)算；大語句拆小語句，減少鎖時(shí)間；一條大sql可以堵死整個(gè)庫；
• 不用SELECT *；
• OR改寫成IN：OR的效率是n級(jí)別，IN的效率是log(n)級(jí)別，IN的個(gè)數(shù)建議控制在200以內(nèi)；
• 不用函數(shù)和觸發(fā)器，在應(yīng)用程序?qū)崿F(xiàn)；
• 避免%xxx式查詢，’%xxx%'不會(huì)使用索引,可以使用全文索引，然后：
• SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
• 少用JOIN；
• 使用同類型進(jìn)行比較，比如用’123’和’123’比，123和123比；
• 組合索引要遵循最做前綴原則，排序分組頻率最高的列放在最左邊，以此類推；
• 盡量避免在WHERE子句中使用!=或<>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；
• 對(duì)于連續(xù)數(shù)值，使用BETWEEN不用IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5；
• 列表數(shù)據(jù)不要拿全表，要使用LIMIT來分頁，每頁數(shù)量也不要太大；
• 使用短索引，如果可能應(yīng)該指定一個(gè)前綴長度。例如，如果有一個(gè)CHAR(255)的 列，如果在前10 個(gè)或20 個(gè)字符內(nèi)，多數(shù)值是惟一的，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤空間和I/O操作。

5.索引的常見問題

1.索引是干什么的？

索引用于快速找出在某個(gè)列中有一特定值的行。不使用索引，mysql必須從第一條記錄開始讀完整個(gè)表直到找出相關(guān)的行。表越大，花費(fèi)的時(shí)間越多。如果表中查詢的列有一個(gè)索引，mysql能快速到達(dá)一個(gè)位置搜尋到數(shù)據(jù)文件的中間，沒有必要查看所有數(shù)據(jù)。

大多數(shù)mysql的索引(primary key、index、unique、fulltext)在B樹中存儲(chǔ)，只是空間列類型的索引使用R樹，并且memory表還支持hash索引。

2.索引好復(fù)雜，我該怎么理解索引，有沒有一個(gè)更形象的例子？

索引就像是一本書的目錄。

3.索引越多越好?

大多數(shù)情況下，索引能大幅提高查詢效率。但是:數(shù)據(jù)變更(增刪改)都需要維護(hù)索引，因此更多索引意味著更多維護(hù)成本；也意味著需要更多控件空間(一本書100頁，卻有50頁目錄？)；過小的表，建索引可能會(huì)更慢。

4.索引的字段類型問題

text類型，也可建索引(需要指定長度)；MyISAM存儲(chǔ)引擎長度綜合不能超過1000字節(jié)；用來篩選的值盡量保持和索引列同樣的數(shù)據(jù)類型。

5.like能用到索引？

盡量減少like查詢，但是也不是絕對(duì)不可用，'xxx%'是可以用到索引的。除了like，以下操作符也可以用到索引：

<，<=，=，>，>=，between，in

這些用不到索引：

<>，not in，!=

6.什么樣的字段不適合建索引？

列的值唯一性太小(比如性別，類型)，不適合建索引。(什么叫大??？一般來說，同值的數(shù)據(jù)超過表的15%，那就沒有必要建索引了)更新非常頻繁的數(shù)據(jù)不適合建索引。

7.一次查詢能用多個(gè)索引？

不能

8.多列查詢?cè)撊绾谓ㄋ饕?/strong>

-- 命中col1、col2聯(lián)合索引
select col1,col2 from test where col1 = 'xxx';
-- 不能命中col1、col2聯(lián)合索引
select col1,col2 from test where col2 = 'xxx';

like '%xxx'
not in
!=

where md5(password) = "xxx"

select * from test where mobile = 13800002222;

select * from test where mobile = '13800002222';