saowu's Blog

MySQL数据库开发的三十六条军规

MySQL数据库开发的三十六条军规
2021-05-02 · 12 min read
MySQL

转自 石展@赶集

一、核心军规

1、尽量不在数据库做运算

别让脚趾头想事情，那是脑瓜子的职责。

  • 让数据库多做她擅长的事
    • ✅尽量不在数据库做运算
    • ✅复杂运算跑到程序端CPU
    • ✅尽可能简单应用MySQL
  • 举例: md5() / Order by Rand()

2、控制单表数据量

  • 一年内的单表数据量预估
    • 纯INT不超1000W
    • 含CHAR不超500W
  • 合理分表不超载
    • USERID
    • DATE
    • AREA
  • 建议单库不超过300～400个表

3、保持表身段苗条

  • 表字段数少而精
    • ✅IO高效
    • ✅全表遍历
    • ✅表修复快
    • ✅提高并发
    • ✅alter table快
  • 单表1G体积500W行评估
    • 顺序读1G文件需N秒
    • 单行不超过200Byte
    • 单表不超50个纯INT字段
    • 单表不超20个CHAR字段
  • 单表字段数上限控制在20~50个

4、平衡范式与冗余

  • ✅效率优先、提升性能
  • 没有绝对的对与错
  • 适当时牺牲范式、加入冗余
  • 但会增加代码复杂度

5、拒绝3B

  • 数据库并发像城市交通
    • 非线性增长
  • ☠️拒绝3B
    • ☝️大SQL（Big SQL）
    • ☝️大事务（Big Transaction）
    • ☝️大批量（Big Batch）

二、字段类军规

1、用好数值字段类型

  • 👉自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；
  • 👉当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
  • 👉不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
  • 👉账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑。

2、将字符转化为数字

  • 数字型VS字符串型索引
    • 更高效
    • 查询更快
    • 占用空间更小
  • IPv4存储也可以采用整型存储
    • 后续查询使用INET_ATON()、INET_NTOA()函数转换使用
    • 可以减少聚簇索引存储空间，如针对此字段有二级索引也会同步减少存储空间

3、优先使用ENUM或SET

  • ENUM占用1字节，转为数值运算
  • SET视节点定，最多占用8字节
  • 比较时需要加'单引号(即使是数值)
  • 举例
    • 'sex' enum('F','M') COMMENT '性别'
    • 'c1' enum('0','1','2','3') COMMENT '职介审核'

4、避免使用NULL字段

  • 避免使用NULL字段
    • 很难进行查询优化
    • NULL列加索引，需要额外空间
    • 含NULL复合索引无效
  • 举例
    • 'a' char(32) DEFAULT NULL
    • 'b' int(10) NOT NULL
    • 'c' int(10) NOT NULL DEFAULT 0

5、少用并拆分TEXT/BLOB

  • TEXT类型处理性能远低于VARCHAR
    • 强制生成硬盘临时表
    • 浪费更多空间
    • VARCHAR(65535)==>64K (注意UTF-8)
  • 尽量不用TEXT/BLOB数据类型
  • 若必须使用则拆分到单独的表
  • 举例:
CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    data TEXT NOT NULL,
    PRIMARY KEY(id)‏
) ENGINE=InnoDB;

6、不在数据库里存图片

  • 对数据库的读/写的速度永远都赶不上文件系统处理的速度
  • 数据库备份变的巨大，越来越耗时间，最后对文件的访问需要穿越你的应用层和数据库层
  • 一般来说数据库都是存储一个URL，然后再通过URL来调用图片

三、索引类军规

1、谨慎合理添加索引

  • 什么时候建索引？
    • 改善查询
    • 减慢更新
    • 索引不是越多越好
  • 能不加的索引尽量不加
    • 综合评估数据密度和数据分布
    • 最好不超过字段数20%
  • 结合核心SQL优先考虑覆盖索引
  • 举例
    • 不要给“性别”列创建索引

2、字符字段必须建前缀索引

  • 区分度
    • 单字母区分度： 26
    • 4字母区分度： 26*26*26*26=456,976
    • 5字母区分度： 26*26*26*26*26=11,881,376
    • 6字母区分度： 26*26*26*26*26*26=308,915,776
  • 举例
CREATE TABLE test (
    blob_col  varchar(100) DEFAULT NULL COMMENT '小区拼音', 
    INDEX(blob_col(10))
)ENGINE=InnoDB;

