引子
在《容易引起雪崩的两个处理》里，我提到一个慢查询的问题。本文先从整洁架构的角度讲讲慢查询sql完成的功能以及设计，再介绍对sql进行的实施测试现象以及思考。
设计讲解
眼看着杨果被误认为是生活，有多少萌妹输给了一个愚蠢的词。金庸写了大量疯狂的孩子和吐槽，所以他们需要申请单独的服务器，并用独立的存储单元存储。
[En]
At the sight of Yang Guo mistakenly for life, how many cute girls lost to a stupid word. Jin Yong wrote a large number of crazy children and complains, so they need to apply for a separate server and store it with an independent storage unit.
我们先来说说杨果的神雕侠义。陆无双和程莹等了他16年。最后，他和小龙女撒了一波狗粮；郭富恩怨二十多年；万艳萍一生梦想着杨洋；郭翔成了尼姑；最差劲的公孙青花为救他而牺牲。杨果转身，拿着杨果买来的解毒剂，把它扔进了绝望的谷底。当公孙绿帽在天上看到这一幕时，他也会生气。
[En]
Let’s first talk about Yang Guo of the divine carving chivalry. Lu unparalleled and Cheng Ying waited for him for 16 years. Finally, he and Xiao Longnu sprinkled a wave of dog food; Guo Fu held a grudge for more than 20 years; Wan Yanping spent all her life dreaming about Yang; Guo Xiang became a nun; the worst Gongsun Green Calyx died in order to save him. Yang Guo turned and threw it into the bottom of the valley of despair with the antidote Yang Guo bought in exchange for life. Gongsun Green Calyx would also be angry when he saw this in the sky.
让我们来谈谈雪山飞狐的胡飞，出家的袁子怡，以及为救他而牺牲的成灵素。
[En]
Let’s talk about Hu Fei of Flying Fox of Snowy Mountain, Yuan Ziyi who became a monk, and Cheng Lingsu who died to save him.
从域驱动设计的角度来看，以上都是价值对象。今天，我们不再谈论值对象，而是谈论实体。所以先把它拉回来。
[En]
From the perspective of domain-driven design, the above are all value objects. Today, instead of talking about value objects, we will talk about entities. So pull it back first.
功能简单如上图，从页面发起查询，有多个查询条目。有一个页面可以询问人物的敌人，例如，杨果的敌人是金轮法王、公孙志和赵志敬。其中一个页面是为那些角色被调戏的女孩准备的。例如，杨果调情的女孩有凌红波、陆无双、程颖。有些人可以在几个查询页面上找到它们。比如，李墨玉不仅是杨果的妹妹，也是杨果的敌人。在这里，我们将页面称为请求者。
[En]
The function is simply like the figure above, which is to initiate a query from the page, and there are multiple query entries. There is a page for querying the enemies of the characters, for example, Yang Guo’s enemies are King of Golden Wheel Fa, Gong Sun Zhi, and Zhao Zhijing. One page is for girls whose roles have been flirted with. For example, the girls Yang Guo flirted with are Ling Hongbo, Lu unparalleled, and Cheng Ying. Some people can find them on several query pages. For example, Li Moyu is not only Yang Guo’s sister, but also Yang Guo’s enemy. Here we call a page a requester.
请求方发起更新查询请求，比如请求方要查询杨过的所有仇人。请求经过转接系统将请求落库后转发到MQ。MQ异步返回包含杨过、胡斐在内的所有仇人的结果。转接系统根据结果反查请求信息，将结果存储。请求方3s后再发起结果查询，这时就会根据请求条件直接返回杨过的所有仇人。这里值得注意的是一次请求返回的结果有的100多条，有的200多条。就是说杨过、胡斐等这些金庸笔下的人物目前仇人列表总数是100多条；杨过、胡斐等这些金庸笔下的人物目前撩过的妹子列表总数是200多条。编辑还在不断录入数据，过一段时间，数据会变化。
功能的设计整体采用整洁架构中的 事件溯源的变通方法。对于发起请求，只有增加和查询操作；对于回执结果，因为查询只会查询最新回执。所以一个请求方结果返回后会将所有之前的老数据更新为is_new=N，新插入记录is_new=Y。
退货收款结果记录的数据表设计如下：
[En]
The design of the data table of the return receipt result record is as follows:
问题描述
这个查询系统受到很多人的喜爱，不知不觉中，查询次数太多，事件溯源的设计导致收款结果记录数据表中积累了358万条数据。之前没有索引，结果是20多秒的请求查询，更新过程直接拖拽数据库。
[En]
This query system is loved by many people, unwittingly, the number of queries is too many, and the design of event traceability leads to the accumulation of 3.58 million pieces of data in the record data table of receipt results. There was no index before, and the result was a request query for more than 20 seconds, and the update process dragged the database directly.
在《容易引起雪崩的两个处理》里，我讲解过给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最高效。
往期推荐
Original: https://www.cnblogs.com/xiexj/p/15551775.html
Author: 编程一生
Title: 一个反直觉的sql
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/508046/
转载文章受原作者版权保护。转载请注明原作者出处！