Mysql查询优化器浅析(下)
当我们评估一个条件表达式,MySQL判断该表达式的存取类型。下面是一些存取类型,按照从最优到最差的顺序进行排列:
以下是引用片段: system … 系统表,并且是常量表 const … 常量表 eq_ref … unique/primary索引,并且使用的是’=’进行存取 ref … 索引使用’=’进行存取 ref_or_null … 索引使用’=’进行存取,并且有可能为NULL range … 索引使用BETWEEN、IN、>=、LIKE等进行存取 index … 索引全扫描 ALL … 表全扫描 |
优化器根据存取类型选择合适的驱动表达式。考虑如下的查询语句:
以下是代码片段: SELECT * FROM Table1 WHERE indexed_column = 5 AND unindexed_column = 6 |
因为indexed_column拥有更好的存取类型,所以更有可能使用该表达式做为驱动表达式。这里只考虑简单的情况,不考虑特殊的情况。
那么驱动表达式的意思是什么呢?考虑到这个查询语句有两种可能的执行方法:
1) 不好的执行路径:读取表的每一行(称为“全表扫描”),对于读取到的每一行,检查相应的值是否满足indexed_column以及unindexed_column对应的条件。
2) 好的执行路径:通过键值indexed_column=5查找B树,对于符合该条件的每一行,判断是否满足unindexed_column对应的条件。
一般情况下,索引查找比全表扫描需要更少的存取路径,尤其当表数据量很大,并且索引的类型是UNIQUE的时候。因此称它为好的执行路径,使用indexed_column列作为驱动表达式。
8 范围存取类型
一些表达式可以使用索引,但是属于索引的范围查找。这些表达式通常对应的操作符是:>、>=、<、<=、IN、LIKE、BETWEEN。
对优化器而言,如下表达式:
以下是代码片段: column1 IN (1,2,3) |
该表达式与下面的表达式是等价的:
以下是代码片段: column1 = 1 OR column1 = 2 OR column1 = 3 |
并且MySQL也是认为它们是等价的,所以没必要手动将IN改成OR,或者把OR改成IN。
优化器将会对下面的表达式使用索引范围查找:
以下是代码片段: column1 LIKE ’x%’ |
但对下面的表达式就不会使用到索引了:
以下是代码片段: column1 LIKE ’%x’ |
这是因为当首字符是通配符的时候,没办法使用到索引进行范围查找。
对优化器而言,如下表达式:
以下是代码片段: column1 BETWEEN 5 AND 7 |
该表达式与下面的表达式是等价的:
以下是代码片段: column1 >= 5 AND column1 <= 7 |
同样,MySQL也认为它们是等价的。
如果需要检查过多的索引键值,优化器将放弃使用索引范围查找,而是使用全表扫描的方式。这样的情况经常出现如下的情况下:索引是多层次的二级索引,查询条件是'<'以及是'>'的情况。
9 索引存取类型
考虑如下的查询语句:
以下是代码片段: SELECT column1 FROM Table1; |
如果column1是索引列,优化器更有可能选择索引全扫描,而不是采用表全扫描。这是因为该索引覆盖了我们所需要查询的列。
再考虑如下的查询语句:
以下是代码片段: SELECT column1,column2 FROM Table1; |
如果索引的定义如下,那么就可以使用索引全扫描:
以下是代码片段: CREATE INDEX … ON Table1(column1,column2); |
也就是说,所有需要查询的列必须在索引中出现。
10转换
MySQL对简单的表达式支持转换。比如下面的语法:
以下是代码片段: WHERE -5 = column1 |
转换为:
以下是代码片段: WHERE column1 = -5 |
尽管如此,对于有数学运算存在的情况不会进行转换。比如下面的语法:
以下是代码片段: WHERE 5 = -column1 |
不会转换为:
以下是代码片段: WHERE column1 = -5 |
11 AND
带AND的查询的格式为:<condition> AND <condition>,考虑如下的查询语句:
以下是代码片段: WHERE column1=’x’ AND column2=’y’ |
优化的步骤:
1) 如果两个列都没有索引,那么使用全表扫描。
2) 否则,如果其中一个列拥有更好的存取类型(比如,一个具有索引,另外一个没有索引;再或者,一个是唯一索引,另外一个是非唯一索引),那么使用该列作为驱动表达式。
3) 否则,如果两个列都分别拥有索引,并且两个条件对应的存取类型是一致的,那么选择定义索引时的先定义的索引。
举例如下:
以下是代码片段: CREATE TABLE Table1 (s1 INT,s2 INT); CREATE INDEX Index1 ON Table1(s2); CREATE INDEX Index2 ON Table1(s1); … SELECT * FROM Table1 WHERE s1=5 AND s2=5; |
优化器选择s2=5作为驱动表达式,因为s2上的索引是新建的。
12 OR
带OR的查询格式为:<condition> OR <condition>,考虑如下的查询语句:
以下是代码片段: WHERE column1=’x’ OR column2=’y’ |
优化器做出的选择是采用全表扫描。
当然,在一些特定的情况,可以使用索引合并,这里不做阐述。
如果两个条件里面设计的列是同一列,那么又是另外一种情况,考虑如下的查询语句:
以下是代码片段: WHERE column1=’x’ OR column1=’y’ |
在这种情况下,该查询语句采用索引范围查找。
13 UNION
所有带UNION的查询语句都是单独优化的,考虑如下的查询语句:
以下是代码片段: SELECT * FROM Table1 WHERE column1=’x’ UNION ALL SELECT * FROM Table1 WHERE column2=’y’ |
如果column1与column2都是拥有索引的,每个查询都是使用索引查询,然后合并结果集。
14 NOT,<>
考虑如下的表达式:
以下是代码片段: Column1<> 5 |
从逻辑上讲,该表达式等价于下面的表达式:
以下是代码片段: Column1<5 OR column1>5 |
然而,MySQL不会进行这样的转换。如果你觉得使用范围查找会更好一些,应该手动地进行转换。
考虑如下的表达式:
以下是代码片段: WHERE NOT (column1!=5) |
从逻辑上讲,该表达式等价于下面的表达式:
以下是代码片段: WHERE column1=5 |
同样地,MySQL也不会进行这样的转换。
15 ORDER BY
一般而言,ORDER BY的作用是使结果集按照一定的顺序排序,如果可以不经过此操作就能产生顺序的结果,可以跳过该ORDER BY操作。
考虑如下的查询语句:
以下是代码片段: SELECT column1 FROM Table1 ORDER BY ’x’; |
优化器将去除该ORDER BY子句,因为此处的ORDER BY子句没有意义。
再考虑另外的一个查询语句:
以下是代码片段: SELECT column1 FROM Table1 ORDER BY column1; |
在这种情况下,如果column1类上存在索引,优化器将使用该索引进行全扫描,这样产生的结果集是有序的,从而不需要进行ORDER BY操作。
再考虑另外的一个查询语句:
以下是代码片段: SELECT column1 FROM Table1 ORDER BY column1+1; |
假设column1上存在索引,我们也许会觉得优化器会对column1索引进行全扫描,并且不进行ORDER BY操作。实际上,情况并不是这样,优化器是使用column1列上的索引进行全扫表,仅仅是因为索引全扫描的效率高于表全扫描。对于索引全扫描的结果集仍然进行ORDER BY排序操作。
16 GROUP BY
这里列出对GROUP BY子句以及相关集函数进行优化的方法:
1) 如果存在索引,GROUP BY将使用索引。
2) 如果没有索引,优化器将需要进行排序,一般情况下会使用HASH表的方法。
3) 如果情况类似于“GROUP BY x ORDER BY x”,优化器将会发现ORDER BY子句是没有必要的,因为GROUP BY产生的结果集是按照x进行排序的。
4) 尽量将HAVING子句中的条件提升中WHERE子句中。
5) 对于MyISAM表,“SELECT COUNT(*) FROM Table1;”直接返回结果,而不需要进行表全扫描。但是对于InnoDB表,则不适合该规则。补充一点,如果column1的定义是NOT NULL的,那么语句“SELECT COUNT(column1) FROM Table1;”等价于“SELECT COUNT(*) FROM Table1;”。
6) 考虑MAX()以及MIN()的优化情况。考虑下面的查询语句:
以下是代码片段: SELECT MAX(column1) FROM Table1 WHERE column1 < ’a’; |
如果column1列上存在索引,优化器使用'a'进行索引定位,然后返回前一条记录。
7) 考虑如下的查询语句:
以下是代码片段: SELECT DISTINCT column1 FROM Table1; |
在特定的情况下,语句可以转化为:
以下是代码片段: SELECT column1 FROM Table1 GROUP BY column1; |
该转换的前提条件是:column1上存在索引,FROM上只有一个单表,没有WHERE条件并且没有LIMIT条件。
建议继续学习:
- Mysql查询优化器浅析(上) (阅读:2541)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询 (阅读:2493)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询 (阅读:2115)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询 (阅读:2039)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析 (阅读:1987)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数 (阅读:1920)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之附录 (阅读:1869)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息 (阅读:1665)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析 (阅读:1561)
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:yzyangwanfu 来源: 杨万富的专栏
- 标签: 查询优化器
- 发布时间:2009-10-19 23:48:10
- Mysql查询优化器浅析(上)
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之单表unique查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之多表简单JOIN查询
- MySQL数据库InnoDB存储引擎查询优化器实现的分析
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之optimizer_search_depth参数
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之附录
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之统计信息
- MySQL数据库InnoDB存储引擎查询优化器实现的分析之best_access_path函数分析
- [34] 界面设计速成
- [32] 视觉调整-设计师 vs. 逻辑
- [31] 如何拿下简短的域名
- [31] Oracle MTS模式下 进程地址与会话信
- [31] 程序员技术练级攻略
- [29] android 开发入门
- [29] IOS安全–浅谈关于IOS加固的几种方法
- [26] 读书笔记-壹百度:百度十年千倍的29条法则
- [26] 【社会化设计】自我(self)部分――欢迎区
- [26] 图书馆的世界纪录