taoCMS是基于php+sqlite/mysql的国内最小(100Kb左右)的功能完善的CMS管理系统

MySQL数据库如何限制子查询

2010-11-04

更正的一致缺陷:如果将NULL值与使用ALL、ANY或SOME的子查询进行比较,而且子查询返回空结果,比较操作将对NULL的非标准结果进行评估,而不是TRUE或FALSE。

  子查询的外部语句可以是SELECT、INSERT、UPDATE、DELETE、SET或DO中的任何一个。

  仅部分支持行比较操作:

  ·对于expr IN (subquery),expr可以是n-tuple(通过行构造程序语法指定),而且子查询能返回n-tuples个行。

  ·对于expr op {ALL|ANY|SOME} (subquery),expr必须是标度值,子查询必须是列子查询,不能返回多列行。

  换句话讲,对于返回n-tuples行的子查询,支持:

  (val_1, ..., val_n) IN (subquery)

  但不支持:

  (val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)

  支持针对IN的行比较,但不支持针对其他的行比较,原因在于,IN实施是通过将其重新编写为“=”比较和AND操作的序列完成的。该方法不能用于ALL、ANY或SOME。

  未良好优化行构造程序。下面的两个表达式是等效的,但只有第2个表达式能被优化:

  (col1, col2, ...) = (val1, val2, ...) 
  col1 = val1 AND col2 = val2 AND ...

  对于IN的子查询优化不如对“=”的优化那样有效。

  对于不良IN性能的一种典型情况是,当子查询返回少量行,但外部查询返回将与子查询结果相比较的大量行。

  FROM子句中的子查询不能与子查询有关系。在评估外部查询之前,将对它们进行具体化处理(执行以生成结果集),因此,不能按照外部查询的行对它们进行评估。

  一般而言,不能更改表,并从子查询内的相同表进行选择。例如,该限制适用于具有下述形式的语句:

  DELETE FROM t WHERE ... (SELECT ... FROM t ...); 
  UPDATE t ... WHERE col = (SELECT ... FROM t ...); 
  {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

  例外:如果为FROM子句中更改的表使用子查询,前述禁令将不再适用。例如:

  UPDATE t ... WHERE col = 
  (SELECT (SELECT ... FROM t...) AS _t ...);

  禁令在此不适用,这是因为FROM中的子查询已被具体化为临时表,因此“t”中的相关行已在满足“t”条件的情况下、在更新时被选中。

  与子查询相比,针对联合的优化程序更成熟,因此,在很多情况下,如果将其改写为join(联合),使用子查询的语句能够更有效地执行。

  但下述情形例外:IN子查询可被改写为SELECT DISTINCT联合。例如:

  SELECT col FROM t1 WHERE id_col IN 
  (SELECT id_col2 FROM t2 WHERE condition);

  可将该语句改写为:

  SELECT DISTINCT col FROM t1, 
  t2 WHERE t1.id_col = t2.id_col AND condition;

  但在该情况下,联合需要额外的DISTINCT操作,而且与子查询相比,效率并不高。

  可能的未来优化:MySQL不改写针对子查询评估的联合顺序。在某些情况下,如果MySQL将其改写为联合,能够更有效地执行子查询。这样,优化程序就能在更多的执行方案间进行选择。例如,它能决定是否首先读取某一表或其他。

  例如:

  SELECT a FROM outer_table AS ot 
  WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);

  对于该查询,MySQL总会首先扫描outer_table,如然后针对每一行在inner_table上执行子查询。如果outer_table有很多行而inner_table只有少量行,查询的执行速度或许要慢于本应有的速度。

  前述查询可改写为:

  SELECT a FROM outer_table AS ot, inner_table AS it 
  WHERE ot.a = it.a AND ot.b = it.b;

  在该情况下,我们能扫描小的表(inner_table)并查询outer_table中的行,如果在“ot.a,ot.b”上有索引,速度会更快。

  可能的未来优化:对外部查询的每一行评估关联的子查询。更好的方法是,如果外部行的值与之前的行相比没有变化,不对子查询进行再次评估,而是使用以前的结果。

  可能的未来优化:通过将结果具体化到临时表,而且该表不使用索引,对FROM子句中的子查询进行评估。在查询中与其他表进行比较时,尽管可能是有用的,但不允许使用索引。

  可能的未来优化:如果FROM子句中的子查询类似于可施加MERGE算法的视图,改写查询并采用MERGE算法,以便能够使用索引。下述语句包含这类子查询:

  SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;

  该语句可被改写为联合,如下所示:

  SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;

  这类改写具有两个优点:

  1.避免使用那些不能使用索引的临时表。在改写的查询中,优化程序可在t1上使用索引。

  2.优化程序在选择不同的执行计划方面具有更大的自由。例如,将查询改写为联合,那么就允许优化程序首先使用t1或t2。

  可能的未来优化:对于没有关联子查询的IN、= ANY、<> ANY、= ALL、以及<> ALL,为结果使用“内存中”哈希处理,或对较大的结果使用具有索引的临时表。例如:

  SELECT a FROM big_table AS bt
  WHERE non_key_field IN 
  (SELECT non_key_field FROM table WHERE condition)

  在该情况下,可创建临时表:

  CREATE TABLE t (key (non_key_field)) 
  (SELECT non_key_field FROM table WHERE condition)

  然后,对big_table中的每一行,根据bt.non_key_field,在“t”中进行键查找。

类别:技术文章 | 阅读:117425 | 评论:0 | 标签:

想收藏或者和大家分享这篇好文章→

“MySQL数据库如何限制子查询”共有0条留言

发表评论

姓名:

邮箱:

网址:

验证码:

公告

taoCMS发布taoCMS2.5Beta5(最后更新14年05月25日),请大家速速升级,欢迎大家试用和提出您宝贵的意见建议。

捐助与联系

☟请使用新浪微博联系我☟

☟在github上follow我☟

标签云