MySQL调优之SQL查询深度分页问题

目录
  • 一、问题引入
  • 二、MySQL中的limit用法
  • 三、深度分页优化策略
    • 方法一:用主键id或者唯一索引优化
    • 方法二:利用索引覆盖优化
    • 方法三:基于索引再排序
    • 方法四:基于索引使用prepare
    • 方法五:利用"子查询+索引"快速定位数据
    • 方法六:利用复合索引进行优化

一、问题引入

例如当前存在一张表test_user,然后往这个表里面插入3百万的数据:

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int(9) NOT NULL COMMENT '本地网',
  `region_id` int(9) NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT;

在数据库开发过程中我们经常会使用分页,核心技术是使用用 limit start, count 分页语句进行数据的读取。

我们分别看下从0、10000、100000、500000、1000000、1800000开始分页的执行时长(每页取100条)。

SELECT * FROM test_user LIMIT 0,100;         # 0.031
SELECT * FROM test_user LIMIT 10000,100;     # 0.047
SELECT * FROM test_user LIMIT 100000,100;    # 0.109
SELECT * FROM test_user LIMIT 500000,100;    # 0.219
SELECT * FROM test_user LIMIT 1000000,100;   # 0.547s
SELECT * FROM test_user LIMIT 1800000,100;   # 1.625s

我们已经看出随着起始记录的增加,时间也随着增大。这说明分页语句limit跟起始页码是有很大关系的,那么我们把起始记录改为290w看下:

SELECT * FROM test_user LIMIT 2900000,100; # 3.062s

我们惊讶的发现MySQL在数据量大的情况下分页起点越大,查询速度越慢!

那么为什么会出现上述这种情况呢?

答案: 因为 limit 2900000,100 的语法实际上是mysql扫描到前2900100条数据,之后丢弃前面的3000000行,这个步骤其实是浪费掉的。

从中我们也能总结出以下两件事情:

limit语句的查询时间与起始记录的位置成正比。

mysql的limit语句是很方便,但是对记录很多的表并不适合直接使用。

二、MySQL中的limit用法

limit子句可以被用于强制select语句返回指定的记录数,其语法格式如下:

SELECT * FROM 表名 limit m,n;
SELECT * FROM table LIMIT [offset,] rows;

limit接受一个或两个数字参数,参数必须是一个整数常量,如果给定两个参数:

第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目

2.1 m代表从m+1条记录行开始检索,n代表取出n条数据。(m可设为0) 

SELECT * FROM 表名 limit 6,5;

上述SQL表示从第7条记录行开始算,取出5条数据

2.2 值得注意的是,n可以被设置为-1,当n为-1时,表示从m+1行开始检索,直到取出最后一条数据

SELECT * FROM 表名 limit 6,-1;

上述SQL表示取出第6条记录行以后的所有数据

2.3 若只给出m,则表示从第1条记录行开始算一共取出m条

SELECT * FROM 表名 limit 6;

2.4 以年龄倒序后取出前3行

select * from student order by age desc limit 3;

2.5 跳过前3行后再2取行

select * from student order by age desc limit 3,2;

三、深度分页优化策略

方法一:用主键id或者唯一索引优化

即先找到上次分页的最大id,然后利用id上的索引来查询:

SELECT * FROM test_user WHERE id>1000000 LIMIT 100; # 0.047秒

使用此优化SQL相比于前面的查询速度已经快了11倍。除了主键ID,也可以利用唯一索引快速定位部分数据,避免全表扫描。例如读取第1000到1019行数据(pk是唯一键),则相对应的优化SQL如下:

SELECT * FROM 表名称 WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

原因:索引扫描,速度会很快。

适用场景:如果数据查询出来是按照pk或者id进行排序,并且全部数据没有缺失的话则可以这样优化,否则分页操作会漏数据。

方法二:利用索引覆盖优化

