postgreSQL单表数据量上千万分页查询缓慢的优化⽅案postgreSQL单表数据量上千万分页查询缓慢的优化⽅案
故事要这样说起，王铁蛋是⼀个初⼊职场的程序猿，每天⼲的活就是实现各种简单的查询业务，但是铁蛋有⼀颗热爱技术的⼼，每天都琢磨着如何写出花式的增删改查操作。没错平凡的铁蛋的有着⼀个伟⼤的梦想，成为⼀名⾼级CRUDER。（⼀不⼩⼼激动了，开⽔倒进了我的花瓶）。
时间就这样⼀天天的流逝，铁蛋感觉不管⾃⼰的crud写的再花骚也不能达到⾼级cruder的级别，于是乎铁蛋⼼⼀横，接下了⼀个艰巨的任务，对单表数据量到百万千万级别的查询页⾯进⾏优化，这是铁蛋⼯作任务上的⼀⼩步，却是铁蛋实现梦想的⼀⼤步。
接任务简单，做任务难呀！ 这是铁蛋第⼀天的感受，接了这个任务之后铁蛋没有⼀点头绪，从哪下⼿呢？铁蛋仔细⼀想既然要优化，那么总得知道 哪⾥需要优化吧？ 可以从哪些⽅⾯优化吧？ 需要知道最
如何分析瓶颈在哪吧？ 不料天降神图，给了铁蛋⼀个指引， 没错就是数据库可以优化的⽅向图。
注：图中效果的渐变其实不太准确， 但是总的来说如果不是SQL写的特别烂的话⼤体上优化这些不同的⽅⾯对性能的影响是以图中的⽰意变化的。
虽然有了神图的指引，但是铁蛋还是不知道应该优化哪个⽅⾯？ 不同⽅⾯的优化⽅式是什么？经过铁蛋的⼀番努⼒查（哈哈，这次不是上天相助了，总要努⼒下的， 不然这⿊幕太明显了），得到了以下信息：sql优化的几种方式
从成本⽅⾯考虑，⼟豪的优化⽅式向来简单粗暴，硬件不⾏就换硬件嘛, 不差钱 但是铁蛋不⾏呀，草根⼀枚，要钱没钱， 要⼈没⼈，只能选择便宜的来下⼿了。柿⼦嘛还是得挑软的捏，于是乎，铁蛋踌躇满志的产品商量改需求。
咳咳 怎么说呢？ 铁蛋是为了降低成本，为公司控本降费，初⼼是好的，但是呀这个做法嗯嗯啊啊。。。， ⼤家以此为戒哦！！！
既然改需求不⾏，那就只能往下⾛了， 先来⼀波SQL优化看看，要优化SQL总得知道SQL慢在哪⾥了吧？
咋办咋办！ 不知道哪⾥慢咋办？
还能咋办，看SQL的执⾏计划呗！
不会看咋办？
啥！ 不会看， 不会看学啊！
好吧，当我没问
怎么看执⾏计划呢，⾸先你得会⼀个SQL的命令，叫EXPLAIN, 此命令⽤于查看SQL的执⾏计划。得此命令，铁蛋如获⾄宝， 拿起来就是⼀顿操作，看到命令输出的结果后，铁蛋傻眼了，这什么⿁？ 这怎么看？
怎么看 ⽤眼睛看呗，还能怎么看。
总的来说sql的执⾏计划是⼀个树形层次结构， ⼀般来说阅读上遵从层级越深越优先， 同⼀层级由上到下的原则。
来跟着铁蛋⽼师读： 层级越深越优先， 同⼀层级上到下。
顺序知道了，得知道⾥⾯的意思了吧， 是的没错， 但是这个⾥⾯⽐较具体的⼀些细节这⾥就不再展开了，只介绍⽐较常关注的⼏个关键字：
重点来了，重点来了，睡觉的玩⼿机的停⼀停。王⽼师要开车了， 啊呸， 开课了。
第⼀⾏的括号中从左到右依次代表的是：
（估计）启动成本，在开始输出之前花费的时间，例如排序时间。
（估计）总成本， 这⾥有⼀个前提是计划节点会完整运⾏，即所有可⽤⾏都会被检索。实际上⼀些节点的⽗节点不会检索所有可⽤⾏（如LIMIT）。
（估计）输出的总⾏数，同样的是基于节点会完整运⾏的假设。
（估计）输出⾏的平均宽度（以字节为单位）
注意：
1. cost中描述的是启动成本和总成本，但是到⽬前为⽌我们还不知道这个数字代表的具体含义，因为我们不知道它的单位是什么。（所
以说这⾥cost中的成本是具有相对意义，不具有绝对意义）
2. rows代表的是输出的总⾏数，他不是计划节点处理或扫描的⾏数，⽽是节点发出的⾏数。由于使⽤where⼦句过滤，这个值通常⼩于
扫描的数⽬。理想情况下，顶级的rows近似于实际的查询返回，更新或删除的⾏数
欲知详情，且待铁蛋⽼师的执⾏计划章节详解，本课就不做衍⽣。
上图中的 Index Scan代表索引扫描， Index Cond代表索引命中，后⾯是命中的具体的索引； Filter是过滤条件，跟具体的sql有关， 注意sort， sort中应该是有两⾏，下⾯的图⽰中能够看到， 第⼀⾏代表对那个键进⾏排序， 第⼆⾏是排序⽅法（主要有内存排序和磁盘排序，应该避免磁盘排序）和数据⼤⼩。
explain还有两个⽐较有⽤的参数⼀个是analyze, ⼀个是buffers。 加上第⼀个参数可以让sql真正的执⾏并且预估执⾏时间， 第⼆参数可以查看缓存命中情况。
actual time对应的意义和cost相似，但是不同于cost， actual time具有绝对意义，因为它的单位是ms。loops代表循环的次数。
缓存命中情况主要看Buffers这⼀⾏， hit就是命中情况，buffers的信息有助于确定查询的哪部分是IO密集型的。
Hash节点主要看 Buckes， 哈希桶的数量， Batches：批处理的数量，批处理的数量如果超过1，则还会使⽤磁盘空间，但不会显⽰。Memory Usage代表内存的使⽤峰值。
有了以上信息我们基本上就可以寻医问药， 对症下药了， 该建索引的建索引， 查询语句没有命中索引的调整下sql，联合索引条件过滤包含驱动列，且驱动列在前效率最⾼。
索引优化⼩技巧：
索引尽量建在数据⽐较分散的列上， 不要在变化很⼩的字段上加索引，⽐如性别之类的。
原因就是：
索引本质上是⼀种空间换时间的操作，通过B Tree这种数据结构减少io的操作次数以此来提升速度。如果在变化很⼩的字段上建⽴索引，那么可能单个叶⼦节点上的数据量也是庞⼤的，反⽽增加了io的次数（如果查询字段有包含⾮索引列，索引命中之后还需要回表）
到了这⾥就开始我们题⽬中的正⽂了， 分页查询性能优化
怎么优化呢？ 经过上述⼀系列的索引和sql优化之后，铁蛋⽼师发现虽然sql的执⾏速度⽐以前快了，但是在单表⼀千万的量级下，这个查询的速度还是有点龟速呀。
仔细看了上图中的执⾏计划发现有三个个地⽅有嫌疑，⼀个是Hash节点， ⼀个是Sort， 还有⼀个是Buffers。
在Hash节点中Batches批处理的数量超过了1， 这说明⽤到了外存， 原来是内存不够了呀！
Sort节点中，排序⽅法是归并， ⽽且是磁盘排序， 原来也是内存不够了。
Buffers 节点中，同⼀个sql执⾏两次每次都有新的io，说明缓存空间也不够，最终这三个现象都指向了内存。
铁蛋打开pg的配置⽂件⼀看， 我靠，穷⿁呀，才分配了512MB的共享缓存总空间， 进程单独分配了4M空间⽤于hash，排序等操作，⽤于维护的分配了512MB。
这哪⾏，再穷不能穷内存呀！ 内从都没有怎么快，怎么快！
铁蛋⼀看，服务器有64GB的内存，恨不得都分过去，还好旁边的⼆狗阻⽌了他。
⼆狗说不是这么玩的， 共享缓存区的内存⼀般分配是内存的1/4，不超过总内存的1/2。 线程内存就看着给了，预计下峰值连接数和均值连接数，做⼀个权衡，适当提⾼。
于是铁蛋将共享缓存区的内存分配为20GB， 单个线程⽤于hash和排序的分配了200MB。 重启数据库， 跑了下执⾏计划。 sql⾥⾯从以前的⼀分钟，四五⼗秒变成了三四秒左右。
仔细看了下执⾏计划， sort中的磁盘排序变成了内存排序，排序⽅法从归并变成了快排。 Hash节点中批处理的数量也变成了1， Buffers 中缓存全部命中。
到了这⾥优化看似就完成了，但是还有些不太圆满。 哪⾥不圆满呢？ 明明sql的分页查询语句很快，为什么页⾯上的分页查询还是要四五秒呢？
铁蛋⼀拍脑袋，怎么把这个给忘了， 分页查询页⾯有个总数统计， 总数统计的sql也需要占时间的呀？ 怎么办？
有办法， 不要慌？ 我们的原则就是两条腿⾛路，两个⽅针政策。
1. 优化全表扫描的速度 （为什么要优化全表扫描的速度，因为统计总数的时候⼤多数情况下是不能避免全表扫描的）
2. 分页查询和统计的sql并⾏执⾏
怎么实⾏？
优化全表扫描的速度还得从服务器下⼿， 全表扫描慢是因为服务器的IO慢，铁蛋恨不得把这个82年的机械硬盘换成SSD，但是⼈微⾔轻，只能从其他⽅⾯下⼿： 调⼤IO预读的⼤⼩
#查看当前预读⼤⼩
blockdev --getra /dev/vda
#设置预读⼤⼩ , 4096的单位是扇区，即512bytes
blockdev --setra 4096 /dev/vda
注意：上⾯的命令在服务器重启之后失效，所以想永久⽣效需要将此命令放到 /etc/rc.local 开机⾃启动脚本中。
sql并⾏化的实现也⽐较容易，在⼀开始就向线程池提交⼀个统计sql’的任务， 等到分页查询的数据处理完成最后要返回给前端之前线程池要总数就⾏了，如果没有执⾏完，会阻塞等待执⾏完，所以响应时间就可以控制在sql执⾏时间最长的那段时间之内了。
⾄此优化任务算是完成个七七⼋⼋了，但是铁蛋突然⼿⼀抖点了最后⼀页，哎发现怎么最后⼀页查询的速度要⽐第⼀页慢上⼀些，怎么回事？
因为如果sql涉及到针对某个字段的排序，那么往后翻页的时候如果采⽤的是limit offset 的⽅式会变得很慢，因为数据库需要先把前⾯的数据都读出来然后扔掉前⾯不需要的。这个时候⼀般情况下没有太多sql上的技巧可以优化了，只有在某些个特殊情况下可以采⽤⼀些⼩技巧。
⽅法是锚点定位法或者叫点位过滤，差不多就这个叫法，知道意思就⾏。
这个定位是怎么做的呢，如果当你的查询不带过滤条件， （⽐如你的个⼈订单记录，只是⽐较下，不要细纠）。且你的数据中有⼀个递增且连续的字段（注意⼀定要连续），那么就可以通过翻页前的最后⼀条数据的id来定位下⼀页的位置， 或者直接根据分页⼤⼩和要跳转的页码直接定位到你要翻页的地⽅，⼀般情况下这个字段是主键。
⽰例：
select id,time from a order by time limit10offset1000;
//锚点定位就是
select id,time from a where id in(select id from a where id >1000limit10)
order by time
//或者直接
select id,time from a where id >1000order by time limit10

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。