4.2.2 MySQL索引原理以及SQL優化

1. 索引與約束

1. 索引是什么

1. 索引是一種有序的數據結構，MySQL 中主要使用 B+ 樹（InnoDB 引擎）來組織索引
2. 它通過加快數據檢索速度來提升數據庫的查詢效率
3. 可理解為：數據庫中的“目錄”或“書的頁碼”
4. 按照單個或者多個進行排序

2. 索引的目的

提升搜索效率

3. 幾種索引

1. 主鍵索引
   • 表的唯一標識
   • 不能為空 (NOT NULL) 且唯一 (UNIQUE)
   • 每張表只能有一個主鍵
   • 創建主鍵索引時，MySQL自動建立索引（底層通常是B+樹）

-- 創建表時指定主鍵
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50)
);-- 或者先創建表，再加主鍵
ALTER TABLE users ADD PRIMARY KEY (id);

2. 唯一索引
   • 保證列值唯一，但允許為空（特殊場景除外）
   • 不作為主鍵，可以有多個唯一索引
   • 通常用于：手機號、郵箱、身份證號等

-- 創建唯一索引
CREATE TABLE employees (emp_id INT,email VARCHAR(100) UNIQUE,phone VARCHAR(20),PRIMARY KEY (emp_id)
);-- 或者后期添加唯一索引
ALTER TABLE employees ADD UNIQUE (email);

3. 普通索引
   • 僅加速查詢速度
   • 沒有唯一性要求,key可以重復
   • 可以為經常用作條件查詢（WHERE）的列加普通索引

-- 創建普通索引
CREATE TABLE articles (id INT PRIMARY KEY,title VARCHAR(200),content TEXT
);-- 給 title 添加普通索引
CREATE INDEX idx_title ON articles(title);-- 或者這樣
ALTER TABLE articles ADD INDEX (title);

4. 組合索引
   • 一個索引包含多個列
   • 適合多列聯合查詢的場景
   • 遵循最左前綴原則（查詢時條件必須從索引的最左列開始）

-- 創建組合索引
CREATE TABLE orders (order_id INT PRIMARY KEY,user_id INT,product_id INT,order_date DATE
);-- 給 (user_id, product_id) 建組合索引
CREATE INDEX idx_user_product ON orders(user_id, product_id);-- 查詢時如果條件是 user_id，或 user_id + product_id，則可以用到索引
SELECT * FROM orders WHERE user_id = 123;SELECT * FROM orders WHERE user_id = 123 AND product_id = 456;-- 但如果單查 product_id，是用不了這個組合索引的

5. 全文索引
   • 用于全文搜索
   • 適合大文本內容的搜索
   • 通常用于：文章、博客、產品描述等

-- 1. 創建表
CREATE TABLE blog_posts (id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(255),content TEXT,FULLTEXT(title, content)
);-- 2. 插入數據
INSERT INTO blog_posts (title, content) VALUES
('MySQL Tutorial', 'Learn how to use MySQL database.'),
('Fulltext Search', 'Learn about fulltext search in MySQL.');-- 3. 搜索
SELECT * FROM blog_posts
WHERE MATCH(title, content) AGAINST('MySQL');

6. 主鍵選擇
   innodb 中表是索引組織表，每張表有且僅有一個主鍵；
   1. 如果顯示設置 PRIMARY KEY ，則該設置的 key 為該表的主鍵；
   2. 如果沒有顯示設置，則從非空唯一索引中選擇；
   3. 只有一個非空唯一索引，則選擇該索引為主鍵；
   4. 有多個非空唯一索引，則選擇聲明的第一個為主鍵；
   5. 沒有非空唯一索引，則自動生成一個 6 字節的 _rowid 作為主鍵；

4. 約束

InnoDB 本身提供對這些約束（PRIMARY KEY（主鍵約束），UNIQUE（唯一約束），NOT NULL（非空約束），FOREIGN KEY（外鍵約束），CHECK（檢查約束））的支持，保證數據的正確性、安全性

1.外鍵

1. 一個表中的字段依賴于另一個表的主鍵/唯一鍵；
2. 保證兩張表數據的關聯完整性；
3. 可以設置 級聯操作（如刪除/更新時一起變化）

