背景说明：

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

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

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

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

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

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

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

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

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

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在数据量大的情况下分页起点越大，查询速度越慢，

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

比如(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;

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

为什么呢？

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外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 语句的优化和索引时非常重要的！

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

40岁老架构师尼恩提示

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

但是面试，是一个需要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；

三.索引类军规合理使用索引

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

如：不要给性别创建索引

`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);

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

如：用独立于业务的AUTO_INCREMENT

四.SQL类军规sql语句尽可能简单

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

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

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

针对不同字段 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 10000,10 改为 where id >xxxx limit 11

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

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

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

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

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

40岁老架构师尼恩提示

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

另附SQL优化的小技巧

前面聊了个SQL 分页 查询 调优的面试题，接着来聊一聊本文的核心：SQL优化，所谓的高手和普通人之间，最大的不同在于能将相同的事情做到更好，比如送外卖，相同的时间内一个人能够送的更多，这是个送外卖的高手。比如玩游戏，相同的角色和装备，一个人的战绩能够更出色，那这是个打游戏的高手......。

上述的道理放在编程中同样适用，一个人代码敲得更快、代码敲的更多、执行效率越高，这也可以被称为是一个写代码的高手，俗称“码农Pro Max”，那作为一个普通码农，如何达到“码农Pro、码农Plus、码农Pro Max.....”的境界呢？首先你得能够写出一手好SQL！

掌握了写SQL的基本功后，足以让你写代码的效率提升，但引言中就聊到过：写的快不代表写的好，就算你能够日码三万行，并且还能满足业务需求，这也不见得的能被称之为高手，真正的SQL高手除开编写效率够高之外，对于每条SQL的执行效率也要可控。如果写的多，但有些业务SQL在大数据的情况下，一跑就是十多秒，这是万万不可的！

那么问题又来了：如何让自己的SQL又快又好呢？答案其实非常简单，减小查询的数据量、提升SQL的索引命中率即可，接着先来说说撰写SQL时的一些注意点。

2.1、编写SQL时的注意点

在写SQL的时候，往往很多时候的细节不注意，就有可能导致索引失效，也因此会造成额外的资源开销，而我们要做的就是避开一些误区，确保自己的SQL在执行过程中能够最大程度上节省资源、缩短执行时间，下面罗列一些经典的SQL注意点。

2.1.1、查询时尽量不要使用*

一般在写SQL为了方便，所以通常会采用*来代替所有字段，毕竟用*号只要按键盘一下，写字段则需要一个个字段名去写。写*的确能让程序员更省力，但对机器就不太友好了，因此在写查询语句时一律不要使用*代替所有字段，这条准则相信大家都知道，但到底是为什么呢？

其实主要有如下几方面的原因：

①分析成本变高。

在《SQL执行篇》中聊过，一条SQL在执行前都会经过分析器解析，当使用*时，解析器需要先去解析出当前要查询的表上*表示哪些字段，因此会额外增加解析成本。但如果明确写出了查询字段，分析器则不会有这一步解析*的开销。

②网络开销变大。

当使用*时，查询时每条数据会返回所有字段值，然后这些查询出的数据会先被放到结果集中，最终查询完成后会统一返回给客户端，但线上Java程序和MySQL都是分机器部署的，所以返回数据时需要经过网络传输，而由于返回的是所有字段数据，因此网络数据包的体积就会变大，从而导致占用的网络带宽变高，影响数据传输的性能和资源开销。但实际上可能仅需要用到其中的某几个字段值，所以写清楚字段后查询，能让网络数据包体积变小，从而减小资源消耗、提升响应速度。

③内存占用变高。

在《MySQL内存篇》中曾详细讲到了InnoDB引擎的工作原理，当查询一条数据时都会将其结果集放入到BufferPool的数据缓冲页中，如果每次用*来查询数据，查到的结果集自然会更大，占用的内存也会越大，单个结果集的数据越大，整个内存缓冲池中能存下的数据也就越少，当其他SQL操作时，在内存中找不到数据，又会去触发磁盘IO，最终导致MySQL整体性能下降。

④维护性变差。

用过MyBatis框架的小伙伴应该都知道一点，一般为了对应查询结果与实体对象的关系，通常都需要配置resultMap来声明表字段和对象属性的映射关系，但如果每次使用*来查询数据，当表结构发生变更时，就算变更的字段结构在当前业务中用不到，也需要去维护已经配置好的resultMap，所以会导致维护性变差。但声明了需要的字段时，配置的resultMap和查询字段相同，因此当变更的表结构不会影响当前业务时，也无需变更当前的resultMap。

综上所述，使用*的情况下反而会带来一系列弊端，所以能显示写明所需字段的情况下，尽量写明所需字段，除开上述原因外，还有一点最关键的原因：基于非主键字段查询可能会产生回表现象，如果是基于联合索引查询数据，需要的结果字段在联合索引中有时，可能通过索引覆盖原理去读数据，从而减少一次回表查询。但使用*查询所有字段数据时，由于联合索引中没有完整数据，因此只能做一次回表从聚簇索引中拿数据，对于索引覆盖感兴趣的可参考之前的《索引应用篇-索引覆盖机制》。

2.1.2、连表查询时尽量不要关联太多表

对于这点的原因其实很简单，一旦关联太多的表，就会导致执行效率变慢，执行时间变长，原因如下：

数据量会随表数量呈直线性增长，数据量越大检索效率越低。当关联的表数量过多时，无法控制好索引的匹配，涉及的表越多，索引不可控风险越大。

一般来说，交互型的业务中，关联的表数量应当控制在5张表之内，而后台型的业务由于不考虑用户体验感，有时候业务比较复杂，又需要关联十多张表做查询，此时可以这么干，但按照《高性能MySQL》上的推荐，最好也要控制在16~18张表之内（阿里开发规范中要求控制在3张表以内）。

2.1.3、多表查询时一定要以小驱大

所谓的以小驱大即是指用小的数据集去驱动大的数据集，说简单一点就是先查小表，再用小表的结果去大表中检索数据，其实在MySQL的优化器也会有驱动表的优化，当执行多表联查时，MySQL的关联算法为Nest Loop Join，该算法会依照驱动表的结果集作为循环基础数据，然后通过该结果集中一条条数据，作为过滤条件去下一个表中查询数据，最后合并结果得到最终数据集，MySQL优化器选择驱动表的逻辑如下：

①如果指定了连接条件，满足查询条件的小数据表作为驱动表。②如果未指定连接条件，数据总行数少的表作为驱动表。

如果在做连表查询时，你不清楚具体用谁作为驱动表，哪张表去join哪张表，这时可以交给MySQL优化器自己选择，但有时候优化器不一定能够选择正确，因此写SQL时最好自己去选择驱动表，小表放前，大表放后！

举个例子感受一下两者之间的区别，假设zz_student学生表中有10000条数据，zz_class班级表中有100条数据，当需要关联这两张表查询数据时，SQL如下：

-- 大表在前，小表在后select * from zz_student as s left join zz_class as c on s.class_id = c.class_id;-- 小表在前，大表在后select * from zz_class as c left join zz_student as s on c.class_id = s.class_id;复制代码

上述是两种联查的SQL语法，如果学生表在前作为驱动表，根据Nest Loop Join算法会循环一万次查询数据，而反之如果班级表在前，则只需要循环100次即可查询出数据，因此诸位在写SQL时一定要记得将小表作为驱动表。

这个道理不仅仅只存在于多表关联查询中，只要涉及到多表查询的情况，都需遵循该原则，比如使用子查询进行多表查询时，请确保结果集小的SQL先执行。

举个子查询的小表驱动大表的例子：

select * from xxx where yyy in (select yyy from zzz where ....);复制代码

MySQL在执行上述这条SQL时，会先去执行in后面的子查询语句，这时尽量要保证子查询的结果集小于in前面主查询的结果集，这样能够在一定程度上减少检索的数据量。通常使用in做子查询时，都要确保in的条件位于所有条件的最后面，这样能够在最大程度上减小多表查询的数据匹配量，如下：

- 优化前：select xxx,xxx,xxx from table where colum in(sql) and id = 10;- 优化后：select xxx,xxx,xxx from table where id = 10 and colum in(sql);复制代码

以小驱大这个规则也可以进一步演化，也就是当查询多张表数据时，如果有多个字段可以连接查询，记得使用and来拼接多个联查条件，因为条件越精准，匹配的数据量就越少，查询速度自然会越快。

对于单表查询时也是如此，比如要对数据做分组过滤，可以先用where过滤掉一部分不需要的数据后，再对处理后的数据做分组排序，因为分组前的数据量越小，分组时的性能会更好！

可以把SQL当成一个链式处理器，每一次新的子查询、关联查询、条件处理....等情况时，都可以看成一道道的工序，我们在写SQL时要注意的是：在下一道工序开始前尽量缩小数据量，为下一道工序尽可能提供更加精准的数据。

2.1.4、不要使用like左模糊和全模糊查询

对于这点的原因十分明显，因为在之前《索引应用篇-索引失效场景》中聊到过，如若like关键字以%号开头会导致索引失效，从而导致SQL触发全表查询，因此需要使用模糊查询时，千万要避免%xxx、%xxx%这两种情况出现，实在需要使用这两类模糊查询时，可以适当建立全文索引来代替，数据量较大时可以使用ES、Solr....这类搜索引擎来代替。

2.1.5、查询时尽量不要对字段做空值判断

select * from xxx where yyy is null;select * from xxx where yyy not is null;复制代码

当出现基于字段做空值判断的情况时，会导致索引失效，因为判断null的情况不会走索引，因此切记要避免这样的情况，一般在设计字段结构的时候，请使用not null来定义字段，同时如果想为空的字段，可以设计一个0、""这类空字符代替，一方面要查询空值时可通过查询空字符的方式走索引检索，同时也能避免MyBatis注入对象属性时触发空指针异常。

2.1.6、不要在条件查询=前对字段做任何运算

select * from zz_users where user_id * 2 = 8;select * from zz_users where trim(user_name) = "熊猫";复制代码

zz_users用户表中user_id、user_name字段上都创建了索引，但上述这类情况都不会走索引，因为MySQL优化器在生成执行计划时，发现这些=前面涉及到了逻辑运算，因此就不会继续往下走了，会将具体的运算工作留到执行时完成，也正是由于优化器没有继续往下走，因此不会为运算完成后的字段选择索引，最终导致索引失效走全表查询。

从这里可以得出一点，千万不要在条件查询的=前，对字段做任何运算，包括了函数的使用也不允许，因为经过运算处理后的字段会变成一个具体的值，而并非字段了，所以压根无法使用到索引！

2.1.7、 !=、!<>、not in、not like、or...要慎用

这点可参考《索引应用篇-索引失效场景》中给出的示例，简单来说就是这类写法也可能导致索引失效，因此在实际过程中可以使用其他的一些语法代替，比如or可以使用union all来代替：

select user_name from zz_users where user_id=1 or user_id=2;-- 可以替换成：select user_name from zz_users where user_id=1union allselect user_name from zz_users where user_id=2;复制代码

虽然这样看起来SQL变长了，但实际情况中查询效率反而更高一些，因为后面的SQL可以走索引（对于其他的一些关键字也一样，可以使用走索引的SQL来代替这些关键字实现）。

2.1.8、必要情况下可以强制指定索引

在表中存在多个索引时，有些复杂SQL的情况下，或者在存储过程中，必要时可强制指定某条查询语句走某个索引，因为MySQL优化器面对存储过程、复杂SQL时并没有那么智能，有时可能选择的索引并不是最好的，这时我们可以通过force index，如下：

select * from zz_users force index(unite_index) where user_name = "熊猫";复制代码

这样就能够100%强制这条SQL走某个索引查询数据，但这种强制指定索引的方式，一定要建立在对索引结构足够熟悉的情况下，否则效果会适得其反。

2.1.10、避免频繁创建、销毁临时表

临时表是一种数据缓存，对于一些常用的查询结果可以为其建立临时表，这样后续要查询时可以直接基于临时表来获取数据，MySQL默认会在内存中开辟一块临时表数据的存放空间，所以走临时表查询数据是直接基于内存的，速度会比走磁盘检索快上很多倍。但一定要切记一点，只有对于经常查询的数据才对其建立临时表，不要盲目的去无限制创建，否则频繁的创建、销毁会对MySQL造成不小的负担。

2.1.11、尽量将大事务拆分为小事务执行

经过之前《MySQL事务机制》、《MySQL锁机制》、《MySQL事务与锁实现原理》这几章的学习后，咱们应该会知道：一个事务在执行事，如果其中包含了写操作，会先获取锁再执行，直到事务结束后MySQL才会释放锁。

而一个事务占有锁之后，会导致其他要操作相同数据的事务被阻塞，如果当一个事务比较大时，会导致一部分数据的锁定周期较长，在高并发情况下会引起大量事务出现阻塞，从而最终拖垮整个MySQL系统。

show status like 'innodb_log_waits';查看是否有大事务由于redo_log_buffer不足，而在等待写入日志。

大事务也会导致日志写入时出现阻塞，这种情况下会强制触发刷盘机制，大事务的日志需要阻塞到有足够的空间时，才能继续写入日志到缓冲区，这也可能会引起线上出现阻塞。

因此基于上述原因，在面对一个较大的事务时，能走异步处理的可以拆分成异步执行，能拆分成小事务的则拆成小事务，这样可以在很大程度上减小大事务引起的阻塞。

2.1.12、从业务设计层面减少大量数据返回的情况

之前在做项目开发时碰到过一些奇葩需求，就是要求一次性将所有数据全部返回，而后在前端去做筛选展现，这样做虽然也可以，但如果一次性返回的数据量过于巨大时，就会引起网络阻塞、内存占用过高、资源开销过大的各类问题出现，因此如果项目中存在这类业务，一定要记住拆分掉它，比如分批返回给客户端。

分批查询的方式也被称之为增量查询，每次基于上次返回数据的界限，再一次读取一批数据返回给客户端，这也就是经典的分页场景，通过分页的思想能够提升单次查询的速度，以及避免大数据量带来的一系列后患问题。

2.1.13、尽量避免深分页的情况出现

前面刚刚聊过分页，分页虽然比较好，但也依旧存在问题，也就是深分页问题，如下：

select xx,xx,xx from yyy limit 100000,10; 复制代码

上述这条SQL相当于查询第1W页数据，在MySQL的实际执行过程中，首先会查询出100010条数据，然后丢弃掉前面的10W条数据，将最后的10条数据返回，这个过程无异极其浪费资源。

哪面对于这种深分页的情况该如何处理呢？有两种情况。

如果查询出的结果集，存在递增且连续的字段，可以基于有序字段来进一步做筛选后再获取分页数据，如下：

select xx,xx,xx from yyy where 有序字段 >= nnn limit 10; 复制代码

也就是说这种分页方案是基于递增且连续字段来控制页数的，如下：

-- 第一页select xx,xx,xx from yyy where 有序字段 >= 1 limit 10; -- 第二页select xx,xx,xx from yyy where 有序字段 >= 11 limit 10; -- 第N页.....-- 第10000页select xx,xx,xx from yyy where 有序字段 >= 100001 limit 10; 复制代码

这种情况下，MySQL就会先按where条件筛选到数据之后，再获取前十条数据返回，甚至还可以通过between做优化：

select xx,xx,xx from yyy where 有序字段 between 1000000 and 1000010; 复制代码

这种方式就完全舍弃了limit关键字来实现分页，但这种方式仅适合于基于递增且连续字段分页。

那么例如搜索分页呢？这种分页情况是无序的，因为搜索到的数据可以位于表中的任意行，所以搜索出的数据中，就算存在有序字段，也不会是连续的，这该如何是好？这种情况下就只能在业务上限制深分页的情况出现了，以百度为例：

虽然搜索mysql关键字之后，显示大约搜索到了一亿条数据，但当咱们把分页往后拉就会发现，最大只能显示76页，当你再尝试往后翻页时就会看到一个提示：“限于网页篇幅，部分结果未予显示”。

上述百度的这个例子中，就从根源上隔绝了深分页的出现，毕竟你都没给用户提供接下来的分页按钮了，这时自然也就无法根据用户操作生成深分页的SQL。

但上述这种思想仅局限于业务允许的情况下，以搜索为例，一般用户最多看前面30页，如果还未找到他需要的内容，基本上就会换个更精准的关键词重新搜索。

哪如果业务必须要求展现所有分页数据，此时又不存在递增的连续字段咋办？哪这种情况下要么选择之前哪种很慢的分页方式，要么就直接抛弃所有！每次随机十条数据出来给用户，如果不想重复的话，每次新的分页时，再对随机过的数据加个标识即可。

2.1.14、SQL务必要写完整，不要使用缩写法

很多开发者，包含我在内，往往都喜欢缩写语法，能够简写的绝不写全，比如：

-- 为字段取别名的简单写法select user_name "姓名" from zz_users;-- 为字段取别名的完整写法select user_name as "姓名" from zz_users;-- 内连表查询的简单写法select * from 表1,表2... where 表1.字段 = 表2.字段 ...; -- 内连表查询的完整写法select * from 表1 别名1 inner join 表2 别名2 on 别名1.字段 = 别名2.字段;......复制代码

这类情况下还有很多，在写的时候为了图简单，都会将一些能简写的SQL就简写，但其实这种做法也略微有些问题，因为隐式的这种写法，在MySQL底层都需要做一次转换，将其转换为完整的写法，因此简写的SQL会比完整的SQL多一步转化过程，如果你考虑极致程度的优化，也切记将SQL写成完整的语法。

2.1.15、基于联合索引查询时请务必确保字段的顺序性

在之前聊到过《联合索引的最左前缀原则》，想要基于建立的联合索引查询数据，就必须要按照索引字段的顺序去查询数据，否则可能导致所以完全利用联合索引，虽然MySQL8.0版本中推出了《索引跳跃扫描机制》，但这种方案也会存在较大的开销，同时还有很强的局限性，所以最好在写SQL时，依旧遵循索引的最左前缀原则撰写。

2.1.16、客户端的一些操作可以批量化完成

批量新增某些数据、批量修改某些数据的状态.....，这类需求在一个项目中也比较常见，一般的做法如下：

for (xxObject obj : xxObjs) {    xxDao.insert(obj);}/** * xxDao.insert(obj)对应的SQL如下： * insert into tb_xxx values(......);**/复制代码

这种情况确实可以实现批量插入的效果，但是每次都需要往MySQL发送SQL语句，这其中自然会带来额外的网络开销以及耗时，因此上述实现可以更改为如下：

xxDao.insertBatch(xxObjs);/** * xxDao.insertBatch(xxObjs)对应的SQL如下： * insert into tb_xxx values(......),(......),(......),(......),.....;**/复制代码

这样会组合成一条SQL发送给MySQL执行，能够在很大程度上节省网络资源的开销，提升批量操作的执行效率。

这样的方式同样适用于修改场景，如果一个业务会出现批量修改的情况时，也切记不要用for循环来调用update语句对应的接口，而是应该再写一个update/replace语句的批量修改接口。

2.1.17、明确仅返回一条数据的语句可以使用limit 1

select * from zz_users where user_name = "竹子";select * from zz_users where user_name = "竹子" limit 1;复制代码

上述这两条SQL语句都是根据姓名查询一条数据，但后者大多数情况下会比前者好，因为加上limit 1关键字后，当程序匹配到一条数据时就会停止扫描，如果不加的情况下会将所有数据都扫描一次。所以一般情况下，如果确定了只需要查询一条数据，就可以加上limit 1提升性能。

但在一些极端情况下，性能可能相差不大，比如要查询的数据位于表/索引文件的最后面，那么依旧会全部扫描一次。还有一种情况是基于主键/唯一索引字段查询数据时，由于这些字段值本身具备唯一性，因此MySQL在执行时，当匹配到第一个值时就会自动停止扫描，因此上述这个方案只适用于普通索引字段、或表中的普通字段。

2.2、SQL优化的业内标准

评判任何一件事情到底有没有做好都会有标准，而SQL语句的执行时间也一样，业内也早有了相应的标准，相信大家一定都听说过下述这个用户体验原则：

客户端访问时，能够在1s内得到响应，用户会觉得系统响应很快，体验非常好。

客户端访问时，1~3秒内得到响应，处于可以接受的阶段，其体验感还算不错。

客户端访问时，需要等待3~5秒时才可响应，这是用户就感觉比较慢了，体验有点糟糕。

客户端访问时，一旦响应超过5秒，用户体验感特别糟糕，通常会选择离开或刷新重试。

上述这四条是用户体验感的四个等级，一般针对于C端业务而言，基本上都需要将接口响应速度控制到第二等级，即最差也要三秒内给用户返回响应，否则会导致体验感极差，从而让用户对产品留下不好的印象。

所谓的三秒原则通常是基于C端业务而言的，对于B端业务来说，通常用户的容忍度会高一些，也包括B端业务的业务逻辑会比C端更为复杂一些，所以可将响应速度控制到第三等级，也就是5s内能够得到响应。针对于一些特殊类型的业务，如后台计算型的业务，好比跑批对账、定时调度....等，这类因为本身业务就特殊，因此可不关注其响应速度。

回归前面的用户三秒体验原则，似乎三秒也不难做到对嘛？基本上SQL语句在1~3秒内都能执行完成呀，但请牢记：这个三秒并不能全部分配给SQL执行，为什么呢？因为用户感受到的响应速度会由多方面的耗时组成，如下：

从上图观察中可得知，所谓给用户的响应时间其实会包含各方面的耗时，也就是这所有的过程加一块儿，必须要在1~3s内给出响应，而SQL耗时属于「系统耗时→数据操作耗时」这部分，因此留给SQL语句执行的时间最多只能有500ms，一般在用户量较大的门户网站中，甚至要求控制在10ms、30ms、50ms以内