一. 面试题及剖析

1. 今日面试题

Limit 20000加载很慢怎么解决？
查询很慢什么原因造成的？解决方案?
如何定位慢SQL？
如何查询十万条数据?

2. 题目剖析

壹哥 在上一篇文章中，给大家仔细分析了索引的内容，相信各位可以从中了解到索引的作用、优缺点、索引的原理及创建等内容， 壹哥 希望大家一定要牢牢掌握索引的相关内容，因为这关系到数据库的优化。我们今天的内容，主要是分析导致查询很慢的原因，这些原因中，有的就和索引失效有关系。当然，导致查询缓慢的原因，不止是因为索引的关系，还有其他一些因素。所以壹哥就再通过今天的文章，来分析这些因素，以及对应的解决方案。

二. Limit 20000加载很慢的问题

壹哥 在前面的文章中，详细的讲解过SELECT查询关键词的执行顺序，其中就有关于limit关键词的介绍，我们知道该关键词是用于分页查询的。如果你对该关键词不熟悉，可以参考之前的内容：
高薪程序员&面试题精讲系列81之说一下SQL查询语句的执行顺序详解-作用篇(上)
高薪程序员&面试题精讲系列82之说一下SQL查询语句的执行顺序详解-原理篇(下)

1. limit存在的问题

在之前的文章中， 壹哥 就给大家讲解过limit的作用及存在的问题。也就是我们可以使用limit来实现分页效果，对于少量数据，使用LIMIT没什么问题；但 当数据量非常大时，使用 LIMIT n, m 是非常低效的，这是因为LIMIT每次都是从头开始扫描。 所以如果需要从第10万行开始，读取5条数据，就需要先定位到第10万行，然后再进行分页读取。这是一个非常低效的扫描过程，因为每次数据库要去扫描 N + M 条记录，都要放弃之前的 N 条记录，开销很大。
现在回到我们的问题上来，即：

select * from table limit 20000,10;

这里 limit10000,10 会先扫描满足条件的20010行，扔掉前面的20000行，返回最后的10行。问题就在这里，你只要10条数据，却还得扫描那些前面没用的20000条，这不是严重的浪费吗？ 这能不慢吗？那该怎么解决这个问题呢？我们继续往下看。

2. 解决思路

既然limit分页存在以上的问题，那该怎么解决呢？ 壹哥 给大家提供的解决思路如下，供大家参考：

前端加缓存 ： 通过缓存，减少对数据库的直接查询操作，可以通过redis实现缓存，或者通过ElasticSearch搭建搜索系统进行实现； 使用延迟关联+子查询 ： 即先利用 limit 得到需要数据的索引字段，然后再通过原表和索引字段关联获得需要数据，比如下面这样的SQL语句：select a.* from table_a a,(select id from table_b limit 100000,20) b where a.id = b.id； 记录上次查询的最大id ：如果id是连续的，我们可以把每次查询出的最大id记录下来，比如下面这样的SQL语句：select id,name from users where id>100020 order by id asc limit 20；在这条SQL语句中我们增加了order by，并且要给order by字段建立索引； 使用join进行查询 ： 我们也可以把SQL语句改变一下，使用join进行查询，这个执行效率也比直接查询快得多，如下：SELECT * FROM tableName AS t1 JOIN (SELECT id FROM tableName ORDERBY id desc LIMIT 20000,10) AS t2 WHERE t1.id <= t2.id ORDERBY t1.id desc LIMIT 10； 从业务上实现 ： 比如不做过多的分页，只分前 100 页，后面不允许再分页查询了； 不使用 limit N,M ： 可以使用 limit N，将 offset 转化为 where 条件。

当然，以上只是 壹哥 给大家提供的解决思路，如果大家还有更好的解决思路，可以在评论区留言，咱们大家互相交流学习。

三. SQL查询日志

壹哥 在之前说过，数据库里的大多数操作，其实都是查询操作，而不是增删改。如果我们数据库中的数据量不是很大，一般的查询语句都是可以满足需求的，但当数据量很大时，不同查询语句的执行性能却可能会差别很大。所以为了提高数据库的查询性能，我们就要仔细分析我们所写的SQL查询语句，找出执行很慢的SQL查询语句，对其进行改造，从而提高查询性能。接下来 壹哥 就针对此问题进行分析，找出导致SQL变慢的原因，并给出解决办法。

