写在前边

两年前的今日(大年初三)，我发布了第一篇 MySQL 相关的文档，开端有了第一个粉丝，得到了在当时看来沉甸甸的流量 MySQL索引原理，规划准则

一年后，很巧的是在我的实习作业期间，我将教科书的内容，真正落地到企业傍边，启动了 MySQL 慢查询管理，冥冥之中一切似有组织……

如今，我打算对这两年的MySQL做个了断，围绕 MySQL 慢查询管理作业打开，从几方面打开：

  • 战略规划：三阶段防控管理，事前召回，事间断损，过后管理
  • 典型事例：含糊查找，推迟相关，减少 IO & 回表次数
  • 增益 buff：chatgpt 优化主张东西

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

战略规划：三阶段优化管理

首先是战略上，咱们分成了三阶段来推进 MySQL 管理，渗透到各个环节，各个角色傍边，包括但不限于 RD、QA、SRE、DBA…

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

能够明显的看到，每个环节的要害词是不尽相同的，准入->管理->止损，咱们是期望前置能发现问题的，而不是堕入「先污染后管理」的怪圈

事前召回：巡检智能检测，召回露出风险

分成了两部分来做，增量检测+存量巡检，抓大放小，先做足覆盖，后续调优巡检阈值 & 支撑白名单

  • 增量检测：经过启发式规则 & 本钱分析引擎，智能检测新增 SQL 功能，并从真实事例库抽调测试「大户人家」
  • 存量巡检：扫描前史大表，识别潜在的数据量大 + 短少索引的危险，提早露出风险，避免数据表扩张后没有第一时刻树立好索引来应对

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

事间断损：实时会话定位，及时限流止损

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

此部分由于不同公司组件，基架不同，便不过多赘述，大差不大的准则：兜底>降级>熔断>限流，一般能够选用异构方法，形成互备容灾

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

过后管理：本钱价值模型调优，索引兼并本钱误判

问题布景：MySQL explain 有误，经过本钱价值模型调优，处理「索引兼并本钱误判」的问题

具体的索引兼并 SQL 这儿我只贴出要害的部分，其他 where 条件没有贴出来，耗时达到了 2.5s，时常触发超时 kill

相关 SQL：

SELECT
  count(*)
FROM
  `xxx`
WHERE
    uid = ？ -- 取值特别多，选择性很高
  AND 
    status = 0; -- 只有2个取值，选择性很低

初步分析：索引兼并的执行进程

MySQL 选择的 explain 战略是：uid+status 的索引兼并战略

所以咱们顺着索引兼并的思路和执行进程，挖掘相应的瓶颈所在

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

那问题其实挺明朗了，短期的处理方法，直接在 SQL 层面疏忽 status 索引，让 MySQL 走 uid 单列索引即可，耗时降低到了 200-400 ms

但从中长期来看，不免这个数据库还有其他 SQL 也有类似问题，考虑到根治，咱们还需求在 MySQL 层面，分析为什么本钱优化器会核算犯错？

恶补常识：本钱价值模型的核算方法

一句话总结：经过采样核算分析，得到核算信息/索引基数，然后推断出大致需求扫描的行数，然后核算本钱

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

粗略的本钱核算方法是怎样的

总价值 = I/O 价值 + CPU 价值 + 内存价值 + 长途价值

  1. I/O本钱

    1. 当咱们想查询表中的记载时，需求先把数据或许索引加载到内存中然后再操作。这个从磁盘到内存加载的进程损耗的时刻称之为I/O本钱。

      • MySQL默许规则读取一个页面花费的本钱默许是1.0

  2. CPU本钱

    1. 读取以及检测记载 是否满意对应的查找条件、对成果集进行排序等这些操作损耗的时刻称之为CPU本钱。
      • MySQL默许规则读取以及检测一条记载是否契合查找条件的本钱默许是0.2

如何得到核算信息 & 索引基数

有两种类型的扫描：

  1. 全表扫描：查看表数据行数
SHOW TABLE STATUS LIKE 'xxx';
  1. 索引扫描：查询索引基数
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = '数据库名' AND table_name = '表名';

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

