MySQL面试：查询性能的优化方法

OceanStar的学习笔记

已于 2022-01-21 18:13:27 修改

阅读量2.3k

点赞数 1

分类专栏：数据库文章标签： mysql 面试数据库

于 2021-10-19 23:38:33 首次发布

原文链接：https://blog.csdn.net/AbstractLiu/article/details/106995896

版权

数据库专栏收录该内容

289 篇文章 17 订阅

订阅专栏

实践中如何优化MySQL?

四条从效果上第一条影响最大，后面越来越小。

SQL语句以及索引的优化
数据库表结构的优化
系统配置的优化
硬件优化

查询性能的优化方法

减少请求的数据量

只返回必要的列：最好不要使用 SELECT * 语句。
只返回必要的行：使用Limit语句来限制返回的数据
缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别是要查询的数据经常被重复查询时，缓存提升将会是非常明显的。

减少服务端扫描的行数

最有效的方式是使用索引来覆盖查询

SQL语句的优化

（1）对查询进行优化，应该尽量避免全表扫描，首先应该拷贝在where以及order by涉及的列上建立索引

（2）应尽量避免在where子句中使用!=操作符，否则将引擎放弃使用索引而进行全表扫描。

（3）应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值，然后这样查询：

select id from t where num=0

（4）应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or nun=20

可以这样查询：

select id from t where num=10
union all
select id from t where num=20

（5）in和not in也要慎用，否则会导致全表扫描，如：

select id from t where num in（l,2, 3；

对于连续的数值，能用between就不要用in 了：

select id from t where num between 1 and 3

很多时候用exists代替in是一个好的选择，比如

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists (select 1 from b where num=a. num)

