关于mysql数据库的n条军规整理

本文的军规的整理思路来自于该出处，对里面部分内容进行重新整理，也有部分来自于笔者自己开发团队在实际生产环境中的总结

军规就是强制性，或许你觉得没这么严重吧？在并发量小，数据量小的应用来说，军规确实是可有可无。。。但如果你是面对一个高并发，有一定规模数据的应用，军规则是整个系统高可用并能最大限度的处理各种高并发的基本保证

一，核心军规

尽量不在数据库做运算
复杂的运算（例如：md5,order by rand()）放在程序端
控制单表数据量 纯INT不超过1000w条，含Char不超过500w条
合理分表（根据userid, date,area）,建议单库不超过300-400个表
保持表身段苗条
表字段数少而精，控制在20到50个，一般纯int字段不超过50个，char字段不超过20个，这样有利于高效io，全表遍历，表修复快，提高并发
平衡范式和冗余
效率优先，提升性能为主，要依据业务需求，仔细权衡数据查询和数据更新关系，指定最合适的范式化程度
拒绝大SQL，复杂事务，大批量任务
禁止存储大文件或者二进制照片
通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机IO操作，文件很大时，IO操作很耗时。

二，字段类军规

用好数值字段，尽量简化字段位数

不建议使用ENUM类型，可使用TINYINT来代替

• 增加新的ENUM值要做DDL操作
• ENUM的内部实际存储就是整数
• ENUM类型的ORDER BY操作效率低，需要额外操作

把字符转化为数字
这样更高效，查询更快，占用空间更小，例如用无符号int 存储 ip,而非char (15)
必须把字段定义为NOT NULL并且提供默认值

• null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
• null这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
• null值需要更多的存储空间，无论是表还是索引中每行中的null的列都需要额外的空间来标识
• 对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

少用并拆封Text/Blob

• 性能远低于varchar,强制生成硬盘临时表，浪费更多空间，必须使用则拆分到单独表
• 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

禁止使用小数存储货币
使用整数吧，小数容易导致钱对不上

必须使用varchar(20)存储手机号

• 涉及到区号或者国家代号，可能出现+-()
• 手机号会去做数学运算么？
• varchar可以支持模糊查询，例如：like“138%”

三，索引类军规

谨慎合理添加索引

• 能不加索引尽量不加，最好不超过字段数20%，核心sql优先考虑覆盖索引
• mysql优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加mysql优化器生成执行计划的时间，同样会降低查询性能。

禁止在更新十分频繁、区分度不高的属性上建立索引

• 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
• “性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
• 建立组合索引，如果不是索引共用，必须把区分度高的字段放在前面

不在索引列做运算
会导致无法命中索引而导致全表扫描
自增列或全局ID做InnoDB主键

• 主键不应该更新修改，忌用字符串做主键
• 主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
• 主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率

尽量不用外键
外键可节省开发量，但会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql性能，特别是高并发下容易造成死锁，非常容易影响数据库性能，因此还是由程序保证约束更好

四，SQL类军规

SQL尽可能简单
一条sql只能在一个cpu运算，在高并发下，1秒是大sql可能把整个数据库堵死，而多个简单sql缓存命中率更高，较少锁表时间，用上多核
保持事务连接短小

• 事务使用原则：即开即用，用完即关
• 与事务无关操作放到事务之外，减少锁资源占用，不破坏一致性前提下，使用多个短事务代替长事务，类似于发帖时的图片上传等待，大量的sleep等待

尽量不用Select*

• 会导致更多的cpu消耗，内存，io和带宽，
• 只获取需要的字段会是更安全的设计：
  1，减少表变化带来的影响
  2，为覆盖索引提供可能
  3，join可降低临时表生成的可能，特别是有text/blob时

改写Or为IN()

• or效率：O(n)
• IN效率 O(Log n）注意控制IN个数，建议小于200

改写Or为Union
减少对不同字段进行or 查询，，merge index 往往很弱智
避免负向查询和%前缀模糊查询
负向查询： NOT ,!= , <>, !<, !>, NOT EXISTS,NOT IN, NOT LIKE等，使用不了索引而导致全表扫描
Count不要使用在可Null的字段上面

• 如果这个“字段”定义允许为 null，count执行时，判断到有可能是 null，会把值取出来再判断一下，不是 null 才累加。
• 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count( * )，所以尽量使用 count( * )。
• 计数统计， 如果需要实时统计，尽量少用count()：用memcache,双向更新，凌晨跑基准，非实时统计：尽量用单独统计表，定期重算

Limit高效分页
SELECT * FROM message where limit 9527,10
因为偏移量越大越慢,所以建议通过下面方式：

• SELECT * FROM message WHERE id >= 9527 limit 10
• SELECT * FROM message WHERE id >= (SELECT id FROM message limit 9527,1) limit 10
• SELECT * FROM message inner join (SELECT id FROM message limit 9527,10) USING(ID)
• 程序先取id ，然后再用in: SELECT id FROM message limit 9527,10 ，然后 SELECT * FROM message WHERE id in（。。。）

使用Union ALL 而不用Union
若无需对结果去重则用union all ,union有去重开销
分解链接，保证高并发
高并发不建议使用两个表以上的join
Group By 去除排序
group by 会分组和自动排序，如果不需要排序可以加上 order by null,这样会更快
同数据类型的列值比较 原则是数字对数字，字符对字符

• 数值列于字符类型对比：同时转换为双精度再对比
• 字符列于数值对比，字符列转数值并且索引失效

Load Data导入数据

• 比Insert快20倍 ,无索引时装载比有索引更快
• insert value，value 多个value减少索引刷新
• 尽量不使用insert..select 会导致延时和同步出错

打散大批量更新，尽量凌晨操作
避开高峰期，白天可以做限制100条每秒 程序上
know every sql (explain， show slow log,)
禁止使用存储过程、视图、触发器、Event
高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧
禁止在WHERE条件的属性上使用函数或者表达式
SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描
正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
禁止大表使用JOIN查询，禁止大表使用子查询
会产生临时表，消耗较多内存与CPU，极大影响数据库性能

五，约定类军规

隔离线上线下
严格的权限控制，，开发无线上操作权限
禁止未经DBA认证的子查询
永远不在程序段显式加锁
外部锁对数据库不可控，高并发是灾难，极难调试和排查
表字符集统一使用UTF8MB4字符集

• UTF8MB4 是比 UTF8 扩展了一些字符,因为MySQL的utf8不是真正的UTF-8，只支持最多三个字节的字符，真正的UTF-8可能会出现四个字节的字符。
• 字符集不统一会对性能造成影响，尤其是连表查询，关联字段字符集不同也是会影响索引的使用

统意命名规范

• 数据表、数据字段必须加入中文注释
• 只允许使用内网域名，而不是ip连接数据库