進階

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

Mysql體系結(jié)構(gòu):

• 連接層

  位于最上層,是一些客戶端和連接服務(wù),主要完成一些類似于連接處理,授權(quán)認證及相關(guān)的安全方案。

  服務(wù)器也會為安全接入的每個客戶端驗證它所具有的操作權(quán)限。

• 服務(wù)層

  第二層,主要完成大多數(shù)的核心服務(wù)功能,如sql接口,并完成緩存的查詢,sql的分析和優(yōu)化,部分內(nèi)置函數(shù)的執(zhí)行。

  所有跨存儲引擎的功能也在這一層實現(xiàn),如:過程,函數(shù)等。

• 引擎層

  第三層，存儲引擎真正的負責(zé)了mysql中數(shù)據(jù)的存儲和提取,服務(wù)器通過API和存儲引擎進行通信。

  不同的存儲引擎具有不同的功能,可以根據(jù)自己的需要來選取合適的存儲引擎。

• 存儲層

  最底層,主要是將數(shù)據(jù)存儲在文件系統(tǒng)之上,并完成與存儲引擎的交互。

存儲引擎

簡介

存儲引擎就是存儲數(shù)據(jù),建立索引,更新/查詢數(shù)據(jù)等技術(shù)的實現(xiàn)方式。

存儲引擎是基于表而不是基于庫的，所以存儲引擎也可以被稱為表引擎。

在 MySQL 5.5 之后，默認的存儲引擎是InnoDB。

相關(guān)操作:

-- 查詢建表語句(以account表為例)
show create table account;
-- 建表時指定存儲引擎
CREATE TABLE 表名(
    ...
) ENGINE=INNODB;
-- 查看當(dāng)前數(shù)據(jù)庫支持的存儲引擎
show engines;

InnoDB

InnoDB 是一種兼顧高可靠性和高性能的通用存儲引擎，在 MySQL 5.5 之后，InnoDB 是默認的 MySQL 引擎。

特點：

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

文件：

• xxx.ibd: xxx代表表名，使用InnoDB 作為引擎的數(shù)據(jù)表都會對應(yīng)這樣一個表空間文件，其存儲對應(yīng)數(shù)據(jù)表的表結(jié)構(gòu)（frm、sdi）、數(shù)據(jù)和索引。

參數(shù)：

innodb_file_per_table，決定多張表共享一個表空間還是每張表對應(yīng)一個表空間

知識點：

查看 Mysql 變量：
show variables like 'innodb_file_per_table';

從ibd文件提取表結(jié)構(gòu)數(shù)據(jù)：
（在cmd運行）

ibd2sdi xxx.ibd

InnoDB 邏輯存儲結(jié)構(gòu)：

MyISAM

MyISAM 是 MySQL 早期的默認存儲引擎。

特點：

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

文件：

• xxx.sdi: 存儲表結(jié)構(gòu)信息
• xxx.MYD: 存儲數(shù)據(jù)
• xxx.MYI: 存儲索引

Memory

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

特點：

• 存放在內(nèi)存中，速度快
• hash索引（默認）

文件：

• xxx.sdi: 存儲表結(jié)構(gòu)信息

存儲引擎對比

存儲引擎的選擇

在選擇存儲引擎時，應(yīng)該根據(jù)系統(tǒng)的特點選擇合適的存儲引擎。

對于復(fù)雜的應(yīng)用系統(tǒng)，還可以根據(jù)實際情況選擇多種存儲引擎進行組合。

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

如: 電商中的足跡和評論適合使用 MyISAM 引擎，緩存適合使用 Memory 引擎。

性能分析

查看執(zhí)行頻次

查看當(dāng)前數(shù)據(jù)庫的 INSERT, UPDATE, DELETE, SELECT 等語句的訪問頻次：
SHOW GLOBAL STATUS LIKE 'Com_______';

或者

SHOW SESSION STATUS LIKE 'Com_______';

例子：

show global status like 'Com_______'

慢查詢?nèi)罩?/h4>

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

日志文件位置：/var/lib/mysql/localhost-slow.log

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

(更改后記得重啟MySQL服務(wù))

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

查看慢查詢?nèi)罩鹃_關(guān)狀態(tài)：
show variables like 'slow_query_log';

profile

show profile 能在做SQL優(yōu)化時幫我們了解時間都耗費在哪里。

通過 have_profiling 參數(shù)，能看到當(dāng)前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
profiling 默認關(guān)閉，可以通過set語句在session/global級別開啟 profiling：
SET profiling = 1;
查看所有語句的耗時：
show profiles;
查看指定query_id的SQL語句各個階段的耗時：
show profile for query query_id;
查看指定query_id的SQL語句CPU的使用情況
show profile cpu for query query_id;

explain

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

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

例子:

EXPLAIN 結(jié)果的各字段含義：

• id：select 查詢的序列號，這是查詢中每個操作的唯一標(biāo)識符

  表示查詢中執(zhí)行 select 子句或者操作表的順序（id相同，執(zhí)行順序從上到下；id不同，值越大越先執(zhí)行）

• select_type：表示 SELECT 的類型，常見取值有 SIMPLE（簡單表，即不適用表連接或者子查詢）、PRIMARY（主查詢，即外層的查詢）、UNION（UNION中的第二個或者后面的查詢語句）、SUBQUERY（SELECT/WHERE之后包含了子查詢）等

• table:顯示操作涉及的表的名稱

• partitions:表示查詢操作所涉及的分區(qū)

