mysql一些规范

一.规范
1命名规范
①使用小写字母+下划线分割
②名字要和意义相关 字段名最好不要超过32个字符，不能使用保留字（万一使用 在sql语句中要用单引号把他包住）
查询关键字：https://dev.mysql.com/doc/refman/5.7/en/keywords.html
③ 临时表以tmp为前缀 日期后缀 备份表以bak为前缀日期后缀
④储存相同数据的字段名和字段类型必须一致
2基本设计规范
①必须使用innidb存储引擎（5.6以后的默认引擎）
②数据库和表的字符集统一使用utf8（兼容性好，避免由于字符集转码后乱码， 注：mysql中一个汉字utf-8 占用3字节ASCLL码占一个）
③所有的表和字段必须添加注释（使用comment添加注释）
④尽量控制单表数据量的大小，建议控制在500w（这不是mysql的限制，mysql限制取决存储设置和文件系统 ，这样做有利于数据量的迁移 备份 查询效率）可以通过数据归档 分库分表来控制。
⑤谨慎使用mysql分区表（谨慎选择分区键 跨分区查询效率可能更低 建议采用物理分表方式管理大数据）
⑥尽量做到冷热数据分离 减少表的宽度（减少磁盘IO）
经常一起使用的列放在一个表中 避免过多联表查询
⑦禁止在表中建立预留字段（因为如果后续修改字段数据类型 会导致全表锁定 影响数据库的并发性）
⑧ 禁止在数据库中存储图片 文件等二进制数据
禁止在线上做数据库压力测试
禁止从 开发 测试环境直接连接生产环境数据库
3索引设计规范
①限制单张表的数量 最好别超过5个 避免建立冗余和重复索引
②每个Innodb表必须有一个主键（不使用更新频繁的列作为主键 不使用多列主键 不使用UUID MD5 HASH 字符串作为主键）
③建议使用自增ID作为主键
④常见索引列建议
a select update delete语句的where从句中的字段
b.包含在orderby grou by distinct中的字段
c.多表join的关联列
⑤ 如何选择索引列的顺序
a.区分度最高的列放在联合索引的最左侧（区分度：列中不同字段除以总行数的比值，主键区分度为1）
b.如果区分度相差不大 就把字段长度小的放在联合索引的最左侧
c.如果上述2个相差不大 就把使用最频繁的列放在联合索引的左侧
⑥对于频繁查询的优先考虑使用覆盖索引
a.避免innodb表进行索引二次查找
b.可以把随机IO变为顺序IO加快查询效率
⑦尽量避免使用外键（不建议使用外键约束 但是一定要在表与表之间的关联键上建立索引 ）
a.外键可用于保证数据参考完整 建议在业务端实现
b.外键会影响父表和字表的写操作从而降低性能

4字段设计规范
①优先选择符合储存需求的最小数据类型
a.尽量把字符串转为数字类型存储（
如利用mysql函数INET_ATON把字符串类型的ip转为数字再存，）
如对于非负整数 优先使用无符号整形存储
varchar（N）中的N代表的是字符而不是字节
②避免使用text blog 类型
建议把blog或TeX列分离到单独的扩展表中
text或blog类型只能使用前缀索引
③避免使用枚举类型enum
修改enum需要使用alter语句
enum的orderby 效率低 需要额外操作
禁止使用数值作为enum的枚举值
④尽可能把所有列定义为NOT NULL
索引NULL列需要额外的空间保存
列的比较和计算时对于NULL值要做特别处理
⑤ 不要使用字符串储存日期（timestamp或datetime， timestamp比datetime占用空间小 但是只能储存1970-01-01 00：00：01~2038-01-19-03：14：07之间的时间）
原因：无法使用日期函数进行比较，用字符串储存占用更多空间）
⑥财务金额数据必须使用精准浮点 decimal
mysql储存浮点有2 种
非精准浮点 float double
精准浮点 decimal
5.sql开发规范
① 建议使用预编译语句进行数据库操作
原因：预编译语句可以重复使用执行计划， 避免重复编译sql语句，一次解析多次使用 提高处理效率，还可以防止sql注入
②避免数据类型的隐式转换（常见于where从句中 当列类型和参数类型不一致时容易出现隐士转换）
隐式转换会导致索引失效
eg: select name from user_account where id = ‘111’;id 是整型 参数是字符串 会出现隐式
③充分利用表上已经存在的索引
*避免使用双% eg： a like ‘%123%’；
*一个sql只能利用复合索引的一列进行范围查询（如果在A列进行的都是范围查找 就把A放在联合索引的右侧）
*使用left join 或not exsits 来代替not in 操作（not in容易导致索引失效）
④程序连接不同的数据库使用不同的账号 禁止跨库查询
好处：为数据库迁移和分库流出余地 降低业务耦合度 避免权限过大产生安全风险
⑤禁止使用select *
消耗更多cpu 和io
无法使用覆盖索引
⑥禁止使用不含有字段列表的INSERT语句
insert into t value（‘a’”b）
⑦避免使用子查询 尽量把子查询优化为join操作
原因：子查询的结果集无法使用索引 子查询会产生临时表操作 影响效率，消耗过多io cpu
⑧避免关联太多的表（建议不超过5个 mysql最多关联61个）
每join一个表多占用一部分内存
⑨减少同数据库交互的次数
数据库更适合批量操作
合并多个相同的操作到一起
eg：alter table t1 add column c1 int,change column c2 c2 int…
10 使用in代替or
in可以更有效的利用索引
11 禁止使用order by rand（）进行随机排序 对mysql影响太大 会把所有复合条件的数据加载到内存进行排序
建议:在程序中随机获取一个随机值 然后从数据库中获取数据
12 where 从句中禁止对列进行函数转换和计算
（因为会导致无法使用索引）
13.在明显不会有重复值时使用UNION ALL而不是UNION（因为UNION会把所有数据放到临时表中后再进行去重操作 UNION ALL不会再对结果集进行去重）
14,.合理拆分大SQL为多个小SQL
(mysql一个sql只能使用一个cpu进行计算 拆分后可以同时使用多个cpu提高效率)
6.操作行为规范
①超过100w行的批量写操作 要分批次进行（大批量的写操作会造成严重的主从延迟 binlog日志为row格式会产生大量日志 避免产生大量事务操作）
②对大表数据结构修改一定要谨慎 会造成严重的锁表操作 尤其是生产操作 大表使用pt-online-schema-change修改表结构可以避免以上情况
③禁止为程序使用的账号赋予super权限
（当达到最大连接数限制时 还运行1个有super权限的用户连接 所以super权限只能留给DBA处理问题的账号使用）
④对于程序连接数据库账号 遵循权限最小的原则
（程序使用数据库账号只能在一个DB下使用 不准跨库 程序使用的账号原则上不准有DROP权限
）