（6）如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但是优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with (index (索引名)：where num=@num

（7）应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为：

select id from t where num=100*2

（8）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

（9）不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

（10）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

（11）不要写一些没有意义的查询，如需要生成一个空表结构：

select coll, col2 into #t from t wheie 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table St (...)

（12）并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex, male、female 几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

（13）索引并不是越多越好，索引固然可以提高相应的select的效率，但是也降低了insert和update的效率，因为insert和update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

（14）应该尽可能的避免更新聚簇索引数据列，因为聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会消耗相当大的资源。如果应用需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为非聚簇索引类

（15）尽量使用数字型字段，如果只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储的开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要一次比较就足够了

（16）尽可能的使用varchar/nvarchar代替char/nchar ,因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

（17）任何地方都不要使用select * from t ,用具体的字段列表代替“*” ,不要返回用不到的任何字段。

（18）尽量使用表变量来替代临时表，如果表变量包含大量数据，请注意索引非常有限（只有主键索引）

（19）避免频繁创建和删除临时表，以减少系统表资源的消耗。

（20）在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替 create table,避免造成大量log ,以提高速度；如果数据量不大，为了緩和系统表的资源，应先create table,然后inserto

（21）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ,然后drop table ,这样可以避免系统表的较长时间锁定。

（22）尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

（23）使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

（24）与临时表一样，游标并不是不可使用。对小型数据集使用FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

（25）在所有的存储过程和触发器的开始处设置SET NOCOUOT ON ,在结束时设置SET NOCOUOT OFF。无需在执行存儲过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

（27）尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

（29）尽量避免大事务操作，提高系统并发能力。

查询性能

查询的生命周期分为：从客户端、到服务端、然后在服务器上进行解析、生成执行计划、执行、并将结果返回给客户端。

查询包含一系列子任务，如果要优化查询，实际上是要优化其子任务，可以：

减少子任务执行次数
消除子任务的方式
让子任务运行的更快

通过上面三种方法来提高查询效率。

在mysql中，能够使用过下面三种方式的where条件，由好到坏依次为：

索引中使用where来过滤不需要的记录，这是在存储引擎中实现的
覆盖索引扫描来返回记录：直接在索引中过滤掉不需要的记录并返回命中结果，这是在mysql服务器实现的
从数据表中返回数据，然后过滤不满足的记录

优化数据访问

查询性能低下最基本的原因是访问的数据太多。对于低效的查询，下面两个步骤的分析总是很有效：

确认应用程序是否检索大量超过需要的数据。这意味着访问了太多的行或者列
确认MySQL服务器层是否在分析大量超过需要的数据行。

是否向数据库请求了不需要的数据

查询不需要的记录

如果我们不需要展示全部记录，最好在查询后加上limit

多表关联时，返回全部列

例如，我们要查询一个员工的工号，姓名，和部门名称：

select
	*
from
	employees e
inner join dept_emp ed
		using (emp_no)
inner join departments d
		using(dept_no);

上面的查询会返回三个表的全部列，尽量不要这样查询，我们应该指定查询的列：

select
	e.emp_no ,e.first_name ,e.last_name ,d.dept_name
from
	employees e
inner join dept_emp ed
		using (emp_no)
inner join departments d
		using(dept_no);

总是取出全部列

每次看到SELECT *时，都要用怀疑的眼光来审视。我们需要根据实际情况来判断是否真的需要查询所有的列，取出全部的列，我们无法完成索引完全覆盖这样的优化，还会对服务器资源带来额外消耗。

重复查询相同的列

如果存在重复查询，我们可以将这个查询结果缓存

MySQL是否在扫描额外的数据

在确定查询只返回需要的数据之后，接下来应该看看查询为了返回这些结果是否扫描了过多的数据。

对于MySQL，最简单的衡量查询开销的三个指标：

响应时间
扫描的行数
返回的行数

这三个指标都会记录到慢日志中，所以检查慢日志记录是找出扫描行数过得的查询的好办法。

查询最重要的是响应时间

响应时间

响应时间=等待时间+排队时间

服务时间：指执行某条具体sql查询时所消耗的时间
排队时间：指服务器因为等待某些资源而没有真正执行查询的时间

扫描行数和返回的行数

分析查询时，查看扫描的行数是非常有帮助的，这在一定程度上说明了该查询找到需要的数据的效率。
理想情况下扫描的行数与返回的行数应该是相同的，但是实际中这种情况并不多，比如关联查询，需要扫描多表的多条记录才能产生一条结果记录

扫描行数与访问类型

在EXPLAIN语句中的type反应了访问类型，访问类型有很多，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里的类型速度从慢到快，扫描的行数从多到少。

如果发现查询需要扫描大量的数据而只返回少量的行，可以通过下面方法进行优化：

使用索引覆盖扫描
改变库表结构，比如使用单独的汇总表
重写这个复杂的查询

重构查询的方式

一个复杂查询还是多个简单查询

在传统实现中，总是强调需要数据库完成尽可能多的工作。但是对于MySQL而言，这并不适用，MySQL从设计上让连接和断开都非常轻量级，在返回一个小的查询结果方面很高效。

切分查询

我们可以将一个大的查询“分而治之”，将大查询分成小查询，每个小查询功能相同，只完成一小部分，每次只返回一小部分查询结果。这样可以减轻服务器负担，避免一次性锁住很多数据，占用过多事务日志等等。

如果是事务性引擎，很多时候小事务可能更高效

分解关联查询

很多高性能的应用都会对关联查询进行分解：可以对每一个表进行一次单标查询，然后将结果在应用程序中进行管理。比如：

select
	e.emp_no ,
	e.first_name ,
	e.last_name ,
	d.dept_name
from
	employees e
inner join dept_emp ed
		using (emp_no)
inner join departments d
		using(dept_no)
where
	e.first_name = 'Mary'

可以分解为：

select e.emp_no,e.first_name,e.last_name from employees e where e.first_name = 'Mary'；
select d.dept_no from dept_emp de where de.emp_no = 10011;
select d.ept_name from departments d where d.dept_no = 'd009';

分解关联查询的方式重构查询有如下优势：

让缓存的效率更高。不管是应用程序缓存还是MySQL查询缓存，对单表查询结果进行缓存，都将提高缓存的利用率
将查询分解后，执行单个查询可以减少锁的竞争
在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能和可扩展
查询本身的效率也可能会有提升（单表查询更容易实现顺序查询，这可能比随机的关联更高效）
可以减少冗余记录的时间。在应用层做关联，意味着对于某条记录应用只需要查询一次；而在关联查询中，可能需要重复的查询某一部分数据
这样做相当于在应用中实现了hash关联

在如下场景中，将关联放在应用程序中将会更加高效：

当应用能够方便的缓存单个查询结果时
当可以将数据分布到不同的MySQL服务器上时
当能够用In()的方式代替关联查询时
当查询中使用同一个数据表时

查询执行的基础

如果我们弄清楚MySQL是如何优化和执行查询的，我们就可以理解：很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行
在这里插入图片描述

MySQL客户端/服务端通信协议

MySQL客户端/服务端通信协议是半双工的，这种协议让MySQL通信变得简单，但是也从很多地方限制了MySQL。一个明显的限制就是无法进行流量控制，一旦一段开始发送消息，另一端要接收完整的消息之后才能响应它

客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询语句很长时，参数max_allowd_packet就特别重要了

相反，服务器响应的数据通常比较多，由多个数据包组成。客户端必须完整的接收整个返回结果，所以查询中使用Limit限制是很有必要的

MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源。所以缓存通常可以减少数据库压力，让查询早点结束、早点释放响应的资源。

查询状态

对于一个MySQL连接，或者说一个线程任何时候都有一个状态，该状态表示了MySQL当前正在做什么。我们可以通过SHOW FULL PROCESSLIST命令结果中的Command列来查看连接的当前状态：

show  full processlist;

Sleep: 线程正在等待客户端发送新请求。
Query：线程正在执行查询或者正在将结果发送给客户端
Locked：在MySQL服务器层，该线程正在等待表锁
Analyzing and statistics:线程正在搜集存储引擎的统计信息，并生成查询的执行计划
Copying to tmp table [on disk]: 线程正在执行查询，并且将其结果集都复制到一个零时表中，这种状态一般要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作。
Sorting result:线程正在对结果集进行排序。
Sending data:线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据。

查询缓存（Query Cache）

MySQL8.0这个功能以及删除

查询优化处理

当MySQL不能通过索引得到排序结果时，就需要自己进行排序，如果数据量小就在内存中进行，如果数据量大就需要使用硬盘，MySQL将这个过程称为“文件排序（sort）”

如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行“快速排序”操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并向每个块的排序结果放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

MySQL有如下两种排序算法：

两次传输排序
- 读取行指针和需要排序的字段，对其进行排序，然后在根据排序结果读取所需数据行。
- 该算法在第二次读取数据时会造成大量的随机I/O，所以两次传输排序的数据传输成本非常高。
- 但是这个算法在排序时存储尽可能少的数据，使的“排序缓冲区”中能够容纳更多的行数进行排序。
单次传输排序
- 先读取查询所需的所有列，然后在根据给定列进行排序，最后直接返回排序结果。
- 该算法的优缺点与两次传输相反。

当查询需要所有列的总长度不超过max_length_sort_data时，MySQL会使用“单次传输排序”，我们可以通过调整该参数来影响MySQL排序算法的选择。

在关联查询的时候如果需要排序：

如果ORDER BY子句的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就会进行文件排序。这种情况下，EXPLAIN 的Extra字段可以看到"Using filesort"。
除去第一种情况的其它情况，MySQL都会先将关联存放在一个临时表中，然后在所有的关联结束后，再进行文件排序。XPLAIN 的Extra字段可以看到"Using temporary;Using filesort"。

如果有LIMIT，LIMIT也会在排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大

查询执行引擎

MySQL的查询执行引擎根据执行计划给出的指令逐步执行，在执行计划逐步执行的过程中，大量的操作需要通过调用存储引擎实现的接口来完成。

返回结果给客户端

查询执行的最后一个阶段是将结果返回给客户端。如果查询可以被缓存，那么查询结果也在这个节点被存放在查询缓存中。

MySQL将结构返回给客户端是一个增量、逐步返回的过程。如关联查询，一旦查询处理完最后一个管理表，开始生成第一条结果时，MySQL就可以开始向客户端逐步返回结果集了。这样做有两个好处：

服务端无需存储太多结果
客户端可以在第一时间得到返回结果

结果集中的每一个都会以满足MySQL客户端/服务端通信协议的封包发送，再通过TCP协议进行传输。

OceanStar的学习笔记

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
MySQL面试：查询性能的优化方法

查询真正重要的是响应时间，查询包含一系列子任务：通过减少执行的子任务执行次数消除子任务的方式来优化查询提高子任务执行的效率通过上面三种方法来提高查询效率。查询的生命周期分为：从客户端、到服务端、然后在服务器上进行解析、生成执行计划、执行、并将结果返回给客户端。在每一个消耗大量时间的查询案例中，都可以看到一些不必要的额外操作，某些操作被重复的执行了很多次，某些操作执行太慢，优化查询的目的是为了减少和消除这些操作所花时间。优化数据访问查询性能低下...
复制链接

扫一扫