-- 創建班級表
CREATE TABLE classes (class_id INT PRIMARY KEY,class_name VARCHAR(100)
);-- 創建學生表，并設置外鍵關聯到班級表
CREATE TABLE students (student_id INT PRIMARY KEY,name VARCHAR(100),class_id INT,FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

2. 約束 vs 索引的區別

項目	約束	索引
定義	整性、合法性	加速數據查詢效率
主要目的	保證正確性（不插錯、不留空、不重復）	提升性能（更快查找）
本質	規則	數據結構（如B+樹）
關系	主鍵約束、唯一約束會自動生成對應索引！	索引不一定帶有約束，單純為了提速
示例	NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY	CREATE INDEX idx_name ON table(col)

5. 索引實現

1. 索引存儲

1. 索引存儲的數據結構通常是 B+樹，而不是哈希表
2. 索引是磁盤上的有序結構，不是存在內存中的

2. 頁

1. innoDB 的數據存儲以 頁（Page） 為最小單位，每一頁大小通常是 16KB。
2. 一棵 B+ 樹的每個節點對應一個或多個磁盤頁
3. 數據讀寫以頁為單位進行（減少磁盤 I/O 次數）

3. B+樹

1. B+樹是數據庫默認的索引結構
2. 每個節點存放有序的數據鍵值+指向子節點的指針
3. 所有數據都存放在葉子節點
4. 葉子節點之間有鏈表連接（范圍查詢快）

4. B+樹層高問題

1. 理想狀態下，B+樹的高度很低，一般在 2-4層
2. 為什么？
   因為一頁（16KB）能存很多索引項（假設一項占 16字節，1頁能存1024項）；所以即使存百萬條數據，只要 2-3 次磁盤IO 就能找到，非常快！

5. 自增id

1. 很多表喜歡用 自增ID（auto_increment） 作為主鍵。
2. 自增ID的好處：
   插入數據總是追加到B+樹的最右邊；
   避免頻繁分裂、重排；
   插入性能最好
   放心用，根本用不完

6. 聚集索引

1. InnoDB 的每張表數據文件本身就是一棵 B+樹，稱為聚集索引。
2. 主鍵索引就是數據本身
3. 特點：
   按主鍵順序存儲；
   查找主鍵非常快；
   非主鍵（普通索引）存儲的是【主鍵值】作為指針

7. 輔助索引

1. 除了主鍵外，創建的其他索引，都是輔助索引
2. 輔助索引的葉子節點，不直接存儲數據行，而是存儲【主鍵值】
3. 查詢時，先通過輔助索引找到主鍵，再通過主鍵去聚集索引找完整數據（回表）

總之，索引信息和數據信息的分層管理，便于高效的組織磁盤數據，快速實現單點和范圍查詢

8. innnodb體系結構

1. buffer pool

Buffer Pool 是 InnoDB 把磁盤上的數據頁、索引頁、插入緩沖（Change Buffer）、自適應哈希索引等緩存到內存中的區域。
目的是：減少磁盤 I/O，提高數據庫訪問速度。

特點：

1. 查詢數據時優先從 Buffer Pool 取（命中則速度很快）
2. 如果沒有命中，才從磁盤讀入，并加入 Buffer Pool（可能引發淘汰機制，比如 LRU）
3. 包括臟頁管理（數據被修改但未刷盤）機制

2. change buffer

Change Buffer 是 InnoDB 中專門為二級索引的插入、更新、刪除操作設置的緩存區域，延遲將二級索引變更寫入磁盤，從而減少磁盤 I/O。

**原理：

1. 對于二級索引的插入/修改，不直接去磁盤更新，而是先記錄到 Change Buffer。
2. 之后在一定條件（比如頁被讀取進內存，或系統空閑時）才真正合并到磁盤上的二級索引頁。

為什么只針對二級索引（非主鍵索引）？

1. 因為主鍵索引（聚集索引）必須保證實時一致性。
2. 二級索引允許延遲一致，所以可以先緩存在 Change Buffer。

CREATE TABLE user (id INT PRIMARY KEY,       -- 主鍵，主索引name VARCHAR(50),age INT,email VARCHAR(50),INDEX idx_name (name)      -- 二級索引
);
--id 是 主索引（一級索引）：--葉子節點：存的是整行數據，比如 {id=1, name="張三", age=20, email="xx@xx.com"}--name 是 輔助索引（二級索引）：--葉子節點：只存 {name="張三", id=1}--如果通過 name 查找，還需要根據 id 再去主索引回表拿到完整那一行。

一級索引（主索引）：葉子節點存整行

二級索引（輔助索引）：葉子節點存主鍵id，查詢時需要回主鍵索引再拿數據

9. 最左匹配原則

組合索引在查詢時，會優先用最左邊的列開始匹配，從左到右連續匹配才能用上索引

CREATE INDEX idx_user_name_age ON users(name, age);SELECT * FROM users WHERE name = 'Tom';      --  用上索引
SELECT * FROM users WHERE name = 'Tom' AND age = 18; --  用上索引
SELECT * FROM users WHERE age = 18;           -- 用不了索引（跳過了最左的 name）

10. 覆蓋索引

查詢的數據只需要索引里的字段，不用回表到原表，因此速度更快

CREATE INDEX idx_name_age ON users(name, age);SELECT name, age FROM users WHERE name = 'Tom'; -- 覆蓋索引
--因為 name 和 age 都在 idx_name_age 這個索引里，不需要回表SELECT name FROM users WHERE name = 'Tom';     -- 回表了

11. 索引下推

在索引遍歷階段就盡量篩選數據，減少回表次數，提升查詢性能

SELECT * FROM users WHERE name LIKE 'Tom%' AND age = 18;
-- 假設沒有索引下推，會先根據 name LIKE 'Tom%' 找到滿足條件的索引項，然后再根據 age = 18 進行過濾。-- 而索引下推則是在索引遍歷階段就對 WHERE 條件進行篩選，減少回表次數。
--少了很多不必要的回表

12. 索引失效

一些不合理的 SQL 寫法，會導致原本能用的索引失效，導致全表掃描

13. 索引原則

原則	內容
最優選擇	盡量選擇區分度高的列建立索引
組合優先	多條件查詢，建組合索引，遵循最左匹配
覆蓋優先	盡可能做到查詢只用索引（覆蓋索引）
更新慎用	索引太多，更新、插入性能會變差
合理選擇	小表不建索引，大表必須優化索引
防止失效	避免在索引列上做函數、運算、隱式轉換

2. sql比較慢怎么辦

1. 慢查詢日志

1. 慢查詢日志是 MySQL 提供的一種日志記錄機制，用于記錄執行時間超過閾值的 SQL 語句。
2. 可以通過配置來開啟慢查詢日志，設置閾值，然后查看日志文件來分析哪些 SQL 語句耗時。