分页查询优化,mysql分页优化问题，主键ID并非自增的整形

分页查询优化,mysql分页优化问题，主键ID并非自增的整形详细介绍

本文目录一览： Mysql使用limit深度分页优化

mysql使用select * limit offset, rows分页在深度分页的情况下。性能急剧下降。
limit用于数据的分页查询，当然也会用于数据的截取，下面是limit的用法：
1. 模仿百度、谷歌方案（前端业务控制）
类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了
2. 记录每次取出的最大id， 然后where id > 最大id
select * from table_name Where id > 最大id limit 10000, 10; 这种方法适用于：除了主键ID等离散型字段外，也适用连续型字段datetime等 最大id由前端分页pageNum和pageIndex计算出来。
3. IN获取id
4. join方式 + 覆盖索引（推荐）
如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！
1. jdbcpagingReader使用方式
2. db索引分区器使用方式
入参1： 表名 如test_table
入参2： 排序索引字段 可以是主键，也可以是其他索引。需要保证是唯一索引即可。如：id
入参3： 主键可手动传入，也可以根据表名计算出来：现在只支持单列主键的。 如：id
入参4: 具体表 要分多少块。如：4

MySQL百万级数据量分页查询方法及其优化建议

offset+limit方式的分页查询，当数据表超过100w条记录，性能会很差。
主要原因是offset limit的分页方式是从头开始查询，然后舍弃前offset个记录，所以offset偏移量越大，查询速度越慢。

比如: 读第10000到10019行元素(pk是主键/唯一键).

使用order by id可以在查询时使用主键索引。
但是这种方式在id为uuid的时候就会出现问题。可以使用where in的方式解决：

带条件的查询：
如果在分页查询中添加了where条件例如 type = 'a’这样的条件，sql变成 ：

这种情况因为type没有使用索引也会导致查询速度变慢。但是只添加type为索引查询速度还是很慢,是因为查询的数据量太多了。这个时候考虑添加组合索引，组合索引的顺序要where条件字段在前，id在后，如 (type,id)，因为组合索引查询时用到了type索引，而type跟id是组合索引的关系，如果只select id ，那么直接就可以按组合索引返回id，而不需要再进行一次查询去返回id

使用uuid作为主键不仅会带来性能上的问题，在查询时也会遇到问题。

因为在使用select id from table limit 10000,10 查询id数据时，默认是对id进行排序，返回的是排序后的id结果，如果我们想按插入顺序查询结果，这样查询出来的结果就与我们的需求不相符。

聚集索引跟非聚集索引：聚集索引类似与新华字典的拼音，根据拼音搜索到的信息都是连续的，可以很快获取到它前后的信息。非聚集索引类似于部首查询，信息存放的位置可能不在一个区域。对经常使用范围查询的字段考虑使用聚集索引。

InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。

如果您的表上定义有主键，该主键索引是聚集索引。
如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。
如果没有这样的列，InnoDB就自己产生一个这样的ID值，
优先选index key_len小的索引进行count(*),尽量不使用聚簇索引

在没有where条件的情况下，count(*)和count(常量)，如果有非聚簇索引，mysql会自动选择非聚簇索引，因为非聚簇索引所占的空间小，如果没有非聚簇索引会使用聚集索引。count(primary key)主键id为聚集索引，使用聚集索引。有where条件的情况下，是否使用索引会根据where条件判断。

数据量太大，分页查询变慢，有什么优化查询的方法吗

很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。
我们先从一个常用但性能很差的查询来看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的问题在于offset(分页偏移量)很大的时候，像下面这样：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。
对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
这个语句耗时20.02sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。
下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。
高效的计算行数
如果采用的引擎是MyISAM，可以直接执行COUNT(*)去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。
我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
获取记录
下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。
如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
现在想获取任意一页的元素就很简单了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下来就可以向下面一样执行分页查询了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。
下面以关系数据库系统Informix为例，介绍改善用户查询计划的方法。
1．合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
2．避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
●索引中不包括一个或几个待排序的列；
●group by或order by子句中列的次序与索引的次序不一样；
●排序的列来自不同的表。
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
3．消除对大型表行数据的顺序存取
在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。
4．避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
5．避免困难的正规表达式
MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。
另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3]>“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。
6．使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
INTO TEMP cust_with_balance
然后以下面的方式在临时表中查询：
SELECT ＊ FROM cust_with_balance
WHERE postcode>“98000”
临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。
注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。
7．用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。
有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。

