一、索引介紹

索引(index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)。在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu),這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法,這種數(shù)據(jù)結(jié)構(gòu)就是索引。

二、索引優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的io成本通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗。

缺點(diǎn)：

索引列也是要占用空間的。索引大大提高了查詢效率，同時(shí)卻也降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE、DELETE時(shí)，效率降低。

三、索引結(jié)構(gòu)

通常我們所說(shuō)的索引，沒有特別指明，都是指B+樹結(jié)構(gòu)組織的索引

B+Tree索引：最常見的索引類型，大部分引擎都支持B+樹索引

Hash索引：底層數(shù)據(jù)結(jié)構(gòu)是用哈希表實(shí)現(xiàn)的，只有精確匹配索引列的查詢才有效，不支持范圍查詢

R-tree(空間索引)：空間索引是MyISAM引擎的一一個(gè)特殊索引類型，主要用于地理空間數(shù)據(jù)類型，通常使用較少

Full-text(全文索引)：是一種通過(guò)建立倒排索引,快速匹配文檔的方式。類似于Lucene,Solr,ES　

1. 經(jīng)典B+樹

看結(jié)構(gòu)和B樹比較像，B+樹與B樹的區(qū)別在于：

1.所有的元素都會(huì)出現(xiàn)在葉子節(jié)點(diǎn)，非葉子節(jié)點(diǎn)主要起到索引的作用，而葉子節(jié)點(diǎn)是用來(lái)存放數(shù)據(jù)的

2.B+樹的數(shù)據(jù)結(jié)構(gòu)中，葉子節(jié)點(diǎn)形成了一個(gè)單向鏈表，每一個(gè)節(jié)點(diǎn)都會(huì)通過(guò)指針指向下一個(gè)元素

2. MySQL中B+樹索引

MySQL索引數(shù)據(jù)結(jié)構(gòu)對(duì)經(jīng)典的B+Tree進(jìn)行了優(yōu)化。在原B+Tree的基礎(chǔ)上,增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的鏈表指針，就形成了帶有順序指針的B+Tree,提高區(qū)間訪問(wèn)的性能，葉子節(jié)點(diǎn)雙向鏈表+首尾相連，便于范圍搜索和排序。

3. Hash索引

哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值,映射到對(duì)應(yīng)的槽位上,然后存儲(chǔ)在hash表中。

如果兩個(gè)(或多個(gè))鍵值，映射到一個(gè)相同的槽位上,他們就產(chǎn)生了hash沖突(也稱為hash碰撞)，可以通過(guò)鏈表來(lái)解決。

特點(diǎn)：

1. Hash索引只能用于對(duì)等比較(=，in)， 不支持范圍查詢(between, >，<, ...)

2. 無(wú)法利用索引完成排序操作

3. 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于B+tree索引

存儲(chǔ)引擎支持：

在MySQL中，支持hash索引的是Memory引擎,而InnoDB中具有自適應(yīng)hash功能，hash索引是存儲(chǔ)引擎根據(jù)B+Tree索引在指定條件下自動(dòng)構(gòu)建的。

4. 為什么InnoDB選擇B+樹索引？ 　　

相對(duì)于二叉樹，層級(jí)更少，搜索效率高；

對(duì)于B-tree,無(wú)論是葉子節(jié)點(diǎn)還是非葉子節(jié)點(diǎn)，都會(huì)保存數(shù)據(jù)，這樣導(dǎo)致一頁(yè)中存儲(chǔ)的鍵值減少，指針跟著減少，要同樣保存大量數(shù)據(jù)，只能增加樹的高度，導(dǎo)致性能降低；

相對(duì)Hash索引，Hash索引只支持等值匹配，B+tree支持范圍匹配及排序操作。

四、索引分類

在InnoDB存儲(chǔ)引擎中，根據(jù)索引的存儲(chǔ)形式，又可以分為以下兩種：

聚簇索引（Clustering Index）：將數(shù)據(jù)存儲(chǔ)與索引放到了一塊，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保存了行數(shù)據(jù)；必須有而且只有一個(gè)。

二級(jí)索引（Secondary Index）：將數(shù)據(jù)與索引分開存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)關(guān)聯(lián)的是對(duì)應(yīng)的主鍵；可以存在多個(gè)。

聚簇索引選取規(guī)則：

如果存在主鍵，主鍵索引就是聚簇索引。

如果不存在主鍵，將使用第一個(gè)唯一(UNIQUE) 索引作為聚簇索引。

如果表沒有主鍵，或沒有合適的唯一索引，則InnoDB會(huì)自動(dòng)生成一個(gè)rowid作為隱藏的聚簇索引。

如果是（非主鍵）條件查詢，則采用回表查詢，即先通過(guò)二級(jí)索引查找主鍵（聚簇索引），得到主鍵再通過(guò)聚簇索引查找這一行數(shù)據(jù)。

InnoDB主鍵索引的B+tree高度為多高呢?

假設(shè):

