SQL解析与优化器 - 基于行存储的单表查询

本来是想写写如何用递归下降法来做字符解析的。但想了好几周都没想出来这东西应该怎么写会比较容易理解，所以跳过去吧- -，感兴趣的大家可以直接看编译原理了，让我们直接来聊聊优化器吧。
优化器是数据库体系中最难写的东西之一，属于那种第一次想想，特别简单，实际做起来，非常复杂的东西。我们分几篇来探讨，目前想想，应该主要有基于行存储的单表查询，基于列存储的单表查询，字串的倒排索引，join的相关处理，GroupBy/OrderBy/Having的相关处理几个大块，本篇我们主要来谈一谈基于行存储的单表查询处理吧。
因为关系模型的讲解有很多专门的书，这里我想换个角度，用一些实际的例子来阐述我们常见的SQL是怎么被拆解和执行的，以及我们常见的SQL查询在下层所使用的数据结构是怎样的。
首先我们来制造一个场景
我们有个市场，卖家会在这个市场内卖他们的商品，商品必然拥有以下几个关键的属性：
1. 需要一个商品名称 (item_description)
2. 需要一个卖家ID，用来标记这个商品属于哪个卖家 (seller_id)
3. 为了方便操作，需要一个商品的唯一ID (item_id)
我们有4个商品。
商品1 ：item_id = 0 , seller_id = 0 , item_description = “iph0ne”
商品2：item_id = 1 , seller_id = 0 , item_description = “lumei920”
商品3：item_id = 2 , seller_id = 1 , item_description = “razer”
商品4：item_id = 3 , seller_id = 1 , item_description = “基伍”

