外键约束与Cardinality_sql中cardinality-优快云博客

本文链接：https://blog.youkuaiyun.com/wenxuankeji/article/details/146353257

前言

在MySQL中，外键约束和**Cardinality（基数）**是优化数据库设计与性能的核心工具。外键确保数据关联的完整性，而Cardinality直接影响查询优化器的决策。

**一、外键约束

1.1 MySQL外键约束的实现基础

MySQL的外键约束依赖于InnoDB存储引擎，默认情况下，所有新表默认使用InnoDB。外键的语法和行为需满足以下条件：

父表和子表必须使用InnoDB引擎。
父表的被引用列必须是主键或唯一键。
子表的外键列数据类型需与父表的对应列一致（如INT对INT）。

1.2 `ON UPDATE` 和 `ON DELETE` 的MySQL实现

在MySQL中，外键的ON UPDATE和ON DELETE行为可通过FOREIGN KEY约束定义。以下是各选项的详细说明及示例：

选项	`ON UPDATE` 行为	`ON DELETE` 行为	MySQL语法	示例
CASCADE	父表字段更新时，子表外键值自动更新。	父表记录删除时，子表关联记录被级联删除。	`ON UPDATE CASCADE` `ON DELETE CASCADE`	`sql<br>ALTER TABLE employees<br>ADD FOREIGN KEY (dept_id)<br>REFERENCES departments(dept_id)<br>ON UPDATE CASCADE;<br>`
SET NULL	将子表外键值设为 `NULL`。	将子表外键值设为 `NULL`。	`ON UPDATE SET NULL` `ON DELETE SET NULL`	`sql<br>ALTER TABLE employees<br>ADD FOREIGN KEY (dept_id)<br>REFERENCES departments(dept_id)<br>ON DELETE SET NULL;<br>`
RESTRICT	阻止父表的更新操作，若子表存在关联记录。	阻止父表的删除操作，若子表存在关联记录。	`ON UPDATE RESTRICT` `ON DELETE RESTRICT`	默认行为，无需显式声明。
NO ACTION	检查操作是否导致外键无效，若无效则拒绝操作。	同上。	`ON UPDATE NO ACTION` `ON DELETE NO ACTION`	默认行为，与`RESTRICT`在MySQL中效果相同。

1.3 MySQL外键的创建与管理

1.3.1 创建带外键的表

-- 创建父表（部门表）
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

-- 创建子表（员工表），定义外键约束
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    -- 定义外键约束
    FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL
) ENGINE=InnoDB;

1.3.2 查看外键信息

-- 查看表的外键约束
SHOW CREATE TABLE employees;

-- 或使用INFORMATION_SCHEMA
SELECT 
    CONSTRAINT_NAME, 
    TABLE_NAME, 
    COLUMN_NAME, 
    REFERENCED_TABLE_NAME, 
    REFERENCED_COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
    TABLE_NAME = 'employees';

1.3.3 修改或删除外键约束

-- 添加外键约束
ALTER TABLE employees
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id)
REFERENCES departments(dept_id)
ON DELETE CASCADE;

-- 删除外键约束
ALTER TABLE employees
DROP FOREIGN KEY fk_emp_dept;

1.4 MySQL外键的性能与注意事项

性能影响：
- 级联操作（CASCADE）可能引发大量数据变更，需谨慎使用。
- 使用SET NULL时，确保外键列允许NULL（默认为NOT NULL需显式设置）。

MySQL的限制：

不支持ON UPDATE SET DEFAULT，需通过触发器实现：

-- 示例：更新父表时将子表外键设为默认值
DELIMITER $$
CREATE TRIGGER update_dept_id BEFORE UPDATE ON departments
FOR EACH ROW
BEGIN
    UPDATE employees 
    SET dept_id = DEFAULT 
    WHERE dept_id = OLD.dept_id;
END $$
DELIMITER ;

事务与一致性：
- 外键约束需在事务中执行，确保操作原子性。

二、Cardinality（基数）：MySQL的查询优化核心

2.1 Cardinality的定义与作用

