1. 数据类型
1.1 数值类型
📌 设计原则
-
字段选择 越小越好（省内存，提高索引效率）
-
金额必须使用
DECIMAL -
自增主键推荐
BIGINT UNSIGNED -
状态字段使用 TINYINT，而不是 INT
1.2 字符串类型
📌 设计原则
-
避免盲目使用超长 VARCHAR（如 VARCHAR(255)）
-
超过 5000 字符使用 TEXT
-
密码存储使用 VARBINARY（哈希值）
-
不建议使用 ENUM/SET（扩展性差）
1.3 时间类型
📌 设计原则
-
默认推荐 DATETIME（范围更大，不受时区影响）
-
自动维护字段：
-
create_time→DEFAULT CURRENT_TIMESTAMP -
update_time→ON UPDATE CURRENT_TIMESTAMP
-
-
不要用字符串存时间
2. 索引类型与优化
2.1 索引类型对比
2.2 索引数据结构
⭐ B+Tree（MySQL 默认）
-
叶子节点存储所有数据（聚簇索引）
-
非叶子节点只存键
-
叶节点有指针形成链表，适合范围查询
⭐ 哈希索引（Memory 引擎）
-
精确查找极快 → O(1)
-
不支持范围查询
-
InnoDB 有 自适应哈希索引（AHI） 自动生成
2.3 索引设计准则
① WHERE 条件一定建索引
② JOIN 字段两端都要建索引
③ ORDER BY / GROUP BY 也要建索引
④ 区分度高字段优先（如身份证 > 性别）
2.4 联合索引优化（最左前缀原则）
例如：
INDEX (a, b, c)
可命中：
-
a
-
a, b
-
a, b, c
不能命中： -
b
-
b, c
-
c
2.5 前缀索引（缩短长字符串索引）
适用场景：手机号、邮箱、URL 等
ALTER TABLE user ADD INDEX idx_email(email(10));
优点：减少存储、提高缓存命中
缺点：区分度可能下降 → 需要评估前缀长度
2.6 索引避坑指南
❌ 不要给所有字段加索引
❌ WHERE 使用函数将导致索引失效
WHERE YEAR(create_time) = 2023 -- 索引失效
正确写法：
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
❌ 使用 LIKE '%xx' 不走索引
✔ 使用 LIKE 'xx%'
❌ NULL 可能导致索引失效
✔ 尽量 NOT NULL + 默认值
3. 字符集与排序规则
3.1 字符集选择
推荐配置
ALTER DATABASE xxx CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3.2 排序规则（Collation）
4. 表设计核心准则
4.1 三范式与反范式
✔ 三范式 NF
-
1NF：字段不可再分
-
2NF：消除部分依赖
-
3NF：消除传递依赖
✔ 反范式（为性能服务）
适用场景：
-
高频 JOIN → 冗余字段减少关联
-
报表系统 → 存储预计算数据
-
读多写少 → 缓存冗余字段
4.2 命名规范
-
表名：
t_order_detail -
主键：
id -
关联字段：
xxx_id -
时间字段：
-
created_at -
updated_at -
deleted_at / is_deleted（软删除）
-
4.3 字段规范
-
金额使用：DECIMAL(20,4)
-
所有表必须具备 3 个字段：
-
created_at -
updated_at -
is_deleted
-
-
大字段如 TEXT 单独拆表
4.4 分库分表策略
4.5 生产禁用操作
❌ 在线 ALTER 大表（用 pt-osc、gh-ost）
❌ SELECT *
❌ 无索引 UPDATE / DELETE
❌ 大事务
❌ 事务内执行 HTTP 请求
❌ 全表扫描
5. 性能优化实战案例（订单系统）
订单主表
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
order_no VARCHAR(32) NOT NULL COMMENT '订单号',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
total_amount DECIMAL(20,4) NOT NULL COMMENT '订单总额',
status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1待支付，2已支付 ...',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
is_deleted TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uk_order_no (order_no),
INDEX idx_user_id (user_id),
INDEX idx_status_ctime (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
订单明细表
CREATE TABLE order_items (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
order_id BIGINT UNSIGNED NOT NULL,
sku_id BIGINT UNSIGNED NOT NULL,
price DECIMAL(10,2) NOT NULL,
quantity INT UNSIGNED NOT NULL,
PRIMARY KEY (id),
INDEX idx_order_id (order_id),
FOREIGN KEY (order_id) REFERENCES orders(id)
) COMMENT='订单明细表';
⭐ 优化点分析
-
订单号创建 唯一索引（业务查询）
-
status + created_at组合索引用于按时间查订单 -
使用
DECIMAL保证金额精度 -
大字段独立存储（如收货地址表）
-
外键应用层维护（生产不建议使用物理外键）