百合怎么doi怎么做网站,市场营销案例100例及答案,精仿腾讯3366小游戏门户网站源码织梦最新内核带全部数据!,南京浦口住房与城乡建设局网站5.6 再深入查询优化
5.6.1. 全局考虑性能优化
5.6.3.1. 为什么查询速度会慢
在尝试编写快速的查询之前#xff0c;需要清楚一点#xff0c;真正重要是响应时间。如果把查询看作是一个任务#xff0c;那么它由一系列子任务组成#xff0c;每个子任务都会消耗一定的时间。…5.6 再深入查询优化
5.6.1. 全局考虑性能优化
5.6.3.1. 为什么查询速度会慢
在尝试编写快速的查询之前需要清楚一点真正重要是响应时间。如果把查询看作是一个任务那么它由一系列子任务组成每个子任务都会消耗一定的时间。如果要优化查询实际上要优化其子任务要么消除其中一些子任务要么减少子任务的执行次数要么让子任务运行得更快。
MySQL 查询的生命周期大致可以按照顺序来看:从客户端到服务器然后在服务器上进行解析生成执行计划执行并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理包括排序、分组等。
在完成这些任务的时候查询需要在不同的地方花费时间包括网络CPU计算生成统计信息和执行计划、锁等待互斥等待等操作尤其是向底层存储引擎检索数据的调用操作,这些调用需要在内存操作,CPU 操作和内存不足时导致的 IO 操作上消耗时间。根据存储引擎不同可能还会产生大量的上下文切换以及系统调用。 优化查询的目的就是减少和消除这些操作所花费的时间。
5.6.3.2. 查询执行的流程再回顾
当希望 MySQL 能够以更高的性能运行查询时最好的办法就是弄清楚MySQL 是如何优化和执行查询的。一旦理解这一点很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理的方式运行。
换句话说是时候回头看看我们前面讨论的内容了:MySQL 执行一个查询的过程。根据下图我们可以看到当向 MySQL 发送一个请求的时候MySQL 到底做了些什么: 1.客户端发送一条查询给服务器。 2服务器先检查查询缓存如果命中了缓存则立刻返回存储在缓存中的结果。否则进入下一阶段。 3服务器端进行 SQL 解析、预处理再由优化器生成对应的执行计划。 4. MySQL 根据优化器生成的执行计划调用存储引擎的 API 来执行查询。 5将结果返回给客户端。
上面的每一步都比想象的复杂查询优化器固然是其中特别复杂也特别难理解的部分。但是其他部分就对查询的性能毫无影响
5.6.3.3. MySQL 客户端/服务器通信协议
一般来说不需要去理解 MySQL 通信协议的内部实现细节只需要大致理解通信协议是如何工作的。MySQL 客户端和服务器之间的通信协议是“半双工”的这意味着在任何一个时刻要么是由服务器向客户端发送数据要么是由客户端向服务器发送数据这两个动作不能同时发生。所以我们无法也无须将一个消息切成小块独立来发送。
这种协议让 MySQL 通信简单快速但是也从很多地方限制了 MySQL。一个明显的限制是这意味着没法进行流量控制。一旦一端开始发生消息另一端要接收完整个消息才能响应它。这就像来回抛球的游戏﹔在任何时刻只有一个人能控制球而且只有控制球的人才能将球抛回去发送消息)。
客户端用一个单独的数据包将查询传给服务器。这也是为什么当查询的语句很长的时候参数 max_allowed_packet 就特别重要了。一旦客户端发送了请求它能做的事情就只是等待结果了。
相反的一般服务器响应给用户的数据通常很多由多个数据包组成。当服务器开始响应客户端请求时客户端必须完整地接收整个返回结果而不能简单地只取前面几条结果然后让服务器停止发送数据。这种情况下客户端若接收完整的结果然后取前面几条需要的结果或者接收完几条结果后就“粗暴”地断开连接都不是好主意。这也是在必要的时候一定要在查询中加上 LIMIT 限制的原因。
换一种方式解释这种行为:当客户端从服务器取数据时看起来是一个拉数据的过程但实际上是 MySQL 在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据客户端也没法让服务器停下来。
多数连接 MySQL 的库函数都可以获得全部结果集并缓存到内存里还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL 通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源所以接收全部结果并缓存通常可以减少服务器的压力让查询能够早点结束、早点释放相应的资源。
当使用库函数从 MySQL 获取数据时其结果看起来都像是从 MySQL 服务器获取数据而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题但是如果需要返回一个很大的结果集的时候这样做并不好因为库函数会花很多时间和内存来存储所有的结果集。
对于 Java 程序来说很有可能发生 OOM所以 MySQL 的 JDBC 里提供了setFetchSize() 之类的功能来解决这个问题
1、当 statement 设置以下属性时采用的是流数据接收方式每次只从服务器接收部份数据直到所有数据处理完毕不会发生 JVM OOM。
setResultSetType(ResultSet.TYPE_FORWARD_ONLY);
setFetchSize(Integer.MIN_VALUE)2、调用 statement 的 enableStreamingResults 方法实际上 enableStreamingResults 方法内部封装的就是第 1 种方式。
3、设置连接属性 useCursorFetchtrue (5.0 版驱动开始支持)statement 以 TYPE_FORWARD_ONLY 打开再设置 fetch size 参数表示采用服务器端游标每次从服务器取 fetch_size 条数据。
比如
con DriverManager.getConnection(url);
ps (PreparedStatement)
con.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setFetchSize(Integer.MIN_VALUE);
ps.setFetchDirection(ResultSet.FETCH_REVERSE);
rs ps.executeQuery();
while (rs.next()) {……实际的业务处理}5.6.3.4. 查询状态
对于一个 MySQL 连接或者说一个线程任何时刻都有一个状态该状态表示了 MySQL 当前正在做什么。在一个查询的生命周期中状态会变化很多次。
5.6.3.5. 查询优化处理
查询的生命周期的下一步是将一个 SQL 转换成一个执行计划MySQL 再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析 SQL、预处理、优化 SQL 执行计划。这个过程中任何错误例如语法错误都可能终止查询。在实际执行中这几部分可能一起执行也可能单独执行。
我们通过前面的学习可以看到 MySQL 的查询优化器是一个非常复杂的部件它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种一种是静态优化一种是动态优化。静态优化可以直接对解析树进行分析并完成优化。例如优化器可以通过一些简单的代数变换将 WHERE 条件转 换成另一种等价形式。静态优化不依赖于特别的数值如 WHERE 条中带入的一些常数等。静态优化在第一次完成后就一直有效即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。
相反动态优化则和查询的上下文有关也可能和很多其他因素有关例如WHERE 条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估可以认为这是“运行时优化”。 优化器是相当复杂性和智能的。建议大家“不要自以为比优化器更聪明”。 如果没有必要不要去干扰优化器的工作让优化器按照它的方式工作。尽量按照优化器的提示去优化我们的表、索引和 SQL 语句比如写查询或者重新设计更优的库表结构或者添加更合适的索引。但是请尽可能的保持 SQL 语句的简洁SQL 语句变得很复杂的情况下请相信我维护会成为一个地狱。而带来的最终的收益微乎其微。
当然虽然优化器已经很智能了但是有时候也无法给出最优的结果。有时候你可能比优化器更了解数据例如由于应用逻辑使得某些条件总是成立﹔还有时优化器缺少某种功能特性如哈希索引﹔再如前面提到的从优化器的执行成本角度评估出来的最优执行计划实际运行中可能比其他的执行计划更慢。 如果能够确认优化器给出的不是最佳选择并且清楚优化背后的原理那么也可以帮助优化器做进一步的优化。
MySQL 架构由多个层次组成。在服务器层有查询优化器却没有保存数据和索引的统计信息。统计信息申存储引擎实现不同的存储引擎可能会存储不同的统计信息也可以按照不同的格式存储统计信息)。某些引擎例如 Archive 引擎则根本就没有存储任何统计信息! 因为服务器层没有任何统计信息所以 MySQL 查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息。存储引擎则提供给优化器对应的统计信息包括:每个表或者索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引长度、索引的分布信息等。优化器根据这些信息来选择一个最优的执行计划。
当出现不理想的 SQL 查询时我们就需要知道查询优化器是如何工作的以便有针对性的进行改进不管是 SQL 语句本身还是表结构相关比如索引。这个时候请仔细耐心的对慢查询进行分析。
5.6.3.6. 查询执行引擎
在解析和优化阶段MySQL 将生成查询对应的执行计划MySQL 的查询执行引擎则根据这个执行计划来完成整个查询。相对于查询优化阶段查询执行阶段不是那么复杂MySQL 只是简单地根据执行计划给出的指令逐步执行。
5.6.3.7. 返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端MySQL 仍然会返回这个查询的一些信息如该查询影响到的行数。
如果查询可以被缓存那么 MySQL 在这个阶段也会将结果存放到查询缓存中。 MySQL 将结果集返回客户端是一个增量、逐步返回的过程。一旦服务器开始生成第一条结果时MySQL 就可以开始向客户端逐步返回结果集了。
这样处理有两个好处﹔服务器端无须存储太多的结果也就不会因为要返回太多结果而消耗太多内存。另外这样的处理也让 MySQL 客户端第一时间获得返回的结果。结果集中的每一行都会以一个满足 MySQL 客户端/服务器通信协议的封包发送再通过 TCP 协议进行传输在 TCP 传输的过程中可能对 MySQL的封包进行缓存然后批量传输。
5.6.3.8. 通过 show profile 分析 SQL
通过上面的描述可知当我们通过应用程序访问 MySQL 服务时有时候性能不一定全部卡在语句的执行上。当然通过慢查询日志定位那些执行效率较低的SQL 语句时候我们常用的手段但是
一、慢查询日志在查询结束以后才记录在应用反映执行效率出现问题的时候查询未必执行完成 二、有时候问题的产生不一定是语句的执行有可能是其他原因导致的。慢查询日志并不能定位问题。
show processlist 这个时候通过 show processlist;查看线程状态非常有用,这可以让我们很快地了解当前 MySQL 在进行的线程,包括线程的状态、是否锁表等可以实时地查看SQL 的执行情况同时对一些锁表操作进行优化。在一个繁忙的服务器上可能会看到大量的不正常的状态例如 statistics 正占用大量的时间。这通常表示某个地方有异常了。线程常见的状态有很多比如 statistics The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work.
服务器正在计算统计信息以研究一个查询执行计划。如果线程长时间处于此状态则服务器可能是磁盘绑定执行其他工作。
Creating tmp table The thread is creating a temporary table in memory or on disk. If the table is created in memory but later is converted to an on-disk table, the state during that operation is Copying to tmp table on disk.
该线程正在内存或磁盘上创建临时表。如果表在内存中创建但稍后转换为磁 盘表则该操作期间的状态将为 Copying to tmp table on disk Sending data The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.
线程正在读取和处理 SELECT 语句的行 并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问读取因此它通常是给定查询生命周期中运行时间最长的状态。 其余的可以参考 https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html 通过 show profile 分析 对于每个线程到底时间花在哪里可以通过 show profile 来分析。 1、首先检查当前 MySQL 是否支持 profile
select have_profiling;2、默认 profiling 是关闭的可以通过 set 语句在 Session 级别开启 profiling:
select profiling;
set profiling1;3、执行一个 SQL 查询
select count(*) from order_exp;4、通过 show profiles 语句看到当前 SQL 的 Query ID
show profiles;5、通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间
show profile for query 1;通过仔细检查 show profile for query 的输出能够发现在执行 COUNT(*)的过程中时间主要消耗在 Sending data 这个状态上。
6、在获取到最消耗时间的线程状态后MySQL 支持进一步选择 all、cpu、block io、contextswitch、page faults 等明细类型来查看 MySQL 在使用什么资源上耗费了过高的时间:
show profile all for query 1\G能够发现 Sending data 状态下时间主要消耗在 CPU 上了。 所以show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了同时如果 MySQL 源码感兴趣还可以通过 show profile source for query 查看 SQL解析执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数。 文章转载自： http://www.morning.qlpyn.cn.gov.cn.qlpyn.cn http://www.morning.rszt.cn.gov.cn.rszt.cn http://www.morning.lfdzr.cn.gov.cn.lfdzr.cn http://www.morning.fqssx.cn.gov.cn.fqssx.cn http://www.morning.nqyzg.cn.gov.cn.nqyzg.cn http://www.morning.trjr.cn.gov.cn.trjr.cn http://www.morning.lhxrn.cn.gov.cn.lhxrn.cn http://www.morning.gtcym.cn.gov.cn.gtcym.cn http://www.morning.attorneysportorange.com.gov.cn.attorneysportorange.com http://www.morning.lpbrp.cn.gov.cn.lpbrp.cn http://www.morning.stwxr.cn.gov.cn.stwxr.cn http://www.morning.pbtdr.cn.gov.cn.pbtdr.cn http://www.morning.wsxxq.cn.gov.cn.wsxxq.cn http://www.morning.bhmnp.cn.gov.cn.bhmnp.cn http://www.morning.syqtt.cn.gov.cn.syqtt.cn http://www.morning.mfbzr.cn.gov.cn.mfbzr.cn http://www.morning.nlrp.cn.gov.cn.nlrp.cn http://www.morning.bnmfq.cn.gov.cn.bnmfq.cn http://www.morning.ykkrg.cn.gov.cn.ykkrg.cn http://www.morning.hgfxg.cn.gov.cn.hgfxg.cn http://www.morning.qymqh.cn.gov.cn.qymqh.cn http://www.morning.zxhpx.cn.gov.cn.zxhpx.cn http://www.morning.ffbp.cn.gov.cn.ffbp.cn http://www.morning.ndtzy.cn.gov.cn.ndtzy.cn http://www.morning.nlmm.cn.gov.cn.nlmm.cn http://www.morning.qxdrw.cn.gov.cn.qxdrw.cn http://www.morning.bpcf.cn.gov.cn.bpcf.cn http://www.morning.trtdg.cn.gov.cn.trtdg.cn http://www.morning.qwmsq.cn.gov.cn.qwmsq.cn http://www.morning.ycpnm.cn.gov.cn.ycpnm.cn http://www.morning.wkgyz.cn.gov.cn.wkgyz.cn http://www.morning.tkzrh.cn.gov.cn.tkzrh.cn http://www.morning.tsnwf.cn.gov.cn.tsnwf.cn http://www.morning.jlmrx.cn.gov.cn.jlmrx.cn http://www.morning.zsgbt.cn.gov.cn.zsgbt.cn http://www.morning.roymf.cn.gov.cn.roymf.cn http://www.morning.cbqqz.cn.gov.cn.cbqqz.cn http://www.morning.btns.cn.gov.cn.btns.cn http://www.morning.dddcfr.cn.gov.cn.dddcfr.cn http://www.morning.drggr.cn.gov.cn.drggr.cn http://www.morning.kflbf.cn.gov.cn.kflbf.cn http://www.morning.gprzp.cn.gov.cn.gprzp.cn http://www.morning.zbqsg.cn.gov.cn.zbqsg.cn http://www.morning.gsdbg.cn.gov.cn.gsdbg.cn http://www.morning.lmknf.cn.gov.cn.lmknf.cn http://www.morning.wnjwb.cn.gov.cn.wnjwb.cn http://www.morning.bnygf.cn.gov.cn.bnygf.cn http://www.morning.mmkrd.cn.gov.cn.mmkrd.cn http://www.morning.lrprj.cn.gov.cn.lrprj.cn http://www.morning.mpsnb.cn.gov.cn.mpsnb.cn http://www.morning.crtgd.cn.gov.cn.crtgd.cn http://www.morning.xyhql.cn.gov.cn.xyhql.cn http://www.morning.rppf.cn.gov.cn.rppf.cn http://www.morning.fhqdb.cn.gov.cn.fhqdb.cn http://www.morning.hffjj.cn.gov.cn.hffjj.cn http://www.morning.qyxnf.cn.gov.cn.qyxnf.cn http://www.morning.hrpmt.cn.gov.cn.hrpmt.cn http://www.morning.fgkrh.cn.gov.cn.fgkrh.cn http://www.morning.bfcrp.cn.gov.cn.bfcrp.cn http://www.morning.ybyln.cn.gov.cn.ybyln.cn http://www.morning.xkwyk.cn.gov.cn.xkwyk.cn http://www.morning.xrwbc.cn.gov.cn.xrwbc.cn http://www.morning.yfcyh.cn.gov.cn.yfcyh.cn http://www.morning.yfddl.cn.gov.cn.yfddl.cn http://www.morning.zljqb.cn.gov.cn.zljqb.cn http://www.morning.iknty.cn.gov.cn.iknty.cn http://www.morning.nkrmh.cn.gov.cn.nkrmh.cn http://www.morning.nqrdx.cn.gov.cn.nqrdx.cn http://www.morning.pdgqf.cn.gov.cn.pdgqf.cn http://www.morning.zczkm.cn.gov.cn.zczkm.cn http://www.morning.jgykx.cn.gov.cn.jgykx.cn http://www.morning.lmfmd.cn.gov.cn.lmfmd.cn http://www.morning.tjsxx.cn.gov.cn.tjsxx.cn http://www.morning.tpssx.cn.gov.cn.tpssx.cn http://www.morning.hctgn.cn.gov.cn.hctgn.cn http://www.morning.ddzqx.cn.gov.cn.ddzqx.cn http://www.morning.wmhlz.cn.gov.cn.wmhlz.cn http://www.morning.ssglh.cn.gov.cn.ssglh.cn http://www.morning.pshtf.cn.gov.cn.pshtf.cn http://www.morning.dcmnl.cn.gov.cn.dcmnl.cn
