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

[yejinmo]

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

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

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

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

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

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

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

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

谢谢

[glues]

你的问题应该是 OFFSET 而不是 LIMIT 吧？

[yejinmo]

@glues #1
对。。。OFFSET。。

[mokeyjay]

楼上+1，mysql 的 offset 是有很大缺陷的，用其他方法替代吧

[yejinmo]

@mokeyjay #3
本来不是做数据库的硬生生的啃了好几天数据库。。
分页居然是读了 OFFSET + LIMIT 然后丢掉 OFFSET 只拿 LIMIT
好大的坑

[glues]

@yejinmo 自己搭个 Postgres，随机生成个几千万数据，测试一下就知道了
分页的话，用游标，不要用 OFFSET

[topbandit]

表结构和 SQL 贴出来，专业 DBA 优化下，千万级别 SELECT，那还不是遛得飞起

[yejinmo]

@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]

路过看看，收藏

[QAPTEAWH]

想办法用 where 代替 offset

[yejinmo]

@QAPTEAWH #9
数据没有连续性啊。。如果是 MySQL 实在是想不到什么好办法了

[crazyneo]

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]

这个问题无解，我所知道的数据库（当然我知道的也不多）基本上要么不支持要么就是扫描后丢掉的。

优化思路上面的也都说了，另外这个和数据是否连续有什么关系。。。你只有有单调增减的字段即可（无需连续）。

[bugsnail]

这个只能程序那边做手脚了, 方法和 @crazyneo #11 类似

https://iexplore.cc/2016/offsetSlow/

[topbandit]

1. 不知道你用 MYSQL 哪个版本的，我了解到的：MYSQL 5.7 起，innodb 比 5.6 以前版本有质的飞跃（这应该是由 oracle 团队带来的），性能应该跟上来了，建议用新版的 InnoDB，也可以和 PG 做下对比

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

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

(PS: 只是个指引，也可能不对，本人曾经在搞数据的公司待过，略懂些，nosql 没接触过）

[jjianwen68]

不太可能为了这个换系统使用的数据库吧

[yejinmo]

@jhdxr #12
表中有一个 id 的字段，由写入历史时提供，虽是唯一但不一定单调
取 id 然后拿后边的记录的思路，是不能有条件查询的吧

[yejinmo]

@topbandit #14
MyISAM 在单纯读写性能上要比 InnoDB 略高些吧
我也只是个码农不懂数据库优化啊心好累

[yejinmo]

@jjianwen68 #15
还没有正式交付，换数据库什么的还来得及

[zhNaMore]

先看看瓶颈在哪里吧。是硬件问题还是你 select 语句问题。
要不你把 sql 或者解析结果发出来大家看看。单纯的换数据库我觉得并不能解决问题。

[jhdxr]

@yejinmo 保证单调是必须的。如果没有其实你可以自己建一个自增字段。
另外当然是可以有查询条件的，排序也没问题。关键字 游标分页

缺点就是不能指定跳转到第几页。如果你的需求是这样子的，建议你查两次，第一次只查单调的字段，查出来范围后第二次再根据那个字段去查真正的数据。这样子在第一次查询时丢弃的数据可以少一些_(:з」∠)_