- 浏览: 496929 次
- 性别:
- 来自: 大连->北京
文章分类
最新评论
-
春天好:
写的很不错 推荐一个免费好用的云端爬虫开发平台不需要安装环境, ...
web爬虫 -
cpu88:
网络爬虫爬来爬去,网上信息可以瞬间扩散,但是也意味着,没有人愿 ...
web爬虫 -
biaoming:
牛。。学习了。。
MongoDB 关于索引的建议 -
biaoming:
楼主用mongo好早啊。
MongoDB 优化 -
biaoming:
好教程,学习了。。。
MongoDB 优化
在语句生命周期的下一步就是把一个SQL查询放入一个可执行的计划中。这个步骤有许多子步骤:解析,预处理并且优化。在这个过程中任意一点抛出错误(比如语法错误)。在这我们不会列出MySQL内部机制。因此我们可以更自由的说一些别的,如单独的描述其中的一些步骤,虽然它们可能有效地全部或者部分的组合在一起。我们的目标就是帮助你理解MySQL执行语句的过程,这样能写出更好的语句。
解析和预处理
开始MySQL解析器会把一个语句分成一些token,并创建一个“解析树”。解析器使用MySQL语法来解释和校验这个语句。它必须确保这些token有效并且顺序正确,还有它会检查一些如字符串双引号没有结束的一些错误。
接下来预处理器就检查这个结果解析树额外的一些信息,这些信息解析器是解析不了的。举个例子,它会校验表和列是否存在,并且决定了命名和别名,这样保证列的引用不会有歧义。
然后,预处理校验权限。这个过程是非常快的,除非你的服务端使用了大量的权限。
查询语句优化器
解析树已经是有效的并且开始准备用优化器把它放入到执行计划中。可以用不同的方法来执行查询语句并且得到的结果相同。优化器的工作就是找到最好的方法。
MySQL使用了基于消耗的优化器。意思就是会尝试多种不同的方法去执行语句,选择一个效率更高的。这种消耗的单元是一个单独的随机4kb数据页读取。你可以查看到优化器执行一个查询语句,通过命令查看Last_query_cost变量。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 1040.599000 |
+-----------------+-------------+
这个结果的意思是,优化器评估它需要大学1040随机数据读取来执行这个语句。他评估标准基于如下的统计:每张表或索引的页,索引的基数(不同值的数目),行或键的长度,以及键的分布情况。在它的评估中,优化器不包含任意缓存的效果-它的前提是每次都以硬盘IO的方式来获取数据。
优化器并不是每一次都选择最优的方式,原因如下:
- 统计可能是错的。服务端依靠的是存储引擎提供的统计,这些统计可能从非常精确到不精确都有。比如,在一张表中innoDB引擎不会维护表的行数,这是由于它的MVCC架构。
- 消耗这个度量也不等同于这个语句实际所消耗的量。因此即使当统计数据是准确的,这个语句的执行开销也或多或少于MySQL的近似值。一个读取过多页的执行计划可能在某些情形下会读的少些。比如当读是连续的,因此硬盘IO会更快一些,或者当页已经被内存所缓存了。
- MySQL优化的方法可能不符合你的期望。你可能期望更快的执行时间,但是MySQL并不知道什么叫快速。它只明白消耗。就像前面我们所看到的,决定消耗的量也并非那么科学。
- MySQL并不会考虑其他正在并发执行的语句。这也影响一个语句的运行。
- MySQL也不会总做基于消耗的优化。有的时候,它仅仅遵循以下的规则,比如“如果有个全文匹配的MATCH条件,如果FULLTEXT索引存在,就使用它”。这样的话即使其他索引方式更快也不会去使用。
- 优化器不能计算账户内部操作的一些消耗。比如存储函数或者用户自定义的函数就没办法计算了。
- 在以后会说到,优化器不能评估每个可能执行的计划。因此会错失一个最佳的执行计划。
MySQL的语句优化器一个软件高度复杂的地方,并且它使用许多优化方法去把一个语句转为一个可执行的计划。有两个基本的优化类型,我们叫做静态或动态的。静态优化表现为的形式是,查看解析树。举一个例子,通过一个代数的规则,优化器可以把WHERE子句转为一个相等形式。静态优化是独立的值,比如WHERE子句不变的值。它们被执行一次,然后总是有效的。你可以把静态优化想象成”编译时的优化“。
相比较之下,动态优化基于上下文并且依赖于很多因素。比如在WHERE子句的值或者在一个索引中有多少行。每一次语句的执行,它们都必须重写进行评估。你可以把动态优化想象成“运行期的优化”。
在执行预处理语句或存储过程时,这些不同尤为重要。MySQL会进行一次静态优化,但是每次语句的执行都要进行动态优化。MySQL有的时候甚至会对一个执行语句重写优化。
下面是一些MySQL知道怎样去做的优化类型。
重写排序连接(join)
在一个查询语句中,表并不总是在你指定的顺序下进行连接。决定最佳连接顺序是一个重要的优化:我们会在Join优化器详细说到
把外连接转为内连接
一个外连接没有必要做为一个“外连接”去执行。由于一些源于,如WHERE条件和表的schema都可能导致一个外连接等于与内连接。MySQL可以发现这点并重新连接。并且以最适当的顺序进行连接。
应用代数等价规则
MySQL应用代数转换使表达式简单化和规范化。 这样可以减少一些约束。评估可能的约束和常数的条件。比如,(5=5 AND a>5)就会缩减为a>5.同样的。(a<b AND b=c) AND a=5 就会变为b>5 AND b=c AND a=5.这些规则对条件查询非常有用。在这章的后面会详细说到。
COUNT(),MIN(),MAX()优化
索引或者列为空的特性可以帮助MySQL优化这些语句。例如,去查找一个B-Tree索引最左边的列的最小值,MySQL仅仅需要请求索引的第一行。在优化查询语句的情形下同样有效,并且对于剩下的查询语句把这个值看做常量。同样的查找B-TREE索引的最大值,服务端会读取最后一行。如果服务端使用了这种优化方式,你会在EXPLAN看到““Select
tables optimized away”。意思就是优化器已经从语句执行计划中移除了这个表,并且以常量来替代它。
同样的 COUNT(*) 查询没有WHERE子句的情况下,也是这样被优化的。优化COUNT()会在以后说到。
评估和简化常量表达式
当MySQL发现一个表达式可以简化为一个常量的时候,就会做这个优化。比如,如果发现在查询中一个用户自定义的变量并不会改变,就会把它转为常量。运算表达式是另一个例子了。
也许令人惊讶的是,在优化的过程中, 你可能认为一个查询可以简化为一个常数。其中一个例子为在索引上的MIN()函数。
在主键或唯一索引上查找,这就被扩展为一个常数。 如果一个WHERE子句给索引附上一个常量的条件,那么优化器就会了解MySQL可以在这个查询的开端来查找这个值。在查询剩下的语句中,就会把这个值看做一个常量。这有个例子。
mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id = 1;
+----+-------------+------------+-------+----------------+-------+------+
| id | select_type | table | type | key | ref | rows |
+----+-------------+------------+-------+----------------+-------+------+
| 1 | SIMPLE | film | const | PRIMARY | const | 1 |
| 1 | SIMPLE | film_actor | ref | idx_fk_film_id | const | 10 |
+----+-------------+------------+-------+----------------+-------+------+
MySQL用两步来执行这个SQL. 看下上面的输出就可以知道了。第一步在film表中查找所需要的行。 MySQL的优化器知道只有一行,因为film_id是主键,并且在查询优化阶段查看将要查询的行数的时候,已经考虑到索引了。这个表的ref是const那是因为优化器已经知道了在这个查找过程中将用到的行数。
在第二步的时候,MySQL把从第一步中找到的film_id做为一个已知的数量。那是因为当到达第二步的时候优化器已经知道这些了,它会知道所有来自第一步的值。注意film_actor表的ref也是const的。和表film是一样的。
另一方面你会看到如果WHERE USING或者ON子句的约束的值相同,那些条件的值都为常量了。在这个例子中,优化器已经通过WHERE子句得知了film_id的值,就会认为这个语句的所有的film_id都为常量了。
覆盖索引
当一个索引覆盖了你所需要查询的列,MySQL有的时候使用这个索引来避免读取行数据。我们在覆盖索引那节已经详细说过了。
子查询优化
MySQL会把一些子查询转为更高效的形式,把它们转为索引查找来取代子查询。
提前结束
当查询满足了条件,MySQL会终止处理这个查询。显而易见的例子是LIMIT子句。但是还有很多提前结束操作的例子,看看如下的例子
mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1;
+----+...+-----------------------------------------------------+
| id |...| Extra |
+----+...+-----------------------------------------------------+
| 1 |...| Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+
MySQL在优化的步骤就终止了这个查询。当这个查询执行的引擎发现它获取的是明确的值,服务器就会应用这种优化。或者值根本不存在就停止这个查询。看看如下的例子
mysql> SELECT film.film_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL;
这个查询排除掉了有演员的电影。每个电影可能有很多演员。但是只要它找到了一个演员,它就停止查询当前的电影了,而去寻找下一个电影是否有演员。因为它知道WHERE条件禁止查询出这个有演员的电影了。同样的如“Distinct/not-exists”优化适用于DISTINCT, NOT EXISTS( ),LEFT JOIN语句。
等价传播
当两个值在一个查询语句中相同,那么MySQL会进行识别的。举个例子,在JOIN条件中,就传播到WHERE子句所有相同的列。来看个示例。
mysql> SELECT film.film_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id > 500;
MySQL知道WHERE子句的film_id不仅仅指的是film表,也指的是film_actor,因为USING语句使这两张表的film_id进行了匹配。
如果你使用了其他数据库,请不要那么做,你可以明确指出这两列的条件。语句如下
... WHERE film.film_id > 500 AND film_actor.film_id > 500
显然对于MySQL上面的语句是不需要的。这只会让你的维护更困难。
IN()列表的比较
在许多数据库中,IN()和许多OR组成的语句是一样的。因为这两个语句的逻辑上相同。在MySQL中,有些不同,对IN()里的值进行排序,并且用快速的二进制搜索来查找值是否存在。in的时间复杂度为O(log n) ,然而OR的为O(n) (也就是说,如果list很大会慢一些)。
上面所说的几点并不太全,MySQL所执行的优化策略超过了本章的内容了。但是能给你带来一些优化的思想。但是重要的一点是,不要试图比优化器更智能。结果就是你可能比它强或者使你的语句更复杂更难维护,我要说的就是,让优化器去做应该做的。
当然有的时候,优化器不能给我们带来高效的结果的使用,也要去做一些特定的优化。有的时候,因为逻辑的原因,可能有个条件一定会为true,但优化器却不知道。优化器也有些不具备的功能,比如优化hash索引等等。这样导致优化器对查询消耗评估错误,去执行了一个消耗更高的执行计划。
如果你知道优化器不能带来一个优化的结果,并且你知道原因,那么你就可以自己优化了。也有一些其他的方法,那就是重写这个语句,重写设计数据结构,或者添加索引。
评论
1. 重写排序连接(join), 是根据什么原则来决定需要优先使用的表?
2. 把外连接转为内连接, 能不能举个例子, 不太明白
3. 子查询优化, 这个是怎么回事呢
希望你能帮忙解答一下爱. 这几点实在想不出来了,. 谢谢
发表评论
-
查询性能的优化 - 语句执行的基础 - 已缓存的查询语句
2009-12-01 09:58 1159在解析一个查询之前,如果缓存开启,MySQL要检查它的缓存。这 ... -
查询性能的优化 - 语句执行的基础 - MySQL 客户端/服务端 协议
2009-12-01 01:25 1929MySQL 客户端/服务端 协 ... -
查询性能的优化 - 语句执行的基础
2009-11-30 00:36 1014如果你想从MySQL服务器获得很高的性能,建议你花费一定的时间 ... -
查询性能的优化 - 重新构建查询的方法 - 分解JOIN查询
2009-11-29 11:54 1828分解JOIN查询 许多高性能的网站都分解了JOIN查询。你可 ... -
查询性能的优化 - 重新构建查询的方法 - 拆分一个查询语句
2009-11-28 23:17 1494拆分一个查询语句 另一个分解查询的方法是分步解决。本质上来 ... -
查询性能的优化 - 重新构建查询的方法 - 复杂查询VS多个查询语句
2009-11-28 01:32 1512当开始优化有问题的查 ... -
查询性能的优化 - 查询慢的基础知识:优化数据访问
2009-08-19 14:50 1564一个查询执行的不是 ... -
查询性能的优化 - 前言
2009-08-12 16:49 990上一章,我们解释了怎样优化schema.这是高性能的一个必要条 ... -
Schema的优化和索引 - 关于存储引擎的简单记录
2009-08-12 15:26 1054这一章的结束,我们来说一下关于设计模型的存储引擎的选择,这些你 ... -
Schema的优化和索引 - 加速ALTER TABLE
2009-08-12 14:02 1830当对于一个大表进行ALTER TABLE的时候,性能问题就产生 ... -
Schema的优化和索引 - 范式和非范式
2009-08-12 11:35 1679有很多方法来展现给定的数据。从完全范式到完全的非范式以及介于两 ... -
Schema的优化和索引 - 索引和表的维护
2009-08-10 15:38 1404当你已经创建了一张表 ... -
Schema的优化和索引 - 学习一个索引示例
2009-08-06 14:09 1039用例子来理解索引的概 ... -
Schema的优化和索引 - 高性能的索引策略 - 索引和锁
2009-07-31 15:48 1011InnoDB中,索引所扮演的角色是非常重要的。因为它们可以能让 ... -
Schema的优化和索引 - 高性能的索引策略 - 冗余和重复的索引
2009-07-31 11:37 2051MySQL可以在一个列上创建多个索引;这么做并不会提醒和防止发 ... -
Schema的优化和索引 - 高性能的索引策略 - 压缩索引(Packed Indexes)
2009-07-30 21:30 1409MyISAM使用前缀压缩来降低索引的大小,这样就可以把更多的索 ... -
Schema的优化和索引 - 高性能的索引策略 - 使用索引扫描来进行排序
2009-07-28 10:43 2189MySQL有两种方法生成有序的结果:使用文件排序或者按顺序的扫 ... -
Schema的优化和索引 - 高性能的索引策略 - 覆盖索引(Covering Indexes)
2009-07-22 15:25 2574索引是高效找到行的一 ... -
Schema的优化和索引 - 高性能的索引策略 - 聚簇索引(Clustered Indexes)
2009-07-20 23:29 3131聚簇索引并不是一个独立的索引类型。确切的说它们是存储数据的一个 ... -
Schema的优化和索引 - 高性能的索引策略 - 前缀索引和索引的选择性
2009-07-13 22:32 1766有的时候你可能需要索 ...
相关推荐
SQL注入的原理 什么SQL注入 将SQL代码插入到应用程序的输入参数中,之后,SQL代码被传递到数据库执行。从而达到对应用程序的攻击目的。... 有了执行计划和执行时间我们就很容易判断一条SQL语句执行效率高不高
《SQL Server 2008查询性能优化》指出的性能要点之一是数据库随着用户和数据的日益增多而进行扩展的必要性。你需要理解性能低下的起因。以及识别并修复它们的方法。《SQL Server 2008查询性能优化》将帮助你: 使用...
目标 了解什么是优化 ...sql 语句超过某个时间后的记录,方便我们做一个后期的优化,我们可以通 过 Slow_queries 显示慢查询 查看你的 mysql 数据库是否有慢查询:SHOW STATUS LIKE 'Slow_queries'
MySQL架构执行与SQL性能优化-MySQL高并发详解课程,课程的目标简单明确,核心就是MySQL的性能优化与高并发。课程内容进行了精华的浓缩,有四大内容主旨,MySQL架构与执行流程,MySQL索引原理详解,MySQL事务原理与...
oralce查询当前执行耗资源的语句;查询Oracle正在执行的sql语句及执行该语句的用户;数据库等待时间查询;数据库锁表关系、源头等一系列锁表问题查询及解决;查看oracle数据库最近执行了哪些sql语句;查询oracle效率...
SQL语句执行原理及性能优化.pdf
本文档主要介绍与SQL调整有关的内容,内容涉及多个方面:SQL语句执行的过程、ORACLE优化器,表之间的关联,如何得到SQL执行计划,如何分析执行计划等内容,从而由浅到深的方式了解SQL优化的过程,使大家逐步步入SQL...
● 找出影响查询执行和性能的关键配置变量 ● 用SQL语句优化的生命周期来识别、确 认、分析然后优化SQL语句,并检查优 化的结果 ● 学习使用不为常人所知的一些性能技巧 来改进索引效率并简化SQL语句
《SQL Server 2008查询性能优化》指出的性能要点之一是数据库随着用户和数据的日益增多而进行扩展的必要性。你需要理解性能低下的起因。以及识别并修复它们的方法。《SQL Server 2008查询性能优化》将帮助你: 使用...
我们知道,一般在查询语句出现性能瓶颈,需要对其进行优化时,一个比较有效的手段就是DBA通过调整sql语句的执行计划,或者建立有效索引以达到数据执行时间最短的效果.
存储过程的安全及性能优化 存储过程分类 系统存储过程 自定义存储过程 SQL Server使用者编写的存储过程 扩展存储过程 动态链接库(DLL)函数的调用看,主要用于客户端和服务器端之间进行通信 exec...
《Effective MySQL之SQL语句最优化》希望能够通过一步步详细介绍SQL优化的方法,帮助读者分析和调优有问题的SQL语句。 内容简介 《Effective MySQL之SQL语句最优化》主要内容: ●找出收集和诊断问题必备的分析...
85提纲挈领的告诉你,SQL语句的执行计划和性能优化有什么关系?.pdf
ORACLEsql语句优化,性能优化,语句技巧优化
对于基于成本的优化,数据库查询优化器的任务是,通过产生可供选择的执行计划,找到最低估算成本的执行计划,来优化一条SQL语句。它在SQL语句性能表现上扮演了至关重要的角色。当一条SQL语句被送入RDBMS服务器,它将...
开发一个注重性能的JDBC应用程序不是一件容易的事. 当你的代码运行很慢的时候JDBC 驱动程序并不会抛出异常告诉你。 本系列的性能提示将为改善 JDBC 应用程序的性能介绍一些基本的指导原则,这其中 的原则已经被...
行业-85提纲挈领的告诉你,SQL语句的执行计划和性能优化有什么关系?.rar
oracle数据库的性能优化直接关系到系统的运行效率,而影响数据库性能的一个重要因素就是sql性能问题。本书是作者十年磨一剑的成果之一,深入分析与解剖oracle sql优化与调优技术,主要内容包括: 第一篇“执行计划...
在 sql 中,首先要注意书写规范。虽然数据库不区分大小写,但是 sql 语句在执行时, ORACLE 会将它全部转化为大写,如果在写 SQL 语句时时就全部写成大写,能节省些资源。