写点什么

Mysql 应用开发规范

作者:阿丞
  • 2022 年 4 月 24 日
  • 本文字数:1269 字

    阅读完需:约 4 分钟

0.核心思想

  1. 表越窄越好

  2. 表越小越好

  3. 请求要高效

1.表越窄越好

设计表字段的时候,选择的数据类型够用就行


比如:


  • 字段 Nid 用来存储员工 id,设计系统中员工 id 最长为 30,那么就不需要字段类型设置为 varchar(255),尽可能设计一个窄表,而不是宽表。

  • 设计 Unix 时间戳时,使用无符号的整型(INT UNSIGNED)。

  • 设计 IPV4 地址时,使用无符号整型(INT UNSIGNED),INET_AOTN()和 INET_NTOA()函数进行转换实现,而不用 char 或 varchar。


可以在设计的时候,查看表的统计信息:


show table status
复制代码


一般,Avg_row_length 这个列超过 100 个字节的时候,需要看一下什么原因导致的,可否优化。

2.表越小越好

设计表的时候,对于热表数据量越小越好,这样 IO 操作的代价更小,可以通过分库分表、冷热数据分离等方式实现。


建议:表的行数最好不要超过 5000W 行,尤其是宽表。


一个 INT 类型(4 字节)列,在最高达到三层高度的时候,可以存储大约 9.7 亿条数据。



3.请求要高效

每条 sql 尽快完成,事务尽快提交/回滚


在修改/删除数据锁定数据行,会产生表锁,锁资源没有释放的话,可能造成其他 sql 或事务被阻塞。


通过监控 MYSQL 的线程状态和监控 InnoDB 的事务状态,监控长 sql,修改或锁定行数的阈值,超出发出告警。


检查有没有 sql 注入的风险。

4.细节

4.1 schema 设计原则

  1. 尽量小的原则

  2. 不建议使用外键(为了高并发,外键会增加行锁,更大概率导致锁的阻塞和等待时间),如果需要外键保证数据一致性,可以选择在代码层做一些控制。

  3. 自增 INT/BIGINT 主键,InnoDB 引擎表(索引组织表),数据按照具体索引的顺序来进行存储,如果采用 char 或这 uuid 做主键或聚集索引,可能会导致数据存储的顺序离散随机,存储时产生大量的磁盘碎片,浪费资源,影响性能。

  4. 字符集和库表设计要一致,Mysql 实例-database-数据库表-字段-存储过程-eve

  5. 为了 mysql 尽量高效

  6. 针对单表:

  7. 单表数据量尽量不超过 5000 万

  8. 单表的物理大小尽量不超过 20G

  9. 索引的数量尽量不超过 5 个

  10. 针对实例:

  11. 总的大小尽量不超过 500G

  12. 总的表的数量尽量不超过 5000 个(包含表分区)

4.2 库表字段设计规范

  1. 每个表字段尽量不超过 50 个

  2. 优先使用 utf8mb4 字符集,因为他的兼容性最好

  3. 创建索引:采用 utf8mb4 字符集影响比较大,用 latin1 或 binary

  4. 严禁在数据库中明文存储用户的一些核心数据,比如身份证、卡号、密码等,最好单向加密

  5. 用好 INT 数据类型,金额可以选择扩大 N 倍存为 INT 或 BIGINT 而不是浮点型

  6. 遇到 BLOB、TEXT(大对象数据类型)字段,尽量拆分出去,然后用主键做关联,避免造成行溢出和碎片的风险

  7. 字符类型尽可能采用 varchar 的数据类型,灵活高效,尽量不要用加长更新(5 个字符更新完变成 10 个字符)

  8. 日期的数据类型尽量采用 datetime 或 int 类型

4.3SQL 开发建议

  1. 多表 join 时,join 列的数据类型要一致

  2. 多表 join 时,把过滤后的数据集较小的表放在后面作为驱动表

  3. 在查询的 where 条件中用上函数索引或表达式索引升级 8.0 版本

  4. 尽量不执行 select *操作

  5. 尽量不执行 like ‘%XX%’,%在前会全表扫描

  6. 尽量不使用“ != ”条件,扫描的数据量占到全表的一半左右会默认变成全表扫描

  7. 能确定结果的返回数量的话,尽量加上 limit n

  8. 优先使用 union all,代替 union

  9. sql 通过 sql 审核系统检查后,达到标准再上线

用户头像

阿丞

关注

既然选择编程,只管风雨兼程。 2021.12.13 加入

本人21年6月毕业于双非本科(软件工程),同年3月实习期内参与公司内部核心数据域(数据仓库)从0到1搭建的全过程,同年7月至今参与国家电网的PMS3.0建设实施方案编写工作,主要负责应用体系建设章节的整理与编写。

评论

发布
暂无评论
Mysql应用开发规范_MySQL_阿丞_InfoQ写作社区