我们都知道,利用了索引查询的语句中如果只包含了那个索引列(也就是索引覆盖),那么这种情况会查询很快。

为什么索引覆盖查询会很快呢?

答案:因为利用索引查找有优化算法,且数据就在查询索引上面,不用再去找相关的数据地址了,这样节省了很多时间。另外Mysql中也有相关的索引缓存,在并发高的时候利用缓存就效果更好了。

在我们的测试表test_user中,id字段是主键,自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何。

这次我们查询第1000001到1000100行的数据(利用覆盖索引,只包含id列):

SELECT id FROM test_user LIMIT 1000000,100; # 0.843秒

从这个结果中发现查询速度比全表扫描速度还要慢(当然在重复执行这条SQL,多次查询之后速度还是变快了很多,几乎省了一半时间,这是由于缓存的原因), 接着使用explain命令来查看该SQL的执行计划,发现该SQL执行采用的普通索引 idx_user_id

EXPLAIN SELECT id FROM test_user LIMIT 1000000,100;

如果我们把普通索引给删除的话,就会发现执行上述SQL其采用的会是主键索引。那如果不删除普通索引的话,针对这种情况,我们要让上述SQL走主键索引的话,则可以使用order by语句:

SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100; # 0.250秒

那么如果我们也要查询所有列,有两种方法,一种是id>=的形式,另一种就是利用join。

第一种写法:

SELECT * FROM test_user WHERE ID >= (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,1) LIMIT 100;

上述SQL查询时间为0.281秒

第二种写法:

SELECT * FROM (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100) a LEFT JOIN test_user b ON a.id = b.id;

上述SQL查询时间为0.252秒

方法三:基于索引再排序

其中pageNum表示页码,其取值从0开始;pageSize表示指的是每页多少条数据。

SELECT * FROM 表名称 WHERE id_pk > (pageNum*pageSize) ORDER BY id_pk ASC LIMIT pageSize;

适应场景:

  • 适用于数据量多的情况
  • 最好ORDER BY后的列对象是主键或唯一索引
  • id数据没有缺失,可以作为序号使用
  • 使用ORDER BY操作能利用索引被消除,但结果集是稳定的

原因:

  • 索引扫描,速度会很快
  • 但MySQL的排序操作,只有ASC没有DESC。MySQL中索引存储的排序方式是ASC的,没有DESC的索引。这就能够理解为啥order by 默认是按照ASC来排序的了吧

方法四:基于索引使用prepare

PREPARE预编译一个SQL语句,并为其分配一个名称 stmt_name,以便以后引用该语句,预编译好的语句用EXECUTE执行。

PREPARE stmt_name FROM 'SELECT * FROM test_user WHERE id > ? ORDER BY id ASC LIMIT ?';
SET @a = 1000000;
SET @b = 100;
EXECUTE stmt_name USING @a, @b;;

上述SQL查询时间为0.047秒。

对于定义好的PREPARE预编译语句,我们可以使用下述命令来释放该预编译语句:

DEALLOCATE PREPARE stmt_name;

原因:

  • 索引扫描,速度会很快.
  • prepare语句又比一般的查询语句快一点。

方法五:利用"子查询+索引"快速定位数据

其中page表示页码,其取值从0开始;pagesize表示指的是每页多少条数据。

SELECT * FROM your_table WHERE id <= (SELECT id FROM your_table ORDER BY id DESC LIMIT ($page-1)*$pagesize ORDER BY id DESC LIMIT $pagesize);

方法六:利用复合索引进行优化

假设数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中id是主键自增,title用定长,info用text, vtype是tinyint,vtype是一个普通索引。

现在往里面填充数据,填充10万条记录,数据库表占用硬1.6G。

select id,title from collect limit 1000,10;

执行上述SQL速度很快,基本上0.01秒就OK。

select id,title from collect limit 90000,10;

然后再执行上述SQL,就发现非常慢,基本上平均8~9秒完成。