• type：表示連接類型，指示數(shù)據(jù)庫引擎在表之間進行連接的方式.

  性能由好到差的連接類型為 NULL、system、const(表中只有一行匹配，主鍵或唯一索引的等值查詢)、eq_ref(使用唯一索引進行查找)、ref(使用非唯一索引進行查找)、range(使用索引進行范圍掃描)、index(全索引掃描[索引樹])、all(全表掃描[磁盤])

  

• possible_keys：可能應(yīng)用在這張表上的索引，一個或多個，如果為 NULL，則沒有使用索引

• Key：實際使用的索引，如果為 NULL，則沒有使用索引

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

• rows：MySQL估計要檢索的行數(shù)，在InnoDB引擎的表中，是一個估計值，可能并不總是準(zhǔn)確的

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

• Extra: 表示額外的信息。

  一些常見的值有：

  • Using where: 表示使用了WHERE子句進行過濾。
  • Using index: 表示使用了索引覆蓋查詢，數(shù)據(jù)直接從索引中獲取而無需訪問表。
  • Using temporary: 表示使用了臨時表來存儲中間結(jié)果。
  • Using filesort: 表示使用了文件排序。

索引

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

在數(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)就是索引。

個人理解: 數(shù)據(jù)表中的某個/某些字段會被選出來作為B+樹節(jié)點的索引值,從而快速找到相應(yīng)的行數(shù)據(jù)。

? 其中具有多個列的索引稱為聯(lián)合索引或復(fù)合索引。

優(yōu)點：

• 提高數(shù)據(jù)檢索效率，降低數(shù)據(jù)庫的IO成本
• 通過索引列對數(shù)據(jù)進行排序，降低數(shù)據(jù)排序的成本，降低CPU的消耗

缺點：

• 索引列也是要占用空間的
• 索引大大提高了查詢效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

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

B Tree

發(fā)展歷程:

二叉樹【左小右大】:

二叉樹的缺點可以用紅黑樹來解決：

紅黑樹也存在大數(shù)據(jù)量情況下，層級較深，檢索速度慢的問題。

為了解決上述問題，可以使用 B-Tree 結(jié)構(gòu)。
B-Tree (多路平衡查找樹) 以一棵最大度數(shù)（max-degree，指一個節(jié)點的子節(jié)點個數(shù)）為5（5階）的 b-tree 為例（5階意味著每個節(jié)點最多存儲4個key，有5個指針,5個子節(jié)點）

【無論是葉子節(jié)點還是非葉子節(jié)點，都會保存數(shù)據(jù)】

B+ Tree

不是每個節(jié)點都有數(shù)據(jù),數(shù)據(jù)存儲在葉子節(jié)點的單向鏈表中。

與 B Tree 的區(qū)別：

• 所有的數(shù)據(jù)都會出現(xiàn)在葉子節(jié)點
• 葉子節(jié)點形成一個單向鏈表

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

MySQL 索引數(shù)據(jù)結(jié)構(gòu)對經(jīng)典的 B+Tree 進行了優(yōu)化。

在原 B+Tree 的基礎(chǔ)上，增加一個指向相鄰葉子節(jié)點的鏈表指針，就形成了帶有順序指針的 B+Tree，提高區(qū)間訪問的性能。

Hash

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

特點：

• Hash索引只能用于對等比較（=、in），不支持范圍查詢（betwwn、>、<、…）
• 無法利用索引完成排序操作
• 查詢效率高，通常只需要一次檢索就可以了，效率通常要高于 B+Tree 索引

存儲引擎支持：

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

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

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

索引分類

常規(guī)分類

InnoDB中的分類

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

主鍵索引也被稱為聚簇索引（clustered index），其余都稱呼為非聚集索引也被稱為二級索引（secondary index）

上面所講的聚集索引和二級索引的概念主要是在InnoDB中討論的！

MyISAM也支持聚集索引，但它將表的數(shù)據(jù)和索引分開存儲，而不像InnoDB那樣聚集在一起。在MyISAM中，表的數(shù)據(jù)存儲在一個文件中，而索引存儲在另一個文件中。

Memory存儲引擎將所有數(shù)據(jù)存儲在內(nèi)存中，不會在磁盤上保留永久的存儲結(jié)構(gòu)。因此，關(guān)于聚集索引和二級索引的概念在Memory存儲引擎中可能不太適用。

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

在InnoDB中，只存在一個聚集索引，而聚集索引的選取規(guī)則：

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

演示圖：

在如下的演示圖中,針對主鍵id字段建立了聚集索引,針對name字段建立了普通索引(二級索引),

每一個索引在 InnoDB 里面對應(yīng)一棵B+樹，那么此時就存在著兩棵B+樹:【一個聚集索引的B+樹,一個二級索引的B+樹】

舉例圖示:

上圖的流程為:

1. 在name索引樹上找到名稱為Arm的節(jié)點 ,主鍵id為10
2. 從id索引樹上找到id為10的節(jié)點, 獲取對應(yīng)的行數(shù)據(jù)
3. 從行數(shù)據(jù)中獲取相應(yīng)字段數(shù)據(jù)的值

---

在流程中從非主鍵索引樹搜索回到主鍵索引樹搜索的過程稱為：回表，在本次查詢中因為查詢結(jié)果只存在主鍵索引樹中，我們必須回表才能查詢到結(jié)果。

再舉例:

