一、SQL索引与约束的基本概念

1.1 SQL索引（Index）

索引是数据库中的物理数据结构（如B+树、Hash表），主要作用是加速数据检索。索引会占用额外的存储空间，并在数据修改时增加维护成本。

• 本质：物理数据结构（B+树/Hash等）
• 目标：加速数据检索
• 特点：占用存储空间，维护成本随数据修改增加

1.2 SQL约束（Constraint）

约束是数据库中的逻辑规则，用于保障数据完整性和一致性。约束不占用物理存储空间，只在数据操作时进行校验。

-- 约束确保数据质量
CREATE TABLE users (
    id INT PRIMARY KEY,       -- 主键约束
    email VARCHAR(100) UNIQUE -- 唯一约束
);

• 本质：逻辑规则
• 目标：保障数据正确性与关联性
• 特点：存储在元数据中，不占用数据空间

1.3 SQL索引与约束的区别

	索引	约束
本质	物理数据结构	逻辑规则
存储占用	✅ 独立存储结构	❌ 仅元数据
创建示例	CREATE INDEX idx ON tbl(col)	PRIMARY KEY (id)

二、SQL索引的分类

2.1 按数据结构分类

类型	语法示例	适用场景
B+树索引	INDEX idx_age (age)	范围查询(>、<、BETWEEN)
自适应Hash索引	自动由InnoDB管理	内存中的等值查询(=、IN)
全文索引	FULLTEXT ftxt (content)	文本关键词搜索

2.2 按物理存储分类

类型	关联对象	数据存储特点
聚集索引	主键	索引与行数据共存于B+树叶子节点
辅助索引	普通/唯一索引	仅存储主键值，需回表查询

2.3 按列属性分类

类型	语法	特性
主键索引	PRIMARY KEY (id)	非空唯一，每表只有一个
唯一索引	UNIQUE INDEX uni_mail(email)	允许一个NULL值
普通索引	INDEX idx_phone(phone)	允许重复值和NULL
前缀索引	INDEX idx_name(name(10))	只比较前N个字符

2.4 按列数量分类

类型	语法	最左匹配原则
单列索引	INDEX idx_email (email)	-
组合索引	INDEX idx_comp (age,city)	✅ 必须从最左列开始使用

三、SQL约束的分类

3.1 主键约束（PRIMARY KEY）

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY
);

• 作用：唯一标识行，禁止NULL
• 特点：自动创建聚集索引

3.2 唯一约束（UNIQUE）

CREATE TABLE employees (
    email VARCHAR(100) UNIQUE
);

• 作用：保证列值唯一
• 特点：自动创建唯一索引

3.3 外键约束（FOREIGN KEY）

CREATE TABLE orders (
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

• 级联操作：CASCADE | SET NULL | RESTRICT | NO ACTION
• 特点：自动在子表创建索引

3.4 非空约束（NOT NULL）

CREATE TABLE products (
    name VARCHAR(200) NOT NULL
);

• 作用：强制字段必须有值
• 优势：提升IS NOT NULL查询效率

3.5 默认约束（DEFAULT）

CREATE TABLE logs (
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

• 动态值支持：CURRENT_TIMESTAMP、UUID()等

3.6 CHECK约束

CREATE TABLE students (
    age TINYINT, 
    -- MySQL中仅语法支持，实际需应用层校验
    CHECK (age >= 18) 
);

• 注意：MySQL中不强制生效，需通过程序或触发器实现

四、SQL约束与索引的联合应用

4.1 主要协同关系

约束类型	自动创建索引	协同优势
主键约束	聚集索引	数据物理有序存储
唯一约束	唯一索引	快速查找重复值
外键约束	普通索引	加速级联操作和JOIN查询

4.2 联合应用语法

主键+聚集索引

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)  -- 自动创建聚集索引
);

外键+索引优化

CREATE TABLE orders (
    user_id INT,
    product_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id), -- 自动建索引
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_user_product (user_id, product_id) -- 组合索引
);

4.3 高级应用场景

会员系统（多约束+索引）

CREATE TABLE members (
    member_id CHAR(12) PRIMARY KEY,  
    phone VARCHAR(20) NOT NULL UNIQUE,  
    level TINYINT NOT NULL DEFAULT 1
);

-- 添加查询索引
CREATE INDEX idx_level ON members(level);

五、模拟案例：银行账户系统

5.1 创建表结构

-- 账户表（主键约束+聚集索引）
CREATE TABLE accounts (
    account_id CHAR(16) PRIMARY KEY,
    holder_name VARCHAR(50) NOT NULL,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    INDEX idx_holder (holder_name)  -- 户名索引
);

-- 交易表（外键+索引）
CREATE TABLE transactions (
    tx_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    from_account CHAR(16) NOT NULL,
    to_account CHAR(16) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    tx_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_account) REFERENCES accounts(account_id),
    FOREIGN KEY (to_account) REFERENCES accounts(account_id),
    INDEX idx_tx_time (tx_time)  -- 时间索引
);

5.2 插入测试数据

INSERT INTO accounts (account_id, holder_name, balance) VALUES 
    ('622588****1234', '张三', 10000.00),
    ('622588****5678', '李四', 5000.00);

INSERT INTO transactions (from_account, to_account, amount) VALUES
    ('622588****1234', '622588****5678', 1000.00),
    ('622588****5678', '622588****1234', 500.00);

5.3 验证联合效果

场景1：外键约束阻止非法删除

-- 尝试删除有交易的账户
DELETE FROM accounts WHERE account_id = '622588****1234';
-- 报错：Cannot delete or update a parent row: a foreign key constraint fails

场景2：索引加速时间范围查询

-- 查询最近24小时交易
EXPLAIN SELECT * FROM transactions 
WHERE tx_time > NOW() - INTERVAL 1 DAY;  
-- 输出：Using index idx_tx_time

场景3：唯一约束防止重复账户

-- 尝试插入重复卡号
INSERT INTO accounts VALUES ('622588****1234', '王五', 2000.00);
-- 报错：Duplicate entry '622588****1234' for key 'PRIMARY'

六、避坑指南

问题场景	错误示例	解决方案
外键字段未建索引	FOREIGN KEY无索引	显式创建索引
级联删除风险	ON DELETE CASCADE核心数据	应用层逻辑控制删除
唯一索引含NULL值	UNIQUE(email)含多个NULL	配合NOT NULL约束
长文本建完整索引	INDEX(content)长文本	改用前缀索引INDEX(content(20))
过度索引	单表15个索引	控制在5-8个以内

七、总结

1. 设计铁律
2. 每表必须显式定义主键
3. 外键字段必须建索引
4. 高频查询字段建索引
5. 性能平衡法则操作约束作用索引影响INSERT/UPDATE保证数据质量降低写入速度SELECT无直接影响提升查询速度10-1000倍DELETE阻止非法删除加速定位记录
6. 企业级实践指南graph LR
   A[定义主键] --> B[设置外键关系]
   B --> C[为外键建索引]
   C --> D[按需添加查询索引]
   D --> E[约束在前索引在后]