神绮

一个程序员的碎碎念


  • 首页

  • 归档

记一次 mysql 重构优化

发表于 2019-09-09

前言

比较长一段时间没有写博客了,倒不是因为懒,主要现在工作主要内容CRUD居多,虽然现在接受的项目和 ElasticSearch 相关,但是感觉相关的内容在 ES 的手册都找的到,没必要当个复读机把这些问题都记录下来,所以博客这边还是写一点一般途径找不到的东西。这次分享的内容和一次后台页面查询优化相关,觉得可以讨论下,刚好最近也在和同学讨论 Mysql 相关的东西,于是记录下,权当分享了。这里顺便预告下,最近在看 《七周七并发编程》 与 《七周七理解多语言编程范型》 接下来的博客可能讨论这两本书相关的内容,顺便也推荐下这两本书,把这两本当做增长见识,理解一些语言特性来源很不错。

问题复盘

起因

本次重构优化的起因是后台的一个慢查询,和之前 Redis 那次优化的起因一样,因为之前负责这个项目的哥们写代码时用户量和数据量不是很多,所以整个页面可以很好的运行,但是随着后来数据的增长之前写的慢查询问题便暴露出来了

情况描述

当前后台有一个统计用户信息的页面,用于记录一部分用户相关的信息,因为是一部分用户,所以该表的自增主键 id 没有实际作用,有一个单独的字段用于记录在主表的用户 id ,需要展示的大部分信息也保存在主表内,此外还 Join 了另外两张信息表用于显示,此外还有一次视图查询用到了 group by id 来统计某项数据,也就是统计这项用户数据出现了问题。这几张表用于关联的字段都加上了聚簇索引也就是 innoDB 的索引,索引类型为 B+tree (非hash索引),用于显示用户表的数量在 1M 级别,用于信息显示的主用户表数量在 10M 级别且增长迅速。

重构分析

在接到需求的时候我先用了 Mysql 的 explain 语句分析了下页面使用的查询语句,发现查询关联的几个信息表的时候是部分表查询,但是在子视图的时候是因为使用了 groupBy 语句 使用了全表查询, groupBy 生成的子视图的总数量在 1M 左右,这也是导致页面打开速度过慢的原因。

重构方案

在分析代码之前我考虑过用 Redis 保存 groupBy 的数据,设置一个时间锁,每次过期便重新生成,但是 group by 生成的数据需要排序,且每次查询结果生成的数据量很大,在代码逻辑层进行排序很有可能导致内存溢出等问题,同时我也觉得对1M级的数据进行排序是一件特别蠢的事情(我个人是不认为我写的排序能比写开源轮子的社区强),同时, Redis 在使用的时候还要考虑网络IO等问题,放到 Redis 内进行保存并不会比之前要强多少。
问题还是回到了视图查询身上,从需求方了解到:对groupBy的数据的实时性要求不高,且能访问这个页面的管理员权限要求较高,于是我想到了一个分而治之的方法。

重构

因为对数据实时性要求不到(可以允许1天以内的错误),所以我想到了直接使用定时任务,统计需要 groupBy 的数据,生成一张新表,只包含 id 和需要显示的数据于是就这么做了,在新表未使用索引的时候,页面显示时间已经由最初的 30S+ 变成了 10S 左右,于是给 id 加上了主键,查询时间变为了2s(这里面包含了用于统计条数的 count 语句 如果改为异步,可能在1s左右),算是完成了需求,之后顺便查看了下生成新表的时间在4S左右,于是在页面加了一个按钮生成新表的数据,使数据更精准了一些。

总结

这次优化算是扫出了我对于 Btree 的一些盲点,之前一直不明便btree的叶子节点为什么要保存为链表形式,为什么聚簇索引要保存所有信息,为什么加索引索引的字段最好为连续递增的Int字段,为什么要尽量避免groupBy语句的出现,一切的原因都是因为 Mysql 底层数据保存的形式,b+tree 可以更快的寻址查找磁盘的内容,同时Join可以更快的返回查询结果,为的都是在查询的时候尽量减少时间 (时间复杂度O(log(n)+m) 写了很久的CRUD都把Mysql当做一个黑箱程序来使用,真正了解了它的数据结构和算法才发现它的智慧所在。

一次成功(失败)的Redis重构

发表于 2019-08-20

导言

很久没写博客了,主要觉得工作上值得写博客的地方不多,个人对于写博客的看法是:凡是网上能找到的东西更适合记忆,比如说 shell 命令、某些语言的官方库或者某项开源技术的文档等。博客更适合的是总结和复盘自己的经验,更适合原创用来深化记忆。下面开始复盘这次重构。

复盘

起因

​ 这次重构起因是一个后台管理项目添加功能的需求,先介绍一下项目的大致情况:

  • 表 A :数据量K级,主要保存一些单条数据内容,核心表,日增长大概几十条左右

  • 表 B :数据量 100K 级,和表A是多对一关联,日增长千条左右。

  • 表 C :数据量 100K 级,和表A也是多对一关联,日增长同表B,表C和表B关联字段不同

  • 表 D :整个数据库的大 BOSS ,慢查询的根本原因所在,数据1000K级而且增长迅速,保存的是表 A 和表 B 的关联状态也就是保存的表 A 与表 B 关联状态的各种值。基本上所有条件查询都是从表D而来

分析环境

当我接手这套项目的时候页面显示速度已经到了惊人的 8S 左右,运营使用的时候进行搜索和打开页面要花很长时间,看了下代码,这套项目用的 Yii2 php 框架,按理说应该用 ActiveRecord 进行数据库操作的,但是当时写这套代码的大兄弟图省事直接用的SQL字符串+占位符+拼接的形式, SQL 大多数都是select A join B的形式,因为涉及到了条件查询,在某些查询中嵌套到了惊人的5层join,直接导致了查询时间过长。

解决思路

首先在解决数据库查询的之前,先发现了一个导致显示时间翻倍的祸首: Yii2 的分页,众所周知,数据进行分页显示的时候要统计数据总数来算出页数和总数 来计算 SQL 查询的 offset 和 limit 值,如果要在单次请求的情况下显示分页的话,会在原有查询的基础上进行一次 count 操作,也就是原本的时间*2 ,接需求的时候考虑到紧急程度,先将分页换成前端异步请求显示,算是优化了打开的速度,把页面打开速度控制在了5S左右。
暂时解决了页面打开速度之后,考虑的是如何优化了,页面逻辑其实很简单,就是几个求并集的操作,以核心表 A 的某个字段为主键查询其他几个表,在看到业务代码的时候我就考虑到可以直接用 redis 的 set 来储存数据集,用 set 的 api 来进行求交集操作。使用redis重构了之后发现速度降到了 200ms 左右.
在解决了条件查询之后,遇到的另外一个问题就是排序,众所周知, redis 自带排序的 zset 排序效果非常差,而且无法和set求交集, 所以靠 zset 解决排序是基本不可能的,在跟运营那边负责人沟通了之后了解到表 A 的数据处于 2K 以内,于是写了个根据表 A 的主键从 zset 取值并进行排序的快排算法,算是部分解决了页面显示过慢的问题。把首页打开时间降到了1S以内。

总结

如果要我归纳下这次redis优化的主要总结有啥,其实还是传统型数据库和新型 Nosql 数据库之间的冲突,mysql在涉及到排序、条件查询且关联数据不多 IO不多的情况下都是最优解,但是一旦涉及到求交集的时候,传统数据库的缺点就很明显了:涉及到的 join 操作过多很容易导致慢查询。此外,当初设计表的时候也有很多不足的地方,比如说像D表这种需要经常修改状态的大中型表,居然没有加 created 和 updated 字段,直接导致无法根据时间戳的时间来更新 redis 内的数据,再比如我现在接到的需求,需要根据 A 和 B 两张表的信息变动查询数据,但是在设计数据库的时候居然没有考虑日志记录这回事,直接导致前期所有变动数据无处可查,只能额外加个日志表储存。

go夜读之httpRouter

发表于 2019-06-23 | 更新于 2019-07-28

大晚上的睡不着,看了下B站的go夜读52期,看见在讲httpRouter相关的内容,之前在写gin的时候看到有人说过gin的router就是基于HttpRouter演变过来的,于是看了下视频,发现还是挺受用的 于是写篇博文谈下感想(顺便吐槽下 写这篇文章的时候北京时间凌晨2点40左右 真的是名副其实的”夜读”了)
首先谈下起源,httpRouter算是go很经典的拓展之一了,go原生的http.HandleFunc很难满足restefulAPI的要求,当需要根据POST PATCH UPDATE GET的URI来获取不同的方法以及参数的时候,原生的handleFunc无法满足取路由变量以及针对不同类型的请求返回不同的值的需求。于是httpRouter诞生了。
httpRouter根据GET HEAD OPTIONS POST PUT PATCH DELETE分别建立了基数树 大概解释下基数树的原理:基数树会根据文本来生成树结构,当只有一个文本来作为节点的时候,这个文本即是根节点,再添加节点会根据文本与根节点的文本重复度来生成子节点。这里借用wiki的一张图来说明

根节点为r节点 r节点有om和com两个子节点,在获取到romanne这个单词的时候,会先查找 r这个根节点,然后找到om开头的子节点,然后an->e节点 这样就能找到romanne这个节点对应的内容了。当需要插入新的节点的时候,比如插入一个名为romanee的节点,会先按照之前的步骤,查找到romane节点 然后在romane下建立一个新的e节点 在这里存储romanee的数据。

在go夜读的视频里还提到了一个问题,就是httpRouter只能支持最多256个节点,视频没有说原因,我看了下源码,在httpRouter的源码里,定义radixTree的节点:

1
2
3
4
5
6
7
8
9
10
type node struct {
path string
wildChild bool
nType nodeType
maxParams uint8
indices string
children []*node
handle Handle
priority uint32
}

这里定义了一个maxParams 也就是最大参数长度,每添加一个参数节点,便会自增1,最大长度便是2**8 =256

httpRouter的原理差不多就是这些,在我了解这块的知识点之前一直以为路由的实现原理是通过正则,(在django和flask里似乎是这样的),没意识到go和python以及php是完全不同的Http原理,php·的yii2 thinkPhp phalcon都是使用了controller->action的方式 根据控制器->动作 的形式来查找相应方法,相对于httpRouter来说简单很多 也原始很多了。

Achab Wang

Divde And Conquer
3 日志
1 标签
© 2022 Achab Wang
主题 – NexT.Gemini v7.1.2