以下圖為例，假設(shè)現(xiàn)在有一個表，存在id、name、age三個字段，其中id為主鍵，因此id為聚集索引，name建立索引為非聚集索引。關(guān)于id和name的索引，有如下的B+樹，可以看到，聚集索引的葉子節(jié)點存儲的是主鍵和行記錄，非聚集索引的葉子節(jié)點存儲的是主鍵。

回表查詢:

從上面的索引存儲結(jié)構(gòu)來看，我們可以看到，在主鍵索引樹上，通過主鍵就可以一次性查出我們所需要的數(shù)據(jù)，速度很快。這很直觀，因為主鍵就和行記錄存儲在一起，定位到了主鍵就定位到了所要找的包含所有字段的記錄。

但是對于非聚集索引，如上面的右圖，我們可以看到，需要先根據(jù)name所在的索引樹找到對應(yīng)主鍵，然后再一次通過主鍵索引樹查詢到所要的記錄(行數(shù)據(jù))，這個過程叫做回表查詢。

所以盡量不要用select *，容易出現(xiàn)回表查詢，降低效率，除非有聯(lián)合索引包含了所有字段

索引覆蓋:

上面的回表查詢無疑會降低查詢的效率，那么有沒有辦法讓它不回表呢？這就是索引覆蓋。

索引覆蓋是指一個查詢的結(jié)果可以完全通過索引來獲取，而不需要訪問實際的數(shù)據(jù)行，即從非主鍵索引中就能查到的記錄，而不需要查詢主鍵索引中的記錄，避免了回表的產(chǎn)生減少了樹的搜索次數(shù)，顯著提升性能,這樣就避免了回表。

當(dāng)一個查詢所有需要的列都包含在索引中時，可以說這個查詢是通過索引覆蓋的。

通俗來說，就是數(shù)據(jù)庫引擎不需要去實際的數(shù)據(jù)表中讀取數(shù)據(jù)行，而是直接通過索引就能夠得到查詢的結(jié)果，從而提高查詢的性能。

舉個例子:

假設(shè)有一張數(shù)據(jù)表（id，name,age）,其中id為主鍵(對應(yīng)了聚集索引),并插入部分數(shù)據(jù),此時數(shù)據(jù)表所含數(shù)據(jù)如下：

然后我們以name和age兩個字段建立聯(lián)合索引,對應(yīng)的B+索引樹如下:

此時如果執(zhí)行如下sql:

SELECT age FROM student WHERE name = '小李'；

那么此時的流程為:

1. 在name,age聯(lián)合索引樹上找到名稱為小李的節(jié)點
2. 此時節(jié)點索引里包含信息age 直接返回 12即可【不需回表】

注意:

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

2.如何確定數(shù)據(jù)庫成功使用了覆蓋索引呢?

答: 當(dāng)發(fā)起一個索引覆蓋查詢時，在explain 語句的extra列可以看到using index的信息。

思考題

1. 在InnoDB存儲引擎中,以下 SQL 語句，哪個執(zhí)行效率高？為什么？

-- 備注：id為主鍵，name字段創(chuàng)建的有索引
--      所以id字段對應(yīng)的是聚集索引,name字段上創(chuàng)建的索引是二級索引
select * from user where id = 10;select * from user where name = 'Arm';

答：第一條語句，因為第二條需要回表查詢，相當(dāng)于兩個步驟,所以比第一句慢。

2. InnoDB 主鍵索引的 B+Tree 高度為多少？

答：假設(shè)B+樹的每個節(jié)點大小為一頁且為16KB,數(shù)據(jù)表的一行數(shù)據(jù)大小為1kB，一頁中可以存儲16行這樣的數(shù)據(jù)(即每個B+節(jié)點可存儲16個行數(shù)據(jù))。且假設(shè)InnoDB 的指針占用6個字節(jié)的空間，主鍵字段假設(shè)為bigint，占用字節(jié)數(shù)為8.
可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字節(jié)數(shù)，n 表示當(dāng)前B+樹節(jié)點存儲的主鍵key的數(shù)量，

(n + 1) 表示指針數(shù)量（比key多一個）。算出n約為1170。

如果樹的高度為2（最下一層葉子節(jié)點的數(shù)量為1171），那么他能存儲的數(shù)據(jù)量(數(shù)據(jù)行數(shù))大概為：1171 * 16 = 18736；
如果樹的高度為3（最下一層葉子節(jié)點的數(shù)量為1171 *1171），那么他能存儲的數(shù)據(jù)量(數(shù)據(jù)行數(shù))大概為：1171 * 1171 * 16 = 21939856。另外，如果有成千上萬的數(shù)據(jù)，那么就要考慮分表(避免樹的層級較深，檢索速度慢的問題)，涉及運維篇知識。

3. 面試題：一張表，有四個字段（id, username, password, status），由于數(shù)據(jù)量大，需要對以下SQL語句進行優(yōu)化，該如何進行才是最優(yōu)方案：
   select id, username, password from tb_user where username='itcast';

答：給username和password字段建立聯(lián)合索引，則不需要回表查詢，直接覆蓋索引（索引覆蓋）。

單列索引和聯(lián)合索引

單列索引

單列索引：即一個索引只包含單個列。

創(chuàng)建語法：

CREATE INDEX index_name ON table_name (column_name);

示例： 假設(shè)有一個表 employees 包含列 employee_id、first_name、last_name，如果我們?yōu)?first_name 列創(chuàng)建單列索引：

CREATE INDEX idx_first_name ON employees (first_name);

這將創(chuàng)建一個名為 idx_first_name 的單列索引，用于加速對 first_name 列的查詢。

