一、存儲(chǔ)引擎

1.1 MySQL體系結(jié)構(gòu)

1). 連接層

最上層是一些客戶端和鏈接服務(wù)，包含本地sock
通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于TCP/IP的通信。主要完成一些類似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念，為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。

2). 服務(wù)層

第二層架構(gòu)主要完成大多數(shù)的核心服務(wù)功能，如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化，部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，如過程、函數(shù)等。在該層，服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹，并對(duì)其完成相應(yīng)的優(yōu)化如確定表的查詢的順序，是否利用索引等，最后生成相應(yīng)的執(zhí)行操作。如果是select語句，服務(wù)器還會(huì)查詢內(nèi)部的緩存，如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。

3). 引擎層

存儲(chǔ)引擎層，存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過API和存儲(chǔ)引擎進(jìn)行通信。不同的存儲(chǔ)引擎具有不同的功能，這樣我們可以根據(jù)自己的需要，來選取合適的存儲(chǔ)引擎。數(shù)據(jù)庫中的索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的。

4). 存儲(chǔ)層

數(shù)據(jù)存儲(chǔ)層，主要是將數(shù)據(jù)(如:redolog、undolog、數(shù)據(jù)、索引、二進(jìn)制日志、錯(cuò)誤日志、查詢?nèi)罩?、慢查詢?nèi)罩镜?存儲(chǔ)在文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。和其他數(shù)據(jù)庫相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲(chǔ)引擎上，插件式的存儲(chǔ)引擎架構(gòu)，將查詢處理和其他的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。

---

1.2 存儲(chǔ)引擎介紹

對(duì)于存儲(chǔ)引擎，它是mysql數(shù)據(jù)庫的核心，我們也需要在合適的場(chǎng)景選擇合適的存儲(chǔ)引擎。接下來就來介紹一下存儲(chǔ)引擎：

存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)、建立索引、更新/查詢數(shù)據(jù)等技術(shù)的實(shí)現(xiàn)方式。存儲(chǔ)引擎是基于表的，而不是基于庫的，所以存儲(chǔ)引擎也可被稱為表類型。我們可以在創(chuàng)建表的時(shí)候，來指定選擇的存儲(chǔ)引擎，如果沒有指定將自動(dòng)選擇默認(rèn)的存儲(chǔ)引擎。

建表時(shí)指定存儲(chǔ)引擎：

CREATE TABLE 表名(
字段1 字段1類型 [ COMMENT 字段1注釋 ] ,
......
字段n 字段n類型 [COMMENT 字段n注釋 ]
) ENGINE = INNODB [ COMMENT 表注釋 ] ;

查詢當(dāng)前數(shù)據(jù)庫支持的存儲(chǔ)引擎：

show engines; 

---

1.3 存儲(chǔ)引擎特點(diǎn)

重點(diǎn)介紹三種存儲(chǔ)引擎InnoDB、MyISAM、Memory的特點(diǎn)。

先來重點(diǎn)看看InnoDB引擎。

1). 介紹
InnoDB是一種兼顧高可靠性和高性能的通用存儲(chǔ)引擎，在 MySQL 5.5 之后，InnoDB是默認(rèn)的MySQL 存儲(chǔ)引擎。

---

2). 特點(diǎn)

• DML操作遵循ACID模型，支持事務(wù)；
  行級(jí)鎖，提高并發(fā)訪問性能；
  支持外鍵FOREIGN KEY約束，保證數(shù)據(jù)的完整性和正確性；

---

3). 文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每張表都會(huì)對(duì)應(yīng)這樣一個(gè)表空間文件，存儲(chǔ)該表的表結(jié)構(gòu)、數(shù)據(jù)和索引。

show variables like 'innodb_file_per_table';#查看是否開啟

如果該參數(shù)開啟，代表對(duì)于InnoDB引擎的表，每一張表都對(duì)應(yīng)一個(gè)ibd文件。 我們直接打開MySQL的數(shù)據(jù)存放目錄： C:\ProgramData\MySQL\MySQL Server 8.0\Data ， 這個(gè)目錄下有很多文件夾，不同的文件夾代表不同的數(shù)據(jù)庫，隨便打開一個(gè)數(shù)據(jù)庫文件夾。

