MySQL开发设计规范有哪些

这期内容当中小编将会给大家带来有关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开发设计规范有哪些了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。