注意:

• 適用于針對單個列的查詢。
• 簡單，占用較少的存儲空間。

聯(lián)合索引(復(fù)合索引)

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

索引的底層是一顆B+樹，那么聯(lián)合索引的底層也是一顆B+樹，只不過聯(lián)合索引的B+樹節(jié)點中存儲的是鍵值。

且由于構(gòu)建一棵B+樹只能根據(jù)一個值來確定索引關(guān)系，所以數(shù)據(jù)庫依賴聯(lián)合索引的最左的字段來構(gòu)建。

舉例：創(chuàng)建一個（a,b）的聯(lián)合索引，那么它的索引樹就是下圖的樣子。

可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。但是我們又可發(fā)現(xiàn)a在等值的情況下，b值又是按順序排列的，但是這種順序是相對的。這是因為MySQL創(chuàng)建聯(lián)合索引的規(guī)則是首先會對聯(lián)合索引的最左邊第一個字段排序，在第一個字段的排序基礎(chǔ)上，然后在對第二個字段進行排序。

創(chuàng)建語法：

CREATE INDEX index_name ON table_name (column1, column2, ...);

示例： 假設(shè)我們?yōu)?employees 表的 first_name 和 last_name 列創(chuàng)建聯(lián)合索引：

CREATE INDEX idx_firstname_lastname ON employees (first_name, last_name);

這將創(chuàng)建一個名為 idx_firstname_lastname 的聯(lián)合索引，用于加速對 first_name 和 last_name 列的組合查詢。

注意:

• 適用于涉及多個列的查詢，尤其是在涉及這些列的組合查詢時。

• 多列的組合查詢可能會更高效。

• 由于最左前綴原則，在創(chuàng)建聯(lián)合索引時，索引字段的順序需要考慮字段值去重之后的個數(shù)，較多的放前面。

  ORDER BY子句也遵循此規(guī)則。

• 建立了一個聯(lián)合索引，比如(col1,col2,col3),則其等價于/相當(dāng)于建立了如下的幾個索引: (col1),(col1,col2),(col1,col2,col3)!!

注意事項

• 在業(yè)務(wù)場景中，如果存在多個查詢條件，考慮針對于查詢字段建立索引時，建議建立聯(lián)合索引，而非單列索引。
• 多條件聯(lián)合查詢時，MySQL優(yōu)化器會評估哪個字段的索引效率更高，會選擇該索引完成本次查詢

前綴索引

前綴索引是一種索引類型，它并不包含整個列的值，而只包含列值的前綴部分。

這種索引可以用來減少索引的存儲空間，提高查詢性能，特別是在對長文本列或大字符串進行索引時。

創(chuàng)建前綴索引的語法

CREATE INDEX index_name ON table_name (column_name(length));

其中，length 是指定前綴長度的參數(shù)。通過指定 length，你可以選擇索引列值的前幾個字符，而不是整個列的值。

示例：

假設(shè)有一個表 products 包含一個長文本列 description，我們希望對這個列創(chuàng)建前綴索引：

CREATE INDEX idx_description_prefix ON products (description(100));

在這個例子中，idx_description_prefix 是一個前綴索引，只包含 description 列值的前100個字符。

這樣的索引可以提高查詢性能，同時占用更少的存儲空間。

為什么使用前綴索引？

1. 節(jié)省存儲空間： 長文本列或大字符串的完整索引可能會占用大量存儲空間，使用前綴索引可以減少索引的大小，提高存儲效率。
2. 提高查詢性能： 在某些場景下，只需使用列值的前綴部分進行查詢，而不需要完整的列值。使用前綴索引可以減少索引樹的深度，提高查詢性能。

注意事項：

1. 選擇合適的前綴長度： 前綴長度的選擇需要權(quán)衡存儲空間和查詢性能。太短的前綴可能導(dǎo)致索引的選擇性下降，而太長可能會失去減小存儲空間的效果。
2. 僅在需要時使用： 不是所有的列都適合使用前綴索引，只有在查詢中確實需要使用列的前綴部分時才使用前綴索引。

總的來說，前綴索引是一種用于優(yōu)化存儲和查詢性能的技術(shù)，特別適用于對大字符串或長文本列進行索引的場景。

索引相關(guān)sql

在創(chuàng)建表的同時,創(chuàng)建索引:

1.創(chuàng)建主鍵索引:

-- 例子中，通過將 PRIMARY KEY 關(guān)鍵字放在列定義上，創(chuàng)建了名為 id 的主鍵索引。
CREATE TABLE your_table (
 id INT PRIMARY KEY,
 name VARCHAR(50),
 age INT
);

2.創(chuàng)建唯一索引:

-- 例子中，通過在列定義上使用 UNIQUE 關(guān)鍵字，創(chuàng)建了名為 username 的唯一索引。
CREATE TABLE your_table (
 username VARCHAR(50) UNIQUE,
 email VARCHAR(100),
 age INT
);

3.創(chuàng)建普通索引

-- 寫法1:通過在列定義后面使用 INDEX 關(guān)鍵字，創(chuàng)建了名為 idx_category 的普通索引。
CREATE TABLE your_table (
 category_id INT,
 product_name VARCHAR(100),
 price DECIMAL(10, 2),
 INDEX idx_category (category_id)
);

-- 寫法2::通過在列定義后面使用 Key 關(guān)鍵字，創(chuàng)建了名為 idx_example 的普通索引。
CREATE TABLE your_table (
 column1 INT,
 column2 VARCHAR(50),
 KEY idx_example (column1, column2)
);

