索引

第八章：索引

💡 索引是数据库性能优化的关键技术，理解索引原理对于编写高效SQL至关重要

8.1 索引概念

8.1.1 什么是索引

定义：索引是对数据库表中一个或多个字段的值进行排序而创建的一种分散存储结构。

为什么需要索引：

数据不可能连续存储（有后加的数据）
数据在库内是分散的
索引把这些数据先排一个序，方便快速查找

8.1.2 建立索引的目的

目的	说明
加速数据检索	避免全表扫描，快速定位数据
加速连接、ORDER BY和GROUP BY	优化排序和分组操作
查询优化器依赖索引	帮助优化器选择最佳执行计划
强制实行唯一性	唯一索引保证数据不重复

8.1.3 索引的存储结构

💡 MySQL vs SQL Server

SQL Server：数据按页存放（每页4KB）

MySQL InnoDB：数据按页存放（每页默认16KB）

8.2 索引分类

8.2.1 按功能分类

索引类型	说明	特点
普通索引	最基本的索引类型	无特殊限制，允许重复和NULL
唯一索引	索引列的值必须唯一	允许NULL（但只能有一个）
主键索引	特殊的唯一索引	不允许NULL，每表只能有一个
全文索引	用于全文搜索	仅支持CHAR、VARCHAR、TEXT
空间索引	用于空间数据类型	仅MyISAM和InnoDB支持

8.2.2 按组织方式分类

聚簇索引（Clustered Index）

特点	说明
数据存储顺序	表中数据的物理顺序与索引顺序相同
数量限制	一个表只能有一个聚簇索引
InnoDB中	主键自动成为聚簇索引
查询效率	范围查询效率高

非聚簇索引/二级索引（Secondary Index）

特点	说明
数据存储顺序	索引顺序与数据物理顺序无关
数量限制	可以有多个
存储内容	存储索引列值和主键值（需要回表查询）

8.2.3 MySQL vs SQL Server 索引对比

特性	SQL Server	MySQL (InnoDB)
聚簇索引	可以选择任意列	主键自动成为聚簇索引
非聚簇索引数量	最多249个	无硬性限制（建议不超过16个）
默认主键索引	非聚簇（除非指定）	聚簇索引

8.3 创建索引

8.3.1 创建普通索引

-- 方法1：CREATE INDEX 语句
CREATE INDEX 索引名 ON 表名(列名);

-- 方法2：CREATE TABLE 时创建
CREATE TABLE 学生 (
    学号 CHAR(10),
    姓名 VARCHAR(20),
    INDEX idx_姓名 (姓名)
);

-- 方法3：ALTER TABLE 添加
ALTER TABLE 学生 ADD INDEX idx_姓名 (姓名);

8.3.2 创建唯一索引

-- 方法1：CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_身份证 ON 学生(身份证号);

-- 方法2：CREATE TABLE 时创建
CREATE TABLE 学生 (
    学号 CHAR(10),
    身份证号 CHAR(18),
    UNIQUE INDEX idx_身份证 (身份证号)
);

-- 方法3：ALTER TABLE
ALTER TABLE 学生 ADD UNIQUE INDEX idx_身份证 (身份证号);

8.3.3 创建主键索引

-- 主键自动创建聚簇索引（InnoDB）
CREATE TABLE 学生 (
    学号 CHAR(10) PRIMARY KEY,  -- 自动创建主键索引
    姓名 VARCHAR(20)
);

-- 或者
CREATE TABLE 学生 (
    学号 CHAR(10),
    姓名 VARCHAR(20),
    PRIMARY KEY (学号)
);

8.3.4 创建复合索引

-- 复合索引（多列索引）
CREATE INDEX idx_班级_姓名 ON 学生(班级, 姓名);

-- 复合唯一索引
CREATE UNIQUE INDEX idx_班级_学号 ON 学生(班级, 学号);

💡 复合索引的最左前缀原则

索引 (A, B, C) 可以支持：

