PolarDB:Create and use GSIs

GSI syntax structure

PolarDB-X extends the MySQL DDL syntax to support creating GSIs, which is the same as creating indexes in MySQL.

• Define a GSI when creating a table

  

• Add a GSI after creating a table

  

Constraints

Examples

1. Create a GSI.

   -- Define a GSI when creating 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

2. Use the GSI in queries.

   • Specify an index using a hint

     You can choose either of the following two HINT statements to specify the target index for the query.

     • FORCE INDEX

       FORCE INDEX({index_name})

       Example:

       SELECT a.order_id FROM t_order a FORCE INDEX(g_i_seller) WHERE a.buyer_id = 123;

     • Hint

       /*+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 needs to use columns not included in the index, the GSI is first queried to obtain the primary keys and base table shard keys of all records, and then the base table is queried to obtain the values of the missing columns. For more information, see INDEX HINT.

   • Index selection examples:

     For queries on a base table with GSIs, PolarDB-X automatically selects the GSI that the optimizer considers to have the lowest cost (only covering index selection is supported).

     EXPLAIN SELECT t_order.id,t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';

     Execution plan result:

     IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")          

     Note

     • The base table in the above SQL query is t_order, with an equality filter condition on seller_id, and the columns involved (id, order_snapshot, and seller_id) are covered by g_i_seller. Selecting the covering index g_i_seller both avoids table lookups and clearly reduces the number of table partitions to scan (seller_id is the shard key of g_i_seller).

     • In the execution plan result, you can see that the PolarDB-X optimizer has selected g_i_seller.

   • IGNORE INDEX

     Syntax:

     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';

   • USE INDEX

     Syntax:

     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';

FAQ