--實際上，這兩個關(guān)鍵字在這個語境中是可以互換使用的，都表示創(chuàng)建普通索引。選擇使用哪一個關(guān)鍵字通常是個人或團隊的偏好問題，對于MySQL來說，它們是等效的。

已有數(shù)據(jù)表后,創(chuàng)建索引：

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

如果不CREATE 后面不加索引類型參數(shù)，則創(chuàng)建的是常規(guī)索引（默認）

---

1. 常規(guī)索引（默認）： 如果你不指定任何特殊類型，直接創(chuàng)建索引，那么就是常規(guī)索引。這種索引允許表中存在相同的索引值，適用于一般的查詢需求。

   CREATE INDEX index_name ON table_name (index_col_name, ...);
   

2. 唯一索引： 如果你希望索引列的值在整個表中是唯一的，不允許重復(fù)，你可以創(chuàng)建唯一索引。這對于確保數(shù)據(jù)完整性很有用。

   CREATE UNIQUE INDEX unique_index_name ON table_name (index_col_name, ...);
   

3. 全文索引： 如果你需要在文本數(shù)據(jù)中進行全文本的搜索和匹配，你可以創(chuàng)建全文索引。

   CREATE FULLTEXT INDEX fulltext_index_name ON table_name (index_col_name, ...);
   

區(qū)別：

• 常規(guī)索引允許索引列字段出現(xiàn)重復(fù)值，唯一索引不允許索引列字段出現(xiàn)重復(fù)值。
• 全文索引用于文本數(shù)據(jù)字段的全文搜索。

查看索引：

SHOW INDEX FROM table_name;

刪除索引：

DROP INDEX index_name ON table_name;

案例：

-- name字段為姓名字段，該字段的值可能會重復(fù)，為該字段創(chuàng)建索引
create index idx_user_name on tb_user(name);
-- phone手機號字段的值非空，且唯一，為該字段創(chuàng)建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 為profession, age, status創(chuàng)建聯(lián)合索引(多個字段作為索引)
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 為email建立合適的索引來提升查詢效率
create index idx_user_email on tb_user(email);
-- 刪除索引
drop index idx_user_email on tb_user;

索引失效情況

1. 在索引列上進行運算操作，索引將失效。如：explain select * from tb_user where substring(phone, 10, 2) = '15';
2. 字符串類型字段使用時，不加引號（推薦單引號），索引將失效。如：explain select * from tb_user where phone = 17799990015;，此處phone的值沒有加引號
3. 模糊查詢中，如果僅僅是尾部模糊匹配，索引不會是失效；如果是頭部模糊匹配，索引失效。如：explain select * from tb_user where profession like '%工程';，前后都有 % 也會失效。
4. 用 or 分割開的條件，如果 or 其中一個條件的列沒有索引，那么涉及的索引都不會被用到。
5. 如果 MySQL 評估使用索引比全表更慢，則不使用索引。

索引設(shè)計原則

1. 針對于數(shù)據(jù)量較大，且查詢比較頻繁的表建立索引
2. 針對于常作為查詢條件（where）、排序（order by）、分組（group by）操作的字段建立索引
3. 盡量選擇區(qū)分度高的列作為索引，盡量建立唯一索引，區(qū)分度越高，使用索引的效率越高
4. 如果是字符串類型的字段，字段長度較長，可以針對于字段的特點，建立前綴索引
5. 盡量使用聯(lián)合索引，減少單列索引，查詢時，聯(lián)合索引很多時候可以覆蓋索引，節(jié)省存儲空間，避免回表，提高查詢效率
6. 要控制索引的數(shù)量，索引并不是多多益善，索引越多，維護索引結(jié)構(gòu)的代價就越大，會影響增刪改的效率
7. 如果索引列不能存儲NULL值，請在創(chuàng)建表時使用NOT NULL約束它。當(dāng)優(yōu)化器知道每列是否包含NULL值時，它可以更好地確定哪個索引最有效地用于查詢

索引使用原則/規(guī)則

在設(shè)計查詢的sql語句時需要注意一些原則，以充分利用索引來提高查詢性能。

聯(lián)合索引最左前綴法則

首先要知道，最左匹配原則都是針對聯(lián)合索引來說的，有必要了解一下聯(lián)合索引的原理。

了解了聯(lián)合索引，那么為什么會有最左匹配原則這種說法也就理解了。

我們都知道索引的底層是一顆B+樹，那么聯(lián)合索引當(dāng)然也是一顆B+樹，只不過聯(lián)合索引的健值數(shù)量不是一個，而是多個。

而構(gòu)建一顆B+樹只能根據(jù)一個值來構(gòu)建，因此數(shù)據(jù)庫依據(jù)聯(lián)合索引中最左的字段來構(gòu)建B+樹。

例子：

假如創(chuàng)建一個（a,b)的聯(lián)合索引，那么它的索引樹是這樣的:

可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。所以b = 2這種查詢條件沒有辦法利用索引，因為聯(lián)合索引首先是按a排序的，而跳過了a,單獨一個b是無序的?！?strong>這就對應(yīng)了最左前綴法則的第一部分內(nèi)容,下面會講】