1. 简介

要想解决慢SQL，首先我们得先了解一下啥是慢SQL。其实看名字你也能猜出来，肯定是查询很慢的SQL呗。但问题又来了，这个”快慢“就不是一种固定的标准，属于相对概念，也就是说到底多慢才算慢呢？总不能 壹哥 说它慢它就慢吧。其实在MySQL中已经给我们制定了一个大致的标准。
MySQL作为一种数据库系统，已经把很多方面都考虑到了，它本身提供了一种慢查询的日志记录，用来记录MySQL中超过特定阈值的SQL语句。 只要某个SELECT语句的执行时间超过了这个日志中long_query_time规定的值，我们就可以把该SELECT语句看做是慢SQL，然后该语句就会被记录到慢查询日志中。 默认情况下， long_query_time 的值是 10秒钟 ，也就是说，只要一条SQL的执行时间超过10秒，就是慢SQL。当然，这是MySQL官方给出的参考值，我们可以根#据自己的项目要求进行更改。

2. 查看慢SQL阈值

那慢SQL的阈值到底是不是10秒中呢？我们可以通过如下语句进行查看：

#查询慢sql时间设设置的值show variables like 'long%';

执行结果如下图所示：

3. 更改慢SQL阈值

在默认情况下，MySQL是没有开启慢SQL查询日志功能的，所以如果我们想检测慢SQL，需要先开启该功能。我们先检查一下当前是否开启了慢SQL日志功能，如下：

#展示慢sql的开启状态和日志文件的存放位置show variables like 'slow%';

执行结果如下所示：

可以看到，这里默认没有开启日志查询功能，所以我们就需要先开启该功能，语句如下：

#开启查询日志功能set global slow_query_log = 1;

但大家要注意，这里我们开启的慢查询日志，只是对当前数据库生效，在重启数据库后就失效了。如果我们需要永久生效，需要修改MySQL数据库的配置文件my.cnf。

[mysqld]#开启全局的查询日志功能slow_query_log=1slow_query_log_file=E:\mysql-5.7.26-winx64\data\YYG39C2-slow.log#设置全局的日志查询阈值时间long_query_time=3log_output=FILE

设置之后，记得重启mysql服务器。在开启了查询日志功能之后，我们就可以查看有多少条语句被加入到了慢SQL查询日志中了。

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4. 更改慢查询时间阈值

壹哥 在上面说过，慢SQL是有个时间阈值的，默认是10秒，我们可以修改该值，我们可以使用如下SQL语句来进行修改：

#修改慢查询时间阈值set global long_query_time=3

当然也可以在my.conf文件中进行全局修改配置，如上面第3小节所写！

5. 开启未使用索引的查询语句日志记录功能

我们要想提高查询效率，很多时候都要使用索引，但某个查询语句，到底有没有使用索引，有时候我们也不知道，那有没有办法知道呢？所以MySQL为了解决这个问题，也搞了一个记录日志，可以将未使用索引的查询语句记录到该日志中。所以我们这里也可以开启这个选项方便后面进行SQL语句调优。这样开启该参数后，full index scan的SQL语句也会被记录到慢查询日志中。

# 查看是否开启了该功能show variables like 'log_queries_not_using_indexes';# 开启索引日志记录开关set global log_queries_not_using_indexes=1

四. 慢SQL

在上面的章节中， 壹哥 给大家讲解了SQL日志记录的相关内容，接下来我们就要分析为什么要优化慢SQL，以及慢SQL是怎么产生及解决办法了。

1. 产生原理

有的小伙伴会说，有慢SQL就有呗，让它在那待着不好吗？那当然是不好了，为什么呢？
这是因为我们执行的每个SQL语句都要消耗一定的I/O资源，SQL语句执行的快慢就决定了I/O资源被占用时间的长短。假设我们总的资源只有100个，有一条慢SQL占用了30个资源，花费了1分钟，那么在这1分钟时间内，其他SQL能够分配的资源总量就只有70个。这样当资源不够分配时，其他新的SQL语句要想执行，就只能排队等待。而且一条慢SQL执行时间较长，也就意味着会产生较长的阻塞等待，用户的使用体验也较差。
所以我们就需要对慢SQL进行治理优化，那慢SQL又是怎么产生的呢？看看 壹哥 下面给大家总结的几条常见原因吧。

