本文转载自微信公众号《小猪笔记本》，作者钟哥。转载本文请联系小猪笔记本公众号。上期我们深入了解了MySQL索引。有了MySQL索引，可以在一定程度上提高MySQL的查询速度。本期我们将学习MySQL查询性能优化的一般方法。1为什么查询速度慢？其实很简单。在一个应用程序中，我们需要查询一些数据，通常是从客户端开始。请求通过网络传输到达服务器后，服务器对其进行解析，然后发送查询命令对于MySQL，经过一系列的分析，优化等，MySQL最终查询出结果，返回给客户端，最后给到给我们的用户。在这一系列操作中，由于网络延迟、CPU、内存、锁竞争、系统调用、上下文切换、存储引擎触发的检索数据等一系列操作等，都会或多或少地影响查询速度.条件不利时，查询速度会变慢。在大致知道查询速度受这些因素的影响后，我们就可以找到一些优化查询速度的方向。2慢查询基础知识：优化数据访问查询性能低下的最基本原因是访问的数据过多。我们可以通过以下两步来分析低效查询：确认应用程序是否正在检索大量超出要求的数据行确认MySQL服务器层是否正在分析大量超出要求的数据行？2.1你是否在向数据库请求不必要的数据？这种冗余操作会给MySQL服务器带来额外的负载，增加网络开销，消耗应用服务器的CPU和内存资源。以下情况属于此类：查询了不需要的记录。例如，当应用程序select*不需要所有的数据，但又不加limit到limit，从而检索表中的所有数据时，显然是画蛇添足。当关联多个表时，返回所有列。多表关联时，所有无意义的列也都checkout了，同样会影响性能。始终获取所有列。像select*这样fetchallcolumn的查询，不一定能完成索引覆盖等优化，会触发各种回表查询，给服务器带来额外的IO、内存、CPU消耗。重复查询相同的数据。对于这种每次返回相同结果的查询，其实检查一次就够了，将结果存储在Redis等缓存中，减轻MySQL的压力。2.2MySQL是否扫描附加记录对于MySQL来说，衡量性能开销的三个指标是：响应时间、扫描的行数、返回的行数。RowsScannedtoRowsReturned理想情况下，扫描的行数与返回的行数之比通常较小，MySQL扫描的额外行数较少。扫描的行数和访问类型在评估查询开销时，您需要考虑从表中查找一行数据的成本。某些MySQL访问方法可能必须扫描许多行才能返回一行结果。使用解释语句中的类型列来反映访问类型。访问类型包括索引扫描、范围扫描、唯一索引查询、常量引用等。通常MySQL可以通过以下三种方式来应用where条件，从好到坏：在索引中使用where条件过滤不匹配的记录，即存储引擎使用索引覆盖扫描（出现在extracolumnusingindex）返回记录，直接从索引中过滤不需要的记录，返回命中结果。这是在MySQL服务器层完成的，从数据表中返回数据，然后过滤不符合条件的记录（使用where出现在extra列），这是在MySQL服务器层完成的。MySQL需要先从数据表中读取记录，然后进行过滤如果发现查询需要扫描大量数据，但只返回几行，通常可以尝试以下技巧进行优化：使用索引覆盖扫描来把所有需要查询的列都放到索引中，这样存储引擎就不需要回表查询了。可以返回结果改变数据库表结构，使用一些汇总表来存储结果，避免各种联合查询重写复杂的查询，让MySQL优化器以更优化的方式执行这个查询3种重构查询的方式3.1一个复杂的Query或多个简单的查询MySQL实际上是为了让连接和断开非常轻量级，和它在返回小查询结果时非常有效。如果要使用一个复杂的查询，而这个查询涉及到多张表的关联，那么性能其实远不如将这个查询分解成多个简单的查询。因此，一般情况下，如果可以使用多个简单查询，就不要使用一个复杂查询。3.2分段查询对于一个大的查询，可以采用分治法将大的查询分成小的查询。每个查询的功能完全一样，只完成了一小部分，每次只返回查询结果的一小部分。3.3关联查询分解将MySQL多表关联查询拆分为多个单表查询，然后在应用逻辑层处理查询结果，可以提高性能。优点如下：查询分解后，执行单个查询减少了锁争用。这是因为复杂关联的查询时间一般比较长，事务之间的锁竞争一般比较激烈，锁等待时间一般比较长。应用逻辑层的数据结果关联使得分库分表更容易，提高性能和扩展性查询本身的效率也会提高。使用索引编写单表查询的SQL语句更容易。使用in()代替关联查询可以让MySQL按照ids的顺序进行查询，比随机关联更高效（后面会介绍）可以减少冗余记录的查询。当多表关联查询可能会重复访问部分数据，而应用逻辑层关联只需要MySQL对某部分数据只查询一次，返回给应用程序。缓存更高效，应用逻辑层可以方便的缓存单表查询对应的结果；对于MySQL中的querycache，如果关联查询中的某个表发生变化，则querycache会失效4查询执行查询优化前面讲了那么多，现在我们来了解一下MySQL中执行一个查询的过程：查询SQL执行路径有一个大概的流程，我们来看看每一步的细节。4.1MySQL客户端与服务器端的通信协议由于客户端与服务器端之间传输的数据必须是完整可靠的，显然是使用TCP协议来建立连接的。MySQL客户端和服务器需要通信。在任何时刻，要么服务器向客户端发送数据，要么客户端向服务器发送数据，即半双工通信。这种通信协议使得MySQL客户端和服务器之间的通信变得简单，但也限制了MySQL。例如，一端必须完全接受另一端发送的数据，才能响应另一端。当我们使用DataGrip、Navicat等数据客户端连接MySQL服务器时，需要从一个数据量很大的表中select*，只能等待服务器返回结果。这个查询请求占用了很多资源。如果这样的查询请求很多，那么MySQL服务器的压力一定很大。所以不使用limit来限制，客户端和服务端都很难从数据库中获取整张表的数据。书中说：客户端从服务器获取数据时，看似是拉数据的过程，实际上是服务器向客户端推送数据的过程。客户端不断地接收服务器推送的数据，没有办法停止服务器，就像从消防水带喝水一样。所以一般来说，使用查询缓存可以减轻服务器压力，让查询提前结束，释放相关资源。查询状态对于每一个MySQL连接，也可以说一个线程在任何时刻都有一个状态，表示MySQL当前在做什么。睡眠线程正在等待客户端发送新的请求。查询线程正在执行查询或将查询结果返回给客户端。lockedMySQserver层，说明线程正在等待表锁。分析统计线程负责收集存储引擎的统计信息并生成查询结果。Executionplancopyingtotmptable[ondisk]线程正在执行查询并将其结果集复制到临时表（groupby、文件排序、并集操作等），如果有[ondisk]标记，则说明它表示MySQL在磁盘上放一张内存临时表排序结果线程排序结果集发送数据线程在多个状态之间传递数据；或生成结果集；或返回数据给客户端4.2Querycache正在解析一条查询语句以前，如果MySQL的querycache功能开启，MySQL会先检查查询是否命中了querycache中的数据。如果命中查询缓存，则返回结果；如果没有命中，则继续进行后续过程。4.3查询优化处理查询优化处理分为几个子阶段：解析SQL、预处理、优化SQL执行计划。4.3.1语法解析器和预处理MySQL通过关键字解析SQL语句，生成对应的解析树，MySQL解析器将使用MySQL语法规则对查询进行校验和解析。预处理器根据MySQL规则进一步检查解析树是否合法，比如检查数据表和列属性是否存在，解析名称和别名，看是否有歧义。接下来将进一步验证权限。4.3.2查询优化器查询优化器的主要功能是为执行一条SQL语句寻找最佳的执行计划。MySQL使用基于成本的优化器，它会尝试预测使用某个执行计划的查询的成本，并选择成本最低的一个。优化器在评估成本时，随机IO次数的统计计算主要受每个表或索引页数、索引基数、索引分布、数据行长度、索引分布的影响。优化器在评估成本时不考虑任何级别的缓存，它假定读取任何数据都需要磁盘IO。MySQL优化器可能选择错误执行计划的情况：随机IO次数统计不准确。受InnoDB等存储引擎的影响，由于MVCC机制，无法维护准确的数据表行数统计信息。执行计划中的成本估算不等于实际执行成本。由于MySQL在分析执行计划时并不知道哪些页面在内存中，哪些页面在磁盘中，因此很难估计查询执行过程中需要多少磁盘IO。MySQL根据成本模型选择最优的执行计划，我们的用户希望查询时间越短越好。MySQL不考虑并发执行的查询，这可能会影响实际执行时的查询速度。一些不可预知的问题也会影响MySQL的实际执行。这里插入一张图片来描述MySQL。处理的优化类型：重新定义关联表的顺序。有可能SQL语句是select*fromainnerjoinbona.id=b.id，但是MySQL查询优化器关联表的顺序可能是先b表再a表。将外连接转换为内连接。可能因为where条件和数据库表的结构，外连接会被MySQL优化器变成内连接。使用等价变换规则简化表达式。例如：5=5和a>5可以简化为a>5可以优化count(),min(),max()。MySQL索引在这里发挥了很大的作用。比如求某列的最小值，只需要查询B+树最左边的叶子节点，直接获取叶子节点的第一条记录即可。同理，求一列的最大值，只需要查询B+树最右边的叶子节点，直接获取叶子节点的最后一条记录即可。对于统计count()，不同类型的存储引擎有不同的优化方式。比如MyISAM维护了一个变量来存储表中的记录数，这使得count计数的时间复杂度直接优化为O(1)。评估并转换为常量表达式。比如where1=1，另外，使用主键或者唯一索引也可以转换成常量表达式。例如：selecta.name,b.moneyfromainnerjoinbusing(a.id)wherea.id=2021，表a的id为主键索引，a.id有一定的值2021，然后把a表中返回的a.id为2021的记录当作常量，再去b表查询。同时using子句也让MySQL知道a.id是一个常量。覆盖指数。当索引中的列中包含要查询的列时，MySQL会直接使用索引返回需要的数据。子查询优化。MySQL会将子查询转换成高效的形式，从而避免多次查询多次访问数据。提前终止查询。当MySQL发现满足查询条件或查询条件不成立时，MySQL将终止查询。例如：使用限制；如果不满足where条件，MySQL会立即返回一个空结构；存储引擎检索不同的值或判断值的存在等。等价传播。如果两列的值是等价关系，MySQL可以将一列的where条件传递给另一列。例如：select*fromainnerjoinbona.id=b.idwherea.id=2021，表a和表b是同一个id关联的，a.id=2021的条件也适用表b。列表IN()的比较。MySQL先对IN中的数据进行排序，然后通过二分查找判断是否满足IN中的条件。这是一个O(logn)操作。因此，当表数据量较大，查询条件有多种时，可以考虑使用IN语句来优化查询。4.3.3关联优化MySQL在执行连接查询时，往往先执行子查询，将子查询的结果存储到临时表中，然后以临时表中的结果作为条件执行父查询。MySQL的优化器优化关联查询，例如selecta.id,b.namefromainnerjoinbona.id=b.id。优化器优化多个表的关联顺序，通过评估不同顺序的成本，选择一个成本最小的关联顺序执行查询。4.3.4排序优化排序是一个开销非常大的操作，所以在性能上应该尽量避免排序或者对大量数据进行排序。当MySQL无法使用索引进行排序时，需要对文件进行排序（少量数据在内存中进行，大量数据需要使用磁盘）。如果待排序的数据量小于“排序缓冲区”，MySQL会使用内存进行快速排序。如果内存不够排序，MySQL会将数据分块，对每个独立的块使用快速排序，并将每个块的排序结果存储在磁盘上，然后合并排序好的块，最后返回排序结果。对于MySQL5.6及以上版本，排序算法为单传输排序：先读取查询所需的所有列，然后根据给定的列进行排序，最后直接返回排序后的结果。虽然这种排序算法只需要一次顺序IO就可以读取所有数据，但是如果要返回的列很多而且非常大，会占用大量的额外空间，而且这些列对于排序操作本身是没有用的，所以有利有弊。值得一提的是，如果在关联查询时需要排序，MySQL会根据情况进行这样的文件排序。另外，MySQL会先将关联的结果存储在一个临时表中，然后再进行文件排序。4.4查询执行引擎在解析和优化阶段，MySQL会生成对应查询计划的执行，MySQL的查询执行引擎根据这个执行计划完成整个查询。查询执行引擎会根据执行计划给出的指令一步步执行。在执行过程中，需要调用存储引擎实现的接口来完成大量的操作，该接口称为“handlerAPI”。MySQL在优化阶段为每个表创建一个handler实例，优化器会根据这些实例的接口获取表的相关信息（列名、索引统计信息等）。并非所有操作都由处理程序完成。例如，当MySQL需要表锁时，处理程序可能会实现特定级别的更细粒度的锁。例如，InnoDB实现了自己的基本行锁，但这不能替代服务器层的表锁。4.5向客户端返回结果查询执行的最后阶段是向客户端返回结果。即使查询不需要返回结果集，MySQL仍然会返回一些关于查询的信息，比如查询影响的行数。如果查询可以被缓存，MySQL在将结果返回给客户端之前将结果存储在查询缓存中。MySQL将结果集返回给客户端是一个渐进的、循序渐进的过程。这种方式的好处是服务器端不需要存储太多的结果，也不会因为返回太多的结果而消耗太多的内存；客户端也可以快速获取返回结果。结果集中的每一行都会在满足MySQL客户端/服务器通信协议的数据包中发送，然后通过TCP协议传输。在TCP传输过程中，可能会将MySQL报文缓存起来，然后进行批量传输。小结本期主要总结一下MySQL的查询过程，了解到执行一条SQL的过程需要经过MySQL的各个组件。下一期，我们将重点探讨优化MySQL查询性能的方法。我是钟格，一名在互联网公司写代码的工人。您的支持是我创作最大的动力。下期见~
