MySQL查询性能优化

优化金字塔

性能优化是一个很大的话题。而真正的内存优化也存在一个金字塔之说。

具体的硬件调优，一般属于DBA专门来操作的。

对于 MySQL 调优，需要确认业务表结构设计是否合理，SQL 语句优化是否足 够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。

如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL 和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。

如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 I/O 存储硬件或者 CPU 也无法支撑业务的全表扫描。所以本章我们重点关注 MySQL 方面的调优，特别是索引。SQL/索引调优要求 对业务和数据流非常清楚。

如何判断sql什么时候需要优化

前面的章节我们知道如何设计最优的库表结构、如何建立最好的索引，这些 对于高性能来说是必不可少的。但这些还不够—还需要合理的设计查询。如果查 询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

也就是说，在表设计比较规范的前提下，什么时候是由于我们的查询语句导致的sql需要优化呢？

慢查询日志

慢查询日志，顾名思义，就是查询花费大量时间的日志，是指 mysql 记录所 有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志。该日志能 为 SQL 语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用 慢查询日志功能，首先要开启慢查询日志功能。

慢查询配置

我们已经知道慢查询日志可以帮助定位可能存在问题的 SQL 语句，从而进行 SQL 语句层面的优化。但是默认值为关闭的，需要我们手动开启。

查询启动状态

show VARIABLES like 'slow_query_log';

开启慢查询

set GLOBAL slow_query_log=1;

查询慢查询阈值

show VARIABLES like '%long_query_time%';

设置慢查询阈值

set global long_query_time=0; ---默认 10 秒，这里为了演示方便设置为 0

查询是没有走索引的SQL语句

show VARIABLES like '%log_queries_not_using_indexes%';

查看慢查询日志输出位置

show VARIABLES like 'log_output';

指定慢查询输出位置

对于产生的慢查询日志，可以指定输出的位置，通过参数 log_output 来控制， 可以输出到[TABLE][FILE][FILE,TABLE]。比如 ：

set global log_output='FILE,TABLE'

缺省是输出到文件，我们的配置把慢查询 输出到表，不过一般不推荐输出到表。

慢查询解读分析

开启慢查询功能以后，会根据我们的配置产生慢查询日志

当然，这里的参数都不好看，实际工作中也没人直接看这个。

日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息：mysqldumpslow --help

参数解释

s: 是表示按照何种方式排序

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询行数

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

t:即为返回前面多少条的数据

g:后边搭配一个正则匹配模式，大小写不敏感的

常用指令

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

 

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

 

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

 

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

导致慢查询的原因

查询性能低下最基本的原因是访问的数据太多。大部分性能低下的查询都可 以通过减少访问的数据量的方式进行优化。对于低效的查询，一般通过下面两个 步骤来分析总是很有效:

1. 确认应用程序是否在检索大量超过需要的数据。这 通常意味着访问了太 多的行，但有时候也可能是访问了太多的列。 （多余列，禁止使用select *）
2. 确认 MySQL 服务器层是否在分析大量超过需要的数据行。（多余行，limit解决）

查询不需要的记录

页面只需要10条记录，然后查100w条，在内存中过滤出10条。

总是取出全部列

大量使用select * 操作，导致回表效率下降。

select * 使用场景。可以将数据缓存到内存中，之后的操作在内存中处理。但如果对数据库刷新时效高的场景，select * 是不合适的。

重复查询相同的数据

一张几乎不会修改的表，例如配置表。却不停地去数据库里反复拿数据。

应该在加载时将数据缓存到内存中，之后针对内存操作，性能会更好。

衡量查询开销的三个指标

没有哪个指标能够完美地衡量查询的开销，但它们大致反映了 MySQL 在内部执行查询时需要访问多少数据，并可以大概推算出查询运行的时间。响应时间、扫描的行数、返回的行数这三个指标都会记录到 MySQL 的慢日志中。

响应时间

响应时间指我们Java客户端向MySQL发起请求，到数据从Mysql返回并到客户端响应的时间。

响应时间是两个部分之和:服务时间+排队时间。

服务时间

MySQL实际执行处理这个查询的时间。

排队时间

指服务器因为等待某些资源而没有真正执行查询的时间—-可能 是等 I/O 操作完成，也可能是等待行锁，等等。

判断响应时间是否合理

当你看到一个查询的响应时间的时候，首先需要问问自己，这个响应时间是 否是一个合理的值。概括地说，了解这个查询需要哪些索引以及它的执行计划是 什么,然后计算大概需要多少个顺序和随机 I/O，再用其乘以在具体硬件条件下一 次 I/O 的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当前响应时间是不是一个合理的值。帮助我们排查问题。（比如可能是IO时间超长，网络不好导致的慢查询等）

扫描的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够 说明该查询找到需要的数据的效率高不高。

返回的行数

理想情况下扫描的行数和返回的行数应该是相同的。但实际情况中这种“美 事”并不多。例如在做一个关联查询时，服务器必须要扫描多行才能生成结果集 中的一行。扫描的行数对返回的行数的比率通常很小，一般在 1:1 和 10:1 之间， 不过有时候这个值也可能非常非常大。

扫描的行数和访问类型

在 EXPLAIN 语句中的 type 列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度 是从慢到快，扫描的行数也是从小到大。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。后面会在执行计划中继续详细讲解。

如何优化查询

好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。

如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下 面的技巧去优化它:

1. 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无 须回表获取对应行就可以返回结果了（在前面的章节中我们已经讨论过了)。
2. 改变库表结构。例如使用单独的汇总表。
3. 重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个查询。

复杂查询分解为多个简单查询

在传统实现中，总是强调需要数据库层完成尽可能多的工作， 这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。

现代的网络速度比以前要快很多，无论是带宽还是延迟。在某些版本的 MySQL 上，即使在一个通用服务器 上，也能够运行每秒超过 10 万的查询，即使是一个千兆网卡也能轻松满足每秒超过 2000 次的查询。所以运行多个小查询现在已经不是大问题了。

但是，对比MySQL内部，每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应数据给客户端就慢得多了。如果频繁的拆分成小查询，对总体的耗时依旧是有影响的。

我们需要根据我们的业务场景来决定是否将复杂查询进行拆分。即时总耗时增加，依旧会降低每个小查询的时间，比如：和用户直观感受到的数据可以优先返回。其他不重要的数据放到其他查询中。根据我们的业务场景而定。

切分查询（一条大sql，分解成多条小sql）

有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

面试高频——如何执行一个大删除

删除旧的数据就是一个很好的例子。定期地清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。将一个大的 DELETE 语句切分成多个较小的查询可以尽可能小地影响 MySQL 性能，同时还可以减少 MySQL 复制的延迟。

一次删除一万行数据一般来说是一个比较高效而且对服务器影响也最小的做法。同时，需要注意的是，如果每次删除数据后，都暂停一会儿再做下一次删除，这样也可以将服务器上原本一次性的压力分散到一个很长的时间段中，就可以大大降低对服务器的影响，还可以大大减少删除时锁的持有时间。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地，可以对每一个表进行 一次单表查询，然后将结果在应用程序中进行关联（把mysql的join操作放到业务代码中实现，访问多次数据库）。到底为什么要这样做?乍一 看，这样做并没有什么好处，原本一条查询，这里却变成多条查询，返回的结果又是一模一样的。

分解关联查询的优势

事实上，用分解关联查询的方式重构查询有如下的优势:

1. 让缓存的效率更高。我们知道执行一条sql的时候，会先去查询缓存中查找。那么许多应用程序可以方便地缓存单表查询对应的结果对象。如果关联查询，那么关联中的某个表发生了变化，那么就无法使用查询缓存，而拆分后，如果某个表很少改变，那么基于该表的查询就可以重复利用查询缓存结果了。
2. 将查询拆分后，执行单个查询可以减少锁的竞争。
3. 在应用层做关联，可以更容易对数据库进行拆分，在现如今分库分表普遍使用的情景下，更容易做到高性能和可扩展。
4. 查询本身效率也可能会有所提升。比如：让某些查询用IN()代替关联查询，可以让Mysql按照ID顺序进行查询，着可能比随机的关联要更高效。
5. 可以减少冗余记录的查询。在应用层做关联，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能要重复地访问一部分数据。从这方面看，这样的重构还可能会减少网络和内存的消耗。（比如我们的xxx.name=yyy.name，假设xxx中的name存在大量重复字段，就会造成重复）。
6. 分解关联查询，相当于在应用中实现了哈希关联，而不是使用Mysql的类似嵌套循环关联。某些场景哈希关联的效率更高效。

MRR（二级索引回表优化）

条件查询中包含多个索引，优化器该如何取舍

一般情况下只能利用单个二级索引执行查询，比方说下边的这个查询：

SELECT * FROM order_exp WHERE insert_time = '2021-03-22 18:34:56' 
AND order_no> '你好，李焕英。7 排 24 号,过期时长:DD00_24S';

查询优化器会识别到这个查询中的两个搜索条件：

1. insert_time = '2021-03-22 18:34:56'
2. order_no> '你好，李焕英。7 排 24 号,过期时长:DD00_24S'

优化器一般会根据 order_exp 表的统计数据来判断到底使用哪个条件到对应 的二级索引中查询扫描的行数会更少，选择那个扫描行数较少的条件到对应的二 级索引中查询。

然后将从该二级索引中查询到的结果经过回表得到完整的用户记录后再根 据其余的 WHERE 条件过滤记录。一般来说，等值查找比范围查找需要扫描的行数更少（也就是 ref 的访问方法一般比 range 好，但这也不总是一定的，也可能采用 ref 访问方法的那个索引列的值为特定值的行数特别多）。

二级索引的查询步骤

假设优化器决定使用 idx_insert_time 索引进行查询，那么整个查询过程可以分为两个步骤：

步骤 1：使用二级索引定位记录的阶段，也就是根据条件 insert_time = '2021-03-22 18:34:56'从 idx_insert_time 索引代表的 B+树中找到对应的二级索引记录。

步骤 2：回表阶段，也就是根据上一步骤中找到的记录的主键值进行回表操 作，也就是到聚簇索引中找到对应的完整的用户记录，再根据条件 order_no>' 你好，李焕英。7 排 24 号,过期时长:DD00_24S' 到完整的用户记录继续过滤。将最终符合过滤条件的记录返回给用户。

MRR（批量处理回表）

从上文可以看出，每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的， 也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。

每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机 IO 带来的性能开销比较大。MySQL 中提出了一个名为 Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后（或许可以减少随机IO）再统一执行回表操作。

相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些 IO 开销。使用这个 MRR 优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。