インデックスの作成 (自動モード) - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB-Xでは、ローカルインデックスとグローバルセカンダリインデックス (GSI) を作成し、これらのタイプのインデックスを削除できます。この構文は、AUTOモードのデータベースにのみ適用できます。

ローカルインデックス

ローカルインデックスの詳細については、「CREATE INDEXステートメント」をご参照ください。

使用上の注意

GSIでレベル2パーティションに関連する機能を使用する場合、PolarDB-Xインスタンスのバージョンは5.4.17-16952556以降です。

GSI

GSIの基本原則の詳細については、「GSI」をご参照ください。

構文

CREATE [UNIQUE]
    GLOBAL INDEX index_name [index_type]    
    ON tbl_name (index_sharding_col_name,...)    
    global_secondary_index_option 
    [index_option] 
    [algorithm_option | lock_option] ...    
# The syntax applies only to GSIs. For more information, see the documentation of the CREATE TABLE statement.  
global_secondary_index_option:   
    [COVERING (col_name,...)]
    [partition_options]
    [VISIBLE|INVISIBLE]

# Specify the partition.
partition_options:
	partition_columns_definition
	[subpartition_columns_definition]
	[subpartition_specs_definition]/* Specify the templated level-2 partition.*/ 
  partition_specs_definition 

# Specify the partition key column of the level -1 partition.
partition_columns_definition:
		PARTITION BY
          HASH({column_name | partition_func(column_name)}) partitions_count
        | KEY(column_list) partitions_count
        | RANGE({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)

# Specify the partition key column of the level-2 partition.
subpartition_columns_definition:
		SUBPARTITION BY
          HASH({column_name | partition_func(column_name)}) subpartitions_count
        | KEY(column_list) subpartitions_count
        | RANGE({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)

partitions_count:
   PARTITIONS partition_count

subpartitions_count:
   SUBPARTITIONS partition_count

# Specify the partitioning function.
partition_func:
    YEAR
  | TO_DAYS
  | TO_MONTHS
  | TO_WEEKS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
  | DAYOFWEEK
  | DAYOFMONTH
  | DAYOFYEAR
  | SUBSTR
  | SUBSTRING


# Specify three types of level -1 partitions.
partition_specs_definition:
	hash_partition_list
  | range_partition_list
  | list_partition_list

# Specify three types of level-2 partitions.
subpartition_specs_definition:
	hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

# Specify the HASH or KEY subpartition of the level-1 partition.
hash_partition_list:
	  /* All subpartitions in the level-1 partition are of the HASH partitioning type.*/
	| ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
  | PARTITION partition_name subpartitions_count [subpartition_specs_definition] /* Specify the non-templated subpartition under the level -1 partition.*/

# Specify the HASH or KEY subpartition of the level-2 partition.
hash_subpartition_list:
  | empty
  | ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
	SUBPARTITION subpartition_name [partition_spec_options]

# Specify the RANGE or RANGE COLUMNS subpartition of the level-1 partition.
range_partition_list:
    ( range_partition [, range_partition, ... ] )

range_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
    | PARTITION partition_name VALUES LESS THAN (range_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Specify the non-templated subpartition under the level -1 partition.*/
   
# Specify the RANGE or RANGE COLUMNS subpartition of the level-2 partition.
range_subpartition_list:
	( range_subpartition [, range_subpartition, ... ] )

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
  
range_bound_value:
	  maxvalue /* Specify the maximum number of RANGE partitions.*/
	| expr /* Specify the range boundary value for a single partition key column.*/
	| value_list /* Specify the range boundary values for multiple partition key columns.*/

# Specify the LIST or LIST COLUMNS subpartition of the level-1 partition.
list_partition_list:
    (list_partition [, list_partition ...])

list_partition:
    	PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options] /* Specify the pure level -1 partition or templated subpartition.*/
    | PARTITION partition_name VALUES IN (list_bound_value) [[subpartitions_count] [subpartition_specs_definition]] /* Specify the non-templated subpartition under the level -1 partition.*/

# Specify the LIST or LIST COLUMNS subpartition of the level-2 partition.
list_subpartition_list:
	(list_subpartition [, list_subpartition ...])

list_subpartition:
	SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

list_bound_value:
		default /* Specify the default LIST partition.*/
	|	value_set

value_set:
	  value_list /* Specify a set of values for a single partition key column.*/
	| (value_list) [, (value_list), ...] /* Specify a set of values for multiple partition key columns.*/

value_list:
	value [, value, ...]

partition_spec_options:
	    [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [LOCALITY [=] locality_option]

table_option:
 	    [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]  

locality_option:
    'dn=storage_inst_id_list'
    
storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

local_partition_definition:
		LOCAL PARTITION BY RANGE (column_name)
    [STARTWITH 'yyyy-MM-dd']
    INTERVAL interval_count [YEAR|MONTH|DAY]
    [EXPIRE AFTER expire_after_count]
    [PRE ALLOCATE pre_allocate_count]
    [PIVOTDATE pivotdate_func]
    [DISABLE SCHEDULE]
    
pivotdate_func:
		NOW()
  |	DATE_ADD(...)
  | DATE_SUB(...)

CREATE GLOBAL INDEX構文は、テーブルの作成後にGSIを追加するために使用されます。この構文では、MySQL構文でGLOBALキーワードを導入し、GSIのみを追加できるように指定します。テーブルが作成された後のGSIの作成にはいくつかの制限があります。 GSIの制限の詳細については、「グローバルセカンダリインデックスの使用方法」をご参照ください。

GSIを定義する句の詳細については、「CREATE TABLE (DRDSモード) 」をご参照ください。

例

次の例では、テーブルの作成後にGSIを作成する方法について説明します。

# 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`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`order_id`) partitions 16;
# Create a GSI.
CREATE GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) COVERING(`order_snapshot`) partition by hash(`seller_id`) partitions 16;

ベーステーブル: 上記の例では、t_orderベーステーブルはパーティションテーブルです。データは、ハッシュアルゴリズムを用いて分割される。 order_id列は、ハッシュされたパーティションキーとして指定されます。
インデックステーブル: 上記の例では、インデックステーブルg_i_sellerはハッシュアルゴリズムを使用して分割されています。 seller_id列は、ハッシュされたパーティションキーとして指定されます。 order_snapshot列はカバー列として指定されます。
インデックスを定義する条項: CREATE GLOBAL INDEX 'g_i_seller 'ON t_order ('seller_id') COVERING('order_snapshot') partition by hash('seller_id') partition 16;

SHOW INDEXステートメントを実行して、order_id列のローカルインデックスやid列とorder_id列のGSIなどのインデックスを表示します。 seller_idは、インデックステーブルのシャードキーとしても指定されます。また、idとorder_idはデフォルトのカバー列です。 idもプライマリキーとして指定され、order_idはベーステーブルのシャードキーです。

説明

GSIの制限の詳細については、「グローバルセカンダリインデックスの使用方法」をご参照ください。 SHOW INDEXの詳細については、「SHOW INDEX」をご参照ください。

show index from t_order;

サンプル結果:

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order_****_00000 |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order_****_00000 |          1 | l_i_order |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

SHOW GLOBAL INDEXステートメントを実行してGSIを表示します。詳細については、「SHOW GLOBAL INDEX」をご参照ください。

show global index from t_order;
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| 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 |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

次のステートメントを実行して、インデックステーブルの構造を表示します。テーブルには、ベーステーブルの主キー、シャードキー、デフォルトのカバーリング列、およびカスタムカバーリング列が含まれます。主キー列は、AUTO_INCREMENT属性とベーステーブルのローカルインデックスを削除します。

show create table g_i_seller;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_seller | CREATE TABLE `g_i_seller` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`seller_id`) partitions 16 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+