背景说明：

Mysql调优，是大家日常常见的调优工作。所以Mysql调优是一个非常、非常核心的面试知识点。

在30岁老架构师 的读者交流群(50+)中，其相关面试题是一个非常、非常高频的交流话题。

近段时间，有小伙伴面试网易，说遇到一个SQL 深度分页 查询 调优的面试题：

MySQL 百万级数据，怎么做分页查询？说说你的思路？

社群中，还遇到过大概的变种：

形式1：如何解决Mysql深分页问题？

形式2：mysql如何实现高效分页

形式3：后面的变种，应该有很多变种........，会收入 《Java面试宝典》。

这里给大家 调优，做一下系统化、体系化的梳理，使得大家可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”。

也一并把这个 SQL 深度分页 题目以及参考答案，收入咱们的《尼恩Java面试宝典》，供后面的小伙伴参考，提升大家的 3高 架构、设计、开发水平。

注：本文以 PDF 持续更新，最新Java 架构笔记、面试题 的PDF文件，请后台私信【笔记】获取哦！

深挖问题：MySQL分页起点越大查询速度越慢

在数据库开发过程中我们经常会使用分页，核心技术是使用用limit start, count分页语句进行数据的读取。

我们分别看下从10， 1000， 10000， 100000开始分页的执行时间（每页取20条）。

select * from product limit 10, 20        0.002秒select * from product limit 1000, 20      0.011秒select * from product limit 10000, 20     0.027秒select * from product limit 100000, 20    0.057秒

我们已经看出随着起始记录的增加，时间也随着增大，

这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为100w看下：

select * from product limit 1000000, 20   0.682秒

我们惊讶的发现MySQL在数据量大的情况下分页起点越大，查询速度越慢，

300万条起的查询速度已经需要1.368秒钟。

这是为什么呢?

因为limit 3000000,10的语法实际上是mysql扫描到前3000020条数据, 之后丢弃前面的3000000行，

这个步骤其实是浪费掉的。

select * from product limit 3000000, 20   1.368秒

从中我们也能总结出两件事情：

limit语句的查询时间与起始记录的位置成正比mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。基础知识：mysql中limit的用法

语法：

SELECT * FROM 表名 limit m,n;SELECT * FROM table LIMIT [offset,] rows;

注释：Limit子句可以被用于强制 SELECT 语句返回指定的记录数。

Limit接受一个或两个数字参数,参数必须是一个整数常量。

如果给定两个参数，

第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

1.m代表从m+1条记录行开始检索，n代表取出n条数据。(m可设为0)

如：SELECT * FROM 表名 limit 6,5;

表示：从第7条记录行开始算，取出5条数据

2.值得注意的是，n可以被设置为-1，当n为-1时，表示从m+1行开始检索，直到取出最后一条数据。

如：SELECT * FROM 表名 limit 6,-1;

表示：取出第6条记录行以后的所有数据。

3.若只给出m，则表示从第1条记录行开始算一共取出m条

如：SELECT * FROM 表名 limit 6;

以年龄倒序后取出前3行：

mysql> select * from student order by age desc;+-----+--------+------+------+| SNO | SNAME  | AGE  | SEX  |+-----+--------+------+------+| 1   | 换换   |   23 | 男   || 2   | 刘丽   |   22 | 女   || 5   | 张友   |   22 | 男   || 6   | 刘力   |   22 | 男   || 4   | NULL   |   10 | NULL |+-----+--------+------+------+5 rows in set (0.00 sec)mysql> select * from student order by age desc limit 3;+-----+--------+------+------+| SNO | SNAME  | AGE  | SEX  |+-----+--------+------+------+| 1   | 换换   |   23 | 男   || 2   | 刘丽   |   22 | 女   || 6   | 刘力   |   22 | 男   |+-----+--------+------+------+3 rows in set (0.00 sec)

跳过前3行后再2取行.

mysql> select * from student order by age desc limit 3,2;+-----+--------+------+------+| SNO | SNAME  | AGE  | SEX  |+-----+--------+------+------+| 6   | 刘力   |   22 | 男   || 4   | NULL   |   10 | NULL |+-----+--------+------+------+

回到问题：MySQL百万级数据大分页查询优化

我们惊讶的发现MySQL在数据量大的情况下分页起点越大，查询速度越慢，

