MySQL数据库设计规范

一、数据库命名规范

1.所有数据库对象名称必须使用小写字母并用下划线分割

因为mysql数据库在默认情况下，对数据库名称的大小写是敏感的：

不同的数据库名	DbName	dbname
不同的表名	Table	table	tabLe

2.所有数据库对象名称，禁止使用MySQL保留的关键字

select id,username,from,age from tb_user//执行语句会报错

上述情况可以通过在关键字上用单引号“ ` ”消除报错

select id,username,`from`,age from tb_user //第一个from会被识别成普通字符串，运行正确（依然不推荐使用）

所有Mysql 关键字的官方网站：点击进入

3.数据库对象的命名，要能做到见名识意，并且最好不要超过32个字符

太长的话会影响后续的使用，以及网络传输的开销

正确示例：

用户数据库	mc_userdb
用户账户表	user_account

4.临时表必须以tmp为前缀，并以日期为后缀

这样为后续清理临时表提供方便，提高效率；

5.备份库和备份表必须以bak为前缀，并以日期为后缀

6.所有存储相同数据的列名和列类型必须一致

如下图中两个表中都有customer_id字段，所以两个表对于相同字段的类型要求，必须保持一致：

 

二、数据库基本设计规范

MySQL5.5使用以前，默认使用的是MyISAM存储引擎

1.所有表必须使用 Innodb 存储引擎

如果之前是使用MyISAM存储引擎的话，建议更换为Innodb存储引擎，主要原因是：

1.MySQL5.6以后默认采用的都是 Innodb；

2.Innodb支持事物、行级锁，而且具备更好的恢复性，高并发下性能更好；

2.数据库和表的字符集统一使用UTF8（重点在于统一字符集，不限于UTF8）

原因：

1.统一字符集可以避免由于字符集转换产成的乱码；

2.UTF8兼容性更好，几乎包括我们常用的字符；

如果存储的数据仅为中文字符的话，那么可以使用GBK或者GB2312（GBK 是 GB2312的扩展 ,除了兼容GB2312外，它还能显示繁体中文，还有日文的假名）。

注意：MySQL中UTF8字符集汉字占3个字节，ASCII码占用1个字节；

3.所有的表和字段都需要添加注释

使用comment从句添加表和列的备注

目的：从一开始就进行数据字典的维护

4.尽量控制单表数据量的大小，建议控制在500万行以内

500万并不是Mysql 数据库的限制，而是因为单表过大时，修改表结构、备份、恢复都会有很大问题；

MySQL最多可以存储多少万数据，这取决于存储设置和文件系统；

具体措施：

可以用历史数据归档、分库分表等手段来控制单表数据量大小；历史数据归档常用于日志类的表，而分库分表常用于业务数据的表中。

5.谨慎使用MySQL分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表；如果真的需要用到分区表，尽量避免使用分区健，因为跨分区查询效率比较低；对于大表建议采用物理分表的方式管理大数据；

6.尽量做到冷热数据分离，减小表的宽度

mysql对存储数据的行数没有限制，但是限制最多存储4096列；

目的：减少磁盘的IO，保证热数据的内存缓存命中率；

避免使用 select * 

措施：将经常使用的列放到一个列中；

7.禁止在表中建立预留字段

通常开发过程中，程序员为了后续开发过程中遇到新的需求时，可以直接使用这些字段，而不用对表结构进行修改，通常这些预留字段是非常大的varchar类型，其实这种操作是不可取的。

原因：1.预留字段的名称很难做到见名识义；

            2.预留字段无法确认存储的数据类型，所以无法选择合适的类型；

            3.对预留字段类型的修改，会堆表进行锁定；

8.禁止在数据库中存储图片、文件等二进制数据

正确的做法：将这些大文件的数据存储在文件服务器中，而数据库中只记录存放文件的地址信息。

9.禁止在线上做数据库压力测试

10.禁止从开发环境、测试环境直连生产环境数据库

做到各个环境进行隔离

三、索引设计规范

1.限制每张表上的索引数量，建议单张表的索引不超过5个

原因：索引可以增加查询效率，但是同样会降低插入和更新的效率

Innodb 是按照“主键”的索引顺序来组织表

2.每个Innodb表必须有一个主键

不使用更新频繁的列作为主键，不建议使用多列主键（联合索引）

原因：Innodb本身是索引组织表的缘故。数据的索引频繁的被更新，那么数据存储的逻辑顺序就需要频繁的变动。那么必然会带来大量的IO操作，从而降低数据库的性能。对于存储数据比较的表，如果索引频繁更新，不仅IO操作会增加，而且会消耗大量的CPU资源；

不使用UUID、MD5、HASH、字符串列作为主键

原因：这些列无法保证数据的顺序增长。例如当采用UUID值作为主键时时，新增数据的UUID值不一定大于已经存在数据的UUID，那么这条数据就会插入到中间某个位置，从而带来大量的IO操作；

主键建议使用自增的ID值

3.常见索引列的建议

1）SELECT、UPDATE、DELETE 语句的 WHERER 从句中的列；

2）包含在 ORDER BY 、GROUP BY、DISTINCT中的字段；

3）多表JOIN的关联列；

4.如何选择索引列的顺序（从左至右的顺序使用）

1）区分度高的列放在联合索引的最左侧；如：主键、唯一索引；

2）尽量把字段长度小的列放在联合索引的最左侧；

3）使用最频繁的列放到联合索引的左侧；

5.避免建立冗余索引和重复索引，例如：

primary key(id)，index(id)，unique index(id)；

冗余索引：部分索引列重复，如 index(a,b,c)、index(a,b)、index(a)；

因为索引是从左到右使用，所以以上三个索引都将会被使用到；

6.对于频繁的查询优先考虑使用覆盖索引

覆盖索引详解

避免 Innodb 表进行索引的二次查找

可以把随机IO变为顺序IO加快查询效率；

7.尽量避免使用外键

避免使用外键约束，但一定要在表与表之间的关联键上建立索引；

外键可用于保证数据的参照完整性，但建议业务端实现；

外键会影响父表和子表的写操作，从而降低性能；

四、数据库字段设计规范

1.将字符串转换为数字类型存储

可利用 INET_ATON 函数和 INET_NTOA函数，完成字符串与数字类型的转换；

IP地址转换成int类型：INET_ATON('255.255.255.255')=4294967295，此时需要4个字节就可以，因为int存储只需要4个字节；

int类型转换成IP地址：INET_NTOA(4294967295)='255.255.255.255'，此时需要至少15个字节存储ip地址；

2.优先选择符合存储需要的最小的数据类型

对于非负型的数据来说，要优先使用无符号整型来存储，因为无符号相对于有符号来说，可以多出一倍的存储空间；

varchar(n) 中的n代表的是字符数，而不是字节数；

使用UTF8存储汉字varchar(255)=765个字节；