约束与索引

约束用于保证数据的完整性，索引用于提高查询性能。

约束 (Constraints)

约束是对表中数据的限制规则，确保数据的准确性和可靠性。

约束类型

约束	说明	作用
PRIMARY KEY	主键	唯一标识每行，不能为 NULL
UNIQUE	唯一约束	值不能重复，可以为 NULL
NOT NULL	非空约束	值不能为 NULL
DEFAULT	默认值	未指定值时使用默认值
CHECK	检查约束	限制列的取值范围
FOREIGN KEY	外键	关联另一个表的主键

PRIMARY KEY 主键

主键是表中每行的唯一标识符。

-- 列级定义
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);
 
-- 表级定义
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);
 
-- 复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);
 
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);
 
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;

UNIQUE 唯一约束

-- 列级定义
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE
);
 
-- 表级定义（可命名）
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100),
    CONSTRAINT uk_email UNIQUE (email)
);
 
-- 复合唯一约束
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE (user_id, role_id)
);
 
-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT uk_phone UNIQUE (phone);
 
-- 删除唯一约束
ALTER TABLE users DROP INDEX uk_email;

NOT NULL 非空约束

-- 定义非空
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL
);
 
-- 添加非空约束
ALTER TABLE users MODIFY COLUMN name VARCHAR(50) NOT NULL;
 
-- 删除非空约束
ALTER TABLE users MODIFY COLUMN name VARCHAR(50) NULL;

DEFAULT 默认值

-- 定义默认值
CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    balance DECIMAL(10,2) DEFAULT 0.00
);
 
-- 添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
 
-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

CHECK 检查约束

-- MySQL 8.0.16+ 支持 CHECK 约束
CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10,2) CHECK (salary > 0),
    gender CHAR(1) CHECK (gender IN ('M', 'F'))
);
 
-- 命名 CHECK 约束
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2),
    discount DECIMAL(3,2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_discount CHECK (discount >= 0 AND discount <= 1)
);
 
-- 添加 CHECK 约束
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);
 
-- 删除 CHECK 约束
ALTER TABLE employees DROP CONSTRAINT chk_age;

FOREIGN KEY 外键

外键用于建立表之间的关联关系。

-- 创建外键
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
 
-- 命名外键
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
);
 
-- 添加外键
ALTER TABLE orders 
ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
 
-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user;

外键动作

-- ON DELETE: 父表删除时的动作
-- ON UPDATE: 父表更新时的动作
 
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE      -- 级联删除
        ON UPDATE CASCADE      -- 级联更新
);

动作	说明
CASCADE	级联操作（父表操作时，子表跟随）
SET NULL	设为 NULL
SET DEFAULT	设为默认值
RESTRICT	拒绝操作（默认）
NO ACTION	同 RESTRICT

-- 示例：用户删除时，订单的 user_id 设为 NULL
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

索引 (Index)

索引是数据库中用于加速查询的数据结构。

索引类型

类型	说明	特点
B-Tree	默认索引	支持范围查询、排序
Hash	哈希索引	仅支持等值查询，更快
Full-Text	全文索引	用于文本搜索
Spatial	空间索引	用于地理数据

创建索引

-- 创建普通索引
CREATE INDEX idx_name ON users(name);
 
-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);
 
-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);
 
-- 建表时创建索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    INDEX idx_name (name),
    UNIQUE INDEX idx_email (email),
    INDEX idx_name_age (name, age)
);
 
-- 添加索引
ALTER TABLE users ADD INDEX idx_name (name);
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

查看索引

-- 查看表的所有索引
SHOW INDEX FROM users;
 
-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE name = '张三';

删除索引

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

前缀索引

对于长字符串，可以只索引前 N 个字符。

-- 只索引 email 的前 10 个字符
CREATE INDEX idx_email ON users(email(10));
 
-- 查找合适的前缀长度
SELECT 
    COUNT(DISTINCT email) AS full,
    COUNT(DISTINCT LEFT(email, 5)) AS prefix5,
    COUNT(DISTINCT LEFT(email, 10)) AS prefix10,
    COUNT(DISTINCT LEFT(email, 15)) AS prefix15
