mysql编程规范

数据库没有规范，那就是参差不齐，为了整合各个项目开发人员，特推出公司的数据库开发规范！

1 概述

1.1. 前言
目前各项目组数据库开发人员都是按照自己的思维逻辑来进行编写数据库代码，没有统一的编程规范，产生了很多弊端：
1. 可读性差。读别人写的一个sql花费的时间，比自己写一个程序的花费时间还要长；非但别人看不懂，时间久了连自己也看不懂了。
2. 可维护性差。程序越写越长，越改越差。
3. 可操作性差。更新sql乱七八糟
4. 效率和性能差。
1.2. 文档目的
本规范的主要目的是规范数据库设计与开发，尽量避免由于数据库设计与开发不当而产生的麻烦, 好的规范，在执行的时候可以培养出好的习惯，好的习惯是软件质量的很好保证。
1.3. 读者对象
本文的目标读者群：各项目组开发，设计架构与测试人员等。
本文档制定了公司各系统中，MYSQL数据库对象的设计规范为系统的设计和开发工作提供统一规范的参考依据，提高系统的规整性和代码的可读性，减轻维护工作量，提高工作效率。
2 基础规范
(1) 全部使用InnoDB引擎，MyISAM适用场景非常少（非特殊情况）
(2) 数据库字符集使用UTF8
(3) 所有表字段都需要添加注释, 所有字段均定义为NOT NULL
(4) 所有表都要有主键,类型为自增int型，不能用UUID/HASH/MD5类型作为主键
，并且主键id不能有业务意义。
 (5)采用队列方式合并多次写请求，持续写入，避免瞬间压力，防止意外崩溃丢失数据
