一、索引的特點

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

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

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

二、索引類型

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%"這類針對文本的模糊查詢效率較低的問題。

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

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

2.HASH

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

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

3.BTREE

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

4.RTREE

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

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

三、索引種類

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

四、索引的使用策略

1.什么時候要使用索引？

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

2.什么時候不要使用索引？

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

3.索引失效的情況?

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

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

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

select * from table where ceate_time > 1524561911;

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

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

字段:

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

索引：

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

查詢sql：

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

5.索引的常見問題

1.索引是干什么的？

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

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

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

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

3.索引越多越好?

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

4.索引的字段類型問題

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

5.like能用到索引？

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

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

這些用不到索引：

<>，not in，!=

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

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

7.一次查詢能用多個索引？

不能

8.多列查詢該如何建索引？

一次查詢只能用到一個索引， a列建索引還是b列建索引？誰的區(qū)分度（同值的少）更高，建誰！當(dāng)然，聯(lián)合索引也是個不錯的方案。

9.聯(lián)合索引的問題

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

所以大多數(shù)情況下，有col1、col2索引了，就不用再去建col1索引了

10.哪些常見的情況不能用到索引？

like '%xxx'
not in
!=

對列進行函數(shù)運算，如：

where md5(password) = "xxx"

存了數(shù)值的字符串類型字段(如手機號)，查詢是記得不要丟掉值的引號，否則無法命中索引：

select * from test where mobile = 13800002222;

如果mobile字段是char或者varchar類型，則上面查詢無法命中索引,應(yīng)為：

select * from test where mobile = '13800002222';

11.NULL的問題

Null會導(dǎo)致索引形同虛設(shè)，所以在設(shè)計表結(jié)構(gòu)應(yīng)避免NULL的存在。
可用其他方式來表達，比如-1。

到此這篇關(guān)于MySQL添加索引特點及優(yōu)化問題的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！