一、SQL索引基础：数据库的“目录”系统

1.1 为什么需要SQL索引？

想象一本500页的《现代汉语词典》：

• 没有目录：要查“数据库”一词，需逐页翻阅（全表扫描）
• 有目录：先查“数”字起始页（索引检索），快速定位

当数据量达到百万级时，SQL索引可将查询速度提升几十到几百倍。

1.2 创建SQL索引的三种方式

-- 方式1：直接创建
CREATE INDEX idx_name ON users(email);

-- 方式2：修改表结构
ALTER TABLE users ADD INDEX idx_name (email);

-- 方式3：建表时创建
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    INDEX idx_email (email)  -- 普通索引
);

1.3 SQL索引管理命令

操作	命令示例	说明
查看索引	SHOW INDEX FROM users;	查看索引名、字段、类型等
删除索引	DROP INDEX idx_email ON users;
强制使用索引	SELECT ... FORCE INDEX(idx_name)	优化器选错索引时使用

二、SQL索引类型详解：数据库的“多重目录”

2.1 按数据结构划分（存储引擎层实现）

类型	原理	适用场景	示例存储引擎
B+树索引	多层平衡树，数据有序	范围查询、排序、准确匹配	InnoDB, MyISAM
哈希索引	键值对哈希表	等值查询（=）	Memory, NDB
全文索引	倒排索引	文本关键词搜索	MyISAM, InnoDB
空间索引	R-Tree结构	地理坐标计算	MyISAM

重点对比：

-- B+树索引支持的操作
SELECT * FROM users WHERE age > 25; -- 范围查询
SELECT * FROM users ORDER BY create_time; -- 排序

-- 哈希索引仅支持
SELECT * FROM users WHERE id = 10086; -- 准确匹配

2.2 按字段数量划分

类型	特点	示例
单列索引	单个字段建立索引	INDEX (email)
联合索引	多个字段组合建立索引	INDEX (last_name, first_name)

⚠️ 联合索引最左前缀原则：

CREATE INDEX idx_name_phone ON users(last_name, phone);

-- ✅ 生效场景
SELECT * FROM users WHERE last_name = '张';
SELECT * FROM users WHERE last_name = '张' AND phone='138****8000';

-- ❌ 失效场景
SELECT * FROM users WHERE phone = '138****8000';

2.3 按功能逻辑划分（最常用分类）

类型	特性	创建方式
普通索引	仅加速查询，允许重复	ADD INDEX idx_name (name)
唯一索引	值必须唯一（含NULL）	ADD UNIQUE INDEX (email)
主键索引	非空+唯一，表唯一标识	ADD PRIMARY KEY (id)
全文索引	文本分词搜索	ADD FULLTEXT INDEX (content)
空间索引	地理数据搜索	ADD SPATIAL INDEX (geom)

主键索引 vs 唯一索引：

INSERT INTO users (id, email) VALUES (NULL, 'a@test.com');
-- 主键索引报错：主键不能为NULL
-- 唯一索引允许：唯一索引列允许一个NULL值

2.4 按存储方式划分（InnoDB核心机制）

类型	数据存储方式	数量限制
聚簇索引	索引节点直接包含行数据	每表1个
非聚簇索引	存储主键ID，需回表查询数据	多个

理解聚簇索引： 假设用户表结构：

聚簇索引（主键索引）：
- 叶子节点存储：| id | name | email | age |

非聚簇索引（普通索引）：
- 叶子节点存储：| age | id |

当通过非聚簇索引查询时：

SELECT name FROM users WHERE age = 30;
执行路径：age索引 -> 主键id -> 聚簇索引 -> 获取数据行

三、模拟数据与应用示例（10万行数据演示）

3.1 数据准备

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 插入10万条产品数据（脚本略）

3.2 SQL索引效果对比实验

场景1：无索引基础查询

-- 耗时约350ms
SELECT * FROM products WHERE category = 'electronics';

场景2：创建单列索引后

CREATE INDEX idx_category ON products(category);

-- 耗时降至8ms (提升44倍)
EXPLAIN SELECT * FROM products WHERE category='electronics';
-- 执行计划：type=ref, key=idx_category

场景3：联合索引范围查询

CREATE INDEX idx_category_price ON products(category, price);

-- ✅ 高效查询（使用索引）
SELECT * FROM products 
WHERE category='books' AND price > 100;

-- ❌ 低效查询（未用索引）
SELECT * FROM products WHERE price > 100;

场景4：覆盖索引优化

-- 原始查询（需回表）
SELECT id, name FROM products WHERE category='furniture';

-- 创建覆盖索引
CREATE INDEX idx_cover ON products(category, name, id);

-- 执行计划显示"Using index"
EXPLAIN SELECT id, name FROM products WHERE category='furniture';

四、SQL索引使用注意事项

4.1 SQL索引的代价

操作	无索引表耗时	5个索引表耗时	增长倍数
INSERT 1000行	0.8s	3.2s	4倍
UPDATE 主键	0.05s	0.31s	6.2倍

4.2 SQL索引失效的六大场景

1. 函数操作：SELECT * FROM users WHERE UPPER(name) = 'JOHN'; -- 失效
   SELECT * FROM users WHERE name = 'John'; -- 有效
2. 隐式类型转换：-- phone是字符串类型
   SELECT * FROM users WHERE phone = 13800138000; -- 失效
3. 模糊查询通配符开头：SELECT * FROM users WHERE name LIKE '%son'; -- 失效
   SELECT * FROM users WHERE name LIKE 'Joh%'; -- 有效
4. OR连接非索引列：-- age列无索引
   SELECT * FROM users WHERE name='John' OR age=30; -- 失效
5. 联合索引跳过首列：CREATE INDEX idx_name_phone ON users(name, phone);
   SELECT * FROM users WHERE phone='13800138000'; -- 失效
6. 数据倾斜优化器弃用：-- 90%数据category='electronics'
   SELECT * FROM products WHERE category='electronics'; -- 可能全表扫描

4.3 SQL索引设计原则

1. 高频查询优先：WHERE/JOIN/ORDER BY/GROUP BY涉及的列
2. 区分度高原则：选Cardinality值高的列（如身份证号比性别适合）
3. 控制索引数量：提议单表索引不超过5个
4. 避免冗余索引：INDEX(a,b) -- 已存在
   INDEX(a) -- 冗余！

附录：SQL索引学习地图

graph TD
    A[索引基础] --> B[创建与管理]
    A --> C[类型体系]
    C --> D[数据结构]
    C --> E[字段数量]
    C --> F[功能逻辑]
    C --> G[存储方式]
    D --> H[B+树 vs 哈希]
    E --> I[联合索引最左前缀]
    F --> J[主键/唯一/全文]
    G --> K[聚簇/非聚簇]
    L[实战应用] --> M[性能对比]
    L --> N[覆盖索引]
    L --> O[失效场景]