1.什么是索引

索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)
MySQL在存儲(chǔ)數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)中還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種引用(指向)表中的數(shù)據(jù),這樣我們就可以通過數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)的高級查找算法來快速找到我們想要的數(shù)據(jù)。而這種數(shù)據(jù)結(jié)構(gòu)就是索引。

1.1 索引的數(shù)據(jù)結(jié)構(gòu)

1.1.1 Hash表

在java中的HashMap，TreeMap是Hash表結(jié)構(gòu)，以鍵值對的方式存儲(chǔ)數(shù)據(jù)，使用Hash表存儲(chǔ)表數(shù)據(jù)Key可以存儲(chǔ)索引列，Value可以存儲(chǔ)行記錄或者行磁盤地址。Hash在等值查詢效率高，時(shí)間復(fù)雜度是O(1),但是，不支持范圍快速查找，范圍查找時(shí)只能通過掃描全表方式

不適合經(jīng)常需要查找和范圍查找的數(shù)據(jù)庫索引使用

1.1.2 二叉查找樹

二叉樹存儲(chǔ)的值是以key-value結(jié)構(gòu)存儲(chǔ)

二叉樹特點(diǎn)：每個(gè)節(jié)點(diǎn)最多有2個(gè)分叉，左小右大。
可以保證每次查找減少IO次數(shù)，但是會(huì)出現(xiàn)一直向右下插，或者左下插入，比如，接下來要插入的數(shù)據(jù)是18，20，22等 這樣就非常難受。
這種數(shù)據(jù)結(jié)構(gòu)不穩(wěn)定

1.1.3 平衡二叉樹

平衡二叉樹采用的是二分法的思維，它最主要的特征是樹的左右兩個(gè)子樹的層級最多相差1，
它的時(shí)間復(fù)雜度是O(log2 n)

存在問題：
當(dāng)插入的數(shù)據(jù)過多時(shí)，樹會(huì)很高，這樣每層節(jié)點(diǎn)的數(shù)量就會(huì)很多。每個(gè)節(jié)點(diǎn)讀取，都對應(yīng)這一次磁盤的IO操作，這樣，查詢性能就會(huì)很差。

1.1.4 B樹

MySQL的數(shù)據(jù)是存儲(chǔ)在磁盤文件中的，查詢處理數(shù)據(jù)時(shí)，需要先把磁盤中的數(shù)據(jù)加載到內(nèi)存中，磁盤IO操作非常耗時(shí)，需要我們?nèi)p少IO操作，那么就需要就減少樹的高度
B樹是一種多叉平衡查找樹，每個(gè)結(jié)點(diǎn)存儲(chǔ)M/2到M個(gè)關(guān)鍵字，非葉子結(jié)點(diǎn)存儲(chǔ)指向關(guān)鍵字范圍的子結(jié)點(diǎn)；

所有關(guān)鍵字在整顆樹中出現(xiàn)，且只出現(xiàn)一次，非葉子結(jié)點(diǎn)可以命中；

1.定義任意非葉子結(jié)點(diǎn)最多只有M個(gè)兒子；且M>2；
2.根結(jié)點(diǎn)的兒子數(shù)為[2, M]；
3.除根結(jié)點(diǎn)以外的非葉子結(jié)點(diǎn)的兒子數(shù)為[M/2, M]；
4.每個(gè)結(jié)點(diǎn)存放至少M(fèi)/2-1（取上整）和至多M-1個(gè)關(guān)鍵字；（至少2個(gè)關(guān)鍵字）
5.非葉子結(jié)點(diǎn)的關(guān)鍵字個(gè)數(shù)=指向兒子的指針個(gè)數(shù)-1；
6.非葉子結(jié)點(diǎn)的關(guān)鍵字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非葉子結(jié)點(diǎn)的指針：P[1], P[2], …, P[M]；其中P[1]指向關(guān)鍵字小于K[1]的
子樹，P[M]指向關(guān)鍵字大于K[M-1]的子樹，其它P[i]指向關(guān)鍵字屬于(K[i-1], K[i])的子樹；
8.所有葉子結(jié)點(diǎn)位于同一層；
B樹的特點(diǎn)：
1.關(guān)鍵字集合分布在整顆樹中；
2.任何一個(gè)關(guān)鍵字出現(xiàn)且只出現(xiàn)在一個(gè)結(jié)點(diǎn)中；
3.搜索有可能在非葉子結(jié)點(diǎn)結(jié)束；
4.其搜索性能等價(jià)于在關(guān)鍵字全集內(nèi)做一次二分查找；
5.自動(dòng)層次控制；

1.1.5 B+樹

使用B+樹構(gòu)建索引，B+樹和B樹最主要的區(qū)別在于非葉子節(jié)點(diǎn)是否存儲(chǔ)數(shù)據(jù)的問題。
B+樹：在B-樹基礎(chǔ)上，為葉子結(jié)點(diǎn)增加鏈表指針，所有關(guān)鍵字都在葉子結(jié)點(diǎn)
中出現(xiàn)，非葉子結(jié)點(diǎn)作為葉子結(jié)點(diǎn)的索引；B+樹總是到葉子結(jié)點(diǎn)才命中；

B+的搜索與B-樹也基本相同，區(qū)別是B+樹只有達(dá)到葉子結(jié)點(diǎn)才命中
B+樹的特點(diǎn)：
1.所有關(guān)鍵字都出現(xiàn)在葉子結(jié)點(diǎn)的鏈表中（稠密索引），且鏈表中的關(guān)鍵字恰好
是有序的；
2.不可能在非葉子結(jié)點(diǎn)命中；
3.非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引（稀疏索引），葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)
（關(guān)鍵字）數(shù)據(jù)的數(shù)據(jù)層；
4.更適合文件索引系統(tǒng)；

2.索引的優(yōu)缺點(diǎn)

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

• 提高數(shù)據(jù)的檢索效率，降低數(shù)據(jù)庫的IO成本
• 通過索引對數(shù)據(jù)進(jìn)行排序，降低了數(shù)據(jù)排序的成本，降低了CPU的消耗
• 被索引的列會(huì)自動(dòng)進(jìn)行排序，包括【單列索引】和【組合索引】，只是組合索引的排序要復(fù)雜一些。
• 如果按照索引列的順序進(jìn)行排序，對應(yīng)order by語句來說，效率就會(huì)提高很多

缺點(diǎn)：

• 索引會(huì)占據(jù)磁盤的空間
• 索引雖然會(huì)提高查詢效率，但是會(huì)降低更新表的效率。比如每次對表進(jìn)行增刪改操作，MySQL不僅要保存數(shù)據(jù)，還有保存或者更新對應(yīng)的索引文件。

3.索引的使用場景

哪些情況需要?jiǎng)?chuàng)建索引：
1.主鍵自動(dòng)建立唯一索引
2.頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引(where后面的語句)
3.查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
4.多字段查詢下傾向創(chuàng)建組合索引
5.查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度
6.查詢中統(tǒng)計(jì)或者分組字段

哪些情況不需要?jiǎng)?chuàng)建索引：
1.表記錄太少
2.經(jīng)常增刪改的表
3.where條件里用不到的字段不建立索引

4.索引的分類

4.1 主鍵索引

• 表中的列設(shè)定為主鍵后，數(shù)據(jù)庫會(huì)自動(dòng)建立主鍵索引，索引列中的值必須是唯一的，不允許有空值
• 單獨(dú)創(chuàng)建和刪除主鍵索引的語法
• 創(chuàng)建：alter table 表名 add primary key(字段)
• 刪除：alter table 表名 drop primary key

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

在這個(gè)例子中，id列被定義為主鍵，MySQL會(huì)自動(dòng)為這個(gè)列創(chuàng)建一個(gè)主鍵索引。

4.2 唯一索引

• 表中的列創(chuàng)建了唯一約束時(shí)，數(shù)據(jù)庫會(huì)自動(dòng)建立唯一索引，索引列中的值必須是唯一的，但是允許為空值。
• 單獨(dú)創(chuàng)建和刪除唯一索引的語法：
• 創(chuàng)建：alter table 表名 add unique 索引名(字段)或
  create unique index 索引名 on 表名(字段)
• 刪除：drop index 索引名 on 表名

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX `unique_email` (email)
);

在這里，email列有一個(gè)唯一索引，確保每個(gè)電子郵件地址在表中是唯一的。

4.3 單值索引(單列索引)

一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單值索引。
1.建表時(shí)可隨表一起建立單值索引
2.單獨(dú)創(chuàng)建和刪除單值索引

• 創(chuàng)建：alter table 表名 add index 索引名(字段)
  或 create index 索引名 on 表名(字段)
• 刪除：drop index 索引名 on 表名

4.4 復(fù)合索引(組合索引)

一個(gè)索引包含多個(gè)列
1.建表時(shí)可隨表一起建立復(fù)合索引
2.單獨(dú)創(chuàng)建和刪除復(fù)合索引

• 創(chuàng)建：create index 索引名 on 表名(字段1，字段2)
  或 alter table 表名 add index 索引名(字段1，字段2)
• 刪除：drop index 索引名 on 表名

CREATE INDEX idx_name_date ON events (name, date);

這個(gè)例子創(chuàng)建了一個(gè)組合索引，包含events表的name和date兩個(gè)列，可以加速同時(shí)涉及這兩個(gè)字段的查詢。

4.5 普通索引

最基本的索引類型，沒有唯一性的限制，主要用于提高查詢性能。一個(gè)表可以有多個(gè)普通索引。

CREATE INDEX idx_username ON users (username);

這個(gè)例子為users表的username列創(chuàng)建了一個(gè)普通索引，用來加速基于username的查詢

4.6 全文索引

專門用于全文搜索的索引。只有在MyISAM和InnoDB（從MySQL 5.6版本開始支持）存儲(chǔ)引擎的CHAR、VARCHAR或TEXT類型列上才能創(chuàng)建全文索引。

CREATE FULLTEXT INDEX ft_idx_content ON articles (content);

在這個(gè)例子中，articles表的content列上創(chuàng)建了一個(gè)全文索引，用于全文搜索。

4.7 空間索引

用于空間數(shù)據(jù)類型的索引，如MySQL的地理數(shù)據(jù)類型GEOMETRY。這種索引類型主要用于空間數(shù)據(jù)查詢，只有MyISAM存儲(chǔ)引擎支持空間索引。

CREATE SPATIAL INDEX sp_idx_location ON parks (location);

在這里，parks表的location列是一個(gè)地理數(shù)據(jù)類型，創(chuàng)建了一個(gè)空間索引，用于地理位置查詢。

4.8 前綴索引

對于文本類的長字段，可以創(chuàng)建前綴索引來提高索引效率，只索引字段的前面一部分字符。

CREATE INDEX idx_text_prefix ON articles (content(100));

在這個(gè)例子中，articles表的content列創(chuàng)建了一個(gè)前綴索引，只索引了內(nèi)容的前100個(gè)字符。

4.9 覆蓋索引

如果一個(gè)索引包含了查詢所需的所有數(shù)據(jù)，那么這個(gè)索引被稱為覆蓋索引。查詢可以直接通過索引來獲取數(shù)據(jù)，無需回表查詢。
假設(shè)有以下查詢：

SELECT username, email FROM users WHERE username = 'john_doe'; 

如果有一個(gè)索引：

CREATE INDEX idx_username_email ON users (username, email);

這個(gè)索引就是一個(gè)覆蓋索引，因?yàn)樗瞬樵兯璧乃凶侄蝩sername和email（無論是返回的數(shù)據(jù)還是過濾個(gè)條件都是索引字段），查詢可以直接使用索引來獲取數(shù)據(jù)，不需要訪問表的其他部分。

5.索引測試

create table person(
pid int (11) auto_increment ,
pname varchar(50) ,
psex varchar(10),
page int(11),
sal decimal(7,2),
primary key(pid)
);

6.性能分析

6.1 Mysql常見瓶頸

SQL中對大量數(shù)據(jù)進(jìn)行比較、關(guān)聯(lián)、排序、分組時(shí)CPU的瓶頸。
實(shí)例內(nèi)存滿足不了緩存數(shù)據(jù)或排序等需要，導(dǎo)致產(chǎn)生大量的物理IO，查詢數(shù)據(jù)時(shí)掃描過多數(shù)據(jù)行，導(dǎo)致查詢效率低。

6.2 Explain

