OBCP第四章 SQL调优-索引

路径选择（Access Path Selection）

访问路径是指数据库中访问表的方法，即使用哪个索引来访问表。

访问路径的分析是单表查询的最重要的问题之一，对于使用主表扫描的访问路径来说，执行时间一般与需要扫描的数据量（范围）成正比。

对于有合适索引的查询，使用索引可以大大减小数据的访问量，因此对于使用主表扫描的查询，要分析没有选中索引扫描的原因，是由于不存在可用的索引，还是索引扫描范围过大以至于代价过高。

何为路径：主键，二级索引

如何选择：规则模型 前置规则（正向）

剪枝规则（反向）

          代价模型

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

路径选择-索引回表

 

路径选择

目前仅支持B+索引

两种访问

  get:索引键全部等值覆盖

  scan:返回有序数据

字符串条件：‘T%’（ ‘%T%’, ‘%T’无法利用索引）

扫描顺序由优化器智能决定

路径选择-覆盖索引

该访问路径是否需要回表？

如果一个访问路径中包含了该查询所需要的所有列，那么该路径就不需要回表，反之，该路径就需要回表

create table t2(c1 int primary key, c2 int, c3 int, c4 int, index t2_c2(c2));

 

路径选择-Interesting Order

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

create table t1(c1 int primary key, c2 int, c3 int);
explain select * from t1 order by c1 desc;

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;

OceanBase (root@oceanbase)> explain 
extended select * from t1 where a = 1 order 
by b;

连接顺序

不同的连接顺序对执行效率影响极大

目前只考虑左深树（某些特定场景除外）

搜索空间

对内存占用更友好

连接顺序的选择是一个动态规划的过程

可通过hint指定连接顺序

存在显式连接条件的连接优先于笛卡尔积连接

                            

	左/右深树	多枝树
优势	搜索空间小 更利于流水线 内存空间小	充分系统并行能力 可能生成更好计划
劣势	无法利用并行执行 可能错失更佳的执行计划	搜索空间巨大 执行消耗资源多

创建高效索引

索引表与普通数据表一样都是实体表，在数据表进行更新的时候会先更新索引表然后再更新数据表

索引要全部包含所查询的列：包含的列越全越好，这样可以尽可能的减少回表的行数

等值条件永远放在最前面

过滤与排序数据量大的放前面

选择具有高选择性、频繁在where 从句中出现、频繁在join关联字段中的字段

不对函数或表达式中的字段建索引，要么就建函数索引

创建一个索引时，评估该索引给查询带来的性能优化是否比因其而引起INSERT，UPDATE，DELETE操作的性能下降以及索引占用的空间更要值得

在常被修改到字段上建索引需要进行评估

创建索引、

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 )

创建索引-等值查询

等值查询

索引中的字段	命中索引的SQL	未命中索引的SQL
（A,B,C）	where A = ? and B = ? and C = ? where A = ? and B = ? where A = ? and C = ?	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=?]

创建索引-范围查询

范围查询

索引中的字段	命中索引的SQL	未命中索引的SQL
(A,B,C)	where A > ? and B > ? and C < ? where A > ? and B > ? where A > ? and C < ?	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字段的索引

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

等值和范围查询

索引中的字段	命中索引的SQL	未命中索引的SQL
(A,B,C)	where A = ? and B = ? and C > ? where A = ? and B > ? and C = ? where A = ? and B > ? and C > ?	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 > ?]