同時我們還可以發(fā)現(xiàn)在a值相等的情況下，b值又是按順序排列的，但是這種順序是相對有序的,不是絕對有序的。所以最左匹配原則遇上范圍查詢就會停止，剩下的字段都無法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因為在a值確定的情況下b是相對有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因為a的值是一個范圍(不是一個值)，在這個范圍中b是無序的。但可以用>=或者<=來規(guī)避索引失效問題，因為=號會讓a出現(xiàn)一個定值,a只要是定值,那么b就是相對有序的了。

【這就對應(yīng)了最左前綴法則的第二部分內(nèi)容,下面會講】

如果創(chuàng)建了聯(lián)合索引,則在書寫sql查詢語句時，要遵守最左前綴法則！它包括兩部分內(nèi)容:

第一部分:

• sql查詢語句的查詢條件需從聯(lián)合索引的最左列開始，并且不跳過聯(lián)合索引中的列,如果跳躍某一列，聯(lián)合索引將部分失效（后面的字段索引失效）。換句話說: 即如果查詢的時候查詢條件精確匹配索引字段從最左邊開始的連續(xù)一列或幾列，則此索引生效。

• 舉例:

  假設(shè)有一個聯(lián)合索引 (col1, col2)。最左前綴法則指的是，如果你想要充分利用這個索引，查詢條件必須從最左邊的列開始，并且不跳過中間的列。如果跳過了某一列，那么索引就會部分失效(因為此時后面的字段是無序的,只有未跳過之前的列是有序)。

  -- 良好的使用方式（可以命中索引）
  SELECT * FROM your_table WHERE col1 = 'value1';
  
  -- 良好的使用方式（可以命中索引）
  -- 這里需要注意的是，查詢的時候如果兩個條件都用上了，但是順序不同，如 col2= xx and col1 ＝xx，那么現(xiàn)在的查詢引擎會自動優(yōu)化為匹配聯(lián)合索引的順序，這樣是能夠命中索引的。
  SELECT * FROM your_table WHERE col1 = 'value1' AND col2 = 'value2';
  
  -- 不良的使用方式（最左前綴法則失效）
  -- 。這是因為MySQL創(chuàng)建聯(lián)合索引的規(guī)則是首先會對聯(lián)合索引的最左邊第一個字段排序，在第一個字段的排序基礎(chǔ)上，然后在對第二個字段進行排序。所以col2=xx這種查詢條件沒有辦法利用索引。
  SELECT * FROM your_table WHERE col2 = 'value2';
  

  在第一個例子中，查詢條件從 (col1, col2) 的最左列開始，索引能夠被充分利用。

  而在第二個例子中，查詢條件跳過了 col1，導(dǎo)致 (col1, col2) 聯(lián)合索引最左前綴法則失效。

• 注意:

  • 查詢的時候如果聯(lián)合索引的多個字段都用上了，但是順序不同，如 col2= xx and col1 ＝xx，那么現(xiàn)在的查詢引擎會自動優(yōu)化為匹配聯(lián)合索引的順序，這樣是能夠命中索引的。

第二部分:

• sql查詢語句的查詢條件在進行范圍查詢時，如果聯(lián)合索引中的某一列字段出現(xiàn)了 < >,between,like等，則該列字段右側(cè)所有索引字段失效?！?strong>但可以用>=或者<=來規(guī)避索引失效問題】

• 舉例:

  假設(shè)有一個聯(lián)合索引(a,b,c,d),此時有如下的sql語句:

  select * from your_table where a=1 and b=2 and c>3 and d=4;
  

  那么d是用不到索引的，因為c字段是一個范圍查詢，它之后的字段會停止匹配索引。

避免select *

盡量不要用select *，容易出現(xiàn)回表查詢，降低效率，除非有聯(lián)合索引包含了所有字段

SQL提示

SQL提示是優(yōu)化數(shù)據(jù)庫的一個重要手段，簡單來說，就是在SQL語句中加入一些人為的提示從而傳遞給數(shù)據(jù)庫來達到優(yōu)化操作的目的。

如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession="軟件工程";
不使用哪個索引：
explain select * from tb_user ignore index(idx_user_pro) where profession="軟件工程";
必須使用哪個索引：
explain select * from tb_user force index(idx_user_pro) where profession="軟件工程";

use 是建議，實際使用哪個索引 MySQL 還會自己權(quán)衡運行速度去更改，force就是無論如何都強制使用該索引。

sql優(yōu)化

主鍵優(yōu)化

在InnoDB存儲引擎中，表數(shù)據(jù)都是根據(jù)主鍵順序組織存放的，這種存儲方式的表稱為索引組織表（Index organized table, IOT）

1. 合理選擇主鍵（主鍵應(yīng)具有唯一性）

   主鍵列的值應(yīng)該具有唯一性，確保每一行都能被唯一標(biāo)識。

   CREATE TABLE your_table (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(50),
       ...
   );
   

2. 避免過長的主鍵

   避免使用過長的主鍵，因為主鍵通常會被用作其他表的外鍵，較長的主鍵可能導(dǎo)致索引的大小增加，從而影響查詢性能。

   CREATE TABLE your_table (
       user_id INT PRIMARY KEY,  -- 避免使用過長的主鍵
       name VARCHAR(50),
       ...
   );
   

3. 利用自增主鍵

   插入數(shù)據(jù)時，盡量選擇順序插入，選擇使用 AUTO_INCREMENT 自增主鍵

   自增主鍵通?？梢蕴岣卟迦胄阅埽⒋_保新插入的數(shù)據(jù)總是添加到索引的末尾。

   CREATE TABLE your_table (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(50),
       ...
   );
   