大数据量实时统计排序分页查询 优化总结

大数据量实时统计排序分页查询 (并发数较小时) 的瓶颈不是函数（count，sum等）执行，

不是having, 也不是order by，甚至不是表join, 导致慢的原因就在于“数据量太大本身”

就是将表划分为M份相互独立的部分,可以是分表，也可以是不分表但冗余一个取模结果字段

实际结果是不分表比分表更加灵活，只需稍加配置，就可以动态切分大表，随意更改M的大小。

将1条慢sql（大于30秒）拆分成为N条查询速度巨快的sql(单条sql执行时间控制在20毫秒以内)

然后再web应用中以适当的线程数去并发查询这些执行时间快的N条小sql再汇总结果

第一步查询中去并发执行这N条小sql, 只取排序字段和标识字段，其他字段一律丢弃

汇总结果后定位出当前页面要显示的pageNum条数据，再进行第二步查询，取出页面上需要展示的所有字段

PS：这一点是至关重要的，其他几点都可以不看，这点是最关键的。慢慢解释一下：

a) 第一种方式是把数据库中所有记录（只取排序字段和标识字段并且不做任何sum，count having order by等操作）

全部拉到web应用中，在web应用中完成所有的计算

b) 第二种方式是把数据库中所有记录做sum count having等操作之后的所有行数拉到web应用中，在web应用中完成剩余计算

c) 第三种方式是把数据库中所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中，

在web应用中对limit后的数据再计算

显然，第一种方式 数据库什么活都不做只取数据 是不可行的。以lg_order_count_seller为例，1500万行，

如果只算id, seller_id和order_count 这三个bigint类型，至少需要拉8*3*1500 0000 = 360000000=340M,

拉到内存中之后存储需要8*4*15000000= 460M,这还不算List是的2的n次方这个特点和计算排序等的内存开销，

不仅数据库与web应用机器IO扛不住，就是应用自身恐怕也要OOM了。

第二种方式，所有记录做sum count having等操作之后,由于是group by seller_id的，总得数据量变为100万（就是卖家总数），

这样子一来，共需要拉8*3*100 0000 = 23M,拉到内存之后，需要8*4*100 0000 = 30M, 再算上List是的2的n次方这个特点和

计算排序等的内存开销也不会超过100M, IO的时间和内存开销勉强可以考虑接受。

第三种方式，所有记录做sum count having order by等操作之后把limit后的数据拉到web应用中，因为做了limit，所以，

数据量很小了，无论是IO还是内存开销都已经很小了。可以忽略。

综合以上三种，第三种方式适用于页面的前n页和后n页，因为这个limit的数据量随着页数的增大而增大，

当大到每个切分后的小表的数据量时就转为第二种方式了。

第二种方式适用于页面的第[n+1, totaoPageNum-n]页。

切分成N条小sql后并行执行时排序不稳定性的解决办法

① 问题描述：

优化之前，还是是一条大慢sql查询时，由于数据库排序是稳定排序，

所以当两条记录排序字段值相同时他们在页面上的页码位置是固定的。

优化之后，当并行执行这N条小sql时，由于无法控制这些小sql的先后执行顺序，

导致在web应用中当两条记录的排序字段值相同时在页面上的页码位置是随机的。

② 解决办法：

除了拉标识字段(seller_id)和排序字段(order_count_sum)之外，再取一个unique（id）的字段，当两条记录的排序字段值相同时，再用这个unique的字段（在卖家监控中这个字段是id）进行第二次排序.这样就解决了排序不稳定的问题。

③ 也许，看到这里会有疑问，为什么不用seller_id？seller_id也是唯一， 这样子不是少取id这个字段，减少IO了？

seller_id虽然也是唯一，可以辅助排序，但是不要忘记数据库的排序规则是：

如果两列的值相等，那么序号在前的排在前面，这里的序号就是主键(自动生成，autoincrement),

