MySQL数据库设计SQL规范

1、库名、表名、字段名必须使用小写字母并采用下划线分割；

2、库名、表名、字段名支持最多32个字符，易于辨识以及减少传输量不要超过32； 3、库名、表名、字段名禁止使用MySQL保留关键字； 4、临时库、临时表名必须以tmp为前缀并以日期为后缀； 5、备份库、备份表名必须以bak为前缀并以日期为后缀；

---------------------------------------------------------------- 2 基本规范

1、使用INNODB存储引擎

5.5以后的默认引擘，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好； 2、表字符集使用UTF8

使用utf8字符集，如果是汉字，占3个字节，但ASCII码字符还是1个字节；统一，不会有转换产生乱码风险； 3、所有表都需要添加注释；

4、不在数据库中存储图片、文件等大数据； 5、禁止在线上做数据库压力测试；

6、禁止从测试、开发环境直连线上数据库；

----------------------------------------------------------------- 3 库表设计规范

1、尽量避免使用分区表

MySQL的分区表实际性能不是很好。

2、拆分大字段和访问频率低的字段，分离冷热数据

3、采用合理的分库分表策略，推荐使用HASH进行分表，表名后缀使用十进制数，下标从0开始

首次分表尽量多的分，避免二次分表，二次分表的难度和成本较高 4、按日期时间分表需符合YYYY[MM][DD][HH]格式 5、单表字段数控制在20个以内

6、一条完整的建表语句中应包含必要的字段、主键、合理的索引（综合代码中所有的条件语句创建合理的索引，主键必须要有）

------------------------------------------------------------------- 4 索引设计规范

索引是一把双刃剑，它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间 1、单张表中索引数量不超过5个； 2、单个索引中的字段数不超过5个；

对字符串使用前缀索引，前缀索引长度不超过10个字符；如果有一个CHAR(200)列，如果在前10个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个字符进行索引能够节省大量索引空间，也可能会使查询更快；

3、表必须有主键，不使用UUID、MD5、HASH作为主键，尽量不选择字符串列作为主键；主键建议选择自增id；

4、创建复合索引时区分度较大的字段放在最前面；不在低区分度的字段上创建索引，例如“性别”；

5、避免冗余或重复索引

合理创建联合索引（避免冗余），index(a、b、c)相当于index(a)、index(a、b)、index(a、、b、c)；

6、索引不是越多越好，按实际需要进行创建

每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能 7、不在索引列进行数学运算和函数运算； 8、尽量不要使用外键

外键用来保护参照完整性，可在业务端实现，对父表和子表的操作会相互影响，降低可用性；

9、不使用%前导的查询，如like“%xxx”，无法使用索引； 10、不使用反向查询，如not in / not like 无法使用索引，导致全表扫描

全表扫描导致buffer pool利用降低

--------------------------------------------------------------- 5 字段设计规范

1、尽可能不要使用TEXT、BLOB类型

删除这种值会在数据表中留下很大的\"空洞\"，可以考虑把BLOB或TEXT列分离到单独的表中

2、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围；浮点数的缺点是会引起精度问题 3、将字符转化为数字

4、使用TINYINT来代替ENUM类型

5、字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量

VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N；

VARCHAR(N)，N尽可能小，因为MySQL一个表中所有的VARCHAR字段最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存； 6、如果可能的话所有字段均定义为not null 7、使用UNSIGNED存储非负整数

同样的字节数，存储的数值范围更大。如tinyint有符号为-128-127，无符号为0-255 8、使用TIMESTAMP存储时间. 因为TIMESTAMP使用4字节，DATETIME使用8个字节,同时TIMESTAMP具有自动赋值以及自动更新的特性.

9、使用INT UNSIGNED存储IPV4

10、使用VARBINARY存储大小写敏感的变长字符串 11、禁止在数据库中存储明文密码

------------------------------------------------------------------- 6 SQL设计规范

1、使用预编译语句prepared statement

只传参数，比传递SQL语句更高效，一次解析，多次使用，降低SQL注入概率 2、尽量避免相同语句由于书写格式的不同，而导致多次语法分析 3、避免隐式转换

会导致索引失效，如select userid from table where userid=’ 1234’ 4、充分利用前缀索引

必须是最左前缀，不可能同时用到两个范围条件 5、避免使用存储过程、触发器、EVENTS等

让数据库做最擅长的事，降低业务耦合度，为sacle out、shading留点余地，避开BUG 6、避免使用大表的join

MySQL最擅长的是单表的主键/二级索引查询 Join消耗较多的内存，产生临时表 7、避免在数据库中进行数学运算 容易将业务逻辑和DB耦合在一起 MySQL不擅长数学运算和逻辑判断 无法使用索引

8、拒绝大SQL，拆分成小SQL 充分利用多核CPU

9、使用in代替or，in的值不超过1000个 10、禁止使用order by rand()

因为ORDER BY rand()会将数据从磁盘中读取，进行排序，会消耗大量的IO和CPU，可以在程序中获取一个rand值，然后通过在从数据库中获取对应的值 11、使用union all而不是union

12、程序应有捕获SQL异常的处理机制 13、禁止单条SQL语句同时更新多个表

14、不使用select * from 消耗cpu和IO、消耗网络带宽，无法使用覆盖索引，减少表结构变更带来的影响

-------------------------------------------------------------------- 7 行为规范

1、批量导入、导出数据必须提前通知DBA协助观察；

2、批量更新数据，如update、delete操作，需要DBA进行审查，并在执行过程中观察服务负载等各种状况；

3、禁止在主库上执行后台管理和统计类的功能查询； 4、禁止有super权限的应用程序账号存在；

5、产品出现非数据库导致的故障时及时通知DBA协助排查； 6、促销活动或上线新功能必须提前通知DBA进行流量评估； 7、数据库数据丢失，及时联系DBA进行恢复；

8、对单表的多次alter操作必须合并为一次操作，相同类型的写操作合并为一条语句； 9、不在MySQL数据库中存放业务逻辑；

10、重大项目的数据库方案选型和设计必须提前通知DBA参与； 11、对特别重要的库表，提前与DBA沟通确定维护和备份优先级； 12、不在业务高峰期批量更新、查询数据库；

13、 提交线上建表需求，必须详细注明所有相关SQL。

转载

http://ibisem.com/2015/06/19/mysql%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1sql%E8%A7%84%E8%8C%83/