ALTER TABLE
语法
ALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
ALTER TABLE 用于改变表的结构,如增加列、增加索引、修改数据定义。详细语法请参考 MySQL 修改表语法。
注意:不允许修改拆分字段。
- 增加列:
ALTER TABLE user_log
ADD COLUMN idcard varchar(30);
- 增加索引:
ALTER TABLE user_log
ADD INDEX idcard_idx (idcard);
- 删除索引:
ALTER TABLE user_log
DROP INDEX idcard_idx;
- 重命名索引:
ALTER TABLE user_log
RENAME INDEX `idcard_idx` TO `idcard_idx_new`;
- 修改字段:
ALTER TABLE user_log
MODIFY COLUMN idcard varchar(40);
全局二级索引
版本限制:MySQL 版本 >= 5.7, 并且 DRDS 版本 >= 5.4.1
DRDS 支持全局二级索引 (Global Secondary Index, GSI) 基本原理请参考 DRDS 全局二级索引文档
列变更
使用全局二级索引的表,对列的修改,语法和普通表的一致。
注意:当修改的表包含全局二级索引时,对列的修改有额外的限制,GSI 的限制与约定参考 DRDS 全局二级索引使用文档
索引变更
语法:
ALTER TABLE tbl_name
alter_specification # 全局二级索引相关变更仅支持一条 alter_specification
alter_specification:
| ADD GLOBAL {INDEX|KEY} index_name # 全局二级索引必须显式指定索引名
[index_type] (index_sharding_col_name,...)
global_secondary_index_option
[index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE GLOBAL
[INDEX|KEY] index_name # 全局二级索引必须显式指定索引名
[index_type] (index_sharding_col_name,...)
global_secondary_index_option
[index_option] ...
| DROP {INDEX|KEY} index_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
# 全局二级索引特有语法,具体说明请参考 CREATE TABLE 文档
global_secondary_index_option:
[COVERING (col_name,...)] # Covering Index
drds_partition_options # 包含且仅包含 index_sharding_col_name 中指定的列
# 指定索引表拆分方式
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]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
ALTER TABLE ADD GLOBAL INDEX
系列语法用于在建表后添加GSI,该系列语法在 MySQL 语法上新引入了 GLOBAL 关键字,用于指定添加的索引类型为GSI
ALTER TABLE { DROP | RENAME } INDEX
语法同样可以对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`);
# 再建全局二级索引
ALTER TABLE t_order ADD UNIQUE GLOBAL INDEX `g_i_buyer` (`buyer_id`) COVERING (`order_snapshot`) dbpartition by hash(`buyer_id`);
其中:
- 主表:
t_order
只分库不分表,分库的拆分方式为按照 order_id 列进行哈希 - 索引表:
g_i_buyer
只分库不分表,分库的拆分方式为按照 buyer_id 列进行哈希,指定覆盖列为 order_snapshot - 索引定义子句:
UNIQUE GLOBAL INDEX `g_i_buyer`(`buyer_id`) COVERING (order_snapshot) dbpartition by hash(`buyer_id`)
通过 SHOW INDEX
查看索引信息,包含拆分键 order_id 上的局部索引,和 buyer_id, id, order_id, order_snapshot 上的 GSI,其中 buyer_id 为索引表的拆分键,id, order_id 为默认的覆盖列(主键和主表的拆分键),order_snapshot 为显式指定的覆盖列。
注意: 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 | 0 | g_i_buyer | 1 | buyer_id | NULL | 0 | NULL | NULL | YES | GLOBAL | INDEX | |
| t_order | 1 | g_i_buyer | 2 | id | NULL | 0 | NULL | NULL | | GLOBAL | COVERING | |
| t_order | 1 | g_i_buyer | 3 | order_id | NULL | 0 | NULL | NULL | YES | GLOBAL | COVERING | |
| t_order | 1 | g_i_buyer | 4 | order_snapshot | 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 | 0 | g_i_buyer | buyer_id | id, order_id, order_snapshot | NULL | buyer_id | HASH | 4 | | NULL | NULL | PUBLIC |
+---------------------+---------+------------+-----------+-------------+------------------------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
查看索引表的结构,索引表包含主表的主键、分库分表键、默认的覆盖列 和 自定义覆盖列,主键列去除了 AUTO_INCREMENT 属性,并且去除了主表中的局部索引,全局唯一索引默认在索引表的所有分库分表键上创建一个唯一索引,以实现全局唯一约束
mysql> show create table g_i_buyer;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_buyer | CREATE TABLE `g_i_buyer` (
`id` bigint(11) NOT NULL,
`order_id` varchar(20) DEFAULT NULL,
`buyer_id` varchar(20) DEFAULT NULL,
`order_snapshot` longtext,
PRIMARY KEY (`id`),
UNIQUE KEY `auto_shard_key_buyer_id` (`buyer_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition by hash(`buyer_id`) |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
删除全局二级索引
# 删除索引
ALTER TABLE `t_order` DROP INDEX `g_i_seller`;
删除名为 g_i_seller
的 GSI,相应的索引表也将被删除。
重命名索引
注意: 默认情况下限制对 GSI 的重命名,GSI 的限制与约定参考 DRDS 全局二级索引使用文档
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论