MySQL数据库规范

一.字符集和排序规则     

        同一个数据库实例内，应使用相同的字符集和排序规则。

        字符集应使用utf8mb4,排序规则应使用utf8mb4_bin。utf8mb4_bin大小写敏感（utf8字符集没有包含完整的Unicode字符）

        不同的字符集和排序规则，容易导致性能问题和大小写判断问题。

二.总体命名规则

     （1）不应使用MySQL关键字进行对象命名。（例：add，alter，drop等）

     （2）名字不应使用无意义字符，做到见名知义，应使用关键词的英文单词缩写，含义明确，关键词之间用下划线分割。

三.存储引擎

        对于MySQL5.0以上版本，应使用InnoDB存储引擎。

        相对于Myisam，Innodb存储引擎支持事务、行级锁、更好的数据恢复能力，更好的并发性能，同时对多核、大内存等硬件有更好的支持，支持数据热备份。

四.建表语句

     （1）无需指定ENGINE参数，默认使用Innodb。

     （2）必须有字段注释和表注释，方便后续维护。

     （3）必须设置自增列作为主键。

              自增列主键有利于插入性能的提高。（每次插入都是插入到最后，可减少数据页分裂和移动的频率）

              可以降低二级索引的空间，提升二级索引的内存命中率。

              可以减小page的碎片，提升空间和内存的使用。

     （4）字段

              一张表的每行记录的字段长度和不应超过65535个字节，不包含TEXT、BLOB类型

     （5）建议单表的字段个数不宜超过50个。

     （6）数据类型

              a.满足需求的前提下，应使用占用空间较小的数据类型，使用较简单的数据类型。能用int就不用char、varchar，（mysql处理int类型更简单）能用tinyint就不用int。

              b.timestamp类型存储数据的范围是‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’

              c.datetime类型存储数据的范围是‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

              d.不应使用float和double存放涉及金额的数据，应使用decimal（浮点类型容易出现精度问题，运算问题）。

              e.定长字段使用char类型，如果存储的值长度几乎相同也应使用char类型。

              f.不定长字段使用varchar类型，设置适当长度，而不是随意设置较大长度作为最大长度。

              g.如果没有包含负数，宜使用UNSIGNED（存储的数据范围会更大）。

     （7）外键

              不宜使用外键。外键与级联更新适用于单机低并发，不适合分布式高并发集群，级联更新影响速度。

     （8）约束

              适当建立约束。例如主键约束，非空约束，唯一约束。

              为表的字段加上默认值：日期字段加当前时间为默认值，状态字段加默认值。

              不宜使用check约束，MySQL对check约束支持不完善。

     （9）建议拥有以下字段（先后顺序代表优先级排序）

              创建日期  create_date，更新日期  update_date，创建者 create_by，更新者 update_by，删除标记  del_flag，备注信息 remarks。

     （10）需建立索引，创建索引应考虑索引原则及使用场景，且索引字段宜设置为not null。

五.查询语句

     （1）不宜使用select *（应尽量减少查询字段，尽可能使用到覆盖索引查询，减少IO，提升性能）

     （2）select count(字段名)统计记录数，该字段为null的记录不会统计到

     （3）多表连接时，每个字段需显示指定表名或表的别名作为前缀

     （4）多表连接时，应将过滤性比较大的表作为驱动表。关联字段需要有索引。表连接个数不宜超过3个，语句的嵌套层数不宜超过3层。（MySQL优化器较弱，降低复杂度，可降低出现性能问题的可能性）

     （5）进行数据比较时，如果两边类型不一致，应在一方加上类型转换的函数

     （6）sql中in包含的值应少于1000个

     （7）sql语句可用in代替or（in是范围查找，内部会对in的值进行排序查找，比or的效率高）

     （8）如果排序字段没有索引，应创建索引，或尽量减少排序。

     （9）如果两个结果集中没有重复数据无需去重，可以用union all代替union。union将结果集合并后进行唯一性过滤，会涉及到排序，大量的CPU运算，加大资源消耗及延迟。

     （10）使用合理的分页，提高效率。（先快速定位需获取记录的主键范围）

                select * from table_name limit 100000,10;可改写为

                select * from table_name where id >100000 limit 10;

                select * from table_name where user = 'x' limit 100000,10;改为

                select * from table_name where  id in (select id from table_name where user = 'x'  limit 100000,10);

                数据量大还可用join

     （11）in和exists的比较。

                in适合内表比外表查询结果集小的情况，反之使用exists。内外表数据量接近时，效率接近。

                not in内外表都不走索引。not exists内表走索引，效率远高于not in。

                in 内表作为驱动表。把内表和外表做hash连接，先查询内表，把内表的结果存到缓存，然后去遍历查询外表。

                exists 外表作为驱动表。先查询外表，然后将主查询放到子查询中做验证。

     （12）group by和order by中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来进行优化。

     （13）不宜使用order by rand(),该操作会严重消耗CPU，使数据库性能呈指数级下降。

     （14）MySQL hint（查询优化器提示）

              1、强制在主库执行/*FORCE_MASTER*/

                   解决主从延迟问题。示例如下：SELECT * /*FORCE_MASTER*/  FROM table_name;

              2、关闭查询缓冲/* ENGINE_NO_CACHE */

                   目的是为了冷热数据分离，防止导入大量数据时污染MySQL的buffer pool。

                   例如每天一次的批量任务查询大量数据，如果不是用此语法，会把buffer pool中的业务数据挤出去，把当前数据缓存起来，缓存中的其它业务数据被挤出导致业务系统性能下降。

                   例： select /* engine_no_cache */ count(*) from t1 

                   类似的还有SQL_NO_CACHE(关闭查询缓冲)，SQL_CACHE(强制查询缓冲)。

              3、强制索引FORCE INDEX/忽略索引IGNORE INDEX

                   select * from t1 force index (idx_xxx) where ...

                   select * from t1 ignore index (idx_xxx) where ...

              4、强制将结果集放进临时表SQL_BUFFER_RESULT

                   当查询的结果集较大时，强制将结果集放进临时表，可快速释放表锁，并且可长时间为客户端提供较大的结果集。

                   select sql_buffer_result * from t1 where...

六.DML语句

     （1）insert语句应显示指定列名，防止在增加或删除字段后程序报错。

     （2）对于需要删除全表数据的操作，应使用truncate而不是delete

              delete耗时较长，大量delete语句可能造成主从延时较大。delete操作会在数据文件中留下碎片，频繁delete会导致表数据文件占用空间不断膨胀。

     （3）对于操作大批量数据的情景，例如delete数据，需加limit。防止执行时间过长，锁表时间过长，主从同步延迟过高等一系列问题。

七.注意事项

     （1）宜将大字段，访问频率低的字段拆分到单独的表中存储。（分离冷热数据，有效利用缓存，防止读入无用冷数据，减少磁盘IO，同时保证热数据常驻内存，提高缓存命中率）

     （2）单表数据超过500w，或者数据量超过2G，需进行分库分表。

     （3）RDS支持防止个别SQL使IOPS过载，通过修改rds_sql_max_iops参数实现。例如在BI数仓库涉及大量数据读写的库有较好优化，使数据库处于平稳良好的运行状态，减少IOPS使用率的报警。