京东网站建设机构,wordpress网站流量统计插件,retina wordpress,自助建站系统哪个好用查询性能优化 排序优化
无论如何排序都是一个成本很高的操作#xff0c;所以从性能角度考虑#xff0c;应尽可能避免排序或者尽可能避免对大量数据进行排序。前面已经提到了#xff0c;当不能使用索引生成排序结果的时候#xff0c;MySQL需要自己进行排序#xff0c;如果…查询性能优化 排序优化
无论如何排序都是一个成本很高的操作所以从性能角度考虑应尽可能避免排序或者尽可能避免对大量数据进行排序。前面已经提到了当不能使用索引生成排序结果的时候MySQL需要自己进行排序如果数据量小则在内存种进行如果数据量大则需要使用磁盘不过MySQL将这个过程统一称为文件排序(filesort)即使完全是内存排序不需要任何磁盘文件时也是如此。如果需要排序的数据量小于排序缓冲区MySQL使用内存进行快速排序操作。如果内存不够排序那么MySQL会先将数据分块对每个独立的块使用快速排序进行排序并将各个块的排序结果存放在磁盘上然后将各个排好序的快进行合并(merge)最后返回排序结果。MySQL有如下两种排序算法:
1.两次传输排序(旧版本使用)(也称双路排序) 读取行指针和需要排序的字段对其进行排序然后再根据排序结果读取所需要的数据行。这需要进行两次数据传输即需要从数据表中读取两次数据第二次读取数据的时候因为是读取排序列进行排序后的所有记录这回产生大量的随机IO,所以两次数据传输的成本非常高。当使用的是MyISAM表的时候成本可能会更高因为MyISAM使用系统调用进行数据的读取(MyISAM非常依赖操作系统对数据的缓存)。不过这样做的优点是在排序的时候存储尽可能少的数据这就让排序缓冲区(内存)中可能容纳尽可能多的行数进行排序2.单次传输排序(新版本使用) 先读取查询所需要的所有列然后再根据给定列进行排序最后直接返回排序结果。这个算法只在MySQL4.1和后续更新的版本中引入。因为不再需要从数据表中读取两次数据对于IO密集型的应用这样做的效率高了很多。另外相比两次传输排序这个算法只需要一次顺序IO读取所有的数据而无须任何的随机IO.缺点是如果需要返回的列非常多、非常大、会额外占用大量的空间而这些列对排序操作本身来说是没有任何作用的。因为单条排序记录很大所以可能会有更多的排序块需要合并.
很难说哪个算法效率更高两种算法都有各自最好和最糟的场景。当查询需要所有列的总长度不超过参数max_length_for_sort_data时MySQL使用单次传输排序可以通过调整这个参数来影响MySQL排序算法的选择
MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的要大得多。原因在于MySQL在排序时对每一个排序记录都会分配一足够长的定长空间来存放。这个定长空间必须足够长以容纳其中最长的字符串例如如果是VARCHAR列则需要分配其完整长度如果使用UTF-8字符集那么MySQL将会为每个字符预留两个字节。曾经在一个库表结构设计不合理的案例中看到排序消耗的临时空间比磁盘上的原表要大很多倍。 在关联查询的时候如果需要排序MySQL会分两种情况来处理这样的文件排序。如果ORDER BY 子句中的所有列都来自关联的第一个表那么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的存储引擎插件式架构成为可能但是正如前面的讨论也给优化器带来了一定的限制。 并不是所有的操作都有handler完成。例如当MySQL需要进行表锁的时候handler可能会实现自己的级别的、更细粒度的锁如InnoDB就实现了自己的行基本锁但这并不能代替服务器层的表锁。如果是所有存储引擎共有的特性则由服务器层实现比如时间和日期函数、视图、触发器等等。 为了执行查询MySQL只需要重复执行计划中的各个操作知道完成所有的数据查询。
返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端MySQL仍然会返回这个查询的一些信息如该查询影响到的行数。如果查询可以被缓存那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如我们回头看看前面的关联操作一旦服务器处理完成最后一个关联表开始生成第一条结果时MySQL就可以开始向客户端逐步返回结果集了。这样处理有两个好处:服务器端无须存储太多的结果也就不会因为要返回太多结果而消耗太多内存。另外这样的处理也让MySQL客户端第一时间获得返回的结果。结果集中的每一行会以一个满足MySQL客户端/服务器通信协议的封包发送再通过TCP协议进行传输在TCP传输的过程中可能对MySQL的风暴进行缓存然后批量传输。
