MYSQL分页limit速度太慢优化方法

MySQL 百万级分页优化(Mysql千万级快速分页)（转）

MYSQL分页limit速度太慢优化方法

MYSQL分页limit速度太慢的优化方法

如何优化Mysql千万级快速分页,limit优化快速分页,MySQL处理千万级数据查询的优化方案！（zz）

MySQL 百万级分页优化(Mysql千万级快速分页)

MySQL大数据量分页性能优化

mysql 正则表达式查询含有非数字和字符的记录

MYSQL分页limit速度太慢优化方法

问题原因

limit10000,20 的意思扫描满足条件的 10020 行，扔掉前面的 10000 行，返回最后的 20 行，问题就在这里

原理

利用表的覆盖索引来加速分页查询
我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外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）, 最后是后匹配效率高, 例如: 你%
  • - 可以表示单个字符