加入收藏 | 设为首页 | 会员中心 | 我要投稿 重庆站长网 (https://www.023zz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL开发设计规范有哪些

发布时间:2022-02-12 14:12:34 所属栏目:MySql教程 来源:互联网
导读:这期内容当中小编将会给大家带来有关MySQL开发设计规范有哪些,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。 命名规范 l 库名、表名、字段名禁止超过32个字符。 l 所有数据库对象名称必须使用小写字母并用下划线分割
       这期内容当中小编将会给大家带来有关MySQL开发设计规范有哪些,文章内容丰富且以专业的角度为大家分析和叙述,阅读完这篇文章希望大家可以有所收获。
 
命名规范
 
l  库名、表名、字段名禁止超过32个字符。
 
l  所有数据库对象名称必须使用小写字母并用下划线分割
 
不同的数据库名  DbName dbname
 
不同的表名  Table table tabLe
 
l  所有数据库对象名称(库名、表名、字段名)禁止使用MySQL保留关键字
 
select id,username,from,age from tb_user
 
有两个from,MySQL并不清楚这两个from有什么区别,执行上面会报错
 
select id,username,`from`,age from tb_user 正确
 
l  数据库对象的命名要能做到见名识义,并且最好不要超过32个字符
 
例如:用户账号表  user_account
 
     用户数据库  aisino_userdb
 
l  临时库表必须以tmp_开头并以日期为后缀, 例如tmp_test01_20161218
 
l   备份库备份表必须以bak_开头并以日期戳为后缀, 例如bak_test01_20161218
 
l  所有存储相同数据的列名和列类型必须一致
 
CREATE TABLE customer_inf(
 
customer_inf id int unsigned auto increment not null comment '自增id',
 
customer_id int unsigned not null comment 'customer_login表的自增id,
 
.........................................
 
CREATE TABLE order_master(
 
order_id id int unsigned not null auto increment comment '订单ID',
 
customer_id int unsigned not null comment '下单人ID',
 
.........................................
 
基础规范
 
l  使用INNODB存储引擎
 
支持事务,行级锁,更好的恢复性,高并发下性能更好
 
l  INNODB表必须有主键列,使用auto_increment
 
l  数据库和表的字符集统一使用UTF8
 
统一字符集可以避免由于字符集转换产生的乱码
 
MySQL中UTF8字符集汉字占3个字节,ASCII码占1个字节
 
l  表必须有主键
 
l  所有表和字段都需要添加注释
 
使用comment添加表和列的备注
 
好处:从一开始就进行数据字典的维护和整理
 
l  表数量不超过300个
 
l  尽量控制单表数据量的大小,建议数据控制在500万行以内
 
500万并不是MySQL数据库的限制,MySQL最多可以存储多少万数据?
 
这种限制取决于存储设置和文件系统
 
  可以用历史数据归档,分库分表等手段来控制数据量大小
 
l  禁止在数据库中存储图片,视频和文件等二进制数据
 
把图片或文件存储到相应的文件服务器中,数据库中只存放图片或文件的地址信息
 
通常文件很大,查询IO操作耗时,会影响数据库的性能
 
利用更有效的利用缓存,避免读入无用的冷数据
 
经常一起使用的列放到一个表中
 
l  禁止在线上做数据库压力测试
 
l  禁止从开发环境,测试环境直连生产环境数据库
 
数据库各个环境之间要进行隔离
 
l  临时表和备份表必须定期清理(备份归档)
 
库表设计
 
l  禁止使用分区表
 
分区表在物理上表现为多个文件,在逻辑上表现为一个表
 
谨慎选择分区键,跨分区查询效率可能更低
 
 对于大表建议采用物理分表的方式管理大数据
 
l  拆分大字段和访问频率低的字段,分离冷热数据
 
尽量做到冷热数据分离,减小表的宽带
 
MySQL限制最多存储4096列,每一行数据的大小不能超过65535个字节
 
减少磁盘 IO,保证热数据的内存缓存命中率
 
l  按日期时间分表需符合YYYY[MM][DD][HH]格式
 
l  采用合适的分库分表策略。
 
字段设计
 
l  避免使用TEXT,BLOB数据类型
 
建议把BLOB或TEXT列分离到单独的扩展表中
 
l  优先选择符合存储需要的最小的数据类型, 使用INT UNSIGNED存储IPV4
 
将字符串转化为数字类型存储:
 
INET_ATON('255.255.255.255')=4294967295
 
将数字类型转化为字符串:
 
INET_NTOA(4294967295)='255.255.255.255'
 
l  使用TINYINT来代替ENUM类型
 
l  表字符集尽量选择UTF8
 
l  避免使用ENUM枚举数据类型
 
修改ENUM值需要使用ALTER语句
 
ENUM类型的ORDER BY操作效率低,需要额外操作
 
禁止使用数值作为ENUM的枚举值
 
l  所有字段均定义为NOT NULL
 
索引NULL列需要额外的空间来保存,所以要占用更多的空间
 
进行比较和计算时候要对NULL值做特殊处理,索引会失效
 
l  使用UNSIGNED存储非负整数
 
无符号值取值范围:
 
UNSIGNED INT (0--4294967295)
 
l  同财务相关的金额类数据,必须使用decimal类型
 
Decimal类型为精准浮点数,在计算时不会丢失精度
 
占用空间由定义的宽度决定
 
可用于存储比bigint更大的整数数据
 
l  尽量避免使用字符串存储日期型数据
 
缺点1:无法用日期函数进行比较和计算
 
缺点2:用字符串存储日期要占用更多的空间
 
l  使用TIMESTAMP或DATATIME类型存储时间
 
TIMESTAMP  1970-01-01 00:00:01 --2038-01-19  03:14:07
 
l  INT类型固定占用4字节存储,TIMESTAMP占用4字节和INT相同,但比INT可读性高
 
l  禁止在数据库中存储明文密码
 
索引规范
 
l  限制每张表的索引数量,建议单表索引数量不超过5个
 
l  禁止给表中的每一列都建立单独的索引,单个索引中的字段数不超过5个
 
l  每个Innodb表都要有一个主键
 
不使用更新频繁的列作为主键,不使用多列主键
 
主键建议选择使用自增ID值
 
l  不使用更新频繁的列
 
l  为经常需要排序、分组和联合操作的字段建立索引
 
常见索引列建议
 
SELECT,UPDATE,DELETE语句的WHERE从句中的列
 
包含在ORDER BY,GROUP BY,DISTINCT中的字段
 
多表JOIN的关联列
 
l  为常作为查询条件的字段建立索引
 
l  删除不再使用或者很少使用的索引
 
l  最左前缀匹配原则,非常重要的原则。
 
l  尽量选择区分度高的列作为索引
 
l  避免建立冗余索引和重复索引
 
index(a,b,c), index(a,b) ,index(a)
 
 a列是冗余索引
 
l  对于频繁的查询优先考虑使用覆盖索引
 
覆盖索引:就是包含了所有查询字段的索引
 
  全部字段不但是指where从句中出现的列,也包括出现在select从句,order by和group by从句中的列
 
  覆盖索引的好处:避免Innodb表进行索引的二次查找
 
  可以把随机IO变为顺序IO加快查询效率
SQL设计规范
 
l  避免隐式转换,会导致索引失效
 
l  充分利用前缀索引
 
l  必须是最左前缀
 
l  JOIN消耗较多内存,产生临时表
 
l  避免在数据库中进行数学运算
 
l  WHERE从句中禁止对列进行函数转换和计算
 
对列进行函数转换或计算会导致无法使用索引
 
where date(createtime)='20160901'
 
改进:
 
 where createtime >= '20160901' and createtime < '20160902'
 
l  使用不等于(!= 或者 <>),无法使用索引
 
l  使用LIKE操作(如'%abc...')时,无法使用索引
 
l  拒绝大SQL,拆分成小SQL
 
不支持SQL并行查询,MySQL一个SQL只能使用一个CPU进行计算
 
l  避免使用JOIN关联太多的表
 
每join一个表会多占用一部分内存(join_buffer_size)
 
会产生临时表操作,影响查询效率
 
 MySQL最多允许关联61个表,建议不超过5个
 
l  程序连接不同的数据库使用不同的账号,禁止跨库查询
 
l  用UNION ALL而不是UNION
 
UNION会把所有数据放到临时表中后再进行去重操作
 
UNION ALL不会再对结果集进行去重操作
 
l  禁止使用select *进行查询及没有字段列表的insert操作
 
l  禁止单条SQL语句同时更新多个表
 
l  尽量不使用select *,而使用SELECT <字段列表>查询
 
SELECT *返回结果中包含很多并不需要的字段,消耗更多的CPU和IO以及网络带宽资源
 
无法使用覆盖索引
 
行为规范
 
l  批量导入、导出数据必须提前通知DBA协助观察
 
超过100万行的批量写操作,要分批多次进行操作
 
l  对大表数据结构的修改一定要谨慎,会造成严重的锁表操作,尤其是生产环境,是不能忍受的
 
对于大表使用pt-online-schema-change修改表结构,不会锁表
 
避免大表修改产生的主从延迟
 
避免在对表字段进行修改时进行锁表
 
l  推广活动或上线新功能须提前通知DBA,请求压力评估
 
l  对于程序连接数据库账号,遵循权限最小原则
 
l  程序使用的账号原则上不准有drop权限
 
l  不使用super权限连接数据库
 
禁止为程序使用的账号赋予super权限
 
super权限只能留给DBA处理问题的账号使用
 
l  对单表的多次alter操作必须合并为一次操作
 
l  产品出现非数据库导致的故障时及时通知DBA协助排查
 
l  数据库数据丢失,及时联系DBA进行恢复
 
l  重大项目的数据库方案选型和设计必须提前通知DBA参与
 
l  对特别重要的库表,提前与DBA沟通确定维护和备份优先级
 
l  不在业务高峰期批量更新、查询数据库
 
l  批量导入、导出数据须提前通知DBA,请求协助观察
 
l  数据库DDL及重要SQL及早提及DBA评审
 
l  提及线上DDL需求,所有SQL语句须有备注说明
 
上述就是小编为大家分享的MySQL开发设计规范有哪些了,如果刚好有类似的疑惑,不妨参照上述分析进行理解。

(编辑:重庆站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读