MySql数据库开发规范

根据之前写的文章内容，结合网上找到的资料，整理出下面的MySql数据库规范作为参考。

数据库命名规范

1. 所有的数据库对象名称(包括库名、表名、列名等等)必须以小写字母命名，每个单词之间用下划线分割
2. 所有的数据库对象名称禁止使用MySQL保留关键字
3. 数据库对象的命名要能做到见名知意，并且不要超过32个字符
4. 数据库中用到的临时表以tmp为前缀并以日期为后缀
5. 数据库中用到的备份表以bak为前缀并以日期为后缀
6. 在不同的库或表中，要保证所有存储相同数据的列名和列类型必须一致

数据库基本设计规范

1. 所有表如果没有特殊需求，都要使用Innodb存储引擎。Innodb存储引擎支持事务、行级锁、更好的恢复性、高并发下性能更好。
2. 数据库和表的字符集统一使用UTF8字符集，避免由于字符集的转换产生乱码。
3. 所有的表和字段都需要添加注释。使用comment从句添加表和列的备注，从一开始就把数据字典维护好
4. 控制单表数据量的大小，建议控制在500万行以内。
   可以采用历史数据归档(常见于日志表)和分库分表的方式控制单表数据的大小。
5. 谨慎使用MySQL分区表，避免跨分区查询，否则效率很低。
   分区表在逻辑上表现为一个表，但是在物理上将数据存储在多个文件。最好能将分区表的不同分区文件存储在不同的磁盘阵列上。
6. 表中的列不要太多，尽量做到冷热数据分离，减小表的宽度。
   减少表的宽度，可以让一页内存中容纳更多的行，进而减少磁盘IO，更有效的利用缓存。
7. 经常一起使用的列尽量放到一个表中，避免过多的关联操作。
8. 禁止在表中建立预留字段。
   修改列的类型会所锁表。修改一个字段类型的成本要高于增加一个字段。
9. 禁止在数据库中存储图片、文件等

数据库字段设计规范

1. 优先为表中的每一列选择符合存储需要的最小的数据类型
   列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，影响性能。
2. 每个字段尽可能具有NOT NULL属性
3. 避免使用Blob、Text及ENUM类型
4. 使用datetime、timestamp存储时间类型，禁止使用字符串替代
5. 使用数字类型存储IP地址，用INET_ATON、INET_NTOA可以在IP地址和数字类型间转换。
6. VARCHAR类型的长度应该尽可能短。
   VARCHAR类型虽然在硬盘上是动态长度的，但是在内存中占用的空间是固定的最大长度。

数据库索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过5个
   索引并不是越多越好。索引可以提高查询的效率，但会降低写数据的效率。有时不好的索引还会降低查询的效率。
2. 禁止给表中的每一列都建立单独的索引。设计良好的联合索引比每一列上的单独索引效率要高出很多。
3. 每个Innodb表都必须有一个主键，且不使用更新频繁的列作为主键，不使用多列主键。不使用UUID、MD5、字符串列作为主键。最好选择值的顺序是连续增长的列作为主键，所以建议选择使用自增ID列作为主键
4. 建议在下面的列上建立索引：
   在SELECT，UPDATE，DELETE语句的WHERE从句上的列。在ORDER BY，GROUP BY，DISTINCT上的列。多表JOIN的关联列。
5. 区分度最高的列放在联合索引的最左侧。使用频繁的列放在联合索引的最左侧。
6. 避免冗余的索引，如：primary key(id)，index(id)，unique index(id)
7. 避免重复的索引，如：index(a,b,c)，index(a,b)，index(a)
   重复的和冗余的索引会降低查询效率，因为MySQL查询优化器会不知道该使用哪个索引。
8. 不要依靠外键保证参照完整性，在业务端实现参照完整性的要求。建立外键约束后的表在插入数据时要进行数据参照完整性检查，这会导致消耗数据库性能。虽然不使用MySql自带的外键，但一定在表与表之间的关联键上建立索引。

数据库SQL开发规范

1. 在程序中，建议使用预编译语句进行数据库操作。预编译只编译一次，多次使用，比SQL效率高。
2. 避免数据类型的隐式转换。隐式转换会导致索引失效。
3. 避免使用双%号或前置%号的查询条件，这样无法利用到索引。
4. 禁止在查询中使用select *
5. 避免使用子查询，子查询会产生临时表，临时表没有任何索引，数据量大时严重影响效率。建议把子查询转化成关联查询。
6. 避免使用JOIN关联太多的表。
7. 尽量减少同数据库的交互次数，数据库更适合处理批量操作。
8. 使用IN代替OR
9. 禁止在where从句中对列进行函数转换和计算，会导致索引无效。
10. 在不需要去重的情况下，要使用UNION ALL代替UNION。