13 / 15

索引与约束

数据库性能优化的两大核心要素是索引与约束。索引通过高效的数据结构加速数据检索，显著提升查询性能；约束则用于保证数据的完整性和一致性，防止无效或错误数据的写入。如果将数据库比作一本大型参考书，索引相当于详尽的目录，能够快速定位到所需内容；而缺乏索引的表在执行查询时则需要全表扫描，效率极低，尤其在数据量较大时影响尤为明显。

索引与约束

索引

什么是索引

在向数据库表插入新数据时，数据库管理系统通常不会按照特定的列（如主键或名称）顺序进行物理存储，而是将数据写入到存储文件中的下一个可用空间。这种无序存储方式意味着，数据在物理层面上并未按照任何业务字段进行排序。

因此，当执行基于某一列的查询操作时，数据库需要遍历表中的所有数据行以查找满足条件的记录，这一过程被称为全表扫描。


SELECT dept_id, name 
FROM department 
WHERE name LIKE '行政%';

当表只有几行数据时，这种方式工作得很好。但想象一下，如果表中有几百万行数据，这种查询方式就会变得非常缓慢。这时候，我们就需要索引的帮助。

索引就像书籍的目录一样，它是一种特殊的数据结构，帮助数据库快速定位到需要的数据行，而不需要扫描整个表。

索引的工作原理

数据库索引是一种特殊的表，它保持着特定的排序顺序。与普通的数据表不同，索引不包含实体的全部信息，而只包含用于定位数据行的列，以及描述这些行在物理存储中位置的信息。

假设你在管理一个大型图书馆。你可以按照以下方式组织图书的查找：

数据库索引的工作方式与此类似。它维护着一个排序的结构，让数据库能够快速跳转到包含所需数据的表行。

创建和管理索引

如何创建索引

假设我们有一个「员工表」，经常需要根据员工的姓名来查找员工信息。我们可以在姓名列上创建一个索引：


-- 在员工表的姓名列上创建索引
ALTER TABLE employee 
ADD INDEX emp_name_idx (name);

创建索引后，当你执行类似下面的查询时，数据库就可以利用索引快速找到结果：


SELECT emp_id, name, department 
FROM employee 
WHERE name = '张三';

索引创建后，查询优化器会自动判断是否使用索引。对于小表，优化器可能选择不使用索引而直接扫描全表，因为这样反而更快。

查看已有索引

有时候我们需要了解一个表上已经有哪些索引，以便进行优化或排查问题。你可以通过数据库提供的命令来查看某个表当前存在的所有索引，包括主键索引、唯一索引和普通索引。这样可以帮助你判断是否需要新增或删除某些索引，避免重复创建，也能更好地理解数据库的查询优化情况：


-- 查看员工表的所有索引
SHOW INDEX FROM employee;

通常，当你创建一个表并指定主键时，数据库会自动在主键列上创建一个唯一索引。

删除索引

有时候，随着业务需求的变化，某些索引可能变得不再必要，甚至会影响写入性能或占用过多存储空间。此时，建议定期检查和评估现有索引。如果确认某个索引已经很少被查询使用，或者完全没有被用到，可以选择将其删除，以简化数据库结构并提升整体性能。删除索引的操作如下：


-- 删除索引
ALTER TABLE employee 
DROP INDEX emp_name_idx;

索引的类型

唯一索引

在某些业务场景下，除了提升查询性能外，还需要通过索引来强制保证列值的唯一性。例如，为防止部门表中出现重名部门，可在部门名称列上创建唯一索引，从而确保数据的一致性和完整性：


-- 创建唯一索引确保部门名称不重复
ALTER TABLE department 
ADD UNIQUE dept_name_unique (name);

一旦创建了唯一索引，如果尝试插入重复的部门名称，数据库就会报错：


INSERT INTO department (name) VALUES ('行政部');
-- 如果行政部已存在，这条语句会失败

