## 1. 创建按地区分区的客户表
mysql> CREATE TABLE fgedu_customers (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> region VARCHAR(20) NOT NULL,
-> email VARCHAR(100) UNIQUE,
-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP
-> ) PARTITION BY LIST (region) (
-> PARTITION p_north VALUES IN (‘北京’, ‘天津’, ‘河北’, ‘山西’, ‘内蒙古’),
-> PARTITION p_east VALUES IN (‘上海’, ‘江苏’, ‘浙江’, ‘安徽’, ‘福建’, ‘江西’, ‘山东’),
-> PARTITION p_south VALUES IN (‘广东’, ‘广西’, ‘海南’),
-> PARTITION p_central VALUES IN (‘河南’, ‘湖北’, ‘湖南’),
-> PARTITION p_west VALUES IN (‘重庆’, ‘四川’, ‘贵州’, ‘云南’, ‘西藏’, ‘陕西’, ‘甘肃’, ‘青海’, ‘宁夏’, ‘新疆’),
-> PARTITION p_other VALUES IN (‘其他’)
-> );

## 2. 插入测试数据
mysql> INSERT INTO fgedu_customers (name, region, email) VALUES
-> (‘张三’, ‘北京’, ‘zhangsan@example.com’),
-> (‘李四’, ‘上海’, ‘lisi@example.com’),
-> (‘王五’, ‘广东’, ‘wangwu@example.com’),
-> (‘赵六’, ‘四川’, ‘zhaoliu@example.com’),
-> (‘孙七’, ‘河南’, ‘sunqi@example.com’),
-> (‘周八’, ‘其他’, ‘zhouba@example.com’),
-> (‘吴九’, ‘北京’, ‘wuju@example.com’),
-> (‘郑十’, ‘上海’, ‘zhengshi@example.com’);

## 3. 查看分区信息
mysql> SELECT
-> partition_name,
-> table_rows
-> FROM
-> information_schema.partitions
-> WHERE
-> table_schema = ‘fgedudb’
-> AND table_name = ‘fgedu_customers’;

## 4. 测试分区查询
mysql> EXPLAIN SELECT * FROM fgedu_customers WHERE region = ‘北京’;

## 5. 添加新分区
mysql> ALTER TABLE fgedu_customers ADD PARTITION (
-> PARTITION p_hongkong VALUES IN (‘香港’, ‘澳门’, ‘台湾’)
-> );

## 6. 插入香港数据
mysql> INSERT INTO fgedu_customers (name, region, email) VALUES
-> (‘陈一’, ‘香港’, ‘chenyi@example.com’),
-> (‘林二’, ‘澳门’, ‘liner@example.com’),
-> (‘黄三’, ‘台湾’, ‘huangsan@example.com’);

## 7. 查看分区信息
mysql> SELECT
-> partition_name,
-> table_rows
-> FROM
-> information_schema.partitions
-> WHERE
-> table_schema = ‘fgedudb’
-> AND table_name = ‘fgedu_customers’;

## 1. 创建Range + List子分区的销售表
mysql> CREATE TABLE fgedu_sales (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> order_no VARCHAR(32) UNIQUE,
-> user_id INT,
-> amount DECIMAL(10,2) NOT NULL,
-> region VARCHAR(20) NOT NULL,
-> created_at DATETIME NOT NULL
-> ) PARTITION BY RANGE (TO_DAYS(created_at))
-> SUBPARTITION BY LIST (region)
-> SUBPARTITIONS 3 (
-> PARTITION p202401 VALUES LESS THAN (TO_DAYS(‘2024-02-01’)),
-> PARTITION p202402 VALUES LESS THAN (TO_DAYS(‘2024-03-01’)),
-> PARTITION p202403 VALUES LESS THAN (TO_DAYS(‘2024-04-01’))
-> );

## 2. 插入测试数据
mysql> INSERT INTO fgedu_sales (order_no, user_id, amount, region, created_at) VALUES
-> (‘FG202401010001’, 1, 100.00, ‘北京’, ‘2024-01-01 10:00:00’),
-> (‘FG202401010002’, 2, 200.00, ‘上海’, ‘2024-01-01 11:00:00’),
-> (‘FG202401010003’, 3, 300.00, ‘广东’, ‘2024-01-01 12:00:00’),
-> (‘FG202402010001’, 4, 400.00, ‘北京’, ‘2024-02-01 10:00:00’),
-> (‘FG202402010002’, 5, 500.00, ‘上海’, ‘2024-02-01 11:00:00’),
-> (‘FG202402010003’, 6, 600.00, ‘广东’, ‘2024-02-01 12:00:00’),
-> (‘FG202403010001’, 7, 700.00, ‘北京’, ‘2024-03-01 10:00:00’),
-> (‘FG202403010002’, 8, 800.00, ‘上海’, ‘2024-03-01 11:00:00’),
-> (‘FG202403010003’, 9, 900.00, ‘广东’, ‘2024-03-01 12:00:00’);

## 3. 查看子分区信息
mysql> SELECT
-> partition_name,
-> subpartition_name,
-> table_rows
-> FROM
-> information_schema.partitions
-> WHERE
-> table_schema = ‘fgedudb’
-> AND table_name = ‘fgedu_sales’
-> ORDER BY
-> partition_name, subpartition_name;

## 4. 测试子分区查询
mysql> EXPLAIN SELECT * FROM fgedu_sales WHERE created_at BETWEEN ‘2024-01-01’ AND ‘2024-01-31’ AND region = ‘北京’;

## 5. 添加新的主分区
mysql> ALTER TABLE fgedu_sales ADD PARTITION (
-> PARTITION p202404 VALUES LESS THAN (TO_DAYS(‘2024-05-01’))
-> );

## 6. 插入4月份数据
mysql> INSERT INTO fgedu_sales (order_no, user_id, amount, region, created_at) VALUES
-> (‘FG202404010001’, 10, 1000.00, ‘北京’, ‘2024-04-01 10:00:00’),
-> (‘FG202404010002’, 11, 1100.00, ‘上海’, ‘2024-04-01 11:00:00’),
-> (‘FG202404010003’, 12, 1200.00, ‘广东’, ‘2024-04-01 12:00:00’);

## 7. 查看分区信息
mysql> SELECT
-> partition_name,
-> table_rows
-> FROM
-> information_schema.partitions
-> WHERE
-> table_schema = ‘fgedudb’
-> AND table_name = ‘fgedu_sales’
-> GROUP BY
-> partition_name;

## 1. 测试环境
– 数据库：TiDB 7.5.0
– 服务器：8核16GB
– 数据量：100万条记录

## 2. 测试场景
– List分区：按地区分区
– 子分区：Range(时间) + List(地区)
– 普通表：无分区

## 3. 查询性能测试

### 按分类查询
– List分区：0.15秒
– 子分区：0.12秒
– 普通表：0.35秒

### 按时间范围查询
– List分区：0.25秒
– 子分区：0.10秒
– 普通表：0.40秒

### 按时间+地区查询
– List分区：0.20秒
– 子分区：0.08秒
– 普通表：0.45秒

### 插入性能
– List分区：1.3秒/1000条
– 子分区：1.5秒/1000条
– 普通表：1.8秒/1000条

### 删除性能
– List分区：0.8秒（删除一个分区）
– 子分区：1.0秒（删除一个主分区）
– 普通表：3.2秒

## 4. 性能优化建议
– 对于多维度查询，使用子分区可以显著提高性能
– 合理设置分区和子分区数量，避免过多分区
– 确保查询条件包含分区键，利用分区裁剪
– 定期维护分区表，保持良好性能