这个时候如果我们执行下述,我们会发现速度又变的很快,0.04秒就OK。

select id from collect order by id limit 90000,10;

那么这个现象的原因是什么?

答案:因为用了id主键做索引,  这里实现了索引覆盖,当然快。

所以如果想一起查询其它列的话,可以按照索引覆盖进行优化,具体如下:

select id,title from collect where id >= (select id from collect order by id limit 90000,1) limit 10;

再看下面的语句,带上where 条件:

select id from collect where vtype=1 order by id limit 90000,10; 

可以发现这个速度上也是很慢的,用了8~9秒!

这里有一个疑惑:vtype 做了索引了啊?怎么会慢呢?

vtype做了索引是不错,如果直接对vtype进行过滤:

select id from collect where vtype=1 limit 1000,10;

可以看到速度还是很快的,基本上0.05秒,如果从9万开始,那就是0.05*90=4.5秒的速度了。

其实加了 order by id 就不走索引,这样做还是全表扫描,解决的办法是:复合索引

因此针对下述SQL深度分页优化时可以加一个search_index(vtype,id)复合索引:

select id from collect where vtype=1 order by id limit 90000,10; 

综上

  • 在进行SQL查询深度分页优化时,如果对于有where条件,又想走索引用limit的,必须设计一个索引,将where放第一位,limit用到的主键放第二位,而且只能select 主键。
  • 最后根据查询出的主键走一级索引找到对应的数据。
  • 按这样的逻辑,百万级的limit 在0.0x秒就可以分完,完美解决了分页问题。

