分区表实践教程

-- ❌ 报错：A PRIMARY KEY must include all columns in the table's partitioning function
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    userid BIGINT
) PARTITION BY HASH(userid) PARTITIONS 16;

-- ✅ 正确：主键包含分区键
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT,
    userid BIGINT NOT NULL,
    PRIMARY KEY (userid)
) PARTITION BY HASH(userid) PARTITIONS 16;

-- ❌ 写入热点：新数据永远写入最后一个分区
CREATE TABLE t (
    id BIGINT AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
) PARTITION BY RANGE(id) (
    PARTITION p0 VALUES LESS THAN (100000),
    PARTITION p1 VALUES LESS THAN (200000),
    PARTITION p_maxvalue VALUES LESS THAN MAXVALUE   -- 所有新数据都在这里
);


-- ✅ RANGE 按时间管理生命周期 + HASH 打散写入
) PARTITION BY RANGE COLUMNS(created_at)
  SUBPARTITION BY HASH(userid) SUBPARTITIONS 6 (
    ...
);

-- ✅ 只访问1个节点，毫秒级返回
SELECT * FROM orders WHERE userid = 12345;

-- ❌ 访问所有节点，每个节点都要查一遍
SELECT * FROM orders WHERE orderno = 'ORD-001';

CREATE TABLE orders (
    ...
    KEY idx_order_no (orderno)     -- 不含分区键 userid 的 LOCAL 索引
) PARTITION BY HASH(userid) PARTITIONS 16;

SELECT * FROM orders WHERE orderno = 'ORD-2026-001';

-- 从这样
SELECT * FROM orders WHERE orderno = 'ORD-001';
-- 改成这样（如果业务能拿到 userid）
SELECT * FROM orders WHERE userid = 123 AND orderno = 'ORD-001';

-- 订单表, 改造前（ MySQL）
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    userid BIGINT,
    orderno VARCHAR(64),
    amount DECIMAL(10,2),
    status TINYINT,
    created_at DATETIME,
    KEY idx_order_no (orderno),
    KEY idx_created (created_at)
);

-- 订单表, 改造后（TDSQL Boundless）
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT,
    userid BIGINT NOT NULL,
    orderno VARCHAR(64) NOT NULL,
    amount DECIMAL(10,2),
    status TINYINT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (userid, orderno),             -- 分区键在主键第一位
    KEY idx_order_no (orderno),                -- 单号索引解决根据订单号的检索操作
    KEY idx_user_created (userid, created_at)  -- 用户订单列表查询
) PARTITION BY HASH(userid) PARTITIONS 16;

--  ✅ 不用改，天然命中分区
SELECT * FROM orders WHERE userid = 123 ORDER BY created_at DESC LIMIT 20;
SELECT * FROM orders WHERE userid = 123 ORDER BY orderno DESC LIMIT 20;

-- 最优场景，带上用户 userid 查询订单号
SELECT * FROM orders WHERE userid = 123 AND orderno = 'ORD-001';
-- 可接受（LOCAL 索引，16次索引查找）
SELECT * FROM orders WHERE orderno = 'ORD-001';

CREATE TABLE dim_city (
    city_id INT PRIMARY KEY,
    city_name VARCHAR(64)
) sync_level = node(all) distribution = node(all);

-- 带分区键
EXPLAIN SELECT * FROM orders WHERE userid = 12345;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: p1
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 2
     filtered: 100.00
        Extra: NULL
        
-- 不带分区键        
orders
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: p0,p1,p2,p3,p4,p5,p6,p7
         type: ref
possible_keys: idx_order_no
          key: idx_order_no
      key_len: 258
          ref: const
         rows: 9
     filtered: 100.00
        Extra: NULL