1.什么是索引

索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)
MySQL在存儲數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)中還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種引用(指向)表中的數(shù)據(jù),這樣我們就可以通過數(shù)據(jù)結(jié)構(gòu)上實現(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)，以鍵值對的方式存儲數(shù)據(jù)，使用Hash表存儲表數(shù)據(jù)Key可以存儲索引列，Value可以存儲行記錄或者行磁盤地址。Hash在等值查詢效率高，時間復(fù)雜度是O(1),但是，不支持范圍快速查找，范圍查找時只能通過掃描全表方式

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

1.1.2 二叉查找樹

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

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

1.1.3 平衡二叉樹

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

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

1.1.4 B樹

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

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

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

1.1.5 B+樹

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

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

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

優(yōu)點：

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

缺點：

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

3.索引的使用場景

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

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

4.索引的分類

4.1 主鍵索引

• 表中的列設(shè)定為主鍵后，數(shù)據(jù)庫會自動建立主鍵索引，索引列中的值必須是唯一的，不允許有空值
• 單獨創(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)
);

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

4.2 唯一索引

• 表中的列創(chuàng)建了唯一約束時，數(shù)據(jù)庫會自動建立唯一索引，索引列中的值必須是唯一的，但是允許為空值。
• 單獨創(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列有一個唯一索引，確保每個電子郵件地址在表中是唯一的。

4.3 單值索引(單列索引)

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

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

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

一個索引包含多個列
1.建表時可隨表一起建立復(fù)合索引
2.單獨創(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);

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

4.5 普通索引

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

CREATE INDEX idx_username ON users (username);

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

4.6 全文索引

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

CREATE FULLTEXT INDEX ft_idx_content ON articles (content);

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

4.7 空間索引

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

CREATE SPATIAL INDEX sp_idx_location ON parks (location);

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

4.8 前綴索引

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

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

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

4.9 覆蓋索引

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

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

如果有一個索引：

CREATE INDEX idx_username_email ON users (username, email);

這個索引就是一個覆蓋索引，因為它包含了查詢所需的所有字段username和email（無論是返回的數(shù)據(jù)還是過濾個條件都是索引字段），查詢可以直接使用索引來獲取數(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ù)進行比較、關(guān)聯(lián)、排序、分組時CPU的瓶頸。
實例內(nèi)存滿足不了緩存數(shù)據(jù)或排序等需要，導(dǎo)致產(chǎn)生大量的物理IO，查詢數(shù)據(jù)時掃描過多數(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.哪些索引被實際使用
5.表之間的引用
6.每張表有多少行被優(yōu)化器查詢
使用：explain + sql語句

6.3 explain重要字段

6.3.1 id

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

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

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

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

6.3.2 select_type

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

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

最外面的查詢會被標(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,一般來說，最好保證查詢能達到range級別，最好能達到ref

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

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

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

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

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

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

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

6.3.4 possible_key

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

6.3.5 key

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

索引失效

key_len:

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

6.3.6 ref

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

6.3.7 rows

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

用索引查詢

6.3.8 extra

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

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

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

Using where：表示使用了where過濾

7.查詢優(yōu)化

7.1 索引失效

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

不符合時：

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

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

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

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

is not null 不能使用索引

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

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

7.字符串不加單引號，索引會失效

8.使用or連接時索引失效

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

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

7.2 排序優(yōu)化

1.盡量避免使用Using FileSort方式排序
2.order by 語句使用索引最左前列或者使用where子句與order by 子句條件組合滿足索引最左前列
3.where子句中如果出現(xiàn)索引范圍查詢會導(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)連接時，mysql.會自動把小結(jié)果集的選為驅(qū)動表，所以大表的字段最好加上索引。左外連接時，左表會全表掃描，所以右邊大表字段最好加上索引，右外連接同理。我們最好保證被驅(qū)動表上的字段建立了索引。
優(yōu)化前：(被驅(qū)動表是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)時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局??？梢杂伤鼇聿榭茨男﹕QL超出了我們最大忍耐時間值。

8.2 使用

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

show variables like '%slow_query_log%'

開啟日志：

set global slow_query_log =1

設(shè)置時間：

set global long_query_log =1

查看時間：

show variables like 'long_query_time%'

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

注意:非調(diào)優(yōu)場景下，一般不建議啟動改參數(shù)，慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，開啟慢查詢?nèi)罩緯蚨嗷蛏賻?mdash;定的性能影響。

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

最新評論