上一篇，我们介绍了《DB——数据的读取和存储方式》，这篇聊聊sql优化器的工作。

关系型数据库的一大优势之一，用户无需关心数据的访问方式，因为这些优化器都帮我们处理好了，但sql查询优化的时候，我不得不要对此进行关注，因为这牵扯到查询性能问题。

有经验的程序员都会对一些sql优化了如指掌，比如我们常说的最左匹配原则，非BT谓词规避等等，那么优化器是如何确定这些的？以及为何一定要最左匹配，最左匹配的原理是什么，你是否有深入了解？

这一篇我们就通过一些实例来剖析优化器做了哪些工作，以方便我们更好的优化SQL查询。

本篇你可以知道:

sql的访问路径是什么

优化器如何确定最优访问路径

最左匹配的原则依据是什么

如何有效的评估sql命中行数

示例table:

CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(100) DEFAULT NULL, sex int(11) DEFAULT NULL, age int(11) DEFAULT NULL, c_date datetime DEFAULT NULL, PRIMARY KEY (id),

索引

KEY id_name_sex (id,user_name,sex), KEY name_sex_age (user_name,sex,age) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

一、访问路径

在SQL语句能够被真正执行之前，优化器必须首先确定如何访问数据。这包括：应该使用哪一个索引，索引的访问方式如何，是否需要辅助式随机读，等等。

从一条SQL，到优化器优化，再到引擎进行数据查询，落地到数据的存储页面，这是一个访问路径确定的过程。

Sql优化器究竟帮你做了哪些工作？

二、谓词

谓词就是我们常说的where子句中的一个或多个搜索参数组成。谓词表达式是索引设计的主要入手点，如果一个索引能够满足select查询语句的所有谓词表达式，那么优化器就可能建立一个高效的访问路径。

select * from test where id =1 and user_name like ’test%’

比如，上述查询中，where后面的搜索参数，id 和user_name 就是谓词。

三、索引片

索引片即代表谓词表达式所确定的值域范围，而访问路径的成本很大程度上取决于索引片的厚度。

索引片越厚，需要扫描的索引页就越多，需要处理的索引记录也越多，而且最大的开销还是来自于需要对标进行同步读操作。相反，索引片比较窄，就会显著减少索引访问的那部分开销，同时会有更少的表同步读取上。

同步读是一个随机IO操作，单次的读取就要耗费10ms左右的时间。这个我们在上篇有说明。

比如：

//会匹配到5个数据 sql1:select * from test where sex=1; // 匹配到2个数据 sql2：select * from test where sex=1 and age <10;

Sql优化器究竟帮你做了哪些工作？

因此我们需要通过谓词来确定索引片的厚度，过滤的值域范围越少，索引片厚度就越窄。那么谓词一定就能匹配到索引么，或者说匹配的规则是什么？

四、匹配列&过滤列

谓词不一定都能匹配到索引，能够匹配上的，我们称之为匹配列。此时它可以参与索引片的定义。

只有匹配列和过滤列可以参与索引片的定义和过滤，其他不可。

我们来看下谓词匹配的定义：

检查索引列，从头到尾依次检查索引列，查看以下规则:

在where子句中，该列是否至少拥有一个足够简单的谓词与之对应？如果有，则这个列就是匹配列。如果没有，那么这个列及其后面的索引列都是非匹配列。

谓词是否是一个范围谓词，如果是，那么剩余的索引列都是非匹配列。

对于最后一个匹配列之后的索引列，如果拥有一个足够简单的谓词与其对应，那么该列为过滤列。

1、示例

select * from test where user_name=’test1’ and sex>0 and age =10

发现索引id_name_sex

逐行检查其索引列(id,user_name,sex)

首先检查 id,发现where后面的谓词没有与之对应，则这个索引列以及后面的索引列都是非匹配列

索引id_name_sex匹配结束,无匹配列

发现索引name_sex_age

逐行检查其索引列(user_name,sex,age)

首先检查 user_name,发现where后面的谓词 user_name 有与之对应，认定此列为匹配列

检查索引字段sex,发现where后面有谓词sex与之对应，认定此列为匹配列，由于谓词sex是范围谓词，则剩余的索引为非匹配列。

索引列age 是在最后一个匹配列sex 之后，而又有谓词age 与之对应，因此此列为过滤列,

通过这个示例，我们最终确定了：

匹配索引: name_sex_age

匹配列: user_name,sex

过滤列: age

我们查看下 explain ,和我们分析的对应。

Sql优化器究竟帮你做了哪些工作？

2、确定匹配列有什么用

确定匹配列之后我们可以知道当前的查询会用到哪些索引，以及匹配到该索引的哪些列，最终可以提前锁定数据的访问范围，为数据的读取节省读取压力。

相对于没用匹配到索引的查询，有匹配列的查询，条件过滤是前置的，而没有匹配到索引的查询，条件过滤是后置的，即全表扫描之后，再过滤结果，如此磁盘IO压力过大。

另外 “最左匹配”原则也是基于匹配列规则而来，为何是最左匹配，除了B树的原理之外，还有一个重要的原因，在核对匹配列的时候，是从头到尾依次检查索引列。

所以对于是否能够匹配到索引,where后面的谓词顺序不重要，重要的是索引列的顺序。

比如：

select * from test where user_name=’test1’ and sex>0 and age =10 select * from test where sex>0 and user_name=’test1’ and age =10 select * from test where age =10 and user_name='test1' and sex>0

都可以匹配到name_sex_age 索引

Sql优化器究竟帮你做了哪些工作？