tidb 增加索引 tidb唯一索引

转载

云端小梦 2024-04-24 21:36:52

表分区，注意事项

确认表中是否存在唯一键(包括主键) ，如果存在，那么唯一键 中，必须包含分区表达式中用到的所有列
如果表中不存在唯一键 ，那么第1条规则也就不需要遵守了
分区列是主键列，那么分区列不可以是 NULL
分区表达式中列的数据类型，要与表中列的数据类型相符合
删除分区一定会删除分区中所有数据，不会自动转移数据
MAXVALUE
按时间分区默认左开右闭区间。

1 表分区 RANGE

-- 创建表
CREATE TABLE table_range (
  a bigint(20) DEFAULT NULL,
  b varchar(255) DEFAULT NULL,
  c int(11) DEFAULT NULL,
  d date DEFAULT NULL,
  KEY m_index (a,b,c)
)
-- 创建 RANGE 表分区 按照年分区
PARTITION BY RANGE(YEAR(d)) (
    PARTITION p0 VALUES LESS THAN (1900),
    PARTITION p1 VALUES LESS THAN (2010),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 添加数据
INSERT INTO table_range VALUES (1,'1',1,'1900-01-01'), (2,'2',2,'2009-02-02'), (3,'3',3,'2019-03-03');

-- 添加表分区
-- ALTER TABLE table_range ADD PARTITION (PARTITION p3 VALUES LESS THAN (2014));

-- 清空表分区
-- ALTER TABLE table_range TRUNCATE PARTITION p1;

-- 删除表分区
-- ALTER TABLE table_range DROP PARTITION p3;

-- 查询数据 1900~2009 之间的数据，这里只会查询 p0 表分区
SELECT * FROM table_range WHERE d > 1900 AND d < 2009;

-- 指定查询 p1 表分区
SELECT * FROM table_range PARTITION (p1);

-- 创建表
CREATE TABLE table_range (
  a bigint(20) DEFAULT NULL,
  b varchar(255) DEFAULT NULL,
  c int(11) DEFAULT NULL,
  d date DEFAULT NULL,
  KEY m_index (a,b,c)
)
-- 创建 RANGE 表分区 按照 年、月、日 分区
PARTITION BY RANGE(TO_DAYS(d)) (
    PARTITION p0 VALUES LESS THAN (TO_DAYS('2017-02-01')),
    PARTITION p1 VALUES LESS THAN (TO_DAYS('2017-03-01')),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

-- 创建表
CREATE TABLE table_range (
  a bigint(20) DEFAULT NULL,
  b varchar(255) DEFAULT NULL,
  c int(11) DEFAULT NULL,
  d TIMESTAMP DEFAULT NULL,
  KEY m_index (a,b,c)
)
-- 创建 RANGE 表分区 按照 时间戳 分区
PARTITION BY RANGE(UNIX_TIMESTAMP(d)) (
    PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2017-02-01 00:00:00')),
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2017-03-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

查询系统表中记录的表分区

SELECT
    PARTITION_NAME AS '分区名称',
    FROM_DAYS(PARTITION_DESCRIPTION) AS '原分区条件',
    PARTITION_EXPRESSION AS '分区表达式',
    PARTITION_DESCRIPTION AS '分区条件',
    TABLE_ROWS AS '分区中数据行数'
FROM
    INFORMATION_SCHEMA.PARTITIONS
WHERE
    TABLE_SCHEMA = 'prd2_pfizer'
    AND TABLE_NAME = 'dc_flowdata_deliver_sale_range';

2 表分区 HASH

使用 Hash 分区时，需要在 CREATE TABLE 后面添加 PARTITION BY HASH (expr)，其中 expr 是一个返回整数的表达式。当这一列的类型是整数类型时，它可以是一个列名。此外，你很可能还需要加上 PARTITIONS num，其中 num 是一个正整数，表示将表划分多少分区。

-- 创建表
CREATE TABLE table_hash (
  a bigint(20) DEFAULT NULL,
  b varchar(255) DEFAULT NULL,
  c int(11) DEFAULT NULL,
  d date DEFAULT NULL,
  KEY m_index (a,b,c)
)
-- 创建 HASH 表分区
PARTITION BY HASH(YEAR(d))
PARTITIONS 6;

-- 添加数据
INSERT INTO table_hash VALUES (1,'1',1,'1900-01-01'), (2,'2',2,'2009-02-02'), (3,'3',3,'2019-03-03');

案例：

CREATE TABLE `deliver_sale` (
  `code` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '客户编码',
  `name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '客户名称',
  `file_id` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT '原始文件ID',
  `seller_date` date NOT NULL COMMENT '销售日期',
  PRIMARY KEY (`seller_date`),
  KEY `index_source_file_id` (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='销售数据'
PARTITION BY RANGE ( TO_DAYS(`seller_date`) ) (
  PARTITION `p01` VALUES LESS THAN ( TO_DAYS('2020-01-25')),
  PARTITION `p02` VALUES LESS THAN ( TO_DAYS('2020-02-25')),
  PARTITION `p03` VALUES LESS THAN ( TO_DAYS('2020-03-25')),
  PARTITION `p04` VALUES LESS THAN ( TO_DAYS('2020-04-25')),
  PARTITION `p05` VALUES LESS THAN ( TO_DAYS('2020-05-25'))
);

# 添加数据
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('9', '9', '9', '2020-01-04');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('5', '5', '5', '2020-02-15');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('4', '4', '4', '2020-03-14');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('1', '1', '1', '2020-04-03');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('3', '3', '3', '2020-05-24');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('3', '3', '3', '2020-06-24');
INSERT INTO `test`.`deliver_sale`(`code`, `name`, `file_id`, `seller_date`) VALUES ('3', '3', '3', '2020-03-25');

# 新增分区
ALTER TABLE test.deliver_sale ADD PARTITION (PARTITION p05 VALUES LESS THAN ( TO_DAYS('2020-05-25')));
ALTER TABLE test.deliver_sale ADD PARTITION (PARTITION p06 VALUES LESS THAN ( TO_DAYS('2020-06-25')));
ALTER TABLE test.deliver_sale ADD PARTITION (PARTITION p07 VALUES LESS THAN ( TO_DAYS('2020-07-25')));
ALTER TABLE test.deliver_sale ADD PARTITION (PARTITION max VALUES LESS THAN MAXVALUE);
# 删除分区
ALTER TABLE test.deliver_sale DROP PARTITION p07;
ALTER TABLE test.deliver_sale DROP PARTITION max;

# 验证左开右闭区间
explain select * from test.deliver_sale where seller_date = '2020-03-25'

mysql支持重组分区tidb现在不支持语句如下：

# 重组分区——拆分分区：
ALTER TABLE test.deliver_sale REORGANIZE PARTITION p07 INTO (
		PARTITION s0 VALUES LESS THAN ( TO_DAYS('2020-07-15')),
		PARTITION s1 VALUES LESS THAN ( TO_DAYS('2020-07-25'))
);

# 合并分区：
ALTER TABLE test.deliver_sale REORGANIZE PARTITION s0,s1 INTO (
    PARTITION a0 VALUES LESS THAN ( TO_DAYS('2020-07-25'))
);

只有以下函数可以用于分区表达式：

ABS()
CEILING() (see CEILING() and FLOOR())
DATEDIFF()
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
EXTRACT() (see EXTRACT() function with WEEK specifier)
FLOOR() (see CEILING() and FLOOR())
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
TO_SECONDS()
UNIX_TIMESTAMP() (with TIMESTAMP columns)
WEEKDAY()
YEAR()
YEARWEEK()

查看执行计划