(6)数据表创建的时候应该有适当的索引（非特殊表），而不是到很大的时候再添加索引
(7)冷热数据进行水平拆分，提高效率 
(8)读写分离，缓解写库压力
(9)单表数据量建议控制在2000W以内，单表20GB以内
(10)单实例下数据表数量不超过2000个，单库下数据表数量不超过500个
(11)最少授权，用户只授予最基础权限需求，delete,insert ,update,select 
(12)压力分散，在线表和归档表（历史表）分开存储
(13)不在数据库中存储图?、文件等大数据，大字段建议单独设计表，通过关联进行查找
(14)库名，表名，字段名最多支持64个字符，为了统一规范、易于辨识以及减少传输量，不超过30个字符
(15)创建表的时候各字段不要加上引号，以免后续出现麻烦
(16)不使用数据库关键字和保留字作为表名与字段名
(17)严禁使用带空格的名称来对字段和表命名；在产生数据库脚本并重新加载的时候可能会出现意想不到的错误而被迫终止
(18)库名、表名、字段名必须使用小写字母，“_”分割与数字组成。
(19)库名、表名、字段名全英文或全中文见名知意, 言之有意，建议使用名词而不是动词，也不要半洋半中
3 数据库对象设计规范
3.1 表设计规范
1、数据库的表数据量大小与读写频率提前预估，是否需要分表分区，前期设计中必须规划好； 
2、和其他表有关联的表，和其他表功能一致的，尽量使用相同的列名，字段类型以及长度；
3、表临时用加上 tmp/temp 前缀/后缀，统计表加上 stat/statistic 前缀/后缀，历史归档加上完整日期，备份表加上bak
4、存储时间（精确到秒）建议使用TIMESTAMP类型，因为TIMESTAMP使用4字节，DATETIME使用8个字节，但是在建立连接的时候要设置时区信息。
3.2 数据库设计规范
1、数据库数据表名使用小写英文以及下划线组成,尽量说明是那个应用或者系统在使用的.，一个业务一个数据库名  如：aatv_cdn
2、数据库采用长连接，异常断开连接支持自动再连接，使用连接前做有效性校验
3、数据库创建时大小写一致，不能出现部分大写部分小写 如：CDNData  cdndata
3.3 字段设计规范
1、能用int的就不用char或者varchar,能用tinyint的就不用int，能用varchar(20)的就不用varchar(255)，char也一样,更具具体业务不能随心所欲，合理的规划字段长度类型都是首要的事情
2、定长字符列使用CHAR类型, 不定长字符型使用VARCHAR类型
3.4 索引设计规范
1、索引的数量要控制：
(1) 单张表中索引数量不超过5个，建立索引字段要考虑到SELECT和INSERT,UPDATE,DELETE的平衡。
(2) 单子段上不要超过两个索引（即一个单字段最多可在上面建立一个单字段索引和一个组合索引包含这个字段）
(3) 单个索引中的字段数不超过5个，原则上不超过3个，索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。合理建立避免冗余，(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)
(4) 对字符串可以考虑使?用前缀索引，前缀索引长度不超过8个字符
2、主键准则（前面有体现）
(1) 表必须有主键，并且主键没有实际业务意义
(2) 尽量不使用更新频繁的列作为主键
(3) 不选择字符串列作为主键
(4) 不使用UUID MD5 HASH这些作为主键(数值太离散了)
(5) 默认使?非空的唯一键作为主键
(6) 建议选择自增或发号器
3、重要的SQL必须被索引，比如：
(1) UPDATE、DELETE语句的WHERE条件列
(2) ORDER BY、GROUP BY、DISTINCT的字段
4、多表JOIN的字段注意以下：
(1) 区分度最大的字段放在前面
(2) 避免冗余和重复索引
(3) 索引要综合评估数据密度和分布以及考虑查询和更新比例
5、索引禁忌
(1) 不在低基数列上建立索引，例如“性别”
(2) 不在索引列进行数学运算和函数运算
6、命名：非唯一索引以 idx_表名_字段1_字段2命名，唯一索引以uniq_表名_字段1_字段2命名
7、索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率
8、查看与表相关的SQL，符合最左前缀的特点建立索引。多条字段重复的语句，要修改语句条件字段的顺序，选择性高条件靠前,或者为其建立一条联合索引，减少索引数量,提高效率
9、使用explain或者sql优化器，尽量避免extra列出现：Using File Sort，Using Temporary 及时发现索引选择性差，没有合理使用索引的情况
建议索引(需要建索引的地方)
建议1:   频繁出现在where子句里的字段建议建立索引；
建议2:   用来和其他表关联的字段建议建立索引；
建议3:   索引字段建议有高的选择性和过滤性（count(distinct)/count(*)>0.6）；
建议4:   一般建议在查询数据量10%以下使用索引；
建议5:   WHERE子句的查询条件构成索引字段前导字段；
建议6:   选择性更高的字段放在组合字段索引的前导字段；
建议7:   如果字段选择性接近，则把频繁查询的字段放在前面；
建议8:   进行GROUP BY或者是ORDER BY的字段应在组合字段索引的前导字段；
4 Sql规范
(1) sql语句尽可能简单，短事务、快速执行、无阻塞，大的sql想办法拆成小的sql语句(充分利用QUERY CACHE和充分利用多核CPU)
(2) sql中不能使用sleep()
(3) 进行模糊查询时，禁止条件中字符串直接以“%”开头，如果有可以使用全文索引或者反向索引技术，提高效率
(4) 禁止在多表关联的时候，在非索引字段上的关联；
(5) 不要用select *，查询哪几个字段就select 那几个字段
(6) 除非必要，避免使用 != 等非等值操作符，会导致用不到索引；
(7) sql中使用到OR的改写为用 IN()(or的效率没有in的效率高) in里面数字的个数建议控制在1000以内
(8) SQL语句不可以出现隐式转换，比如 select id from 表 where id=’123′
(9) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写：
select id from tlimit 10000, 10;  =>  select id from t where id > 10000 limit10;
(10) 使用union all替代union
(11) 避免使?大表的JOIN，避免使用子查询 
(12) 减少关联表的数量，没有用的不要关联上
(13) 不用/少用FOR UPDATE、LOCK IN SHARE MODE，防止锁范围扩大化
(14)使用性能分析工具进行数据性能分析,提早发现问题
Sql explain  /  showprofile   /    mysqlsla
(15) IN条件里面的数据数量要少，我记得应该是500个以内，要学会使用exist代替in，exist在一些场景查询会比in快
(16) 能不用NOT IN就不用NOT IN，坑太多了。。会把空和NULL给查出来还有 
(17) 使?预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用； 
(18) 禁止使?order by rand()，将数据从磁盘中读取，进行排序，会消耗大量的IO和CPU。
(19) 禁?单条SQL语句同时更新多个表
(20) 尽力少用不等值WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引 
(21) 有时我们做设计时可以不严格遵循范式，在一些表上增加冗余字段来减少避免大表、多表的关联查询，来提高数据库的查询性能。
(22) 合理使用索引
让SQL合理使用索引的原则：
首先，看是否用上了索引。对于该使用索引而没有用上索引的SQL语句，应该想办法用上索引。
其次，看是否用上了合理的索引，特别是复杂的SQL语句，当其中Where子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高，反而导致性能的降低。
5 开发环境规范
开发环境的数据库服务器应该和线上一样,这样问题才能尽早的发现，线上的架构我们也会整理并发给开发。
6 数据库上线流程规范
(1) 上线单要写清数据库名，sql要遵循上面规范。
(2) 所有的建表需要确定建立哪些索引后才可以建表上线（非特殊情况）；
(3) 所有的改表结构、加索引操作都需要研发至少要提前1天邮件出来，给dba们评估、优化和审核的时间；
 (4) 批量导入、导出数据必须提前通知DBA协助观察
(5) 数据库更新全部在上午12点前完成，下午更新的话得有CTO邮件同意。核心业务有需要在早上八点以前配合代码上线的，请提前一天邮件通知DBA 
(6) 不在业务高峰期批量更新、更改数据
(7) 涉及到敏感信息的数据查询提取需要有CTO的邮件同意
（8）数据库更新数据应该有备份脚本，备份需要更新的数据，事后提上线单删除备份数据，数据量比较大的情况下，提前告知dba使用命令行备份。
7 其它