Mysql 应用开发规范
0.核心思想
表越窄越好
表越小越好
请求要高效
1.表越窄越好
设计表字段的时候,选择的数据类型够用就行
比如:
字段 Nid 用来存储员工 id,设计系统中员工 id 最长为 30,那么就不需要字段类型设置为 varchar(255),尽可能设计一个窄表,而不是宽表。
设计 Unix 时间戳时,使用无符号的整型(INT UNSIGNED)。
设计 IPV4 地址时,使用无符号整型(INT UNSIGNED),INET_AOTN()和 INET_NTOA()函数进行转换实现,而不用 char 或 varchar。
可以在设计的时候,查看表的统计信息:
一般,Avg_row_length 这个列超过 100 个字节的时候,需要看一下什么原因导致的,可否优化。
2.表越小越好
设计表的时候,对于热表数据量越小越好,这样 IO 操作的代价更小,可以通过分库分表、冷热数据分离等方式实现。
建议:表的行数最好不要超过 5000W 行,尤其是宽表。
一个 INT 类型(4 字节)列,在最高达到三层高度的时候,可以存储大约 9.7 亿条数据。
3.请求要高效
每条 sql 尽快完成,事务尽快提交/回滚
在修改/删除数据→锁定数据行,会产生表锁,锁资源没有释放的话,可能造成其他 sql 或事务被阻塞。
通过监控 MYSQL 的线程状态和监控 InnoDB 的事务状态,监控长 sql,修改或锁定行数的阈值,超出发出告警。
检查有没有 sql 注入的风险。
4.细节
4.1 schema 设计原则
尽量小的原则
不建议使用外键(为了高并发,外键会增加行锁,更大概率导致锁的阻塞和等待时间),如果需要外键保证数据一致性,可以选择在代码层做一些控制。
自增 INT/BIGINT 主键,InnoDB 引擎表(索引组织表),数据按照具体索引的顺序来进行存储,如果采用 char 或这 uuid 做主键或聚集索引,可能会导致数据存储的顺序离散随机,存储时产生大量的磁盘碎片,浪费资源,影响性能。
字符集和库表设计要一致,Mysql 实例-database-数据库表-字段-存储过程-eve
为了 mysql 尽量高效
针对单表:
单表数据量尽量不超过 5000 万
单表的物理大小尽量不超过 20G
索引的数量尽量不超过 5 个
针对实例:
总的大小尽量不超过 500G
总的表的数量尽量不超过 5000 个(包含表分区)
4.2 库表字段设计规范
每个表字段尽量不超过 50 个
优先使用 utf8mb4 字符集,因为他的兼容性最好
创建索引:采用 utf8mb4 字符集影响比较大,用 latin1 或 binary
严禁在数据库中明文存储用户的一些核心数据,比如身份证、卡号、密码等,最好单向加密
用好 INT 数据类型,金额可以选择扩大 N 倍存为 INT 或 BIGINT 而不是浮点型
遇到 BLOB、TEXT(大对象数据类型)字段,尽量拆分出去,然后用主键做关联,避免造成行溢出和碎片的风险
字符类型尽可能采用 varchar 的数据类型,灵活高效,尽量不要用加长更新(5 个字符更新完变成 10 个字符)
日期的数据类型尽量采用 datetime 或 int 类型
4.3SQL 开发建议
多表 join 时,join 列的数据类型要一致
多表 join 时,把过滤后的数据集较小的表放在后面作为驱动表
在查询的 where 条件中用上函数索引或表达式索引升级 8.0 版本
尽量不执行 select *操作
尽量不执行 like ‘%XX%’,%在前会全表扫描
尽量不使用“ != ”条件,扫描的数据量占到全表的一半左右会默认变成全表扫描
能确定结果的返回数量的话,尽量加上 limit n
优先使用 union all,代替 union
sql 通过 sql 审核系统检查后,达到标准再上线
评论