1 相關理論介紹

一、索引基礎概念

二、索引類型

1. 按數據結構分類

2. 按功能分類

三、索引數據結構原理

B+樹索引特點：

哈希索引特點：

四、索引使用原則

1. 創建索引原則

2. 避免索引失效情況

五、索引優化策略

六、索引維護與管理

七、特殊索引注意事項

八、索引與存儲引擎

2、代碼操作示例

一、索引創建方法

1. 創建表時定義索引

?2. 在已有表上創建索引

二、索引使用方法

1. 基本查詢使用索引

2. 覆蓋索引查詢

三、索引優化方法

1. 索引設計原則

2. 索引使用優化

3. 索引維護優化

四、綜合示例

---

1 相關理論介紹

一、索引基礎概念

索引是MySQL中用于加速查詢的一種數據結構，類似于書籍的目錄。它通過建立額外的數據結構來快速定位數據，避免全表掃描。索引本質上是一種有序的數據結構，MySQL主要使用B+樹作為索引結構。

二、索引類型

1. 按數據結構分類

• B+樹索引：MySQL最常用的索引類型，適合范圍查詢和排序
• 哈希索引：Memory引擎默認索引類型，適合等值查詢但不支持范圍查詢
• 全文索引：用于全文搜索，MyISAM和InnoDB都支持
• 空間索引：用于地理空間數據，MyISAM支持

2. 按功能分類

• 普通索引：最基本的索引類型，無特殊限制
• 唯一索引：索引列值必須唯一但允許NULL值
• 主鍵索引：特殊的唯一索引，不允許NULL值
• 復合索引：多個列組合的索引
• 前綴索引：對字符列前N個字符建立的索引

三、索引數據結構原理

B+樹索引特點：

1. 所有數據都存儲在葉子節點，非葉子節點只存儲鍵值
2. 葉子節點通過指針連接形成鏈表，便于范圍查詢
3. 樹高度通常為3-4層，能支持千萬級數據高效查詢
4. 查詢時間復雜度為O(log n)

哈希索引特點：

1. 基于哈希表實現，查詢時間復雜度為O(1)
2. 只支持等值查詢(=, IN)，不支持范圍查詢(>, <, BETWEEN)
3. 不支持排序操作

四、索引使用原則

1. 創建索引原則

• 為常用于WHERE條件的列創建索引
• 為JOIN連接的列創建索引
• 為ORDER BY、GROUP BY的列創建索引
• 選擇區分度高的列建立索引
• 使用短索引，特別是對字符串列
• 合理使用復合索引，遵循最左前綴原則

2. 避免索引失效情況

• 在索引列上使用函數或運算
• 使用!=或<>操作符
• 使用OR連接條件(可改為IN)
• 使用前導通配符LIKE '%xxx'
• 隱式類型轉換導致索引失效
• 復合索引不遵循最左前綴原則

五、索引優化策略

1. 覆蓋索引：查詢列都包含在索引中，避免回表操作
2. 索引下推：MySQL5.6+特性，將WHERE條件下推到存儲引擎層過濾
3. MRR優化：Multi-Range Read優化，減少隨機IO
4. ICP優化：Index Condition Pushdown優化
5. 使用EXPLAIN分析：查看SQL執行計劃，優化索引使用

六、索引維護與管理

1. 定期分析表(ANALYZE TABLE)更新索引統計信息
2. 定期優化表(OPTIMIZE TABLE)減少碎片
3. 監控索引使用情況，刪除無用索引
4. 避免過多索引，一般不超過表字段數的20%

七、特殊索引注意事項

1. 自增主鍵：InnoDB推薦使用自增列作為主鍵
2. 前綴索引：對長字符串列使用前N個字符建立索引
3. NULL值處理：盡量避免NULL值，可為NULL的列需要額外空間
4. 外鍵索引：線上OLTP系統慎用外鍵

八、索引與存儲引擎

1. InnoDB：

   • 使用聚簇索引，主鍵作為聚簇索引
   • 二級索引存儲主鍵值
   • 支持事務和行級鎖

2. MyISAM：

   • 使用非聚簇索引，索引和數據分離
   • 只支持表級鎖
   • 支持全文索引

2、代碼操作示例

一、索引創建方法

1. 創建表時定義索引