2. 产生原因及解决思路

导致数据库操作执行缓慢的原因并不是唯一的，常见的原因大致如下：

SQL语句问题； 数据库及表锁定；服务器硬件配置低；MySQL本身有故障；其他原因

但对我们一般的程序员来说，MySQL优化时可以先不用考虑数据库的配置参数以及硬件等因素，这些更多是DBA的职责，我们主要去考虑某条SQL语句是否有问题，以及该语句执行时所占用的I/O和CPU资源情况。一般来说，影响资源占用的因素主要是：

表中的数据量越大，需要的I/O次数越多；
读取数据的方式：缓存、索引、直接磁盘读取；
数据加工方式：数据有没有进行排序、子查询等。

而对于我们程序员来说，MySQL资源占用过多、SQL语句执行缓慢的解决思路大致就是：

将数据适当进行缓存 ：当数据量不大，变化频率不高，但访问频率很高时，考虑将数据存放在Redis等缓存中； 适当合并I/O ：分别执行select c1 from t1与select c2 from t1，与执行select c1,c2 from t1相比，后者开销更小； 合理进行分布式架构 ：在处理海量数据时，考虑将数据和I/O分散到多台主机上执行。

这里 壹哥 给大家分析了导致SQL执行缓慢的常见原因及解决思路，今天 壹哥 不会讲解如何实现缓存和分布式架构。因为对于一般的开发来说，导致数据库缓慢的最常见原因还是我们编写的SQL查询语句有问题，比如索引失效了、过多的表关联查询、单表数据量过大等。所以 壹哥 今天主要针对SQL语句导致的查询缓慢现象，给大家提供一些解决办法。

3. 预防慢SQL

本篇文章主要讲解针对SQL语句不当造成的慢SQL，如果各位能把这些内容都说清楚，这次面试基本也就通过了。
《黄帝内经》中说， ”上医治未病，中医治欲病，下医治已病“ ，放到我们的开发中来同样如此，预防故障的产生才是最关键的。所以既然是因为SQL查询语句导致的慢SQL，我们首先就要想办法优化SQL语句，提高查询效率。只要SQL语句写得好，自然就可以预防慢SQL产生了，接下来 壹哥 首先就把一些有效的SQL优化技巧告诉给大家。

3.1 避免索引失效

壹哥 在上一篇文章中给大家讲过，要想提高我们的查询效率，使用索引是一个很有效的手段，所以我们就要建立索引，并确保索引不失效。如果你没看过 壹哥 的上一篇文章，请左拐往前翻：
《MySQL有哪些索引？索引原理你熟悉吗？索引什么情况下会失效？》
在上一篇文章中， 壹哥 跟大家讲过，我们要确保索引不失效，这样我们的查询语句执行速度才能真正快起来，所以那些可能会导致索引失效的操作都要避免，如下所示：

l ike %会使索引失效； *通配符会 使 索引失效； NOT、!=、<>、NOT IN、NOT LIKE等负向条件会使普通索引失效； 索引列参与计算(+、-、*、/等操作)会使索引失效； 对索引列字段使用函数或会使索引失效； 对字段进行null值判断会使索引失效； 使用or连接会使索引失效； 违反最左前缀原则会使组合索引失效； 数据类型不一致会使索引失效； > < 范围查询不当会使索引失效； order by使用不当会使索引失效；

以上这些操作，我们要尽量避免，这样就避免了索引失效，SQL查询的速度自然就可以提升了。

3.2 优化表结构设计

我们的查询效率高不高，还得看我们设计的表结构是否良好，一个设计很差的表，查询是不可能快的。在我们设计表时，尽量满足如下特点：

在满足需求的情况下，数据类型尽量选择使用小的类型；尽量使用 tinyint、smallint、mediumint 作为整数类型，而非 int；尽可能使用 not null 定义字段，因为 null 会占用 4 个字节。数字类型默认为 0 ，字符串默认为 ""；尽量少用 text 类型，非用不可时最好独立出一张表；尽量使用 timestamp，而非 datetime；单表不要有太多字段，建议在 20 个字段以内。

