database-design

name database-design description 数据库设计规范与优化指南 version 1.0.0 category development triggers ["database design","数据库设计","SQL 优化","表设计","数据库规范"] scriptPath check-db.sh scriptType bash autoExecute true scriptTimeout 10 数据库设计规范技能包 设计高效、可维护的数据库结构。 表设计原则 1. 命名规范 表名 ： 小写字母 + 下划线 使用复数形式 示例： users , order_items , user_profiles 字段名 ： 小写字母 + 下划线 见名知意 示例： created_at , user_id , email_address 索引名 ： -- 主键：pk_表名 PRIMARY KEY pk_users -- 唯一索引：uk_表名_字段名 UNIQUE INDEX uk_users_email -- 普通索引：idx_表名_字段名 INDEX idx_users_created_at -- 外键：fk_表名_关联表名 FOREIGN KEY fk_orders_users 2. 字段类型选择 数据类型 使用场景 示例 INT/BIGINT ID、数量、年龄 user_id BIGINT VARCHAR 变长字符串 name VARCHAR(100) CHAR 定长字符串 country_code CHAR(2) TEXT 长文本 description TEXT DECIMAL 金额、精确数值 price DECIMAL(10,2) DATETIME 日期时间 created_at DATETIME ENUM 固定选项 status ENUM('active','inactive') BOOLEAN 布尔值 is_deleted BOOLEAN 注意 ： 避免使用 FLOAT/DOUBLE 存储金额 VARCHAR 长度设置合理（避免过大） 时间字段统一使用 DATETIME 或 TIMESTAMP 3. 主键设计 推荐 ： -- 自增ID（适合单机） id BIGINT AUTO_INCREMENT PRIMARY KEY -- 雪花ID（适合分布式） id BIGINT PRIMARY KEY COMMENT '雪花ID' -- UUID（分布式场景） id CHAR ( 36 ) PRIMARY KEY COMMENT 'UUID' 避免 ： 业务字段作为主键（如手机号、邮箱） 联合主键（复杂度高） 4. 外键约束 使用外键的优势 ： 数据完整性保证 级联操作 不使用外键的场景 ： 高并发系统（性能考虑） 分库分表场景 微服务架构 -- 外键示例 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE 索引设计 1. 索引类型 主键索引 ： PRIMARY KEY (id) 唯一索引 ： UNIQUE INDEX uk_users_email (email) 普通索引 ： INDEX idx_users_created_at (created_at) 联合索引 （遵循最左前缀原则）： INDEX idx_users_name_age (name, age) -- 可以走索引: WHERE name = 'xxx' -- 可以走索引: WHERE name = 'xxx' AND age = 25 -- 不走索引: WHERE age = 25 全文索引 ： FULLTEXT INDEX ft_articles_content (content) 2. 索引优化原则 何时创建索引 ： ✓ WHERE 子句常用字段 ✓ ORDER BY 字段 ✓ JOIN 关联字段 ✓ 查询频率高的字段 何时避免索引 ： ✗ 数据重复度高的字段（如性别） ✗ 频繁更新的字段 ✗ 小表（全表扫描更快） 索引失效场景 ： -- ❌ 在索引列上使用函数 WHERE YEAR (created_at) = 2025 -- ✓ 改为 WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01' -- ❌ 模糊查询前导模糊 WHERE name LIKE '%张%' -- ✓ 改为 WHERE name LIKE '张%' -- ❌ 类型转换 WHERE user_id = '123' -- user_id 是 INT -- ✓ 改为 WHERE user_id = 123 SQL 优化 1. 查询优化 **避免 SELECT ***： -- ❌ 不推荐 SELECT * FROM users; -- ✓ 推荐 SELECT id, name, email FROM users; 使用 LIMIT ： SELECT id, name FROM users LIMIT 100 ; 避免 N+1 查询 ： -- ❌ N+1 问题 SELECT * FROM orders; -- 查询 N 个订单 -- 然后循环查询每个订单的用户 SELECT * FROM users WHERE id = ?; -- ✓ 使用 JOIN SELECT o. * , u.name FROM orders o JOIN users u ON o.user_id = u.id; 2. 分页优化 传统分页 （大偏移量性能差）： SELECT * FROM users LIMIT 100000 , 20 ; 优化方案（使用 ID 范围） ： SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20 ; 使用覆盖索引 ： SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at LIMIT 20 ; 3. COUNT 优化 避免 COUNT(*) ： -- ❌ 慢 SELECT COUNT ( * ) FROM users WHERE status = 'active' ; -- ✓ 使用近似值（如缓存） -- 或者定期统计存到另一个表 范式设计 第一范式（1NF） 每个字段都是不可分割的原子值 第二范式（2NF） 消除部分依赖（非主键字段完全依赖主键） 第三范式（3NF） 消除传递依赖（非主键字段不依赖其他非主键字段） 反范式化 为了性能适当冗余数据： -- 订单表冗余用户名（避免频繁JOIN） CREATE TABLE orders ( id BIGINT PRIMARY KEY , user_id BIGINT , user_name VARCHAR ( 100 ), -- 冗余字段 total_amount DECIMAL ( 10 , 2 ) ); 常用字段 标准字段 id BIGINT AUTO_INCREMENT PRIMARY KEY , created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , is_deleted BOOLEAN DEFAULT FALSE , created_by BIGINT , updated_by BIGINT 乐观锁 version INT NOT NULL DEFAULT 0 软删除 deleted_at DATETIME NULL , is_deleted BOOLEAN DEFAULT FALSE 分库分表 垂直拆分 按业务模块拆分表 水平拆分 -- 按 user_id 取模 user_0, user_1, user_2, ... -- 按时间分表 order_202501, order_202502, ... 最佳实践 必须字段 ：id, created_at, updated_at 统一字符集 ：utf8mb4 统一时区 ：UTC 避免 NULL ：尽量使用 NOT NULL + DEFAULT 合理使用注释 ：COMMENT '字段说明' 定期备份 ：自动化备份机制 监控慢查询 ：开启 slow_query_log