绝对必备：MySQL数据库开发的完整规范指南

      在进行MySQL数据库开发时，遵循一定的规范和最佳实践可以确保代码的可维护性、可扩展性和性能，从而确保数据库系统的稳定运行和长期发展。

以下是MySQL数据库开发的几个关键规范：

一、基础规范

• 存储引擎非特殊情况使用InnoDB，使用其他存储引擎需申请。

• 统一采用utf8mb4字符集。

• 统一字符集可以避免由于字符集转换产生的乱码。

• 绝不能使用test作为库名。

• 对于图像类、文档类存储，建议在MySQL数据库之外进行存储，比如使用文档服务器或者文档数据库。

• 所有表、字段都应添加注释 。

• 对于大表修改表结构使用gh-ost、pt-online-schema-change。

二、命名规范

• 所有SQL语句编写统一大小写风格，其中数据库关键字大写，库名、表名、字段名使用小写字母，须见名知意。

• 数据库对象命名禁止超过30个字符。

• 命名避免使用 Mysql 的保留字和系统关键字。

• 临时库、表名必须以tmp为前缀，并以日期为后缀。

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

• 索引命名：

                非唯一索引以 “idx_字段1_字段2” ，

                唯一索引以 “uniq_字段1_字段2” 命名，

                外键以”fk_字段1”命名，

                主键以“pk_字段1”命名。

三、表结构规范

• 除了特殊的日志表，每个表均要求有主键，尽量不使用字符串列做主键，主键字段或组合字段必须满足非空属性和唯一性要求。

• 主键字段不超过3个。

• 表之间的关联查询使用主键作为关联字段。

• 对较少访问的varchar/blob/text等大字段，尽量单独拆成一个表。

• 用临时表业务需特别申请，无高可用保障。

• 禁用外键约束，容易出现死锁，可能影响性能。

四、字段类型设计规范

• 字段默认情况下尽可能为非空 NOT NULL 。对于字段能否设为NULL，建议在SQL建表脚本中明确指明，不应使用缺省。

• 字段默认情况尽可能设置默认值。字符型的默认值为一个空字符值串，数字型的默认值为0,设置默认值能节省空间，提高索引的效率，让查询变得不繁琐。

• 越简单越好，将字符转化为数字、使用TINYINT代替ENUM类型。

• 使用INT UNSIGNED替代char(15)存储ipv4地址，通过MySQL函数inet_ntoa和inet_aton来进行转化。

• 不使用负数值的字段须加入UNSIGNED属性。

• 仅存储年使用YEAR类型，日期使用DATE类型

• 时间类型使用datetime，不要使用timestmp。

• 钱币等精确浮点类型使用DECIMAL类型。

• 数值字段增长上限不大，就不要用BIGINT。

• 尽可能不使用TEXT、BLOB类型。对于报文之类的大文本，可以用TEXT、BLOB类型，建议将该列单独设计为一张表，并通过关联字段与主表关联进行查询或其他操作。

• 更小的字段类型和更小的字符数通常对请求的处理更快，占用的资源更少，选择合适的数据类型。

• 同一意义的字段设计定义必须相同。

• 禁用在数据库中存储明文密码。

五、索引设计规范

• 不使用更新频繁的列作为主键，如无特殊要求，使用自增id作为主键。对于并发插入量较大且需要物理主键的表，可以通过类似JAVA里的guid键值来代替。

• 索引创建选择唯一性较强的字段。

• 索引选择数据类型较短的字段。

• 合理创建联合索引，联合索引(a,b,c) 等于 (a) 、(a,b) 、(a,b,c)三个索引，索引中的字段数不超过5个。

• 新建的唯一索引不能和主键重复。

• 单张表的索引数量控制在5个以内。

• 使用前缀索引长度不超过8个字符。例如：alter table tablename add index indexname (column(8));

• 尽量避免使用外键，容易产生死锁，由上层应用程序保证约束。

• 筛选text 或较长varchar类型字段，需使用全文索引。

• 全文索引必须使用match函数， AGAINST函数，不支持％通配符匹配，例如：SELECT * FROM articles     WHERE MATCH (title,body)     AGAINST ('database' IN NATURAL LANGUAGE MODE)；

