第8课: 索引优化

索引类型、创建索引、性能优化

索引基础概念与原理

什么是索引？

索引是数据库中用于快速查找数据的数据结构，类似于书籍的目录。它通过建立数据值与物理存储位置之间的映射关系，大幅减少数据查找所需的IO操作，从而提升查询性能。

索引的工作原理

B+树索引结构：

MySQL中最常用的索引结构是B+树，它具有以下特点：

平衡树结构： 所有叶子节点都在同一层，保证查询效率稳定
有序存储： 数据按索引键值有序排列，支持范围查询
叶子节点链接： 叶子节点之间通过指针链接，提高范围查询性能
非叶子节点存储键值： 非叶子节点只存储索引键值，不存储数据行
叶子节点存储数据： 叶子节点存储完整的索引键值和指向数据行的指针

B+树索引结构示意图

索引的优缺点

优点：

大幅提升查询速度，特别是对于大表
加速表与表之间的连接操作
通过唯一索引保证数据的唯一性
加速ORDER BY和GROUP BY操作

缺点：

占用额外的存储空间
降低INSERT、UPDATE、DELETE操作的性能
索引需要定期维护和优化
过多的索引会增加查询优化器的负担

索引类型

索引类型	说明	特点	适用场景
PRIMARY KEY	主键索引	唯一且不能为NULL，一个表只能有一个	表的唯一标识，如用户ID、商品ID
UNIQUE	唯一索引	列值必须唯一，可以有NULL	需要唯一约束的字段，如邮箱、手机号
INDEX	普通索引	最基本的索引，无限制	经常用于查询条件的字段
FULLTEXT	全文索引	用于全文搜索，仅支持CHAR、VARCHAR、TEXT	文章内容、商品描述等文本字段
SPATIAL	空间索引	用于地理空间数据	地理位置、坐标等空间数据

特殊索引类型

前缀索引： 只对字符串的前几个字符建立索引，节省存储空间

CREATE INDEX idx_email_prefix ON users(email(10));

覆盖索引： 索引包含查询所需的所有字段，避免回表查询

CREATE INDEX idx_cover ON users(username, email, age);

函数索引： 基于函数计算结果的索引（MySQL 5.7+）

CREATE INDEX idx_lower_username ON users((LOWER(username)));

创建索引

-- 示例1: 创建普通索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 示例2: 创建唯一索引
CREATE UNIQUE INDEX uk_email ON users(email);

-- 示例3: 创建复合索引（多列索引）
CREATE INDEX idx_category_price ON products(category, price);
CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- 示例4: 在建表时创建索引
CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT,
    INDEX idx_category (category),
    INDEX idx_price (price),
    INDEX idx_category_price (category, price)
) ENGINE=InnoDB;

-- 示例5: 使用 ALTER TABLE 添加索引
ALTER TABLE users ADD INDEX idx_age (age);
ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);
ALTER TABLE products ADD FULLTEXT idx_description (description);

删除索引

-- 示例6: 删除索引
DROP INDEX idx_username ON users;
ALTER TABLE users DROP INDEX idx_age;

-- 示例7: 查看表的索引
SHOW INDEX FROM users;
SHOW CREATE TABLE users;

复合索引与最左前缀原则

复合索引遵循最左前缀原则：查询条件必须从索引的最左列开始，且不能跳过中间列。

-- 假设有复合索引：INDEX idx_abc (a, b, c)

-- 示例8: 可以使用索引的查询
SELECT * FROM table WHERE a = 1;  -- 使用索引
SELECT * FROM table WHERE a = 1 AND b = 2;  -- 使用索引
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;  -- 使用索引
SELECT * FROM table WHERE a = 1 AND c = 3;  -- 部分使用索引（只用到a）

-- 示例9: 不能使用索引的查询
SELECT * FROM table WHERE b = 2;  -- 不使用索引
SELECT * FROM table WHERE c = 3;  -- 不使用索引
SELECT * FROM table WHERE b = 2 AND c = 3;  -- 不使用索引

索引优化高级策略

复合索引最佳实践

复合索引设计原则：

最左前缀原则： 查询条件必须从索引的最左列开始，且不能跳过中间列
选择性原则： 选择性高的列放在前面，提高索引的过滤效率
长度原则： 索引列的长度应尽可能短，减少索引大小和IO操作
覆盖原则： 包含查询所需的所有列，避免回表查询

-- 示例10: 前缀索引（节省空间）
CREATE INDEX idx_email_prefix ON users(email(10));

-- 示例11: 覆盖索引（避免回表查询）
CREATE INDEX idx_cover ON users(username, email, age);
SELECT username, email, age FROM users WHERE username = 'zhangsan';  -- 只查索引，不回表