复合索引

在实际业务场景中，常常需要基于多个字段进行联合查询。例如，在员工管理系统中，可能需要同时根据员工的姓氏（last_name）和名字（first_name）来精确检索员工信息：


-- 创建复合索引
ALTER TABLE employee 
ADD INDEX emp_full_name_idx (last_name, first_name);

复合索引有一个重要特点：它可以用于查询索引的前缀列。比如上面的索引可以用于以下查询：


-- 可以使用索引（查询last_name）
SELECT * FROM employee WHERE last_name = '张';
 
-- 可以使用索引（查询last_name和first_name）
SELECT * FROM employee WHERE last_name = '张' AND first_name = '三';
 
-- 无法有效使用索引（只查询first_name）
SELECT * FROM employee WHERE first_name = '三';

复合索引的列顺序很重要！应该把最常用于查询条件的列放在前面，把选择性高的列（不同值较多的列）放在前面。

B树索引

在绝大多数关系型数据库管理系统（如 MySQL、PostgreSQL、Oracle 等）中，默认采用 B 树（Balanced Tree，准确地说是 B+ 树）作为索引的底层实现结构。 B+ 树是一种多路平衡查找树，能够保持数据有序，并且支持高效的范围查询和等值查找。其设计充分考虑了磁盘 I/O 的特性，通过将数据分块存储在磁盘页中，极大地减少了磁盘访问次数，从而提升了大规模数据集上的检索性能。

B+ 树的每个非叶子节点只存储索引键和指向子节点的指针，所有实际数据都存储在叶子节点，并且叶子节点之间通过链表相连，便于范围扫描。这种结构非常适合数据库中频繁的插入、删除和查找操作，能够在数据量极大的情况下依然保持较高的查询效率。

当你查询姓氏以「李」开头的员工时，数据库会：

从根节点开始，判断「李」属于A-M范围
向左分支移动到下一层节点
继续细化范围，直到找到包含「李」姓员工的叶子节点
在叶子节点中扫描所有「李」姓员工

这种树状结构保证了查找效率，即使表中有百万行数据，通常也只需要几次磁盘访问就能找到目标数据。

索引的使用策略

何时使用索引

虽然索引能够极大提升数据库的查询效率，但其引入也伴随着一定的系统开销。每创建一个索引，数据库都会额外维护一套数据结构（如B+树、哈希表等），这些结构不仅会占用磁盘空间，还会在内存中消耗缓冲区资源。更重要的是，索引的维护会直接影响数据写入、更新和删除操作的性能：每当表中的数据发生变更时，相关的索引也必须同步更新，这会导致额外的I/O和CPU消耗，尤其是在高并发写入场景下影响尤为明显。

因此，索引的设计和使用需要在查询性能与系统资源消耗、数据维护成本之间做出权衡，避免因过多或不合理的索引导致整体性能下降。

一般来说，应该在以下情况下创建索引：

主键列自动获得索引，这个通常由数据库自动处理。
外键列应该创建索引，因为数据库需要检查引用完整性。
经常用于查询条件的列值得考虑创建索引，特别是日期列和短字符串列。

索引策略的黄金法则：既不要太多，也不要太少。从基础的主键和外键索引开始，然后根据实际的查询模式逐步添加其他索引。

索引优化示例

让我们看一个实际的例子。假设有一个账户表，我们经常需要按客户ID汇总账户余额：


SELECT cust_id, SUM(balance) as total_balance 
FROM account 
WHERE cust_id IN (1001, 1005, 1009, 1011) 
GROUP BY cust_id;

最初，数据库可能只有一个基于客户ID的索引。查询时需要：

使用客户ID索引找到相关行
访问实际的表行获取余额数据
进行汇总计算

如果我们创建一个包含客户ID和余额的复合索引：


ALTER TABLE account 
ADD INDEX cust_balance_idx (cust_id, balance);