-- 主鍵索引
CREATE TABLE employee_tbl (emp_id CHAR(9) NOT NULL PRIMARY KEY,emp_name VARCHAR(40) NOT NULL,emp_st_addr VARCHAR(20) NOT NULL,emp_city VARCHAR(15) NOT NULL,emp_st CHAR(2) NOT NULL,emp_zip NUMBER(5) NOT NULL
);-- 多列索引
CREATE TABLE sales (id INT NOT NULL,customer_id INT NOT NULL,amount DECIMAL(10,2),sale_date DATE,PRIMARY KEY (id),INDEX idx_customer_date (customer_id, sale_date)
);

?2. 在已有表上創建索引

-- 普通索引
CREATE INDEX idx_name ON employee_tbl(emp_name);-- 唯一索引
CREATE UNIQUE INDEX idx_zip ON employee_tbl(emp_zip);-- 前綴索引（針對字符串列）
CREATE INDEX idx_city_prefix ON employee_tbl(emp_city(5));

二、索引使用方法

1. 基本查詢使用索引

-- 使用主鍵查詢（自動使用索引）
SELECT * FROM employee_tbl WHERE emp_id = '12345';-- 使用普通索引列查詢
SELECT * FROM employee_tbl WHERE emp_name = 'John Doe';-- 使用多列索引
SELECT * FROM sales WHERE customer_id = 100 AND sale_date = '2025-05-23';

2. 覆蓋索引查詢

-- 如果索引包含所有查詢字段，可以避免回表
CREATE INDEX idx_covering ON sales(customer_id, sale_date, amount);-- 查詢只使用索引列
SELECT customer_id, sale_date FROM sales 
WHERE customer_id = 100 AND sale_date BETWEEN '2025-05-21' AND '2025-05-23';

三、索引優化方法

1. 索引設計原則

優先使用數值類型索引：數值比較比字符串快

-- 不推薦
CREATE INDEX idx_bad ON table(phone_str);-- 推薦：將字符串轉為數字
CREATE INDEX idx_good ON table(CAST(phone_str AS UNSIGNED));

合理使用ENUM/SET：

-- 對于有限可能值的字段
ALTER TABLE employee_tbl 
ADD COLUMN gender ENUM('M','F') NOT NULL COMMENT '性別';

避免NULL字段：

-- 不推薦
ALTER TABLE employee_tbl ADD COLUMN middle_name VARCHAR(20) NULL;-- 推薦
ALTER TABLE employee_tbl ADD COLUMN middle_name VARCHAR(20) NOT NULL DEFAULT '';

2. 索引使用優化

避免索引列運算：

-- 不推薦（索引失效）
SELECT * FROM sales WHERE YEAR(sale_date) = 2025;-- 推薦
SELECT * FROM sales WHERE sale_date BETWEEN '2025-05-23' AND '2025-05-23';

合理使用前綴索引：

-- 對長字符串列使用前綴索引
CREATE INDEX idx_name_prefix ON employee_tbl(emp_name(10));

多列索引順序：

-- 選擇性高的列在前
CREATE INDEX idx_optimal ON sales(sale_date, customer_id);

3. 索引維護優化

定期分析表：

ANALYZE TABLE employee_tbl;

刪除未使用索引：

-- 通過性能Schema或慢查詢日志識別未使用索引
DROP INDEX idx_unused ON employee_tbl;

處理索引碎片：

OPTIMIZE TABLE employee_tbl;

四、綜合示例

-- 創建優化后的表結構
CREATE TABLE optimized_employee (id INT UNSIGNED NOT NULL AUTO_INCREMENT,emp_code CHAR(8) NOT NULL COMMENT '員工編碼',name VARCHAR(30) NOT NULL,department ENUM('IT','HR','Finance','Sales') NOT NULL,join_date DATE NOT NULL,salary DECIMAL(10,2) NOT NULL DEFAULT 0,status TINYINT NOT NULL DEFAULT 1 COMMENT '0-離職 1-在職',PRIMARY KEY (id),UNIQUE KEY uk_emp_code (emp_code),INDEX idx_department_status (department, status),INDEX idx_name (name(10)),INDEX idx_join_date (join_date)
) ENGINE=InnoDB;-- 查詢示例（充分利用索引）
-- 1. 使用主鍵查詢
SELECT * FROM optimized_employee WHERE id = 100;-- 2. 使用多列索引
SELECT id, name FROM optimized_employee 
WHERE department = 'IT' AND status = 1
ORDER BY join_date DESC;-- 3. 覆蓋索引查詢
SELECT department, COUNT(*) 
FROM optimized_employee
WHERE join_date > '2025-05-23'
GROUP BY department;