FROM users;

复合索引

-- 创建复合索引
CREATE INDEX idx_dept_salary ON employees(department_id, salary);
 
-- 复合索引遵循最左前缀原则
-- ✅ 可以使用索引
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM employees WHERE department_id = 1 AND salary > 10000;
 
-- ❌ 无法使用索引（没有最左列）
SELECT * FROM employees WHERE salary > 10000;

覆盖索引

当索引包含查询所需的所有列时，无需回表查询。

-- 创建复合索引
CREATE INDEX idx_name_email ON users(name, email);
 
-- 覆盖索引查询（只查索引中的列）
SELECT name, email FROM users WHERE name = '张三';
-- Extra: Using 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('+必须 -排除' IN BOOLEAN MODE);

索引使用原则

应该创建索引的场景

-- 1. 主键和外键
-- 2. 频繁用于 WHERE 条件的列
SELECT * FROM users WHERE email = 'test@example.com';
 
-- 3. 用于 ORDER BY 的列
SELECT * FROM orders ORDER BY created_at DESC;
 
-- 4. 用于 JOIN 的列
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
 
-- 5. 用于 GROUP BY 的列
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

不应该创建索引的场景

-- 1. 数据量很小的表
-- 2. 频繁更新的列
-- 3. 区分度很低的列（如性别）
-- 4. 很少用于查询的列

索引失效的情况

-- 1. 对索引列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;  -- ❌
SELECT * FROM users WHERE created_at >= '2024-01-01';  -- ✅
 
-- 2. 使用 OR 连接非索引列
SELECT * FROM users WHERE name = '张三' OR age = 25;  -- 可能失效
 
-- 3. LIKE 以通配符开头
SELECT * FROM users WHERE name LIKE '%三';  -- ❌
SELECT * FROM users WHERE name LIKE '张%';  -- ✅
 
-- 4. 隐式类型转换
SELECT * FROM users WHERE phone = 13800138000;  -- ❌ phone 是字符串
SELECT * FROM users WHERE phone = '13800138000';  -- ✅
 
-- 5. 使用 != 或 NOT IN
SELECT * FROM users WHERE status != 'active';  -- 可能失效
 
-- 6. IS NULL / IS NOT NULL（取决于数据分布）

查看执行计划

-- 使用 EXPLAIN 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
 
-- 关键字段说明
-- type: 访问类型（性能从好到差）
--   system > const > eq_ref > ref > range > index > ALL
-- key: 实际使用的索引
-- rows: 预估扫描行数
-- Extra: 额外信息

type	说明
const	主键或唯一索引等值查询
eq_ref	JOIN 时使用主键或唯一索引
ref	非唯一索引等值查询
range	索引范围查询
index	全索引扫描
ALL	全表扫描（最差）

总结

约束

约束	用途	可否为 NULL
PRIMARY KEY	唯一标识	不可
UNIQUE	唯一值	可
FOREIGN KEY	表关联	可
NOT NULL	非空	-
CHECK	值校验	-
DEFAULT	默认值	-

索引最佳实践

合理使用复合索引，遵循最左前缀原则
避免冗余索引，定期清理无用索引
使用 EXPLAIN 分析查询性能
避免索引失效的写法

上一节：06-数据定义语言 下一节：08-事务与并发控制 — ACID 与事务隔离

sql 约束索引数据库设计

Muliminty Note

探索

07-约束与索引

约束与索引

约束 (Constraints)

约束类型

PRIMARY KEY 主键

UNIQUE 唯一约束

NOT NULL 非空约束

DEFAULT 默认值

CHECK 检查约束

FOREIGN KEY 外键

外键动作

索引 (Index)

索引类型

创建索引

查看索引

删除索引

前缀索引

复合索引

覆盖索引

全文索引

索引使用原则

应该创建索引的场景

不应该创建索引的场景

索引失效的情况

查看执行计划

总结

约束

索引最佳实践

关系图谱

目录

反向链接