现在，查询所需的所有字段（cust_id 和 balance）都已经包含在我们新建的复合索引 cust_balance_idx 中。这样，数据库在执行上述查询时，无需再回表（即不需要再访问原始数据表），而是可以直接在索引结构中完成数据的定位、读取和聚合操作。这种只依赖索引、无需访问原表的查询方式被称为「覆盖索引查询」（Covering Index Query）。

覆盖索引查询的优势在于：

查询只需扫描索引，减少了磁盘 I/O 操作，速度更快；
索引通常比原表小，数据页更容易被缓存在内存中，进一步提升性能；
避免了回表操作，尤其在大表场景下能显著降低查询延迟。

因此，通过合理设计复合索引，不仅能提升单条查询的效率，还能让数据库整体的查询性能获得大幅提升，特别适合高并发、读多写少的业务场景。

数据库约束

约束的作用

约束是对表中一列或多列的限制规则。它们确保数据的完整性和一致性，防止无效或错误的数据进入数据库。想象你在管理一个学生信息系统。没有约束的话，可能会出现以下问题：

两个学生有相同的学号
学生的年龄是负数
学生选择了不存在的课程

约束就是用来防止这些问题的规则。

约束的类型

数据库约束主要有以下几种类型：

约束类型	作用说明	备注
主键约束	标识表中每一行的唯一性。每个表只能有一个主键，主键列不能重复或为空。	主键列自动获得唯一索引，且不允许NULL。
外键约束	确保一个表中的值必须在另一个表中存在。	用于维护表之间的引用完整性，如学生表的专业ID引用专业表。
唯一约束	确保列中的值唯一。与主键不同，允许空值，且一个表可有多个唯一约束。	适用于如邮箱、手机号等需要唯一但可为空的字段。
检查约束	限制列中允许的值范围或条件。	例如年龄必须大于0且小于150。部分数据库对CHECK支持有限。

创建约束

在数据库设计时，约束（如主键、外键、唯一约束、检查约束等）通常会在创建表的同时一并定义。这样可以在表结构建立的第一时间就对数据的有效性和一致性进行严格控制。例如，在创建学生表时，可以直接在表结构中声明每一列需要满足的约束条件，包括哪些字段必须唯一、哪些字段不能为空、哪些字段需要引用其他表的数据、以及字段值的取值范围等。