如果用seller_id的话还是不能保证排序的稳定性，只能用主键id.

优先加载页面上的主要元素，然后再去异步加载次要元素，

反应在卖家监控页面中，查数据和查页页码的sql语句基本相同，是在竞争同一资源，

所以，需要做一个策略，优先把资源让给查数，数据查完之后再去查页码。

限流

由于多线程取数据并没有从本质上提高数据库性能，所以必须针对大数据量实时统计排序分页查询做限流

我这里打个比方：食堂有6个窗口，物流团队吃饭要买6个菜，平均每买1个菜需要1分钟的时间，

如果派我一个人去一个窗口买的话需要6分钟的时间

假如派6个人分别去6个窗口买这6个菜，只需要1分钟的时间

但是，如果除了物流团队，再来其他5个团队呢，也就是说6个团队每个团队买6个菜共买36个菜，

这样子有的团队先买完，有的团队后买完，但平均时间还是6分钟。本质上没有变化。

所以，对于特定的查询条件，必须进行限流。让每分钟至多有6个团队买菜，这样子能使得情况变得不至于太糟糕。

从根本上改变现状

这一点从目前来看只能是展望了，比如mysql数据库换更为强大的oracle数据库，

或更换InnoDb引擎为其他，或更换SATA硬盘为SSD 。。。。。。

从实践效果来看，优化后的效果是很明显的。

相同的查询条件，原来一个页面查询时间由于超过60秒超时了，根据1-6点建议优化之后，查询时间变为2秒至3.5秒之间。

mysql百万数据分页查询4秒，求教怎么优化