這些文件是基于二進(jìn)制存儲(chǔ)的，不能直接基于記事本打開，我們可以使用mysql提供的一
個(gè)指令ibd2sdi，通過該指令就可以從ibd文件中提取sdi信息，而sdi數(shù)據(jù)字典信息中就包含該表的表結(jié)構(gòu)。

---

4). 邏輯存儲(chǔ)結(jié)構(gòu)

• 表空間 : InnoDB存儲(chǔ)引擎邏輯結(jié)構(gòu)的最高層，ibd文件其實(shí)就是表空間文件，在表空間中可以包含多個(gè)Segment段。
• 段 : 表空間是由各個(gè)段組成的，常見的段有數(shù)據(jù)段、索引段、回滾段等。InnoDB中對(duì)于段的管理，都是引擎自身完成，不需要人為對(duì)其控制，一個(gè)段中包含多個(gè)區(qū)。
• 區(qū) : 區(qū)是表空間的單元結(jié)構(gòu)，每個(gè)區(qū)的大小為1M。 默認(rèn)情況下， InnoDB存儲(chǔ)引擎頁大小為16K， 即一個(gè)區(qū)中一共有64個(gè)連續(xù)的頁。
• 頁 : 頁是組成區(qū)的最小單元，頁也是InnoDB 存儲(chǔ)引擎磁盤管理的最小單元，每個(gè)頁的大小默認(rèn)為16KB。為了保證頁的連續(xù)性，InnoDB 存儲(chǔ)引擎每次從磁盤申請(qǐng) 4-5 個(gè)區(qū)。
• 行 : InnoDB 存儲(chǔ)引擎是面向行的，也就是說數(shù)據(jù)是按行進(jìn)行存放的，在每一行中除了定義表時(shí)所指定的字段以外，還包含兩個(gè)隱藏字段。

---

再來簡(jiǎn)單看看MyISAM引擎。

1). 介紹
MyISAM是MySQL早期的默認(rèn)存儲(chǔ)引擎。

---

2). 特點(diǎn)

• 不支持事務(wù)，不支持外鍵
  支持表鎖，不支持行鎖
  訪問速度快

---

3). 文件
xxx.sdi：存儲(chǔ)表結(jié)構(gòu)信息
xxx.MYD: 存儲(chǔ)數(shù)據(jù)
xxx.MYI: 存儲(chǔ)索引

---

最后簡(jiǎn)單來看看Memory引擎。

1). 介紹
Memory引擎的表數(shù)據(jù)時(shí)存儲(chǔ)在內(nèi)存中的，由于受到硬件問題、或斷電問題的影響，只能將這些表作為臨時(shí)表或緩存使用。

2). 特點(diǎn)
內(nèi)存存放
hash索引（默認(rèn)）

3).文件
xxx.sdi：存儲(chǔ)表結(jié)構(gòu)信息

---

三者的區(qū)別與聯(lián)系：

1.4 存儲(chǔ)引擎選擇

在選擇存儲(chǔ)引擎時(shí)，應(yīng)該根據(jù)應(yīng)用系統(tǒng)的特點(diǎn)選擇合適的存儲(chǔ)引擎。對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實(shí)際情況選擇多種存儲(chǔ)引擎進(jìn)行組合。

• InnoDB: 是Mysql的默認(rèn)存儲(chǔ)引擎，支持事務(wù)、外鍵。如果應(yīng)用對(duì)事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數(shù)據(jù)的一致性，數(shù)據(jù)操作除了插入和查詢之外，還包含很多的更新、刪除操作，那么InnoDB存儲(chǔ)引擎是比較合適的選擇。
• MyISAM：如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對(duì)事務(wù)的完整性、并發(fā)性要求不是很高，那么選擇這個(gè)存儲(chǔ)引擎是非常合適的。
• MEMORY：將所有數(shù)據(jù)保存在內(nèi)存中，訪問速度快，通常用于臨時(shí)表及緩存。MEMORY的缺陷就是對(duì)表的大小有限制，太大的表無法緩存在內(nèi)存中，而且無法保障數(shù)據(jù)的安全性。

---

二、索引

2.1 基本介紹

索引（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)就是索引。

