You can create a global index by specifying the GLOBAL keyword in the creation statement. Unlike a local index, the partitioning of a global index is independent of the partitioning of the table. You can specify the partitioning rules and the number of partitions for a global index. These rules and this number do not have to be the same as those of the table.
Create a global index
If you do not specify a partitioning key for a table or specify the table to have only one partition, then the physical location of the data in the global index and that of the data in the table are bound together. They are considered as a whole in migration and leader switching. If a table and a global index have the same partition quantity and boundaries, we recommend that you create a local index instead. The reasons are twofold. First, global indexes cost higher to maintain. Second, the physical storage locations of a table and a global index are not necessarily the same.
Create a global index
Create a HASH-partitioned table tbl1_h, and then create a RANGE-partitioned global index tbl1_h_idx1 for the table.
obclient> CREATE TABLE tbl1_h(col1 INT PRIMARY KEY,col2 INT)
PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected
obclient> CREATE INDEX tbl1_h_idx1 ON tbl1_h(col2) GLOBAL
PARTITION BY RANGE(col2)
(PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200),
PARTITION p2 VALUES LESS THAN(300)
);
Query OK, 0 rows affected
Create a global unique index
Create a RANGE-LIST-subpartitioned table tbl2_f_rl without using a template, and then create a global unique index tbl2_f_rl_idx1 for the table.
obclient> CREATE TABLE tbl2_f_rl(col1 INT,col2 INT)
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST(col2)
(PARTITION p0 VALUES LESS THAN(100)
(SUBPARTITION sp0 VALUES IN(1,3),
SUBPARTITION sp1 VALUES IN(4,6),
SUBPARTITION sp2 VALUES IN(7,9)),
PARTITION p1 VALUES LESS THAN(200)
(SUBPARTITION sp3 VALUES IN(1,3),
SUBPARTITION sp4 VALUES IN(4,6),
SUBPARTITION sp5 VALUES IN(7,9))
);
Query OK, 0 rows affected
obclient> CREATE UNIQUE INDEX tbl2_f_rl_idx1 ON tbl2_f_rl(col1) GLOBAL;
Query OK, 0 rows affected
Partition pruning
The partitioning key of a global index is the index key itself. Therefore, when you use a global index, you must specify the query condition for the partitioning key of the index. You can use the partitioning rule of the index to perform partition pruning. After you get the index key-value pair, you can use the primary key information in the index table to calculate the location of the partition, thereby quickly locating the partition without scanning all table partitions. Therefore, when it is impossible to find a partitioning key for a table, you can use a global index to speed up queries.
Here is an example:
obclient> EXPLAIN SELECT /*+index(t1 gkey)*/ * FROM t1 WHERE b=1;
+---------------------------------------------------------------+
| Query Plan |
+---------------------------------------------------------------+
| ========================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ---------------------------------------------------------- |
| |0 |DISTRIBUTED TABLE SCAN|t1(GKEY)|4950 |38645 | |
| ========================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([t1.A], [t1.B], [t1.C]), filter(nil), rowset=256 |
| access([t1.A], [t1.B], [t1.C]), partitions(p0) |
| is_index_back=true, is_global_index=true, |
| range_key([t1.B], [t1.A]), range(1,MIN ; 1,MAX), |
| range_cond([t1.B = 1]) |
+---------------------------------------------------------------+
In the preceding query, partition pruning is performed based on the b=1 condition in the WHERE clause to get the global index partition p1. Next, a table scan is performed on the global index table to get the primary key. Then, the system uses the primary key to perform a precise scan on the target partition, without scanning all table partitions.