书签

首先获取符合条件的记录的最大 id和最小id(默认id是主键)

select max(id) as maxid ,min(id) as minid 
  from t where kid=2333 and type=1;

根据id 大于最小值或者小于最大值进行遍历。

select xx,xx from t where kid=2333 and type=1 
  and id >=min_id order by id asc limit 100;
select xx,xx from t where kid=2333 and type=1 
  and id <=max_id order by id desc limit 100;

案例

当遇到延迟关联也不能满足查询速度的要求时

SELECT a.id as id, client_id, admin_id, kdt_id, type, token, created_time, update_time, is_valid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = 'xxx' and is_valid = '1' order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;

100 rows in set (0.51 sec)

使用延迟关联查询数据510ms ，使用基于书签模式的解决方法减少到10ms以内 绝对是一个质的飞跃。

SELECT * FROM t1 where client_id='xxxxx' and is_valid=1 and id<47399727 order by id desc LIMIT 100;

100 rows in set (0.00 sec)

小结

根据主键定位数据的方式直接定位到主键起始位点，然后过滤所需要的数据。

相对比延迟关联的速度更快，查找数据时少了二级索引扫描。但优化方法没有银弹，比如：

order by id desc 和 order by asc 的结果相差70ms ，生产上的案例有limit 100 相差1.3s ，这是为啥？

还有其他优化方式，比如在使用不到组合索引的全部索引列进行覆盖索引扫描的时候使用 ICP 的方式 也能够加快大分页查询。

子查询优化

先定位偏移位置的 id，然后往后查询，适于 id 递增场景：

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;
select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;
select * from orders_history where type=8 limit 100000,100;

4条语句的查询时间如下：

第1条语句：3674ms
第2条语句：1315ms
第3条语句：1327ms
第4条语句：3710ms

• 1 V.S 2：select id 代替 select *，速度快3倍
• 2 V.S 3：速度相差不大
• 3 V.S 4：得益于 select id 速度增加，3的查询速度快了3倍

这种方式相较于原始一般的查询方法，将会增快数倍。

使用 id 限定优化

假设数据表的id是连续递增，则根据查询的页数和查询的记录数可以算出查询的id的范围，可使用 id between and：

select *
from order_history
where c = 2
  and id between 1000000 and 1000100
limit 100;

查询时间：

15ms
12ms
9ms

这能够极大地优化查询速度，基本能够在几十毫秒之内完成。

限制是只能使用于明确知道id。

另一种写法：

select *
from order_history
where id >= 1000001
limit 100;

还可以使用 in，这种方式经常用在多表关联时进行查询，使用其他表查询的id集合，来进行查询：

select *
from order_history
where id in
      (select order_id from trade_2 where goods = 'pen')
limit 100;

临时表

已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

数据表的id

一般在DB建立表时，强制为每一张表添加 id 递增字段，方便查询。

像订单库等数据量很大，一般会分库分表。这时不推荐使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器，并在数据表中使用另外的字段来存储这个唯一标识。

先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *。

参考

• https://segmentfault.com/a/1190000038856674