-- 示例12: 合理的复合索引顺序
CREATE INDEX idx_status_date_amount ON orders(status, order_date, total_amount);
-- 有效查询: WHERE status = 'paid' AND order_date >= '2024-01-01'
-- 有效查询: WHERE status = 'paid'
-- 无效查询: WHERE order_date >= '2024-01-01'  -- 违反最左前缀原则

索引使用原则

何时创建索引：

选择性高的列：区分度高的列适合建索引（如用户ID、邮箱）
WHERE 常用字段：经常出现在WHERE、ORDER BY、GROUP BY的字段
JOIN 条件字段：表连接的字段应建立索引
排序和分组字段：ORDER BY、GROUP BY的字段适合建索引
唯一性约束：需要保证唯一性的字段应建立唯一索引

何时不创建索引：

小表：数据量小的表全表扫描更快
频繁更新的列：会导致索引频繁重建，影响性能
选择性低的列：如性别、状态等区分度低的列
TEXT/BLOB列：除非使用前缀索引，否则不适合建索引
过多的索引：索引会占用空间，影响INSERT、UPDATE、DELETE性能

索引失效场景与解决方案

常见的索引失效场景：

-- 场景1: 在索引列上使用函数
-- 失效查询
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- 优化方案
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- 或使用函数索引（MySQL 5.7+）
CREATE INDEX idx_year_created ON users((YEAR(created_at)));

-- 场景2: 在索引列上进行运算
-- 失效查询
SELECT * FROM users WHERE age + 1 = 20;
-- 优化方案
SELECT * FROM users WHERE age = 19;

-- 场景3: 前导通配符
-- 失效查询
SELECT * FROM users WHERE username LIKE '%zhang%';
-- 优化方案
SELECT * FROM users WHERE username LIKE 'zhang%';  -- 后导通配符有效
-- 或使用全文索引
CREATE FULLTEXT INDEX idx_username_ft ON users(username);
SELECT * FROM users WHERE MATCH(username) AGAINST('zhang');

-- 场景4: 类型不匹配
-- 失效查询（age是INT类型）
SELECT * FROM users WHERE age = '18';
-- 优化方案
SELECT * FROM users WHERE age = 18;

-- 场景5: 对索引列使用IS NULL/IS NOT NULL
-- 可能失效的查询
SELECT * FROM users WHERE email IS NULL;
-- 优化方案
为该列创建索引，MySQL 5.7+ 对NULL值的索引支持较好

-- 场景6: 使用OR连接条件
-- 可能失效的查询
SELECT * FROM users WHERE username = 'zhangsan' OR age = 18;
-- 优化方案
为两个条件都创建索引，或使用UNION
SELECT * FROM users WHERE username = 'zhangsan'
UNION
SELECT * FROM users WHERE age = 18;

-- 场景7: 违反最左前缀原则
-- 失效查询（索引为idx_category_price）
SELECT * FROM products WHERE price > 100;
-- 优化方案
单独为price创建索引，或调整查询条件
CREATE INDEX idx_price ON products(price);

索引维护与监控

索引维护策略：

定期分析表：使用 ANALYZE TABLE 更新表的统计信息
重建碎片化索引：使用 OPTIMIZE TABLE 优化表和索引
监控索引使用情况：使用 Performance Schema 或 Slow Query Log 分析索引使用
删除未使用的索引：移除长期未使用的索引，减少维护成本
优化索引结构：根据查询模式调整索引结构

-- 示例13: 分析表统计信息
ANALYZE TABLE users;

-- 示例14: 优化表和索引
OPTIMIZE TABLE users;

-- 示例15: 查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database' AND index_name IS NOT NULL
ORDER BY count_star DESC;

-- 示例16: 查找未使用的索引
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database' AND index_name IS NOT NULL
AND count_star = 0;

-- 示例17: 查看索引大小
SELECT
    table_name,
    index_name,
    ROUND((stat_value * @@innodb_page_size) / 1024 / 1024, 2) AS size_mb
FROM information_schema.statistics
JOIN performance_schema.table_io_waits_summary_by_index_usage
    ON table_schema = object_schema
    AND table_name = object_name
    AND index_name = index_name
WHERE table_schema = 'your_database'
ORDER BY size_mb DESC;

实战场景：电商索引设计

-- 用户表索引设计
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone CHAR(11),
    created_at DATETIME,
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email),
    INDEX idx_phone (phone),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- 订单表索引设计
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    order_date DATETIME,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    INDEX idx_date (order_date),
    INDEX idx_user_date (user_id, order_date)  -- 复合索引
) ENGINE=InnoDB;

练习题：

为商品表的分类字段创建普通索引
为用户表的邮箱字段创建唯一索引
创建一个包含用户ID和订单日期的复合索引
查看用户表的所有索引
删除商品表的价格索引
分析查询 "SELECT * FROM users WHERE username LIKE '%test%'" 为什么慢
为订单表设计合理的索引结构（考虑常见查询场景）
说明复合索引 (a, b, c) 在哪些查询条件下会生效

上一课下一课