OBCP第四章 SQL调优-局部索引与全局索引

简介: OBCP第四章 SQL调优-局部索引与全局索引

主键和二级索引

主表

指使用CREATE TABLE语句创建的表对象。也是索引对象所依赖的表(即CREATE INDEX语句中ON子句所指定的表)

主键

OceanBase的每一张表都有主键,并在内部以主键为序组织数据。如果在创建用户表时不显式指定主键,系统会自动为表生成隐藏主键,隐藏主键不可被查询


索引(索引表)

指使用CREATE INDEX语句创建的索引对象。有时为了便于大家理解,也会把索引对象类比为一个表对象,即索引表

传统“非分区表”中主表和索引的关系

传统的“非”分区表中,主表和索引的对应关系:

主表的所有数据都保存在一个完整的数据结构中,主表上的每一个索引也对应一个完整的数据结构(比如最常见的B+ Tree),主表的数据结构和索引的数据结构之间是一对一的关系,如下图所展示,在employee表中,以emp_id创建的索引:

局部索引与全局索引


当分区表出现之后,情况发生了变化:主表的数据按照分区键(Partitioning Key)的值被分成了多个分区,每个分区都是独立的数据结构,分区之间的数据没有交集。这样一来,索引所依赖的单一数据结构不复存在,那索引需要如何应对呢?——这就引入了“局部索引”和“全局索引”两个概念。

局部索引

局部索引又名分区索引,创建索引的分区关键字是LOCAL,分区键等同于表的分区键,分区数等同于表的分区数,总之,局部索引的分区机制和表的分区机制一样

全局索引

全局索引的创建规则是在索引属性中指定GLOBAL关键字,与局部索引相比,全局索引最大的特点是全局索引的分区规则跟表分区是相互独立的,全局索引允许指定自己的分区规则和分区个数,不一定需要跟表分区规则保持一致

局部索引

分区表的局部索引和非分区表的索引类似,索引的数据结构还是和主表的数据结构保持一对一的关系,但由于主表已经做了分区,主表的“每一个分区”都会有自己单独的索引数据结构。局部索引的结构如下图所示:

全局索引-全局非分区索引

分区表的全局索引不再和主表的分区保持一对一的关系,而是将所有主表分区的数据合成一个整体来建立全局索引。更进一步,全局索引可以定义自己独立的数据分布模式,既可以选择非分区模式也可以选择分区模式 :

全局非分区索引(Global Non-Partitioned Index)

全局分区索引(Global Partitioned Index)

全局索引-全局分区索引

分区表的全局索引不再和主表的分区保持一对一的关系,而是将所有主表分区的数据合成一个整体来建立全局索引。更进一步,全局索引可以定义自己独立的数据分布模式,既可以选择非分区模式也可以选择分区模式 :


全局非分区索引(Global Non-Partitioned Index)

全局分区索引(Global Partitioned Index)

功能需求:在表的‘分区键无关’的字段上建唯一索引

局部索引在“索引键没有包含主表所有的分区键字段”的情况下,此时索引键值对应的索引数据在所有分区中都可能存在。如下图,employee按照emp_id做了分区,但同时想利用局部索引建立关于emp_name的唯一约束是无法实现的。


由于某索引键值在所有分区的局部索引上都可能存在,索引扫描必须在所有的分区上都做一遍,以免造成数据遗漏。这会导致索引扫描效率低下,并且会在全局范围内造成CPU和IO资源的浪费


全局非分区索引与全局分区索引的比较

全局索引的分区键一定是索引键的前缀

全局非分区索引 :

此时索引的结构和“非分区”表没有区别,只有一个完整的索引树,自然保证唯一性。并且只有一个完整的索引树,自然没有多分区扫描的问题

全局分区索引

数据只可能落在一个固定的索引分区中,因此每个索引分区内保证唯一性约束,就能在全表范围内保证唯一性约束全局索引能保证某一个索引键的数据只落在一个固定的索引分区中 ,所以无论是针对固定键值的索引扫描,还是针对一个键值范围的索引扫描,都可以直接定位出需要扫描的一个或者几个分区


局部索引与全局索引的执行计划的比较

局部索引

create table t_p_hash (c1 varchar(20),c2 int, c3 varchar(20)) partition by hash(c2) partitions 3;
create index idx_t_p_hash_c1 on t_p_hash (c1) local;
create index idx_t_p_hash_c3 on t_p_hash (c3) local;
explain extended select c1,c2 from t_p_hash where c3='100';

局部索引与全局索引的执行计划的比较

全局索引

create table t_p_key (c1 varchar(20),c2 int,c3 varchar(20)) partition by key (c2) partitions 3;
create unique index idx_t_p_key_c3_g on t_p_key (c3) global partition by key (c3) partitions 3;
explain extended select c1,c2 from t_p_key where c3='66';


局部索引与全局索引的取舍

如果查询条件里“包含完整的分区键”,使用本地索引是最高效的


如果需要“不包含完整分区键”的唯一约束:


用全局索引


或者本地索引,且需要索引列上必须带上表的分区键


其他情况,case by case:


通常来说,全局索引能为高频且精准命中的查询(比如单记录查询)提速并减少IO;对范围查询则不一定哪种索引效果更好


不能忽视全局索引在DML语句中引入的额外开销:数据更新时带来的跨机分布式事务,事务的数据量越大则分布式事务越复杂


如果数据量较大,或者容易出现索引热点,可考虑创建全局分区索引


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