一行數(shù)據(jù)大小為1k,一頁(yè)中可以存儲(chǔ)16行這樣的數(shù)據(jù)。InnoDB的指針占用6個(gè)字節(jié)的空間，主鍵即使為bigint,占用字節(jié)數(shù)為8。

高度為2:

n*8+(n+ 1)*6= 16*1024 , 算出n約為1170

1171*16= 18736

高度為3：

1171 * 1171 * 16 = 21939856

五、索引語(yǔ)法

創(chuàng)建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_ name ON table_ name ( index_ _col_ name,.. ) ;

查看索引

SHOW INDEX FROM table_ name ;

刪除索引

DROP INDEX index_ name ON table_ name ;

六、SQL性能分析

1. SQL執(zhí)行頻率

MySQL客戶端連接成功后,通過(guò)show [session|global] status命令可以提供服務(wù)器狀態(tài)信息。通過(guò)如下指令，可以查看當(dāng)前數(shù)據(jù)庫(kù)的INSERT、UPDATE、DELETE、 SELECT的訪問(wèn)頻次：

show global status like 'Com_______';

2. 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)指定參數(shù)(long_ query_ _time, 單位:秒,默認(rèn)10秒)的所有SQL語(yǔ)句的日志。

MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟,需要在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:

#開啟MySQL慢日志查詢開關(guān)
slow_query_log=1

#設(shè)置慢日志的時(shí)間為2秒，SQL 語(yǔ)句執(zhí)行時(shí)間超過(guò)2秒，就會(huì)視為慢查詢，記錄慢查詢?nèi)罩?
long query time=2

配置完畢之后，通過(guò)以下指令重新啟動(dòng)MySQL服務(wù)器進(jìn)行測(cè)試,查看慢日志文件中記錄的信息/var/lib/mysql/localhost-slow.log

當(dāng)某一操作時(shí)間多于2s則會(huì)被記錄在慢查詢?nèi)罩局小?/p>

3. profile詳情

show profiles能夠在做SQL優(yōu)化時(shí)幫助我們了解時(shí)間都耗費(fèi)到哪里去了。通過(guò)have_ profiling參數(shù), 能夠看到當(dāng)前MySQL是否支持profile操作:

#查看當(dāng)前數(shù)據(jù)庫(kù)是否支持profile操作
select @@have_profiling

默認(rèn)profiling是關(guān)閉的，可以通過(guò)set語(yǔ)句在session/ global級(jí)別開啟profiling:

#開啟profiling
set profiling = 1;
#查看每一條SQL 的耗時(shí)基本情況
show profiles;

#查看指定query_ id的SQL語(yǔ)句各個(gè)階段的耗時(shí)情況
show profile for query query_ id;

#查看指定query_ id的SQL語(yǔ)句CPU的使用情況
show profile cpu for query query_id;

4. explain執(zhí)行計(jì)劃

EXPLAIN或者DESC命令獲取MySQL如何執(zhí)行SELECT語(yǔ)句的信息，包括在SELECT語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序。語(yǔ)法:

#直接在select語(yǔ)句之前加，上關(guān)鍵字explain / desc
EXPLAIN SELECT 字段列表FROM 表名WHERE 條件;

EXPLAIN執(zhí)行計(jì)劃各字段含義:

Id：　

select查詢的序列號(hào)，表示查詢中執(zhí)行select子句或者是操作表的順序(id相同,執(zhí)行順序從上到下; id不同,值越大,越先執(zhí)行)。

select_ type：

表示SELECT的類型,常見的取值有SIMPLE (簡(jiǎn)單表,即不使用表連接或者子查詢)、PRIMARY (主查詢，即外層的查詢)、UNION (UNION 中的第二個(gè)或者后面的查詢語(yǔ)句)、SUBQUERY (SELECT/WHERE之后包含了子查詢)等

type：

表示連接類型，性能由好到差的連接類型為NULL、system、 const、 eq_ref、ref、range、index、all 。

possible_ key：

顯示可能應(yīng)用在這張表上的索引，一個(gè)或多個(gè)。

Key：

實(shí)際使用的索引，如果為NULL，則沒有使用索引。

Key_ len：

表示索引中使用的字節(jié)數(shù)，該值為索引字段最大可能長(zhǎng)度,并非實(shí)際使用長(zhǎng)度,在不損失精確性的前提下，長(zhǎng)度越短越好。

rows：

MySQL認(rèn)為必須要執(zhí)行查詢的行數(shù),在innodb引擎的表中，是-一個(gè)估計(jì)值，可能并不總是準(zhǔn)確的。

filtered：

表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比，filtered 的值越大越好。

七、索引使用

1. 索引效率

當(dāng)數(shù)據(jù)量特別大時(shí)，在未建立索引之前，執(zhí)行SQL，查詢無(wú)索引字段SQL的耗時(shí)非常大。

針對(duì)字段創(chuàng)建索引后。

再次執(zhí)行相同的SQL語(yǔ)句，SQL的耗時(shí)將大大減小。

