【OceanBase】31.OB索引管理

1.路径选择（Access Path Selection）

◼ 访问路径是指数据库中访问表的方法，即使用哪个索引来访问表。
◼ 访问路径的分析是单表查询的最重要的问题之一，对于使用主表扫描的访问路径来说，
执行时间一般与需要扫描的数据量（范围）成正比。
◼ 对于有合适索引的查询，使用索引可以大大减小数据的访问量，因此对于使用主表扫描的查询，
要分析没有选中索引扫描的原因，是由于不存在可用的索引，还是索引扫描范围过大以至于代价过高。

--何为路径
⚫ 主键
⚫ 二级索引

--如何选择
⚫ 规则模型
➢ 前置规则（正向）
➢ 剪枝规则（反向）
⚫ 代价模型

考虑因素
⚫ 扫描范围
⚫ 是否回表
⚫ 路径宽度
⚫ 过滤条件
⚫ Interesting order

2.路径选择–索引回表

------------------                            ----------------
MemTable内存数据 | \                       / |MemTable内存数据| \
SSTable基线数据  | /fuse主键row key-->回表 \ |SSTable基线数据 | /fuse-->返回数据
-----------------                            -------------------
索引表                                           主表 

--路径选择
◼ 目前仅支持B+索引
◼ 两种访问
⚫ get：索引键全部等值覆盖
⚫ scan：返回有序数据
◼ 字符串条件：‘T%’（ ‘%T%’, ‘%T’无法利用索引）
◼ 扫描顺序由优化器智能决定

3.路径选择-覆盖索引

该访问路径是否需要回表？
如果一个访问路径中包含了该查询所需要的所有列，那么该路径就不需要回表，反之，该路径就需要回表
create table t2(c1 int primary key, c2 int, c3 int, c4 int, index t2_c2(c2));
OceanBase (root@oceanbase)> explain 
select c2 from t2; 
========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------
|0 |TABLE SCAN|t2(t2_c2)|1000 |1145|   --只查c2列，使用了t2_c2的索引
========================================
Outputs & filters: 
-------------------------------------
0 - output([t2.c2]), filter(nil), 
access([t2.c2]), partitions(p0)

OceanBase (root@oceanbase)> explain select  c1, c2 from t2;
========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------
|0 |TABLE SCAN|t2(t2_c2)|1000 |1180|  --同时查了c1、c2列，为什么也使用了索引？覆盖索引（主键补全）
========================================
Outputs & filters: 
-------------------------------------
0 - output([t2.c1], [t2.c2]), filter(nil), 
access([t2.c1], [t2.c2]), partitions(p0)

4.路径选择-Interesting Order

优化器通过 Interesting Order 利用底层的序，就不需要对底层扫描的行做排序，还可以消除 ORDER BY，
进行MERGE GROUP BY，提高 Pipeline（不需要进行物化）等
OceanBase (root@oceanbase)> create table t1(c1 int primary key, c2 int, c3 int);
OceanBase (root@oceanbase)> explain select * from t1 order by c1;
| ===================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------
|0 |TABLE SCAN|t1 |1000 |2327|  --指定了order by进行排序实际未进行排序
===================================
Outputs & filters: 
-------------------------------------
0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), 
access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)

5.路径选择-逆序索引扫描

优化器通过 Interesting Order 利用底层的序，就不需要对底层扫描的行做排序，还可以消除 ORDER BY，进行
MERGE GROUP BY，提高 Pipeline（不需要进行物化）等

OceanBase (root@oceanbase)> create table t1(c1 int primary key, c2 int, c3 int);
OceanBase (root@oceanbase)> explain select * from t1 order by c1 desc;
| ==========================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
------------------------------------------
|0 |TABLE SCAN|t1(Reverse)|1000 |2327| --指定了倒序排序采用了逆序索引扫描
==========================================
Outputs & filters: 
-------------------------------------
0 - output([t1.c1], [t1.c2], [t1.c3]), filter(nil), 
access([t1.c1], [t1.c2], [t1.c3]), partitions(p0)

6.OB的索引选择

◼ OB的索引选择有大量的规则挡在代价模型之前
⚫ 正向规则: 一旦命中规则直接选择该索引
➢ 命中唯一性索引
⚫ 逆向规则(skyline剪枝规则)
➢ 通过比较两个索引, 剪掉一些比较”差”的索引（Query range, 序，是否需要回表）
➢ 剩下的索引通过代价模型选出
create table t1(a int , b int, c int, unique key idx1(a, b), key idx2(b));
OceanBase (root@oceanbase)> explain 
extended select * from t1 where a = 1 and b = 1;
Optimization Info:
-------------------------------------
t1:optimization_method=rule_based, 
heuristic_rule=unique_index_with_indexback  --命中唯一索引


