一、索引概述

• 索引（index） 是幫助 MySQL 高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（有序）

• 在數(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)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。

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

(1) 不同類型的索引結(jié)構(gòu)

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

以下是不同的存儲(chǔ)引擎對(duì)于索引結(jié)構(gòu)的支持情況：

(2) 二叉樹和紅黑樹

• 順序插入時(shí)，會(huì)形成一個(gè)鏈表，查詢性能大大降低。
• 大數(shù)據(jù)量情況下，層級(jí)較深，檢索速度慢

為解決 順序插入時(shí)，會(huì)形成一個(gè)鏈表，查詢性能大大降低 的情況，可以選擇紅黑樹（紅黑樹是一顆自平衡二叉樹，即使是順序插入數(shù)據(jù)，最終形成的數(shù)據(jù)結(jié)構(gòu)也是一顆平衡的二叉樹）

由于紅黑樹也是一顆二叉樹，所以也會(huì)存在大數(shù)據(jù)量情況下，層級(jí)較深，檢索速度慢的缺點(diǎn)。

(3) B 樹

• B 樹是一種多路平衡查找樹，相對(duì)于二叉樹，B樹每個(gè)節(jié)點(diǎn)可以有多個(gè)分支，即多叉。

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

• 5階的B樹，每一個(gè)節(jié)點(diǎn)最多存儲(chǔ)4個(gè)key，對(duì)應(yīng)5個(gè)指針。
• 一旦節(jié)點(diǎn)存儲(chǔ)的key數(shù)量到達(dá)5，就會(huì)裂變，中間元素向上分裂。
• 在B樹中，非葉子節(jié)點(diǎn)和葉子節(jié)點(diǎn)都會(huì)存放數(shù)據(jù)。

(4) B+樹

• B+Tree是B樹的變種

• 綠色框框起來的部分，是索引部分，僅僅起到索引數(shù)據(jù)的作用，不存儲(chǔ)數(shù)據(jù)
• 紅色框框起來的部分，是數(shù)據(jù)存儲(chǔ)部分，在其葉子節(jié)點(diǎn)中要存儲(chǔ)具體的數(shù)據(jù)

• 所有的數(shù)據(jù)都會(huì)出現(xiàn)在葉子節(jié)點(diǎn)
• 葉子節(jié)點(diǎn)形成一個(gè)單向鏈表。
• 非葉子節(jié)點(diǎn)僅僅起到索引數(shù)據(jù)作用，具體的數(shù)據(jù)都是在葉子節(jié)點(diǎn)存放的

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ū)間訪問的性能，利于排序。

(5) Hash

• MySQL中除了支持B+Tree索引，還支持一種索引類型 — Hash索引

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

• Hash索引只能用于對(duì)等比較(=，in)，不支持范圍查詢（between，>，< ，…）
• 無法利用索引完成排序操作
• 查詢效率高，通常(不存在hash沖突的情況)只需要一次檢索就可以了，效率通常要高于B+tree索引

(6) 為什么InnoDB存儲(chǔ)引擎選擇使用B+tree索引結(jié)構(gòu)相對(duì)于二叉樹，層級(jí)更少，搜索效率高；

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

三、索引的分類

(1) 聚集索引和二級(jí)索引

• 在MySQL數(shù)據(jù)庫，將索引的具體類型主要分為以下幾類：主鍵索引、唯一索引、常規(guī)索引、全文索引。
  

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

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

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

(2) 思考題

以下兩條SQL語句，那個(gè)執(zhí)行效率高? 為什么?

A.select * from user where id = 10 ;
B.select * from user where name = 'Arm' ;
# id為主鍵，name字段創(chuàng)建的有索引

• A 語句的執(zhí)行性能要高于B 語句
• 因?yàn)锳語句直接走聚集索引，直接返回?cái)?shù)據(jù)。 而B語句需要先查詢name字段的二級(jí)索引，然后再查詢聚集索引，也就是需要進(jìn)行回表查詢

四、索引 SQL 語法

(1) 表

drop table tb_user;

create table tb_user(
	id int primary key auto_increment comment '主鍵',
	name varchar(50) not null comment '用戶名',
	phone varchar(11) not null comment '手機(jī)號(hào)',
	email varchar(100) comment '郵箱',
	profession varchar(11) comment '專業(yè)',
	age tinyint unsigned comment '年齡',
	gender char(1) comment '性別 , 1: 男, 2: 女',
	status char(1) comment '狀態(tài)',
	createtime datetime comment '創(chuàng)建時(shí)間'
) comment '系統(tǒng)用戶表';


INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('呂布', '17799990000', 'lvbu666@163.com', '軟件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通訊工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('趙云', '17799990002', '17799990@139.com', '英語', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孫悟空', '17799990003', '17799990@sina.com', '工程造價(jià)', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木蘭', '17799990004', '19980729@sina.com', '軟件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大喬', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '應(yīng)用數(shù)學(xué)', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('項(xiàng)羽', '17799990008', 'xiaoyu666@qq.com', '金屬材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '機(jī)械工程及其自動(dòng)化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韓信', '17799990010', 'hanxin520@163.com', '無機(jī)非金屬材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荊軻', '17799990011', 'jingke123@163.com', '會(huì)計(jì)', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('蘭陵王', '17799990012', 'lanlinwang666@126.com', '工程造價(jià)', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂鐵', '17799990013', 'kuangtie@sina.com', '應(yīng)用數(shù)學(xué)', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蟬', '17799990014', '84958948374@qq.com', '軟件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '軟件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('羋月', '17799990016', 'xiaomin2001@sina.com', '工業(yè)經(jīng)濟(jì)', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '國際貿(mào)易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市規(guī)劃', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韋', '17799990020', 'ycaunanjian@163.com', '城市規(guī)劃', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉頗', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市園林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO study_index.tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造價(jià)', 29, '1', '4', '2003-05-26 00:00:00');

(2) 索引 SQL

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

CREATE INDEX idx_user_name ON tb_user ( `name` );

SHOW INDEX FROM tb_user;

??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_profession_age_status ON tb_user ( profession, age, `status` );

??4.為email建立合適的索引來提升查詢效率

CREATE INDEX idx_user_email ON tb_user ( email );

五、SQL 性能分析（查看執(zhí)行頻次）

MySQL 客戶端連接成功后，可通過 show [session|global] status 命令查看服務(wù)器狀態(tài)信息?？刹榭串?dāng)前數(shù)據(jù)庫的 INSERT、UPDATE、DELETE、SELECT 的執(zhí)行頻次

-- session 是查看當(dāng)前會(huì)話 ;
-- global 是查詢?nèi)謹(jǐn)?shù)據(jù) ;
SHOW GLOBAL STATUS LIKE 'Com_______';

