All Products
Search
Document Center

CREATE INDEX

Last Updated: Sep 09, 2020

This topic describes how to create a local index or global secondary index (GSI) in Distributed Relational Database Service (DRDS) by executing this SQL statement. DRDS also allows you to delete these two types of indexes.

GSIs can be used in DRDS only when the DRDS version is 5.4.1 or later and the MySQL version is 5.7 or later. For more information about GSIs, see Global secondary index.

Local index

For more information, see MySQL CREATE INDEX Statement.

GSI

Syntax:

  1. CREATE [UNIQUE]
  2. GLOBAL INDEX index_name [index_type]
  3. ON tbl_name (index_sharding_col_name,...)
  4. global_secondary_index_option
  5. [index_option]
  6. [algorithm_option | lock_option] ...
  7. # GSI-specific syntax. For more information, see MySQL CREATE TABLE Statement.
  8. global_secondary_index_option:
  9. [COVERING (col_name,...)]
  10. drds_partition_options
  11. # The database sharding and table sharding clause. For more information, see MySQL CREATE TABLE Statement.
  12. drds_partition_options:
  13. DBPARTITION BY db_sharding_algorithm
  14. [TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]
  15. db_sharding_algorithm:
  16. HASH([col_name])
  17. | {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  18. | UNI_HASH(col_name)
  19. | RIGHT_SHIFT(col_name, n)
  20. | RANGE_HASH(col_name, col_name, n)
  21. table_sharding_algorithm:
  22. HASH(col_name)
  23. | {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
  24. | UNI_HASH(col_name)
  25. | RIGHT_SHIFT(col_name, n)
  26. | RANGE_HASH(col_name, col_name, n)
  27. # MySQL DDL syntax
  28. index_sharding_col_name:
  29. col_name [(length)] [ASC | DESC] # The length parameter is used only when you want to create a local index for the shard key of the index table.
  30. index_option:
  31. KEY_BLOCK_SIZE [=] value
  32. | index_type
  33. | WITH PARSER parser_name
  34. | COMMENT 'string'
  35. index_type:
  36. USING {BTREE | HASH}
  37. algorithm_option:
  38. ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  39. lock_option:
  40. LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

The CREATE GLOBAL INDEX syntax is used to add a GSI to a table after the table is created. This syntax introduces the GLOBAL keyword to the MySQL syntax to set the type of index you want to add to GSI. Limits are imposed on creating a GSI for a table after the table is created. For limits on GSIs, see Use GSIs.

For the GSI definition clause, see CREATE TABLE.

Sample code

The following example describes how to create a common GSI after a table is created.

  1. # Create a table first.
  2. CREATE TABLE t_order (
  3. `id` bigint(11) NOT NULL AUTO_INCREMENT,
  4. `order_id` varchar(20) DEFAULT NULL,
  5. `buyer_id` varchar(20) DEFAULT NULL,
  6. `seller_id` varchar(20) DEFAULT NULL,
  7. `order_snapshot` longtext DEFAULT NULL,
  8. `order_detail` longtext DEFAULT NULL,
  9. PRIMARY KEY (`id`),
  10. KEY `l_i_order` (`order_id`)
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`order_id`);
  12. # Then, create a GSI.
  13. CREATE GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`);

where:

  • Primary table: The primary table is t_order, on which database sharding is performed by hashing based on the order_id column.
  • Index table: The index table is g_i_seller, on which database sharding is performed by hashing based on the buyer_id column.
  • GSI definition clause: GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`)

Execute SHOW INDEX to view index information, including the local index on shard key order_id and GSIs on seller_id, id, and order_id. In particular, seller_id is the shard key of the index table and id and order_id are the default covering columns (which are the primary key and the shard key of the primary table, respectively).

Note: For limits on GSIs, see Use GSIs. For more information about SHOW INDEX, see SHOW INDEX.

  1. mysql> show index from t_order;
  2. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
  3. | TABLE | NON_UNIQUE | KEY_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT | INDEX_COMMENT |
  4. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
  5. | t_order | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
  6. | t_order | 1 | l_i_order | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | |
  7. | t_order | 1 | g_i_seller | 1 | seller_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | |
  8. | t_order | 1 | g_i_seller | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | |
  9. | t_order | 1 | g_i_seller | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | |
  10. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

To view GSI information only, execute SHOW GLOBAL INDEX. For more information, see SHOW GLOBAL INDEX.

  1. mysql> show global index from t_order;
  2. +---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
  3. | SCHEMA | TABLE | NON_UNIQUE | KEY_NAME | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
  4. +---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
  5. | ZZY3_DRDS_LOCAL_APP | t_order | 1 | g_i_seller | seller_id | id, order_id | NULL | seller_id | HASH | 4 | | NULL | NULL | PUBLIC |
  6. +---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

View the structure of the index table. The index table includes the primary key of the primary table, the shard key, default covering columns, and custom covering columns. The AUTO_INCREMENT attribute has been deleted from the primary key column. The local index has been deleted from the primary table.

  1. mysql> show create table g_i_seller;
  2. +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Table | Create Table |
  4. +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | g_i_seller | CREATE TABLE `g_i_seller` (
  6. `id` bigint(11) NOT NULL,
  7. `order_id` varchar(20) DEFAULT NULL,
  8. `seller_id` varchar(20) DEFAULT NULL,
  9. PRIMARY KEY (`id`),
  10. KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
  11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`seller_id`) |
  12. +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+