MYSQL数据库开发规范
MYSQL数据库开发规范
古城痴人 发表于1年前
MYSQL数据库开发规范
  • 发表于 1年前
  • 阅读 72
  • 收藏 18
  • 点赞 1
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

摘要: 收集整理的MYSQL数据库开发规范

命名规范

  1. 数据库名、表名、字段名都使用小写字母
  2. 单词之前使用“_”来连接,不使用驼峰命名法
  3. 长度禁止超过48个字符
  4. 不许使用MySQL关键字
  5. 常规库以db_开头,常规表以tbl_开头
  6. 临时库,临时表以 tmp_ 为前缀,以日期为后缀
  7. 备份库,备份表以 bak_ 为前缀,以日期为后缀

基础规范

  1. 使用InnoDB存储引擎
  2. 一律采用UTF-8编码
  3. 表和属性必须填注释
  4. 单表数据量控制在1000W以下
  5. 禁止在生产库做压力测试
  6. 禁止从测试、开发环境直接生产库

库表设计

  1. 生产环境禁止使用分区表
  2. 拆分大字段及访问频率低的字段,分离冷热数据
  3. Hash分表时,表后缀必须是10进制数
  4. 按日期分表时,年份使用2位而不使用4位
  5. 采用合适的Sharding策略,userId、date、province、
  6. 尽量不使用外键,由程序来保证约束 有额外开销、逐行操作、高并发时容易死锁
  7. 单库表数量不超过300张
  8. 单表字段数不超过50个
  9. 单行不超过200字节

字段规范

  1. 尽可能不使用TEXT,CLOB,BLOB类型的字段: 对于大文本存储,可以使用varchar类型,varchar(65535) -> 64K 如果一定要使用,可以单独拆成一个表来使用
  2. 尽量不在数据库中保存图片,文件等大字段
  3. 涉及金额的字段一律使用 Decimal类型,绝对不允许使用 double/float 类型
  4. 使用合适的数据类型: tinyint(1),smallint(2),mediumint(3),int(4),bigint(8),float(4),double(8),decimal(总长度,小数部分长度)
  5. 除非NULL有特殊含意,所有字段不许为空: 很难进行查询优化、NULL列加索引需额外空间、含NULL复合索引无效、应用开发容易出空指针异常
  6. 时间可以使用datetime类型
  7. 不要使用枚举类型

索引规范

  1. 单张表索引数量最好不超过5个
  2. 单个索引中字段数不超过5个
  3. 对字符串类型的字段使用前缀索引,前缀索引长度不超过8个字符
  4. 表必须有主键,主键不应该更新 禁止使用UUID,MD5,Hash结果作为主键。 推荐使用独立于业务的Auto_increment字列,或全局ID生成器作代理主键 数字型相比字符串型索引:高效、查询快、占空间小
  5. 重要的SQL必须被索引 where条件中的字段、order by、group by、distinct 字段、多表join的字段
  6. 区分度大的字符放在前面
  7. 核心SQL优先考虑覆盖索引
  8. 避免冗余和重复索引
  9. 不在低区分度字段建索引,如:性别,车型,车款
  10. 不要在索引字段进行数学运算和函数运算,索引会失效
  11. 查询时,不使用%前导的查询,如: like '%cd'
  12. 尽量不使用负向查询,如:not in / not like / <> / != 原因:全表扫描

SQL设计

  1. 使用预编译语句和参数形式,不手动拼接SQL字符串,1)提高性能 2)防止SQL注入
  2. 充份利用索引,尤其前缀索引
  3. 避免使用存储过程、触发器、UDF、Events等 原因:1)降低业务复杂度,所有业务处理都不放在数据库进行,只让数据库做最擅长的事情;2)存储过程出Bug不好调试
  4. 避免大表Join
  5. 避免在数据库中进行数学运算 原因:1)数据库不擅长做数学运算和逻辑判断;2)可能会使索引失效
  6. 减少与数据库的交互次数 INSERT ... ON DUPLICATE KEY UPDATE REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),() UPDATE … WHERE ID IN(10,20,50,…)
  7. 合理使用分页 限制分页展示的页数,如:页面上只显示有100页数据 只能点击上一页、下一页,不能随意跳转页号 采用延迟关联:不直接关联两张大表,而是先查一张表,再用主键从另一张表中查数据
  8. 拒绝大SQL,将大SQL拆分成小SQL 简单SQL的缓存命中率高;1条SQL只能使用一个核,把大SQL拆成多个小SQL可以充份利用多核CPU;减少锁表时间
  9. 拒绝大事务,事务使用原则: 即开即用,用完即关 与事务无关的操作放在事务外面 不破坏一致性的前提下,使用多个短事务替代长事务
  10. 拒绝大批量
  11. 使用in来代替 orin中的值不许超过200个 对于使用or的SQL,如果or的条件不是同一个字段,可以使用 union 来优化
  12. 禁止使用 order by rand(),除非数据量非常少
  13. 在上线前,使用 explain 来分析SQL的执行计划
  14. 使用 union all ,不使用 union
  15. 尽量不使用 select * 来执行查询,原因: 消耗网络带宽
  16. 实时统计计数使用缓存,双向更新凌晨跑基准;非实时在数据仓库统计
  17. 高效分页:使用查询条件减少数据集 select * from table where id>=12345 limit 10;
  18. 使用同数据类型在where条件中进行列值比较 数字对数字、字符对字符 数值列与字符串比较:同时转为双精度 字符串列与数字比较:字符串列转数值,并且不会使用索引
  19. group by 默认会排序,如果不需要对分组结果排序,可以使用 order by null,这样就不会对结果排序了。 select phone, count(*) from post group by phone order by null

行为规范

  1. 批量导⼊、导出数据必须提前通知DBA协助观察
  2. 禁⽌在线上从库执⾏后台管理和统计类查询
  3. 禁⽌有super权限的应⽤程序账号存在
  4. 产品出现⾮数据库导致的故障时及时通知DBA协助排查
  5. 推⼲活动或上线新功能必须提前通知DBA进⾏流量评估
  6. 数据库数据丢失,及时联系DBA进⾏恢复
  7. 对单表的多次alter操作必须合并为⼀次操作
  8. 不在MySQL数据库中存放业务逻辑
  9. 重⼤项目的数据库⽅案选型和设计必须提前通知DBA参与
  10. 对特别重要的库表,提前与DBA沟通确定维护和备份优先
  11. 不在业务⾼峰期批量更新、查询数据库
  12. 提交线上建表改表需求,必须详细注明所有相关SQL语句
共有 人打赏支持
粉丝 16
博文 16
码字总数 19276
×
古城痴人
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: