MYSQL分页limit速度太慢优化方法
问题原因
当进行分页时，MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行。例如 limit 10000, 20。mysql排序取出10020条数据后，仅返回20条数据，查询和排序的代价都很高。那当 offset 特别大的时候，效率就非常的低下，所以我们要对sql进行改写。
原理
利用表的覆盖索引来加速分页查询
我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。
因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外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 查询时间也很短，赞！
优化方案
使用书签
用书签记录上次取数据的位置，过滤掉部分数据
如下面语句
SELECT id, name, description FROM film ORDER BY name LIMIT 1000, 10;
可以改为
SELECT id, name, description FROM film WHERE name > 'begin' ORDER BY name LIMIT 10;
name为上次分页后的最大值，注意这种场景只适用于不存在重复值的场景。
延迟关联
延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据
SELECT id, name, description FROM film ORDER BY name LIMIT 100,5;
id是主键值，name上面有索引。这样每次查询的时候，会先从name索引列上找到id值，然后回表，查询到所有的数据。可以看到有很多回表其实是没有必要的。完全可以先从name索引上找到id（注意只查询id是不会回表的，因为非聚集索引上包含的值为索引列值和主键值，相当于从索引上能拿到所有的列值，就没必要再回表了），然后再关联一次表，获取所有的数据
因此可以改为
SELECT film.id, name, description FROM film JOIN (SELECT id from film ORDER BY name LIMIT 100,5) temp ON film.id = temp.id
倒序查询
假如查询倒数最后一页，offset可能回非常大
SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10;
改成倒序分页，效率是不是快多了？
SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10;
正则【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
https://blog.csdn.net/zzti_erlie/article/details/105902012
