局部索引
關于局部索引,詳情清參見
CREATE INDEX Statement。
全局二級索引
關于全局二級索引基本原理,請參見
文法
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的限制與約定,詳情請參見
如何使用全局二級索引關于全局二級索引定義子句詳細說明,請參見
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“。
- 索引定義子句:
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的限制與約定,詳情請參見
,SHOW INDEX詳細說明,請參見
SHOW INDEXmysql> 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 INDEXmysql> 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`) |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+