全栈进阶指南

皮肤切换

第 6 章：索引与性能优化 ​

在处理大量数据时，查询性能成为关键问题。索引是提高数据库查询速度的重要工具。在本章中，我们将学习索引的基本概念、类型、创建方法以及如何通过索引优化查询性能。

本章内容概要 ​

  • 什么是索引？
  • 索引的类型（主键索引、唯一索引、普通索引、全文索引）
  • 创建与删除索引
  • 索引的工作原理
  • 索引的优点与缺点
  • 查询性能分析（EXPLAIN）
  • SQL 优化技巧
  • 实战练习：优化慢查询

什么是索引？ ​

索引是数据库中一种特殊的数据结构，类似于书籍的目录。它可以帮助数据库系统快速定位到表中的特定记录，而不需要扫描整个表。

索引的作用 ​

  1. 提高查询速度 - 通过索引可以直接定位到目标数据
  2. 加速排序 - 索引本身是有序的，可以加速 ORDER BY 操作
  3. 保证唯一性 - 唯一索引可以确保数据的唯一性
  4. 加速表连接 - 在表连接操作中，索引可以提高连接速度

索引的代价 ​

虽然索引有很多优点，但也有一些代价：

  1. 占用存储空间 - 索引需要额外的存储空间
  2. 降低写入性能 - 插入、更新、删除数据时需要维护索引
  3. 增加复杂性 - 需要合理设计和维护索引

索引的类型 ​

主键索引（Primary Key Index） ​

主键索引是一种特殊的唯一索引，不允许有重复值和空值。每个表只能有一个主键索引。

sql
-- 创建表时定义主键索引
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL
);

-- 或者明确指定主键索引
CREATE TABLE students (
    id INT AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL,
    PRIMARY KEY (id)
);

唯一索引（Unique Index） ​

唯一索引确保索引列中的值是唯一的，但允许有空值。

sql
-- 创建表时定义唯一索引
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL UNIQUE
);

-- 或者明确指定唯一索引
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL,
    UNIQUE KEY uk_student_number (student_number)
);

-- 在已存在的表上创建唯一索引
CREATE UNIQUE INDEX uk_student_number ON students(student_number);

普通索引（Normal Index） ​

普通索引是最基本的索引类型，没有任何限制。

sql
-- 创建表时定义普通索引
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL,
    major VARCHAR(50),
    INDEX idx_major (major)
);

-- 在已存在的表上创建普通索引
CREATE INDEX idx_major ON students(major);

全文索引（Fulltext Index） ​

全文索引主要用于文本搜索，支持复杂的文本查询操作。MySQL 5.6 之后，InnoDB 存储引擎也支持全文索引。

sql
-- 创建表时定义全文索引
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT KEY ft_title_content (title, content)
);

-- 在已存在的表上创建全文索引
CREATE FULLTEXT INDEX ft_title_content ON articles(title, content);

组合索引（Composite Index） ​

组合索引是在多个列上创建的索引。

sql
-- 创建表时定义组合索引
CREATE TABLE grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    grade DECIMAL(5,2),
    INDEX idx_student_course (student_id, course_id)
);

-- 在已存在的表上创建组合索引
CREATE INDEX idx_student_course ON grades(student_id, course_id);

创建与删除索引 ​

创建索引的方法 ​

1. 使用 CREATE INDEX 语句 ​

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

-- 创建唯一索引
CREATE UNIQUE INDEX uk_email ON students(email);

-- 创建组合索引
CREATE INDEX idx_major_name ON students(major, name);

-- 创建前缀索引（适用于长字符串）
CREATE INDEX idx_address ON students(address(10));

2. 使用 ALTER TABLE 语句 ​

sql
-- 添加普通索引
ALTER TABLE students ADD INDEX idx_phone (phone);

-- 添加唯一索引
ALTER TABLE students ADD UNIQUE INDEX uk_phone (phone);

-- 添加主键索引
ALTER TABLE students ADD PRIMARY KEY (id);

