mysql分页查询不排序

转载

云中谁寄锦书来 2024-11-02 22:53:16

文章标签 mysql分页查询不排序 mysql 大分页查询优化数据主键子节点 文章分类 MySQL 数据库

背景

mysql分页查询不排序_子节点

系统结构如上图。经过排查是因为系统B拉取数据时间太长导致的推送超时。

系统B拉取数据的方法是根据_tiemstamp(数据操作时间)分页查询系统A的接口,即：

1SELECT 字段名2FROM 表名3WHERE _timestamp >= beginTime AND _timestamp <= endTime 4LIMIT n, m;

由于该数据是从其他数据源中导入的，所以_timestamp这个字段值几乎相同，这就导致了在我们的查询范围内存在大约150万的数据。一般遇到这种情况，首先想到的就是是否需要给_timestamp添加索引，这张表上是存在_timestamp索引的。那么为什么还会出现这个问题呢？这就要从分页查询本身说起了。

分页查询的性能瓶颈

B+树简述

首先我们要了解InnoDB存储引擎中的B+数索引。这里我简单总结一下：

mysql分页查询不排序_mysql分页查询不排序_02

上图是一颗B+树，通过观察我们可以发现它的一些特点：

1.每个节点中子节点个个数不能少于m/2个，不能大于m个(B+树是一颗m叉树，图中m=3)

2.根节点的节点个数可以超过m/2个，这是一个例外

上述两点特性是为了保证B+树的查询效率。

节点数超过m越多，在总节点数相同的情况下，树的高度h就越小，此时m叉数就会向链表退化(O(logn)->O(n))。节点数小于m/2越多，在总节点数相同的情况下，树的高度h就越高，此时查询数据，就需要经历更多次的IO

3.m叉树非叶子节点只存储索引，不存储数据

4.通过链表将叶子节点串联在一起，这样可以方便按区间查找。

B+比起二叉查找树，有什么优势？

更矮，这就减少了IO次数。

由于非叶子节点不存储数据，上图查询任何数据，都需要3次IO，查询性能更稳定

由于叶子节点使用了链表连接，范围查询更简便。

分页查询过程

1.首先通过非主键索引查询出所有条件的主键

2.通过主键索引，定位到数据

3.不断重复上述操作

4.根据分页条件，确定返回数据的启始位置以及数据量

5.返回数据

可以看出，初始位置值越大，定位时需要查询的数据就越多，查询效率也会越低

测试集

为了测试优化效果，我准备了150万测试数据(需要跑几分钟)。

1# 建表语句
2CREATE TABLE `test`(
3 `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
4 `name` varchar(512) NOT NULL DEFAULT '无' COMMENT '创建人',
5 `_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
6 PRIMARY KEY (`id`),
7 KEY `ix_timestamp` (`_timestamp`)
8) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';
9
10
11# 通过存储过程导入数据
12drop procedure idata;
13delimiter ;;
14create procedure idata()
15begin
16 declare i int;
17 set i=1;
18 while(i<=1500000)do
19 insert into test values(i, i, now());
20 set i=i+1;
21 end while;
22end;;
23delimiter ;
24
25call idata();

接着，我们看一下使用索引的情况下，分页查询语句的耗时情况。

mysql分页查询不排序_数据_03