OBCP第四章 SQL调优-索引

简介: 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 > ?]


相关文章
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
170 2
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
596 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
64 1
|
3月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
3月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