旧安卓手机做网站,南昌校园文化设计公司,wordpress 首页插件,阿里云可以做电商网站吗查询性能优化
MySQL客户端/服务器通信协议
一般来说#xff0c;不需要去理解MySQL通信协议的内部实现细节#xff0c;只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是半双工的#xff0c;这意味着#xff0c;在任何一个时刻#…查询性能优化
MySQL客户端/服务器通信协议
一般来说不需要去理解MySQL通信协议的内部实现细节只需要大致理解通信协议是如何工作的。MySQL客户端和服务器之间的通信协议是半双工的这意味着在任何一个时刻要么是由服务器向客户端发送数据要么是由客户端向服务器发送数据这两个动作不能同时发生。所以我们也无法也无须将一个消息切成小块独立来发送。这种协议让MySQL通信简单快速但是也从很多地方限制了MySQL.一个明显的限制是这意味着没法进行流量控制。一旦一端开始发送消息另一端要接收完整个消息才能响应它。这就像来回抛球的游戏:在任何时刻只有一个人能控制球而且只有控制球的人才能将球抛回去(发送消息)。一旦客户端发送了请求它能做的事情就只是等待结果了。相反的一般服务器响应给用户的数据通常很多由多个数据包组成。当服务器开始响应客户端请求时客户端必须完整接收整个返回结果而不能简单地只取前面几条结果然后让服务器停止发送数据。这种情况下客户端若接收完整的结果然后取前面几条需要的结果或者接收完几条结果后就粗暴地断开连接都不是好注意。这也是在必要的时候一定要在查询中加上LIMIT限制的原因。换一种方式解释这种行为:当客户端从服务器取数据时看起来是一个拉数据的过程但实际上是MySQL在向客户端推送数据的过程。客户端不断地接收从服务器推送的数据客户端也没法让服务器停下来。客户端像是从消防管喝水(这是一个术语)。多数连接MySQL的库函数都可以获得全部结果集并缓存到内存力还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发生给客户端才能释放这条查询所占用的资源所以接收全部结果并缓存通常可以减少服务器的压力让查询能够早点结束、早点释放相应的资源。 当使用多数连接MySQL的库函数从MySQL获取数据时其结果看起来都像时从MySQL服务器获取数据而实际上都是从这个库函数的缓存获取数据。多数情况下这没什么问题但是如果需要返回一个很大的结果集的时候这样做并不好因为库函数会花很多时间和内存来存储所有的结果集。如果能够尽早开始处理这些结果集就能大大减少内存的消耗这种情况下可以不适用缓存来记录结果而是直接处理。这样做的缺点是对于服务器来说需要查询完成后才能释放资源所以在和客户端交互的整个过程中服务器的资源都是被这个查询搜占用的(你可以使用SQL_BUFFER_RESULT) 我们看啊可能当使用PHP的时候是什么情况。首先下面是我们连接MySQL的通常写法:
?php
$link mysql_connect(localhost,user,p4ssword);
$result mysql_query(SELECT * FROM HUGE_TABLE, $link);
$while($row mysql_fetch_array($result)) {
// Do something with result
}
?这段代码看起来像是只有当你需要的时候才通过循环从服务器取出数据。而实际上在上面的代码中在调用mysql_query()的时候PHP就已经将整个结果缓存到内存中。下面的while循环只是从这个缓存中逐行取出数据相反如果使用下面的查询用mysql_unbuffered_query()代替mysql_query(),PHP则不会缓存结果:
?php
$link mysql_connect(localhost, user, p4ssword);
$result mysql_unbuffered_query(SELECT * FROM HUGE_TABLE, $link);
while($row mysql_fetch_array($result)) {
//Do something with result
}
?不同的编程语言处理缓存的方式不同。例如在Perl的DBD:mysql驱动中需要指定C连接库的mysql_use_result属性(默认是mysql_buffer_result)。下面是一个例子:
#!usr/bin/perl
use DBI;
my $dbh DBI-connect(DBI:mysql:;hostlocalhost, user, p4ssword);
my $sth $dbh-prepare(SELECT * FROM HUGE_TABLE, {mysql_use_result 1});
$sth - execute();
while (my $row $sth-fetchrow_array()) {
# Do something with result
}注意到上面的prepare()调用指定了mysql_use_result属性为1所以应用将直接使用返回的结果集而不会将其缓存。也可以在连接MySQL的时候指定这个属性这会让整个连接都使用不缓存的方式处理结果集:
my $dbh DBI-connect(DBI:mysql:;mysql_use_result1, user,p4ssword);查询状态 对于一个MySQL连接或者说一个线程任何时刻都有一个状态该状态标识了MySQL当前正在做什么。有很多种方式能查看当前的状态最简单的是使用SHOW FULL PROCESSLIST命令(该命令返回结果中的Command列就表示当前的状态)。在一个查询的生命周期中状态会变化很多次。MySQL官方手册中对这些状态值的含义有权威的解释下面将这些状态列出来并做一个简单的解释.
1.Sleep 线程正在等待客户端发送新的请求2.Query 线程正在执行查询或者正在将结果发送给客户端3.Locked 在MySQL服务器层该线程正在等待表锁。存储引擎级别实现的锁例如InnoDB的行锁并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态但在其他没有行锁的一你请中也会经常出现4.Analyzing and statistics 线程正在收集存储引擎的统计洗脑洗并生成查询的执行计划5.Copying to tmp table [on disk] 线程正在执行查询并且将结果集都复制到一个临时表中这种状态一般要么是在做GROUP BY操作要么是文件排序操作或者UNION操作。如果这个状态后面还有on disk标记那表示MySQL正在将一个内存临时表放到磁盘上6.Sorting result 线程正在对结果集进行排序7.Sending data 这表示多种情况:线程可能在多个状态之间传送数据或者在生成结果集或者在向客户端返回数据。
了解这些状态的基本含义非常有用这可以让我们很快地了解当前谁正在持球。在一个繁忙的服务器上可能会看到大量的不正常的状态。例如statstics正占用大量的时间。这通常表示某个地方有异常了
查询缓存(这里是指Query Cache)
在解析一个查询语句之前如果查询缓存是打开的那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同那也不会匹配缓存结果(Percona版本的MySQL中提供了一个新的特性可以在计算查询语句哈希值时先将注释移除再计算哈希值这对于不同注释的相同查询可以命中相同的查询缓存结果)。这种情况下查询就会进入下一个阶段的处理。如果当前的查询恰好命中了查询缓存那么再返回查询结果之前MySQL会检查一次用户权限。这仍然时无须解析查询SQL语句的因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题MySQL会跳过所有其他阶段直接从缓存中拿到结果并返回给客户端。这种情况下查询不会被解析不用生成执行计划不会被执行
查询优化处理
查询的生命周期的下一步时将一个SQL转换成一个执行计划MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误(例如语法错误)都可能终止查询。接下来选择性地介绍其中几个独立的部分在实际执行中这几部分可能一起执行也可能单独执行。目的是帮助大家理解MySQL是如何执行查询以便写出更优秀的查询。
语法解析器和预处理
首先MySQL通过关键字将SQL语句进行解析并生成一棵对应的解析树MySQL解析器将使用MySQL语法规则验证和解析查询例如它将验证是否使用错误的关键字或者使用关键字的顺序是否正确等再或者它还会验证引号是否能前后匹配。预处理器则根据一些MySQL规则进一步检查解析树是否合法例如这里将检查数据表和数据列是否存在还会解析名字和别名看看它们是否有歧义下一步预处理器会验证权限。这通常很快除非服务器上有很多的权限配置。
查询优化器
现在语法书被认为是合法的了并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器它将尝试预测一个查询使用某种执行计划时的成本并选择其中成本最小的一个。最初成本的最小单位是随机读取一个4K数据页的成本后来(成本计算公式)变得更加复杂并且引入了一些因子来估算某些操作的代价如当执行一次WHERE条件比较的成本.可以通过查询当前会话的Last_query_cost的值来得知MySQL计算的当前查询的成本;
mysql SELECT SQL_NO_CACHE COUNT(*) FROM film_actor;
----------
| COUNT(*) |
----------
| 5462 |
----------
1 row in set (0.07 sec)
mysql SHOW STATUS LIKE Last_query_cost;
------------------------------
| Variable_name | Value |
------------------------------
| Last_query_cost | 1104.399000 |
------------------------------
1 row in set (0.07 sec)这个结果表示MySQL的优化器认为大概需要做1040个数据页的随机查找才能完成上面的查询。这是根据一系列的统计信息计算得来的:每个表或者索引的页面个数、索引的基数(索引中不同值得数量)、索引和数据行得长度、索引分布情况。优化器再评估成本的时候并不考虑任何层面的缓存它假设读取任何数据都需要一次磁盘IO.有很多种原因会导致MySQL优化器选择错误的执行计划如下所示:
1.统计信息不准确。MySQL依赖存储引擎提供的统计信息来评估成本但是有的存储引擎提供的信息是准确的有的偏差可能非常大。例如InnoDB因为其MVCC的架构并不能维护一个数据表的行数的精确统计信息2.执行计划种的成本估算不等同于实际执行的成本所以即使统计信息准确优化器给出的执行计划也可能不是最优的。例如有时候某个执行计划虽然需要读取更多的页面但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面已经在内存种的花那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上所以查询实际执行过程中到底需要多少次物理IO是无法得知的。3.MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短但是MySQL只是基于其成本模型选择最优的执行计划而有些时候这并不是最快的执行方式。所以这里我们看到的根据执行成本来选择执行计划并不是完美的模型4.MySQL从不考虑其他并发执行的查询这可能会影响到当前查询的速度5.MySQL也并不是任何时候都是基于成本的优化。有时也会给予一些固定的规则例如如果存在全文搜索的MATCH()子句则在存在全文索引的时候就使用全文索引。即使有时候使用别的索引和WHERE条件可以远比这种方式要快MySQL也仍然会使用对应的全文索引。6.MySQL不会考虑不受其控制的操作的成本例如执行存储过程或者用户自定义函数的成本7.后面我们还会看到优化器有时候无法去估算所有可能的执行计划所以它可能错过实际上最优的执行计划