3.3 其他注意事项

用 exists 代替 in，比如select num from a where exists(select y from b where num=a.num)；避免频繁创建和删除临时表，以减少系统表资源的消耗；用 join 代替 in，join的效率要高于使用in，因为join不需要建立临时表；尽量避免大事务操作，提高系统并发能力；优化分页查询：参考 壹哥 前面对limit关键字的优化内容；其他注意事项

4. 定位解决慢SQL

如果我们的预防工作没做好，还是导致产生了慢SQL，那也不要怕，想办法解决就行了。

4.1 治理优先级

当我们要治理慢SQL时，如果有很多地方都产生了慢SQL，而我们的时间精力又有限，此时该怎么办？我们也不要眉毛胡子一把抓，还是要有所侧重的。一般来说，在定位解决慢SQL时，应当遵循如下的治理优先级原则：

如果存在主从分离，则先解决master主库，再解决slave从库； 执行次数多的SQL优先治理； 如果某个SQL语句会高并发地访问某一张表，应当优先治理。

4.2 查看慢查询日志

首先我们要确认到底是哪里产生了慢SQL，然后对症下药，找到问题产生的地方，才能想办法解决。 壹哥 在第三章节中，已经给大家讲解了如何开启SQL的查询日志功能，大家往前翻一下。因为必要的慢SQL查询日志我在前面已经讲过，这里我就直接查询到底产生了多少个慢SQL语句，如下：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

执行结果如下所示：

如果没有产生慢SQL，Slow_queries的value值就是0。当然，如果有慢SQL，value就是对应的条数了。
接下来我们可以写入mysql slow语句来测试是否写入slow数据：

mysql> select sleep(10) as a, 1 as b;

执行结果如下所示：

然后我们可以在mysql数据库的data目录中，打开之前配置的mysql-slow.log日志文件，内容如下：

我们可以看到，现在slow.log日志文件，就已经成功的把慢SQL记录了下来，这样以后其他执行超过3秒钟的SQL语句都会被记录到这个日志文件中，我们就可以通过分析该日志文件来排查慢SQL了。
我们除了可以使用以上方式定位慢SQL之外，还可以利用第三方工具，比如 APM 来进行慢SQL语句的监控，这里 壹哥 不再展开讲解。

4.3 安装ActivePerl

因为 壹哥 是在windows环境中给大家讲解的面试题，现在我们要分析慢SQL，除了开启慢SQL查询日志的功能之外，我们还需要用到另一个MySQL自带的工具--mysqldumpslow.pl，该命令是在MySQL的bin目录下，如下图所示：

mysqldumpslow.pl是一个perl编写的脚本文件，默认情况下windows是无法执行的。我们需要先安装ActivePerl，这是一个可以让你任意执行 Perl 程序的工具软件，所以我们要先下载并安装ActivePerl工具。至于ActivePerl的下载安装过程，大家自行百度即可，就是傻瓜式安装，没有什么难度，安装完毕后就可以执行mysqldumpslow.pl脚本命令了。

4.4 mysqldumpslow.pl命令

mysqldumpslow命令的常用参数如下：

--help 输出帮助信息；-v 输出详细信息；-d 调试； -s 按照什么排序，默认是'at'，显示顺序为倒序； al: 平均锁表时间；ar: 平均结果行数；at: 平均查询时间；c: 次数；l: 锁表时间； r: 总结果行数； t: 总查询时间；-r 正序排序，即从小到大排序；-t NUM 限制显示的条数；-a 显示出数字和字符串，默认数字为 N 字符串为 'S'；-g PATTERN 过滤字符串，后接正则表达式，如'10$' 以10为结尾的条件。

壹哥 在这里执行如下命令：

#查询得到记录集最多的10个SQL#该命令后面跟着自己之前配置的慢SQL日志文件位置即可mysqldumpslow.pl -s r -t 10 ..\data\YYG39C2-slow.log

执行结果如下图所示：

我们也可以使用如下命令：

#得到访问次数最多的10个SQLmysqldumpslow.pl -s c -t 10 slow.log#得到按照时间排序的前10条里面含有左连接的查询语句mysqldumpslow.pl -s t -t 10 -g "left join" slow.log