• 重要的SQL语句必须被索引，例如：Update\delete语句的where条件列；order by \group by\distinct字段。

• 多表JOIN的字段注意以下：

                    1）区分度最大的字段放在前面；

                    2）核心SQL优先考虑覆盖索引；

                    3）避免冗余和重复索引；

                  4）索引要综合评估数据密度和分布以及考虑查询和更新比例。

六、SQL查询规范

• SQL语句尽可能简单，大的SQL想办法拆分成小的SQL实现。

• 不要使用SELECT * ，查询具体要用到的字段。

• 禁止like “%abs”做where条件，会全表扫描且不能用索引。

• 除非必要，避免使用 != 等非等值操作符，会导致用不到索引。

• Where条件里不要对列使用函数，不会引用索引。

• 能确定返回结果只有一条时，使用limit 1（LIMIT分页注意效率，LIMIT越大，效率越低）

• 少用子查询，改用JOIN（子查询要在内存里建临时表）。

• 多表JOIN的字段，区分度最大的字段放在前面

• IN条件里的数据数量要尽量少，超过200个用EXIST代替IN

• 禁止单条语句同时更新多个表。

• 杜绝大事务，事务要尽量简单，整个事务的时间长度不要太长。

• 只读查询语句不要显式开启事务，例如不要加begin或start transaction。

• 多条INSERT语句使用bulk insert提交（INSERT INTO table VALUES(),(),()……）。

• 避免大表join。

• SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'，其中id列为非字符类型。

七、存储过程及函数使用规范

• 在存储过程中，MySQL禁止使用下述语句：

         CHECK TABLES

         LOCK TABLES, UNLOCK TABLES

         LOAD DATA, LOAD TABLE

         OPTIMIZE TABLE

SQL预处理语句（PREPARE、EXECUTE、DEALLOCATE PREPARE），目前仅适用于存储过程;不适用于存储函数和触发器;也不适用于在存储函数或者触发器里面调用含有预处理语句的存储过程。

•  创建存储过程以 proc_开头命名，函数以 func_开头命名。

八、视图使用规范

• 数据库不能包含具有相同名称的表和视图。

• 定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

• 不能将触发程序与视图关联在一起。

• 视图以v_name命名。

九、触发器使用规范

建议禁止使用触发器，触发器可以理解为是一个隐藏的存储过程，它不需要调用，不需要显示调用，维护起来容易被忽略。可以使用存储过程来替代。

十、数据库设计规范

• 第一范式(1NF)：数据表的每一列都要保持它的原子特性，也就是列不能再被分割。

• 第二范式(2NF)：属性必须完全依赖于主键，不能只依赖于主键的一部分。

• 第三范式(3NF)：所有的非主属性不依赖于其他的非主属性，非主键列必须直接依赖于主键，不能存在传递依赖；即不能存在非主键列A依赖于非主键列B，非主键列B依赖于主键的情况。

十一、数据库维护规范

• 备份策略：定期备份数据库，确保在出现问题时能够恢复。

• 监控和性能调优：监控数据库性能，及时调整参数和索引，优化查询。

• 错误处理和日志记录：处理数据库错误，并记录日志以供排查。

• 数据一致性：确保数据的一致性，避免脏数据的产生。

十二、安全规范

• 用户权限管理：按照最小权限原则分配用户权限，避免过多权限导致的安全风险。

• 加密敏感数据：对于敏感数据，建议进行加密处理。

• 防范SQL注入：使用预编译查询，并避免动态生成SQL语句。

• 审计和日志记录：记录关键操作日志，确保可追溯性。

十三、文档和代码规范

• 文档化：为数据库设计和SQL查询编写适当的文档，便于他人理解和维护。

• 代码审查：定期进行代码审查，确保符合规范，并共享知识。

遵循这些规范有助于构建稳定、安全、性能良好的MySQL数据库，并使开发团队的合作更加顺畅。

加入数据库技术交流群：

进群福利：

1、知识共享与学习机会

2、问题求助与解决支持

3、技术沉淀和分享平台

4、及时了解行业动态