date: 2019-02-01 09:42:13
title: tech| 技术分享: mysql 慢查优化实践

技术分享的文案, 部分内容演讲过程中有修改.

技术分享 slide 地址: http://c.daydaygo.top/landslide/TS20190220.html
技术分享文案地址: https://www.jianshu.com/p/5688aa9fe8d3

自我介绍

swoft是基于 swoole 协程的高性能微服务框架, 2019的目标会基于 swoole5 进行全面协程化改造, 补充更多微服务相关的组件.

基础知识

主题的第一部分, 预热与 mysql 查询息息相关的基础知识.
一次查询的一生, 用 生命周期理解法 的方式来理解事物
索引有话说, 查询的快与慢, 就看索引能不能用好, 大部分时候我们不需要关心, 因为 mysql 服务器会帮我们优化. 但它不是万能的.

让人类永远保持理智, 确实是一种奢求. -- 流浪地球

一次查询的一生. 会按照 mysql分层架构/mysql架构总览/查询执行流程 的顺序来讲解. 就好比我们看到一所大房子, 当我们在外面的时候, 我们首先看到的是它大概有多少门和窗; 等到我们推开门, 我就可以去数数它有多少房间; 最后, 也许需要找到一把钥匙, 来打开我们想要进去的房间.

mysql分层架构 - 3层

• mysql协议: 停等协议. 协议是tcp通信中很重要的概念, 以为tcp的数据传输是流式的, 必须要使用协议来定义数据边界. 如果查询大量数据, mysql 客户端和服务器是如何通信的?
• mysql server 层: 连接器 分析器(词法分析 语法分析) 优化器 执行器. 可以猜一猜词法分析干些啥, 语法分析干些啥.
• mysql 存储层: innoDB. 在没有特别注明的情况下, 我们之后的所有讨论, 都在 innoDB 的基础上进行. 至于为什么不讲其他存储引擎, 大家可以下去自行了解. 顺带一提innoDB的一点八卦: 本来innoDB只是第三方实现的存储引擎, 并非官方实现, 后来才被官方集成进来, 并成为默认存储引擎.(像不像宫斗大戏的剧情)

mysql架构总览

经常会出现 DDL/DML 等名词, 有些小伙伴看到不认识的名词通常会产生畏惧心理. 在问题的解决道路上经常会有这样或者那样 陌生 的事物, 再加上这样的心理作用, 很可能以失败告终. 一方面, 虽然牛顿顿爷说过, 「我只是站在巨人的肩膀上」, 但现实是, 匍匐在巨人脚下才像是大众常态. 我推崇的是保持好奇心, 尽管去问, 落下实地来就是: 百度一下, 你就知道.
日志在mysql中是非常重要的部分, 是 mysql 高可用的重要手段. 这里引出几个名词, WAL(write-ahead logging) binlog redolog 感兴趣可以下去了解

再来简单看看查询执行流程, 不用刻意去记这里的细节, 只需要希望重复一下之前讲到的一些内容, 加深印象. 是的, 在尝试使用的演讲技巧.

到这里不知道大家有没有一个疑问, 这么明显的 查询缓存, 为什么不讲? 大家可以脑补一下如果加上缓存, 流程需要做哪些哪些改变, 这样改后, 可能会出现哪些新问题? 如果在业务中实现呢? mysql8.0 中, 把查询缓存给移除了.

希望这一部分的讲解完后, 大家可以让一条执行的语句, 能在脑海中 流动 起来.

索引

为什么要使用索引? 提高数据查询效率. 那么, 是先有查询还是先有索引? 让我们回想一下, 在很久很久以前, 我们需要有一些需求需要查询数据, 结果我们发现它很慢, 我们就想能不能快一点呢? 于是就有了索引. 看起来像 「走的人多了, 便成了路」. 到此, 如果我们再细想一下:

• 有哪些查询
• 索引是不是也是数据? 既然是数据, 怎么存, 存在哪?
• 查询: 等值 范围
• 模型/数据结构: B+树
• 主键索引(聚簇索引 clustered index), 存的整行数据
• 非主键索引(二级索引 secondary index), 存的主键的值. 所以, 如果查询需要不包含索引的列怎么办? 答案是回表, 其实就是再用主键索引去查整行数据, 再查出相应列.
• 索引维护: 页分裂/合并

