36条军规
核心军规
- 不在数据库做运算,尽可能简单应用mysql,复杂运算移到程序段
举例：
1 | md5(); |
- 控制单表数据量
1 | 1. int型不超过1000w，含char则不超过500w |
- 控制列数量
1 | 表字段数少而精：io高效、全表遍历、表修复快、提高并发、alter table快 |
- 拒绝3B：拒绝大SQL，复杂事务，大批量任务
- 平衡范式与冗余
字段类军规
- 用好数值字段类型
tinyint(1Byte)、smallint(2Byte)、mediumint(3Byte)、int(4Byte)、bigint(8Byte)
1 | 举例：bad case |
举例： 用无符号INT存储IP，而非CHAR(15):数字型VS字符串,更高效、查询更快、占用空间更小1
2
3
4
5
6+ 避免使用NULL字段,特别是索引列
首先，我们要搞清楚“空值” 和 “NULL” 的概念：
1、空值是不占用空间的
2、MySQL中的NULL其实是占用空间的
> 所谓的NULL就是什么都没有，连\0都没有，\0在字符串中是结束符，但是在物理内存是占空间的，等于一个字节，而NULL就是连这一个字节都没有。在数据库里是严格区分的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。
- null字段很难进行查询优化: where子句中使用is null或is not null的语句优化器是不允许使用索引的
- Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位
- Null字段的复合索引无效,索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，结果集中也不会包含这些记录
NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错
1
2
3
4
5
6
7
8
9
10
11+ 少用并拆分TEXT/BLOB，尽量不使用TEXT/BLOB,若必须则尽可能拆分到单独的表中
```
1. TEXT类型处理性能远低于VARCHAR（各自处理过程）
2. 强制生成硬盘临时表
3. 浪费更多空间 VARCHAR(65535) == 64K (UFT8)
```
+ 不在数据库中存图片
## 索引类军规
+ 谨慎合理的添加索引:改善查询，减慢更新- 结合核心sql优先考虑覆盖索引
- 字段区分度要大，综合评估数据密度和数据分布
- 最好不超过字段数20%
- 索引并不是越多越好，能不加的索引尽量不加
1
+ 自增列或者全局id做INNODB主键
主键建立聚簇索引；
- 主键不应该被修改,按自增顺序插入值,二级索引存储主键值
- 字符串不应该做主键
如果不指定主键，innodb会使用唯一且非空值索引代替；
1
2
3
4
5+ 尽量不使用外键,可“到达”其他表，意味着锁表高并发容易死锁，由程序保证约束
+ 不在索引列进行数据运算 和函数运算：会无法使用索引导致全表扫描
## 约定类军规：
+ 隔离线上线下:构建数据库的生态环境,开发无线上库操作权限
- 原则：线上连线上，线下连线下
实时数据用real库，测试用qa库，模拟环境用sim库，开发用dev
1
+ 禁止未经DBA确认的子查询
mysql子查询：
- 大部分情况优化较差，特别是 where中使用 in id的子查询
- 一般可用join改写，mysql对子查询会处理为临时表，所以一般join效率比做子查询高
1
+ 永远不再程序端显示加锁
外部锁对数据库不可控
- 高并发时是灾难
- 极难调试和排查
1
2
3
4
5
6
7
8+ 字符集,统一字符集：utf8;校对规则：utf8_general_ci
+ 注意避免用保留字命名,eg. type
+ 统一命名规范：表名、字段释义、表注释
+ mysql大批量操作最好改变为手动提交
+ 批量操作，避开高峰区，挪至凌晨
## sql类军规
+ sql语句尽可能简单
大sql缺点：
- 可能一条大sql就把整个库hang死
- 一条sql只能在一个cpu运算
- 5000+qps的高并发中，1s大sql意味着服务要挂
做法：
- 拒绝大sql，拆解成多条简单sql
- 用上多cpu
- 简单sql缓存命中率更高
- 减少锁表时间
拆分也要适量
1
+ 保持事务连接短小
事务/连接使用原则：即开即用，用完即关
- 与事务无关的操作放在事务外面，减少锁资源占用
在不破坏一致性前提下，使用多个短事务代替长事务
1
+ 尽可能避免使用SP/TRIG/FUNC
- 尽可能少用存储过程
- 尽量减少使用触发器
- 减少使用MYSQL函数对结果进行处理,由客户端程序负责
1
+ 尽量不要select *，只取需要的列
更少的io
- 可能利用覆盖索引取值
更多消耗cpu、内存、io、网络带宽
1
+ OR改写为IN()
- or效率 o(n)，in效率 o(log n)，当n很大时，or会很慢
- 注意控制in的个数，建议小于200个。 eg： 大客户系统的慢sql
1
+ OR改写为UNION
OR也可以使用到联合索引
- 但mysql对于or很多时候并不会优化为两次索引或者合并索引，所以or最好写做两条sql 做union all
瞎举个例子，看下union写法，其实也走合并索引：
select id from t1 where name = ‘婷宝’ or ucid = ‘131’;
=>
select id from t1 where name = ‘婷宝’
union
select id from t1 where ucid = ‘131’;1
2
3
4
5
6
+ 避免负向查询和%前缀模糊查询:使用不了索引,导致全表扫描
> not, !=, <>, !> , !<, "not exists", "not in", "not like"
+ 慎用count(*)
举例：
count(col) = count(1) = count(0) = count(100) != count(col)1
+ limit高效分页,limit越大，效率越低
- 偏移量越大则越慢
- 当是id时，降偏移量移到 where条件中
eg.
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;1
+ 少用连接join
- 减少硬盘临时表生成，特别是有TEXT/BLOB时
mysql在join中对“order by”， “group by”优化较弱 join中的排序分组几乎都会使用fille sort 和临时表
1
2
3
4+ 若无需对结果去重，用union all，而非union，union有去重开销
+ group by无排序要求时使用自动排序
+ 同数据类型的列值比较
+ 使用load data导数据- 批量数据块导入,load data比insert快约20倍
- 成批装载比单行装载更快，不需要每次刷新缓存
- 无索引时装载比索引装载更快
- insert values，values，values 减少索引刷新
- 尽量不用insert … select, 会延迟、同步出错
1
+ 打散批量更新
大批量更新凌晨操作，避开高峰
凌晨不限制，白天上限默认为100条/秒
1
2
3
4
5
6
7
8
9+ know every sql
# 优化建议
## 1.批量插入代替循环单条插入
## 2.当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，在这种情况下，加上 LIMIT 1 ，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查下一条符合记录的数据。
## 3.在Join表的时候使用相当类型的例，并将其索引
如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。而且，这些被用来Join的字段，应该是相同的类型的。DECIMAL和INT字段Join在一起，MySQL就无法使用它们的索引
- 对于那些STRING类型，还需要有相同的字符集才行。
1
2
3
4
5
6
7
8## 4.千万不要 ORDER BY RAND()
MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是用了Limit 1也无济于事（因为要排序）
## 5.Prepared Statements
> Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。
## 6.垂直分割
> “垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的
eg.大客户系统的客户属性拆解：主属性+附加属性1
2
3## 7.拆分大的 DELETE 或 INSERT 语句
首先，我们的服务器并不会有太多的子进程，线程和数据库链接，因为这是极大的占服务器资源的事情，尤其是内存；其次，DELETE 或 INSERT是会锁表的，表一锁住了，别的操作都进不了；结果，如果表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让WEB服务Crash，还可能会让整台服务器马上挂掉；所以，需要对一个大的处理，进行拆分，
eg. 使用limit + sleep
while (1) {
//每次只做1000条
mysql_query(“DELETE FROM logs WHERE log_date <= ‘2017-11-01’ LIMIT 1000”);
if (mysql_affected_rows() == 0) {
break;
}
// 每次都要休息一会儿
usleep(50000);
}
```
8. 越小的列会越快
对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。
9.小心“永久链接”
“永久链接”的目的是用来减少重新创建MySQL链接的次数,在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数等等。
