文档章节

mysql大数据量分页场景的查询优化

big_cat
 big_cat
发布于 2016/05/11 18:00
字数 1746
阅读 2441
收藏 17

本文主要实测验证如何在不分表不使用程序并发的方式尽可能提升大数据量分页的应用场景。

预测条件:

数据量:400w

字   段:id 主索引,title、create_at 普通索引

通过 Cardinality(集的趋) 我们也能想出要尽量使用哪个索引,id 索引势 是 title 索引势的4倍,也就说 title 索引的检索能力只有 25 %, id 1 vs 1,title 这里 1 vs 25

1、先来清理下 order by 的一些小要点

    网上有不少博文都写着 order by 会启用索引 这么一个观点,其实这个观点并不是在所有场景下都适用的。当数据量比较小的时候,万级,此时 order by 会启用索引,这也是有时你使用 limit 做辅助检索也会帮助 order by 启用索引的原因。但当数据量达到十万,百万,千万级别时,此时 order by 就不会启用索引了。

select `id`, `title`, `create_at` from `sys_data` order by `id` limit 500000, 10;

上面这种查询方式,数据量一大 order by 就不使用索引了

5w 使用 索引

50w 索引失效了

    order by 在什么情况下才会一定使用索引呢,这个其实是有一个很确切的场景的

select `id` from `sys_data` order by `id` limit 3000000, 10;

     当 select 的列与 order by 完全对应时,order by 会一直使用索引。虽然可能会因为数据量比较大检索起来稍微有些慢,但比不使用索引快多了。

300w select 字段 与 order by 字段一致

所以,如果乱用 order by,很可能会让你步入 Using filesort 的深渊....

2、优化建议

首先,我们要明确,使用 Cardinality 比较高的索引有助于我们快速检索,像 primary key 这种索引定位查询的时间复杂度是O(1),指哪查哪。即是用来做 range 范围检索也要快的多

网上比较流行的优化方式如下

//升序
select * from sys_data where id > (select id from sys_data order by id limit 500000, 1) limit 10;
//降序
select * from sys_data where id > (select id from sys_data order by id desc limit 500000, 1) limit 10;

通过子查询启用 id 索引检索出第 50 w 条数据的 id,然后取后面或前面的 10 条

这样就能实现高效的分页场景,比 'select id from sys_data order by id desc limit 500000, 1' 这种方式效率提高十几倍

其实也可以这样子

先通过

select id from sys_data order by id limit 500000, 10;

检索出所有符合条件的 id,然后将id组装成 in 模式,二次查询

select * from sys_data where id in(5000001, 5000002, 5000003, 5000004, .....5000010);

这种方式比较容易在业务层实现,方便控制和筛选

代码是这样写的

example_1是我自己写的in二次查询

example_2是网上流行的方法

example_3则是网上流传的错误知识点的方法 当 select 字段与 order by 字段不吻合时 order by 只有在少量数据下才会启用索引,否则无效,大家看结果吧

<?php
/**
 * mysql 大数据量分页优化
 * @author sallency@osc 2016-5-11 16:48:44
 */
$conn = new mysqli('localhost', 'root', '123456', 'sys');

/**
 *  400w数据分页优化
 */

/**
 * 自己在业务层做写的
 * 先通过 order by 索引的特性将符合条件的 id 检索出来
 * 再次拼接成 in 条件进行检索 链接没断掉 虽然二次查询但不消耗再次建立链接的资源
 * 但遗憾的是你不能把它写成一条语句去执行 mysql 无法优化成我们想要的执行逻辑
 */
$sql = "select sql_no_cache id from sys_data order by id limit 3000020, 10";
$start = microtime(true);
$result = $conn->query($sql);
$id_arr = array_column($result->fetch_all(), 0);
$id_set_str = implode(',', $id_arr);
$sql = "select * from sys_data where id in($id_set_str)";
$result = $conn->query($sql);
$data_1 = $result->fetch_all();
$end = microtime(true);
echo "example_1:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

/**
 * 业界流传的经典方法
 * 通过子查询查处符合条件的 id,取偏移量获得记录
 */
$sql = "select sql_no_cache * from sys_data where id > (select id from sys_data order by id limit 3000020, 1) limit 10";
$start = microtime(true);
$result = $conn->query($sql);
$data_2 = $result->fetch_all();
$end = microtime(true);
echo "example_2:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

/**
 * 普通方法
 * 此类方法针对对数据量比较小的时候还可以应对
 * 数据量十万时可能order by 就无法启用索引了 具体我也没测
 */
$sql = "select sql_no_cache * from sys_data order by id limit 3000020, 10";
$start = microtime(true);
$result = $conn->query($sql);
$data_3 = $result->fetch_all();
$end = microtime(true);
echo "example_3:";
echo number_format($end - $start, 3) . ' secs' . PHP_EOL;

然后我惊喜的发现我自己的方式比业界流行的还要快一些

limit 500, 10 在数据量比较小的时候最基本的方法效率反而更高一些

example_1:0.006 secs
example_2:0.015 secs
example_3:0.003 secs

limit 5000, 10 我突然发现我的方法比网上的还好一些 哈哈

example_1:0.004 secs
example_2:0.008 secs
example_3:0.013 secs

limit 50000,10 5w 我的方法依然雄壮

example_1:0.029 secs
example_2:0.042 secs
example_3:0.109 secs