3、不在索引列做运算

  • 不在索引列进行数学运算或凼数运算
    • 无法使用索引
    • 导致全表扫描
  • 举例：
    • DELETE FROM t1 where TIMESTAMPDIFF(DAY, modify_time,CURRENT_TIMESTAMP()) > 31
    • DELETE FROM t1 WHERE modify_time < '31天前的时间'

4、自增列或全局ID做INNODB主键

  • 对主键建立聚簇索引
  • 二级索引存储主键值
  • 主键不应更新修改
  • 按自增顺序插入值
  • 忌用字符串做主键
  • 聚簇索引分裂
  • 推荐用独立亍业务的AUTO_INCREMENT列或全局ID生成器做代理主键
  • 若不指定主键，InnoDB会用唯一且非空值索引代替

5、尽量不用外键

  • 线上OLTP系统（线下系统另论）
    • 外键可节省开发量
    • 有额外开销
    • 逐行操作
    • 可‘到达’其它表，意味着锁
    • 高并发时容易死锁
  • 由程序保证约束

四、SQL类军规

1、SQL语句尽可能简单

可能一条大SQL就把整个数据库堵死

  • 拒绝大SQL，拆解成多条简单SQL
    • 简单SQL缓存命中率更高
    • 减少锁表时间，特别是MyISAM
    • 用上多CPU

2、保持事务(DB连接)短小

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

3、尽可能避免使用SP/TRIG/FUNC

  • 线上OLTP系统（线下库另论）
    • 尽可能少用存储过程
    • 尽可能少用触发器
    • 减用使用MySQL凼数对结果进行处理
  • 由客户端程序负责

4、尽量不用SELECT *

  • 用SELECT * 时
    • 更多消耗CPU、内存、IO、网络带宽
  • 尽量不用SELECT * ，叧取需要数据列
    • 更安全的设计：减少表变化带来的影响
    • 为使用covering index提供可能性
    • Select/JOIN减少硬盘临时表生成，特别是有TEXT/BLOB时

5、改写OR语句

  • 同一字段，将or改写为in()
    • OR效率：O(n)
    • IN 效率：O(Log n)
    • 当n很大时，OR会慢很多
    • 注意控制IN的个数，建议n小于200
    • 举例
      • Select * from opp WHERE phone='12347856' or phone=‘42242233'
      • Select * from opp WHERE phone in ('12347856' ,'42242233')
  • 不同字段，将or改为union
    • 减少对不同字段进行or查询
    • Merge index往往很弱智
    • 如果有足够信心：set global optimizer_switch='index_merge=off';
    • 举例
      • Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000';
      • Select * from opp WHERE phone='010-88886666' union Select * from opp WHERE cellPhone='13800138000';

6、避免负向查询和% 前缀模糊查询

  • 避免负向查询
    • NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等
  • 避免% 前缀模糊查询
    • B+ Tree，使用不了索引,导致全表扫描
    • 举例
MySQL> select * from post WHERE title like '北京%' ;
298 rows in set (0.01sec)  
MySQL> select * from post WHERE title like '%北京%' ;
572 rows in set (3.27sec)

7、减少COUNT(*)

  • COUNT(*)的几个例子
    • COUNT(*)=COUNT(1)
    • COUNT(0)=COUNT(1)
    • COUNT(1)=COUNT(100)
    • COUNT(*)!=COUNT(col)
  • COUNT(*)的资源开销大，尽量不用少用
  • 计数统计
    • 实时统计：用memcache，双向更新，凌晨跑基准
    • 非实时统计：尽量用单独统计表，定期重算

8、LIMIT的高效分页

  • LIMIT原理
    • Select * from table limit 10000,10;
    • 偏移量越大则越慢
  • 推荐分页
    • Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条)
    • select * from table WHERE id>=23434 limit 11;
  • 分页方式二
    • Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;
  • 分页方式三
    • SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;
  • 分页方式四
    • 程序取ID：select id from table limit 10000,10;
    • Select * from table WHERE id in (123,456...) ;
  • 可能需按场景分析并重组索引
  • 示例