删除索引 ​

sql
-- 使用 DROP INDEX 删除索引
DROP INDEX idx_name ON students;

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

-- 删除主键索引
ALTER TABLE students DROP PRIMARY KEY;

索引的工作原理 ​

B+树索引结构 ​

MySQL 中的 InnoDB 存储引擎使用 B+树作为索引的数据结构：

  1. 叶子节点 - 存储实际的数据记录或主键值
  2. 非叶子节点 - 存储索引键值和指向子节点的指针
  3. 有序性 - 索引按键值有序排列
  4. 平衡性 - 所有叶子节点到根节点的距离相同

索引查找过程 ​

当执行查询时，MySQL 会：

  1. 根据查询条件找到对应的索引
  2. 在索引树中查找匹配的键值
  3. 通过索引找到对应的数据记录
  4. 返回查询结果

索引的优点与缺点 ​

优点 ​

  1. 大幅提升查询速度 - 特别是 WHERE 条件和 ORDER BY 操作
  2. 保证数据唯一性 - 唯一索引可以防止重复数据
  3. 加速表连接 - 在 JOIN 操作中提高性能
  4. 优化分组和排序 - 加速 GROUP BY 和 ORDER BY 操作

缺点 ​

  1. 占用额外存储空间 - 索引本身需要存储空间
  2. 降低写入性能 - INSERT、UPDATE、DELETE 需要维护索引
  3. 增加维护成本 - 需要定期优化和维护索引
  4. 不当使用可能适得其反 - 过多或不当的索引反而会降低性能

查询性能分析（EXPLAIN） ​

EXPLAIN 命令可以帮助我们分析 SQL 查询的执行计划。

基本用法 ​

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

-- 更详细的分析
EXPLAIN FORMAT=JSON SELECT * FROM students WHERE name = '张三';

EXPLAIN 输出字段说明 ​

字段说明
id查询序列号
select_type查询类型
table表名
partitions匹配的分区
type连接类型
possible_keys可能使用的索引
key实际使用的索引
key_len索引长度
ref索引比较的列
rows扫描的行数
filtered过滤百分比
Extra额外信息

连接类型（type）说明 ​

按性能从好到差排序：

  1. system - 表只有一行记录（系统表）
  2. const - 通过主键或唯一索引查找，最多只有一行匹配
  3. eq_ref - 对于每个来自前表的行组合，从该表中读取一行
  4. ref - 使用非唯一索引查找
  5. range - 只检索给定范围的行
  6. index - 全索引扫描
  7. ALL - 全表扫描（应尽量避免）

SQL 优化技巧 ​

1. 合理使用索引 ​

sql
-- 好的查询：使用索引
SELECT * FROM students WHERE student_number = '2023001';

-- 不好的查询：没有使用索引
SELECT * FROM students WHERE name LIKE '%张%';

2. 避免在索引列上使用函数 ​

sql
-- 不好的查询：无法使用索引
SELECT * FROM students WHERE YEAR(enrollment_date) = 2023;

-- 好的查询：可以使用索引
SELECT * FROM students WHERE enrollment_date >= '2023-01-01' AND enrollment_date < '2024-01-01';

3. 使用组合索引时遵循最左前缀原则 ​

sql
-- 假设有组合索引 idx_major_name (major, name)

-- 可以使用索引的查询
SELECT * FROM students WHERE major = '计算机科学与技术';
SELECT * FROM students WHERE major = '计算机科学与技术' AND name = '张三';

-- 无法使用索引的查询
SELECT * FROM students WHERE name = '张三';

4. 优化 LIMIT 分页查询 ​

sql
-- 不好的分页：深度分页性能差
SELECT * FROM students ORDER BY id LIMIT 100000, 10;

-- 好的分页：使用索引定位
SELECT * FROM students WHERE id > 100000 ORDER BY id LIMIT 10;

实战练习：优化慢查询 ​

让我们通过实际例子来练习索引优化。

1. 创建测试数据 ​