这样通过以上的工具执行，我们就能知道现在有多少条和哪几条慢SQL了，这为我们后续的慢SQL分析打下了基础。

4.5 show profile

我们还可以使用另一个工具--Show Profile，这是MySQL提供的用来分析某个SQL语句执行时所消耗资源情况的命令，可以为SQL语句的调优提供量化指标。但该命令默认是关闭的，我们需要手动开启该功能，开启后可以保存查询SQL语句在服务器中的执行细节和生命周期。
4.5.1 查看show profile开启状况

SHOW VARIABLES LIKE 'profiling';

执行结果如下：

我们可以看到，默认该功能是没有开启的。
4.5.2 开启show profile功能
接下来我们先开启show profile功能，方便后面的SQL语句分析。

SET profiling=ON;

这样就开启了show profile功能，如果再次查看开启状态，就可以看到profiling=ON了。
4.5.3 查看SQL执行历史记录
接下来我们可以使用如下命令来查看profiles情况。

show profiles;

该命令可以把我们之前执行过的SQL语句都记录下来，比如下面这样的执行结果：

从执行结果中，我们可以看到有多个Query_ID，我们先查出来这里的ID值，接下来就可以使用这些ID值进一步进行查询分析。
4.5.4 诊断SQL
利用上面查询出来的Query_ID，接下来我们诊断SQL语句所消耗的资源情况。

show profile cpu,block io for query 上面查询出来的某个Query_ID值;

我们可以在上面的语句中，添加执行如下不同的命令：

ALL：显示所有开销信息；
BLOCK IO：显示IO相关开销；
CONTEXT SWITCHES：显示上下文切换相关开销；
CPU：显示CPU相关开销；
IPC：显示发送接收相关开销；
MEMORY：显示内存相关开销；
PAGE FAULTS：显示页面错误相关开销；
SOURCE：显示和Source_function，Source_file，Source_line相关开销；
SWAPS：显示交换次数相关开销

例如我们可以把上面Query_ID=39的SQL语句分析一下，如下所示：

执行上述命令时，如果我们遇到以下这几种情况就需要考虑进行优化：

Converting HEAP to MyISAM： 查询结果太大，内存不够用； Creating tmp table：创建临时表； Copying to tmp table on disk：将内存中的临时表复制到磁盘； locked：有锁定。

4.6 explain分析慢SQL语句

这样通过上面的一系列步骤，我们就可以知道有多少条慢SQL，以及是哪些慢SQL了。接下来我们可以利用另一个explain命令，来进一步分析，看看到底是什么原因导致了慢SQL的产生。explain可以模拟优化器执行SQL查询语句的情况，从而知道MySQL是如何处理SQL语句的，进而分析出查询语句或表结构的性能瓶颈。通过expalin命令我们可以可以得到如下信息：

表的读取顺序； 表的读取操作的操作类型； 哪些索引可以使用； 哪些索引被实际使用； 表之间的引用； 每张表有多少行被优化器查询

这样通过explain，我们就能知道是不是命中了索引，是不是产生了临时表等。关于explain关键字的使用详情，在我的线下课程中讲解的非常细致了， 壹哥 在这里先不做详细解释，我会在下一篇文章中专门讲解。

5. MySQL服务器参数调优

如果经过以上措施，SQL语句的执行效果还是不够理想，比如当order by 和 group by无法使用索引时，可以考虑增大MySQL服务器的max_length_for_sort_data和sort_buffer_size等参数设置。

六. 结语

以上就是我们针对慢SQL而采取的各种预防及解决措施，但因为MySQL优化本身就是一个非常庞大而复杂的工作，仅靠一篇文章，我们也无法完全将相关内容讲解清楚，这里我们主要是为了应付面试而进行的核心内容梳理。文中若有不当之处，还请各位不吝赐教！
至此， 壹哥 就把导致SQL查询变慢的原因及解决办法给详细地讲解了，现在你会了吗？如果你还有不明白的地方，可以在评论区给 壹哥 留言哦。原创不易，如果你觉得本文不错，可以给 壹哥 点个赞哦，你们的支持是我继续创作的动力！
作者：一一哥Sun

游戏编程 ️，一个游戏开发收藏夹~

如果图片长时间未显示，请使用Chrome内核浏览器。