V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
yejinmo
V2EX  ›  数据库

PostgreSQL 的 LIMIT 操作相比 MySQL 性能是否有极大提高?

  •  
  •   yejinmo · 2017-12-19 15:00:06 +08:00 · 2339 次点击
    这是一个创建于 2542 天前的主题,其中的信息可能已经有所发展或是发生改变。

    手上的项目由于客户对历史记录查询的要求颇高,MySQL 的性能不太够用了

    历史记录表需要在 4 个 int 字段上做筛选

    以及一个时间字段上做范围筛选

    数据量在千万级,现在 SELECT 一下子要几十秒,主要耗时在了 LIMIT 上

    由于筛选之后数据不连续,也想不到什么可以优化的地方了

    查了查资料说 PostgreSQL 对大量数据有特殊优化

    特想问下 PostgreSQL 的分页是不是要比 MySQL 好些

    或者有什么别的潇洒方法解决这个问题

    谢谢

    第 1 条附言  ·  2017-12-19 17:01:34 +08:00


    CREATE TABLE `event_info` (
    `event_name` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '事件名称',
    `event_info` char(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '事件信息',
    `event_time` bigint(20) NOT NULL COMMENT '事件时间',
    `event_id` bigint(20) NOT NULL COMMENT '事件编号',
    `event_value` double(16, 4) NOT NULL COMMENT '事件值',
    `state0` int(8) NOT NULL COMMENT '自定义 0',
    `state1` int(8) NOT NULL COMMENT '自定义 1',
    `state2` int(8) NOT NULL COMMENT '自定义 2',
    `state3` int(8) NOT NULL COMMENT '自定义 3',
    INDEX `key_event_time`(`event_time`) USING BTREE,
    INDEX `key_state0`(`state0`) USING BTREE,
    INDEX `key_state1`(`state1`) USING BTREE,
    INDEX `key_state2`(`state2`) USING BTREE,
    INDEX `key_state3`(`state3`) USING BTREE
    ) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = FIXED;
    44 条回复    2017-12-20 18:31:30 +08:00
    glues
        1
    glues  
       2017-12-19 15:03:40 +08:00   ❤️ 1
    你的问题应该是 OFFSET 而不是 LIMIT 吧?
    yejinmo
        2
    yejinmo  
    OP
       2017-12-19 15:04:24 +08:00
    @glues #1
    对。。。OFFSET。。
    mokeyjay
        3
    mokeyjay  
       2017-12-19 15:05:04 +08:00   ❤️ 1
    楼上+1,mysql 的 offset 是有很大缺陷的,用其他方法替代吧
    yejinmo
        4
    yejinmo  
    OP
       2017-12-19 15:07:34 +08:00
    @mokeyjay #3
    本来不是做数据库的硬生生的啃了好几天数据库。。
    分页居然是读了 OFFSET + LIMIT 然后丢掉 OFFSET 只拿 LIMIT
    好大的坑
    glues
        5
    glues  
       2017-12-19 15:08:09 +08:00
    @yejinmo 自己搭个 Postgres,随机生成个几千万数据,测试一下就知道了
    分页的话,用游标,不要用 OFFSET
    topbandit
        6
    topbandit  
       2017-12-19 15:14:00 +08:00   ❤️ 1
    表结构和 SQL 贴出来,专业 DBA 优化下,千万级别 SELECT,那还不是遛得飞起
    yejinmo
        7
    yejinmo  
    OP
       2017-12-19 15:23:17 +08:00
    @topbandit #6
    感觉。。这不是优化不优化的问题了吧,硬件条件限制了吧,单机 MySQL 干这个好吃力
    CREATE TABLE `event_info` (
    `event_name` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '事件名称',
    `event_info` char(80) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '事件信息',
    `event_time` bigint(20) NOT NULL COMMENT '事件时间',
    `event_id` bigint(20) NOT NULL COMMENT '事件编号',
    `event_value` double(16, 4) NOT NULL COMMENT '事件值',
    `state0` int(8) NOT NULL COMMENT '自定义 0',
    `state1` int(8) NOT NULL COMMENT '自定义 1',
    `state2` int(8) NOT NULL COMMENT '自定义 2',
    `state3` int(8) NOT NULL COMMENT '自定义 3',
    INDEX `key_event_time`(`event_time`) USING BTREE,
    INDEX `key_state0`(`state0`) USING BTREE,
    INDEX `key_state1`(`state1`) USING BTREE,
    INDEX `key_state2`(`state2`) USING BTREE,
    INDEX `key_state3`(`state3`) USING BTREE
    ) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = FIXED;
    yinjiayi
        8
    yinjiayi  
       2017-12-19 15:31:29 +08:00   ❤️ 1
    路过看看,收藏
    QAPTEAWH
        9
    QAPTEAWH  
       2017-12-19 15:32:39 +08:00   ❤️ 1
    想办法用 where 代替 offset
    yejinmo
        10
    yejinmo  
    OP
       2017-12-19 15:35:20 +08:00
    @QAPTEAWH #9
    数据没有连续性啊。。如果是 MySQL 实在是想不到什么好办法了
    crazyneo
        11
    crazyneo  
       2017-12-19 15:52:06 +08:00   ❤️ 2
    MariaDB 和 Percona 有讲 pagination 的,基本思路是在前端过滤掉不相关的查询进而摆脱 offset,使用自增主键来计算当前 pagination 所需的查询列表。
    https://mariadb.com/kb/en/library/pagination-optimization/
    https://www.percona.com/files/presentations/ppc2009/PPC2009_mysql_pagination.pdf
    jhdxr
        12
    jhdxr  
       2017-12-19 16:14:29 +08:00   ❤️ 1
    这个问题无解,我所知道的数据库(当然我知道的也不多)基本上要么不支持要么就是扫描后丢掉的。

    优化思路上面的也都说了,另外这个和数据是否连续有什么关系。。。你只有有单调增减的字段即可(无需连续)。
    bugsnail
        13
    bugsnail  
       2017-12-19 16:20:33 +08:00   ❤️ 1
    这个只能程序那边做手脚了, 方法和 @crazyneo #11 类似

    https://iexplore.cc/2016/offsetSlow/
    topbandit
        14
    topbandit  
       2017-12-19 16:21:06 +08:00   ❤️ 1
    1. 不知道你用 MYSQL 哪个版本的,我了解到的:MYSQL 5.7 起,innodb 比 5.6 以前版本有质的飞跃(这应该是由 oracle 团队带来的),性能应该跟上来了,建议用新版的 InnoDB,也可以和 PG 做下对比

    2. SELECT 语句和建表语句要反复锤炼下
    建表语句字段和索引的创建,要和 SELECT 要结合起来考虑; SELECT 语句的底层处理逻辑,用 explain 打印下执行计划,检查下逻辑上的问题,有无改写提升的可能; SELECT 语句会产生许多中间表(临时表),需要查看中间表 Profile 性能瓶颈

    3. 数据库优化有本入门好书,《高性能 MySQL 》第 3 版,翻译的不太好,也能看

    (PS: 只是个指引,也可能不对,本人曾经在搞数据的公司待过,略懂些,nosql 没接触过)
    jjianwen68
        15
    jjianwen68  
       2017-12-19 16:23:16 +08:00   ❤️ 1
    不太可能为了这个换系统使用的数据库吧
    yejinmo
        16
    yejinmo  
    OP
       2017-12-19 16:28:02 +08:00
    @jhdxr #12
    表中有一个 id 的字段,由写入历史时提供,虽是唯一但不一定单调
    取 id 然后拿后边的记录的思路,是不能有条件查询的吧
    yejinmo
        17
    yejinmo  
    OP
       2017-12-19 16:30:16 +08:00
    @topbandit #14
    MyISAM 在单纯读写性能上要比 InnoDB 略高些吧
    我也只是个码农不懂数据库优化啊心好累
    yejinmo
        18
    yejinmo  
    OP
       2017-12-19 16:31:01 +08:00
    @jjianwen68 #15
    还没有正式交付,换数据库什么的还来得及
    zhNaMore
        19
    zhNaMore  
       2017-12-19 16:34:07 +08:00   ❤️ 1
    先看看瓶颈在哪里吧。是硬件问题还是你 select 语句问题。
    要不你把 sql 或者解析结果发出来大家看看。单纯的换数据库我觉得并不能解决问题。
    jhdxr
        20
    jhdxr  
       2017-12-19 16:35:06 +08:00   ❤️ 1
    @yejinmo 保证单调是必须的。如果没有其实你可以自己建一个自增字段。
    另外当然是可以有查询条件的,排序也没问题。关键字 游标分页

    缺点就是不能指定跳转到第几页。如果你的需求是这样子的,建议你查两次,第一次只查单调的字段,查出来范围后第二次再根据那个字段去查真正的数据。这样子在第一次查询时丢弃的数据可以少一些_(:з」∠)_
    runntuu
        21
    runntuu  
       2017-12-19 16:50:35 +08:00 via iPhone   ❤️ 1
    @yejinmo MyISAM 只是单纯读性能好,在写并发和事务支持上跟 InnoDB 没有可比性。
    另外对于索引的建立,多个独立列的索引不如一个适合大多数查询的覆盖索引。
    另外还没有看到你的查询计划,能否一并提出来参考一下?
    likuku
        22
    likuku  
       2017-12-19 16:54:08 +08:00   ❤️ 1
    翻到笔记, 2013 年前做过对比测试,同一软硬件环境,非常简单的单表,180 万行:

    postgres=# select count(*) from status;
    count
    ---------
    1841495
    (1 row)

    Time: 516.717 ms

    mysql Innodb 表:

    mysql> select count(*) from status;
    +----------+
    | count(*) |
    +----------+
    | 1841495 |
    +----------+
    1 row in set (3.53 sec)

    表结构:

    | status | CREATE TABLE `status` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `date` date NOT NULL,
    `license` varchar(100) NOT NULL,
    `localdomain` varchar(100) NOT NULL,
    `mailing` varchar(50) NOT NULL DEFAULT 'unknown',
    `isp` varchar(50) NOT NULL,
    `success` int(50) NOT NULL,
    `spamrelay` int(50) NOT NULL,
    `overlimit` int(50) NOT NULL,
    `badmailbox` int(50) NOT NULL,
    `timeexpired` int(50) NOT NULL,
    `unknown` int(50) NOT NULL DEFAULT '0',
    `open` int(50) NOT NULL DEFAULT '0',
    `click` int(50) NOT NULL DEFAULT '0',
    `olapstamp` char(19) NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updatestamp` char(19) NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1911671 DEFAULT CHARSET=utf8 |
    +--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    yejinmo
        23
    yejinmo  
    OP
       2017-12-19 17:03:53 +08:00
    @zhNaMore #19
    @runntuu #21
    相关信息已 Append
    之所以考虑换掉 MySQL
    是觉得以 MySQL 的机制对大表查询不够友好啊
    yejinmo
        24
    yejinmo  
    OP
       2017-12-19 17:05:25 +08:00
    @likuku #22
    额。。postgres 上没有对表和查询做特殊的优化么?
    likuku
        25
    likuku  
       2017-12-19 17:11:30 +08:00
    @yejinmo

    count(*) 是比较特别的需求(我那时就是需要这个),对你参考价值有限,建议你直接装一个 PG 载入数据实际测测看。

    这个测试里 postgresql 没有作特殊优化,debian 下直接 apt-get 装完就测试。

    MySQL 是作了优化,设置了 InnoDB MEM Pool 为内存一半,其他相关优化也做过。

    对了,MyISAM 类型表,count(*) 是直接返回数值,没有实际查询耗时(因为它对这个操作设计记得就是删改记录时维护一个计数器,count()直接返回数值)。MyISAM 不支持事务,只有表级锁,没有全文索引,表特别容易坏,只适合纯查询,Innodb 这些年性能也提高很多了,一般也没人用 MyISAM 了。
    likuku
        26
    likuku  
       2017-12-19 17:14:11 +08:00
    @yejinmo 你这台机是 SSD 么?换用 SSD 对 DB 的效能提升简直就是银子弹。
    yejinmo
        27
    yejinmo  
    OP
       2017-12-19 17:16:35 +08:00
    @likuku #25
    膜拜大手 我去跑一遍试试
    另外配置使用默认会对性能造成很大影响么?
    likuku
        28
    likuku  
       2017-12-19 17:19:42 +08:00   ❤️ 1
    @yejinmo [配置使用默认] MySQL ? PostgreSQL ?

    MySQL 使用默认,尤其 InnoDB 使用默认,则极大影响性能。
    likuku
        29
    likuku  
       2017-12-19 17:22:01 +08:00   ❤️ 1
    mysql, postgresql 都有内置的 benchmark 基准测试功能,可以用这个测试作对比
    likuku
        30
    likuku  
       2017-12-19 17:25:50 +08:00   ❤️ 1
    PostgreSQL 性能优化方法 - 1-博客-云栖社区-阿里云 : https://yq.aliyun.com/articles/214

    发现这个博客,PG 相关还有好几篇

    Greenplum 是基于 PostgreSQL 的并行计算版本,假若单机 DB 还不能满足,可以尝试用它
    runntuu
        31
    runntuu  
       2017-12-19 17:48:47 +08:00 via iPhone   ❤️ 1
    @yejinmo mysql 的 offset 是个坑,offset 的逻辑是读取 500050 条记录,然后抛弃前面的 50w 条数据,返回 50 条记录。所以这么简单的查询,即使走了索引,你的 show profile 还是花了 46s 在 sending data 从磁盘读。
    建议你考虑使用字段 event_id 来分页,然后在 limit 里仅限制返回行数。
    wucancc
        32
    wucancc  
       2017-12-19 18:18:17 +08:00   ❤️ 1
    有一个思路。

    题主提到了数据不是连续的,是否可以这么解决了。
    offset limit 的速度很慢,但是数据表加了索引后对 between and 的查询速度极快。
    比如说你的是数据是:1,2,3,4,8,9,10,需要每次取 3 条。
    先用 select * from table between n and n+10; 每次多取一些。
    再对结果取前 3 条。
    如果结果不足 3 条,再查一次 select * from table between n and n+20; 直到结果多余 3 条。
    tabris17
        33
    tabris17  
       2017-12-19 18:25:54 +08:00
    现在不是都不用分页了么,都是从当前数据加载更多啊
    wucancc
        34
    wucancc  
       2017-12-19 18:34:37 +08:00
    @tabris17 对于后台来说不是一样么,都是再获取一页的数据。在前台展现的是不清空当前数据继续追加。
    ke1e
        35
    ke1e  
       2017-12-19 19:02:31 +08:00 via Android   ❤️ 1
    或者可以考虑分表
    likuku
        36
    likuku  
       2017-12-19 19:14:14 +08:00   ❤️ 1
    [历史记录表需要在 4 个 int 字段上做筛选] 历史记录筛选,这个是不是交由 hadoop 来作个 mapreduce 会更好?
    whx20202
        37
    whx20202  
       2017-12-19 19:14:29 +08:00   ❤️ 3
    我来说说吧
    如果仅仅是为了分页的话,可以参考 openstack 的实现
    第一页:
    select * from instances limit 20;
    第二页:
    select * from instances where id> 第 20 行的 ID limit 20;

    改进版:
    select * from instances where id> 第 20 行的 ID and created_at>第 20 行的时间 limit 20;

    仅供参考
    chengs
        38
    chengs  
       2017-12-20 10:01:20 +08:00
    @whx20202 为什么不用 pg 呢
    whx20202
        39
    whx20202  
       2017-12-20 10:20:41 +08:00   ❤️ 1
    @chengs
    不太懂你说的哪一个领域不用 pg 哈
    我司搞 openstack 用的是 pg
    开源社区 openstack 主要是 mysql
    我个人项目两个都用

    pg 如果单纯 limit offset 也很慢,得用一些高级特性或者技巧,德哥的博客里说了不少
    我说的那个办法,pg mysql 都能用啊
    alcarl
        40
    alcarl  
       2017-12-20 13:06:20 +08:00 via Android   ❤️ 1
    可以考虑用子查询搜索第一条记录 id 的方法再加个时间排序,一般都是这么优化。但数据库不是万能的,还要考虑针对业务来优化一下,几千万行在里面一页一页翻这种需求我是没想出来谁会去翻。。。。
    yejinmo
        41
    yejinmo  
    OP
       2017-12-20 13:43:54 +08:00
    @runntuu #31
    @wucancc #32
    @alcarl #40

    类似的解决思路想过,包括 @wucancc #32 所说,但这样依旧会造成极大的性能损失,因为数据实在是不连续呀。。。
    还有需要 COUNT 进行总页数的返回,实在是没有思路了

    可能 关系库 真的不适合做这样的事情,要考虑在数据存储上入手了
    谢过大家了
    runntuu
        42
    runntuu  
       2017-12-20 16:12:45 +08:00 via iPhone
    @yejinmo 试一下这个查询吧,在字段 event_id 建一个索引
    select * from event_info inner join ( select event_id from event_info where state0=0 limit 500000,50) q using (event_id)
    yejinmo
        43
    yejinmo  
    OP
       2017-12-20 16:35:28 +08:00
    @runntuu #42
    此条语句五千万数据要一秒,子查询中的 where 拖慢了运行时间

    目前考虑使用实时库做历史查询,将数据自定协议二进制化后存入实时库,再自建索引,根据时间查询,测试了些数据是很快的
    wucancc
        44
    wucancc  
       2017-12-20 18:31:30 +08:00
    @yejinmo count 操作是不需要 count(*)的,count(*)实在是太慢了。pg 在存数据的时候在 scheme 里面存了数据表的条数,可以直接取。
    可以参考: http://www.voidcn.com/article/p-qrcvdalt-bbz.html

    我目前用的,between and 是在建索引后最快的方式了。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4148 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 34ms · UTC 04:11 · PVG 12:11 · LAX 20:11 · JFK 23:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.