2.1.0 阿里云PolarDB-X（1.0）分库分表后导致的原表唯一索引失效的问题

1、RDS数据表由原来的单库单表切换成DRDS分库分表以后出现单库索引唯一，全库索引不唯一

1.1 原因

      DRDS在分库分表后，默认情况下如果按照原来的表创建的唯一索引是只能做到单库分表唯一的，但是不能做到全库，就所有的分库都唯一，即全局唯一，需要利用DRDS数据库的语法，建立全局唯一索引；

1.2 操作

-- 表user_app 分库分表

-- 原创建表语句
CREATE TABLE `user_app` (
	`id` bigint(18) UNSIGNED NOT NULL AUTO_INCREMENT BY GROUP,
	`app_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '应用code',
	`user_id` bigint(16) NOT NULL COMMENT '用户Id',
	`client_id` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '客户端Id',
	`client_secret` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '客户端密钥',
	`ustatus` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '状态',
	`access_token` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '访问token',
	`refresh_token` varchar(80) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '刷新token',
	`auth_time` datetime DEFAULT NULL COMMENT '授权时间',
	`access_expires` bigint(15) DEFAULT NULL COMMENT '过期时限',
	`refresh_expire_time` datetime DEFAULT NULL COMMENT '刷新token的过期时间',
	`create_time` datetime DEFAULT NULL,
	`create_by` bigint(18) DEFAULT NULL,
	`update_time` datetime DEFAULT NULL,
	`update_by` bigint(18) DEFAULT NULL,
	PRIMARY KEY USING BTREE (`id`),
	UNIQUE KEY `app_user_client` USING BTREE (`app_code`, `user_id`),
	KEY `user_app_refresh_token_IDX` USING BTREE (`refresh_token`),
	KEY `auto_shard_key_user_id` USING BTREE (`user_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  dbpartition by hash(`id`) tbpartition by hash(`user_id`) tbpartitions 3;

-- 上面的表是做了分库分表的，下面语句可以看到表的分库分表情况，分了16个库，每个库里面分3个表，分库数从dms管理台看的
show topology from user_app;

-- 执行语句，能插入，违反唯一性索引还是能插入其中，无论执行几次都能插入进去
INSERT INTO `user_app` (`app_code`, `user_id`, `access_token`, `refresh_token`) VALUES 
('1001', 103296, '1MEnlY9loUe2FW3ADBPndA6cGUmFmS7ZscruxD_adXc', 'BC7C3356FB26D6CF50EA56AB91F171EC2e1cfa9a-835b-4a67-b273-5f1ce3cdfe2b');

-- 创建索引前先把重复的数据删除
select app_code,user_id,count(1) from user_app group by app_code,user_id HAVING count(1) >1

-- 删除重复数据
DELETE t1 FROM user_app t1, user_app t2 WHERE t1.app_code = t2.app_code and t1.user_id = t2.user_id AND t1.id > t2.id;

-- 删除原来在表上面的唯一索引【这一步骤是错误，原表中的索引不能删除，删除的话，原来业务逻辑中的查询都走全表索引，这个是不对的】
DROP INDEX app_user_client ON user_app;   

-- 创建表上的唯一索引，这个因为1.0版本的问题，创建完以后，还是表架构只能dbpartition by hash(user_id),全局唯一索引的字读顺序最好不要和单独库中的唯一索引的字段顺序一致，这样会导致，命中不了单独库中的唯一索引，而造成数据查询缓慢，甚至所有服务不可用！
create global unique index gsi_unq_idx_app_code_user on user_app (user_id,app_code) dbpartition by hash(user_id,app_code);

-- 查看这个表上面额索引信息
show global index from user_app;

-- 再执行违反唯一索引的数据，报错
 
 -- 原表创建语句 user 只分库不分表
 CREATE TABLE `user` (
	`id` bigint(16) UNSIGNED NOT NULL AUTO_INCREMENT BY GROUP COMMENT '用户ID',
	`mobile` varchar(30) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '手机号码',
	`nickname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '昵称',
	`gender` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '性别(F/M)',
	`ustatus` tinyint(2) DEFAULT NULL COMMENT '0：未审核,1：正常',
	`profile_photo` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户头像',
	`user_src` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1' COMMENT '用户来源',
	`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
	`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
	`app_id` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1000' COMMENT '应用ID',
	`create_time` datetime DEFAULT NULL COMMENT '创建时间',
	`create_by` bigint(16) DEFAULT NULL COMMENT '创建人',
	`update_time` datetime DEFAULT NULL COMMENT '修改时间',
	`update_by` bigint(16) DEFAULT NULL COMMENT '修改人',
	`real_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '真实姓名',
	`app_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1000' COMMENT '应用分类',
	`type_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '用户类型',
	`certi_type` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '证件类型',
	`certi_nbr` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '证件号码',
	`status` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1' COMMENT '1.开启；2.关闭',
	`cancell_time` datetime DEFAULT NULL COMMENT '注销时间',
	`out_flag` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL COMMENT '注销标识',
	PRIMARY KEY USING BTREE (`id`),
	UNIQUE KEY `mobile_status_uni` USING BTREE (`mobile`, `status`),
	KEY `user_create_time_IDX` USING BTREE (`create_time`),
	KEY `user_user_name_IDX` USING BTREE (`user_name`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  dbpartition by hash(`id`);

--上面的表，只做了分库，库里面没有进行分表

-- 插入数据，能插入，违反唯一索引
INSERT INTO `user` (`mobile`, `nickname`,`user_src`, `user_name`, `password`, `create_time`, `create_by`, `update_time`,  `status`) 
VALUES
 ('18200000000', '18200000000',  'CC', '18200000000', 'xxx',  '2019-10-26 15:36:34', 0, '2020-04-24 22:34:17',  '4');
 
 -- 查询违反唯一索引的数据情况
select * from user where mobile is not null group by mobile,status having count(1) >1

-- 删除重复数据
DELETE t1 FROM user t1, user t2 WHERE t1.mobile = t2.mobile and t1.status = t2.status and t1.mobile is not null AND t1.id > t2.id;

-- 删除原来在表上面的唯一索引【这一步骤是错误，原表中的索引不能删除，删除的话，原来业务逻辑中的查询都走全表索引，这个是不对的】
DROP INDEX mobile_status_uni ON user;   

-- 创建全局唯一索引
create global unique index gsi_unq_idx_mobile_status on user (mobile,status) dbpartition by hash(mobile,status);

-- 查看表的全局索引
show global index from user;

-- 再执行违反唯一索引的数据，报错

1.3 注意

mysql创建了唯一索引，唯一索引含有多个字段，比如 上面的user表，唯一索引：UNIQUE KEY mobile_status_idx (mobile,status),在mobile、status两个字段中有一个字段出现空的情况，那么唯一索引将会失效；

2、Drds的基本语法

2.1 分库不分表表创建语句

CREATE TABLE table_name(
 id bigint not null auto_increment, 
 name varchar(30), 
 primary key(id)
) dbpartition by hash(id);

2.2 分库分表表创建语句

CREATE TABLE table_name(
id bigint not null auto_increment, 
bid int, 
name varchar(30), 
primary key(id)
) dbpartition by hash(id) tbpartition by hash(bid) tbpartitions 3;

说明：此文章是2023-07为了解决线上问题而记录的，一直未完善！