sql
USE student_management;

-- 创建一个大表用于测试
CREATE TABLE large_students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    student_number VARCHAR(20) NOT NULL UNIQUE,
    major VARCHAR(50),
    class_name VARCHAR(50),
    enrollment_date DATE,
    INDEX idx_major (major),
    INDEX idx_enrollment_date (enrollment_date)
);

-- 插入大量测试数据（简化版）
INSERT INTO large_students (name, student_number, major, class_name, enrollment_date)
SELECT 
    CONCAT('学生', s1.n + s2.n * 10 + s3.n * 100) AS name,
    CONCAT('2023', LPAD(s1.n + s2.n * 10 + s3.n * 100, 4, '0')) AS student_number,
    CASE (s1.n + s2.n * 10) % 5
        WHEN 0 THEN '计算机科学与技术'
        WHEN 1 THEN '软件工程'
        WHEN 2 THEN '数据科学与大数据技术'
        WHEN 3 THEN '人工智能'
        ELSE '网络安全'
    END AS major,
    CONCAT('班级', (s1.n + s2.n * 10) % 10 + 1) AS class_name,
    DATE_ADD('2023-09-01', INTERVAL (s1.n + s2.n * 10) DAY) AS enrollment_date
FROM 
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) s1,
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) s2,
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) s3
LIMIT 1000;

2. 分析慢查询 ​

sql
-- 分析没有索引的查询
EXPLAIN SELECT * FROM large_students WHERE name LIKE '学生5%';

-- 分析使用索引的查询
EXPLAIN SELECT * FROM large_students WHERE major = '计算机科学与技术';

-- 分析范围查询
EXPLAIN SELECT * FROM large_students WHERE enrollment_date BETWEEN '2023-09-01' AND '2023-09-30';

3. 创建合适的索引 ​

sql
-- 为姓名创建前缀索引
CREATE INDEX idx_name ON large_students(name(10));

-- 为组合查询创建组合索引
CREATE INDEX idx_major_enrollment ON large_students(major, enrollment_date);

4. 优化后再次分析 ​

sql
-- 优化后的查询分析
EXPLAIN SELECT * FROM large_students WHERE name LIKE '学生5%';
EXPLAIN SELECT * FROM large_students WHERE major = '计算机科学与技术' AND enrollment_date > '2023-09-15';

5. 全文搜索优化 ​

sql
-- 创建支持全文搜索的表
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT KEY ft_title_content (title, content)
);

-- 插入测试数据
INSERT INTO articles (title, content) VALUES
('MySQL索引优化', 'MySQL索引是提高查询性能的重要手段...'),
('数据库设计原则', '良好的数据库设计是系统性能的基础...'),
('SQL查询优化技巧', '编写高效的SQL查询语句是数据库优化的关键...');

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

索引维护 ​

查看索引信息 ​

sql
-- 查看表的索引信息
SHOW INDEX FROM students;

-- 查看表的状态信息
SHOW TABLE STATUS LIKE 'students';

分析表 ​

sql
-- 分析表的索引分布
ANALYZE TABLE students;

优化表 ​

sql
-- 优化表结构和索引
OPTIMIZE TABLE students;

小结 ​

在本章中，我们学习了索引与性能优化的重要知识：

  1. 索引基础 - 索引的概念、作用和代价
  2. 索引类型 - 主键索引、唯一索引、普通索引、全文索引、组合索引
  3. 索引操作 - 创建、删除索引的方法
  4. 索引原理 - B+树结构和索引查找过程
  5. 性能分析 - 使用 EXPLAIN 分析查询执行计划
  6. 优化技巧 - SQL 优化的最佳实践
  7. 实战练习 - 通过实际例子练习索引优化

掌握索引和性能优化技能对于构建高性能的数据库应用至关重要。在下一章中，我们将学习视图与存储过程，这些是数据库高级功能的重要组成部分。

Copyright © 2025 梁欢的前后端技术笔记 鲁ICP备2025190626号