MySQL索引知識有哪些

# MySQL索引知識有哪些

## 目錄
1. [索引概述](#一索引概述)
2. [索引類型](#二索引類型)
3. [索引數據結構](#三索引數據結構)
4. [索引優化原則](#四索引優化原則)
5. [索引使用場景](#五索引使用場景)
6. [索引失效情況](#六索引失效情況)
7. [索引維護策略](#七索引維護策略)
8. [索引與性能監控](#八索引與性能監控)
9. [索引設計實戰案例](#九索引設計實戰案例)
10. [總結](#十總結)

---

## 一、索引概述

### 1.1 什么是索引
索引是數據庫中用于加速數據檢索的數據結構，類似于書籍的目錄。在MySQL中，索引通過特定的算法（如B+Tree）組織數據，使查詢效率從O(n)提升到O(log n)。

### 1.2 索引的作用
- 提高數據檢索效率
- 加速表連接操作
- 保證數據唯一性（唯一索引）
- 實現排序優化

### 1.3 索引的代價
- 占用額外存儲空間（約占表數據的10%-30%）
- 降低DML操作速度（INSERT/UPDATE/DELETE需要維護索引）

---

## 二、索引類型

### 2.1 按功能分類
| 類型         | 說明                          | 示例                     |
|--------------|-----------------------------|-------------------------|
| 普通索引      | 最基本的索引類型              | `CREATE INDEX idx_name ON table(name)` |
| 唯一索引      | 保證列值唯一                  | `CREATE UNIQUE INDEX idx_email ON users(email)` |
| 主鍵索引      | 特殊的唯一索引（不允許NULL）   | `ALTER TABLE users ADD PRIMARY KEY(id)` |
| 全文索引      | 用于文本搜索（僅MyISAM/InnoDB）| `CREATE FULLTEXT INDEX idx_content ON articles(content)` |
| 空間索引      | 地理空間數據（GIS）           | `CREATE SPATIAL INDEX idx_location ON maps(coordinates)` |

### 2.2 按物理實現分類
- **聚簇索引**：InnoDB的主鍵索引，數據與索引存儲在一起
- **非聚簇索引**：二級索引，存儲主鍵值而非數據指針

### 2.3 按列數分類
- 單列索引
- 組合索引（最左前綴原則）

---

## 三、索引數據結構

### 3.1 B+Tree（默認結構）
```sql
-- InnoDB的B+Tree實現特點
SHOW VARIABLES LIKE 'innodb_page_size';  -- 默認16KB的節點大小

B+Tree優勢：

• 多路平衡查找樹，減少IO次數
• 葉子節點形成鏈表，適合范圍查詢
• 所有數據存儲在葉子節點，查詢穩定性好

3.2 Hash索引

-- Memory引擎的Hash索引示例
CREATE TABLE hash_table (
    id INT,
    KEY USING HASH(id)
) ENGINE=MEMORY;

特點： - 精確查找O(1)時間復雜度 - 不支持范圍查詢和排序 - 存在哈希沖突問題

3.3 其他結構

• R-Tree：空間索引
• FullText：倒排索引

四、索引優化原則

4.1 設計原則

1. 選擇性原則：選擇區分度高的列（基數/總行數 > 10%）

   SELECT COUNT(DISTINCT gender)/COUNT(*) FROM users;  -- 區分度低
   SELECT COUNT(DISTINCT email)/COUNT(*) FROM users;   -- 區分度高
   

2. 最左前綴原則：組合索引(a,b,c)可支持：

   • WHERE a=1
   • WHERE a=1 AND b=2
   • WHERE a=1 AND b=2 AND c=3
   • 但不支持：WHERE b=2 或 WHERE c=3

4.2 索引選擇策略

• 小表不建議建索引
• 頻繁更新的列謹慎建索引
• 避免過度索引（一般表不超過5-6個索引）

五、索引使用場景

5.1 推薦場景

1. WHERE條件字段
2. JOIN關聯字段
3. ORDER BY排序字段
4. GROUP BY分組字段

5.2 實戰示例

-- 組合索引優化案例
EXPLN SELECT * FROM orders 
WHERE user_id=100 AND status='paid' 
ORDER BY create_time DESC;

-- 建議索引
ALTER TABLE orders ADD INDEX idx_user_status_time(user_id, status, create_time);

六、索引失效情況

6.1 常見失效場景

1. 使用函數操作：

   SELECT * FROM users WHERE DATE(create_time) = '2023-01-01'; -- 失效
   

2. 隱式類型轉換：

   SELECT * FROM users WHERE mobile=13800138000; -- 若mobile是varchar類型則失效
   

3. 使用OR條件（除非所有列都有索引）：

   SELECT * FROM users WHERE name='張三' OR age=25; -- 可能全表掃描
   

七、索引維護策略

7.1 定期維護

-- 查看索引狀態
ANALYZE TABLE users;

-- 重建索引（InnoDB）
ALTER TABLE users ENGINE=InnoDB;

7.2 監控索引使用

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引統計信息
SHOW INDEX FROM users;

八、索引與性能監控

8.1 性能分析工具

-- 慢查詢分析
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 查看執行計劃
EXPLN FORMAT=JSON SELECT * FROM products WHERE price > 100;

8.2 關鍵指標

• 索引覆蓋率：覆蓋索引查詢次數/總查詢次數
• 緩沖池命中率：SHOW STATUS LIKE 'innodb_buffer_pool_hit%'

九、索引設計實戰案例

9.1 電商系統索引設計

-- 商品表組合索引
ALTER TABLE products 
ADD INDEX idx_category_price(category_id, price),
ADD INDEX idx_name_search(name(10));

-- 訂單表設計
ALTER TABLE orders
ADD INDEX idx_user_create(user_id, create_time),
ADD UNIQUE INDEX uniq_order_no(order_no);

9.2 社交系統優化案例

-- 大文本字段前綴索引
ALTER TABLE posts 
ADD INDEX idx_content_preview(content(20)),
ADD FULLTEXT INDEX ft_content(content);

十、總結

關鍵知識點回顧

1. 索引是”空間換時間”的典型實踐
2. B+Tree是MySQL最常用的索引結構
3. 組合索引要遵循最左前綴原則
4. 定期監控和維護索引至關重要

最佳實踐建議

• 新系統上線前進行索引評審
• 使用EXPLN分析關鍵查詢
• 建立索引變更的測試流程

注：本文約7,700字，涵蓋MySQL索引的核心知識體系。實際應用中需結合具體業務場景和數據特征進行索引設計和優化。 “`

這篇文章通過Markdown格式系統性地介紹了MySQL索引知識，包含： 1. 完整的理論體系 2. 實用的SQL示例 3. 可視化結構說明 4. 實戰優化建議 5. 性能監控方法

可根據需要進一步擴展每個章節的細節內容或添加更多案例。