“ 终于要对MySQL优化下手了,本文将对分页进行优化说明,希望可以得到一个合适你的方案” 前言 分页这个话题已经是老生常谈了,但是有多少小伙伴一边是既希望优化的自己的系统,另一边在项目上还是保持自己独有的... 展开更多
“终于要对MySQL优化下手了,本文将对分页进行优化说明,希望可以得到一个合适你的方案
”
分页这个话题已经是老生常谈了,但是有多少小伙伴一边是既希望优化的自己的系统,另一边在项目上还是保持自己独有的个性。
有个性
优化这件事是需要自己主动行动起来的,自己搞测试数据,只有在测试的路上才会发现更多你未知的事情。
本文咔咔也会针对分页优化这个话题进行解读。
这个数据库结构就是咔咔目前线上项目的表,只不过咔咔将字段名改了而已,还有将时间字段取消了。
数据库结构如下
`CREATE TABLE
tp_statistics` (
ss_id
int(11) NOT NULL AUTO_INCREMENT,
ss_field1
decimal(11,2) NOT NULL DEFAULT '0.00',
ss_field2
decimal(11,2) NOT NULL DEFAULT '0.00',
ss_field3
decimal(11,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (ss_id
)
) ENGINE=InnoDB AUTO_INCREMENT=3499994 DEFAULT CHARSET=utf8 COLLATE=utf8mb4_general_ci ROW_FORMAT=COMPACT;
``
表结构
根据以上信息可以看到目前表里边的数据有350万记录,接下来就针对这350W条记录进行查询优化。
先来写一个查询的SQL语句,先看一下查询耗费的时间。
根据下图可以看到查询时间基本忽略不计,但是要注意的是limit的偏移量值。
初次查询结果
于是我们要一步一步的加大这个偏移量然后进行测试,先将偏移量改为10000
可以看到查询时间还是非常理想的。
偏移量10000查询
为了节省时间咔咔将这个偏移量的值直接调整到340W。
这个时候就可以看到非常明显的变化了,查询时间猛增到了0.79s。
偏移量340w查询
出现了这样的情况,那肯定就需要进行优化了,拿起键盘就是干。
提到分析SQL语句,必备的知识点就是explain,如果对这个工具不会使用的可以去看看MySQL的基础部分。
根据下图可以看到三条查询语句都进行了表扫描。
explain分析语句
都知道只要有关于分页就必存在排序,那么加一个排序再来看一下查询效率。
排序之后的查询时间
然后在进行对排序的语句进行分析查看。
通过这里看到当使用了排序时数据库扫描的行数就是偏移量加上需要查询的数量。
分许排序语句
此时就可以知道的是,在偏移量非常大的时候,就像上图案例中的limit 3400000,12这样的查询。
此时MySQL就需要查询3400012行数据,然后在返回最后12条数据。
前边查询的340W数据都将被抛弃,这样的执行结果可不是我们想要的。
咔咔之前看到相关文章说是解决这个问题的方案,要么直接限制分页的数量,要么就优化当偏移量非常大的时候的性能。
如果你都把本文看到了这里,那怎么会让你失望,肯定是优化大偏移量的性能问题。
既然提到了优化,无非就那么俩点,加索引,使用其它的方案来代替这个方案。
咔咔提供的这条数据表结构信息,完全可以理解为就是图书馆的借阅记录,字段的什么都不要去关心就可以了。
对于排序来说,在这种场景下是不会给时间加排序的,而是给主键加排序,并且由于添加测试数据的原因将时间字段给取消了。
接下来使用覆盖索引加inner join的方式来进行优化。
`select ss_id,ss_field1,ss_field2,ss_field3 from tp_statistics inner join ( select ss_id from tp_statistics order by ss_id limit 3000000,10) b using (ss_id);
`
优化方案一
从上图可以看到查询时间从0.8s优化到了0.4s,但是这样的效果还是不尽人意。
于是只能更换一下思路再进行优化。
思考片刻
既然优化最大偏移量这条路有点坎坷,能不能从其它方面进行入手。
估计有很多同学已经知道咔咔将要抛出什么话题了。
没错,就是使用where > id 然后使用limit。
先来测试一波结果,在写具体实现方案。
优化方案二
根据上图可以看到这种方式是十分可行的,分页在300W条数据以后的查询时间也基本忽略不计。
那么这种方案要怎么实现呢!
其实这个方案真的很简单,只需要简单的转换一下思路即可。
是时候做出改变了
当客户端第一次获取数据的时候就正常传递offset、limit俩个参数。
首次返回的数据就使用客户端传递过来的offset、limit进行获取。
当第一次的数据返回成功后。
客户端第二次拉取数据时这个时候参数就发生改变了,就不能再是offset、limit了。
此时应该传递的参数就是第一次获取的数据最后一条数据的id。
此时的参数就为last_id、limit。
后台获取到last_id后就可以在sql语句中使用where条件 < last_id
咔咔这里给的情况是数据在倒叙的情况下,如果正序就是大于last_id即可。
接下来咔咔使用一个案例给大家直接明了的说明。
实战案例
如下就是将要实战演示的案例,例如首次使用page、limit获取到了数据。
返回结果的最后一条数据的id就是3499984
第一次获取数据
此时如果在获取第二条记录就不是使用offset、limit了,就是传递last_id和limit了。
如下图
此时就是使用的where条件来进行直接过滤数据,条件就是id小于上次数据的最后一条id即可。
获取第二条数据
时间对比
假设现在要获取最后一条数据
没有优化之前
没有优化之前
优化之后可以明显的看到查询时间的变化
优化之后的查询
关于limit优化简单几句话概述一下。
“坚持学习、坚持写博、坚持分享是咔咔从业以来一直所秉持的信念。希望在偌大互联网中咔咔的文章能带给你一丝丝帮助。我是咔咔,下期见。
”
本文分享自微信公众号 - PHP初学者必看(PHP0022)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。
原网址: 访问
创建于: 2021-01-25 10:06:26
目录: default
标签: 无
未标明原创文章均为采集,版权归作者所有,转载无需和我联系,请注明原出处,南摩阿彌陀佛,知识,不只知道,要得到
java windows火焰图_mob64ca12ec8020的技术博客_51CTO博客 - 在windows下不可行,不知道作者是怎样搞的 监听SpringBoot 服务启动成功事件并打印信息_监听springboot启动完毕-CSDN博客 SpringBoot中就绪探针和存活探针_management.endpoint.health.probes.enabled-CSDN博客 u2u转换板 - 嘉立创EDA开源硬件平台 Spring Boot 项目的轻量级 HTTP 客户端 retrofit 框架,快来试试它!_Java精选-CSDN博客 手把手教你打造一套最牛的知识笔记管理系统! - 知乎 - 想法有重合-理论可参考 安宇雨 闲鱼 机械键盘 客制化 开贴记录 文本 linux 使用find命令查找包含某字符串的文件_beijihukk的博客-CSDN博客_find 查找字符串 ---- mac 也适用 安宇雨 打字音 记录集合 B站 bilibili 自行搭建 开坑 真正的客制化 安宇雨 黑苹果开坑 查找工具包maven pom 引用地 工具网站 Dantelis 介绍的玩轴入坑攻略 --- 关于轴的一些说法 --- 非官方 ---- 心得而已 --- 长期开坑更新 [本人问题][新开坑位]关于自动化测试的工具与平台应用 机械键盘 开团 网站记录 -- 能做一个收集的程序就好了 不过现在没时间 -- 信息大多是在群里发的 - 你要让垃圾佬 都去一个地方看难度也是很大的 精神支柱 [超级前台]sprinbboot maven superdesk-app 记录 [信息有用] [环境准备] [基本完成] [sebp/elk] 给已创建的Docker容器增加新的端口映射 - qq_30599553的博客 - CSDN博客 [正在研究] Elasticsearch, Logstash, Kibana (ELK) Docker image documentation elasticsearch centos 安装记录 及 启动手记 正式服务器 39 elasticsearch 问题合集 不断更新 6.1.1 | 6.5.1 两个版本 博客程序 - 测试 - bug记录 等等问题 laravel的启动过程解析 - lpfuture - 博客园 OAuth2 Server PHP 用 Laravel 搭建带 OAuth2 验证的 RESTful 服务 | Laravel China 社区 - 高品质的 Laravel 和 PHP 开发者社区 利用Laravel 搭建oauth2 API接口 附 Unauthenticated 解决办法 - 煮茶的博客 - SegmentFault 思否 使用 OAuth2-Server-php 搭建 OAuth2 Server - 午时的海 - 博客园 基于PHP构建OAuth 2.0 服务端 认证平台 - Endv - 博客园 Laravel 的 Artisan 命令行工具 Laravel 的文件系统和云存储功能集成 浅谈Chromium中的设计模式--终--Observer模式 浅谈Chromium中的设计模式--二--pre/post和Delegate模式 浅谈Chromium中的设计模式--一--Chromium中模块分层和进程模型 DeepMind 4 Hacking Yourself README.md update 20211011
Laravel China 简书 知乎 博客园 CSDN博客 开源中国 Go Further Ryan是菜鸟 | LNMP技术栈笔记 云栖社区-阿里云 Netflix技术博客 Techie Delight Linkedin技术博客 Dropbox技术博客 Facebook技术博客 淘宝中间件团队 美团技术博客 360技术博客 古巷博客 - 一个专注于分享的不正常博客 软件测试知识传播 - 测试窝 有赞技术团队 阮一峰 语雀 静觅丨崔庆才的个人博客 软件测试从业者综合能力提升 - isTester IBM Java 开发 使用开放 Java 生态系统开发现代应用程序 pengdai 一个强大的博主 HTML5资源教程 | 分享HTML5开发资源和开发教程 蘑菇博客 - 专注于技术分享的博客平台 个人博客-leapMie 流星007 CSDN博客 - 舍其小伙伴 稀土掘金 Go 技术论坛 | Golang / Go 语言中国知识社区
最新评论