MySQL>  select sql_no_cache * from post limit 10,10;
10 row in set (0.01 sec)
MySQL>  select sql_no_cache * from post limit 20000,10; 
10 row in set (0.13sec)
MySQL>  select sql_no_cache * from post limit 80000,10;
10 rows in set (0.58sec)
MySQL>  select sql_no_cache id from post limit 80000,10;
10 rows in set (0.02sec)
MySQL>  select sql_no_cache * from post WHERE id>=323423 limit 10;
10 rows in set (0.01sec)
MySQL> select * from post WHERE id >= ( select sql_no_cacheid from post limit 80000,1 ) limit 10 ; 
10 rows in set (0.02 sec)

9、用UNION ALL 而非UNION

  • 若无需对结果进行去重，则用UNION ALL,UNION有去重开销。

10、分解联接保证高并发

  • 高并发DB不建议进行两个表以上的JOIN
  • 适当分解联接保证高并发
    • 可缓存大量早期数据
    • 使用了多个MyISAM表
    • 对大表的小ID IN()
    • 联接引用同一个表多次
  • 举例
    • MySQL> Select * from tag JOIN tag_poston tag_post.tag_id=tag.idJOIN post on tag_post.post_id=post.id WHERE tag.tag=‘二手玩具’;
    • MySQL> Select * from tag WHERE tag=‘二手玩具’;
    • MySQL> Select * from tag_post WHERE tag_id=1321;
    • MySQL> Select * from post WHERE post.id in (123,456,314,141);

11、GROUP BY 去除排序

  • GROUP BY实现
    • 分组
    • 自动排序
  • 无需排序：Order by NULL
  • 特定排序：Group by DESC/ASC
  • 举例
MySQL> select phone, count(*) from post group by phone limit 1;
1 row in set (2.19 sec)
MySQL> select phone, count(*) from post group by phone order by null limit 1;
1 row in set (2.02 sec)

12、同数据类型的列值比较

  • 原则：数字对数字，字符对字符
  • 数值列与字符类型比较
    • 同时转换为双精度,进行比对
  • 字符列与数值类型比较
    • 字符列整列转数值
    • 不会使用索引查询
  • 举例
#字段：`remark` varchar(50) NOT NULL COMMENT '备注,默认为空',
MySQL>SELECT `id`, `gift_code` FROM gift WHERE `deal_id` = 640 AND remark=115127;
1 row in set (0.14sec)
MySQL>SELECT `id`, `gift_code` FROM pool_giftWHERE `deal_id` = 640 AND remark='115127';
1 row in set (0.005 sec)

13、Load data导数据

  • 批量数据快导入：
    • 成批装载比单行装载更快，不需要每次刷新缓存
    • 无索引时装载比索引装载更快
    • Insert values, values, values 减少索引刷新
    • Load data比insert快约20倍
  • 尽量不用INSERT ... SELECT
    • 延迟
    • 同步出错

14、打散大批量更新

  • 大批量更新凌晨操作，避开高峰
  • 凌晨不限制
  • 白天上限默认为100条/秒（特殊再议）
  • 举例
update post set tag=1 WHERE  id in (1,2,3);
sleep 0.01;
update post set tag=1 WHERE  id in (4,5,6);
sleep 0.01;

15、Know Every SQL！

五、约定类军规

1、隔离线上线下

  • 构建数据库的生态环境
    • 开发人员无线上库操作权限
  • 👊原则：线上连线上，线下连线下
    • 线上环境用prod库
    • 预发环境用pre库
    • 测试环境用`test``库
    • 开发环境用dev库

2、禁止未经DBA确认的子查询上线

  • MySQL子查询
    • 大部分情况优化较差
    • 特别WHERE中使用IN id的子查询
    • 一般可用JOIN改写
  • 示例
    • select * from table1 where id in (select id from table2);
    • insert into table1 (select * from table2);

3、永远不在程序端显式加锁

  • 永远不在程序端对数据库显式加锁
    • 外部锁对数据库不可控
    • 高并发时是灾难
    • 极难调试和排查
  • 并发扣款等一致性问题
    • 采用事务
    • 相对值修改
    • Commit前二次较验冲突

4、统一字符集为UTF8

  • 统一字符集：UTF8
  • 校对规则：utf8_general_ci

5、统一命名规范

  • 库表等名称统一用小写
    • Linux VS Windows
    • MySQL库表名大小写敏感
    • 字段名的大小写不敏感
  • 索引命名默认为idx_字段名
  • 库名用缩写，尽量在2~7个字母
    • DataSharing ==> ds
  • 注意避免用保留字命名
Copyright © 2020 - 2024 saowu. All Right Reserved
Powered by Gridea