4. 合理使用聚集索引(指定存儲引擎為InnoDB)

   在InnoDB等支持聚集索引的數(shù)據(jù)庫中，主鍵通常是聚簇索引，這意味著行數(shù)據(jù)的物理存儲順序與主鍵的順序相同。

   這有助于提高范圍查詢的性能。

   CREATE TABLE your_table (
       id INT PRIMARY KEY,
       name VARCHAR(50),
       ...
   ) ENGINE=InnoDB;
   

5. 業(yè)務(wù)操作時，避免對主鍵的修改

   主鍵的值不宜頻繁更新，因為更新主鍵可能涉及到對其他索引的維護，導(dǎo)致性能開銷。

   -- 不推薦的更新方式
   UPDATE your_table SET id = id + 1 WHERE ...;
   

6. 盡量不要使用 UUID(盡管UUID有全局唯一性的優(yōu)勢) 做主鍵或者是其他的自然主鍵，如身份證號.

插入數(shù)據(jù)優(yōu)化

1. 批量插入：

   批量插入是提高插入性能的有效手段。 相較于逐條插入，批量插入能減少事務(wù)處理和日志寫入的開銷，提高整體性能。

   -- 逐條插入
   INSERT INTO your_table (column1, column2) VALUES (value1, value2);
   INSERT INTO your_table (column1, column2) VALUES (value3, value4);
   
   -- 批量插入
   INSERT INTO your_table (column1, column2) VALUES
   (value1, value2),
   (value3, value4),
   (value5, value6);
   

   如果一次性需要插入大批量數(shù)據(jù)，使用insert語句插入性能較低，此時可以使用MySQL數(shù)據(jù)庫提供的load指令插入。

   # 客戶端連接服務(wù)端時，加上參數(shù) --local-infile（這一行在bash/cmd界面輸入）
   mysql --local-infile -u root -p
   # 設(shè)置全局參數(shù)local_infile為1，開啟從本地加載文件導(dǎo)入數(shù)據(jù)的開關(guān)
   set global local_infile = 1;
   select @@local_infile;
   # 執(zhí)行l(wèi)oad指令將準(zhǔn)備好的數(shù)據(jù)，加載到表結(jié)構(gòu)中
   load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
   

2. 禁用索引

   在大批量插入數(shù)據(jù)時，可以考慮在插入過程中禁用索引，然后再啟用索引。這有助于減少索引維護的開銷。

   -- 禁用索引
   ALTER TABLE your_table DISABLE KEYS;
   
   -- 執(zhí)行插入操作
   
   -- 啟用索引
   ALTER TABLE your_table ENABLE KEYS;
   

3. 合理設(shè)置事務(wù)塊,手動提交事務(wù)

   對于事務(wù)性要求不是很高的插入操作，可以考慮將插入語句放在更大的事務(wù)塊中，減少事務(wù)的開銷,然后手動提交事務(wù)。

   START TRANSACTION;
   
   -- 執(zhí)行插入操作
   
   COMMIT;
   

4. 主鍵順序插入數(shù)據(jù)

   按照主鍵的順序插入數(shù)據(jù)，有助于減少數(shù)據(jù)頁的頻繁分裂和移動，提高寫入性能

   -- 主鍵順序插入
   INSERT INTO your_table (id, column1, column2) VALUES (1, 'value1', 'value2');
   INSERT INTO your_table (id, column1, column2) VALUES (2, 'value3', 'value4');
   INSERT INTO your_table (id, column1, column2) VALUES (3, 'value5', 'value6');
   

update優(yōu)化

1. 避免全表更新

   避免不帶WHERE條件的全表更新，因為這樣的更新會涉及到整個表的數(shù)據(jù)，性能開銷較大。

   -- 不推薦的全表更新
   UPDATE your_table SET column1 = value1;
   

2. 確保更新操作的WHERE條件涉及的列上有合適的索引，以提高檢索效率和避免行鎖升級為表鎖。

   InnoDB 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，并且該索引不能失效，否則會從行鎖升級為表鎖。

   如以下兩條語句：

   update student set no = '123' where id = 1;，-- 這句由于id有主鍵索引，所以只會鎖這一行；
   update student set no = '123' where name = 'test'; -- 這句由于name沒有索引，所以會把整張表都鎖住進行數(shù)據(jù)更新，解決方法是給name字段添加索引
   

3. 使用limit限制更新行數(shù)

   在進行大量更新時，可以使用LIMIT限制每次更新的行數(shù)，避免一次性鎖定大量數(shù)據(jù)。

   UPDATE your_table 
   SET column1 = value1 
   WHERE condition1 
   LIMIT 1000;
   

group by優(yōu)化

1. 使用索引

   確保 GROUP BY 語句中涉及的列上有合適的索引，以提高檢索效率。

   分組操作時，索引的使用也是滿足最左前綴法則的

   -- 假設(shè)category有合適的索引
   SELECT category, AVG(price)
   FROM products
   WHERE date BETWEEN '2022-01-01' AND '2022-12-31'
   GROUP BY category;
   

2. 避免過多的group by列

   盡量減少 GROUP BY 語句中的列數(shù)，避免不必要的計算。

   -- 不推薦的寫法，GROUP BY 列數(shù)過多
   SELECT category, subcategory, AVG(price)
   FROM products
   GROUP BY category, subcategory;
   
   -- 推薦的寫法，盡量減少 GROUP BY 列數(shù)
   SELECT category, AVG(price)
   FROM products
   GROUP BY category;
   

order by優(yōu)化