咱们重点关注下 Cardinality 这一列：索引基数

  • 意义：表明该索引列，有多少种不同的取值，比方这儿粗略核算出 status 有 1 个取值（实际上2个）

  • 核算规则：结合 SEQ_IN_INDEX 来看，两种类型规则不同

    • 单列索引：本列有多少个不同的取值
    • 联合索引：结合上一个列，组合起来有多少个不同的取值

如何经过基数，推断出 SQL 扫描行数

  • 假定这儿 uid 的 基数是 2100，表明 uid 有 2100 种取值，也就是说每一种的取值占比 = 1/2100

  • 而咱们本次查询，只查询 uid = 某个值（只有一个单点区间）

    • 所以行数预算 = 1/2100 * 全表总行数

再次剖析：索引基数的核算方法 & 犯错原因

核算方法：毕竟基数 = 采样部分页面的基数平均值 总页面数

犯错原因：

  • 当索引重复值过多时，部分页面的基数平均值 总页面数，使得毕竟基数偏高(由于额定乘以总页面数放大了成果)

  • 基数高，毕竟导致 MySQL 误以为索引的选择性很高，倾向于选择「 索引兼并」战略，认为能够带来更高的过滤节约回表，然后产生了误判

扩展阅览

由于时刻联系，大家感兴趣的再自行阅览，这儿不过多打开

关于重复值过多的采样核算，MySQL 8.0 引入了 直方图处理，相关的核算：PolarDB 数据库内核月报

采样核算的方法，默许状况下 InnoDB 会对 20 个 叶子节点的信息进行核算，进程如下:（略）

  • 取得 B+Tree 所有 叶子节点的数量，记为 N

  • 随机取得 B+Tree 索引的 20个 叶子节点。核算每个叶子节点的不同记载的条数，即为P1,P2,…,P8

  • 根据采样核算出 Cardinality 的预估值：Cardinality =（ P1+P2+…+P8 ）/20 * N

还有哪些状况会导致本钱低，耗时却更高？

一篇抨击大多数状况下，核算信息犯错导致本钱误判的论文：Is Query Optimization a “Solved” Problem?

  1. 核算信息不精确，导致本钱核算有误，比方这儿索引的重复值过多，导致采样核算出来的值并不精准

  2. 核算本钱时，只知道要读取 多少个页面，没有考虑 页面的连续性， 或许从 随机 IO 优化为 顺序IO

    1. 献身了额定扫描的页面数，但换来了顺序IO的高效，使得读取更多页面，本钱看似更高但执行速度更快

  3. 核算本钱时，无法得知要读取的页面，是否现已在 Buffer Pool 中，或许出现本钱更高的战略现已缓存了，耗时会更低

短期处理：调优本钱价值模型

经过查看索引核算信息，调研本钱价值模型

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = '数据库' AND table_name = '数据表';

发现其间 status 的索引基数核算有误，误差巨大，从 2->797(误差了700多)，导致 MySQL 误以为 status 索引的选择性很高，毕竟评价后运用了「索引兼并战略」

所以分三步处理：

  1. 排查：是否核算信息已过时，好久未更新

  2. 止损：重新 analyze table 核算最新的 核算信息，但核算出来的成果仍然有较大误差

  3. 调优：调大采样的页面数量，避免由于重复值过多导致核算有误

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

小结：

  • 随着表行数增多，索引数量变多，索引核算信息或许核算犯错，然后导致 MySQL 对索引的选择性判断有误，认为本钱更低，但实际耗时会更高

可是呢，这毕竟没有从根本上处理问题，而且额定的采样页面，也会吃 CPU 内存，没有绝对的银弹

长期根治：单列索引兼并管理 + 分库分表 + 冷热别离

  1. 研制侧构建索引 - SQL 映射联系
  • 进步关于树立索引的敬畏心，新增索引时，统一收敛记载 索引处理的是哪几条 SQL
  • 这样有利于后续管理时，更好拆分、兼并索引，不至于畏手畏脚，不敢轻易碰索引，只能经过 ignore 来避开 ta
  1. user_id 分库分表
  • 咱们的事务场景下，大多数状况下是用户侧查询，按 user_id 分表，能够极大减小查询的开支，且分区不容易倾斜
  1. 冷热别离
  • 用户侧并不需求感知那么久远的站内信消息，能够考虑冷存在介质较差的数据库，具体的完成有很多种方法，这儿就不过多赘述了，后边感兴趣能够再聊聊

