MySql全局二级索引创建与使用

MySql全局二级索引创建与使用

前言：单表是不支持全局二级索引的

如果你是单表，需要先创建一个新的支持分库分表的表结构。下面举例根据表ID做分库键

CREATE TABLE `ip_b_time_order_vip` (
	`id` bigint(20) NOT NULL COMMENT 'id',
	`bill_no` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '编号',
	`order_sn` varchar(100) CHARACTER SET utf8 DEFAULT NULL COMMENT '订单号',
	`ISTRANS` int(11) DEFAULT NULL COMMENT '转换状态 ',
	`occupied_order_sn` varchar(100) CHARACTER SET utf8 NOT NULL COMMENT '占用订单号',
	PRIMARY KEY USING BTREE (`id`),
	KEY `auto_shard_key_occupied_order_sn` USING BTREE (`occupied_order_sn`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8  dbpartition by hash(`id`);

创建全局二级索引

CREATE GLOBAL INDEX `gsi_ip_b_time_order_vip_pick_no` ON ip_b_time_order_vip (`pick_no`) COVERING (id,bill_no,order_sn,occupied_