跳到主要内容

SQL 索引

索引是数据库中用于加速查询的重要机制。本章将详细介绍索引的类型、创建方法和最佳实践。

什么是索引？​

索引是一种数据结构，类似于书籍的目录，可以帮助数据库快速定位数据，而不必扫描整个表。

为什么需要索引？​

-- 没有索引时，数据库需要扫描整个表
SELECT * FROM users WHERE email = '[email protected]';
-- 如果表有 100 万行，需要检查 100 万行数据

-- 创建索引后，数据库可以直接定位到目标行
CREATE INDEX idx_email ON users(email);
-- 查询时间从秒级降到毫秒级

解释：

  • 索引将查询复杂度从 O(n) 降低到 O(log n)
  • 索引占用额外的存储空间
  • 索引会降低写入性能（INSERT、UPDATE、DELETE 需要更新索引）

索引类型​

1. 主键索引（Primary Key）​

主键自动创建索引，每个表只能有一个主键：

-- 创建表时定义主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 主键索引特点：
-- 1. 唯一性约束
-- 2. 不能为 NULL
-- 3. 自动创建索引

2. 唯一索引（Unique Index）​

确保列中的值唯一，允许 NULL 值：

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 或在创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

解释：唯一索引与主键的区别：

  • 一个表可以有多个唯一索引，但只能有一个主键
  • 唯一索引允许 NULL 值，主键不允许

3. 普通索引（Index）​

最基本的索引类型，没有唯一性约束：

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 或在创建表时定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

4. 复合索引（Composite Index）​

在多个列上创建的索引：

-- 创建复合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- 复合索引遵循最左前缀原则
-- 以下查询可以使用索引：
SELECT * FROM users WHERE last_name = '张';
SELECT * FROM users WHERE last_name = '张' AND first_name = '三';
SELECT * FROM users WHERE last_name = '张' AND first_name = '三' AND age = 25;

-- 以下查询不能使用索引（跳过了 last_name）：
SELECT * FROM users WHERE first_name = '三';
SELECT * FROM users WHERE age = 25;

最左前缀原则解释：

索引结构：(last_name, first_name, age)

查询条件                     能否使用索引
─────────────────────────────────────────
last_name = '张'             ✓ 使用索引
last_name, first_name        ✓ 使用索引
last_name, first_name, age   ✓ 使用索引
first_name = '三'            ✗ 不能使用
age = 25                     ✗ 不能使用
first_name, age              ✗ 不能使用
last_name, age               ✓ 部分使用（只用到 last_name）

5. 全文索引（Full-Text Index）​

用于全文搜索，适用于文本内容的搜索：

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

-- 使用全文搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库优化');

-- 布尔模式搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

6. 空间索引（Spatial Index）​

用于地理空间数据类型：

-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON stores(coordinates);

-- 空间查询示例
SELECT * FROM stores 
WHERE ST_Distance_Sphere(coordinates, POINT(116.4, 39.9)) < 5000;

创建索引​

基本语法​

-- 创建索引
CREATE INDEX index_name ON table_name(column);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(column);

-- 创建复合索引
CREATE INDEX index_name ON table_name(column1, column2, column3);

-- 指定索引类型
CREATE INDEX index_name ON table_name(column) USING BTREE;

在创建表时定义索引​

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INT,
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- 创建普通索引
    INDEX idx_category (category_id),
    
    -- 创建唯一索引
    UNIQUE INDEX idx_name (name),
    
    -- 创建复合索引
    INDEX idx_category_price (category_id, price)
);

使用 ALTER TABLE 添加索引​

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 添加复合索引
ALTER TABLE users ADD INDEX idx_name_age (name, age);

查看索引​

-- 查看表的索引
SHOW INDEX FROM users;

-- 查看表的索引（简化）
SHOW INDEXES FROM users;

-- 使用信息模式查询
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    SEQ_IN_INDEX,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 'users';

输出解释：

字段含义
Key_name索引名称
Column_name索引列名
Seq_in_index列在索引中的位置
Non_unique是否允许重复值（0=唯一，1=非唯一）
Index_type索引类型（BTREE、HASH、FULLTEXT）

删除索引​

-- 删除索引
DROP INDEX idx_name ON users;

-- 使用 ALTER TABLE 删除索引
ALTER TABLE users DROP INDEX idx_name;

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

索引设计原则​

应该创建索引的情况​

  1. WHERE 子句经常使用的列
-- 如果经常按 status 查询
SELECT * FROM orders WHERE status = 'pending';
-- 应该为 status 创建索引
CREATE INDEX idx_status ON orders(status);
  1. JOIN 操作的关联列
-- JOIN 的关联列应该创建索引
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id;
-- 确保 user_id 和 id 都有索引
  1. ORDER BY 或 GROUP BY 的列
-- 经常排序的列
SELECT * FROM products ORDER BY price DESC;
CREATE INDEX idx_price ON products(price);