使用EXPLAIN關(guān)鍵字可以模擬優(yōu)化器執(zhí)行sQL查詢語句,從而知道MYSQL是如何處理SQL語句的?？梢杂脕矸治霾樵冋Z句或是表的結(jié)構(gòu)的性能瓶頸。其作用:
1.表的讀取順序
2.哪些索引可以使用
3.數(shù)據(jù)讀取操作的操作類型
4.哪些索引被實(shí)際使用
5.表之間的引用
6.每張表有多少行被優(yōu)化器查詢
使用：explain + sql語句

6.3 explain重要字段

6.3.1 id

select查詢的序列號，表示查詢中執(zhí)行select子句或操作表的順序。
id相同時(shí)，執(zhí)行順序由上至下

先從t1表里去，再去t2表里取，最后再去t3表里取

id不同，如果是子查詢，id的序號會(huì)遞增，id值越大，優(yōu)先級越高，則先被執(zhí)行。

id相同和不同都存在時(shí)，id相同的可以理解為一組，從上到下順序執(zhí)行，所有組中，id值越大，優(yōu)先級越高越先執(zhí)行
derived是s3能夠臨時(shí)表里查出的數(shù)據(jù)

6.3.2 select_type

查詢的類型，常見的值有：
SIMPLE:簡單的select查詢，查詢中不包含子查詢或者UNION

PRIMARY：查詢中若包含任何復(fù)雜的子部分，最外查詢則被標(biāo)記為Primary
DERIVED：在FROM列表中包含的子查詢被標(biāo)記為DERIVED(衍生)，Mysql會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里。

最外面的查詢會(huì)被標(biāo)記為primary，子查詢被標(biāo)記為derived

SUBQUERY：在select或where列表中包含了子查詢。

6.3.3 type

訪問類型排序：
all index range ref eq_ref const system
從最好到最差依次是 system > const > eq_ref >ref >range>index > All,一般來說，最好保證查詢能達(dá)到range級別，最好能達(dá)到ref

All：將遍歷全表，以找到匹配的行

System：表里只有一行記錄(等于系統(tǒng)表)，這是const類型的特列，平時(shí)不會(huì)出現(xiàn)，這個(gè)可以忽略不計(jì)。

Const：表示通過索引一次就找到了，const用于比較primary key(主鍵索引) 或者unique(唯一索引)。因?yàn)?strong>只匹配一行數(shù)據(jù)，所以很快，如將主鍵置于where列表中，Mysql就能把該查詢轉(zhuǎn)換成一個(gè)常量

eq_ref:唯一性索引掃描，對于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見于主鍵或者唯一索引掃描(常用在連表查詢)

ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件得行，所以他應(yīng)該屬于查找和掃描得混合體。

range：只檢索給定范圍的行，使用一個(gè)索引來選擇行。key列顯示使用了哪個(gè)索引，一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)，不用掃描全部索引。(使用索引進(jìn)行范圍查詢)

index：index和all的區(qū)別在于index類型只遍歷索引樹。這通常比all快，因?yàn)?strong>索引文件通常比數(shù)據(jù)文件小，也就是說雖然all和index都是讀全表，但是all讀的是存數(shù)據(jù)的表，index讀取的是索引表，all從硬盤中讀取數(shù)據(jù)，index 是從索引中讀取的

6.3.4 possible_key

顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)，查詢涉及到的字段上如果存在索引，則該索引將會(huì)被列出來，但不一定會(huì)被查詢實(shí)際使用上。

6.3.5 key

查詢中實(shí)際使用的索引，如果為null，則表示沒有使用索引。

索引失效

key_len:

使用索引的字節(jié)數(shù)，數(shù)越大 代表使用索引越充分

6.3.6 ref

顯示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值
t3的查詢運(yùn)用到了t2里面id這個(gè)列

6.3.7 rows

rows列顯示MySQL認(rèn)為它執(zhí)行查詢時(shí)必須查詢的行數(shù)，一般越少越好。
刪除索引 進(jìn)行查詢

用索引查詢

6.3.8 extra

一些常見的重要的額外信息：
Using filesort：MySQL無法利用索引完成的排序操作稱為“文件排序”

Using temporary：MySQL在對查詢結(jié)果排序時(shí)使用臨時(shí)表，常見于排序order by 和分組查詢group by