2. 聯(lián)合索引

最左前綴法則

如果索引了多列(聯(lián)合索引) , 要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，查詢必須包含最左邊的列（否則全部失?。?，并且不跳過(guò)索引中的列。

如果跳躍某一列，索引將部分失效(后面的字段索引失效)。

范圍查詢

聯(lián)合索引中，出現(xiàn)范圍查詢(>,<),范圍查詢右側(cè)的列索引失效，一般使用>=或者<=可以有效規(guī)避這種情況

3. 索引失效

索引列運(yùn)算

不要在索引列上進(jìn)行運(yùn)算操作，索引將失效。

字符串不加引號(hào)

字符串類型字段使用時(shí)，不加引號(hào)，索引將失效。

模糊查詢

如果僅僅是尾部模糊匹配，索引不會(huì)失效。如果是頭部模糊匹配，索引失效?！?/p>

or連接的條件

用or分割開的條件，如果or前的條件 中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會(huì)被用到。只有兩側(cè)都使用索引時(shí)索引才會(huì)生效。

數(shù)據(jù)分布影響　

如果MySQL評(píng)估使用索引比全表掃描更慢，則不使用索引、索引失效。

4. SQL提示

SQL提示，是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段,簡(jiǎn)單來(lái)說(shuō)，就是在SQL語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化操作的目的。

#  use index:
explain select * from tb_name use index(索引名) where profession= 'xxxx';

#  ignore index:
explain select * from tb_name ignore index(索引名) where profession='xxxx';

#  force index:
explain select * from tb_name force index(索引名) where profession='xxxx';

5. 覆蓋索引

盡量使用覆蓋索引(查詢使用了索引，并且需要返回的列,在該索引中已經(jīng)全部能夠找到)，減少 select * 。

在Extra字段中出現(xiàn)的數(shù)據(jù)分析：

using index condition:查找使用了索引，但是需要回表查詢數(shù)據(jù)

using where; using index:查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢數(shù)據(jù)

6. 前綴索引

當(dāng)字段類型為字符串(varchar, text等 ),時(shí),有時(shí)候需要索引很長(zhǎng)的字符串,這會(huì)讓索引變得很大,查詢時(shí)，浪費(fèi)大量的磁盤IO,影響查詢效率。此時(shí)可以只將字符串的一部分前綴建立索引，這樣可以大大節(jié)約索引空間，從而提高索引效率。

#語(yǔ)法
create index idx_xxx on table_ name(column(n)) ;
#前綴長(zhǎng)度
可以根據(jù)索引的選擇性來(lái)決定，而選擇性是指不重復(fù)的索引值(基數(shù))和數(shù)據(jù)表的記錄總數(shù)的比值,索引選擇性越高則查詢效率越高,
唯一索引的選擇性是1,這是最好的索引選擇性,性能也是最好的。

# 求取選擇性
　select count(distinct email)/ count(*) from tb_name ;
　select count(distinct substring(email,1 ,5)) / count(*) from tb_name ;

7. 單列索引與聯(lián)合索引

單列索引:即一個(gè)索引只包含單個(gè)列。

聯(lián)合索引:即一個(gè)索引包含了多個(gè)列。

在業(yè)務(wù)場(chǎng)景中，如果存在多個(gè)查詢條件,考慮針對(duì)于查詢字段建立索引時(shí),建議建立聯(lián)合索引（效率較高、有效規(guī)避一些回表查詢），而非單列索引。

多條件聯(lián)合查詢時(shí)，MySQL優(yōu)化器會(huì)評(píng)估哪個(gè)字段的索引效率更高,會(huì)選擇該索引完成本次查詢。當(dāng)創(chuàng)建了聯(lián)合索引時(shí)會(huì)有單列索引干擾，我們可以指定聯(lián)合索引查詢。

聯(lián)合索引情況：

八、索引設(shè)計(jì)原則

1. 針對(duì)于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。

2.針對(duì)于常作為查詢條件(where) 、排序(order by)、分組(group by)操作的字段建立索引。

3.盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高,使用索引的效率越高。

4.如果是字符串類型的字段， 字段的長(zhǎng)度較長(zhǎng)，可以針對(duì)于字段的特點(diǎn)，建立前綴索引。

5.盡量使用聯(lián)合索引， 減少單列索引,查詢時(shí),聯(lián)合索引很多時(shí)候可以覆蓋索引，節(jié)省存儲(chǔ)空間,避免回表,提高查詢效率。

6.要控制索引的數(shù)量, 索引并不是多多益善,索引越多,維護(hù)索引結(jié)構(gòu)的代價(jià)也就越大,會(huì)影響增刪改的效率。

7.如果索引列不能存儲(chǔ)NULL值，請(qǐng)?jiān)趧?chuàng)建表時(shí)使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時(shí)，它可以更好地確定哪個(gè)索引最有效地用于查詢。

總結(jié)

本篇文章就到這里了，希望能夠給你帶來(lái)幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容!