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

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

@glues #1
对。。。OFFSET。。

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

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

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

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

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

路过看看，收藏

想办法用 where 代替 offset

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

MariaDB 和 Percona 有讲 pagination 的，基本思路是在前端过滤掉不相关的查询进而摆脱 offset，使用自增主键来计算当前 pagination 所需的查询列表。
https://mariadb.com/kb/en/library/pagination-optimization/
https://www.percona.com/files/presentations/ppc2009/PPC2009_mysql_pagination.pdf

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@yejinmo MyISAM 只是单纯读性能好，在写并发和事务支持上跟 InnoDB 没有可比性。
另外对于索引的建立，多个独立列的索引不如一个适合大多数查询的覆盖索引。
另外还没有看到你的查询计划，能否一并提出来参考一下？

翻到笔记, 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)

@zhNaMore #19
@runntuu #21
相关信息已 Append
之所以考虑换掉 MySQL
是觉得以 MySQL 的机制对大表查询不够友好啊

@likuku #22
额。。postgres 上没有对表和查询做特殊的优化么？

@yejinmo

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

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

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

对了，MyISAM 类型表，count(*) 是直接返回数值，没有实际查询耗时(因为它对这个操作设计记得就是删改记录时维护一个计数器，count()直接返回数值)。MyISAM 不支持事务，只有表级锁，没有全文索引，表特别容易坏，只适合纯查询，Innodb 这些年性能也提高很多了，一般也没人用 MyISAM 了。

@yejinmo 你这台机是 SSD 么？换用 SSD 对 DB 的效能提升简直就是银子弹。

@likuku #25
膜拜大手 我去跑一遍试试
另外配置使用默认会对性能造成很大影响么？

@yejinmo [配置使用默认] MySQL ? PostgreSQL ?

MySQL 使用默认，尤其 InnoDB 使用默认，则极大影响性能。

mysql, postgresql 都有内置的 benchmark 基准测试功能，可以用这个测试作对比

PostgreSQL 性能优化方法 - 1-博客-云栖社区-阿里云 : https://yq.aliyun.com/articles/214

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

Greenplum 是基于 PostgreSQL 的并行计算版本，假若单机 DB 还不能满足，可以尝试用它

@yejinmo mysql 的 offset 是个坑，offset 的逻辑是读取 500050 条记录，然后抛弃前面的 50w 条数据，返回 50 条记录。所以这么简单的查询，即使走了索引，你的 show profile 还是花了 46s 在 sending data 从磁盘读。
建议你考虑使用字段 event_id 来分页，然后在 limit 里仅限制返回行数。

有一个思路。

题主提到了数据不是连续的，是否可以这么解决了。
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 对于后台来说不是一样么，都是再获取一页的数据。在前台展现的是不清空当前数据继续追加。

或者可以考虑分表

[历史记录表需要在 4 个 int 字段上做筛选] 历史记录筛选，这个是不是交由 hadoop 来作个 mapreduce 会更好?

我来说说吧
如果仅仅是为了分页的话，可以参考 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;

仅供参考

@whx20202 为什么不用 pg 呢

@chengs
不太懂你说的哪一个领域不用 pg 哈
我司搞 openstack 用的是 pg
开源社区 openstack 主要是 mysql
我个人项目两个都用

pg 如果单纯 limit offset 也很慢，得用一些高级特性或者技巧，德哥的博客里说了不少
我说的那个办法，pg mysql 都能用啊

可以考虑用子查询搜索第一条记录 id 的方法再加个时间排序，一般都是这么优化。但数据库不是万能的，还要考虑针对业务来优化一下，几千万行在里面一页一页翻这种需求我是没想出来谁会去翻。。。。

@runntuu #31
@wucancc #32
@alcarl #40

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

可能 关系库 真的不适合做这样的事情，要考虑在数据存储上入手了
谢过大家了

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

@runntuu #42
此条语句五千万数据要一秒，子查询中的 where 拖慢了运行时间

目前考虑使用实时库做历史查询，将数据自定协议二进制化后存入实时库，再自建索引，根据时间查询，测试了些数据是很快的

@yejinmo count 操作是不需要 count(*)的，count(*)实在是太慢了。pg 在存数据的时候在 scheme 里面存了数据表的条数，可以直接取。
可以参考： http://www.voidcn.com/article/p-qrcvdalt-bbz.html

我目前用的，between and 是在建索引后最快的方式了。