WHERE A = ? ✓

WHERE A = ? AND B = ? ✓

WHERE A = ? AND B = ? AND C = ? ✓

WHERE B = ? ✗（不能使用索引）

WHERE B = ? AND C = ? ✗（不能使用索引）

8.3.5 创建全文索引

-- 创建全文索引（用于文本搜索）
CREATE FULLTEXT INDEX idx_内容 ON 文章(内容);

-- 使用全文索引查询
SELECT * FROM 文章
WHERE MATCH(内容) AGAINST('关键词');

-- 使用自然语言模式
SELECT * FROM 文章
WHERE MATCH(内容) AGAINST('关键词' IN NATURAL LANGUAGE MODE);

8.3.6 约束与索引的关系

-- 创建测试表
CREATE TABLE 学院 (
    学院编号 CHAR(2) PRIMARY KEY,      -- 自动创建主键索引（聚簇）
    学院名称 VARCHAR(30) UNIQUE,       -- 自动创建唯一索引
    学院电话 CHAR(12)
);

-- 查看索引
SHOW INDEX FROM 学院;

输出结果：

+-------+------------+----------+--------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+
| 学院  |          0 | PRIMARY  |            1 | 学院编号     |
| 学院  |          0 | 学院名称  |            1 | 学院名称     |
+-------+------------+----------+--------------+-------------+

💡 结论：

主键约束自动创建主键索引（InnoDB中为聚簇索引）

唯一约束自动创建唯一索引

外键约束会自动创建普通索引（如果不存在）

8.4 查看索引

8.4.1 SHOW INDEX 语句

-- 查看表的所有索引
SHOW INDEX FROM 学生;

-- 查看索引的详细信息
SHOW INDEX FROM 学生\G

输出字段说明：

字段	说明
`Table`	表名
`Non_unique`	0表示唯一索引，1表示非唯一
`Key_name`	索引名称
`Seq_in_index`	索引中的列序号
`Column_name`	列名
`Collation`	排序方式（A升序，D降序，NULL不排序）
`Cardinality`	索引中唯一值的估计数量
`Index_type`	索引类型（BTREE、HASH、FULLTEXT等）

8.4.2 从系统表查询

-- 从information_schema查询索引信息
SELECT
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE,
    INDEX_TYPE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '数据库名'
  AND TABLE_NAME = '表名';

8.4.3 查看表结构中的索引

-- 查看建表语句（包含索引）
SHOW CREATE TABLE 学生\G

8.5 删除索引

8.5.1 DROP INDEX 语句

-- 删除普通索引和唯一索引
DROP INDEX 索引名 ON 表名;

-- 示例
DROP INDEX idx_姓名 ON 学生;
DROP INDEX idx_身份证 ON 学生;

8.5.2 ALTER TABLE 删除

-- 删除普通索引
ALTER TABLE 学生 DROP INDEX idx_姓名;

-- 删除主键索引
ALTER TABLE 学生 DROP PRIMARY KEY;
-- 注意：如果主键是AUTO_INCREMENT，需要先去掉自增属性
ALTER TABLE 学生 MODIFY 学号 CHAR(10);
ALTER TABLE 学生 DROP PRIMARY KEY;

8.5.3 删除约束创建的索引

-- 对于主键约束创建的索引
ALTER TABLE 学生 DROP PRIMARY KEY;

-- 对于唯一约束创建的索引（MySQL中唯一约束=唯一索引）
ALTER TABLE 学生 DROP INDEX 约束名;

-- 对于外键，需要先删除外键约束
ALTER TABLE 学生 DROP FOREIGN KEY FK_学生_学院;
ALTER TABLE 学生 DROP INDEX FK_学生_学院;  -- 再删索引

8.6 索引设计原则

8.6.1 适合建立索引的场景

场景	说明
主键列	必须建立（自动创建）
外键列	加速连接查询
WHERE条件列	加速条件过滤
ORDER BY列	加速排序
GROUP BY列	加速分组
高选择性列	列中唯一值比例高

