核心 MySQL 应用开发规范 mysql核心技术

1. 表设计原则

1.1 表越窄越好

? 字段类型选择够用即可：

? 账号名字段长度 ≤30 字符，无需设置为 varchar(255)。

? 时间戳使用 INT UNSIGNED。

? IPv4 地址使用 INT UNSIGNED，通过 INET_ATON 和 INET_NTOA 转换。

? 判断宽表：

? SHOW TABLE STATUS 查看表信息：

? Avg_row_length > 100：可能为宽表，需分析原因。

? Data_free > 0：表示存在碎片，碎片越多表越宽。

1.2 表越小越好

? 保持表的数据量尽可能小，尤其是热表（业务高频使用表）：

? 推荐方案：分库分表、冷热数据分离。

? 数据量参考：

? 单表行数尽量不超过 5000 万（窄表可适当增加，宽表需控制更小）。

? 窄表三层 B+Tree 可存储约 9.7 亿行，平均 IO 代价低。

2. 高效请求

? 快速完成 SQL/事务，避免锁资源占用：

? 长时间未提交事务可能导致表锁和阻塞。

? 优化建议：

1. 监控 MySQL 线程状态和 InnoDB 事务状态。

2. 设置修改或锁定行数的阈值。

重要细节

1. Schema 设计原则

1. 尽量小：字段、表、数据量都应尽量精简。

2. 禁止外键：避免增加锁和阻塞，需求可通过代码实现。

3. 主键优选自增 INT/BIGINT。

4. 字符集一致：库、表、字段统一使用相同字符集。

5. 推荐标准：

? 单表行数 ≤5000 万。

? 单表物理大小 ≤20GB。

? 单表索引数量 ≤5。

? 实例总大小 ≤500GB。

? 实例总表数量 ≤5000。

2. 字段设计规范

1. 每表字段数量建议不超过 50 个字段

原因：

? 字段过多会导致表设计复杂，查询效率下降，维护成本增加。

? 每条记录占用更多存储空间，可能导致更多的磁盘 IO。

? 会增加索引设计复杂度，并且表中的宽字段可能导致碎片化，影响性能。

2. 优先选择 utf8mb4 字符集

原因：

? utf8mb4 是 utf8 的升级版本，能够完全支持包括表情符号在内的 4 字节字符集。

? 避免因不支持 4 字节字符（如 emoji）导致插入错误。

? 更适应国际化需求，兼容性更好。

3. 严禁明文存储核心数据

原因：

? 安全性：防止敏感数据（如密码、个人信息等）泄露。

? 合规性：符合如 GDPR 等隐私保护法规的要求。

? 核心数据明文存储一旦泄露，可能导致无法挽回的损失。

4. 用好 INT 数据类型

原因：

? INT 数据类型存储和查询效率高，占用固定 4 字节。

? 用 INT UNSIGNED 存储无符号整数，可以支持更大的正数范围。

? INT 是最适合主键和 ID 的数据类型，索引效率高。

5. 遇到 BLOB 和 TEXT 字段尽量拆分

原因：

? BLOB 和 TEXT 类型存储在独立的数据页中，访问它们时会额外增加 IO 操作。

? 过多使用会导致表变宽，影响查询性能。

? 拆分到独立表后，可以通过主键关联，优化存储和访问性能。

6. 字符类型尽可能采用 VARCHAR

原因：

? 节省空间：VARCHAR 根据实际存储内容分配存储空间，节省磁盘资源。

? 效率更高：与 CHAR 比较，VARCHAR 更适合长度不固定的字符类型。

? 避免因为固定长度的 CHAR 造成存储空间浪费。

7. 日期时间类型建议使用 DATETIME

原因：

? 可读性强：DATETIME 格式直接以 YYYY-MM-DD HH:MM:SS 存储，便于查看和调试。

? 不受时区影响，和 TIMESTAMP 相比更稳定。

? 适合表示与业务逻辑相关的固定时间点。

SQL 开发建议

1. 多表 JOIN 时，JOIN 列的数据类型要一致

原因：

? 数据类型不一致会导致 MySQL 隐式类型转换，增加计算成本。

? 隐式转换可能导致无法使用索引，从而降低查询性能。

? 明确的数据类型能减少潜在的错误和非预期行为。

2. 多表 JOIN 时，把过滤后结果集较小的表作为驱动表

原因：

? MySQL 在执行 JOIN 时，会先遍历驱动表的记录，再与被驱动表进行匹配。

? 选择小结果集的表作为驱动表，能减少遍历次数，优化查询效率。

3. WHERE 条件中优先使用函数或表达式（MySQL 8.0）

原因：

? MySQL 8.0 开始支持功能更强的窗口函数和表达式，能直接对数据进行灵活处理。

? 使用函数可以减少客户端逻辑处理的复杂度，提升整体执行效率。

? 可以通过索引优化函数结果的查询性能（如生成虚拟列后添加索引）。

4. 不要为所有 WHERE 条件中出现的列都创建索引

原因：

? 索引会占用额外存储空间，过多索引会导致插入、更新、删除性能下降。

? 创建索引需考虑查询频率和性能瓶颈，仅对高频使用的条件列建索引。

? 一些查询条件可能不适合索引（如使用范围查询或低选择性字段）。

5. 避免 SELECT *，仅查询必要字段

原因：

? 减少返回的数据量，降低网络传输成本。

? 避免返回不必要的数据，提升查询性能。

? 避免未来表结构调整导致查询失效。

6. 避免 LIKE '%c%' 模式匹配

原因：

? 前置通配符 % 会导致 MySQL 无法利用索引，只能进行全表扫描。

? 全表扫描的代价高，尤其在数据量大的表中性能极差。

? 优化方式：尽可能改为 LIKE 'c%' 或使用全文索引。

7. 尽量不要使用 != 条件

原因：

? != 不支持索引优化，MySQL 需要逐行扫描，性能低。

? 替代方法：尽可能使用范围查询（如 < 或 >），或者通过逻辑重组条件。

8. 如果能确定返回结果数量，最好加上 LIMIT N

原因：

? 限制返回结果集，减少内存使用和网络传输成本。

? 避免查询返回过多结果影响客户端性能。

? 提高查询响应速度。

9. 优先使用 UNION ALL 替代 UNION

原因：

? UNION 会去重，增加排序和比较的计算成本。

? 如果结果集中不需要去重，使用 UNION ALL 性能更高。

10. 所有 SQL 上线前必须通过审核

原因：

? 防止低效的 SQL 语句上线，避免对生产环境造成性能问题。

? 确保符合业务逻辑和公司数据库规范。

? 审核能及时发现潜在问题，如表锁、死锁、错误的查询条件等。

---

以上是我阅读之后自己总结的，我只是稍微整理了一下