路径选择(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 > ?]