MySQL数据库设计规范与性能优化全攻略：从表结构到SQL调优

为你整理一份完整的数据库规范手册。这不仅仅是规则列表，更是为什么需要这些规则的深度思考。

一、为什么数据库规范比业务代码规范更重要？

1.1 一个真实的拖垮数据库的SQL

-- 这是“订单表”设计
CREATE TABLE `order` (
`id` int(11) NOT NULL,
`order_no` varchar(50) DEFAULT NULL,  -- 订单号，没加唯一索引！
`user_id` int(11) DEFAULT NULL,       -- 用户ID，没加索引！
`amount` decimal(10,2) DEFAULT NULL,
`status` int(11) DEFAULT NULL,        -- 状态，没加索引！
`create_time` datetime DEFAULT NULL,  -- 创建时间，没加索引！
`pay_time` datetime DEFAULT NULL,
-- ... 还有30多个字段
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 高峰期每秒执行1000次的查询
SELECT * FROM `order`
WHERE `user_id` = 123456
AND `status` = 2
AND `create_time` > '2023-11-10'
ORDER BY `create_time` DESC
LIMIT 20;

为什么这个查询会拖垮数据库？

1. 全表扫描：user_id、status、create_time都没有索引，MySQL必须扫描全部500万行数据
2. 排序成本高：ORDER BY create_time DESC需要对所有匹配的行进行排序
3. 内存消耗大：500万行数据加载到内存进行过滤和排序
4. 锁竞争激烈：大量查询同时进行，行锁、表锁竞争激烈

根本原因不是技术问题，是规范问题：

• 设计表时没有考虑查询模式
• 索引添加没有规划
• 字段类型选择随意
• 没有性能测试意识

二、表设计规范：从第一行SQL开始就正确

2.1 命名规范：一致性是协作的基础

为什么命名要规范？

当团队有10个开发人员，每个人用不同的命名风格时：

• 新成员需要学习10种不同的命名习惯
• 写联表查询时不知道字段对应关系
• 重构时不敢轻易修改字段名

-- ❌ 混乱的命名（真实项目见过的情况）
CREATE TABLE `user_info` (      -- 表名：下划线
`UserID` int(11) NOT NULL,    -- 字段名：驼峰
`user_name` varchar(50),      -- 字段名：下划线
`userEmail` varchar(100),     -- 字段名：驼峰
`user_phone_number` varchar(20), -- 字段名：下划线，但太长了
`created_at` datetime,        -- 创建时间：下划线+at
`updateTime` datetime         -- 更新时间：驼峰
);

-- ✅ 统一的命名规范
CREATE TABLE `user` (                    -- 表名：单数，小写
`id` bigint(20) NOT NULL,              -- 主键：id，不是user_id
`username` varchar(50) NOT NULL,       -- 字段名：全小写，下划线分隔
`email` varchar(100) NOT NULL,         -- 简洁明了
`phone` varchar(20) DEFAULT NULL,      -- 不要带表名前缀
`create_time` datetime NOT NULL,       -- 创建时间：create_time
`update_time` datetime NOT NULL -- 更新时间：update_time
);

命名规范的核心原则：

1. 表名：使用复数形式还是单数形式？
   • 推荐单数：user而不是users
   • 理由：一张表存储的是一种实体的集合，这个实体是单数概念
   • 示例：order（订单）、product（商品）、category（分类）
2. 字段名：要不要带表名前缀？
   • 不要带前缀：username而不是user_name
   • 理由：在SQL中通过表名.字段名访问，不需要重复表名
   • 例外：关联表中的外键字段，如order表中的user_id
3. 使用小写和下划线：
   • 为什么：MySQL在Linux下默认区分大小写，统一小写避免问题
   • 跨平台兼容：Windows不区分大小写，Linux区分，统一小写最安全

2.2 字段类型选择：空间、性能、精度的平衡艺术

为什么字段类型如此重要？

1. 存储空间：错误的类型会浪费大量磁盘空间
2. 查询性能：类型影响索引大小和查询效率
3. 数据精度：数值类型选择错误会导致精度丢失

-- ❌ 常见的类型选择错误

-- 1. 过度使用VARCHAR
`status` varchar(10) DEFAULT 'active'  -- 应该用TINYINT
`age` varchar(3) DEFAULT NULL          -- 应该用TINYINT UNSIGNED
`is_deleted` varchar(1) DEFAULT '0'    -- 应该用TINYINT(1)

-- 2. 数值类型范围错误
`views` int(11) DEFAULT 0              -- 可能溢出，应该用BIGINT
`price` float DEFAULT NULL             -- 精度问题，应该用DECIMAL

-- 3. 时间类型错误
`create_time` varchar(20) DEFAULT NULL -- 应该用DATETIME或TIMESTAMP

-- ✅ 正确的类型选择
CREATE TABLE `product` (
`id` bigint(20) NOT NULL,                     -- 主键用BIGINT，避免溢出
`name` varchar(200) NOT NULL,                 -- 适度长度，不要过度预留
`category_id` int(11) NOT NULL,               -- 外键用INT，通常足够
`price` decimal(10,2) NOT NULL,               -- 金额用DECIMAL，保证精度
`stock` int(11) NOT NULL DEFAULT '0',         -- 库存用INT
`status` tinyint(1) NOT NULL DEFAULT '1',     -- 状态用TINYINT
`weight` float DEFAULT NULL,                  -- 重量用FLOAT，允许小数
`description` text,                           -- 长文本用TEXT
`tags` json DEFAULT NULL,                     -- 标签用JSON类型（MySQL 5.7+）
`is_hot` tinyint(1) NOT NULL DEFAULT '0',     -- 是否热门：0或1
`view_count` bigint(20) NOT NULL DEFAULT '0', -- 浏览量可能很大，用BIGINT
`create_time` datetime NOT NULL,              -- 创建时间
`update_time` datetime NOT NULL -- 更新时间
);

字段类型选择指南：

数据类型	存储空间	适用场景	注意事项
TINYINT	1字节	状态字段（0/1）、枚举值	范围：-128~127，无符号：0~255
INT	4字节	外键、计数、ID	范围足够大多数场景
BIGINT	8字节	主键ID、大数量计数	主键推荐使用，避免未来溢出
DECIMAL(M,D)	变长	金额、精确小数	M是总位数，D是小数位数
VARCHAR(N)	变长	字符串，N<=255用VARCHAR	不要过度预留（如varchar(500)存用户名）
TEXT	变长	长文本、文章内容	避免SELECT *，性能较差
DATETIME	8字节	需要存储的时间	范围：1000-01-01到9999-12-31
TIMESTAMP	4字节	自动更新时间戳	范围：1970-01-01到2038-01-19

为什么主键要用BIGINT而不是INT？

1. 未来扩展性：INT最大21亿，BIGINT最大922亿亿
2. 分布式ID：雪花算法生成的ID是64位，需要BIGINT
3. 成本考虑：BIGINT只比INT多4字节，但避免未来重构

2.3 默认值和NOT NULL：数据完整性的第一道防线

为什么要有默认值和NOT NULL约束？

-- ❌ 没有默认值和NOT NULL的问题
CREATE TABLE `order` (
`status` int(11) DEFAULT NULL,      -- 可能为NULL
`total_amount` decimal(10,2) DEFAULT NULL
);

-- 插入数据时
INSERT INTO `order` (`order_no`) VALUES ('ORD001');
-- 此时status和total_amount都是NULL

-- 查询时出现问题
SELECT SUM(total_amount) FROM `order`;  -- 结果为NULL！
SELECT * FROM `order` WHERE status = 1; -- 会漏掉status为NULL的记录

-- ✅ 正确的做法
CREATE TABLE `order` (
`status` tinyint(1) NOT NULL DEFAULT '1',  -- 默认待支付
`total_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0' -- 软删除标志
);

NOT NULL的好处：

1. 查询优化：MySQL更容易优化NOT NULL字段的查询
2. 索引效率：NULL值不会进入索引（除非是唯一索引）
3. 数据一致性：避免程序中出现NPE（NullPointerException）
4. 统计准确：SUM、AVG等聚合函数结果准确

默认值的设计原则：

1. 状态字段：设置合理的初始状态，如订单状态默认为1（待支付）
2. 数值字段：默认为0，避免NULL参与计算的问题
3. 时间字段：创建时间可以在业务层设置，更新时间用ON UPDATE CURRENT_TIMESTAMP
4. 软删除字段：默认为0（未删除）

2.4 字符集和排序规则：中文和Emoji的兼容性

为什么字符集如此重要？

2018年，某知名APP因为不支持Emoji表情，导致用户昵称显示为问号，大量用户投诉。

-- ❌ 过时的字符集配置
CREATE TABLE `comment` (
`content` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  -- 注意：MySQL的utf8不是真正的UTF-8！

-- 用户评论：'这个产品真棒！👍'
-- 存储结果：'这个产品真棒！??'

-- ✅ 正确的字符集配置
CREATE TABLE `comment` (
`content` varchar(500) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_unicode_ci;

-- 存储结果：'这个产品真棒！👍'  -- 完美支持

字符集选择指南：

1. 永远使用utf8mb4：
   • MySQL的utf8只支持最多3字节的字符（基本多文种平面）
   • utf8mb4支持4字节字符（包括Emoji、生僻汉字）
   • 存储空间：Emoji需要4字节，utf8mb4完美支持
2. 排序规则选择：
   • utf8mb4_unicode_ci：基于Unicode标准排序，支持多语言
   • utf8mb4_general_ci：旧版排序规则，性能稍好但不准确
   • 推荐：utf8mb4_unicode_ci，准确性更重要
3. 存储空间考虑：
   • utf8mb4比utf8每个字符多1字节
   • 对于10亿条记录，每个VARCHAR(100)字段多占用100GB空间
   • 但这是必要的代价：用户体验比存储空间更重要

三、索引设计规范：查询性能的关键

3.1 索引的基本原理：为什么索引能加速查询？

没有索引的查询（全表扫描）：

想象一下：在一本没有目录的书中找特定内容
1. 从第一页开始，一页一页翻
2. 检查每一行是否满足条件
3. 找到所有匹配的行
时间复杂度：O(n)，n是表的总行数

有索引的查询（索引查找）：

想象一下：在一本有目录的书中找特定内容
1. 查目录，找到关键词所在的页码
2. 直接翻到那一页
3. 找到具体内容
时间复杂度：O(log n)，使用B+树索引

3.2 哪些字段需要索引？一个实用的决策框架

索引添加的决策流程：

-- 示例：用户表常见的查询场景
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`username` varchar(50) NOT NULL,      -- 场景1：按用户名登录
`email` varchar(100) NOT NULL,        -- 场景2：按邮箱登录
`phone` varchar(20) DEFAULT NULL,     -- 场景3：按手机号登录
`status` tinyint(1) NOT NULL DEFAULT '1', -- 场景4：按状态查询
`create_time` datetime NOT NULL,      -- 场景5：按时间范围查询
`company_id` bigint(20) DEFAULT NULL -- 场景6：按公司筛选
);

-- 分析查询场景，决定索引策略

索引决策矩阵：

字段	查询频率	选择性	索引类型	理由
id	高	100%	主键索引	主键，自动创建
username	高	高	唯一索引	登录场景，高频率
email	中	高	唯一索引	备用登录方式
phone	中	高	普通索引	可能为空，但查询多
status	高	低	普通索引	低选择性，但查询频率高
create_time	中	中	普通索引	时间范围查询
company_id	低	低	普通索引	关联查询需要

选择性计算：

-- 选择性 = 不同值的数量 / 总行数
SELECT
COUNT(DISTINCT status) / COUNT(*) as status_selectivity,
COUNT(DISTINCT company_id) / COUNT(*) as company_selectivity
FROM `user`;

-- 结果示例：
-- status_selectivity = 0.001 (只有3种状态，选择性差)
-- company_selectivity = 0.8   (用户分布在不同公司，选择性好)

索引创建示例：

-- 基础索引
ALTER TABLE `user`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `uk_username` (`username`),
ADD UNIQUE KEY `uk_email` (`email`),
ADD KEY `idx_phone` (`phone`),
ADD KEY `idx_status` (`status`),
ADD KEY `idx_create_time` (`create_time`),
ADD KEY `idx_company_id` (`company_id`);

-- 复合索引：针对特定查询模式
-- 查询：SELECT * FROM user WHERE status = 1 AND company_id = 100 ORDER BY create_time DESC
ADD KEY `idx_status_company_time` (`status`, `company_id`, `create_time` DESC);

3.3 复合索引设计：最左前缀原则的深度理解

什么是复合索引？

• 在多个列上建立的索引
• 索引按照列的顺序存储
• 遵循最左前缀原则

最左前缀原则示例：

-- 创建复合索引
CREATE INDEX idx_a_b_c ON table1(a, b, c);

-- 哪些查询能用上索引？
SELECT * FROM table1 WHERE a = 1;                    -- ✅ 能用索引（使用a）
SELECT * FROM table1 WHERE a = 1 AND b = 2;          -- ✅ 能用索引（使用a,b）
SELECT * FROM table1 WHERE a = 1 AND b = 2 AND c = 3; -- ✅ 能用索引（使用a,b,c）
SELECT * FROM table1 WHERE b = 2;                    -- ❌ 不能用索引（没有a）
SELECT * FROM table1 WHERE b = 2 AND c = 3;          -- ❌ 不能用索引（没有a）
SELECT * FROM table1 WHERE a = 1 AND c = 3;          -- ⚠️ 部分使用索引（只使用a）

复合索引设计实战：

-- 场景：订单表常见查询
CREATE TABLE `order` (
`user_id` bigint(20) NOT NULL,
`status` tinyint(1) NOT NULL,
`create_time` datetime NOT NULL,
`amount` decimal(10,2) NOT NULL
);

-- 查询1：用户的所有订单，按时间倒序
-- SELECT * FROM order WHERE user_id = 123 ORDER BY create_time DESC;
-- 索引设计：idx_user_time(user_id, create_time DESC)

-- 查询2：特定状态的订单，按时间倒序
-- SELECT * FROM order WHERE status = 2 ORDER BY create_time DESC;
-- 索引设计：idx_status_time(status, create_time DESC)

-- 查询3：用户的特定状态订单
-- SELECT * FROM order WHERE user_id = 123 AND status = 2;
-- 索引设计：idx_user_status(user_id, status)

-- 查询4：统计用户订单金额
-- SELECT user_id, SUM(amount) FROM order GROUP BY user_id;
-- 索引设计：idx_user_amount(user_id, amount)

-- 最终的索引方案
ALTER TABLE `order`
ADD KEY `idx_user_time` (`user_id`, `create_time` DESC),
ADD KEY `idx_status_time` (`status`, `create_time` DESC),
ADD KEY `idx_user_status` (`user_id`, `status`),
ADD KEY `idx_user_amount` (`user_id`, `amount`);

复合索引设计原则：

1. 高频查询优先：为最频繁的查询设计索引
2. 高选择性列在前：选择性高的列放在复合索引前面
3. 覆盖索引：让索引包含所有查询需要的字段
4. 避免重复索引：索引(a,b)已经包含索引(a)的功能

3.4 索引的代价：为什么不能乱加索引？

索引的成本：

1. 存储空间：每个索引都是一棵B+树，占用磁盘空间
2. 写入性能：每次INSERT、UPDATE、DELETE都需要更新索引
3. 维护成本：索引需要定期维护（OPTIMIZE TABLE）
4. 选择困难：优化器可能选择错误的索引

-- ❌ 过度索引的示例
CREATE TABLE `product` (
`id` bigint(20) NOT NULL,
`name` varchar(200) NOT NULL,
`category_id` int(11) NOT NULL,
`price` decimal(10,2) NOT NULL,
`status` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_category` (`category_id`),
KEY `idx_price` (`price`),
KEY `idx_status` (`status`),
KEY `idx_category_status` (`category_id`, `status`),  -- 和上面重复
KEY `idx_status_category` (`status`, `category_id`),  -- 和上面重复
KEY `idx_name_category` (`name`, `category_id`),      -- 很少这样查询
KEY `idx_category_price` (`category_id`, `price`)     -- 很少这样查询
);

-- 问题：10万条记录，8个索引，索引数据是表数据的3倍！
-- 写入性能：下降70%

索引维护建议：

1. 定期分析：每月分析一次索引使用情况

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计信息
SHOW INDEX FROM `order`;

2. 删除无用索引：超过90天未使用的索引考虑删除
3. 合并索引：将多个单列索引合并为复合索引
4. 监控索引大小：索引大小不应超过表大小的50%

四、SQL编写规范：从正确到高效

4.1 SELECT语句：不要用SELECT * 的深层原因

*为什么SELECT 是坏习惯？**

-- ❌ SELECT * 的问题
SELECT * FROM `user` WHERE id = 1;

-- 问题分析：
-- 1. 网络传输：传输所有字段，包括不需要的text/blob字段
-- 2. 内存消耗：应用层需要分配内存存储所有字段
-- 3. 索引失效：覆盖索引无法使用
-- 4. 耦合性：表结构变更可能导致应用异常
-- 5. 可读性：不知道具体查询哪些字段

-- ✅ 明确指定字段
SELECT
id, 
  username, 
  email, 
  phone,
status,
  create_time
FROM `user`
WHERE id = 1;

-- 好处：
-- 1. 减少网络传输：只传输需要的字段
-- 2. 减少内存使用：应用层只分配需要的字段
-- 3. 可能使用覆盖索引：如果索引包含所有查询字段
-- 4. 解耦：表结构变更不影响查询
-- 5. 可读性：明确知道查询哪些字段

覆盖索引的优势：

-- 创建覆盖索引
CREATE INDEX idx_user_covering ON user(id, username, email, phone, status);

-- 使用覆盖索引的查询（从索引中直接获取数据，不需要回表）
EXPLAIN SELECT id, username, email FROM user WHERE id = 1;
-- Extra列显示：Using index

4.2 WHERE条件优化：避免索引失效的常见陷阱

索引失效的常见场景：

-- 1. 对索引列使用函数或计算
SELECT * FROM `order` WHERE DATE(create_time) = '2024-01-01';  -- ❌ 索引失效
SELECT * FROM `order` WHERE  create_time >= '2024-01-01 00:00:00'
AND create_time < '2024-01-02 00:00:00';  -- ✅ 能用索引

-- 2. 使用OR条件（除非所有列都有索引）
SELECT * FROM `user` WHERE username = 'john' OR email = 'john@example.com';  -- ❌ 可能全表扫描
-- 优化方案1：使用UNION
SELECT * FROM `user` WHERE username = 'john'
UNION
SELECT * FROM `user` WHERE email = 'john@example.com';
-- 优化方案2：使用IN（如果可能）
SELECT * FROM `user` WHERE username IN ('john', 'mary');

-- 3. 使用LIKE以通配符开头
SELECT * FROM `product` WHERE name LIKE '%手机%';  -- ❌ 索引失效
SELECT * FROM `product` WHERE name LIKE '手机%';   -- ✅ 能用索引（前缀匹配）

-- 4. 类型转换
SELECT * FROM `user` WHERE phone = 13800138000;  -- ❌ phone是varchar，与数字比较
SELECT * FROM `user` WHERE phone = '13800138000'; -- ✅ 类型一致

-- 5. 使用NOT、!=、<> 
SELECT * FROM `user` WHERE status != 1;  -- ❌ 可能全表扫描
SELECT * FROM `user` WHERE status IN (0, 2, 3);  -- ✅ 优化方案

4.3 JOIN优化：理解JOIN的执行过程

JOIN的执行原理：

-- 示例：用户和订单表关联查询
SELECT u.username, o.order_no, o.amount
FROM `user` u
INNER JOIN `order` o ON u.id = o.user_id
WHERE u.status = 1
AND o.create_time > '2024-01-01'
ORDER BY o.create_time DESC
LIMIT 100;

JOIN的执行过程：

1. 驱动表选择：MySQL优化器选择较小的表作为驱动表（user）
2. 从驱动表过滤：从user表中读取status=1的记录
3. 关联查询：对于每个user，在order表中查找对应记录
4. 二次过滤：过滤order.create_time > '2024-01-01'
5. 排序和限制：按时间倒序，取前100条

JOIN优化建议：

-- 1. 确保关联字段有索引
ALTER TABLE `order` ADD INDEX `idx_user_id` (`user_id`);

-- 2. 小表驱动大表
-- 如果user表有1000条记录，order表有100万条
-- 应该让user表驱动order表

-- 3. 使用EXPLAIN分析JOIN
EXPLAIN SELECT u.username, o.order_no 
FROM `user` u 
INNER JOIN `order` o ON u.id = o.user_id;

-- 关注：
-- type: ref/eq_ref（好） | ALL（坏，全表扫描）
-- key: 使用的索引
-- rows: 预估扫描行数
-- Extra: Using index（好） | Using temporary（坏，临时表）

4.4 分页优化：大数据量分页的性能陷阱

传统分页的问题：

-- ❌ 传统分页：OFFSET越大越慢
SELECT * FROM `order`
ORDER BY create_time DESC
LIMIT 1000000, 20;  -- 需要扫描1000020行，然后丢弃前100万行

-- 执行过程：
-- 1. 读取1000020行数据
-- 2. 排序1000020行数据
-- 3. 跳过前1000000行
-- 4. 返回最后20行
-- 性能：随着offset增大，性能线性下降

分页优化方案：

-- 方案1：使用游标分页（推荐）
-- 第一页
SELECT * FROM `order`
ORDER BY create_time DESC, id DESC -- id用于区分相同时间的记录
LIMIT 20;

-- 假设最后一条记录：create_time='2024-01-15 10:30:00', id=1000
-- 第二页
SELECT * FROM `order`
WHERE (create_time < '2024-01-15 10:30:00') 
OR (create_time = '2024-01-15 10:30:00' AND id < 1000)
ORDER BY create_time DESC, id DESC
LIMIT 20;

-- 方案2：使用覆盖索引+子查询
-- 先通过覆盖索引获取id，再通过id获取数据
SELECT o.* 
FROM `order` o
INNER JOIN (
SELECT id
FROM `order`
ORDER BY create_time DESC
LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;

-- 方案3：业务上限制深度分页
-- 最多允许查看前100页：LIMIT 2000, 20
-- 提供搜索功能代替深度分页

五、MyBatis规范：Java与SQL的桥梁

5.1 XML映射文件规范

为什么MyBatis XML需要规范？

当XML文件没有规范时：

• SQL难以维护和调试
• 性能问题难以发现
• 动态SQL容易出错
• 代码复用性差

<!-- ❌ 不规范的Mapper XML -->
<mapper namespace="com.example.UserMapper">
<select id="findUsers" resultType="User">
        SELECT * FROM user  <!-- 1. SELECT * -->
        WHERE 1=1           <!-- 2. 无意义的1=1 -->
<if test="name != null">
            AND name like '%${name}%'  <!-- 3. ${}有SQL注入风险 -->
</if>
<if test="status != null">
            AND status = #{status}
</if>
<!-- 4. 没有分页参数处理 -->
<!-- 5. 没有排序控制 -->
</select>
</mapper>

<!-- ✅ 规范的Mapper XML -->
<mapper namespace="com.example.UserMapper">

<!-- 1. 定义结果映射（避免使用resultType） -->
<resultMap id="UserResultMap" type="User">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="email" column="email"/>
<result property="phone" column="phone"/>
<result property="status" column="status"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
</resultMap>

<!-- 2. 定义可重用的SQL片段 -->
<sql id="Base_Column_List">
        id, username, email, phone, status, 
        create_time, update_time
</sql>

<!-- 3. 条件查询（使用<where>标签） -->
<select id="selectUsers" resultMap="UserResultMap">
        SELECT 
<include refid="Base_Column_List"/>
        FROM user
<where>
<if test="username != null and username != ''">
                AND username LIKE CONCAT('%', #{username}, '%')
</if>
<if test="email != null and email != ''">
                AND email = #{email}
</if>
<if test="status != null">
                AND status = #{status}
</if>
<if test="createTimeStart != null">
                AND create_time >= #{createTimeStart}
</if>
<if test="createTimeEnd != null">
                AND create_time <= #{createTimeEnd}
</if>
            AND is_deleted = 0  <!-- 软删除过滤 -->
</where>
<!-- 排序 -->
<choose>
<when test="orderBy != null and orderBy != ''">
                ORDER BY ${orderBy}
</when>
<otherwise>
                ORDER BY id DESC
</otherwise>
</choose>
<!-- 分页 -->
<if test="pageSize != null and pageSize > 0">
            LIMIT #{offset}, #{pageSize}
</if>
</select>

<!-- 4. 批量插入（提高性能） -->
<insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO user (username, email, phone, status)
        VALUES
<foreach collection="list" item="item" separator=",">
            (#{item.username}, #{item.email}, #{item.phone}, #{item.status})
</foreach>
</insert>

<!-- 5. 使用<set>标签的更新 -->
<update id="updateUser">
        UPDATE user
<set>
<if test="username != null">username = #{username},</if>
<if test="email != null">email = #{email},</if>
<if test="phone != null">phone = #{phone},</if>
<if test="status != null">status = #{status},</if>
            update_time = NOW()  <!-- 自动更新时间 -->
</set>
        WHERE id = #{id}
          AND is_deleted = 0
</update>
</mapper>

5.2 动态SQL的最佳实践

动态SQL的常见陷阱和解决方案：

<!-- 1. <if>标签的布尔判断 -->
<!-- ❌ 错误：直接判断布尔值 -->
<if test="isAdmin">
    AND role = 'ADMIN'
</if>
<!-- 问题：当isAdmin为false时，MyBatis可能不会添加条件 -->

<!-- ✅ 正确：明确判断 -->
<if test="isAdmin != null and isAdmin == true">
    AND role = 'ADMIN'
</if>
<!-- 或者 -->
<if test="isAdmin != null">
    AND role = 'ADMIN'
</if>

<!-- 2. 字符串判断 -->
<!-- ❌ 错误：只判断null -->
<if test="name != null">
    AND name = #{name}
</if>
<!-- 问题：空字符串也会进入条件 -->

<!-- ✅ 正确：同时判断null和空字符串 -->
<if test="name != null and name != ''">
    AND name = #{name}
</if>

<!-- 3. 集合判断 -->
<!-- ❌ 错误：直接使用集合 -->
<if test="ids != null">
    AND id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
</foreach>
</if>
<!-- 问题：空集合也会生成IN ()，语法错误 -->

<!-- ✅ 正确：判断集合非空 -->
<if test="ids != null and ids.size() > 0">
    AND id IN
<foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
</foreach>
</if>

<!-- 4. 多条件选择 -->
<!-- 使用<choose>代替多个<if> -->
<choose>
<when test="orderBy == 'time'">
        ORDER BY create_time DESC
</when>
<when test="orderBy == 'price'">
        ORDER BY price ASC
</when>
<otherwise>
        ORDER BY id DESC
</otherwise>
</choose>

5.3 接口设计规范

// MyBatis Mapper接口规范
@Mapper
public interface UserMapper {

// 1. 方法命名规范：动词+领域+By+条件
// 查询方法
User selectUserById(@Param("id") Long id);

List<User> selectUsersByCondition(UserQuery query);

// 使用PageHelper分页
List<User> selectUsersWithPage(UserQuery query);

// 统计方法
Long countUsersByCondition(UserQuery query);

// 判断方法
boolean existsByUsername(@Param("username") String username);
boolean existsByEmail(@Param("email") String email);

// 2. 插入方法
int insertUser(User user);

// 批量插入（返回影响行数）
int batchInsertUsers(@Param("list") List<User> users);

// 插入并返回主键（useGeneratedKeys）
int insertUserAndReturnId(User user);

// 3. 更新方法
int updateUserById(User user);

// 部分更新
int updateUserStatus(@Param("id") Long id, @Param("status") Integer status);

// 乐观锁更新
int updateUserWithVersion(User user);

// 4. 删除方法
int deleteUserById(@Param("id") Long id);  // 物理删除

int softDeleteUserById(@Param("id") Long id);  // 软删除

// 5. 复杂查询
// 关联查询
List<UserWithOrderDTO> selectUsersWithOrders(@Param("userId") Long userId);

// 子查询
List<User> selectUsersWithSubQuery(UserQuery query);

// 6. 存储过程调用（如果需要）
void callUserProcedure(@Param("userId") Long userId);
}

六、数据库变更管理：安全地演进数据库结构

6.1 为什么需要数据库变更管理？

没有变更管理的问题：

1. 环境不一致：开发、测试、生产环境数据库结构不同
2. 回滚困难：修改出错后无法快速回滚
3. 团队协作冲突：多人同时修改数据库结构
4. 缺乏审计：不知道谁在什么时候修改了什么

6.2 使用Flyway进行数据库版本控制

Flyway的核心概念：

• Migration：一个数据库变更脚本
• Version：每个Migration有唯一的版本号
• Checksum：脚本内容的校验和，防止意外修改

Flyway目录结构：

src/main/resources/db/migration/
├── V1__Initial_schema.sql          -- 版本1：初始结构
├── V2__Add_user_table.sql          -- 版本2：添加用户表
├── V3__Add_index_to_user.sql       -- 版本3：添加索引
├── V4__Add_order_table.sql         -- 版本4：添加订单表
└── V5__Update_user_table.sql       -- 版本5：修改用户表

Migration脚本示例：

-- V1__Initial_schema.sql
-- 创建用户表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
UNIQUE KEY `uk_username` (`username`),
UNIQUE KEY `uk_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- V2__Add_index_to_user.sql
-- 添加手机号索引
ALTER TABLE `user`
ADD COLUMN `phone` varchar(20) DEFAULT NULL AFTER `email`,
ADD INDEX `idx_phone` (`phone`);

-- V3__Update_user_table.sql
-- 添加状态字段和软删除标记
ALTER TABLE `user`
ADD COLUMN `status` tinyint(1) NOT NULL DEFAULT '1' AFTER `phone`,
ADD COLUMN `is_deleted` tinyint(1) NOT NULL DEFAULT '0' AFTER `status`,
ADD INDEX `idx_status` (`status`);

Flyway配置：

# application.yml
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true # 如果数据库非空，创建基线版本
    baseline-version: 1 # 基线版本号
    validate-on-migrate: true # 迁移时验证
    out-of-order: false # 禁止乱序执行
    clean-disabled: true # 禁止在生产环境使用clean

6.3 数据库变更的最佳实践

1. 每次变更都要可回滚：

-- 向前迁移（up）
ALTER TABLE `user` ADD COLUMN `age` tinyint(3) DEFAULT NULL;

-- 回滚迁移（down）
ALTER TABLE `user` DROP COLUMN `age`;

2. 大表变更要小心：

-- ❌ 直接添加列（对大数据量表可能锁表）
ALTER TABLE `big_table` ADD COLUMN `new_column` varchar(100);

-- ✅ 使用pt-online-schema-change（Percona工具）
-- 原理：创建新表 -> 复制数据 -> 重命名表
-- 不会锁表，但需要更多磁盘空间和时间

3. 索引变更要在低峰期：

-- 创建索引会锁表（MySQL 5.6+支持Online DDL，但仍有影响）
ALTER TABLE `user` ADD INDEX `idx_new_column` (`new_column`);

-- 建议：在夜间或低峰期执行

4. 数据迁移要分批：

-- ❌ 一次性更新大量数据（可能锁表、产生大事务）
UPDATE `order` SET status = 2 WHERE status = 1;

-- ✅ 分批更新（使用游标或limit）
UPDATE `order` SET status = 2
WHERE status = 1
AND id BETWEEN 1 AND 10000;
-- 循环执行，每次更新一部分

七、监控与调优：持续改进数据库性能

7.1 数据库监控指标

必须监控的核心指标：

指标	监控方法	告警阈值	说明
QPS	SHOW GLOBAL STATUS	> 5000	每秒查询次数
TPS	SHOW GLOBAL STATUS	> 1000	每秒事务次数
连接数	SHOW PROCESSLIST	> max_connections的80%	当前连接数
慢查询	slow_query_log	> 1秒	执行时间超过阈值的查询
锁等待	SHOW ENGINE INNODB STATUS	> 1秒	锁等待时间
缓冲池命中率	SHOW GLOBAL STATUS	< 95%	InnoDB缓冲池命中率
复制延迟	SHOW SLAVE STATUS	> 60秒	主从复制延迟

监控脚本示例：

-- 1. 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 2. 查看慢查询数量
SHOW STATUS LIKE 'Slow_queries';

-- 3. 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 4. 查看锁等待
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 5. 查看表大小和索引大小
SELECT
  table_name,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb,
ROUND((index_length / 1024 / 1024), 2) AS index_size_mb,
ROUND((data_length / (data_length + index_length)) * 100, 2) AS data_percent
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY size_mb DESC;

7.2 性能调优实战

案例：订单查询慢问题分析

-- 原始查询（执行时间：3.5秒）
SELECT * FROM `order`
WHERE user_id = 123456
AND status IN (1, 2, 3) 
AND create_time BETWEEN '2024-01-01' AND '2024-03-01'
ORDER BY create_time DESC
LIMIT 20;

-- 使用EXPLAIN分析
EXPLAIN SELECT * FROM `order` WHERE user_id = 123456 ...;

EXPLAIN结果分析：

+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | rows | Extra  |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | order | ALL  | idx_user_id   | NULL | NULL    | 500K | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

问题分析：

1. type: ALL：全表扫描，没有使用索引
2. key: NULL：没有使用任何索引
3. Extra: Using filesort：使用了文件排序，性能差

解决方案：

-- 1. 创建复合索引
ALTER TABLE `order`
ADD INDEX `idx_user_status_time` (`user_id`, `status`, `create_time` DESC);

-- 2. 优化后的查询（使用覆盖索引）
SELECT
id, user_id, order_no, amount, status, create_time
FROM `order`
WHERE user_id = 123456
AND status IN (1, 2, 3) 
AND create_time BETWEEN '2024-01-01' AND '2024-03-01'
ORDER BY create_time DESC
LIMIT 20;

-- 3. 再次EXPLAIN分析
-- type: range（使用了索引范围扫描）
-- key: idx_user_status_time（使用了新创建的索引）
-- Extra: Using where; Using index（使用了覆盖索引）

优化结果：

• 执行时间：3.5秒 → 0.02秒
• 扫描行数：500,000行 → 150行
• 排序方式：文件排序 → 索引排序

八、最后的思考：规范不是终点，而是起点

规范的真正价值：

1. 降低认知成本：新同事三天就能上手，不是三个月
2. 减少人为错误：80%的数据库问题在设计和编码阶段就能避免
3. 提升系统稳定性：规范的数据库设计是系统稳定性的基石
4. 便于知识传承：规范文档成为团队共同的知识资产
5. 支持快速迭代：有了规范，重构和优化变得安全可控

但规范不是银弹：

1. 规范需要适应业务：不同业务场景可能需要不同的规范
2. 规范需要持续演进：技术发展，规范也要更新
3. 规范需要团队共识：单方面强推规范很难成功
4. 规范需要工具支持：通过工具自动化检查，减少人为失误

如何开始？

1. 从小处着手：从一个表、一个查询开始规范
2. 建立代码审查机制：数据库变更必须经过审查
3. 使用自动化工具：SQL检查工具、Flyway等
4. 定期复盘：每月回顾一次数据库问题，更新规范
5. 培养团队意识：让大家理解规范的价值，而不是强制执行

记住：最好的规范，是大家愿意遵守的规范。

我们不是因为规范而规范，而是因为：
好的数据库设计，能让我们的系统跑得更快、更稳、更久。
好的数据库规范，能让我们的团队协作更顺畅、更高效、更快乐。

想要深入探讨更多关于 MySQL 性能优化或 后端架构 的设计思路？欢迎来到 云栈社区 与众多开发者一起交流学习。