Using index：表示索引被用來執(zhí)行索引鍵值的查找，避免訪問了表的數(shù)據(jù)行，效率不錯(cuò)

Using where：表示使用了where過濾

7.查詢優(yōu)化

7.1 索引失效

1.合最佳左前綴法則：如果索引了多列，要遵循最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
正常符合法則時(shí)：

不符合時(shí)：

2.不在索引列上做任何計(jì)算、函數(shù)操作，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
做函數(shù)操作會(huì)導(dǎo)致失效：

3.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
使用范圍條件后，后面的列的索引會(huì)失效

4.MySQL在使用不等于時(shí)無法使用索引會(huì)導(dǎo)致全表掃描
使用不等于時(shí) 會(huì)導(dǎo)致索引失效

5.is null 可以使用索引，但是is not null 無法使用索引
is null 可以使用素引

is not null 不能使用索引

6.like 以通配符開頭會(huì)使索引失效導(dǎo)致全表掃描
通配符在前面：索引失效

通配符在后面：索引不失效

7.字符串不加單引號，索引會(huì)失效

8.使用or連接時(shí)索引失效

9.數(shù)據(jù)庫和表的字符集統(tǒng)一使用utf8mb4
統(tǒng)一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不同的 字符集 進(jìn)行比較前需要進(jìn)行 轉(zhuǎn)換 會(huì)造成索引失效。

建議：
1.對于單值索引，盡量選擇針對當(dāng)前查詢字段過濾性更好的索引。
2.對于組合索引，當(dāng)前where查詢中過濾性更好的字段在索引字段順序中位置越靠前越好。
3.對于組合索引，盡量選擇能夠包含在當(dāng)前查詢中where子句中更多字段的索引。
4.盡可能通過分析統(tǒng)計(jì)信息和調(diào)整query的寫法來達(dá)到選擇合適索引的目的。

7.2 排序優(yōu)化

1.盡量避免使用Using FileSort方式排序
2.order by 語句使用索引最左前列或者使用where子句與order by 子句條件組合滿足索引最左前列
3.where子句中如果出現(xiàn)索引范圍查詢會(huì)導(dǎo)致order by 索引失效

7.3 優(yōu)化案例

7.3.1 單表查詢優(yōu)化

優(yōu)化前：

優(yōu)化：(建索引，發(fā)現(xiàn)comment是范圍查詢，導(dǎo)致views索引失效)

把comment踢出索引，優(yōu)化成功

7.3.2 關(guān)聯(lián)查詢優(yōu)化

內(nèi)連接時(shí)，mysql.會(huì)自動(dòng)把小結(jié)果集的選為驅(qū)動(dòng)表，所以大表的字段最好加上索引。左外連接時(shí)，左表會(huì)全表掃描，所以右邊大表字段最好加上索引，右外連接同理。我們最好保證被驅(qū)動(dòng)表上的字段建立了索引。
優(yōu)化前：(被驅(qū)動(dòng)表是book)

優(yōu)化后：

7.3.3 分組優(yōu)化

優(yōu)化前：

優(yōu)化后：

8.慢查詢?nèi)罩?/h2>

8.1 簡介

MysQL 的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，他用來記錄在MysQL中響應(yīng)時(shí)間超過閥值的語句，具體指運(yùn)行時(shí)間超過long_query_time值的SQL，則會(huì)被記錄到慢查詢?nèi)罩局小？梢杂伤鼇聿榭茨男﹕QL超出了我們最大忍耐時(shí)間值。

8.2 使用

默認(rèn)情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)罩?，需要手?dòng)設(shè)置參數(shù)：
查看是否開啟：

show variables like '%slow_query_log%'

開啟日志：

set global slow_query_log =1

設(shè)置時(shí)間：

set global long_query_log =1

查看時(shí)間：

show variables like 'long_query_time%'

查看超時(shí)的sql記錄日志：Mysql的數(shù)據(jù)文件夾下：

注意:非調(diào)優(yōu)場景下，一般不建議啟動(dòng)改參數(shù)，慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，開啟慢查詢?nèi)罩緯?huì)或多或少帶來—定的性能影響。

到此這篇關(guān)于MySQL的索引詳細(xì)介紹(全網(wǎng)最詳細(xì)?。?！)的文章就介紹到這了,更多相關(guān)mysql索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論