8.2.1.2 How MySQL Optimizes WHERE Clauses[译]
8.2.1.2 How MySQL Optimizes WHERE Clauses[译]
不是simaguo 发表于3年前
8.2.1.2 How MySQL Optimizes WHERE Clauses[译]
  • 发表于 3年前
  • 阅读 12
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

摘要: 这节讨论关于WHERE子句的优化,例子使用SELECT语句,但同样有效于DELETE语句和UPDATE语句

下面是一些快速查询的例子:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

下列查询仅使用索引树就可以解决(假设索引的列为数值型,参看14):

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

下列查询使用索引按排序顺序检索行,不用另外的排序

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

你也许牺牲可读性,重写你的查询语句,使算法执行更快。但你能避免这个工作,因为MySQL自动做了相似的优化,使查询语句易于理解和维护。下面是些被MySQL自动做的优化:

注:因为对MySQL的优化一直再进行,所以下面并不是MySQL执行优化全部

1、删除不必要的括号: 

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

2、常数合并:

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

3、常数条件删除(因为需要常数合并):

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

4、索引使用的常量表达式只计算一次。

5、在一个没有where条件的单表中,对于MyISAM和MEMORY表,count(*)直接从表中检索信息。当仅使用一个表时,对NOT NULL表达式也这样做。

6、早发现无效的常数表达式,MySQL会快速检测到某些SELECT语句是不可能的并且返回空行。

7、如果不使用ORDER BY 或者 聚合函数(count(),min()),HAVING将和WHERE合并

8、

9、常数表比其它表先读,常数表的定义:空表或只有一行的表;使用where子句中有主键索引或唯一索引的表,这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL

    常量表实例如下:

SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

10、尝试所有可能性来找到表联接的最好联接组合。如果所有在ORDER BYGROUP BY的列来自同一个表,那么当联接时,该表首先被选中。

11如果有一个ORDER BY子句和不同的GROUP BY子句,或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列,则创建一个临时表。

12、如果使用SQL_SMALL_RESULT,MySQL使用内存中的一个临时表。

13、个表的索引被查询,并且使用最好的索引,除非优化器认为使用表扫描更有效。同时,是否表扫描取决于:使用最好的索引是否跨越超过表总数据的30%,但优化器更加复杂,其评估基于其它因素,如表大小、行数和I/O块大小,因此固定比例(30%)不再决定是选择使用索引还是使用扫描。

14、在一些情况下,MySQL能从索引中读出行,甚至不查询数据文件。如果索引使用的所有列是数值类,那么只使用索引树来进行查询。

15、输出每个记录前,跳过不匹配HAVING子句的行(having子句就是在聚合后对组记录进行筛选)。

用户评论

1、使用OR-situations时索引丢失

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

更快于

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';

2、查询优化也有糟糕的例子,如

SELECT id FROM foo WHERE bar IN (SELECT bar FROM baz WHERE qux='foo')

当foo表是个大表而baz是个很小的表时,子select首先会使用使用消除大部分foo,假如使用下面条语句会怎样呢?

SELECT foo.id FROM foo, baz WHERE foo.bar=baz.bar and baz.qux='foo'

然而,如果第一条操作是DELETE,则后者的举措就不可能了。

3、<>操作,索引被忽略:

SELECT * FROM tab WHERE score <> 0;
使用UNION有明显的效果
(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);


共有 人打赏支持
粉丝 9
博文 26
码字总数 15538
×
不是simaguo
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: