Use global secondary indexes

PolarDB-X 1.0 supports global secondary indexes (GSIs). This topic describes how to create GSIs and use the GSI feature.

Prerequisites

The MySQL version must be 5.7 or later, and the minor version of the kernel must be 5.4.1 or later.

Create a GSI

PolarDB-X 1.0 has extended MySQL data definition language (DDL) syntax by adding syntax that is used to define GSIs. The syntax usage is the same as the usage of syntax that is used to create indexes on MySQL.

Define a GSI when you create a table
Add a GSI after you create a table

Note

Index name: used as the name of an index table to create the index table.
Index column: the shard key of the index table, that is, all the columns that are used in the sharding clause of the index.
Covering column: other columns in the index table. By default, the primary key and all the shard keys of the primary table are included.
Sharding clause of the index: the sharding algorithm of the index table. Its syntax is the same as the syntax of the sharding clause in CREATE TABLE.

Examples：

# Define a GSI when you create a table.
CREATE TABLE t_order (
 `id` bigint(11) NOT NULL AUTO_INCREMENT,
 `order_id` varchar(20) DEFAULT NULL,
 `buyer_id` varchar(20) DEFAULT NULL,
 `seller_id` varchar(20) DEFAULT NULL,
 `order_snapshot` longtext DEFAULT NULL,
 `order_detail` longtext DEFAULT NULL,
 PRIMARY KEY (`id`),
 GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition by hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
# Add a GSI.
CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`) 
    COVERING(`seller_id`, `order_snapshot`) 
    dbpartition by hash(`buyer_id`) tbpartition by hash(`buyer_id`) tbpartitions 3

Use a GSI

After you create a GSI, you can enable a query to use an index table by using the following methods:

Use HINT to specify indexes

You can choose one of the following two HINT statements to specify and use the index for the query:
- Statement:
```
  FORCE INDEX({index_name})
```
  Example：
```
  SELECT a.*, b.order_id 
   FROM t_seller a 
     JOIN t_order b FORCE INDEX(g_i_seller) ON a.seller_id = b.seller_id 
   WHERE a.seller_nick="abc";
```
- Statement:
```
  /*+TDDL:INDEX({table_name/table_alias}, {index_name})*/
```
  Example：
```
  /*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123
```
  Note If a query requires the columns that are not contained in an index, you can query the index table first to retrieve all the recorded primary keys and the shard keys of the primary table. Then, query the primary table to retrieve the values of the missing columns. For more information, see INDEX HINT.

Directly query an index table

If the index table contains all the columns that are required for a query, you can directly query the index table to retrieve the result.
Select an index

For a query on the primary table that has a GSI, PolarDB-X 1.0 automatically selects the index table that the optimizer considers to have the minimum cost. Only covering indexes can be selected.

In the following SQL query, the primary table is t_order. The seller_id equality filter is contained, and the involved columns such as id, order_snapshot, and seller_id are covered by the GSI g_i_seller. The use of the g_i_seller covering index eliminates the need to query the primary table and significantly reduces the number of scans on the table shardings. seller_id is the shard key of g_i_seller. The result of EXPLAIN shows that the PolarDB-X 1.0 optimizer selects g_i_seller.
```
EXPLAIN SELECT t_order.id,t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';
IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")
```

IGNORE INDEX and USE INDEX

You can enable the optimizer to use or not to use some indexes by using the following HINT:

Statement:

IGNORE INDEX({index_name},...)

Example：

SELECT t_order.id,t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Statement:

USE INDEX({index_name},...)

Example：

SELECT t_order.id,t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Notes for using GSIs

Notes for creating a GSI
- You cannot create a GSI on ApsaraDB RDS for MySQL 5.6.
- You cannot create a GSI in a single table or broadcast tables.
- You cannot create a GSI in tables that do not have primary keys.
- You cannot use a prefixed index in a UNIQUE GSI.
- Specify the index name when you create an index table.
- When you create an index table, specify database sharding rules or database sharding and table sharding rules. You cannot specify only table sharding rules or specify no sharding rules.
- The INDEX column in index tables must contain all shard keys.
- The name of a GSI cannot be the same as that of other local indexes in a primary table.
- Index columns and covering columns cannot be the same in GSI definition clauses.
- By default, an index table contains all the primary keys and shard keys of the primary table. If the primary keys and shard keys are not explicitly contained in the index columns, the keys are added to the covering columns by default.
- For each local index in the primary table, if all the referenced columns are contained in the index table, the local index is added to the index table by default.
- By default, an index is separately created for each index column of a GSI if no index exists.
- By default, a composite local index is created for a GSI that contains multiple index columns. The composite local index contains all the index columns.
- In an index definition, the length parameter of the index column is used only to create local indexes on the shard keys of the index table.
- If you create a GSI after a table is created, data is automatically verified at the end of the creation process. The execution of the data definition language (DDL) statement to create the GSI can be successful only after the data passes the verification.
  
  Note You can also use the CHECK GLOBAL INDEX statement to verify or modify the index data.

Notes for executing the ALTER TABLE statement

The following table describes whether the ALTER TABLE statement can be executed to change columns.

| Statement | Change the shard keys of the primary table | Change the primary keys of the primary table, also referred to as the primary keys of the index table | Change the local unique index column | Change the shard keys of the index table | Change the unique index column | Change the index column | Change the covering column | |----------------------------------------------------|-------------|---------------------|---------------|--------------|---------------------|--------------|-----------------| | ADD COLUMN | No such scenario | Not supported | No such scenario | No such scenario | No such scenario | No such scenario | No such scenario | | ALTER COLUMN SET DEFAULT和ALTER COLUMN DROP DEFAULT | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported | | CHANGE COLUMN | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported | | DROP COLUMN | Not supported | Not supported | Supported only when the unique key has only one column | Not supported | Not supported | Not supported | Not supported | | MODIFY COLUMN | Not supported | Not supported | Supported | Not supported | Not supported | Not supported | Not supported |

Note
- To ensure the stability and performance of GSIs, you are not allowed to delete columns from GSIs by executing the DROP COLUMN statement in a direct way. To delete some columns from a GSI,you can execute the DROP INDEX statement to delete the GSI and create another GSI.
- Overlaps exist in the preceding column classification. For example, the index column contains the shard keys of the index table. The covering column contains the shard keys of the primary table, the primary keys, and the specified columns. If a conflict occurs between Not supported and Supported, Not supported takes precedence over Supported.

The following table describes whether the ALTER TABLE statement can be executed to change indexes.

| Statement | Supported or not |
```
|-----------------------------------------------------------|--------------------------------------|
```
| ALTER TABLE ADD PRIMARY KEY | Supported | | ALTER TABLE ADD [UNIQUE/FULLTEXT/SPATIAL/FOREIGN] KEY | Supported. You can add local indexes to the primary table and the index table at the same time. An index name cannot be the same as the name of the GSI. | | ALTER TABLE ALTER INDEX index_name {VISIBLE | INVISIBLE} | Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status. | | ALTER TABLE {DISABLE | ENABLE} KEYS | Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status. | | ALTER TABLE DROP PRIMARY KEY | Not supported | | ALTER TABLE DROP INDEX | Supported. You can execute this statement on only the primary table. You are not allowed to change the GSI status. | | ALTER TABLE DROP FOREIGN KEY fk_symbol | You can delete only a common index or a GSI. | | ALTER TABLE RENAME INDEX | Not supported |

Note To ensure the stability and performance of GSIs, you are not allowed to rename GSIs by executing the ALTER TABLE RENAME INDEX statement in a direct way. To change the GSI name, you can execute the DROP INDEX statement to delete the GSI and create another GSI by using a new name.
Notes for changing index tables
- You cannot execute DDL or data manipulation language (DML) statements on index tables.
- You cannot execute DML statements that contain NODE HINT to update primary tables or index tables.

Notes for executing other DDL statements

| Statement | Supported or not | |--------------------|------| | DROP TABLE | Supported | | DROP INDEX | Supported | | TRUNCATE TABLE | Not supported | | RENAME TABLE | Not supported | | ALTER TABLE RENAME | Not supported |

Note
- To ensure data consistency between primary tables and index tables, you are not allowed to execute the TRUNCATE TABLE statement. To clear data in the primary tables and the index tables, you can execute the DELETE statement.
- To ensure the stability and performance of GSIs, you are not allowed to rename GSIs by executing the RENAME TABLE or ALTER TABLE RENAME statement in a direct way. To change the GSI name, you can execute the DROP INDEX statement to delete the GSI, rename the table, and then create another GSI.

Notes for executing DML statements

You cannot execute DML statements on index tables.
For more information about the limits on executing DML statements on primary tables, see Limits of global secondary indexes on DML.

CREATE TABLE t_order(
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `l_i_order` (`order_id`),
  GLOBAL INDEX `g_i_seller` (`seller_id`) dbpartition by hash(`seller_id`) tbpartition by hash(`seller_id`),
  GLOBAL UNIQUE INDEX `g_i_buyer` (`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
SET DRDS_TRANSACTION_POLICY='XA';
INSERT INTO t_order(order_id, buyer_id, seller_id) VALUES('order_1', 'buyer_1', 'seller_1');
# Failed
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_1', 'seller_1');
# Not allowed
INSERT IGNORE INTO t_order(order_id, buyer_id, seller_id) VALUES('order_2', 'buyer_2', 'seller_2');
# Not allowed
COMMIT;

How to use global secondary indexes

Use global secondary indexes

Prerequisites

Create a GSI

Use a GSI

Notes for using GSIs

results matching ""

No results matching ""