CREATE INDEX
DRDS 支持创建局部索引和全局二级索引 (Global Secondary Index, GSI) ,同时支持删除这两种索引。
注意:DRDS 全局二级索引要求 MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1,基本原理请参考 DRDS 全局二级索引文档
局部索引
参考 MySQL CREATE INDEX 文档。
全局二级索引
语法:
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] ...
# 全局二级索引特有语法,具体说明请参考 CREATE TABLE 文档
global_secondary_index_option:
[COVERING (col_name,...)]
drds_partition_options
# 分库分表子句,具体说明请参考 CREATE TABLE 文档
drds_partition_options:
DBPARTITION BY db_sharding_algorithm
[TBPARTITION BY {table_sharding_algorithm} [TBPARTITIONS num]]
db_sharding_algorithm:
HASH([col_name])
| {YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
| UNI_HASH(col_name)
| RIGHT_SHIFT(col_name, n)
| RANGE_HASH(col_name, col_name, n)
table_sharding_algorithm:
HASH(col_name)
| {MM|DD|WEEK|MMDD|YYYYMM|YYYYWEEK|YYYYDD|YYYYMM_OPT|YYYYWEEK_OPT|YYYYDD_OPT}(col_name)
| UNI_HASH(col_name)
| RIGHT_SHIFT(col_name, n)
| RANGE_HASH(col_name, col_name, n)
# 以下为 MySQL DDL 语法
index_sharding_col_name:
col_name [(length)] [ASC | DESC] # length 参数仅用于在索引表拆分键上创建局部索引
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
CREATE GLOBAL INDEX
系列语法用于在建表后添加 GSI,该系列语法在 MySQL 语法上新引入了 GLOBAL 关键字,用于指定添加的索引类型为 GSI。目前建表后创建 GSI 存在一定限制,GSI 的限制与约定参考 DRDS 全局二级索引使用文档
全局二级索引定义子句详细说明,参考 CREATE TABLE 文档
示例
下面以建立普通全局二级索引为例,介绍在建表后创建 GSI
# 先建表
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 dbpartition by hash(`order_id`);
# 再建全局二级索引
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 文档
mysql> 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 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| t_order | 1 | l_i_order | 1 | order_id | A | 0 | NULL | NULL | YES | BTREE | | |
| t_order | 1 | g_i_seller | 1 | seller_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | |
| t_order | 1 | g_i_seller | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | |
| t_order | 1 | g_i_seller | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
通过 SHOW GLOBAL INDEX
可以单独查看 GSI 信息,详细说明参考 SHOW GLOBAL INDEX 文档
mysql> 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 属性,并且去除了主表中的局部索引
mysql> 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 dbpartition by hash(`seller_id`) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论