查询指定的记录最好通过Id进行in查询来获得真实的数据.其实不是最好而是必须，也就是你应该先查询出复合的ID列表,通过in查询来获得数据
我们来做一个测试ipdatas表：
CREATE TABLE `ipdatas` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`uid` INT(8) NOT NULL DEFAULT '0',
`ipaddress` VARCHAR(50) NOT NULL,
`source` VARCHAR(255) DEFAULT NULL,
`track` VARCHAR(255) DEFAULT NULL,
`entrance` VARCHAR(255) DEFAULT NULL,
`createdtime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`createddate` DATE NOT NULL DEFAULT '0000-00-00',
PRIMARY KEY (`id`),
KEY `uid` (`uid`)
) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;
这是我们做的广告联盟的推广ip数据记录表，由于我也不是mysql的DBA所以这里咱们仅仅是测试
因为原来里面有大概7015291条数据
这里我们通过jdbc的batch插入6000万条数据到此表当中“JDBC插入6000W条数据用时:9999297ms”；
大概用了两个多小时,这里面我用的是batch大小大概在1w多每次提交,还有一点是每次提交的数据都很小，而且这里用的myisam数据表，因为我需要知道mysql数据库的大小以及索引数据的大小结果是
ipdatas.MYD 3.99 GB (4,288,979,008 字节)
ipdatas.MYI 1.28 GB (1,377,600,512 字节)
这里面我要说的是如果真的是大数据如果时间需要索引还是最好改成数字字段,索引的大小和查询速度都比时间字段可观。
步入正题:
1.全表搜索
返回结构是67015297条数据
SELECT COUNT(id) FROM ipdatas;
SELECT COUNT(uid) FROM ipdatas;
SELECT COUNT(*) FROM ipdatas;
首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的最大条数
查询索引条件
SELECT COUNT(*) FROM ipdatas WHERE uid=1; 返回结果时间:2分31秒594
SELECT COUNT(id) FROM ipdatas WHERE uid=1; 返回结果时间:1分29秒609
SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回结果时间:2分41秒813
第二次查询都比较快因为mysql中是有缓存区的所以增大缓存区的大小可以解决很多查询的优化，真可谓缓存无处不在啊在程序开发中也是层层都是缓存
查询数据
第一条开始查询
SELECT * FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒
SELECT * FROM ipdatas LIMIT 1,10 ; 15ms
很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。
我们先从一个常用但性能很差的查询来看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的问题在于offset(分页偏移量)很大的时候，像下面这样：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。
对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
这个语句耗时20.02sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。
下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。
高效的计算行数
如果采用的引擎是MyISAM，可以直接执行COUNT(*)去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。
我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些操作导致缓存失效时，执行下面的语句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
获取记录
下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。
如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
现在想获取任意一页的元素就很简单了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;
还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下来就可以向下面一样执行分页查询了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;
简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

db2分页技术优化问题：

select * from (select 字段1,字段2,字段3,字段4,字段5,rownumber() over(order by 排序字段 asc ) as rowid from 表名 )as a where a.rowid >= startPage AND a.rowid
<endpage
和 fetch first 20 rows only 没有关系

fetch first 20 rows only 是提取前20条数据的不能作为分页来使用。

分页有三种：

1、 如果一定要使用fetch first 20 rows only 的方式，可以使用WITH AS 方式实现:

-------------------------------------------------

WITH TMP AS(

select * from (

select *,rownumber() over(order by id asc ) as row_id from table_name

) where row_id > (pagenumber-1)*pagesize

)

SELECT TMP.* FROM TMP WHERE fetch first pagesize rows only

-------------------------------------------------

2、比较常见的SQL分页，可以使用沈阳java网友的写法：

-----------------------------------------------------

select * from (

select *,rownumber() over(order by id asc ) as rowid from table_name

)as a

where a.rowid >= (pagenumber-1)*pagesize+1 AND a.rowid <= pagenumber*pagesize

-------------------------------------------------------

3、使用PageHelper分页插件，也可以实现自动分页：

使用方法

--------------------------------------------------------------

a.引配置

-------------------------------------------------------------

b.使用：

LogInfoQuery example = new LogInfoQuery();

example.setOrderByClause(" id desc ");

Criteria criteria = example.createCriteria();

if(null != logInfo.getStartTimeQuery() && null != logInfo.getEndTimeQuery()) {

criteria.andOpTimeBetween(logInfo.getStartTimeQuery(), logInfo.getEndTimeQuery());

}

PageHelper.startPage(page.getPageNo(), page.getPageSize());

List

result = logInfoMapper.selectByExample(example);

PageInfo

pageInfo = new PageInfo<>(result);

return pageInfo;

-------------------------------------------

使用示例结束。

如果不懂可以追问。

</endpage

mysql分页优化问题，主键ID并非自增的整形

添加 添加约束： alter table table-name add index index-name （column[dataName]）
自动自增 1，2, 3.。。。。

Oracle 数据量非常大（上亿）时，使用存储过程中的游标返回分页查询的10条记录非常耗时，请问如何优化？

select * /*+ FIRST_ROWS */　from XXX where XXX
提高SQL语句的响应时间，快速的先返回 n 行。
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

MySQL的limit 优化

mysql 5.7.28

按id增序 导出t_order_detail表数据，由于数据量过多，防止一次查询数据量大多导致异常，批量查询数据，每次查询200条数据，数据量50万，查询出的数据量5万多条。

SQL如下

Explain结果

《高性能MySql第三版》章节6.7.5 优化Limit分页中提到，在偏移量非常大的时候，例如可能是LIMIT 1000,20 这样的查询，这时候MySQL需要查询10020条记录然后只返回最后20条，前面10000条记录都将被抛弃，这样的代价非常高。要优化此种查询，要么在页面中限制分页数量，要么是优化大偏移量的性能。使用“延迟关联”，它让MySQL扫描尽可能少的页面，获取需要要访问的记录后再根据关联列回原表查询需要的所有列。

Explain结果

也没看不出来区别，直接用SQL执行看消耗的时间

这个延迟关联蛮简单的(自我感觉)，为啥MySQL不直接内部实现优化呢？

延迟关联到底节省了哪部分动作消耗的时间，如果只是如下的SQL,那就根本没必要关联，在查询了其他的字段后，才需要延迟关联。所以是节省了获取其他字段的消耗的时间？还是排序时多个字段后更加耗时？

当前SQL使用id排序，可以直接使用上一页数据最后一条数据的Id做筛选，这样直接筛选出需要的数据，查询查第49999条数据的order_id为707352，SQL如下

Explain结果

此种优化方法要求 使用唯一的字段排序。

高性能MySql
MySQL ORDER BY _ LIMIT performance_ late row lookups at EXPLAIN EXTENDED