假如數(shù)據(jù)庫主要執(zhí)行的是【查詢】，則可為該數(shù)據(jù)庫創(chuàng)建索引，進(jìn)而優(yōu)化查詢。

六、SQL 性能分析（慢查詢?nèi)罩荆?/h2>

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

• MySQL的慢查詢?nèi)罩灸J(rèn)沒有開啟

--- 查看是否開啟了慢查詢?nèi)罩?/span>
SHOW VARIABLES LIKE 'slow_query_log';

---

開啟慢查詢?nèi)罩?，需要在MySQL的配置文件 /etc/my.cnf 中配置如下信息：

Windows 中啟動(dòng)慢查詢?nèi)罩?/font>
// 開啟MySQL慢日志查詢開關(guān)
slow_query_log=1
// 設(shè)置慢日志的時(shí)間為2秒，SQL語句執(zhí)行時(shí)間超過2秒，就會(huì)視為慢查詢，記錄慢查詢?nèi)罩?br> long_query_time=2
slow_query_log_file = “D:\Dev\MySQL\MySQLData\slow_query.txt”
① 一定要記得重啟 MySQL 服務(wù)

配置完畢之后，通過以下指令重新啟動(dòng)MySQL服務(wù)

systemctl restart mysqld

Linux 中可在 /var/lib/mysql/localhost-slow.log 文件中查看慢日志文件中記錄的信息

七、SQL 性能分析（profiles ）

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

# 查看當(dāng)前數(shù)據(jù)庫是否支持 profile
SELECT @@have_profiling;

---

• MySQL是支持 profile操作的，但是開關(guān)是關(guān)閉的。可以通過set語句在session/global級(jí)別開啟profiling

-- 查看每一條 SQL 的耗時(shí)基本情況
show profiles;
-- 查看指定 query_id 的 SQL 語句各個(gè)階段的耗時(shí)情況
show profile for query query_id;
-- 查看指定 query_id 的 SQL 語句 CPU 的使用情況
show profile cpu for query query_id;

八、SQL 性能分析（explain）

EXPLAIN 或者 DESC命令獲取 MySQL 如何執(zhí)行 SELECT 語句的信息【執(zhí)行計(jì)劃】，包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序。

Explain 執(zhí)行計(jì)劃中各個(gè)字段的含義：

九、最左前綴法則和范圍查詢

(1) 最左前綴法則

① 如果索引了多列（聯(lián)合索引），要遵守最左前綴法則

② 最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。

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

④ 如果最左邊的列不存在，索引全部失效

---

---

(2) 范圍查詢

• 聯(lián)合索引中，出現(xiàn)范圍查詢(>,<)，范圍查詢右側(cè)的列索引失效

十、索引失效情況

(1) 索引列運(yùn)算

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

函數(shù)運(yùn)算操作之后，索引失效

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

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

(3) 頭部模糊匹配查詢

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

(4) or 連接的條件

用or分割開的條件， 如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會(huì)被用到。

or 連接的條件必須都有索引，索引才有效

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

如果MySQL評(píng)估使用索引比全表更慢，則不使用索引

十一、索引使用原則

(1) 索引提示

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

(2) 覆蓋索引

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

---

(3) 前綴索引

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

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

單列索引：即一個(gè)索引只包含單個(gè)列
聯(lián)合索引：即一個(gè)索引包含了多個(gè)列

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

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

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

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

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

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

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

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

---

推薦三本書，如下圖所示：

---

共送3本書，請(qǐng)你在評(píng)論區(qū)回復(fù)【ZGQ ~ 我要贈(zèng)書】
我根據(jù)Java程序隨機(jī)選擇2人送書（每人可發(fā)布3個(gè)評(píng)論）
截止時(shí)間：2023/06/15

618，清華社 IT BOOK 多得圖書活動(dòng)開始啦！活動(dòng)時(shí)間為 2023 年 6 月 7 日至 6 月 18 日，清華
社為您精選多款高分好書，涵蓋了 C++、Java、Python、前端、后端、數(shù)據(jù)庫、算法與機(jī)器學(xué)習(xí)等多
個(gè) IT 開發(fā)領(lǐng)域，適合不同層次的讀者。全場(chǎng) 5 折，掃碼領(lǐng)券更有優(yōu)惠哦！快來京東點(diǎn)擊鏈接 IT BOOK 多得
（或掃描京東二維碼）查看詳情吧文章來源地址http://www.zghlxwxcb.cn/news/detail-476659.html

到了這里，關(guān)于【MySQL 數(shù)據(jù)庫】6、一篇文章學(xué)習(xí)【索引知識(shí)】，提高大數(shù)據(jù)量的查詢效率【文末送書】的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！