MySql设计规范简介

数据库命名规范

1. 所有数据库对象名称必须使用小写字母并用下划线分割，msql数据库在默认情况下是对大小写敏感的，尤其是在Linux（Linux系统下本身对大小写敏感）系统下。

2. 所有的数据库对象名称禁止使用mysql保留关键字，如果使用的mysql保留关键字，在使用的时候要使用两个单引号将字段名包含，例如’from’，在今后的数据库字段命名中禁止使用mysql保留关键字。

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

4. 所有的零时表必须以tmp为前缀并以如期为后缀。

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

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

数据库基础设计规范

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

5.6以后的默认引擎，支持事物，行级锁，更好的恢复性，高并发下性能更好。

2. 数据库和表的字符集统一使用UTF8

统一字符集可以避免由于字符集转换产生的乱码，mysql中UTF8字符集汉子点3个字节，ASCII码占用1个字节。

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

使用comment从句添加表和列的注释，从一开始就进行数据字典的维护。

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

500万并不是mysql数据库的限制；（注：mysql并没有对存储大小做限制，这种限制取决于存储设置和文件系统，32位系统下，单个文件的大小不能超过2G）。可以使用历史数据归档（日志类的表上，这类表都是一些增长比较迅速的表）、分库分表（一般是业务表，如订单表…）等手段来控制数据量的大小。

5. 谨慎使用mysql分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表（IO优化），谨慎现则分区键，跨分区查询效率可能更低；建议采用物理分表的方式管理大数据，具体考量更具具体业务判断。

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

mysql限制最多存储4096列，没行数数据的大小不能超过65535个字节；减少磁盘IO，保证热数据的内存缓存命中率；利用更有效的利用缓存，避免读入无用的冷数据；经常一起使用的列放在一个表中。

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

预留字段的命名很难做到见名识义；预留字段无法确认存储的数据类型，所以无法选择合适的类型；对预留字段类型的修改，会对表进行锁定。

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

9. 禁止在线上做数据库压力测试禁止从开发环境，测试环境直接连生产环境数据库。

数据库索引设计规范

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

       通常索引的数量和数据库表的列是成正比的；索引并不是越多越好，索引可以提高效率也能降低效率；索引可以增加查询效率，但同样也会降低插入和更新的效率；禁止给表中的每一列都建立单独的索引。Innodb是按照主键索引的顺序来组织表的。

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

不能使用更新频繁的列作为主键，不使用多列主键（联合索引作为主键），不使用UUID、MD5、HASH、字符串列作为主键，主键建议选择使用自增ID值

3.     常见索引列建议

Select，update，delete语句的where从句中的列；包含在order by，group by，distinct中的字段；多表的jion的关联列

4.     在联合索引中，索引的顺序是按照从左到右的顺序来试用的，所以索引列的顺序决定了查询是否能使用到索引，也决定了索引的性能是否高效

建立索引的目的：之所以建立索引，就是希望在查询时可以通过索引进行数据查找，从而减少磁盘的IO，增加查询的性能，所以我们的索引能够过滤出更少的数据，则我们从磁盘读入的数据就越少。

(a)区分度最高的列放在联合索引的最左侧

区分度是指这个列中唯一值的数量我们可以用列中不同的数据的数量和这个表中总行数的比率计算区分度，所以区分度最大的就是主键，因为比率接近于1

(b)尽量把字段长度小的列放在联合索引的最左侧

(c)使用最频繁的列放在联合索引的左侧

(d)避免简历冗余索引和重复索引

(e)对于频繁的查询优先考虑使用覆盖索引

       覆盖索引：就是包含了所有查询字段的索引

       避免Innodb表进行索引的二次查找；可以把随机IO变为顺序IO加快查询效率

(f)尽量避免使用外键

不建议使用外键约束，但一定在表与表之间的关联键上建立索引；外键可用于保证数据的参照完整性，但建议在业务端实现；外键会影响父表和子表的写操作从而降低性能

数据库字段设计规范

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

       将字符串转化为数字类型存储

       （INET_ATON(‘255.255.255.255’)=4294967295）

       （INET_NTOA(‘4294967295’)= ‘255.255.255.255’）

       对于非负性的数据来说，要优先使用无符号整型来存储，无符号相对于有符号可以多出一倍的存储空间，对于非负数据采用无符号整型进行存储；VACHAR(N)中N代表的是字符数，而不是字节数。

2. 避免使用text、blob数据类型

       建议吧blob或是text列分离到单独的扩展表中

3. 避免使用ENUM数据类型

       修改ENUM值需要使用ALTER语句

       ENUM类型的ORDER BY操作效率低，需要额外操作

       禁止使用数值作为ENUM的枚举值

5.     尽可能把所有列定义为NOT NULL

索引NULL列需要额外的空间来保存，所以要占用更多的空间；

6.     字符串存储日期型的数据（不正确的做法）

缺点一：无法用日期函数进行计算和比较

缺点二：用字符串存储日期要占用更多的空间