OceanBase (root@oceanbase)> explain 
extended select * from t1 where a = 1 order by b;
Optimization Info:
-------------------------------------
t1:optimization_method=cost_based, 
avaiable_index_name[t1,idx1],pruned_index_name[idx2]--保留较好的索引idx1，裁掉了较差的idx2

7.连接顺序

◼ 不同的连接顺序对执行效率影响极大
◼ 目前只考虑左深树（某些特定场景除外）
⚫ 搜索空间
⚫ 对内存占用更友好
◼ 连接顺序的选择是一个动态规划的过程
◼ 可通过hint指定连接顺序
◼ 存在显式连接条件的连接优先于笛卡尔积连接

左/右深树 :
优势
搜索空间小
更利于流水线
内存空间（foot-print）小

劣势
无法利用并行执行
可能错失更佳的执行计划

多枝树
优势：充分系统并行能力可能生成更好计划
劣势：搜索空间巨大执行消耗资源多

8.创建高效索引

◼ 索引表与普通数据表一样都是实体表，在数据表进行更新的时候会先更新索引表然后再更新数据表
◼ 索引要全部包含所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数
◼ 等值条件永远放在最前面
◼ 过滤与排序数据量大的放前面
◼ 选择具有高选择性、频繁在where 从句中出现、频繁在join关联字段中的字段
◼ 不对函数或表达式中的字段建索引，要么就建函数索引
◼ 创建一个索引时，评估该索引给查询带来的性能优化是否比因其而引起INSERT，UPDATE，DELETE操作的性能下
降以及索引占用的空间更要值得
◼ 在常被修改到字段上建索引需要进行评估

9.创建索引

◼ OceanBase 数据库支持在非分区表和分区表上创建索引，索引可以是局部索引或全局索引，也可以是唯一索引或普
通索引。如果是分区表的唯一索引，则唯一索引必须包含表分区的拆分键。
◼ 可以对一张表的单列或多列创建索引来提高表查询速度。创建合适的索引，能够减少对磁盘的读写
⚫ 建表的时候创建，立即生效
⚫ 建表后再创建索引，是同步生效，表中数据量大时需要等待一段时间

创建索引（MySQL/Oracle模式）
CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] 
[ PARTITION BY column_list PARTITIONS N ] ;

创建索引（MySQL模式）
ALTER TABLE table_name ADD INDEX|KEY index_name ( column_list ) ;

创建索引-等值查询
--等值查询
索引中的字段：(A,B,C)
--命中索引的SQL：
where A = ? and B = ? and C = ?
where A = ? and B = ?
where A = ? and C = ?


--未命中索引的SQL
where B = ? and C = ?
where C = ?
条件的先后顺序不影响索引能效，如where A = ? and B = ? 和 where B = ? and A = ? 效果相同
从索引能效来看: 【Where A =? And B=? and C=?】>【Where A=? and B=? 】> 【Where A=? and C=?】

创建索引-范围查询
范围查询

索引中的字段:(A,B,C)
命中索引的SQL:
where A > ? and B > ? and C < ?
where A > ? and B > ?
where A > ? and C < ?

未命中索引的SQL
where B > ? and C < ?
where C in (?,?)
常见的范围查询有： 大于、小于、大于等于、小于等于、between…and 、 in(?,?)
遇到第一个范围查询字段后，后续的字段不参与索引过滤（不走索引）
如【where A > ? and B > ? and C < ?】、【where A > ? and B > ?】 、
【where A > ? and C< ?】 只能走A字段的索引


--创建索引-等值和范围查询

索引中的字段:(A,B,C)
--命中索引的SQL
where A = ? and B = ? and C > ?
where A = ? and B > ? and C = ?
where A = ? and B > ? and C > ?

--未命中索引的SQL
where B > ? and C < ?
where C in (?,?)
where C = ?

遇到第一个范围查询字段后，后续的字段不参与索引过滤（不走索引）
从索引能效看：
【where A = ? and B = ? and C > ?】>【where A = ? and B > ? and C > ?】
【where A = ? and B > ? and C = ?】=【where A = ? and B > ? and C > ?】