一、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索引管理命令

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

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

📌 重点对比：

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

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

2.2 按字段数量划分

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

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 按功能逻辑划分（最常用分类）

主键索引 vs 唯一索引：

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

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

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

聚簇索引（主键索引）：
  - 叶子节点存储：| 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索引的代价

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[失效场景]

阅读原文：原文链接