表如上圖所示，假如我們要執(zhí)行的SQL語句為:select * from user where age = 45;在無索引情況下，就需要從第一行開始掃描，一直掃描到最后一行，我們稱之為全表掃描，性能很低。

但如果我們針對(duì)于這張表建立了索引，假設(shè)索引結(jié)構(gòu)就是二叉樹，那么也就意味著，會(huì)對(duì)age這個(gè)字段建立一個(gè)二叉樹的索引結(jié)構(gòu)。

此時(shí)我們?cè)龠M(jìn)行查詢時(shí)，只需要掃描三次就可以找到數(shù)據(jù)了，極大的提高的查詢的效率。

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

---

2.2 索引結(jié)構(gòu)

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

上述是MySQL中所支持的所有的索引結(jié)構(gòu)，接下來，我們?cè)賮砜纯床煌拇鎯?chǔ)引擎對(duì)于索引結(jié)構(gòu)的支持情況。

---

2.2.2：二叉樹

如圖所示，如果選擇二叉樹作為索引結(jié)構(gòu)，會(huì)存在以下缺點(diǎn)：

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

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

---

2.2.3：B-Tree

所以，在MySQL的索引結(jié)構(gòu)中，并沒有選擇二叉樹或者紅黑樹，而選擇的是B+Tree，那么什么是B+Tree呢？在詳解B+Tree之前，先來介紹一個(gè)B-Tree。

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

插入一組數(shù)據(jù)： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88
120 268 250 。

特點(diǎn)：

• 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ù)。

---

2.2.4：B+Tree

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

仍然插入上面的數(shù)據(jù)。

B+Tree 與 B-Tree相比，主要有以下三點(diǎn)區(qū)別：

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

上述我們所看到的結(jié)構(gòu)是標(biāo)準(zhǔn)的B+Tree的數(shù)據(jù)結(jié)構(gòu)，接下來，我們?cè)賮砜纯碝ySQL中優(yōu)化之后的B+Tree。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ū)間訪問的性能，利于排序。

---

2.2.5：Hash
哈希索引就是采用一定的hash算法，將鍵值換算成新的hash值，映射到對(duì)應(yīng)的槽位上，然后存儲(chǔ)在hash表中。如果兩個(gè)(或多個(gè))鍵值，映射到一個(gè)相同的槽位上，他們就產(chǎn)生了hash沖突（也稱為hash碰撞），可以通過鏈表來解決。

特點(diǎn)：

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

存儲(chǔ)引擎支持：

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

---

2.3 索引分類

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

聚集索引&二級(jí)索引:

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

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

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

---

聚集索引和二級(jí)索引的具體結(jié)構(gòu)如下：

由上圖可知：

• 聚集索引的葉子節(jié)點(diǎn)下掛的是這一行的數(shù)據(jù) 。
  二級(jí)索引的葉子節(jié)點(diǎn)下掛的是該字段值對(duì)應(yīng)的主鍵值。

---

接下來，我們來分析一下，當(dāng)我們執(zhí)行如下的SQL語句時(shí)，具體的查找過程是什么樣子的。

具體過程如下:

• 由于是根據(jù)name字段進(jìn)行查詢，所以先根據(jù)name='Arm’到name字段的二級(jí)索引中進(jìn)行匹配查找。但是在二級(jí)索引中只能查找到 Arm 對(duì)應(yīng)的主鍵值 10。
  由于查詢返回的數(shù)據(jù)是*，所以此時(shí)，還需要根據(jù)主鍵值10，到聚集索引中查找10對(duì)應(yīng)的記錄，最終找到10對(duì)應(yīng)的行row。
  最終拿到這一行的數(shù)據(jù)，直接返回即可，這種方式叫做回表查詢。

---

2.4 索引語法

