全部产品
云市场

CREATE INDEX

更新时间:2019-11-04 11:37:47

DRDS 支持创建局部索引和全局二级索引 (Global Secondary Index, GSI) ,同时支持删除这两种索引。

注意:DRDS 全局二级索引要求 MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1,基本原理请参考 DRDS 全局二级索引文档

局部索引

参考 MySQL CREATE INDEX 文档。

全局二级索引

语法:

  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. # 全局二级索引特有语法,具体说明请参考 CREATE TABLE 文档
  8. global_secondary_index_option:
  9. [COVERING (col_name,...)]
  10. drds_partition_options
  11. # 分库分表子句,具体说明请参考 CREATE TABLE 文档
  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 语法
  28. index_sharding_col_name:
  29. col_name [(length)] [ASC | DESC] # length 参数仅用于在索引表拆分键上创建局部索引
  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}

CREATE GLOBAL INDEX 系列语法用于在建表后添加 GSI,该系列语法在 MySQL 语法上新引入了 GLOBAL 关键字,用于指定添加的索引类型为 GSI。目前建表后创建 GSI 存在一定限制,GSI 的限制与约定参考 DRDS 全局二级索引使用文档

全局二级索引定义子句详细说明,参考 CREATE TABLE 文档

示例

下面以建立普通全局二级索引为例,介绍在建表后创建 GSI

  1. # 先建表
  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. # 再建全局二级索引
  13. CREATE GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`);

其中:

  • 主表:t_order 只分库不分表,分库的拆分方式为按照 order_id 列进行哈希
  • 索引表:g_i_seller 只分库不分表,分库的拆分方式为按照 seller_id 列进行哈希
  • 索引定义子句:GLOBAL INDEX `g_i_seller` ON t_order (`seller_id`) dbpartition by hash(`seller_id`)

通过 SHOW INDEX 查看索引信息,包含拆分键 order_id 上的局部索引,和 seller_id, id, order_id 上的 GSI,其中 seller_id 为索引表的拆分键,id, order_id 为默认的覆盖列(主键和主表的拆分键)

注意: GSI 的限制与约定参考 DRDS 全局二级索引使用文档,SHOW INDEX 详细说明参考 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. +---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+

通过 SHOW GLOBAL INDEX 可以单独查看 GSI 信息,详细说明参考 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. +---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

查看索引表的结构,索引表包含主表的主键、分库分表键、默认的覆盖列 和 自定义覆盖列,主键列去除了 AUTO_INCREMENT 属性,并且去除了主表中的局部索引

  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. +------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+