limit 500000,10   50w 差距一目了然了

example_1:0.286 secs
example_2:0.346 secs
example_3:4.563 secs

limit 3000000, 10 300w 原始方法已经无法忍受了

300w 第一次
example_1:1.356 secs
example_2:1.936 secs
example_3:6.097 secs

300w 第二次
example_1:1.013 secs
example_2:1.534 secs
example_3:6.306 secs

300w 第三次
example_1:1.248 secs
example_2:1.924 secs
example_3:5.769 secs

300w 第四次
example_1:0.782 secs
example_2:1.582 secs
example_3:5.324 secs

我自己写的方法可能有些麻烦,分两步,但性能还是可以的,对比业界流行的方法或者普通的乱用方法显而易见,但比较蛋疼的是你不能把子查询写到 in 里面去,分析一下:

    大家都知道 in 和 exists 的争论,大致就是 大子表用 exists 小子表用 in,exists会根据主表条件去遍历子表,选取同时符合exists条件的记录。in 则是根据子表条件来卡主表,先将子表的数据查询出来,然后再检索主表看是否有符合的记录存在。

   所以大子表的时候我们应该偏向检索主表,这样记录才会比较少,速度会更快,所以用 exists 比较合适;小子表的时候应该先用 in 将子查询条件检索出来,然后再去检索主表。

最后来个重量级的测试,其实我主表里一共有 34105584 (三千四百一十万 零五千五百八十四)条数据, 17 个字段, 而且字段大都设计的不合理,很多都是 varchar,不关我事,项目一直在线,我也不能改,现在用分表缓解的查询压力,看下测试结果吧

limit 34105450, 100 我的方法又再度雄起,虽然超过了 3秒 但和其他方法比起来性能很不错了

example_1:17.915 secs
example_2:51.673 secs
example_3:209.536 secs

就写到这里吧 

© 著作权归作者所有

big_cat
粉丝 49
博文 237
码字总数 162865
作品 0
长宁
后端工程师
私信 提问
阿里咸鱼毫秒级从百亿大表任意维度筛选数据,是怎么做到的...

  点击上方“java进阶架构师”,选择右上角“置顶公众号”   20大进阶架构专题每日送达   进阶架构精品专题   Mysql优化专题(★★★★)   网络协议专题(★★★★)   其余18大...

java进阶架构师
2018/12/20
0
0
百亿大表任意维度查询,如何做到毫秒级返回?

随着闲鱼业务的发展,用户规模达到数亿级,用户维度的数据指标,达到上百个之多。 如何从亿级别的数据中,快速筛选出符合期望的用户人群,进行精细化人群运营,是技术需要解决的问题。业界的...

架构师springboot
01/23
179
0
【limit优化】MySQL延迟关联性能优化方法

一.业务 假设业务某个场景中,需要查询多次查询数据,sql如下: startNum = i * 500;select id,content from testtable order by updatedate asc limit " + startNum + ",500"; limit n,m定义......

谢思华
2015/08/24
971
0
[MySQL优化案例]系列 — 分页优化

通常,我们会采用ORDER BY LIMIT start, offset 的方式来进行分页查询。例如下面这个SQL: SELECT * FROM t1 WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10; 或者像下面这个不带任何条件的分...

运维技术
2016/08/08
117
0
如何做到毫秒级从百亿大表任意维度筛选数据?| 技术头条

业务背景 随着闲鱼业务的发展,用户规模达到数亿级,用户维度的数据指标,达到上百个之多。如何从亿级别的数据中,快速筛选出符合期望的用户人群,进行精细化人群运营,是技术需要解决的问题...

CSDN资讯
03/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

LeetCode 第 287 号问题:寻找重复数,一道非常简单的数组遍历题,加上四个条件后感觉无从下手

今天分享的题目来源于 LeetCode 第 287 号问题:寻找重复数。 题目描述 给定一个包含 n + 1 个整数的数组 nums,其数字都在 1 到 n 之间(包括 1 和 n),可知至少存在一个重复的整数。假设只...

五分钟学算法
17分钟前
2
0
vuex mapActions

本文转载于:专业的前端网站➧vuex mapActions 在组件中使用 this.$store.dispatch('xxx') 分发 action,或者使用 mapActions 辅助函数将组件的 methods 映射为 store.dispatch 调用(需要先在...

前端老手
18分钟前
2
0
使用bash -x 调试信息查看lvextend -r的调用

--1.打开调试: [root@db01 storage]# set -x --2.执行命令: [root@db01 storage]# /usr/sbin/lvextend -r -L 710M /dev/shazam/storage + /usr/sbin/lvextend -r -L 710M /dev/shazam/sto......

突突突酱
27分钟前
2
0
Linux重定向及反弹shell详解

这里说Linux重定向的原因主要还是为了详细了解一下Linux中的反弹shell 目录 0x01 文件描述符 0x02 重定向 0x03 反弹shell 0x04 交互重定向 0x05 命令变形 0x01 文件描述符 Linux的文件描述符...

无心的梦呓
33分钟前
2
0
开发经验 初学51单片机建议用C语言

typesetting : Markdown    blog : my.oschina.net/zhichengjiu    gitee : gitee.com/zhichengjiu   新手学习51单片机建议用C语言。因为使用C语言入门的话,上手速度快。实现几个例程后...

志成就
38分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部