第一个需要解决的查询是：
Select item_id,seller_id,item_description from item_tab le where item_id = 0
可以看到，在整个数据集中，我们应该返回的是商品1：
商品1：item_id = 0 , seller_id = 0 , item_description = “iph0ne”
那么，我们就必须设计出一种数据结构，能够查询出符合这个要求的数据。在之前，我们已经介绍过了行存储，请在这里复习一下：）
http://blog.sina.cn/dpool/blog/s/blog_693f08470101omuq.html?vt=4
那么我们直接将这些数据按照行存的方式做一个映射，需要指出的是，基于列的存储，在目前大部分的实现中，也是以行存的方式存储的，所以处理的方式也都是类似的：）
map_schema
Key
Value
item_id
是Map_item_id的Key, 在Key中的位置 :0，在value中的位置是0
seller_id
是Map_item_id的Value,在Value中的位置:1
item_description
是Map_item_id的Value,在Value中的位置:2
Map_Item_id
Key
Value
0
0
0
iph0ne
1
1
0
lumei920
2
2
1
razer
3
3
1
基伍
使用上面的这两层Map，我们就可以很容易的发现，只需要先根据map_schema 得知item_id是Map_Item_id的key，所以如果我需要拿到正行记录的话，只需进行一次Map_Item_id.get(0)
就可以拿到value list[0,0,iph0ne] 。
然后就再使用map_schema把用户所需要的数据，按照用户所需要的顺序，返回给用户就行了。
这也就是将SQL查询转换为映射查询的所有情况中最简单的一种了，看起来挺简单的，不是么？
那么，让我们一起 做一个稍微复杂一点点的SQL语句：
Select item_id,seller_id,item_description from item_table where seller_id = 1
与上面的查询相比，这个查询最大的变化是where条件从item_id变成了seller_id。这样，我们没办法再使用map.getKey()的方式来直接查询出数据了。我们该怎么办呢？
一种最容易想到的方式，就是把所有记录拿出来，做一次遍历，然后，将每个value list里面的下标为0的数据都取出来，判断一下是否等于1，如果是，那么返回给用户，如果不等于1，那么直接丢弃掉。
例如：遍历整个结构，依次取出数据，从map_schema表内，我们已经能够知道，位置为1的数据对应seller_id：
Value list[0,0, iph0ne] 位置1的数据为0，丢弃
Value list[1,0,lumei920] 位置1的数据为0，丢弃
Value list[2,1,razer] 位置1的数据为1，符合用户需求，返回
Value list[3,1,基伍] 位置1的数据为1，符合用户需求，返回
这种方式能够满足用户的实际需求，不过这性能么。随着用户数据集的不断扩大，几千条应该是能够在毫秒级返回的，不过如果几万，甚至几百万的情况下，那基本上就得好几百秒才能返回了。代价会非常巨大滴。
那么，有什么办法能够加速这类查询的运行呢？自然是有的，让我们再用一次映射这个魔法吧~
首先，增加一个映射关系，以seller_id作为key，让item_id作为value。
Map_idx_seller_id
Key
Value
0
0
0
1
1
2
1
3
这样我们就能在新的映射中使用Map.get来快速的查找数据了。
然后还需要扩充一下schema信息：
map_schema
Key
Value
item_id
是Map_item_id的Key, 在Key中的位置 :0，在value中的位置是0。
是Map_idx_seller_id的Value,位置是0
seller_id
是Map_item_id的Value,在Value中的位置:1。
是Map_idx_seller_id的Key，位置是0
item_description
是Map_item_id的Value,在Value中的位置:2
这样，我们就可以快速的从Map_idx_seller_id根据seller_id获得Item_id.然后，再使用Map_item_id，根据item_id获得全数据，得到用户所需要的反馈了。这样的做法，效率必然会比刚才说的简单遍历的方式要快很多，因为时间复杂度不再是O(n)，一般来说，我们把这个Map_idx_seller_id的表叫做二级索引，二级的含义嘛其实从简化的角度理解就是走两次映射的意思~
下面让我们回归本源一下，对刚才的做法做一个更深入点儿的分析，上面的这种模式的本质就是空间换时间，通过复制原有数据，再按照新的维度进行重排的方式，让查询变得更快。当然了，世界上没有免费的午餐，二级索引会增加更多的写入次数，从而增加了内存和磁盘的写入tps消耗，也会额外的增加内存和磁盘的空间消耗，同时，因为传统数据库中都要保证写入事务的一致性，因此他必须等待主数据和所有的索引数据全部写成功后，才算是这次写入成功，因此在传统数据库中，单次写入的延迟也会与索引的个数有一定关系，索引的次数越多，单次写入的延迟也就会越大了。不过瑕不掩瑜，索引本身能够极大的让查询变得更快捷，因此也是我们在数据库中所使用的一种最常见的查询加速手段。
下面我们再来看下一个查询
Select item_id,seller_id,item_description from item_table where seller_id = 1 and item_description = “razer”
这个查询呢有两个条件，如果想用最快速的方式查找到数据，那么我们依然可以如法炮制，建立一个新的映射来解决
首先，增加一个映射关系，以seller_id作为key，让item_id作为value。
Map_idx_seller_id_item_descri ption
Key
Value
0
iph0ne
0
0
lumei920
1
1
razer
2
1
基伍
3
然后我们还需要对schema原信息做一些工作
map_schema
Key
Value
item_id
是Map_item_id的Key, 在Key中的位置 :0，在value中的位置是0。
是Map_idx_seller_id的Value,位置是0。
是Map_idx_seller_id_item_description的value,位置是0
seller_id
是Map_item_id的Value,在Value中的位置:1。
是Map_idx_seller_id的Key，位置是0。
是Map_idx_seller_id_item_description的Key,位置是0
item_description
是Map_item_id的Value,在Value中的位置:2
是Map_idx_seller_id_item_description的Key,位置是1
这样，如果我们发现用户需要同时满足seller_id = 1 and item_description = “razer” 需求时，就可以直接通过Map_idx_seller_id_item_description 这个映射关系，拼装seller_id和item_description两个条件在一起组成一个key,从而快速的定位到结果了。
这种索引是二级索引思想的延续，我们一般管他叫组合索引。组合索引能够很好的处理多个条件并列的情况。
但是，处理这个场景的方法并不唯一，我们还可以选择其他的映射完成同样的功能，只是每个索引的代价不一定相同。
方法1： 利用Map_idx_seller_id这个索引，先通过seller_id = 1这个条件，找到所有符合要求的item_id数据，根据这些 item_id,在Map_item_id中找到符合要求的整行数据
Value list[2,1,razer] 和Value list[3,1,基伍] ，然后再使用item_description = “razer” 进行二次过滤，得到最终符合预期的结果：Value list[2,1,razer]，一般的，我们把这个二次过滤的过程叫做“回表”
方法2：直接利用Map_item_id，遍历所有数据，找到那些同时符合seller_id = 1 and item_description = “razer”条件的数据，然后返回。
方法不唯一，就意味着需要做一件事，就是索引选择，这也就是优化器最常做的一件事了，输入是查询条件和索引的一些原信息，比如索引内数据量的大小，区分度如何等。输出则是应该使用哪些映射关系能够以最低的成本来完成查询功能。
是不是想想挺简单的？根据一些条件将所有适合的索引组织到一起不就行了嘛~是个人就能写~
其实则不然，因为查询优化本质来说是个NP完全问题，完全最优策略很难找到，即使找到了代价也非常高。
因此主流的方案基本都是在优化速度与最优策略中选择一个平衡点。
主流的思路主要是基于规则的优化，以及基于成本的优化方式。
规则优化方法，就是设定一些规则，按照顺序进行匹配，只要匹配到就走这条规则。举个最简单的例子：
我们可以设计这样一条规则：只要有二级索引的情况下，就一定会先使用二级索引。
那么也就意味着Select item_id,seller_id,item_description from item_table where seller_id = 1 这条查询一定会先走到Map_idx_seller_id 这个映射规则上，然后再回表到Map_item_id上，最终返回用户所需要的记录。
好处是刚开始写的时候挺简单的，一拍脑袋就能想出几条规则和对应的优先级，然而随着优化更加深入细节，会发现这种方式越来越入不敷出，因为实际上规则不一定是真实情况的反馈，并且，规则和规则之间也可能会有冲突的情况。
比如，如果整张表内只有两个卖家(两个seller_id)，一个卖家(seller_id =1)有1000000条商品在售，另外一个卖家2(sell er_id = 2)呢则只有一条商品在售。
那么这时候，对于Select item_id,seller_id,item_description from item_table where seller_id = 1这条查询来说，走二级索引回表的策略就会显得比较笨拙，而直接在主表上进行全表扫描是效率最高的方式。
那么这时候我们有两个选择，第一个选择是加一条规则，如果区分度在xxx以上，那么直接全表扫描。不过大家也会发现，这种规则的堆叠对系统本身没有任何好处，相反的维护代价会变得非常高。
于是目前主流的方案就是换一个思路，将系统查询都抽象为代价(对内存的读，对磁盘的读，对网络的使用)，然后将不同的方案的代价得分进行加总后取代价最小的方案。
这就是目前主流数据库都在用的基于代价的优化器方案了。
不过，基于代价的优化器也有自己的问题，因为代价这件事很多时候不能提前预测，并且在不同时间点，不同资源的代价也不会完全相同。比如针对磁盘，已经有100个查询在等待的情况下，第101个查询的代价必然的会比没有人在等待大很多，然而系统不可能预测这件事，而力度这么细对系统的编写来说难度也太大了。
我们以前在使用oracle的时候，也经常因为cbo在不同数据量大小的情况下生成了错误的优化策略，直接导致系统down掉的情况。其实原因也是因为cbo本身不够稳定所致，一般来说，长远运行的系统，更希望的是系统资源的可控，也就是说，你可以慢一点，但别突高突低，而自动化的基于代价的优化在稳定性上不大容易能够得到很好地保证。
因此，目前没有完美的方案能够解决一个小小的sql优化问题（笑），不过目前CBO已经是各类数据库的主流配置，所以也只能说是矮子里拔高吧~
下面留个小作业。
还是上面的那张表，有item_id,seller_id,item_description三列数据组成，建有两个索引，map_idx_seller_id 与 map_idx_item_description 。
那么，用户有个查询是
Select item_id,seller_id,item_description from item_table where seller_id = 1 and item_description = “razer ”
这个查询能够同时利用上面的两个索引么？为什么？
好啦，这篇到这里，下篇我们将介绍一下列存的优化策略，并回答一下上面的留下的问题~晚安