业务与CBO算法的碰撞

优化从者三方面出发：

存储引擎

1.innodb

2.myisam

3.redis

索引

1.主键索引

2.唯一索引

3.普通索引

4.联合索引

服务层

1.sql接口

2.sql解析器

3.sql优化器

4.sql执行器

---

一条sql的执行过程

首先sql会被加载到sql接口中（sql inface），sql接口会执行sql，将sql发送给sql解析器。

sql解析器：用来解析理解你的sql，判断sql是否有语法问题，字段是否存在等问题，并将sql交给优化器。

sql解析器将解析的sql任务交给sql优化器。

sql优化器：sql读取表中的数据会有多种方式：先取值后判断，先判断后取值。执行哪个方式是sql优化决定。

 sql优化器生成执行计划，并根据执行计划生成物理执行逻辑，发送给sql执行器。

sql执行器：执行sql，将结果返回客户端

存储引擎调用接口，去执行sql。

---

从底层架构出发，优化sql就要从sql优化器出发。

一.首先了解，优化的常客join

        join的优化是sql优化的关键，join的结果往往十分庞大，十分影响性能，所以能优化好joinsql的执行效率会显著提高。

        join的本质就是两张表先通过过滤条件分别得出过滤数据，然后用一定规则进行合并，这样就不可避免的要检索表，等值操作。那么两张表的join前后顺序，用何种join算法就尤为重要，好的join算法能事半功倍。

 二.join算法

常规算法：

nested_Loop join(嵌套循环连接)

foreach Row ro in T1

         foreach Row ri in T2

                if (ri.JoinColValue == ro.JoinColValue)

                Add (ro.RowID, ri.RowID) to ResultSet;

两个循环匹配，外循环叫外表，内循环叫内表。

对于nested-Loop 来说，小表为外表能减少循环次数，效率更高。

 sort merge join

sort-merge join是排序归并连接，先将两张表按连接条件进行排序，排序后根据连接条件进行连接。sort-merge对于两张大表，且连接条件都有是索引的情况下效率最高

 Hash join

hash函数：hash function

hash值：hash function的输出

hash碰撞：不同的输入相同的输出

hash桶：hash碰撞的合集

hash表：hash桶和合集

hash倾斜：某个桶过于大

原理：一个表按照其连接字段进行hash处理，生成对应hash表。另一张表再用连接字段进行hash，去指定的桶中寻找值。

优点：快捷，只需要一次建hash表就可以。

缺点：只能做等值连接，不能范围

 2.分布式join

Shuffle Join

两张表根据连接字段进行重新分布，相同key的字段分布在一个task中，在单机连接。

BroadCast Join

 一张表shuffle到不同task中，另一张表全部复制一份到每一个task中进行join

---

 三.sql优化器算法

sql优化器有两类：

优化算法的本质都是通过一定的filter的rule后再进行处理，减少对表的检索。

RBO与CBO在本质上都是rule，只是CBO有cost。

1.RBO优化器：对原语句进行统一优化，优化结果不保留原语句，只要符合一定规则，优化结果是一样的。

缺点：对于数据变化的表优化过程是一样的，非常不灵活。

2.CBO优化器（optimistic base cost）：对原语句进行优化，优化后保留部分原语句。CEO优化器会生成多条优化语句，然后会根据统计信息和代价模型计算每个语句的cost值，根据cost选取最优的语句。（cost最小不一定最快，CEO算法经常导致执行计划不按照设定好的索引进行检索）。

        优化器中的RBO优化算法适用于sql语法基本不变，每次运行的优化结果都相同，RBO算法是根据规则进行优化，对于这种sql基本不变的sql语句，效果会很好，缺点就是sql语句变化或数据变化，RBO优化不会变化。

        优化器中的CBO优化算法，更适用于sql语句不断变化，数据内容不断更新的sql任务，CBO会根据不同的sql和数据，进行信息统计与成本计算，生成多种计划，选取最小的cost任务。在生产中一般默认是CBO算法，还有会将RBO与CBO合用。

CBO算法详情：CBO大致分为两大步骤：

一.找出所有原始数据（全表扫描）

二.统计数据信息，向父类节点提供过滤信息

• estimatedSize: 每个LogicalPlan节点输出数据大小（解压）
• rowCount: 每个LogicalPlan节点输出数据总条数
• basicStats: 基本列信息，包括列类型、Max、Min、number of nulls, number of distinct values, max column length, average column length等
• Histograms: Histograms of columns, i.e., equi-width histogram (for numeric and string types) and equi-height histogram (only for numeric types).

for example：Hive的CEO优化

A、B、C三张表进行关联，其中A表数据1000kb，B：200Kb，C：4000kb。

按照正常情况下来说语句应该为：

select * from A，B，C on A.id=b.id and A.id=C.id and C.id<100

这样B表在前，符合表为外表的条件，能提供效率。

但是CBO算法，强调的是过滤信息后进行关联，那么就应该是C表进行 Where 后的条数进行join，那么如果C表where后为100kb后，是不是比B表更适合做外表呢。

所以CBO最后的执行语句应该为

select * form A,C,B on A.id=C.id and A.id=B.id and C.id<100

此时的执行路径cost最小。

最后选出最优执行顺序，再选取最优join算法。（hash，sort merge，nested-loop）

以上就是从服务处出发，对SQL的调优思路。