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语句须有备注说明
正文到此结束
热门推荐
相关文章
近期评论
-
666
-
文章和留言都翻到11页了 没有OOM
-
-
朋友，翻页到11页，及以后，会出现OOM，无法访问
-
-
-
-
版本号是多少，你可以下载哪个代码仓库，jdk选1.8 直接跑就行
-
-
Loading...
![[HBLOG]公众号](https://www.liuhaihua.cn/img/qrcode_gzh.jpg)