CREATE TABLE student (
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL,
    major_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    
    -- 主键约束
    CONSTRAINT pk_student PRIMARY KEY (student_id),
    
    -- 外键约束

如果在创建表时忘记添加约束，你也可以在表创建后添加约束：


-- 添加外键约束
ALTER TABLE student 
ADD CONSTRAINT fk_student_major 
FOREIGN KEY (major_id) REFERENCES major(major_id);
 
-- 添加唯一约束
ALTER TABLE student 
ADD CONSTRAINT uk_student_email UNIQUE (email);

级联约束

级联更新和删除

默认情况下，如果你尝试删除或修改被其他表引用的数据，数据库会阻止这个操作。比如：


-- 这会失败，因为有学生属于这个专业
DELETE FROM major WHERE major_id = 1;

但有时候，我们希望当父表中的数据改变时，自动更新相关的子表数据。这就是级联约束的作用：

级联更新示例


-- 创建支持级联更新的外键约束
ALTER TABLE student 
DROP FOREIGN KEY fk_student_major;
 
ALTER TABLE student 
ADD CONSTRAINT fk_student_major 
FOREIGN KEY (major_id) REFERENCES major(major_id)
ON UPDATE CASCADE;

现在，如果你更新专业表中的专业ID：


UPDATE major SET major_id = 999 WHERE major_id = 1;

学生表中所有原本major_id为1的记录会自动更新为999。

级联删除示例

类似地，你可以设置级联删除：


ALTER TABLE student 
ADD CONSTRAINT fk_student_major 
FOREIGN KEY (major_id) REFERENCES major(major_id)
ON UPDATE CASCADE
ON DELETE CASCADE;

级联删除要谨慎使用！当你删除一个专业时，所有属于该专业的学生记录也会被删除。这可能不是你想要的结果。

约束与索引的关系

不同的数据库系统在处理约束和索引的关系上有所不同：

约束类型	MySQL	SQL Server	Oracle
主键约束	自动创建唯一索引	自动创建唯一索引	使用现有索引或创建新索引
外键约束	自动创建索引	不创建索引	不创建索引
唯一约束	自动创建唯一索引	自动创建唯一索引	使用现有索引或创建新索引

即使数据库不自动为外键创建索引，我也强烈建议你手动创建，因为外键列经常用于连接查询和完整性检查。

性能考虑

索引的权衡

索引是一把双刃剑。它们能显著提升查询性能，但也会影响数据修改操作的性能：

因此，索引策略需要根据你的应用特点来制定。如果你的应用主要是读操作（如报表系统），可以创建更多索引。如果主要是写操作（如交易系统），则要谨慎使用索引。

维护策略

在某些情况下，你可能需要动态管理索引。比如在数据仓库环境中：

白天用户运行报表和查询时，索引至关重要。但在夜间加载数据时，索引会拖慢加载速度。一个常见的策略是：

夜间加载数据前删除索引
数据加载完成后重新创建索引
白天开始前确保所有索引都已就位


-- 数据加载前
DROP INDEX emp_name_idx ON employee;
 
-- 执行大量数据插入操作
-- ...
 
-- 数据加载后
CREATE INDEX emp_name_idx ON employee (name);

良好的索引和约束策略是保障数据库性能和数据质量的关键，应在设计阶段就加以规划。建议从主键、外键和常用查询条件等基础列开始建立索引，并根据实际查询情况定期分析和调整，避免过度索引，始终以“够用且高效”为原则，动态优化以适应业务变化。

记住，数据库优化是一个持续的过程。随着数据量的增长和使用模式的变化，你的索引和约束策略也需要相应调整。

实战演练

习题1：创建员工表索引

假设我们有一个员工表 employees，包含以下字段：

emp_id (INT, 主键)
first_name (VARCHAR(50), 非空)
last_name (VARCHAR(50), 非空)
department_id (INT, 外键)
salary (DECIMAL(10,2))
hire_date (DATE)

请为这个表创建合适的索引以优化以下查询：

根据姓名查找员工
根据部门和薪资范围查找员工
根据入职日期排序查询


-- 1. 为姓名查询创建复合索引
CREATE INDEX idx_employee_name ON employees (last_name, first_name);
 
-- 2. 为部门和薪资查询创建复合索引
CREATE INDEX idx_employee_dept_salary ON employees (department_id, salary);
 
-- 3. 为入职日期查询创建索引
CREATE INDEX idx_employee_hire_date ON employees (hire_date);

习题2：设计产品表约束

创建一个产品表 products，包含以下字段：

product_id (INT, 主键，自增)
name (VARCHAR(100), 非空)
category_id (INT, 外键引用categories表)
price (DECIMAL(8,2), 非空)
stock_quantity (INT, 非空)
sku (VARCHAR(20), 唯一)

请为这个表添加适当的约束，并解释每个约束的作用。


CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    stock_quantity INT NOT NULL,
    sku VARCHAR(20) NOT NULL,
 
    -- 主键约束：确保每条记录唯一标识

习题3：订单表索引优化

考虑一个订单表 orders，包含以下字段：

order_id (INT, 主键)
customer_id (INT, 外键)
order_date (DATETIME)
total_amount (DECIMAL(10,2))
status (ENUM('pending', 'processing', 'shipped', 'delivered'))

常见的查询模式包括：

查询特定客户的订单
查询特定日期范围内的订单
查询高价值订单（总金额大于1000）
统计各状态的订单数量

请设计最优的索引策略。


-- 1. 客户ID索引（外键查询优化）
CREATE INDEX idx_orders_customer ON orders (customer_id);
 
-- 2. 订单日期索引（时间范围查询）
CREATE INDEX idx_orders_date ON orders (order_date);
 
-- 3. 复合索引：客户+日期（用户订单历史查询）
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
 
-- 4. 订单金额索引（高价值订单查询）
CREATE INDEX idx_orders_amount ON orders (total_amount);
 
-- 5. 状态索引（订单状态统计）
CREATE INDEX idx_orders_status ON orders (status);

习题4：级联约束实战

有以下两个表结构：

部门表 departments：dept_id (INT, 主键), dept_name (VARCHAR(50), 唯一), manager_id (INT)
员工表 employees：emp_id (INT, 主键), name (VARCHAR(100)), dept_id (INT, 外键), salary (DECIMAL(8,2))

请创建这两个表，设置适当的级联约束，使得：

当部门被删除时，该部门的所有员工也被删除
当部门ID更新时，员工表的dept_id自动更新


-- 创建部门表
CREATE TABLE departments (
    dept_id INT NOT NULL AUTO_INCREMENT,
    dept_name VARCHAR(50) NOT NULL,
    manager_id INT,
 
    CONSTRAINT pk_departments PRIMARY KEY (dept_id),
    CONSTRAINT uk_departments_name UNIQUE (dept_name)
);
 
-- 创建员工表，设置级联约束
CREATE TABLE employees (
    emp_id INT NOT NULL AUTO_INCREMENT,

习题5：索引性能分析

分析以下学生表 students 的索引设计：表结构：

student_id (INT, 主键)
name (VARCHAR(50))
grade (INT, 年级 1-12)
class_id (INT, 班级ID)
gpa (DECIMAL(3,2))

现有查询：

SELECT * FROM students WHERE grade = 10 AND class_id = 5;
SELECT name, gpa FROM students WHERE grade BETWEEN 9 AND 12 ORDER BY gpa DESC;
SELECT COUNT(*) FROM students WHERE grade = ? GROUP BY class_id;

请判断是否需要创建新索引，并说明原因。


-- 当前索引（主键）：student_id
 
-- 需要添加的索引：
 
-- 1. 年级和班级复合索引（覆盖查询1）
CREATE INDEX idx_students_grade_class ON students (grade, class_id);
 
-- 2. 年级和GPA复合索引（覆盖查询2，需要同时排序）
CREATE INDEX idx_students_grade_gpa ON students (grade, gpa);
 
-- 3. 年级索引（如果查询1的复合索引不够用）
-- CREATE INDEX idx_students_grade ON students (grade);
 
-- 分析：
-- 查询1：复合索引 (grade, class_id) 可以完全覆盖
-- 查询2：复合索引 (grade, gpa) 支持范围查询和排序
-- 查询3：grade索引可以支持分组查询

习题6：约束冲突解决

有一个用户表 users 和角色表 roles：

用户表：user_id, username, email, role_id, created_at
角色表：role_id, role_name, permissions

当前约束：


ALTER TABLE users ADD CONSTRAINT fk_users_role
FOREIGN KEY (role_id) REFERENCES roles(role_id);

当尝试执行以下操作时会发生什么？如何解决？


-- 尝试删除管理员角色
DELETE FROM roles WHERE role_name = 'admin';


-- 问题：如果有用户被分配了管理员角色，删除会失败
 
-- 解决方案1：先更新用户角色，再删除
UPDATE users SET role_id = (SELECT role_id FROM roles WHERE role_name = 'user')
WHERE role_id = (SELECT role_id FROM roles WHERE role_name = 'admin');
 
DELETE FROM roles WHERE role_name = 'admin';
 
-- 解决方案2：设置级联删除（危险，不推荐）

索引与约束 | 自在学