8.6.2 不适合建立索引的场景

场景	原因
数据量很小的表	全表扫描更快
频繁更新的列	维护索引开销大
大量重复值的列	索引效果差（如性别）
很少用于查询的列	浪费存储空间
TEXT/BLOB大字段	索引效率低

8.6.3 索引的优缺点

8.7 实验验证

8.7.1 验证主键的聚簇索引特性

-- 创建三个对比表
-- 只有唯一约束的表
CREATE TABLE 学院1 (
    学院编号 CHAR(2),
    学院名称 VARCHAR(30) UNIQUE
);

-- 只有主键约束的表
CREATE TABLE 学院2 (
    学院编号 CHAR(2) PRIMARY KEY,
    学院名称 VARCHAR(30)
);

-- 什么约束都没有的表
CREATE TABLE 学院3 (
    学院编号 CHAR(2),
    学院名称 VARCHAR(30)
);

-- 插入相同的无序数据
INSERT INTO 学院1 VALUES ('03', 'C学院'), ('01', 'A学院'), ('02', 'B学院');
INSERT INTO 学院2 VALUES ('03', 'C学院'), ('01', 'A学院'), ('02', 'B学院');
INSERT INTO 学院3 VALUES ('03', 'C学院'), ('01', 'A学院'), ('02', 'B学院');

-- 查询结果
SELECT * FROM 学院1;  -- 按插入顺序显示：03, 01, 02
SELECT * FROM 学院2;  -- 按主键顺序显示：01, 02, 03 ← 聚簇索引效果！
SELECT * FROM 学院3;  -- 按插入顺序显示：03, 01, 02

💡 结论：在InnoDB中，主键是天然的聚簇索引，数据会按主键顺序存储。

8.7.2 验证聚簇索引唯一性

-- InnoDB中，聚簇索引就是主键索引，只能有一个
-- 如果没有定义主键，InnoDB会选择一个唯一的非空索引作为聚簇索引
-- 如果也没有，InnoDB会生成一个隐藏的行ID作为聚簇索引

8.8 快速参考

8.8.1 索引操作语法汇总

-- 创建普通索引
CREATE INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD INDEX 索引名 (列名);

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名);

-- 创建复合索引
CREATE INDEX 索引名 ON 表名(列1, 列2, 列3);

-- 创建全文索引
CREATE FULLTEXT INDEX 索引名 ON 表名(列名);

-- 查看索引
SHOW INDEX FROM 表名;

-- 删除索引
DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;

⬅️ 多表查询与集合运算 🏠 00-数据库 ➡️ 视图

13-索引

索引

第八章：索引

8.1 索引概念

8.1.1 什么是索引

8.1.2 建立索引的目的

8.1.3 索引的存储结构

8.2 索引分类

8.2.1 按功能分类

8.2.2 按组织方式分类

聚簇索引（Clustered Index）

非聚簇索引/二级索引（Secondary Index）

8.2.3 MySQL vs SQL Server 索引对比

8.3 创建索引

8.3.1 创建普通索引

8.3.2 创建唯一索引

8.3.3 创建主键索引

8.3.4 创建复合索引

8.3.5 创建全文索引

8.3.6 约束与索引的关系

8.4 查看索引

8.4.1 SHOW INDEX 语句

8.4.2 从系统表查询

8.4.3 查看表结构中的索引

8.5 删除索引

8.5.1 DROP INDEX 语句

8.5.2 ALTER TABLE 删除

8.5.3 删除约束创建的索引

8.6 索引设计原则

8.6.1 适合建立索引的场景

8.6.2 不适合建立索引的场景

8.6.3 索引的优缺点

8.7 实验验证

8.7.1 验证主键的聚簇索引特性

8.7.2 验证聚簇索引唯一性

8.8 快速参考

8.8.1 索引操作语法汇总

💬 评论