目錄

1、索引概述

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

2.1?BTree

2.2 B+Tree

2.3 Hash

3、索引分類(lèi)

4、索引語(yǔ)法

4.1 創(chuàng)建索引

4.2 查看索引

4.3 刪除索引

5、SQL性能分析

5.1?SQL執(zhí)行頻率

5.2 慢查詢?nèi)罩?/p>

5.3 profile詳情

5.4 explain執(zhí)行計(jì)劃

6、索引使用

6.1 驗(yàn)證索引效率

6.2?最左前綴法則

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

---

1、索引概述

介紹：索引(index)是幫助MySQ高效獲取數(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)勢(shì)：

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

劣勢(shì)：

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

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

MySQL的索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的，不同的存儲(chǔ)引警有不同的結(jié)構(gòu)，主要包含以下幾種:

我們平常所說(shuō)的索引，如果沒(méi)有特別指明，都是指B+樹(shù)結(jié)構(gòu)組織的索引。

2.1?BTree

介紹：多路平衡查找樹(shù)

以一顆最大度數(shù)(max-degree)為5(5階)的BTree為例(每個(gè)節(jié)點(diǎn)最多存儲(chǔ)4個(gè)key，5個(gè)指針):

知識(shí)小貼士: 樹(shù)的度數(shù)指的是一個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)。? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

2.2 B+Tree

以一顆最大度數(shù)(max-degree)為4(4階)的B+Tree為例:

相對(duì)于BTree區(qū)別:

1. 所有的數(shù)據(jù)都會(huì)出現(xiàn)在葉子節(jié)點(diǎn)
2. 葉子節(jié)點(diǎn)形成一個(gè)單向鏈表

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)的性能。

2.3 Hash

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

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

Hash索引特點(diǎn)

1. Hash索引只能用于對(duì)等比較(=，in)，不支持范圍查詢(between，>，<，…)
2. 無(wú)法利用索引完成排序操作
3. 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于B+Tree索引

存儲(chǔ)引擎支持

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

為什么InnoDB存儲(chǔ)引擎選擇使用B+ Tree索引結(jié)構(gòu)?

1. 相對(duì)于二叉樹(shù)，層級(jí)更少，搜索效率高
2. 對(duì)于BTree，無(wú)論是葉子節(jié)點(diǎn)還是非葉子節(jié)點(diǎn)，都會(huì)保存數(shù)據(jù)，這樣導(dǎo)致一頁(yè)中存儲(chǔ)的鍵值減少，指針跟著減少，要同樣保存大量數(shù)據(jù)，只能增加樹(shù)的高度，導(dǎo)致性能降低
3. 相對(duì)Hash索引，B+Tree支持范圍匹配及排序操作

3、索引分類(lèi)

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

聚集索引選取規(guī)則:

1. 如果存在主鍵，主鍵索引就是聚集索引。
2. 如果不存在主鍵，將使用第一個(gè)唯一(UNIQUE)索引作為聚集索引。
3. 如果表沒(méi)有主鍵，或沒(méi)有合適的唯一索引，則InnoDB會(huì)自動(dòng)生成一個(gè)rowid作為隱藏的聚集索引。

4、索引語(yǔ)法

4.1 創(chuàng)建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...)?

4.2 查看索引

SHOW INDEX FROM table_name;

4.3 刪除索引

DROP INDEX index_name ON table_name;

案例：

表數(shù)據(jù)：

需求：

1、name字段為姓名字段，該字段的值可能會(huì)重復(fù)，為該字段創(chuàng)建索引。

CREATE INDEX idx_user_name ON tb_user(name)

2、phone手機(jī)號(hào)字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone)

3、為profession、age、status創(chuàng)建聯(lián)合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status)

4、為email建立合適的索引來(lái)提升查詢效率

CREATE INDEX idx_user_email ON tb_user(email)

5、SQL性能分析

5.1?SQL執(zhí)行頻率

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

SHOW GLOBAL STATUS LIKE 'Com____';

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

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

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

查看慢查詢?nèi)罩臼欠耖_(kāi)啟

SHOW VARIABLES LIKE 'slow_query_log'

開(kāi)啟慢查詢?nèi)罩?/strong>

SET GLOBAL slow_query_log='ON'

關(guān)閉慢查詢?nèi)罩?/strong>

SET GLOBAL slow_query_log='OFF'

5.3 profile詳情

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

profile操作:

SELECT @@have_profiling

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

SET PROFILING=1

執(zhí)行一系列的業(yè)務(wù)SQL的操作，然后通過(guò)如下指令查看指令的執(zhí)行耗時(shí):

-- 查看每一條SOL的耗時(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;

5.4 explain執(zhí)行計(jì)劃

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

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

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

1、id

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

2、select_type

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

3、type

表示連接類(lèi)型，性能由好到差的連接類(lèi)型為NULL、system、const、eg ref、ref、range、index、all

4、possible_key

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

5、Key

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

6、Key_len

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

7、rows

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

8、filtered

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

6、索引使用

6.1 驗(yàn)證索引效率

在未建立索引之前，執(zhí)行如下SQL語(yǔ)句，查看SQL的耗時(shí)。

SELECT * FROM tb_sku WHERE sn='100000003145001'

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

create index idx_sku_sn on tb_sku(sn)

然后再次執(zhí)行相同的SQL語(yǔ)句，再次查看SQL的耗時(shí)。

SELECT * FROM tb_sku WHERE sn='100000003145001'

6.2?最左前綴法則

如果索引了多列(聯(lián)合索引)，要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開(kāi)始，并且不跳過(guò)索引中的列。

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

explain select * from tb_user where profession ='軟件工程' and age = 31 and status ='0'explain select * from tb_user where profession ='軟件工程' and age = 31explain select * from tb_user where profession='軟件工程'explain select * from tb_user where age = 31 and status = '0'explain select * from tb_user where status = '0'

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