Understanding and Optimizing Indexes in OceanBase

1 OceanBase Indexes

Indexes and partitions are core database features; OceanBase is a monolithic distributed architecture that offers high performance, scalability, and availability, where indexes and partitions play a decisive role.

OceanBase provides both local and global indexes. The differences between them are introduced, and a practical optimization example is presented, focusing on key metrics such as is_index_back (whether the query needs a back‑table lookup), is_global_index (whether a global index is used), physical_range_rows and logical_range_rows (the number of rows read), and Plan type (LOCAL, REMOTE, DISTRIBUTED).

Preparation Environment

Create a user1 table and insert sample data.

CREATE TABLE `user1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `phone` int(12) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL
) partition by hash(id+1) partitions 3;

obclient [tpch]> select count(*) from user1;
+----------+
| count(*) |
+----------+
|    79993 |
+----------+
1 row in set (0.025 sec);

delimiter //
create procedure bulk_user()
begin
  declare i int;
  declare phone int;
  set i=100000;
  set phone=1592014273;
  while i<1000001 do
    insert INTO user (id, name, phone, address) values (i,'yang',phone+i,'address');
    set i=i+1;
  end while;
end
//
delimiter ;

2 Index Usage Scenarios

Scenario 1: No Index

obclient [tpch]> explain extended select phone, name from user1 where phone = 1592014286;

The query performs a full‑table scan, locating the target without a back‑table operation.

Scenario 2: Local Index Added

obclient [tpch]> create index idx_user1_phone on user1(phone) local;
Query OK, 0 rows affected (3.152 sec);

explain extended select phone, name from user1 where phone = 1592014286;

After adding a local index, the scan is reduced to 791 rows, but a back‑table lookup occurs.

Scenario 3: Partition Lookup

obclient [tpch]> explain extended select name, phone from user1 where id = 5000;

Using the partition key (id) without any index leads to scanning all 26,664 rows of the selected partition, with no back‑table.

Scenario 4: Partition + Local Index

obclient [tpch]> create index idx_user1_id on user1(id) local;
Query OK, 0 rows affected (3.379 sec);

explain extended select name, phone from user1 where id = 5000;

When the partition column is also indexed, both physical_range_rows and logical_range_rows improve dramatically, yet a back‑table still happens because the query projects columns not covered by the index.

Scenario 5: Unique Local Index to Eliminate Back‑Table

obclient [tpch]> create unique index idx_user_id_phone_name on user1(phone, name, id) local;
Query OK, 0 rows affected (3.352 sec);

explain extended select phone, name from user1 where phone = 1592014286;

A unique local index that includes the partition key (id) covers all projected columns, thus removing the back‑table operation.

Scenario 6: Global Index to Eliminate Back‑Table

create unique index global_idx_phone on user1(phone, name) global;

explain extended select phone, name from user1 where phone = 1592014286;

The global index on phone and name also avoids back‑table lookups.

3 Summary

OceanBase follows a "single‑node first, then distributed" optimization principle: maximize local performance before leveraging distribution, prefer LOCAL plan type, treat REMOTE as secondary, and consider DISTRIBUTED as the worst case. Evaluation should combine scan range and back‑table metrics.

Local indexes are suitable for LOCAL scenarios and help avoid DISTRIBUTED plans, but they must align with the partition key. Unique local indexes that include the partition column can eliminate back‑table operations. Global indexes can also achieve LOCAL performance when designed properly.

Future work will explore OceanBase in a fully distributed environment, where DISTRIBUTED plans become more common.