300万条起的查询速度已经需要1.368秒钟。

那么，该如何优化呢？方法1: 直接使用数据库提供的SQL语句

语句样式：

MySQL中,可用如下方法:

SELECT * FROM 表名称 LIMIT start, count

功能

Limit限制的是从结果集的start 位置处取出count 条输出,其余抛弃.

原因/缺点：

全表扫描,速度会很慢

而且， 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3).

适应场景：

适用于数据量较少的情况

元祖数量、记录数量级别：百/千级

方法2:建立主键或唯一索引, 利用索引(假设每页10条)

语句样式：

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M

除了主键，也可以 利用唯一键索引快速定位部分元组，避免全表扫描

比如: 读第1000到1019行元组(pk是唯一键).

SELECT * FROM 表名称 WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

原因 ：

索引扫描,速度会很快.

缺点：

如果数据查询出来并不是按照pk_id排序，并且pk_id全部数据都存在没有缺失可以作为序号使用，不然，分页会有漏掉数据，

适应场景 ：

适用于数据量多的情况(元组数上万)id数据没有缺失，可以作为序号使用方法3: 基于索引再排序

语句样式 ：

MySQL中,可用如下方法:

SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

适应场景：

适用于数据量多的情况(元组数上万).

最好ORDER BY后的列对象是主键或唯一索引,

id数据没有缺失，可以作为序号使用

使得ORDERBY操作能利用索引被消除但结果集是稳定的

原因：

索引扫描,速度会很快.

但MySQL的排序操作,只有ASC没有DESC

mysql中，索引存储的排序方式是ASC的，没有DESC的索引。

这就能够理解为啥order by 默认是按照ASC来排序的了吧

虽然索引是ASC的，但是也可以反向进行检索，就相当于DESC了

方法4: 基于索引使用prepare

语句样式：MySQL中,可用如下方法:

PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？*10) ORDER BY id_pk ASC LIMIT M

第一个问号表示pageNum

适应场景：

大数据量

原因：

索引扫描，速度会很快.

prepare语句又比一般的查询语句快一点。

方法5: 利用"子查询+索引"快速定位元组

利用"子查询+索引"快速定位元组的位置,然后再读取元组.

比如(id是主键/唯一键)

利用子查询示例:

SELECT * FROM your_table WHERE id <= (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize

方法6: 利用"连接+索引"快速定位元组的位置,然后再读取元组.

比如(id是主键/唯一键,蓝色字体时变量)

利用连接示例:

SELECT * FROM your_table AS t1 JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

方法7: 利用表的索引覆盖来调优

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

为什么呢？

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

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

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

select id from product limit 866613, 20      0.2秒

如果查询了所有列的37.44秒，这里只要0.2秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是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

查询时间也很短！

方法8：利用复合索引进行优化

假设数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。

现在往里面填充数据，填充10万篇新闻。

最后collect 为 10万条记录，数据库表占用硬1.6G。

看下面这条sql语句：

select id,title from collect limit 1000,10;

很快；基本上0.01秒就OK，再看下面的

select id,title from collect limit 90000,10;

从9万条开始分页，结果？

8-9秒完成，my god 哪出问题了？

看下面一条语句:

select id from collect order by id limit 90000,10;

很快，0.04秒就OK。

为什么？

因为用了id主键做索引, 这里实现了索引覆盖（方法7），当然快。

所以，可以按照方法7进行优化，具体如下：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

再看下面的语句，带上where

select id from collect where vtype=1 order by id limit 90000,10; 

很慢，用了8-9秒！

注意：vtype 做了索引了啊？怎么会慢呢？vtype做了索引是不错，如果直接对vtype进行过滤，比如

select id from collect where vtype=1 limit 1000,10;

是很快的，基本上0.05秒，可是提高90倍，从9万开始，那就是0.05*90=4.5秒的速度了。

和测试结果8-9秒到了一个数量级。

其实加了where 就不走索引，这样做还是全表扫描，解决的办法是：复合索引！

加一个复合索引， search_index(vtype,id) 这样的索引。

然后测试

select id from collect where vtype=1 limit 90000,10;

非常快！0.04秒完成！再测试:

select id ,title from collect where vtype=1 limit 90000,10;

非常遗憾，8-9秒，没走search_index 复合索引，不是索引覆盖！

综上：

如果对于有where 条件，又想走索引用limit的，

必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

按这样的逻辑，百万级的limit 在0.0x秒就可以分完。完美解决了分页问题了。

看来mysql 语句的优化和索引时非常重要的！

像这种分页最大的页码页显然这种时间是无法忍受的。

30岁老架构师尼恩提示

回答到了这里，已经接近满分了

但是面试，是一个需要120分的活儿

怎么得到120分呢？

可以告诉面试官：如何 提升SQL的性能， 还是要从 表设计、索引设计、SQL设计等全方位解决，具体请看MySQL数据库开发的三十六条军规

接下来，就给面试官介绍一下，MySQL数据库开发的三十六条军规

MySQL数据库开发的三十六条军规一.核心军规尽量不在数据库做运算，cpu计算的事务必移至业务层;控制表、行、列数量（【控制单张表的数据量 1年/500W条，超出可做分表】，【单库表数据量不超过300张】 、【单张表的字段个数不超过50个，多了拆表】）三大范式没有绝对的要使用，效率优先时可适当牺牲范式拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);二.字段类军规用好数值类型(用合适的字段类型节约空间);

