mysql 36条军规

发表于 | 分类于

36条军规

核心军规

  • 不在数据库做运算,尽可能简单应用mysql,复杂运算移到程序段

举例：

1
2
md5();
Order by Rand();
  • 控制单表数据量 
1
2
3
1. int型不超过1000w，含char则不超过500w
2. 合理分表不超载，常用分表策略：UserID、DATE、AREA...
3. 限制单库表数量在300以内；
  • 控制列数量
1
表字段数少而精：io高效、全表遍历、表修复快、提高并发、alter table快
  • 拒绝3B：拒绝大SQL，复杂事务，大批量任务
  • 平衡范式与冗余

字段类军规

  • 用好数值字段类型

tinyint(1Byte)、smallint(2Byte)、mediumint(3Byte)、int(4Byte)、bigint(8Byte)

1
2
3
4
5
6
7
8
9
举例：bad case
int(1)/int(11)，填充的是0的个数

举例：时间格式中timestamp、datetime、int的选择
1. int: 4字节、索引快、between、范围广，适合需要进行大量时间范围查询的数据表
2. datetime: 8字节、时区无关、索引最慢，范围较Timestamp广
3. timestamp：4字节、时区相关[UTC存储]，范围到2037年，索引较datetime快，可自动更新，跨国应用不合适
```  
+ 字符转化为数字

举例： 用无符号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关键字。

  1. null字段很难进行查询优化: where子句中使用is null或is not null的语句优化器是不允许使用索引的
  2. Null 列需要更多的存储空间：需要一个额外字节作为判断是否为 NULL 的标志位
  3. Null字段的复合索引无效,索引不会包含有NULL值的列 只要列中包含有NULL值都将不会被包含在索引中，结果集中也不会包含这些记录

  4. 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)
    ``` 
    + 不在数据库中存图片

    ## 索引类军规
    + 谨慎合理的添加索引:改善查询，减慢更新
    1. 结合核心sql优先考虑覆盖索引
    2. 字段区分度要大，综合评估数据密度和数据分布
    3. 最好不超过字段数20%
    4. 索引并不是越多越好，能不加的索引尽量不加 
      1
      + 自增列或者全局id做INNODB主键

  5. 主键建立聚簇索引；

  6. 主键不应该被修改,按自增顺序插入值,二级索引存储主键值
  7. 字符串不应该做主键

  8. 如果不指定主键，innodb会使用唯一且非空值索引代替；

    1
    2
    3
    4
    5
    + 尽量不使用外键,可“到达”其他表，意味着锁表高并发容易死锁，由程序保证约束
    +  不在索引列进行数据运算 和函数运算：会无法使用索引导致全表扫描

    ## 约定类军规：
    + 隔离线上线下:

  9. 构建数据库的生态环境,开发无线上库操作权限

  10. 原则：线上连线上，线下连线下

  11. 实时数据用real库，测试用qa库，模拟环境用sim库，开发用dev 

    1
    + 禁止未经DBA确认的子查询

    mysql子查询：

    1. 大部分情况优化较差，特别是 where中使用 in id的子查询
    2. 一般可用join改写，mysql对子查询会处理为临时表，所以一般join效率比做子查询高
      1
      + 永远不再程序端显示加锁

  12. 外部锁对数据库不可控

  13. 高并发时是灾难
  14. 极难调试和排查 
    1
    2
    3
    4
    5
    6
    7
    8
    + 字符集,统一字符集：utf8;校对规则：utf8_general_ci
    + 注意避免用保留字命名,eg. type
    + 统一命名规范：表名、字段释义、表注释    
    + mysql大批量操作最好改变为手动提交
    + 批量操作，避开高峰区，挪至凌晨
    ## sql类军规

    + sql语句尽可能简单

大sql缺点：

  1. 可能一条大sql就把整个库hang死
  2. 一条sql只能在一个cpu运算
  3. 5000+qps的高并发中，1s大sql意味着服务要挂

做法：

  1. 拒绝大sql，拆解成多条简单sql
  2. 用上多cpu
  3. 简单sql缓存命中率更高
  4. 减少锁表时间

  5. 拆分也要适量 

    1
    + 保持事务连接短小

  6. 事务/连接使用原则：即开即用，用完即关

  7. 与事务无关的操作放在事务外面，减少锁资源占用

  8. 在不破坏一致性前提下，使用多个短事务代替长事务

    1
    + 尽可能避免使用SP/TRIG/FUNC
    1. 尽可能少用存储过程
    2. 尽量减少使用触发器
    3. 减少使用MYSQL函数对结果进行处理,由客户端程序负责
      1
      + 尽量不要select *，只取需要的列

  9. 更少的io

  10. 可能利用覆盖索引取值

  11. 更多消耗cpu、内存、io、网络带宽

    1
    + OR改写为IN()
    1. or效率 o(n)，in效率 o(log n)，当n很大时，or会很慢
    2. 注意控制in的个数，建议小于200个。 eg： 大客户系统的慢sql
      1
      + OR改写为UNION

  12. OR也可以使用到联合索引

  13. 但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越大，效率越低

  1. 偏移量越大则越慢
  2. 当是id时，降偏移量移到 where条件中

eg.
select id from t limit 10000, 10;
=>
select id from t where id > 10000 limit 10;

1
+ 少用连接join

  1. 减少硬盘临时表生成，特别是有TEXT/BLOB时

  2. mysql在join中对“order by”， “group by”优化较弱 join中的排序分组几乎都会使用fille sort 和临时表

    1
    2
    3
    4
    + 若无需对结果去重，用union all，而非union，union有去重开销
    + group by无排序要求时使用自动排序 
    + 同数据类型的列值比较
    + 使用load data导数据
    1. 批量数据块导入,load data比insert快约20倍
    2. 成批装载比单行装载更快，不需要每次刷新缓存
    3. 无索引时装载比索引装载更快
    4. insert values，values，values 减少索引刷新
    5. 尽量不用insert … select, 会延迟、同步出错
      1
      + 打散批量更新

  3. 大批量更新凌晨操作，避开高峰

  4. 凌晨不限制，白天上限默认为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的字段，应该是相同的类型的。

  5. DECIMAL和INT字段Join在一起，MySQL就无法使用它们的索引

  6. 对于那些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链接的次数,在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数等等。