創(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 ;

案例演示：

# name字段為姓名字段，該字段的值可能會(huì)重復(fù)，為該字段創(chuàng)建索引
mysql> create index idx_user_name on tb_user(name);
# phone手機(jī)號(hào)字段的值，是非空，且唯一的，為該字段創(chuàng)建唯一索引
mysql> create unique index idx_user_phone on tb_user(phone);
# 為profession、age、status創(chuàng)建聯(lián)合索引
mysql> create index idx_user_pro_age_sta on tb_user(profession,age,status);
# 為email建立合適的索引來提升查詢效率
mysql> create index idx_email on tb_user(email);
# 查看tb_user表的所有的索引數(shù)據(jù)
mysql> show index from tb_user;

---

2.5 SQL性能分析

2.5.1 SQL執(zhí)行頻率

通過如下指令，可以查看當(dāng)前數(shù)據(jù)庫的INSERT、UPDATE、DELETE、SELECT的訪問頻次：

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

---

2.5.2 慢查詢?nèi)罩?/strong>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過指定參數(shù)（long_query_time，單位：秒，默認(rèn)10秒）的所有SQL語句的日志。
MySQL的慢查詢?nèi)罩?strong>默認(rèn)沒有開啟，我們可以查看一下系統(tǒng)變量 slow_query_log。

# 查看慢查詢?nèi)罩居涗浭欠耖_啟
mysql> show variables like 'slow_query_log';

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

# 開啟MySQL慢日志查詢開關(guān)
slow_query_log=1
# 設(shè)置慢日志的時(shí)間為2秒，SQL語句執(zhí)行時(shí)間超過2秒，就會(huì)視為慢查詢，記錄慢查詢?nèi)罩?/span>
long_query_time=2

開啟后，我們可以通過下面的語句來對(duì)慢查詢進(jìn)行監(jiān)督：

[root@localhost mysql]# cat /var/lib/mysql/localhost-slow.log 
/usr/sbin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
[root@localhost mysql]# tail -f localhost-slow.log 

---

2.5.3 profile詳情

# 查看當(dāng)前MySQL是否支持profile操作：
SELECT @@have_profiling ;
# 查看當(dāng)前profiling的值
SELECT @@profiling ;
# 通過set語句在session/global級(jí)別開啟profiling
SET profiling = 1;

-- 查看每一條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;

---

2.5.4 explain

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

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

字段	含義
id	select查詢的序列號(hào)，表示查詢中執(zhí)行select子句或者是操作表的順序(id相同，執(zhí)行順序從上到下；id不同，值越大，越先執(zhí)行)。
select_type	表示 SELECT 的類型，常見的取值有 SIMPLE（簡(jiǎn)單表，即不使用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個(gè)或者后面的查詢語句）、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 的值越大越好

---

2.6 索引使用

2.6.1 最左前綴法則

如果索引了多列（聯(lián)合索引），要遵守最左前綴法則。最左前綴法則指的是查詢從索引的最左列開始，并且不跳過索引中的列。如果跳躍某一列，索引將會(huì)部分失效(后面的字段索引失效)。

以 tb_user 表為例，我們先來查看一下之前 tb_user 表所創(chuàng)建的索引。

對(duì)于最左前綴法則指的是，查詢時(shí)，最左邊的列，也就是profession必須存在，否則索引全部失效。而且中間不能跳過某一列，否則該列后面的字段索引將失效。

以上的這三組測(cè)試中，我們發(fā)現(xiàn)只要聯(lián)合索引最左邊的字段 profession存在，索引就會(huì)生效，只不過索引的長(zhǎng)度不同。 而且由以上三組測(cè)試，我們也可以推測(cè)出profession字段索引長(zhǎng)度為47、age字段索引長(zhǎng)度為2、status字段索引長(zhǎng)度為5。

---

通過上面的這兩組測(cè)試，我們也可以看到索引并未生效，原因是因?yàn)椴粷M足最左前綴法則，聯(lián)合索引最左邊的列profession不存在。

---

上述的SQL查詢時(shí)，存在profession字段，最左邊的列是存在的，索引滿足最左前綴法則的基本條件。但是查詢時(shí)，跳過了age這個(gè)列，所以后面的列索引是不會(huì)使用的，也就是索引部分生效，所以索引的長(zhǎng)度就是47。

---

當(dāng)查詢字段順序發(fā)生改變時(shí)，我們可以看到，是完全滿足最左前綴法則的，索引長(zhǎng)度54，聯(lián)合索引是生效的。即：最左前綴法則中指的最左邊的列，是指在查詢時(shí)，聯(lián)合索引的最左邊的字段(即是第一個(gè)字段)必須存在，與我們編寫SQL時(shí)，條件編寫的先后順序無關(guān)。