扩展：其他数据库的处理方法： 位图索引

其实关于这种基数很小的索引列，其他数据库比方 Oracle 供给了位图索引，专门用于加快这类查询

而且当涉及到索引兼并时，它也能够进行核算分析以确定索引是否对过滤条件具有足够的选择性， 毕竟确保运用它的本钱。

这儿就简单介绍下位图索引，具体细节不过多深化，毕竟是题外话

举个小 ， 表结构如下：

名字(Name) 性别(Gender) 婚姻状况(Marital)
Melo 男 已婚
Anthony 女 已婚
Kobe 男 未婚
James 女 离婚
Curry 女 未婚

索引的树立进程

比方性别这种区分度很低的列，针对每一个取值，位图索引形成一个向量

向量的每一位表明该行是否是男，如果是则为1，否为0。 同理，女向量位01011。

RowId 1 2 3 4 5
男 1 0 1 0 0
女 0 1 0 1 1

关于婚姻状况这一列，位图索引生成三个向量，已婚为11000…，未婚为00100…，离婚为00010…。

RowId 1 2 3 4 5
已婚 1 1 0 0 0
未婚 0 0 1 0 1
离婚 0 0 0 1 0

利用索引查询的进程

  1. 当咱们运用查询句子 select * from table where Gender=‘男’ and Marital=“未婚”;
  2. 首先取出男向量10100…，然后取出未婚向量00100…，将两个向量做 and 操作，这时生成新向量 00100…
  3. 能够发现第三位为1，表明该表的第三行数据就是咱们需求查询的成果。

索引的存储结构

Oracle 数据库运用 B 树索引结构来存储每个索引键的 位图。

  • eg： status 是位图索引的键列，则用一棵 B 树存储 status 索引数据，叶块存储单独的位图。

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

特点和束缚

  1. 位图索引适合枚举类型的取值，连续改变的需求分段离散化才干运用位图
  2. 不适兼并发更新环境，或许造成事务堵塞：例如咱们将列值从 蓝色 修改为 绿色，就需求同时确定 蓝色 和 绿色的位图片段，其他针对这两个取值的修改就会被堵塞

典型事例：含糊查找，推迟相关

-- 耗时550-650ms
select * from `table` where (name like '%Melo%')

很常见的索引失效：以 % 最初的含糊查询，索引会失效，故走了全表扫描

优化方法：

-- 耗时120-150ms
SELECT * FROM `table` 
inner join
    (
        SELECT id FROM `table` WHERE name LIKE '%Melo%'
    ) tmp
on a.id = tmp.id;

优化前后对比：

  • 全表扫描 -> 二级索引扫描 + 回表，挑选 name 的时候，降低了扫描的数据项巨细

    • 节约单次扫描的数据项巨细：一次 磁盘 IO 能够加载更多的索引项
    • 节约回表的次数：咱们的场景下，契合条件的 id 一般是少数，在回表之前就大幅过滤了数据

具体进程：

优化前 优化后
执行进程 全表扫描：挑选出 name 契合 like 条件的 二级索引扫描：只需求扫描 name 索引树
磁盘 IO 次数 多 ： 扫描全数据项，由于单个较大导致一次磁盘 IO 能加载的数量较少 少 ： 只需求扫描 name 数据项，单个较小一次磁盘 IO 能加载的数量更多
回表次数 十分多：相当于全回表 十分少：在 name 索引树上找到满意条件的，才会回表

增益 Buff：chatgpt 优化主张东西

介绍： Chat2DB 是一款有开源免费的多数据库客户端东西，和传统的数据库客户端软件 Navicat、DBeaver 相比 Chat2DB 集成了 AIGC 的能力

能够将自然语言转换为 SQL，也能够将 SQL 转换为自然语言，能够给出研制人员 SQL 的优化主张，极大的提高人员的功率，是 AI 年代数据库研制人员的利器，未来即便不懂 SQL 的运营事务也能够运用快速查询事务数据、生成报表能力。

自然语言转换为SQL：

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

SQL 优化：

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

SQL 解说：

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

个性化测试数据构造：

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理

数据仪表盘建立：

「MySQL实践篇」MySQL 三阶段优化管理：事前召回->事间断损->过后管理