一个反直觉的sql

2022年01月16日 阅读数:2
这篇文章主要向大家介绍一个反直觉的sql,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

引子sql

《容易引发雪崩的两个处理》里,我提到一个慢查询的问题。本文先从整洁架构的角度讲讲慢查询sql完成的功能以及设计,再介绍对sql进行的实施测试现象以及思考。数据库

 

设计讲解服务器

一见杨过误终身,有多少萌妹子败给了一个痴字。金庸笔下的痴儿怨女数量之多就须要单独申请服务器,用独立存储单元进行存储。mybatis

先说神雕侠侣的杨过,陆无双和程英两姐妹苦等他16年最终等来了他和小龙女撒了一波狗粮而去;郭芙含怨20多年;完颜萍一辈子为杨过梦绕魂牵;郭襄作了尼姑;最惨公孙绿萼,为救他而死,用生命换来的解药杨过转身扔进绝情谷底,公孙绿萼在天上看到此情此景也会气的骂娘吧。架构

再说雪山飞狐的胡斐,出家的袁紫衣;为救他惨死的程灵素……并发

从领域驱动设计的角度讲,上面都是值对象。今天不讲值对象,讲实体。因此先拉回来。异步

功能简单来讲如上图,就是从页面发起查询,查询入口有多个。有个页面是查询角色的仇人,好比杨过的仇人有金轮法王、公孙止、赵志敬……;有个页面是查询角色撩过的妹子,好比杨过撩过的妹子有凌洪波、陆无双、程英……;有些人在几个查询页面都能查到,好比李莫愁既是杨过撩过的妹子,又是杨过的仇人。这里咱们把一种页面叫一个请求方。分布式

请求方发起更新查询请求,好比请求方要查询杨过的全部仇人。请求通过转接系统将请求落库后转发到MQ。MQ异步返回包含杨过、胡斐在内的全部仇人的结果。转接系统根据结果反查请求信息,将结果存储。请求方3s后再发起结果查询,这时就会根据请求条件直接返回杨过的全部仇人。这里值得注意的是一次请求返回的结果有的100多条,有的200多条。就是说杨过、胡斐等这些金庸笔下的人物目前仇人列表总数是100多条;杨过、胡斐等这些金庸笔下的人物目前撩过的妹子列表总数是200多条。编辑还在不断录入数据,过一段时间,数据会变化。性能

功能的设计总体采用整洁架构中的事件溯源的变通方法。对于发起请求,只有增长和查询操做;对于回执结果,由于查询只会查询最新回执。因此一个请求方结果返回后会将全部以前的老数据更新为is_new=N,新插入记录is_new=Y。测试

回执结果记录数据表设计以下:

图片

 

问题描述

这个查询系统受到很多人的喜好,不知不觉间,查询次数过多,事件溯源的设计致使回执结果记录数据表积累了358万条数据。以前没有索引,结果出现一次请求查询20多秒,更新处理直接把数据库拖挂了。

《容易引发雪崩的两个处理》里,我讲解过给is_new加上索引,虽然is_new只有N和Y两个值,整体数据区分度不高,可是对于这种N和Y的值占比是万比一比例,只会查其中少的那部分,倒是比普通索引有更高的效率。

问题来了,请求方的查询条件是where is_new=Y and 角色名=杨过 and 查询来源=仇人。我是该创建三个字段联合索引仍是两个字段联合索引仍是单个索引?

反直觉!通过试验:
1>is_new、角色名、查询来源 三个字段加索引
2>is_new、查询来源两个字段加索引

3>is_new、角色名两个字段加索引
4>is_new单个字段加索引
四种状况sql执行速度在ms级别没有任何差异!几回测试都是6ms返回!而更新时间虽然增长了建索引的时间,反而耗时大大减小!

 

原理分析

查询时间分析

上面所列的索引添加方式都是索引全中,假设is_new的数据共400多条,某查询来源的数据是一二百条,某角色名大概十几条。B+树底层:
1> is_new、角色名、查询来源 三个字段加索引时索引命中十几条,而后经过主键查到数据返回

2> is_new、查询来源  两个字段加索引时索引命中一二百条,而后扫描这一二百条数据,查到须要的十几条数据返回

3>is_new、角色名  两个字段加索引时索引命中二十几条,而后扫描这二十几条数据,查到须要的十几条数据返回

4>is_new单个字段加索引时索引命中400多条,而后扫描这400多条数据,查到须要的十几条数据返回

之因此单个索引和联合索引查询结果区别不大呢,是由于扫描的数据共400多条,按照数据库的处理能力来讲不算什么,这6ms时间主要花在了组装数据和传输数据上。若是查询条件不命中(返回数据条数为0),查询时间几乎为0!

 

更新时间分析

上面提到每次查询都会把上次结果的is_new更新为N,新数据插入时is_new=Y。因此读写比例为1:1。更新性能就是不能不考虑的问题。固然最重要的是更新操做是数据库被拖挂的罪魁祸首。

update 回执结果记录数据表 set is_new=N where is_new=Y and 查询来源=仇人。

我测试了一下:

1> is_new、角色名、查询来源 三个字段加索引时更新时间90ms。

2> is_new、查询来源  两个字段加索引时更新时间60ms。

3>is_new、角色名 两个字段加索引时更新时间60ms。

4>is_new单个字段加索引时更新时间30ms。

这个更新操做,涉及索引重建。层数越多越慢不难理解。可是为何时间是几十毫秒级别呢?没建索引以前查询都要20s。缘由是B+树是树形结构。示意图以下所示,声明:下面的解释只是针对这个问题一个脑补过程,实际上有不少不严谨的地方。好比B+树还有最底层的叶子节点来存放数据。叶子节点之间有双向链表,与主题无关,没画那么细。

更新操做会首先进行一个查询,is_new=Y,而后会在is_new=Y这个范围内将is_new=Y涉及的一二百条数据,再到is_new=N下面本身的位置进行插入。若是是三层,每层的数据都须要先找本身的位置,最慢。这里面没有画的叶子节点是从左到右按id顺序排序的。若是只有一层is_new=Y的整个直接放到is_new=N下面的最后就能够了,最快。这就解释了四种更新方式的更新时间差别。

可是为何加了索引和彻底不加索引之间有有多于千倍的性能差别呢?更新操做的时间也主要是花在查询上。若是彻底不加索引,一二百条数据每条插入前先进行查找,查找要全表扫描,358万条数据,16K为一个内存换页。我就不具体算了,可是要进行不少次内存换页才能查出来。还要乘以数据条数。而加了索引,由于有is_new=Y条件,进行一次内存换页就能够了。由于is_new=Y数据总共就400多条,1个内存页是能够存下的。因此一二百条数据中下一条就不须要内存换页了,查询总共就须要1次内存换页,基本不花什么时间。剩下的就是一条条插入具体位置了。

 

最终结论

经过上面比较,天然是只加单索引is_new最高效。

 

往期推荐

「前任的50种死法」开发踩坑案例--慢就是错

学会用数听说话-分布式锁究竟能够多少并发?

MySQL常见6个考题在实际工做中的运用

mybatis的本质和原理