MySQL数据库开发规范

西木风落

已于 2022-03-13 17:13:31 修改

阅读量1.4k

点赞数 1

分类专栏：数据库学习文章标签： mysql mysql开发规范索引建立规范查询规范

于 2016-10-12 11:49:32 首次发布

原文链接：https://zhuanlan.zhihu.com/p/165940906

版权

数据库学习专栏收录该内容

15 篇文章 1 订阅

订阅专栏

MySQL开发规范本身包含几部分：基本命名和约束规范，字段设计规范，索引规范和使用规范。所有规范DBA一般会按照【高危】、【强制】、【建议】三个级别进行标注，遵守优先级从高到低。对于不满足【高危】和【强制】两个级别的设计，需要重新修改。

1. 基本命名和约束规范

1.1 数据库设计

库的名称格式：业务系统名称_子系统名，同一模块使用的表名尽量使用统一前缀。
库名一般为小写字母，长度控制在32个字符以内，必须显式指定字符集，并且字符集只能是utf8或者utf8mb4。
分库名：库通配名_编号，编号从0开始递增

1.2 表设计

创建表必须显示指定字符集为utf8或utf8mb4。如果需要存储emoj表情，需要使用UTF8mb4(MySQL 5.5.3以后支持)
表名要求模块名强相关，表和列的名称必须控制在32个字符以内，表名只能使用字母、数字和下划线，一律小写；
创建表时必须显式指定表存储引擎类型，如无特殊需求，一律为InnoDB，因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性，为业界使用最多的MySQL存储引擎。
表的主键选择自增ID，类型为int或bigint。标识表每一行记录的字段不要设置为主键，建立unique key索引。不选择uuid作为主键原因见：MySql为什么不推荐使用UUID做主键_西木风落-CSDN博客
核心表设计字段必须包含create_time, update_time, create_operator, update_operator，便于操作审计查验；
中间表用于保留中间结果集，名称必须以tmp_开头。备份表用于备份或抓取源表快照，名称必须以bak_开头。中间表和备份表定期清理。
建议对表里的blob、text等大字段，垂直拆分到其他表里，仅在需要读这些对象的时候才去select。
对于超过100万记录的表，做alter操作要慎重，会锁全表；
单表数量要控制在5千万一下；分表原则，要尽量将数据均匀分散到各表，注意分表的扩展性和同一个用户的相关记录尽量保持相同的分表位。

1.3 列数据类型优化

表中的自增列（auto_increment属性），推荐使用bigint类型。无符号int存储范围为-2147483648~2147483647（大约21亿左右），溢出后会导致报错。
表中字段尽量NOT NULL属性，因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差等问题。
字段类型在满足需求条件下越小越好，使用UNSIGNED存储非负整数，实际使用时候存储负数场景不多；状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空间。
不推荐使用enum、set，枚举值写死了，变更不方便。推荐使用tinyint或smallint。
使用TIMESTAMP存储时间，因为datetime占用8字节，timestamp仅占用4字节，更为高阶的方法，选用int来存储时间，使用SQL函数unix_timestamp()和from_unixtime()来进行转换。
使用DECIMAL存储精确浮点数，用float有的时候会有问题；使用varchar存储变长字符串，当然要注意varchar(M)里的M指的是字符数不是字节数；
文本数据尽量用varchar存储。因为varchar是变长存储，比char更省空间。MySQL server层规定一行所有文本最多存65535字节，因此在utf8字符集下最多存21844个字符，超过会自动转换为mediumtext字段。而text在utf8字符集下最多存21844个字符，mediumtext最多存2^24/3个字符，longtext最多存2^32个字符。一般建议用varchar类型，字符数不要超过2700。
业务中IP地址字段推荐使用int类型，因为int只占4字节,char(15)占用至少15字节. 可以利用函数 select inet_aton('192.168.3.1') 将ip转换成int，通过select inet_ntoa(3232236044) 转换成IP。而且char(15)方式只能存储IPv4，存储不了IPv6

1.4 索引优化

InnoDB表必须主键为id int/bigint auto_increment，且主键值禁止被更新
建议主键的名称以“pk_”开头，唯一键以“uk_”开头，普通索引以“idx_”开头，一律使用小写格式，以表名/字段的名称或缩写作为后缀。
InnoDB和MyISAM存储引擎表，索引类型必须为BTREE；MEMORY表可以根据需要选择HASH或者BTREE类型索引。
单个索引中每个索引记录的长度不能超过64KB，单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则
在建立索引时，多考虑建立联合索引，并把区分度最高的字段放在最前面；
DML和order by和group by字段要建立合适的索引，避免索引的隐式转换和冗余索引，比如里已经存在key(a,b)，则key(a)为冗余索引，需要删除。隐式转换比如：字段定义为varchar，但传入的值是个int，就会导致全表扫描，要求程序端要做好类型检查。
建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾

1.5 分库和分区表规范

分区表的分区字段（partition-key）必须有索引，或者是组合索引的首列。
单个分区文件不超过2G，总大小不超过50G。建议总分区数不超过20个。
访问分区表的SQL必须包含分区键。

1.6 关于分区表概念介绍

什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

表分区有什么好处？

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。
和单个磁盘或者文件系统相比，可以存储更多数据。
优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

分区表的限制因素

一个表最多只能有1024个分区
MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
在创建分区时，如果表中存在主键或者唯一键，那么分区列必须包含主键或者唯一键的部分或者全部列。
分区表中无法使用外键约束。
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

MySQL支持的分区类型有哪些？

通过命令：show variables like '%partition%' :

RANGE分区：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
LIST分区：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
HASH分区：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
KEY分区：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

2. SQL 规范

2.1 DQL 规范

SELECT语句必须指定具体字段名称，禁止写成*。因为select *会将不该读的数据也从MySQL里读出来，造成网卡压力。
insert语句指定具体字段名称，insert into…values(XX),(XX),(XX)…，values不超过5000个，避免主从同步缓慢；
in值列表限制在500以内。例如select… where userid in(….500个以内…)，这么做是为了减少底层扫描，减轻数据库压力从而加速查询。
SELECT语句不要使用UNION，推荐使用UNION ALL，因为union all不需要去重，节省数据库资源，提高性能。
索引列不要使用函数或表达式，否则无法利用索引。WHERE 子句中禁止只使用全模糊的LIKE条件进行查找，必须有其他等值或范围查询条件，否则无法利用索引
减少使用or语句，可将or语句优化为union，然后在各个where条件上建立索引
分页查询，当limit起点较高时，可先用过滤条件进行过滤。
禁止跨db的join语句，不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用join来代替子查询。
order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据，这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。
禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源，降低数据库实例可扩展性。推荐都在程序端实现。
禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。因为会导致主从不一致，导致数据错乱。建议加上order by PK。
UPDATE、DELETE语句不使用LIMIT ，容易造成主从不一致。

文章参考：分享一份大佬的MySQL数据库设计规范，值得收藏 - 知乎