- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章MySQL单表亿级数据分页怎么优化?由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
。
有人说单表超千万数据就应该分库分表了,这么玩不合理啊。但是对于创新业务来讲,业务系统的设计不可能一上来就预估这么大的容量,成本和工期都不足矣完成系统的开发工作。我觉得对于创新型业务系统的设计,首先满足需求,其次考虑到万一业务井喷发展所要考虑到的临时解决方案,为系统升级预留时间.
谁都希望业务井喷,那么它来了.
具体时间点就不说了,开始做了一个新业务,见了一个表,该表累计数据条不超过100万,提供查询功能。后来业务量持续上涨,mysql 磁盘开始报警,查询超时报警。而且,客户需要实时查询该业务表的数据并下载。头大,临时改存储方案已经来不及了,不能耽误KPI.
先解决眼下问题,先扩充磁盘。停止双机房同步,减少不必要的报警.
但是1000G 估计也扛不了多久,和业务同学讨论后,业务接受的范围T-7范围内的数据实时查询下载。按这个增长量,7天也是过亿的记录条数。但是7天的数据磁盘肯定是够用的,那就要先把历史数据离线存储.
这个也简单,几行代码的事儿。当然这样依靠完善的基建.
容量的问题解决了,那么改对数据分页查询的进行优化。为了说明问题,去掉敏感的业务数据,数据表结构如下:
当数据量少时,我们用下面的分页是没有问题的:
例如:
pagesize :每页显示条数.
pageno:页码 。
那么 m=pagesize; n=(pageno-1)*pagesize. 。
MySQL的limit工作原理就是先读取前面n条记录,然后抛弃前n条,读后面m条想要的,所以n越大,偏移量越大,性能就越差.
修改sql,减少io的消耗 。
其实这样也避免不了扫描前n 条,但是时间已经节约了很多.
上面是每页请求的RT,可见随着页数的增加,RT 逐渐上升.
Qps 逐渐下降.
那么如果数据太多的话,最后一页超时的概率会非常大.
。
先卖个关子,先看看优化后的表现,这个接口的性能明显提升。如图所示:
RT 平均在10ms 左右,因为返回做了数据处理,RT最终在15ms左右 。
qps 也很平稳,应该可以再高一些,取决于客户的调用.
。
全表扫描肯定不现实,这时我想到了LSM, Log Structured Merge Trees.这种数据结构,被用在许多产品的文件结构策略:HBase, Cassandra, LevelDB, SQLite,Kafka 等。是一种非常复杂的复合数据结构,它包含了 WAL(Write Ahead Log)、跳表(SkipList)和一个分层的有序表(SSTable,Sorted String Table).
这里,没有必要实现一个LSM 树,只是参考了其稀疏索引的思想,能够准确定位数据。这样就简单了。步骤如下:
1.根据分析业务,构建一个 字段 a,b的联合索引。因为a,b 是数据的查询条件,且能分离出1/7的数据.
2.因为这个表的数据 都是通过 insert ... on duplicate key update ... 来更新的,【这也是线上死锁分析的那篇文章留下的伏笔】,而且 id 是自增主键,所以,所有的数据都是按照入库时的顺序来的,且后面遇到冲突时修改也是update 的,所以主键id 是不会变的.
在redis 中设计 稀疏索引.
那么这样就能很快定位到每页的起始id,少了大量的扫描操作,同时使用了索引,虽然 ab 联合索引 在ab 值都是一样的时候 区分度不高,但是这样也保证了id的顺序,不用order by。因为主键索引的id 本来就是有序的.
。
在一批数据入库完成后开始稀疏索引的计算.
计算方法:
第一页 :id = 0 。
第二页:id计算方法,
第三页:id计算方法,
.......... 。
依次类推..... 。
然后写入redis ,更新也是同样的道理.
。
有人肯定会说为什么不用覆盖索引呢,这样就不用回表了啊.
答案是不能,
假如我们返回的 字段 是 a,b ,c d,e,f,那么我们建一个 覆盖索引 x。x的B+树如下:
那如果这个时候 我改了id=5 的值a=4 改为a =1 。
那现在id 就是不是顺序的了!!!!!.
。
数据量不大的话也是可以的,但是这又是何必呢。我们看看order by 的原理.
首先 MySQL 会为每个查询线程分配一块内存,叫做 sort_buffer,这块内存的作用就是用来排序的。这块内存有多大呢?由参数 **sort_buffer_size** 控制,可以通过如下命令来查看.
这样有两个问题:
每次都是按照筛选条件全量排序 。
如果数据量太大内存不够会触发文件排序,比较慢.
所以还是老老实实用了刚刚的方案。效果也还不错,也是仅仅加了几行代码而已 。
这个临时方案也是平稳运行了1年多。(>‿◠) 。
原文链接:https://www.toutiao.com/i6974629421202424353/ 。
最后此篇关于MySQL单表亿级数据分页怎么优化?的文章就讲到这里了,如果你想了解更多关于MySQL单表亿级数据分页怎么优化?的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
我有一台 MySQL 服务器和一台 PostgreSQL 服务器。 需要从多个表中复制或重新插入一组数据 MySQL 流式传输/同步到 PostgreSQL 表。 这种复制可以基于时间(Sync)或事
如果两个表的 id 彼此相等,我尝试从一个表中获取数据。这是我使用的代码: SELECT id_to , email_to , name_to , status_to
我有一个 Excel 工作表。顶行对应于列名称,而连续的行每行代表一个条目。 如何将此 Excel 工作表转换为 SQL 表? 我使用的是 SQL Server 2005。 最佳答案 这取决于您使用哪
我想合并两个 Django 模型并创建一个模型。让我们假设我有第一个表表 A,其中包含一些列和数据。 Table A -------------- col1 col2 col3 col
我有两个表:table1,table2,如下所示 table1: id name 1 tamil 2 english 3 maths 4 science table2: p
关闭。此题需要details or clarity 。目前不接受答案。 想要改进这个问题吗?通过 editing this post 添加详细信息并澄清问题. 已关闭 1 年前。 Improve th
下面两个语句有什么区别? newTable = orginalTable 或 newTable.data(originalTable) 我怀疑 .data() 方法具有性能优势,因为它在标准 AX 中
我有一个表,我没有在其中显式定义主键,它并不是真正需要的功能......但是一位同事建议我添加一个列作为唯一主键以随着数据库的增长提高性能...... 谁能解释一下这是如何提高性能的? 没有使用索引(
如何将表“产品”中的产品记录与其不同表“图像”中的图像相关联? 我正在对产品 ID 使用自动增量。 我觉得不可能进行关联,因为产品 ID 是自动递增的,因此在插入期间不可用! 如何插入新产品,获取产品
我有一个 sql 表,其中包含关键字和出现次数,如下所示(尽管出现次数并不重要): ____________ dog | 3 | ____________ rat | 7 | ____
是否可以使用目标表中的LAST_INSERT_ID更新源表? INSERT INTO `target` SELECT `a`, `b` FROM `source` 目标表有一个自动增量键id,我想将其
我正在重建一个搜索查询,因为它在“我看到的”中变得多余,我想知道什么 (albums_artists, artists) ( ) does in join? is it for boosting pe
以下是我使用 mysqldump 备份数据库的开关: /usr/bin/mysqldump -u **** --password=**** --single-transaction --databas
我试图获取 MySQL 表中的所有行并将它们放入 HTML 表中: Exam ID Status Assigned Examiner
如何查询名为 photos 的表中的所有记录,并知道当前用户使用单个查询将哪些结果照片添加为书签? 这是我的表格: -- -- Table structure for table `photos` -
我的网站都在 InnoDB 表上运行,目前为止运行良好。现在我想知道在我的网站上实时发生了什么,所以我将每个页面浏览量(页面、引荐来源网址、IP、主机名等)存储在 InnoDB 表中。每秒大约有 10
我在想我会为 mysql 准备两个表。一个用于存储登录信息,另一个用于存储送货地址。这是传统方式还是所有内容都存储在一张表中? 对于两个表...有没有办法自动将表 A 的列复制到表 B,以便我可以引用
我不是程序员,我从这个表格中阅读了很多关于如何解决我的问题的内容,但我的搜索效果不好 我有两张 table 表 1:成员 id*| name | surname -------------------
我知道如何在 ASP.NET 中显示真实表,例如 public ActionResult Index() { var s = db.StaffInfoDBSet.ToList(); r
我正在尝试运行以下查询: "insert into visits set source = 'http://google.com' and country = 'en' and ref = '1234
我是一名优秀的程序员,十分优秀!