到此这篇关于MySQL调优之SQL查询深度分页问题的文章就介绍到这了,更多相关MySQL 查询深度分页内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • MySQL深度分页(千万级数据量如何快速分页)

    目录 前言 案例 优化 小结 前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned

  • 如何解决mysql深度分页问题

    目录 1.基本分页:耗时0.019秒 2.深度分页:耗时10.236秒 3.深度ID分页:耗时0.052秒 4.两步走深度分页:耗时0.049秒+0.017秒 5.一步走深度分页:耗时0.05秒 6.集成BeanSearcher框架 7.万能优化技巧:索引 总结 数据:单表数据25万条. 1.基本分页:耗时0.019秒 select * from cf_qb_info limit 0,20 2.深度分页:耗时10.236秒 select * from cf_qb_info limit 20000

  • MySQL调优之SQL查询深度分页问题

    目录 一.问题引入 二.MySQL中的limit用法 三.深度分页优化策略 方法一:用主键id或者唯一索引优化 方法二:利用索引覆盖优化 方法三:基于索引再排序 方法四:基于索引使用prepare 方法五:利用"子查询+索引"快速定位数据 方法六:利用复合索引进行优化 一.问题引入 例如当前存在一张表test_user,然后往这个表里面插入3百万的数据: CREATE TABLE `test_user` ( `id` int(11) NOT NULL AUTO_INCREMENT CO

  • Mysql调优Explain工具详解及实战演练(推荐)

    Mysql调优Explain工具详解以及实战演练 Explain工具介绍Explain分析示例explain 两个变种explain中的列 索引最佳实战索引使用总结: Mysql安装文档参考 Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是 执行这条SQL 注意:如果 from 中包含子查询,仍会执行该子

  • MySQL中一条SQL查询语句是如何执行的

    目录 前言 1. 处理连接 1.1 客户端和服务端的通信方式 1.1.1 TCP/IP协议 1.1.2 UNIX域套接字 1.1.3 命名管道和共享内存 1.2 权限验证 1.3 查看MySQL连接 2. 解析与优化 2.1 查询缓存 2.2 解析器 & 预处理器(Parser & Preprocessor) 2.2.1 词法解析 2.2.2 语法分析 2.2.3 预处理器 2.3 查询优化器(Optimizer)与查询执行计划 2.3.1 什么是查询优化器? 2.3.2 优化器究竟做了什

  • 分析mysql中一条SQL查询语句是如何执行的

    目录 一.MySQL 逻辑架构概览 二.连接器(Connector) 三.查询缓存(Query Cache) 四.解析器(Parser) 五.优化器(Optimizer) 六.执行器 七.小结 一.MySQL 逻辑架构概览 MySQL 最重要.最与众不同的特性就是它的可插拔存储引擎架构(pluggable storage engine architecture),这种架构的设计将查询处理及其他系统任务和数据的存储/提取分离开来.来看官网的解释: The MySQL pluggable stora

  • mysql sql语句性能调优简单实例

     mysql sql语句性能调优简单实例 在做服务器开发时,有时候对并发量有一定的要求,有时候影响速度的是某个sql语句,比如某个存储过程.现在假设服务器代码执行过程中,某个sql执行比较缓慢,那如何进行优化呢? 假如现在服务器代码执行如下sql存储过程特别缓慢: call sp_wplogin_register(1, 1, 1, '830000', '222222'); 可以按如下方法来进行调试: 1. 打开mysql profiling: 2. 然后执行需要调优的sql,我们这里执行两条sq

  • SQL Server 性能调优之查询从20秒至2秒的处理方法

    一.需求 需求很简单,就是需要查询一个报表,只有1个表,数据量大约60万左右,但是中间有些逻辑. 先说明一下服务器配置情况:1核CPU.2GB内存.机械硬盘.Sqlserver 2008 R2.Windows Server2008 R2 SP1和阿里云的服务器,简单说就是阿里云最差的服务器. 1.原始表结构 非常简单的一张表,这次不讨论数据冗余和表字段的设计,如是否可以把Project和Baojian提出成一个表等等,这个是原始表结构,这个目前是没有办法改变的. 2.查询的sql语句为 sele

  • 使用MySQL的Explain执行计划的方法(SQL性能调优)

    目录 前言 1. explain的使用 2. explain字段详解 id列 select_type列 table列 partitions列 type列 system const eq_ref ref ref_or_null index_merge range index ALL possible_keys列 key列 key_len列 ref列 rows列 filtered列 Extra列 Using where Using index Using filesort Using tempora

  • 分析MySQL复制以及调优原理和方法

    一. 简介 MySQL自带复制方案,带来好处有: 数据备份. 负载均衡. 分布式数据. 概念介绍: 主机(master):被复制的数据库. 从机(slave):复制主机数据的数据库. 复制步骤: (1). master记录更改的明细,存入到二进制日志(binary log). (2). master发送同步消息给slave. (3). slave收到消息后,将master的二进制日志复制到本地的中继日志(relay log). (4). slave重现中继日志中的消息,从而改变数据库的数据. 下

  • 数据库SQL调优的几种方式汇总

    目录 char  vs varchar 开启慢查询日志来定位查询慢的语句 合理使用关键字 优化查询缓存 适当使用索引 分割数据表 非规范化的方式 总结 最近在复习SQL调优,总结了下主要有以下几种方式: char  vs varchar 1.如果文本字段始终是固定长度的(例如,US 邮编,其始终具有“XXXXX-XXXX”形式的规范表示),那么推荐使用char.varchar 类型的长度是可变的,而 char 类型是一个定长的字段,以 char(10) 为例,不管真实的存储内容多大或者是占了多少

  • MySQL参数调优实例探究讲解

    目录 一.MySQL 参数调优 1.一些生产常用查询命令 2.分析慢查询 3.分析连接数 4.缓冲区调优 Key_buffer_size 5.临时表调优 Created_tmp_tables 6.分析 Open Table 使用情况 7.查看 MySQL进程使用情况 8.查询缓存 query cache 9.分析SQL排序与锁及文件使用与表扫描情况 二.MySQL优化参数实战 总结 一.MySQL 参数调优 1.一些生产常用查询命令 -- 查看数据库里正在执行的sql语句 show proces

随机推荐