B+树

为什么要使用B+树呢? 我们可以比较一下我们熟悉的 hash 数据类型.

什么是 hash 呢? 简单理解就是给数据编个号, 然后用编号就可以直接找到数据. hash的效率非常高, O(1)的时间复杂度. 比如我们要在这个房间找一个叫陈志林的人, 大家第一反应会是我. 但是 hash 这种数据类型会遇到一些问题, 比如 hash 碰撞. 当然还有另外一个明显的问题; 范围查询怎么办呢? 这也是一开始会问是先有查询还是先有索引的问题.

大家可以比较一下这 2 张图, 看看b+树有哪些特点, 以及带来的好处. 叶子节点包含所有数据, 并且有序排列.

索引区分度: 怎么衡量一个索引呢? 比如在衡量 web 服务的用户体验方面, 会统计所有请求的延迟, 要求 99.9% 在200ms内返回. 在高可用方面, 也通过几个9来衡量服务的可用性, 比如 99.9%, 全年宕机不能超过8.76h

联合索引: 从左到右依次有序组合成一项新数据 -> 转化成单个索引的问题

但是大家要注意这里涉及到的 名 和 实 的问题, 名和实是有区别的. 区分出这个问题很重要, 可以避免很多问题. 有一个我身上发生的关于名与实的八卦: 大家有没有注意到这里一个是 B+树 一个是 B树. 曾经在面试的时候, 面试官问我mysql的索引是啥数据结构, 我说 B树, 面试官条件反射式的来了一句, 你确定? 很多问题其实都是 语义问题, 当把语义解释清楚了, 可能问题就已经解决了. 而语义的本质, 其实是名与实问题的一部分, 哲学家在2-3世纪就认识到了这个问题.

慢查实例

• 最常见问题: 查询字段没有加索引 -> 添加索引
• 索引最左原则
• 多个可用索引, mysql没有选在最佳索引 -> 需要强制指定索引

为什么mysql没有选择最佳索引呢? 因为查询优化器是基于部分数据进行统计估算的来做出选择的. 这里就涉及到数据统计相关的思维, 有限的数据只能反映有限的信息, 基于有限数据的统计信息, 也有可能是并不准确. 这里涉及到统计思维和因果关系(相关vs因果, 著名的啤酒和尿布)

• 扫全表/扫的数据量太大 -> 分页查询/大表分表/读写分离
• 数据类型错误, 导致没有用到索引 -> 修改sql, 使用正确的数据类型

yii 中查询 mysql

简明开发规范

more

方法论 - 生命周期理解法:

• 这条语句的执行流程
• 代码是静态的, 程序是动态的(多进程/多线程)
• 多个连接(connection/session)
• 多个事务

生命周期理解法特别适合用来理解 算法:

• 算法入门书. 江湖有个传闻, 这几本书难度依次排开: 数据结构->算法->算法导论->计算机程序设计与艺术, 但其实前 3 本都算入门书, 内容也有大量的重复, 并没有那么明显的 坡度.
• 对于数据结构而言, 其上的增删改查都算是算法, 只是大家倾向于把更「难」的操作才当做算法, 可以说, 数据结构是典型的面向对象思维, 数据结构是对象, 算法是其方法
• 对于算法而言, 需要相应的数据结构来配合, 有些不那么明显, 比如 递归/贪婪(贪心)/动态规划等, 有些在数据结构中就似曾相识, 比如 BFS/DFS(图的遍历), 有的甚至共用相同的词汇, 优先级队列/最大堆/最小堆, 可以说, 算法是典型的面对过程思维, 习惯步骤一步骤二这样的形式展现在面前.

高性能msyql: 被誉为 mysql 领域的圣经
设计数据密集型应用: 如果你需要一份数据相关内容/技术的坐标或者地图, 这本书绝对可以排在明显靠前的位置, 如果再考虑到时效性, 我建议你立刻打开来看看
极客时间-mysql实战45讲: 感受最深的地方在于讲师解决问题时详细步骤后面所表现出的解决问题的思路
编程方法学: 教授当堂叫学生上来撕书的场景还历历在目