如：一个字段注定就只有1跟2 要设计成 int(1) 而不是 int(11)字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);

如，一个字段注定就只有1跟2，要设计成int(1) 而不是char(1) 查询优化如：字段类型是 char(1) 查询应当where xx='1' 而不是 xx=1 会导致效率慢避免使用NULL字段

NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效;

如： 要设计成  `c` int(10) NOT NULL DEFAULT 0    而不是  `c` int(10) NOT NULL；

少用text/blob类型(尽量使用varchar代替text字段), 需要请拆表不在数据库存图片，请存图片路径，然后图片文件存在项目文件夹下。三.索引类军规合理使用索引

改善查询,减慢更新,索引一定不是越多越好;

如：不要给性别创建索引

字符字段必须建前缀索引;

`pinyin` varchar(100) DEFAULT NULL COMMENT '小区拼音', KEY `idx_pinyin` (`pinyin`(8)),

不在索引做列运算;

如：WHERE to_days(current_date) – to_days(date_col) <= 10   改为：WHERE date_col >= DATE_SUB('2011-10- 22',INTERVAL 10 DAY);

innodb主键推荐使用自增列

主键建立聚簇索引,主键不应该被修改,字符串不应该做主键

如：用独立于业务的AUTO_INCREMENT

不用外键(由程序保证约束);四.SQL类军规sql语句尽可能简单

一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库;  

简单的事务;避免使用trig/func(触发器、函数不用，由客户端程序取而代之);不用select *(消耗cpu,io,内存,带宽,这种程序不具有扩展性);

如：select  a ,b,c 会比 select * 好 只取需要列

OR改写为 IN

如：where  a=1 or a=2  改  a in(1,2)

OR改写为UNION

针对不同字段 where a=1 or b=1 改：select 1 from a where a=1  union select 1 from a where b=1

避免负向%;

如：where a like %北京%  改为  where a like '北京%'

limit高效分页(limit越大，效率越低);

如：Limit 10000,10 改为 where id >xxxx limit 11

使用union all替代union(union有去重开销);高并发db少用2个表以上的join;使用group by 去除排序加快效率;

如：group by name   默认是asc排序改：group by name  order by null 提高查询效率

请使用同类型比较;

如：where 双精度=双精度  数字=数字  字符=字符  避免转换导致索引丢失

打散大批量更新;

如：在凌晨空闲时期更新执行

五.约定类军规隔离线上线下 ，

如：开发用dev库，测试用qa库，模拟用sim库， 线上用线上库，开发无线上库操作权限

不在程序端加锁，即外部锁，外部锁不可控，会导致 高并发会炸，极难调试和排查统一字符 UTF-8 校对规则 utf8_general_ci 出现乱码 SET NAMES UTF8统一命名规范，库表名一律小写，索引前缀用idx_ 库名 用缩写（2-7字符），不使用系统关键字保留字命名30岁老架构师尼恩提示

问题回答到这里，已经20分钟过去了，面试官已经爱到 “不能自已、口水直流” 啦。

注：本文以 PDF 持续更新，最新Java 架构笔记、面试题 的PDF文件，请后台私信【笔记】获取哦！