Cardinality指某一列的不同值的数量，MySQL的优化器通过分析Cardinality选择最优的索引和查询计划。例如：

高基数列（如用户ID）：适合创建索引，加速查询。
低基数列（如性别）：索引效果差，可能增加写入开销。

2.2 MySQL中Cardinality的获取与更新

2.2.1 查看列的Cardinality

通过SHOW INDEX命令查看索引的Cardinality：

SHOW INDEX FROM employees WHERE Key_name = 'idx_dept_id';

2.2.2 更新统计信息

-- 更新表的统计信息
ANALYZE TABLE employees;

-- 强制优化器重新计算Cardinality
ANALYZE TABLE employees;

2.3 基于Cardinality的索引优化

2.3.1 高基数列的索引优化

-- 为用户ID（高基数）创建B-Tree索引
CREATE INDEX idx_user_id ON users(user_id);

-- 使用覆盖索引（避免回表）
SELECT user_id, name FROM users WHERE user_id = 123;

2.3.2 低基数列的优化策略

避免索引：直接使用WHERE条件过滤。

位图索引（需MySQL 8.0+）：

-- 创建位图索引（适用于低基数列）
CREATE INDEX idx_gender ON users(gender) USING BITMAP;

2.4 EXPLAIN分析Cardinality的影响

通过EXPLAIN查看查询计划中的Cardinality：

EXPLAIN SELECT * FROM employees WHERE dept_id = 10;

输出示例：

id | select_type | table    | type   | possible_keys | key     | key_len | ref  | **rows** | Extra
1  | SIMPLE      | employees| ref    | idx_dept_id   | idx_dept_id | 4       | const | 50      | Using where

rows：优化器估算的扫描行数，由Cardinality决定。

三、实战案例：电商系统设计

3.1 场景需求

设计一个电商系统，包含orders（订单表）和users（用户表），要求：

删除用户时保留订单记录，但标记为“已注销用户”。
确保订单表的user_id与用户表关联。

3.2 MySQL实现

-- 创建用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- 创建订单表，定义外键约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    -- 定义外键约束
    FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON DELETE SET NULL,  -- 删除用户时，订单的user_id设为NULL
    FOREIGN KEY (user_id)
    REFERENCES users(user_id)
    ON UPDATE CASCADE    -- 用户ID更新时，订单表同步更新
) ENGINE=InnoDB;

3.3 性能优化

-- 为订单表的user_id创建索引
CREATE INDEX idx_order_user ON orders(user_id);

-- 更新统计信息
ANALYZE TABLE orders;

四、常见问题与解决方案

4.1 外键约束冲突

问题：删除父表记录时，子表存在关联记录导致失败。

解决方案：

-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM departments WHERE dept_id = 10;
SET FOREIGN_KEY_CHECKS = 1;

4.2 Cardinality统计过时

问题：数据量变化后查询性能下降。

解决方案：

ANALYZE TABLE orders;  -- 强制更新统计信息

4.3 MySQL不支持`ON UPDATE SET DEFAULT`的替代方案

-- 使用触发器实现
DELIMITER $$
CREATE TRIGGER trg_update_user_id
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET user_id = NEW.user_id 
    WHERE user_id = OLD.user_id;
END $$
DELIMITER ;

五、总结：MySQL的外键与Cardinality最佳实践

场景	建议
数据完整性	始终使用InnoDB引擎，并定义外键约束。
删除操作	根据业务需求选择`CASCADE`或`SET NULL`，避免`RESTRICT`导致操作失败。
索引优化	高基数列使用B-Tree索引，低基数列考虑位图索引或避免索引。
性能监控	定期执行`ANALYZE TABLE`，并通过`EXPLAIN`分析查询计划。

附录：MySQL配置与工具

5.1 启用外键支持

-- 查看外键检查状态
SELECT @@FOREIGN_KEY_CHECKS;

-- 启用外键检查（默认为ON）
SET FOREIGN_KEY_CHECKS = 1;

5.2 使用pt-online-schema-change修改外键

pt-online-schema-change --alter "ADD CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL" D=dbname,t=orders --execute