百度网站分析工具,北京欢迎您,网站怎么做现场直播视频,广西南宁最新确诊名单査询优化、索引优化、库表结构优化需要齐头并进#xff0c;一个不落。 一、为什么查询速度为变慢
在尝试编写快速的查询之前,需要清楚一点,真正重要是响应时间。如果把查询看作是一个任务#xff0c;那么他由一系列子任务组成#xff0c;每个子任务都会消耗一定的时间。如果… 査询优化、索引优化、库表结构优化需要齐头并进一个不落。 一、为什么查询速度为变慢
在尝试编写快速的查询之前,需要清楚一点,真正重要是响应时间。如果把查询看作是一个任务那么他由一系列子任务组成每个子任务都会消耗一定的时间。如果要优化查询实际上要优化其子任务要么消除其中一些子任务要么减少子任务的执行的次数要么让子任务运行得更快。
MySQL在执行查询的时候有哪些子任务。哪些子任务运行的速度很慢,这里很难给出完整的列表通常来说查询的生命周期大致可以按照顺序来看从客户端到服务器然后再服务器上进行解析生成执行计划执行并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理包括排序、分组等。
在完成这些任务的时候查询需要在不同的地方花费时间包括网络CPU计算生成统计信息和执行计划、锁等待互斥等待等操作尤其是向底层存储引擎检索数据的调用操作这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间根据引擎不同可能还会产生大量的上下文切换以及系统调用。
在每一个消耗大量时间的查询案例中我们都能看到一些不必要的额外操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询的目的就是减少和消除这些操作所花费的时间。有了这些 概念我们再一起来看看如何优化査询。
二、慢查询基础:优化数据访问 査询性能低下最基本的原因是访问的数据太多。 大部分性能低下的査询都可以通过减少访问的数据量的方式进行 优化。对于低效的査询我们发现通过下面两个步骤来分析总是很有效
确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量超过需要的数据行。
2.1、是否向数据库请求了不需要的数据
有些査询会请求超过实际需要的数据然后这些多余的数据会被应用程序丢弃。这会给MySQL服务器带来额外的负担并增加网络开销,另外也会消耗应用服务器的CPU 和内存资源。
2.2、MySQL是否在扫描额外的记录
最简单衡量查询开销的三个指标
响应时间分为服务时间和排队时间 服务时间是指数据库处理这个查询真正花了多长时间排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——坑内是等I/O操作完成也可能使行锁等等 扫描的行数返回的行数
在EXPLAIN语句中的type列反应了访问的类型。访问类型有很多种从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些速度是从慢到快扫描的行数也是小到大。你不需要记住这些访问类型但是要明白扫描表扫描索引范围访问和单值访问的概念。如果查询没有办法找到合适的访问类型那么最好的办法通常就是增加一个合适的索引。
一般MySQL能够使用如下三种应用WHERE条件从好到坏依次为
在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。使用索引覆盖扫描来返回记录直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的但无须在回表查询记录。从数据表中返回数据然后过滤不满足条件的记录。这是在MySQL服务器层完成MySQL需要先从数据表读出记录然后过滤。
如果说发现查询需要扫描大量的数据但只返回少数的行那么通常可以尝试下面的技巧去优化它 使用索引覆盖扫描把所有需要用到的列都放到索引中这样存储引擎无须回表获取对应行就可以返回结果 改变库表结构。例如使用单独的汇总表 重写这个复杂的查询让MySQL优化器能够以更优化的方式执行这个查询
三、重构查询方式
3.1 一个复杂查询还是多个简单查询
MySQL内部每秒能够扫描内存中上百万行数据相比之下MySQL响应数据给客户端 就慢得多了。在其他条件都相同的时候使用尽可能少的査询当然是更好的。但是有时候, 将一个大査询分解为多个小査询是很有必要的。
3.2 切分查询将大查询切分成小查询每个查询完全一样只完成一小部分每次只返回一小部分查询结果
有时候对于一个大査询我们需要“分而治之”将大査询切分成小査询每个査询功能 完全一样只完成一小部分每次只返回一小部分査询结果。
删除旧的数据就是一个很好的例子。定期地清除大量数据时如果用一个大的语句一次 性完成的话则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞 很多小的但重要的査询。将一个大的DELETE语句切分成多个较小的查询可以尽可能小地 影响MySQL性能同时还可以减少MySQL复制的延迟。
3.3 分解关联查询
对每一个表进行一次单表查询然后再应用程序中进行关联例如
mysql SELECT * FROM tag
- JOIN tag_post ON tag_post.tag_idtag.id
- JOIN post ON tag_post.post_idpost.id
- WHERE tag.tagmysql;可以分解成下面的语句来代替
mysql SELECT * FROM tag WHERE tagmysql;
mysql SELECT * FROM tag_post WHERE tag_id1234
mysql SELECT * FROM post WHERE post.id in (123,456,567,9098,8904)使用分解关联查询的方式重构查询有如下的优化 让缓存的效率更高。 将查询分解后执行单个查询可以减少锁的竞争。 在应用层做关联可以更容易的对数据库进行拆分更容易做到高性能和可扩展。 查询本身效率也可能会有所提升。 可以减少冗余记录的查询。 这样做相当于在应用中实现了哈希关联而不是使用MySQL的嵌套循环关联。
四、查询执行的基础
查询执行路径 步骤
客服端发送一条查询给服务器服务器先检查查询缓存如果命中缓存则立刻返回存储在缓存中的结果。否则进入下一个阶段。服务器端进行SQL解析、预处理在由优化器生成对应的执行计划。MySQL根据优化器生成的执行计划调用存储引擎的API来执行查询将结果返回给客户端
4.1 MySQl客户端/服务器通信协议
4.1.1、MySQL客户端和服务器之间的通讯是”双半工“的这意味着在任何一个时刻要么是由服务器向客户端发送数据要么是由客户端向服务器发送数据这两个动作不能同时发生。
4.2.2、查询状态对于一个MySQL连接或者说一个线程任何时刻都有一个状态表示MySQL当前在做什么。我们使用最简单的SHOW FULL PROCESSLIST命令该命令返回结果中的Command列就表示当前的状态来查询。下面将这些状态列出来并做一个简单的解释
Sleep线程正在等待客户端发送新的请求。Query线程正在执行查询或者正在将结果发送给客户端。Locked在MySQL服务器层该线程正在等待表锁。Analyzing and statistics : 线程正在收集存储引擎的统计信息并生成查询的执行计划。Coping to tmp table [on disk]线程正在执行查询并且将其结果都复制到一个临时表中这种状态一般要么是在做GROUP BY操作要么是文件排序操作或者是UNION操作。如果这个状态后面还有on disk标记那表示MySQL正在讲一个内存临时表放到磁盘上。Sorting result线程正在对结果集进行排序。Sending data这表示多种情况线程可能在对多个状态之间传输数据或者而在生成结果集或者在向客户端返回数据。
4.2 查询缓存
在解析一个查询语句之前如果查询缓存是打开的那么MYSQL会优先检查这个查询是否命中查询缓存中的数据。
这个检查是通过一个对大小写敏感的哈希查找的。查询和缓存中的查询即使只有一个不同也不会匹配缓存结果。如果命中缓存那么在但会结果前MySQL会检查一次用户权限有权限则跳过其他步骤直接返回数据
4.3 查询优化处理
查询的生命周期的下一步是将一个SQL转换成执行计划MySQL再依照这个执行计划和存储引擎进行交互。
4.3.1 语法解析器和预处理
MySQL解析器将使用MySQL语法规则验证和解析查询。例如验证是否使用错误的关键字、关键字顺序、引号前后是否匹配等预处理器则根据一些MySQL 规则进一步解析树是否合法例如检查数据表和数据列是否存在解析名字和别名是否有歧义等
4.3.2 查询优化器
一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划
有很多中原因导致MySQL优化器选择错误的计划如下所示
统计信息不准确MySQL依赖存储引擎提供的统计信息来评估成本但是有的存储引擎提供的信息偏差有点大例如InnoDB因为其MVCC的架构并不能维护一个数据表的行数的精确统计信息
执行计划中的成本估算不等于实际的操作成本MySQL的最优可能和你想的最优不一样MySQL从不考虑其他并发执行的查询MySQL也并不是任何时候都是基于成本的优化MySQL不会考虑不受其控制的操作成本。例如执行存储过程或者用户自定义函数的成本优化器有时间无法估算所有可能的执行计划
MySQL的查询优化器使用很多策略来生成一个最优的执行计划。
优化策略可以简单的分为两种
静态优化 静态优化可以直接对解析树进行分析并完成优化。例如优化器可以通过简单的代数变化将WHERE条件转换成另外一种等价形式静态优化在第一次完成后就一直有效即使使用不同的参数重复执行查询也不会变化。可以认为是一种”编译时优化“动态优化和查询的上下文有关也可能和其他因素有关例如WHERE中取值、索引中条目对应的数据行数等。这需要在每次查询的时候重新评估可以让那位u是”运行时优化“。
MySQL能够处理的优化类型部分 重新定义关联表顺序 将外连接转化成内连接 使用等价变换规则 优化COUNT() 、MIN() 、 MAX() 预估并转换为常数表达式 覆盖索引扫描 子查询优化 提前终止查询 等值传播 列表IN()的比较
4.3.3 数据和索引的统计信息
在服务器层有查询优化器却没有保存数据和索引的统计信息。统计信息由存储引擎实现不同的存储引擎可能会存储不同的统计信息有的引擎根本不存储任何统计信息例如Archive引擎。
因为服务器层没有任何统计信息所有MySQL查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息优化器根据这些信息来选择一个最优的执行计划。
4.3.4 MySQL如何执行关联查询
MySQL中“关联”认为任何一个查询都是一次“关联”并不仅仅是一个查询需要到两个表匹配才叫关联。素以在MySQL中每一个查询每一个片段包括子查询甚至于单表的SELECT都可能是关联。
MySQL关联查询的策略很简单MySQL对任何关联都执行嵌套循环关联操作即MySQL先在要给表中循环取出单条数据然后再嵌套循环到下一个表中寻找匹配的行依次下去直到找到所有表中匹配的行为止。然后根据各个表的行返回查询中需要的各个列。
4.3.5 执行计划
和很多其他关系数据库不同MySQL并不会生成查询字节码来执行查询。MySQL生成查询的一颗指令树然后通过存储引擎执行完成这颗树并返回结果
4.3.6 关联查询优化器
如果优化器给出的并不是最优的关联顺序这时可以使用STRAIGHT_JOIN关键字重写查询让优化器按照你认为最优的关联顺序执行——不过老实说人的判断很难那么精准。绝大多数时候优化器做出的选择都比普通人的判断更精准。
如果超过N个表的关联那么需要检查N的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间‘当搜索空间非常大的时候优化器选择使用”贪婪“搜索方式查找”最优’的关联顺序。当关联的表超过optimizer_search_depth的限制的时候就会选择“贪婪”搜索模式了。
4.3.7 排序优化
排序优化无论如何排序都是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。尽量通过索引进行排序。当不能使用索引生成排序结果的时候MySQL需要自己进行排序如果数据量小则在内存中进行如果数量大则需要使用磁盘不过MySQL将这个过程统一称为文件排序即使完全是内存排序不需要任何磁盘文件时也是如此。
MySQL有如下两种排序算法 两次传输排序(旧版本使用)读取行指针和需要排序的字段对其进行排序然后再根据排序结果读取所需要的数据行。需要进行两次传输即需要从数据表中读取两次数据第二次读取数据的时候因为是读 取排序列进行排序后的所有记录。这回产生大量的随机IO。 单次传输排序(新版本使用)先读取查询所需要的所有列然后在根据给定列进行排序最后直接返回排序结果。效率更高但占用内存更大。 如果查询中有LIMIT的话LIMIT也会在排序之后应用的所以即使需要返回较少的数据临时表和需要排序的数据量仍然后非常大。貌似5.6版本有所改进会先抛弃不满足条件的记录然后再进行排序。
4.4 查询执行引擎
在解析和优化阶段MySQL将生成查询对应的执行计划MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构而不是和很多其他的关系型数据库那样会生成对应的字节码。
4.5 返回结果给客户端
即使查询不需要返回结果集给客户端MySQL仍然会返回这个查询的一些信息如查询影响到的行数。如果查询可以被缓存那么MySQL在这个阶段也会将结果存放到缓存中。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。开始生成第一条结果时MySQL就开始向客户端逐步返回结果集了。 五、 优化特定类型的查询
5.1、优化COUNT()查询
1). COUNT()是一个特殊的函数有两种非常不同的作用它可以统计某个列值的数量也可以统计行数。在统计列值的时候要求列值是非空的(不统计NULL)。如果COUNT()的括号中指定了列或者列的表达式则
统计的就是这个表达式有值的结果数。最简单的就是我们使用count(*)的时候这种情况下通配符*并不会向我们猜想的那样扩展所有的行实际上它会忽略所有的值而直接统计所有的行数。
2). 使用近似值有时候某些业务场景并不要求完全精确的COUNT值此时可以用近似值来代替。
3). 更复杂的优化覆盖索引增加汇总表等。**
5.2 优化关联查询
1). 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用到列C关联的时候如果优化器关联顺序是B、A那就不需要在B表的对应列上建立索引。没有用到的索引只会
2). 确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列。这样MySQL才有可能使用索引来优化这个过程。**
5.3 优化子查询
关于优化子查询我们给出的最重要的优化建议就是尽可能使用关联查询代替至少当前MySQL版本需要这样。
5.4 优化GROUP BY和DISTINCT
1). 它们都可以使用索引来优化这也是最有效的方法。
2). 在MySQL中当无法使用索引的时候GROUP BY使用两种策略来完成使用临时表或文件排序来做分组。对于任何查询语句这两种策略的性能都有可以提升的地方。可以通过使用提示SQL_BIG_RESULT和
SQL_SMALL_RESULT来让优化器按你希望的方式运行。
3). 如果需要对关联查询分组(GROUP BY)并且是按照查找表中的某个列进行分组那么通常采用查找表的标识列分组的效率比其他列更高。**
4). 如果没有通过ORDER BY子句显式地指定排序列当查询使用GROUP BY 子句的时候结果集会自动按照分组的列进行排序。如果不关心结果集的顺序而这中默认排序又导致了需要文件排序则可以使用
ORDER BY NULL让MySQL文件不再进行排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字使分组的结果集按照需要的方向排序。
5). 优化GROUP BY WITH ROLLUP分组查询的一个变种思想就是要求MySQL对返回的分组结果再做一次超级聚合。最好的办法尽可能的将WITH ROLLUP 功能转移到应用程序中处理。**
5.5 优化LIMIT分页
1). 使用索引
2). 要优化这种查询要么是在页面中限制分页的数量要么是优化大偏移量的性能。
3). 尽肯能的使用索引覆盖
4). 延迟关联
5). 有时候也可以将LIMIT查询转换为已知位置的查询让MySQL通过范围扫描找到对应的结果。
6). 其他优化办法还包括使用预先计算的汇总表或者关联一个冗余表冗余表只包含主键列和需要做排序的数据列。
5.6 优化SQL_CALC_FOUND_ROWS
分页的时候另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示(hint)这样就可以获得去掉LIMIT以满足条件的行数因此可以作为分页的总数。
用业务的手段解决下一页获取更多数据等。
5.7 优化UNION查询
1). MySQL总是通过创建填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE,LIMIT,ORDER BY等子句下推到UNION的各个子查询中以
便优化器可以充分利用这些条件进行优化。
2). 除非确实需要服务器消除重复的行否则就一定要使用UNION ALL这一点很重要。如果没有ALL关键字MySQL会给临时表加上DISTINCT选项这回导致对临时表做唯一性检查。这样做的代价非常高
即使有ALL关键字MySQL仍然会使用临时表存储结果。事实上MySQL总是经结果放入临时表然后再读出再返回给客户端。**
5.8 静态查询分析
Percona Toolkit中的pt-query-advisor 能够解析查询日志、分析查询模式然后再给出所有可能存在的潜在问题的查询并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康
检查它能检测出很多问题。
六、总结
如果把创建高性能应用程序比作是一个环环相扣的“难题”除了前面介绍的schema. 索引和査询语句设计之外査询优化应该是解开“难题”的最后一步了。要想写一个好 的査询你必须要理解schema设计、索引设计等反之亦然。
理解査询是如何被执行的以及时间都消耗在哪些地方这依然是前面我们介绍的响应时 间的一部分。再加上一些诸如解析和优化过程的知识就可以更进一步地理解上一章讨 论的MySQL如何访问表和索引的内容了。这也从另一个维度帮助读者理解MySQL在 访问表和索引时査询和索引的关系。
参考
《高性能 MySQL 第三版》