---

2.6.2 范圍查詢

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

當(dāng)范圍查詢使用> 或 < 時(shí)，走聯(lián)合索引了，但是索引的長(zhǎng)度為49，就說明范圍查詢右邊的status字段是沒有走索引的。

---

當(dāng)范圍查詢使用>= 或 <= 時(shí)，走聯(lián)合索引了，但是索引的長(zhǎng)度為54，就說明所有的字段都是走索引的。所以，在業(yè)務(wù)允許的情況下，盡可能的使用類似于 >= 或 <= 這類的范圍查詢，而避免使用 > 或 <。

---

2.6.3 索引失效情況

失效方式一：索引列運(yùn)算

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

---

失效方式二：字符串不加引號(hào)

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

---

失效方式三：模糊查詢

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

經(jīng)過上述的測(cè)試，我們發(fā)現(xiàn)，在like模糊查詢中，在關(guān)鍵字后面加%，索引可以生效。而如果在關(guān)鍵字前面加了%，索引將會(huì)失效。

---

失效方式四：or連接條件

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

由于age沒有索引，所以即使id有索引，索引也會(huì)失效，所以需要針對(duì)于age也要建立索引。

---

失效方式五：數(shù)據(jù)分布影響

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

經(jīng)過測(cè)試我們發(fā)現(xiàn)，相同的SQL語句，只是傳入的字段值不同，最終的執(zhí)行計(jì)劃也完全不一樣，這是為什么呢？就是因?yàn)?strong>MySQL在查詢時(shí)，會(huì)評(píng)估使用索引的效率與走全表掃描的效率，如果走全表掃描更快，則放棄索引，走全表掃描。 因?yàn)樗饕怯脕硭饕倭繑?shù)據(jù)的，如果通過索引查詢返回大批量的數(shù)據(jù)，則還不如走全表掃描來的快，此時(shí)索引就會(huì)失效。

---

2.6.4 SQL提示

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

• use index ： 建議MySQL使用哪一個(gè)索引完成此次查詢（僅僅是建議，mysql內(nèi)部還會(huì)再次進(jìn)行評(píng)估）

explain select * from tb_user use index(idx_user_pro) where profession = '軟件工程';

• ignore index ： 忽略指定的索引

explain select * from tb_user ignore index(idx_user_pro) where profession = '軟件工程';

• force index ： 強(qiáng)制使用索引

explain select * from tb_user force index(idx_user_pro) where profession = '軟件工程';

---

2.6.5 覆蓋索引

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

從上述的執(zhí)行計(jì)劃我們可以看到，這些SQL語句的執(zhí)行計(jì)劃前面所有的指標(biāo)都是一樣的，看不出來差異。但是此時(shí)，我們主要關(guān)注的是后面的Extra，第一條SQL的結(jié)果為 Using where; UsingIndex ; 而后面兩條SQL的結(jié)果為: Using index condition 。

---

2.6.6 前綴索引

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

語法：

create index idx_xxxx on table_name(column(n)) ;

示例：

# 為tb_user表的email字段，建立長(zhǎng)度為5的前綴索引。
create index idx_email_5 on tb_user(email(5)); 

---

前綴長(zhǎng)度：

可以根據(jù)索引的選擇性來決定，而選擇性是指不重復(fù)的索引值（基數(shù)）和數(shù)據(jù)表的記錄總數(shù)的比值，索引選擇性越高則查詢效率越高， 唯一索引的選擇性是1，這是最好的索引選擇性，性能也是最好的。

select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

前綴索引的查詢流程：

---

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

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

在業(yè)務(wù)場(chǎng)景中，如果存在多個(gè)查詢條件，考慮針對(duì)于查詢字段建立索引時(shí)，建議建立聯(lián)合索引，而非單列索引，避免回表查詢。

如果查詢使用的是聯(lián)合索引，具體的結(jié)構(gòu)示意圖如下：

---

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

• 針對(duì)于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引。
• 針對(duì)于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引。
• 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高。
• 如果是字符串類型的字段，字段的長(zhǎng)度較長(zhǎng)，可以針對(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è)索引最有效地用于查詢。