mysql limit 很慢_MYSQL分页limit速度太慢优化方法

利用表的覆盖索引来加速分页查询

我们都知道，利用了索引查询的语句中如果只包含了那个索引列(覆盖索引)，那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们之间查询最后一页的数据(利用覆盖索引，只包含id列)，如下：

select id from product limit 866613, 20 0.2秒

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20 查询时间为0.2秒，简直是一个质的飞跃啊，哈哈

另一种写法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id 查询时间也很短，赞！

优化方案

我是用了先找 id 再进行 where。

select id from table order by id limit 90000,10;【因为id是索引所以很快】

SELECT * FROM table WHERE id IN(10000, 100000, 1000000...);

复合索引

加复合索引, search(vtype,id)

如果对于有 where 条件，又想走索引用limit的，必须设计一个索引，将 where 放第一位，limit 用到的主键放第2位，而且只能 select 主键！

子查询优化法

先找出第一条数据，然后大于等于这条数据的id就是要获取的数据

缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性。

记录之前数据的最大ID【不太靠谱， 当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。】

倒排表优化法

倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据

缺点：只适合数据数固定的情况，数据不能删除，维护页表困难

反向查找优化法

当偏移超过一半记录数的时候，先用排序，这样偏移就反转了

缺点：order by优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数

limit限制优化法

把limit偏移量限制低于某个数。。超过这个数等于没数据，我记得alibaba的dba说过他们是这样做的

正则【mysql 中正则表达式使用 regexp 关键字】

^ 匹配字符开始的部分

eg1: 从info表name字段中查询以L开头的记录select * from info where name regexp '^L';

eg2: 从info表name字段中查询以aaa开头的记录select * from info where name regexp '^aaa';

$ 匹配字符结束的部分

eg1: 从info表name字段中查询以c结尾的记录select * from info where name regexp 'c$';

eg2: 从info表name字段中查询以aaa结尾的记录select * from info where name regexp 'aaa$';

. 匹配字符串中的任意一个字符，包括回车和换行

eg1: 从info表name字段中查询以L开头y结尾中间有两个任意字符的记录select * from info where name regexp '^L..y$';

[字符集合] 匹配字符集合中的任意字符

eg1: 从info表name字段中查询包含c、e、o三个字母中任意一个的记录select * from info where name regexp '[ceo]';

eg2: 从info表name字段中查询包含数字的记录select * from info where name regexp '[0-9]';

eg3: 从info表name字段中查询包含数字或a、b、c三个字母中任意一个的记录select * from info where name regexp '[0-9a-c]';

[^字符集合]匹配除了字符集合外的任意字符

eg1: 从info表name字段中查询包含a-w字母和数字以外字符的记录select * from info where name regexp '[^a-w0-9]';

s1|s2|s3 匹配s1s2s3中的任意一个, | 为正则表达式的 OR 操作符

eg1: 从info表name字段中查询包含'ic'的记录select * from info where name regexp 'ic';

eg2: 从info表name字段中查询包含ic、uc、ab三个字符串中任意一个的记录select * from info where name regexp 'ic|uc|ab';

* 代表多个该字符前的字符，包括0个或1个

eg1: 从info表name字段中查询c之前出现过a的记录select * from info where name regexp 'a*c';

+ 代表多个该字符前的字符，包括1个

eg1: 从info表name字段中查询c之前出现过a的记录select * from info where name regexp 'a+c';(注意比较结果！)

字符串{N} 字符串出现N次

eg1: 从info表name字段中查询出现过a3次的记录select * from　info where name regexp 'a{3}';

字符串 {M，N} 字符串最少出现M次，最多出现N次

eg1: 从info表name字段中查询ab出现最少1次最多3次的记录select * from info where name regexp 'ab{1,3}';

Mysql正则大小写都会匹配，为区分大小写可使用 binary 关键字，如：

`SELECT * FROM a1 WHERE name LIKE binary` `'%J%'` `#使用LIKE+通配符匹配大写J`

`SELECT * FROM a1 WHERE name regexp binary` `'j'` `#使用正则匹配小写j`

MYSQL中自带通配符(LIKE 关键词)

% 可以表示任意长度的字符(包括 0), 最后是后匹配效率高, 例如: 你%

- 可以表示单个字符

参考文章：https://www.jianshu.com/p/0a7e3055a01f