精华内容
下载资源
问答
  • 数据库优化——慢查询MySQL定位优化流程

    万次阅读 多人点赞 2021-05-19 11:07:25
    如何定位并优化查询SQL?如何使用慢查询日志?本文带来慢查询例子演示,新手都能看懂!那查询语句慢怎么办?explain带你分析sql执行计划!当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?本文带你一...

    继前篇博文看了这么多,终于理解了MySQL索引之后,给大家再来一篇日常开发需要用到的优化技巧。

    1.如何定位并优化慢查询SQL?

    一般有3个思考方向
    1.根据慢日志定位慢查询sql
    2.使用explain等工具分析sql执行计划
    3.修改sql或者尽量让sql走索引

    2.如何使用慢查询日志?

    先给出步骤,后面说明
    有3个步骤
    1.开启慢查询日志

      首先开启慢查询日志,由参数slow_query_log决定是否开启,在MySQL命令行下输入下面的命令:

    set global slow_query_log=on;
    

    默认环境下,慢查询日志是关闭的,所以这里开启。

    2.设置慢查询阈值

    set global long_query_time=1;
    

      只要你的SQL实际执行时间超过了这个阈值,就会被记录到慢查询日志里面。这个阈值默认是10s,线上业务一般建议把long_query_time设置为1s,如果某个业务的MySQL要求比较高的QPS,可设置慢查询为0.1s。发现慢查询及时优化或者提醒开发改写。一般测试环境建议long_query_time设置的阀值比生产环境的小,比如生产环境是1s,则测试环境建议配置成0.5s。便于在测试环境及时发现一些效率的SQL

      甚至某些重要业务测试环境long_query_time可以设置为0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。

    3.确定慢查询日志的文件名和路径

    show global variables like 'slow_query_log_file'
    

    在这里插入图片描述
    结果会发现慢日志默认路径就是MySQL的数据目录,我们可以来看一下MySQL数据目录

     show global variables like 'datadir';
    

    在这里插入图片描述
    不用关注这里为什么不是MySQL 8.0,这和版本没什么关系的。

    来,直接上菜,干巴巴的定义我自己都看不下去

    我们先来查看一下变量,我框出了需要注意的点
    查询带有quer的相关变量

    show global variables like '%quer%';
    

    这里设置慢查询阈值为1s

    set global long_query_time=1;
    

    可以看到已经修改过来了

      但是重启mysql客户端设置和统计慢查询日志条数就会清零,即所有配置修改会还原

      命令修改配置之后,在命令行net stop mysql关闭MySQL服务,再net start mysql开启MySQL服务,接着执行show global variables like '%quer%';会发现配置还原了。

      在配置文件修改才能永久改变,否则重启数据库就还原了

    3.慢查询例子演示,新手都能看懂

    数据表结构,偷懒没写comment

    CREATE TABLE `person_info_large` (  
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  
    

    这里的数据是200W条。请注意表结构,记住哪几个字段有索引即可,后续围绕这个表进行分析。

    这个3.36s并不是实际执行时间,实际执行时间得去慢查询日志去看Query_time参数

      可以看到Query_time: 6.337729s,超过了1s,所以会被记录,一个select语句查询这么久,简直无法忍受。

    图中其他的参数解释如下:
    Time:慢查询发生的时间
    Query_time:查询时间
    Lock_time:等待锁表的时间
    Rows_sent:语句返回的行数
    Rows_exanined:语句执行期间从存储引擎读取的行数

      上面这种方式是用系统自带的慢查询日志查看的,如果觉得系统自带的慢查询日志不方便查看,可以使用pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析。

      注意:有的慢查询正在执行,结果已经导致数据库负载过高,而由于慢查询还没执行完,因此慢查询日志看不到任何语句,此时可以使用show processlist命令查看正在执行的慢查询。show processlist显示哪些线程正在运行,如果有PROCESS权限,则可以看到所有线程。否则,只能看到当前会话线程。

    4.查询语句慢怎么办?explain带你分析sql执行计划

    根据上一节的表结构可以知道,account是添加了唯一索引的字段。explain分析一下执行计划。

      我们重点需要关注select_typetypepossible_keyskeyExtra这些列,我们来一一说明,看到select_type列,这里是SIMPLE简单查询,其他值下面给大家列出。

    type列,这里是index,表示全索引扫描

      表格从上到下代表了sql查询性能从最优到最差,如果是type类型是all,说明sql语句需要优化。

    注意:如果type = NULL,则表明个MySQL不用访问表或者索引,直接就能得到结果,比如explain select sum(1+2);

      possible_keys代表可能用到的索引列,key表示实际用到的索引列,以实际用到的索引列为准,这是查询优化器优化过后选择的,然后我们也可以根据实际情况强制使用我们自己的索引列来查询。

      Extra列,这里是Using index

      一定要注意,Extra中出现Using filesortUsing temporary代表MySQL根本不能使用索引,效率会受到严重影响,应当尽可能的去优化。

      出现Using filesort说明MySQL对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容,有索引就维护了B+树,数据本来就已经排好序了,这说明根本没有用到索引,而是数据读完之后再排序,可能在内存或者磁盘上排序。也有人将MySQL中无法利用索引的排序操作称为“文件排序”。

      出现Using temporary表示MySQL在对查询结果排序时使用临时表,常见于order by和分组查询group by

    回到上一个话题,我们看到account是添加了唯一索引的字段。explain分析了执行计划后

    直接按照account降序来查

    查看慢查询日志发现,使用索引之后,查询200W条数据的速度快了2s

    接着我们分析一下查询namesql执行计划

    然后给name字段加上索引

    加上索引之后,继续看看查询namesql执行计划

      对比一下前面name不加索引时的执行计划就会发现,加了索引后,typeALL全表扫描变成index索引扫描。order by并没有 using filesort,而是using index,这里B+树已经将这个非聚集索引的索引字段的值排好序了,而不是等到查询的时候再去排序。

      接着我们继续执行查询语句,此时name已经是添加了索引的。

      结果发现,name添加索引之前,降序查询name是花费6.337729s,添加索引之后,降序查询name花费了3.479827s,原因就是B+树的结果集已经是有序的了。

    5.当主键索引、唯一索引、普通索引都存在,查询优化器如何选择?

      查询一下数据的条数,这里count(id),分析一下sql执行计划

      这里实际使用的索引是account唯一索引。

      分析一下:实际使用哪个索引是查询优化器决定的,B+树的叶子结点就是链表结构,遍历链表就可以统计数量,但是这张表,有主键索引、唯一索引、普通索引,优化器选择了account这个唯一索引,这肯定不会使用主键索引,因为主键索引是聚集索引,每个叶子包含具体的一个行记录(很多列的数据都在里面),而非聚集索引每个叶子只包含下一个主键索引的指针,很显然叶子结点包含的数据是越少越好,查询优化器就不会选择主键索引

      当然,也可以强制使用主键索引,然后分析sql执行计划

    我们看一下优化器默认使用唯一索引大致执行时间676ms

    强制使用主键索引大致执行时间779ms

      我们可以用force index强制指定索引,然后去分析执行计划看看哪个索引是更好的,因为查询优化器选择索引不一定是百分百准确的,具体情况可以根据实际场景分析来确定是否使用查询优化器选择的索引。


    欢迎一键三连~

    有问题请留言,大家一起探讨学习

    ----------------------Talk is cheap, show me the code-----------------------
    展开全文
  • MySQL执行查询过程: ① 客户端发送一条查询给服务器。 ② 服务器先检查查询缓存,如果命中缓存,则立即返回结果。否则进入下一阶段。 ③ 服务器端进行sql解析、预处理,在由优化器生成对应的执行...

    当希望MySQL能够以更高的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解这一点,很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。

    MySQL执行查询过程:

    ① 客户端发送一条查询给服务器。

    ② 服务器先检查查询缓存,如果命中缓存,则立即返回结果。否则进入下一阶段。

    ③ 服务器端进行sql解析、预处理,在由优化器生成对应的执行计划。

    ④ MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。

    ⑤ 将结果返回给客户端和查询缓存

    上面的每一步都比相像的复杂,我们在后续继续讨论。我们会看到在每一个阶段查询处于何种状态。查询优化器是其中特别复杂也是特别难理解的部分。还有很多例外情况,例如,当查询使用绑定变量后,执行路径会有所不同。

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

    一般来说,不需要去理解MySQL通信协议的内部实现细节,只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信洗衣是半双工的,这意味着在任何一个时刻,要么是由服务器想客户端发送数据,要么是由客户端想服务器发送数据,这两个动作不能同时发生。所以,我们无法也无需将一个消息切成小块来独立发送。

    这种协议让MySQL通信简单快速,但是也从很多地方限制了MySQL。一个明显的限制是,这以为着无法进行流量控制。一点一端开始发送消息,另一端要接收完整个消息才能响应它。这就想来回抛球的游戏:在任何时刻,只有一个人能控制球,而且只有控制球的人才能将球抛回去。

    客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候,参数max_allowed_packet就特别重要了。一旦客户端发送了请求,它能做的事情就只是等待结果了。

    相反的,一般服务器响应给用户的数据通常很多,由多个数据包组成。当服务器开始响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送数据。这种情况下,客户端若接收完整的结果,然后取消前面几条需要的结果,或者接收完几条结果后就断开链接,都不是好主意。这也是在必要的时候一定要在查询中加上limit限制的原因。

    换一种方式解释这种行为:当客户端从服务器去数据时,看起来是一个拉取数据的过程,但实际上是MySQL在想哭独断推送数据的过程。客户端不断的接收从服务器推送的数据,客户端也没办法让服务器停止。

    多数链接MySQL的库函数都可以获得全部结果集并缓存到内存里,还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所以接收全部结果并缓存通常可以减少服务器的压力,让查询能够早点结束、早点释放相应资源。

    当使用多数链接MySQL的库函数从MySQL获取数据时,其结果看起来都像是从MySQL服务器获取数据,实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题,但是如果需要返回一个很大的结果集的时候,这样做并不好,因为库函数会花费很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集,就能大大减少内存的消耗,这种情况下可以补使用缓存来记录结果而是直接处理。这样做的缺点是,对于服务器来说,需要查询完成后才能释放资源,所以在和客户端交互的整个过程中,服务器的资源都是被这个查询所占用的。

    1 查询状态

    对于一个MySQL链接,或者说一个线程,任何时刻都有一个状态,该状态标识了MySQL当前正在做什么。有很多种方式能查看当前的状态,最简单是使用 show full processlist命令,该命令返回结果中的Command列就标识当前的状态。在一个查询的声明周期中,状态会变化很多次。MyQL官方手册中对这些状态表名了最权威的解释:

    ① Sleep:线程正在等待哭护短发送新的请求

    ② Query:线程正在执行查询或者正在讲结果发送给客户端

    ③ Locked:在MySQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnoDB的行锁,并不会提现在线程状态中。对于没有行锁的引擎中会经常出现。

    ④ Analyzing and statistics:线程正在收集存储引擎的统计信息,并生成查询执行计划。

    ⑤ Copying to tmp table[on disk]:线程正在执行查询,并且将其结果集都赋值到一个临时表中,这种状态一般要么是在group by操作,要么是文件培训操作,或union操作。如果这个状态后面还有on disk标记,那标识MySQL正在讲一个内存临时表放到磁盘上。

    ⑥ Sorting result:线程正在对结果集进行排序。

    ⑦ Sending data:这标识多种情况;线程可能在多个状态之间传送数据,或者在生成及国际,或在想客户端返回数据。

    了解这些状态的基本含义非常有用,这可以让你很快的了解“当前谁在拿着球”。在一个繁忙的服务器上,可能会看到大量的不正常的状态,例如statistics正占用大量的时间。这通常标识,某个地方有异常了。

    二 查询缓存

    在解析一个查询语句之前,如果查询缓存使打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个队大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果,这种情况下查询就会进入下一阶段处理。

    如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前MySQL会检查一次用户权限。这然然是无需解析查询SQL语句的,因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题,MySQL会跳过所有其他截断,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

    三 查询优化处理

    查询的声明周期的下一步是将一个SQL转换成一个执行计划,MySQL在依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。在实际执行中,这几部分可能一起执行也可能单独执行。我们目的是帮助大家理解MySQL如何执行查询,以便写出更优秀的查询。

    1 语法解析器和预处理

    首先,MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。

    MySQL解析器将使用MySQL语法规则验证和解析查询。例如,它将验证是否使用错误的关键字,或者使用关键字的顺序是否正确等,喊会验证引号是否能前后匹配。

    预处理器则根据一些MySQL规则进一步检查解析树是否合法,例如,这里讲检查数据表和数据列是否存在,还会解析名字和别名,看看他们是否有歧义。

    下一步预处理器会验证权限。这通常很快,除非服务器上有非常多的权限配置。

    2 查询优化器

    现在语法树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

    MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。最初,成本的最小单位是随机读取一个4K数据也的成本,后来成本计算公式变得更加复杂,并且引入了一些因子来估算某些操作的代价,例如执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知mySQL计算当前查询的成本

    select count(*) from tast_user;
    show status like 'last_query_cost';

    这个结果标识MySQL的优化器认为需要做10个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的:每个表或者索引的页面个数、索引的基数、索引和数据行的长度、索引分布情况等。优化器在评估成本的时候并不考虑任何层面的缓存,它假设读取任何数据都需要一次磁盘I/O。

    有很多种原因会导致MySQL优化器选择错误的执行计划:

    ① 统计信息不准确。

    MySQL依赖存储引擎提供的统计信息来评估成本,但是有的存储引擎提供的信息是准确的,有的可能会有很大偏差。例如InnoDB因为其MVCC的架构,并不能维护一个数据表的行数的精确统计信息。

    ② 执行计划中的成本估算不等同于实际执行的成本。

    所以即使统计信息精确,优化器给出的执行计划也可能不是最优的。例如有时候猴哥执行计划虽然需要读取更多的页面,但是它的成本却更小。因为如果这些页面都是顺序读或这些页面都已经在内存中的话,那么它的访问成本将很小。MySQL层面并不知道那些页面在内存中、那些在磁盘上,所以查询实际执行过程中到底需要多少次物理I/O是未知的。

    ③ MySQL的最优可能和你想的最优不一样。

    你可能希望执行时间尽可能的短,但是MySQL只是基于其成本模型选择最优的执行计划,而有些时候这并不是最快的执行方式。所以,这里我们看到根据执行成本来选择执行计划并不是完美的模型。

    ④ MySQL从不考虑其他并发执行的查询,这可能会影响到当前查询的速度。

    ⑤ MySQL也并不是任何时候都是基于成本的优化。

    有时也会基于一些固定的规则,例如,如果存在全文检索的match()字句,则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和where条件可以原比这种方式要快,MySQL也仍然会使用对应的全文索引。

    ⑥ MySSQL不会考虑不收其控制的操作的成本,例如执行存储过程或者用户自定义函数的成本。

    ⑦ 优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

     

    MySQL的查询优化器是一个非常复杂的不见,它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单的分为两种,一种是静态优化,一种是动态优化。静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过一些简单的代数变换将where条件装换成另一种等价形式。静态优化不依赖于特别的数值,如where条件中带入的一些常熟等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种编译时优化。

    相反,的动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如where条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为这是运行时优化。

    在执行语句和存储过程的时候,动态优化和静态优化的区别非常重要。MySQL对查询的静态优化只需要做一次,但对查询的动态优化则在每次执行时都需要重新评估。有时甚至在查询的执行过程中也会重新优化。

    下面是一些MySQL能够处理的优化类型:

    ① 重新定义关联表的顺序。

    数据表的关联并不总是按照在查询中指定的顺序执行。决定关联的顺序是优化器很重要的一部分功能。

    ② 将外连接转换成内连接。

    并不是所有的outer join 语句都必须以外连接的方式执行。例如where条件、库表结构都可能会让外连接等价一个内连接。MySQL能够识别这点并重写查询,让其可以调整关联顺序。

    ③ 使用等价变换规则。

    MySQL可以使用一些等价变换来简化并规范表达式。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断例如(5=5 and a>5)将被改写成 a>5。

    ④ 优化count()  min()和max().

    索引和列是否可以为空通常可以帮助MySQL优化这类表达式。例如,要找到某一列的最小值,值需要查询对应的B-Tree索引最左端的记录,MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候就可以利用这一点,在B-Tree索引中,优化器会将这个表达式作为一个常数对待。类似的,如果要找到一个最大值,也只需读取B-Tree索引的最后一条记录。如果MySQL使用了这种类型的优化,那么在EXPLAIN中就可以看到select tables optimized away。从字面意思可以看出,它标识优化器已经从执行计划中移除了该表,并以一个常数取代。

    类似的,没有任何where条件的count(*)查询通常也可以使用存储引擎提供的一些优化。

    ⑤ 预估并转化为常数表达式。

    当MySQL检测到一个表达式可以转化为常数的时候,就会一直把该表达式作为常数进行优化处理。例如,一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另一种典型的例子。

    让人惊讶的是,在优化截断,有时候甚至一个查询也能够转化为一个常数。一个例子是在索引列上执行min()函数。甚至是主键或者唯一键查找语句也可以转换为常数表达式。如果where子句中使用了这类索引的常数条件,MySQL可以在查询开始阶段就先查找到这些值,这样优化器就能够知道并转换为常数表达式。

    另一种会看到常数条件的情况是通过等式将常熟之从一个表传到另一个表,这可以通过where/using或on语句来限制某列取值为常数。

    ⑥ 覆盖索引扫描。

    当索引中的列包含所有查询中需要使用的列的时候,MySQL就可以使用索引返回需要的数据,而无需查询对应的数据行。

    ⑦ 子查询优化。

    MySQL在某些情况下可以将子查询转换一种效率更高的形式,从而减少多个查询多次对数据进行访问。

    ⑧ 提前终止查询。

    在发现已经满足查询需求的时候,MySQL总是能够like终止查询。比如说使用了limit子句的时候。初次之外,MySQL还有几种情况也会提前终止查询,例如发现了一个不成立的条件,这时MySQL可以立刻返回一个空结果。例如:

    explain select * from test_user where id = -1;

    这个例子看到查询在优化截断就已经终止。除此之外,MySQL在执行过程中,如果发现某些特殊的条件,则会提前终止查询。当存储引擎需要检索不同取值或者判断存在性的时候,MySQL都可以使用者类优化。类似这种不同值/不存在的列的优化一般可用distinct、not exist()或left join类型的查询类型的查询。

    ⑨ 等值传播。

    如果两个列的值通过等式关联,那么MmSQL能够把其中一个列的where条件传递到另一个裂伤。

    ⑩ 列表in()的比较。

    在很多数据库系统中,in()完全等同于多个or条件的子句,因为这两者是完全等价的。在MySQL中这点是不成立的,MySQL将in()列表中的数据线进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件,这是一个O(log n)复杂度的操作,等价的转换成or 查询的复杂度为O(n),对于in()列表中有大量取值的时候,MySQL的处理速度将会更快。

     

    上面列举的不是MySQL优化器的全部,MySQL还会做大量其他的优化,但上面的这些例子已经足以上大家明白优化器的复杂性和智能性了。如果说从上面这段讨论中我们能学到什么,那就是不要自以为比优化器更聪明。

    当然,虽然优化器已经很智能了,但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据,例如,由于应用逻辑使得某些条件总是成立的;还有时,优化器缺少某种功能特性,如哈希索引;在例如前面提到的,从优化器执行成本评估出来的执行计划,实际运行中可能比其他执行计划更慢。

    如果能够确认优化器给出的不是最佳选择,并且清楚背后的原理,那么也可以帮助优化器做进一步优化,例如,可以在查询中添加hint提示,也可以重写查询,或者重新设计更优的库表结构,或者添加更合适的索引。

    3 数据和索引的统计信息

    MySQL架构由多个层次组成。在服务器层有查询优化器,却没有保存数据和索引的统计信息。统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息。例如Archive引擎,则没有存储任何信息。

    因为服务器层没有任何统计信息,所以MySQL查询优化器再生成查询的执行计划时,需要向存储引擎获取响应的统计信息。存储引擎则提供给优化器对应的统计信息,包括:每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。在后面的小姐中我们将看到统计信息是如何影响优化器的。

    4 MySQL如何执行关联查询

    MySQL中关联一次所包含的意义比一般意义上理解的要更广泛。总的来说,MySQL认为任何一个查询都是一次关联;并不仅仅是一个查询需要到两个表匹配才叫关联,所以在MySQL中,每一个查询,每一个片段都可能是关联。

    当前MySQL关联执行的侧率很贱但:MySQL对任何关联都执行嵌套循环关联操作,即MySQL先在一个表中循环去除单条数据,然后再嵌套循环到下一个表中虚招匹配的行,依次下去,知道找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需要的各个列。MySQL会尝试在最后一个关联表中找到所有匹配的行,如果最后一个关联表无法找到更多的行以后,MySQL返回到上一层此关联表,看是否能够找到更多的匹配记录,以此类推迭代执行。

    按照这样的方式查找到第一个表记录,在嵌套查询下一个关联表,然后回溯到上一个表,在MySQL中是通过嵌套循环的方式实现

    从本质上说,MySQL对所有的类型的查询都以同样的方式运行。例如,MySQL在from子句中遇到子查询时,先执行子查询并将其结果放到一个临时表中,然后将这个临时表当做一个普通表对待。MySQL在执行union查询时也使用类似的临时表,在遇到右外连接的时候,MySQL将其改写成等价的左外连接,但也有例外情况无法通过嵌套循环和回溯的方式完成。

    5 执行计划

    和很多其他关系数据库不同,MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一颗指令树,然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果对某个查询执行explain extended后,在执行show warnings,就可以看到重构出的查询。

    热河夺标查询都可以使用一颗树表示:多表关联的一种方式

    在计算机科学中这被称为一颗平衡树。但是,这并不是MySQL执行查询的方式。正如我们前面章节介绍的,MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。所以,是一颗左侧深度优先树。

    MySQL如何实现多表关联:

    6 关联查询优化器

    MySQL优化器最重要的一部分就是关联查询优化,它决定了多个表关联时的顺序。通常夺标关联的时候,可以有多重不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。所以sql中书写的关联顺序不一定就是生成执行计划时的关联顺序,这个顺序也不一定就是不变的。

    重新定义关联的顺序是优化器非常重要的一部分功能。不过有时候,优化器给出的并不是最优的关联顺序。这时可以使用straight_join关键字重写查询,让优化器按照你认为最优的关联顺序执行。

    关联优化器会尝试在所有的关联顺序中选一个执行成本最小的来生成执行计划书。如果可能,优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本,最后返回一个最优的执行计划。

    不过,如果有n个表的关联,那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的收缩空间,搜索空间的增长速度非常快;例如如果是10个表的关联那么共有3628800种不同的关联顺序。当搜索空间非常大的时候,优化器不可能注意评估每一种关联顺序的成本,这时,优化器选择使用贪婪搜索的方式查找最优的关联顺序。实际上,当关联的表超哥optimizer_search_depth的限制的时候,就会选择贪婪搜索模式了。

    在MySQL这些年的发展过程中,优化器积累了很多启发式的优化策略来加速执行计划的生成。绝大多数情况下,这都是有效的,但因为不会去计算每一种关联顺序的成本,所以偶尔也会选择一个不是最优的执行计划。

    有时,每个查询的顺序并不能随意安排,这时关联优化器可以根据这些规则大大减少搜索空间,例如左连接。这是因为,后面的表的查询需要依赖于前面表的查询结果。这种依赖关系通常可以帮助优化器大大减少需要扫描的执行计划数量。

    7 排序优化

    无论如何排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或尽可能避免对大量数据进行排序。

    之前我们已经看过如何通过索引进行排序。当不能使用索引生成排序结果的时候,MySQL需要自己进行排序,如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程同一称为文件排序,即使完全是内存排序不需要任何磁盘文件时也是如此。

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

    MySQL有如下两种排序算法

    ① 两次传输排序(旧版本)

    读取行指针和需要排序的字段,对其进行排序,然后在根据排序结果读取所需要的数据行。

    这需要进行两次数据传输,及需要从数据表中读取两次数据,第二次读取数据的时候,因为是读取排序列进行排序后的所有记录,这会产生大量的随机I/O,所以两次数据传输的成本非常高。当使用的是MyISAM表的时候,成本可能会更高,因为MyISAM使用系统调用进行数据的读取,MyISAM非常依赖操作系统对数据的缓存。不过这样做的优点是,在排序的时候存储尽可能少的数据,这就让排序缓冲区中可能容乃尽可能多的行数进行排序。

    ② 单词传输排序(新版本)

    先读取查询所需要的所有列,然后在根据给定列进行排序,最后直接返回排序结果。

    这个算法只在4.1版本以后引入。因为不在需要从数据表中读取两次数据,对于I/O密集型应用,这样做的效率高了很多。另外,相比两次传输排序,这个算法只需要一次顺序I/O读取所有的数据,而无需任何的随机I/O。缺点是如果需要返回的列非常多、非常大,会占用大量的空间,而这些列队排序操作本身来说是没有任何作用的。因为单条排序记录很大,所以可能会有更多的排序块需要合并。

    很难说那个排序效率更高,两种算法都有各自最好的最糟的场景。当查询需要所有列的总长度不超过参数max_length_for_sort_data时,MySQL使用单词传输排序,可以通过调整这个参数来影响MySSQL排序算法的选择。

     

    MySQL在进行文件排序的时候需要使用的临时存储空间可能会比相像的大的多。因为在MySQL排序时,对每一个排序记录都会分配一个足够长的定长空间来存放。这个定长空间必须足够长才容纳其中最长的字符串,例如,如果是varchar列则需要分配其完整长度;如果使用UTF-8字符集,那么MySQL将会为每个字符预留三个字节。我们曾经在一个库表结构设计不合理的案例中看到,排序消耗的临时空间比磁盘上原表要大很多倍。

    在关联查询的时候如果需要排序,MySQL会分两种情况来处理这样的文件排序。如果order by子句中的所有列都来自关联的第一个表,那么MySQL在关联处理第一个表,那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样,那么在MySQL的explain结果中可以看到Extra字段会有Using filesort。除此之外的所有情况,MySQL都会先将关联的结果存放到一个临时表中,然后在所有的关联都结束后,在进行文件排序。这种情况下,在MySQL的explain结果的Extra字段都可以看到Using temporary;Using filesort。如果查询中有limit的话,limit也会在排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量任然会非常大。

    MySQL5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候,例如使用了limit子句,MySQL不在对所有结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后在进行排序。

    四 查询执行引擎

    在解析和优化截断,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构,而不是和很多其他的关系型数据库那样会生成对应的字节码。

    相对于查询优化截断,查询执行截断不是那么复杂:MySQL只是简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中,有大量的操作需要通过调用存储引擎实现的接口来完成,这些接口也就是我们成为handler API的接口。查询中的每一个表由一个handler的实例标识。前面我们有意忽略了这点,实际上,MySQL在优化截断就为每个表创建了一个handler实例,优化器根据这些实际的接口可以获取表的相关信息,包括表的所有列名、索引统计信息等。

    存储引擎接口有着非常丰富的功能,但是底层接口却只有几十个,这些接口像搭积木一样能够完成查询的大部分操作。例如,有一个查询某个索引的第一行的接口,在有一个查询某个索引条目的下一个条目的功能,就可以完成全索引扫描工作了。这种简单的接口模式,让MySQL的存储引擎插件式架构成为可能,但也给优化器带来了一定的限制。

    执行查询,MySQL只需要重复执行计划中的各个操作,直到完成所有的数据查询。

    注意:并不是所有的操作都有handler完成。例如,当MySQL需要进行表锁的时候,handler可能会实现自己的级别的、更细粒度的锁,如InnoDB就实现了自己的行基本锁,但这并不能代替服务层的表锁。如果是所有存储引擎共有特性则由服务器层实现。

    五 返回结果给客户端

    查询执行的最后一个阶段但是将结果返回给客户端。即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数。

    如果查询可以被缓存,那么MySQL在这个阶段也会将结果存放到查询缓存中。

    MySql将结果集返回客户端是一个增量、逐步返回的过程。例如,前面的关联操作,一旦服务器处理完最后一个关联表,开始生成第一条结果时,MySQL就可以开始像客户端逐步返回结果集了。

    这样处理有两个好处:服务器端无需存储太多的结果集,也就不会因为结果集过多而消耗更多内存。另外,这样处理也让MySQL客户端第一时间获得返回的结果。

    结果集中每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送,在通过TCP协议进行传输,在TCP传输的过程中,可能对MySQL的封包进行缓存然后批量传输。

    展开全文
  • 在上一篇文章MySQL查询语句执行过程及性能优化-基本概念和EXPLAIN语句简介中介绍了EXPLAIN语句,并举了一个慢查询例子,本篇详细说明MySQL查询执行过程原理及优化方法。

    在上一篇文章MySQL查询语句执行过程及性能优化-基本概念和EXPLAIN语句简介中介绍了EXPLAIN语句,并举了一个慢查询例子:


    可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦。

    在优化这个语句之前,我们先了解下SQL查询的基本执行过程:

    *)应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析

    *)检查权限、MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(query plan),并可以被缓存

    *)如果存在索引,那么先扫描索引,如果数据被索引覆盖,那么不需要额外的查找,如果不是,根据索引查找和读取对应的记录

    *)如果有关联查询,查询次序是扫描第一张表找到满足条件的记录,按照第一张表和第二张表的关联键值,扫描第二张表查找满足条件的记录,按此顺序循环

    *)输出查询结果,并记录binary logs

    显然合适的索引将大大简化和加速查找。再看一下上面那条查询语句,除了条件查询外,还有关联查询以及ORDER BY即排序操作,

    那么让我们进一步了解下关联查询(JOIN)和ORDER BY是怎么工作的,MySQL有三种方式来处理关联查询和数据排序:


    第一种方法是基于索引,第二种是对第一个非常量表进行filesort(quicksort),还有一种是把联合查询的结果放入临时表,然后进行filesort。

    注1:关于什么是非常量表,请参考阅读MySQL开发手册:Consts and Constant Tables,
    注2:什么是filesort呢,这不是字面意思的文件排序,filesort有两种模式:
    1、模式1:排序后的元素涵盖了要输出的数据。排序结果是一串有序序列元素组,不再需要额外的记录读取;
    2、模式2:排序结果是<sort_key,row_id>键值对序列,通过这些row_ids再去读取记录(随机读取,效率低下);
    注3:关于什么是临时表,请参考阅读MySQL开发手册:How MySQL Uses Internal Temporary Tables


    第一种方法用于第一个非常量表中存在ORDER BY所依赖的列的索引,那就可直接使用已经有序的索引来查找关联表的数据,这种方式是性能最优的,因为不需要额外的排序动作:

    diagram

    第二种方式用于ORDER BY所依赖的列全部属于第一张查询表且没有索引,那么我们可以先对第一张表的记录进行filesort(模式可能是模式1也可能是模式2),得到有序行索引,然后再做关联查询,filesort的结果可能是在内存中,也可能在硬盘上,这取决于系统变量sort_buffer_size(一般为2M左右):

    diagram

    第三种方法用于当ORDER BY的元素不属于第一张表时,需要把关联查询的结果放入临时表,最后对临时表进行filesort:

    diagram

    第三种方法中的临时表,可能是在内存中(in-memory table),也可能是在硬盘上,一般是下面两种情况会使用硬盘(on-disk table):

    (1)使用了BLOB,TEXT类型的数据

    (2)内存表占用超过了系统变量tmp_table_size/max_heap_table_size的限定(一般为16M左右),只能放在硬盘上

    从上面的查询执行过程和方式,我们应该可以清楚的知道为什么Using filesort,Using temporary会严重的影响查询性能,因为如果数据类型或者字段设计有问题,

    在需要查询的表以及结果中存在大数据的字段,而没有合适的索引可用时,都可能会导致产生大量的IO操作,这就是查询性能缓慢的根源所在。


    回到文章开头所举的查询实例,它显然是使用了效率最低的第三种方法,我们需要做和尝试的优化手段有:

    1、为users.fl_no添加索引,为select和where所使用的字段建立索引

    2、把users.fl_no转移到或者作为冗余字段添加到表user_profile中

    3、去除TEXT类型的字段,TEXT可以替换为VARCHAR(65535)或对于中文而言VARCHAR(20000)

    4、如果实在无法消除Using filesort,那么提高sort_buffer_size,以减少IO操作负担

    5、尽量使用第一张表所覆盖的索引进行排序,实在不行,可以把排序逻辑从MySQL中移到PHP/Java程序中执行

    实施1、2、3的优化方法后,EXPLAIN结果如下:


    备注:编写简单的PHP应用,用siege测试,查询效率提高>3倍。



    by iefreer



    展开全文
  • 查询优化

    千次阅读 2018-07-11 18:30:07
    查询优化 概述 关系系统和关系模型是两个密切相关而有不同的概念。支持关系模型的数据库管理系统称为关系系统。但是关系模型中并非每一部分都是同等重要的,所以我们不苛求完全支持关系模型的系统才能称为关系...

    查询优化

    概述

    关系系统和关系模型是两个密切相关而有不同的概念。支持关系模型的数据库管理系统称为关系系统。但是关系模型中并非每一部分都是同等重要的,所以我们不苛求完全支持关系模型的系统才能称为关系系统。因此,我们给出一个关系系统的最小要求以及分类的定义。

    关系系统的定义

    • 1.支持关系数据库(关系数据结构)
      • 从用户观点看,数据库由表构成,并且只有表这一种结构。
    • 2.支持选择、投影和(自然)连接运算,对这些运算不必要求定义任何物理存取路径
      • 当然并不要求关系系统的选择、投影、连接运算和关系代数的相应运算完全一样,而只要求有等价的这三种运算功能就行。

    查询优化

    查询优化:对于给定的查询选择代价最小的操作序列,使查询过程既省时间,具有较高的效率,这就是所谓的查询优化。对于关系数据库系统,用户只要提出“做什么”,而由系统解决“怎么做”的问题。具体来说,是数据库管理系统中的查询处理程序自动实现查询优化。

    关系查询优化是影响RDBMS性能的关键因素。关系系统的查询优化既是RDBMS实现的关键技术又是关系系统的优点所在。

    查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率,而且在于系统可以比用户程序的“优化”做得更好。

    查询优化的一般准则

    • 1.选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条。它常常可使执行时节约几个数量级,因为选择运算一般使计算的中间结果大大变小
    • 2.在执行连接前对关系适当地预处理。预处理方法主要有两种,在连接属性上建立索引和对关系排序 。
    • 3.把投影运算和选择运算同时进行。如有若干投影和选择运算,并且它们都对同一个关系操作,则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。
    • 4.把投影同其前或其后的双目运算结合起来,没有必要为了去掉某些字段而扫描一遍关系
    • 5.杷某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算,连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间
    • 6.找出公共子表达式。
      这里写图片描述
    展开全文
  • mysql千万级数据量根据索引优化查询速度

    万次阅读 多人点赞 2016-08-15 18:05:16
    (一)索引的作用 ...提升查询速度的方向一是提升硬件(内存、cpu、硬盘),二是在软件上优化(加索引、优化sql;优化sql不在本文阐述范围之内)。 能在软件上解决的,就不在硬件上解决,毕竟硬件提升...
  • 嵌套查询查询优化

    万次阅读 2016-03-31 19:21:26
    嵌套查询查询优化 Table of Contents 1. 嵌套查询的分类和优化概述2. Kim: On Optimizing an SQL-like Nested Query 2.1. 嵌套查询的分类 2.1.1. A 类2.1.2. N 类2.1.3. J 类2.1.4. JA 类2.1.5. D ...
  • 关系查询处理和查询优化

    千次阅读 2016-12-07 15:44:33
    关系查询处理和查询优化
  • 如果不是,那么又该如何对该查询树进行优化?而优化所使用的策略正是本节要讨论的重点内容,而且优化部分也是整个查询引擎的难点。 子链接(SubLink)如何优化?子查询(SubQuery)又如何处理?对表达式...
  • 1次查询优化过程

    千次阅读 2014-05-07 14:13:46
    基于拥有CRM系统进行扩展的过程,有一个工单的操作,数据库中tc_worksheettc_worksheet_1_1 tc_worksheet_attr tc_worksheet_mattr tc_worksheet_xattr。工单数据同时存储在以上几张表中。   最开始的实现方案:...
  • oracle 查询优化

    千次阅读 2019-01-10 14:45:21
    oracle查询优化(一) 1、IN 操作符 用 IN 写出来的 SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。 但是用 IN 的 SQL 性能总是比较低的,从 ORACLE 执行的步骤来分析用 IN 的 SQL 与不用 IN 的...
  • Oracle大数据常见优化查询

    万次阅读 2017-07-23 15:29:55
    1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select ...
  • 对于普通类型的子查询,MySQL在上拉子查询,提供了多种优化策略供查询优化器利用代价估算进行选择,这种方式细化了子查询优化后期阶段的处理过程,目的是提高查询的效率。 1 子查询处理的情况 MySQL支持的子查询...
  • 优化查询步骤总结

    千次阅读 2019-05-01 20:07:41
    查询调优。 1.索引本身:有无对应索引,联合索引的所有列是否能都被用到,字符串索引是否满足最左,联合索引是否区分度高的在前,索引是不是太多了影响插入,能否能用索引...3.查询优化:例如前公司的最佳实践...
  • SQL查询优化

    万次阅读 多人点赞 2019-05-03 12:53:51
    SQL查询优化 一、获取有性能问题SQL的三种方法 通过用户反馈获取存在性能问题的SQL 通过慢查询日志获取存在性能问题的SQL 实时获取存在性能问题的SQL 二、慢查询日志介绍 1、使用慢查询日志获取有性能问题...
  • 优化分页查询

    千次阅读 2020-06-01 13:46:41
    很多时候,我们写分页查询的时候,只是单纯的想把结果查询出来就好了,但是有没有想过,自己写的分页查询效率会怎么,数据少的是没太大影响,但是多了就会有影响了,所以这篇简单介绍下分页查询的一些基本优化 ...
  • Presto查询优化

    千次阅读 2018-01-17 09:53:25
    Presto是一个开源的分布式SQL查询引擎,适用于交互...笔者在多个项目中用到Presto做即席查询,总结了一些优化措施。 一、数据存储 合理设置分区 与Hive类似,Presto会根据元信息读取分区数据,合理的分区能减少P
  • MySQL的查询优化——[面试系列]

    千次阅读 2018-11-02 12:14:27
    查找分析查询速度慢的原因: ...优化查询过程中的数据访问: 查询数据太多导致查询性能下降;确定应用程序是否在检索大量超过需要的数据,可能是太多行或列;确认MySQL服务器是否在分析大量不必要的数据...
  • MySQL查询优化

    千次阅读 2019-08-04 17:28:02
    为什么查询会慢? 查询慢的原因可能很多,...查询优化 一个查询根据不同的场景,一般可以通过下面的方式来进行优化: 使用COUNT * 在使用COUNT统计行数时,COUNT(*)是性能最高的。 使用LIMIT 有时候查询并不需要...
  • 记一次MySQL分组查询优化过程

    千次阅读 2020-08-15 10:29:50
    优化方向: ①给md5_code、nuclear_time字段加索引。 ②给sql语句后面加order by null。 ③调整where条件里字段的查询顺序,有索引的放前面。 ④给所有where条件的字段加组合索引。 ⑤用子查询的方式,先查...
  • 数据库查询优化

    2017-04-26 09:54:33
    根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小,使用字节数小的列建索引号,不要对有限的几个值得列建单一索引。 用OR的字句可以...
  • mysql 使用force index优化查询

    千次阅读 2016-04-27 10:14:22
    一个简单查询语句,添加一个条件以后,查询速度慢得不能接受,使用force Index 优化查询
  • redis查询优化

    千次阅读 2019-07-12 10:36:39
      最近公司运维提示线上redis出现报警,排查下来是redis存储的value过大导致查询的时候耗时过高。虽然redis最大限制可以存储512M,但是当单key过大时,每一次访问都会造成redis阻塞,由于redis是单线程的,其他...
  • PostgreSQL支持子查询优化,只是把子查询细分为两种情况: 子链接(sublink):子查询的特例,只是子查询语句嵌入在表达式中。 子查询(subquery):子查询语句不嵌入在表达式中,独立存在于SQL语句中。 这两种...
  • MySQL多表查询核心优化

    万次阅读 多人点赞 2016-03-22 17:00:33
    在一般的项目开发中,对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始。
  • Vertica 查询优化

    2018-08-10 11:22:43
    Vertica 查询优化  vertica是惠普公司推出的列式分布式数据库,在OLAP领域有其独到的地方,... 所谓的数据库调优、程序优化之类的工作,实际上是一个解决问题的过程,而解决问题,第一部就是需要定位问题。找到...
  • 关系数据库的查询优化

    千次阅读 2018-07-17 16:29:43
    关系查询优化是影响关系数据库管理系统性能的关键因素。 一、查询优化概述 查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较高的效率,而且在于系统可以比用户程序的“优化”做得更好。 1. 优化...
  • 优化系列 | DELETE子查询改写优化

    千次阅读 2016-07-06 10:37:25
    有个采用子查询的DELETE执行得非常慢,改写成SELECT后执行却很快,最后把这个子查询DELETE改写成JOIN优化过程 1、问题描述 朋友遇到一个怪事,一个用子查询的DELETE,执行效率非常低。把DELETE改成SELECT后...
  • 海量数据查询优化

    千次阅读 2013-07-18 11:11:25
    今天下午去微软面试,被问到了海量...数据库优化查询计划的方法 数据库系统是管理信息系统的核心,基于数据库的联机事务处理(OLTP)以及联机分析处理(OLAP)是银行、企业、政府等部门最为重要的计算机应用之一。从大
  • 效率分析关键词:explain + SQL语句 一,最常见MYSQL最基本的分页方式limit: select * from `table` order by id desc ...随着数据量的增加,页数会越来越多,在数据慢慢增长的过程中,可能就会出现limit 10000,2

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 433,751
精华内容 173,500
关键字:

优化查询的过程