補充explain結(jié)果的extra字段知識:

1. Using filesort：通過表的索引或全表掃描，讀取滿足條件的數(shù)據(jù)行，然后在排序緩沖區(qū) sort buffer 中完成排序操作，所有不是通過索引直接返回排序結(jié)果的排序都叫 FileSort 排序
2. Using index：通過有序索引順序掃描直接返回有序數(shù)據(jù)，這種情況即為 using index，不需要額外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，則都會走索引，但是如果一個字段升序排序，另一個字段降序排序，則不會走索引，explain的extra信息顯示的是Using index, Using filesort，如果要優(yōu)化掉Using filesort，則需要另外再創(chuàng)建一個索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此時使用select id, age, phone from tb_user order by age asc, phone desc;會全部走索引。

總結(jié)：

• 根據(jù)排序字段建立合適的索引，多字段排序時，也遵循最左前綴法則
• 盡量使用覆蓋索引
• 多字段排序，一個升序一個降序，此時需要注意聯(lián)合索引在創(chuàng)建時的規(guī)則（ASC/DESC）
• 如果不可避免出現(xiàn)filesort，大數(shù)據(jù)量排序時，可以適當(dāng)增大排序緩沖區(qū)大小 sort_buffer_size（默認256k）

limit優(yōu)化

1. 避免使用子查詢

   在 LIMIT 查詢中，盡量避免使用子查詢，因為子查詢可能導(dǎo)致性能下降。

   -- 下面的語句是錯誤的，因為 MySQL 不支持 in 里面使用 limit
   select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
   
   -- 不推薦的寫法，使用子查詢
   SELECT column1, column2
   FROM your_table
   WHERE id IN (SELECT id FROM another_table WHERE some_condition)
   LIMIT 10;
   
   -- 推薦的寫法，使用 JOIN
   SELECT t1.column1, t1.column2
   FROM your_table t1
   JOIN another_table t2 ON t1.id = t2.id
   WHERE t2.some_condition
   LIMIT 10;
   

2. 使用覆蓋索引

   如果可以使用覆蓋索引，可以減少對數(shù)據(jù)表的實際訪問，提高性能。

   -- 假設(shè)有合適的覆蓋索引
   SELECT column1, column2
   FROM your_table
   WHERE some_condition
   ORDER BY indexed_column
   LIMIT 10;
   
   -- 通過覆蓋索引加快速度，直接通過主鍵索引進行排序及查詢
   select id from tb_sku order by id limit 9000000, 10;
   

3. 避免全表掃描

   LIMIT 查詢時，盡量避免對整個表進行掃描。通過使用索引和合適的 WHERE 子句，減小查詢的數(shù)據(jù)集。

   -- 不推薦的寫法，全表掃描
   SELECT column1, column2
   FROM your_table
   LIMIT 10;
   
   -- 推薦的寫法，使用索引和 WHERE 子句
   SELECT column1, column2
   FROM your_table
   WHERE some_condition
   LIMIT 10;
   

count優(yōu)化

補充知識:

• MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count(*) 的時候會直接返回這個數(shù)，效率很高（前提是不適用where）；

  而InnoDB 在執(zhí)行 count(*) 時，則需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累計計數(shù),效率慢。

• 如果count函數(shù)的參數(shù)（count里面寫的那個字段）不是NULL（字段值不為NULL），累計值就加一，最后返回累計值

• count的幾種用法: count(*)、count(主鍵)、count(字段)、count(1)

• count的幾種用法的性能:

  • count(*)跟count(主鍵)一樣，因為主鍵不能為空；且兩者計數(shù)時都不會忽略列值為NULL的情況

  • count(字段)只計算字段值不為NULL的行

  • count(1)引擎會為每行添加一個1，然后就count這個1，返回結(jié)果也跟count(*)一樣；

    這是因為 COUNT 函數(shù)需要對指定的列或表達式進行計數(shù)，而 COUNT(1) 中的 “1” 不是實際的數(shù)據(jù)列，而是一個常量。因此，數(shù)據(jù)庫引擎為每一行生成一個 “1”，然后對這些 “1” 進行計數(shù)，最終得到行數(shù)。

  • count(null)返回0

  • 按效率排序：count(字段) < count(主鍵) < count(1) < count(*)，所以盡量使用 count(*)

總結(jié):

1. 由于考慮效率和性能,盡量使用count(*)

   -- 不推薦的寫法，COUNT(column)
   SELECT COUNT(column)
   FROM your_table
   WHERE some_condition;
   
   -- 推薦的寫法，COUNT(*)
   SELECT COUNT(*)
   FROM your_table
   WHERE some_condition;
   

2. 避免使用COUNT(DISTINCT column)

   在某些情況下，COUNT(DISTINCT column) 可能會導(dǎo)致性能下降，盡量避免使用

   -- 不推薦的寫法，COUNT(DISTINCT column)
   SELECT COUNT(DISTINCT column)
   FROM your_table
   WHERE some_condition;
   
   -- 推薦的寫法，使用其他方式處理
   SELECT COUNT(*)
   FROM (SELECT DISTINCT column FROM your_table WHERE some_condition) AS subquery;
   

The End!!創(chuàng)作不易,歡迎點贊/評論!!歡迎關(guān)注我的WXGZ號!

文章來源地址http://www.zghlxwxcb.cn/news/detail-784641.html

到了這里，關(guān)于【一文詳解】知識分享:(MySQL關(guān)系型數(shù)據(jù)庫知識進階)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！