-- 经常分组的列
SELECT category_id, COUNT(*) FROM products GROUP BY category_id;
CREATE INDEX idx_category ON products(category_id);
  1. 高选择性的列
-- 选择性 = 不同值数量 / 总行数
-- 选择性越高，索引效果越好

-- 高选择性（适合索引）
-- email 列：每个值都不同，选择性 ≈ 1
CREATE INDEX idx_email ON users(email);

-- 低选择性（不适合索引）
-- gender 列：只有 'M' 和 'F'，选择性 ≈ 0.5
-- 不建议为 gender 单独创建索引

不应该创建索引的情况​

  1. 频繁更新的列

    • 每次更新都会更新索引，影响性能

  2. 数据量小的表

    • 全表扫描可能比索引更快

  3. 低选择性的列

    • 如性别、状态等只有几个不同值的列

  4. 很少用于查询的列

    • 浪费存储空间

索引优化技巧​

覆盖索引​

如果查询的所有列都在索引中，数据库可以直接从索引获取数据，无需回表：

-- 创建覆盖索引
CREATE INDEX idx_user_info ON users(id, name, email);

-- 查询只需要索引中的列
SELECT id, name, email FROM users WHERE id = 1;
-- 不需要回表，查询更快

索引条件下推（ICP）​

MySQL 5.6+ 支持索引条件下推，将 WHERE 条件的过滤下推到存储引擎：

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 查询条件
SELECT * FROM users WHERE name LIKE '张%' AND age > 20;
-- 在 MySQL 5.6 之前：先通过索引找到 name LIKE '张%' 的行，再过滤 age
-- 在 MySQL 5.6+：直接在索引中过滤 age > 20 的行，减少回表次数

避免索引失效​

以下情况会导致索引失效：

-- 1. 在索引列上使用函数
SELECT * FROM users WHERE UPPER(name) = 'ZHANG';  -- 索引失效
SELECT * FROM users WHERE name = 'ZHANG';         -- 索引有效

-- 2. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;    -- phone 是 VARCHAR，索引失效
SELECT * FROM users WHERE phone = '13800138000';  -- 索引有效

-- 3. 使用 NOT、!=、<>、NOT IN
SELECT * FROM users WHERE status != 0;  -- 索引可能失效

-- 4. 使用 OR 连接非索引列
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 如果 age 没有索引，整个索引失效

-- 5. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%张';  -- 索引失效
SELECT * FROM users WHERE name LIKE '张%';  -- 索引有效

-- 6. 复合索引不满足最左前缀
-- 索引：(name, age)
SELECT * FROM users WHERE age = 25;  -- 索引失效

使用 EXPLAIN 分析索引使用​

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 查看结果
-- type: ALL（全表扫描）、index（索引扫描）、range（范围扫描）、
--       ref（索引查找）、const（单值匹配）
-- key: 实际使用的索引
-- rows: 预计扫描的行数

-- 更详细的执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

MySQL 8.0 索引新特性​

1. 隐藏索引​

可以隐藏索引而不删除，用于测试：

-- 创建隐藏索引
CREATE INDEX idx_name ON users(name) INVISIBLE;

-- 隐藏现有索引
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

-- 显示索引
ALTER TABLE users ALTER INDEX idx_name VISIBLE;

解释：隐藏索引不会被优化器使用，但仍然会随数据更新。可以用于测试删除索引后的性能影响。

2. 降序索引​

MySQL 8.0 支持真正的降序索引：

-- 创建降序索引
CREATE INDEX idx_price_desc ON products(price DESC);

-- 查询时可以利用降序索引
SELECT * FROM products ORDER BY price DESC;

3. 函数索引​

可以为表达式创建索引：

-- 创建函数索引
CREATE INDEX idx_lower_email ON users((LOWER(email)));

-- 查询时可以使用索引
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

索引维护​

重建索引​

-- 重建表的索引
ALTER TABLE users ENGINE=InnoDB;

-- 分析表（更新统计信息）
ANALYZE TABLE users;

-- 检查表
CHECK TABLE users;

-- 优化表
OPTIMIZE TABLE users;

查看索引使用情况​

-- 查看索引统计信息
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    CARDINALITY,
    SEQ_IN_INDEX
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database';

-- 查看未使用的索引
SELECT 
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
    AND COUNT_STAR = 0
    AND OBJECT_SCHEMA = 'your_database';

小结​

本章我们学习了：

  1. 索引概念：加速查询的数据结构
  2. 索引类型：主键、唯一、普通、复合、全文、空间索引
  3. 索引创建：CREATE INDEX、ALTER TABLE
  4. 最左前缀原则：复合索引的使用规则
  5. 索引设计原则：何时创建、何时不创建
  6. 索引优化：覆盖索引、避免索引失效
  7. MySQL 8.0 新特性：隐藏索引、降序索引、函数索引

练习​

  1. 为一个用户表创建合适的索引
  2. 使用 EXPLAIN 分析查询的索引使用情况
  3. 创建一个复合索引并测试最左前缀原则
  4. 找出并修复索引失效的问题

参考资源​