对象管理 #

mysql> create database mydb;

mysql> drop database mydb;

mysql> show databases;

mysql> use mydb;

  REGISTER STORAGE UNIT ds_3307 (
      HOST=127.0.0.1,
      PORT=3307,
      DB=test,
      USER=testuser,
      PASSWORD=testpwd
  )

sql> CREATE SHARDING TABLE RULE t_order(
  RESOURCES(ds_3307,ds_3308),
  SHARDING_COLUMN=order_id,
  TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
  KEY_GENERATE_STRATEGY(
    COLUMN=order_id,
    TYPE(NAME=snowflake,PROPERTIES("worker-id"=123))
  )
);

sql> CREATE TABLE t_order (
  order_id int NOT NULL,
  user_id int NOT NULL,
  status varchar(50) DEFAULT NULL,
  PRIMARY KEY (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

sql> DROP TABLE IF EXISTS t_order;

sql> DROP SHARDING TABLE RULE t_order;

db0
  ├── t_order0
  └── t_order1
db1
  ├── t_order0
  └── t_order1

db0
  ├── t_order0
  └── t_order1
db1
  ├── t_order2
  ├── t_order3
  └── t_order4

mysql> SHOW BROADCAST TABLE RULES;
Empty set (0.16 sec)

mysql> CREATE BROADCAST TABLE RULE t_address;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW BROADCAST TABLE RULES;
+-----------------+
| broadcast_table |
+-----------------+
| t_address       |
+-----------------+
1 row in set (0.03 sec)

mysql> COUNT BROADCAST RULE;
+-----------------+-------------+-------+
| rule_name       | database    | count |
+-----------------+-------------+-------+
| broadcast_table | sharding_db | 1     |
+-----------------+-------------+-------+
1 row in set (0.02 sec)

mysql> CREATE TABLE IF NOT EXISTS `t_address` (
    ->   `address_id` bigint(20) NOT NULL,
    ->   `address_name` varchar(100) NOT NULL,
    ->   `status` varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (`address_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.57 sec)

mysql> INSERT INTO t_address (address_id, address_name, status) VALUES 
    -> (1,'beijing','OK'),(2,'chengdu','OK');
Query OK, 2 rows affected (0.18 sec)

mysql> select * from t_address;
+------------+--------------+--------+
| address_id | address_name | status |
+------------+--------------+--------+
|          1 | beijing      | OK     |
|          2 | chengdu      | OK     |
+------------+--------------+--------+
2 rows in set (0.21 sec)

mysql> SHOW ARCHIVE RULES;
Empty set (0.36 sec)

mysql> CREATE ARCHIVE RULE t_order (
    ARCHIVE_STRATEGY(ARCHIVE_COLUMN=createtime, ARCHIVE_ALGORITHM(TYPE(NAME="SphereEx:TIME_PARTITION", PROPERTIES("interval-unit"="MONTHS", "interval-amount"=1, "retain-amount"=12, "pre-allocate-amount"=2)))),
    SCHEDULER_ALGORITHM(TYPE(NAME="SphereEx:TIMED_TASK", PROPERTIES("schedule-cron-expr"="0 15 10 ? * *", "time-zone"="+08:00")))
);

mysql> SHOW ARCHIVE TABLE RULE t_order;
+---------+----------------+-------------------------+-------------------------------------------------------------------------------+------------------------+-------------------------+--------------------------+---------------------------------------------------+
| table   | archive_column | archive_algorithm_type  | archive_algorithm_props                                                       | storage_algorithm_type | storage_algorithm_props | scheduler_algorithm_type | scheduler_algorithm_props                         |
+---------+----------------+-------------------------+-------------------------------------------------------------------------------+------------------------+-------------------------+--------------------------+---------------------------------------------------+
| t_order | createtime     | SphereEx:TIME_PARTITION | interval-amount=1,pre-allocate-amount=2,interval-unit=MONTHS,retain-amount=12 |                        |                         | SphereEx:TIMED_TASK      | schedule-cron-expr=0 15 10 ? * *,time-zone=+08:00 |
+---------+----------------+-------------------------+-------------------------------------------------------------------------------+------------------------+-------------------------+--------------------------+---------------------------------------------------+

mysql> CREATE TABLE `t_order` (
  `order_id` bigint NOT NULL,
  `user_id` int NOT NULL,
  `status` varchar(50) NOT NULL,
  `merchant_id` int DEFAULT NULL,
  `remark` varchar(50) NOT NULL,
  `createtime` datetime NOT NULL,
  PRIMARY KEY (`order_id`,`creation_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(createtime)
(PARTITION p20230122 VALUES LESS THAN ('2023-01-25') ENGINE = InnoDB,
 PARTITION p20230125 VALUES LESS THAN ('2023-01-28') ENGINE = InnoDB,
 PARTITION p20230128 VALUES LESS THAN ('2023-01-31') ENGINE = InnoDB,
 PARTITION p20230131 VALUES LESS THAN ('2023-02-03') ENGINE = InnoDB,
 PARTITION p20230203 VALUES LESS THAN ('2023-02-06') ENGINE = InnoDB,
 PARTITION p20230206 VALUES LESS THAN ('2023-02-09') ENGINE = InnoDB,
 PARTITION p20230209 VALUES LESS THAN ('2023-02-12') ENGINE = InnoDB,
 PARTITION p20230212 VALUES LESS THAN ('2023-02-15') ENGINE = InnoDB,
 PARTITION p20230215 VALUES LESS THAN ('2023-02-18') ENGINE = InnoDB,
 PARTITION p20230218 VALUES LESS THAN ('2023-02-21') ENGINE = InnoDB,
 PARTITION p20230221 VALUES LESS THAN ('2023-02-24') ENGINE = InnoDB,
 PARTITION p20230224 VALUES LESS THAN ('2023-02-27') ENGINE = InnoDB,
 PARTITION p20230227 VALUES LESS THAN ('2023-03-02') ENGINE = InnoDB,
 PARTITION p20230302 VALUES LESS THAN ('2023-03-05') ENGINE = InnoDB,
 PARTITION p20230305 VALUES LESS THAN ('2023-03-08') ENGINE = InnoDB,
 PARTITION p20230308 VALUES LESS THAN ('2023-03-11') ENGINE = InnoDB) */;

-- 1. 创建分片规则，按照 order_id 维度分片
CREATE SHARDING TABLE RULE t_order (
    STORAGE_UNITS(ds_0),
    SHARDING_COLUMN=order_id, TYPE(NAME="mod", PROPERTIES("sharding-count"=10))
);
       
-- 2. 创建全局索引策略 & 规则，按照 user_id 维度分片
CREATE GLOBAL INDEX STRATEGY t_order (
    (
        GLOBAL_INDEX_NAME=t_order_user_id_idx,
        COVERING_COLUMNS=order_id,
        STORAGE_UNITS(ds_0),
        SHARDING_COLUMN=user_id, TYPE(NAME="mod", PROPERTIES("sharding-count"=10))
    ),
    (
        GLOBAL_INDEX_NAME=t_order_merchant_id_idx,
        COVERING_COLUMNS=order_id,
        STORAGE_UNITS(ds_0),
        SHARDING_COLUMN=merchant_id, TYPE(NAME="mod", PROPERTIES("sharding-count"=10))
    ),
    CONSISTENCY_LEVEL=STRONG,
    TABLE_ACCESS_BY_INDEX_THRESHOLD=1000,
    SYNC_DELAY_MILLISECONDS_THRESHOLD=1000
);

-- 3. 查看全局索引规则（全局索引表名称目前为自动生成，待支持自定义）
SHOW GLOBAL INDEX STRATEGIES\G;
*************************** 1. row ***************************
                    primary_table: t_order
               global_index_table: t_order_user_id_idx
                 covering_columns: order_id
                actual_data_nodes:
              actual_data_sources: ds_0
           database_strategy_type:
         database_sharding_column:
 database_sharding_algorithm_type:
database_sharding_algorithm_props:
              table_strategy_type: STANDARD
            table_sharding_column: user_id
    table_sharding_algorithm_type: mod
   table_sharding_algorithm_props: {"sharding-count":"10"}
*************************** 2. row ***************************
                    primary_table: t_order
               global_index_table: t_order_merchant_id_idx
                 covering_columns: order_id
                actual_data_nodes:
              actual_data_sources: ds_0
           database_strategy_type:
         database_sharding_column:
 database_sharding_algorithm_type:
database_sharding_algorithm_props:
              table_strategy_type: STANDARD
            table_sharding_column: merchant_id
    table_sharding_algorithm_type: mod
   table_sharding_algorithm_props: {"sharding-count":"10"}
2 rows in set (0.00 sec)

-- 4. 创建分片表
CREATE TABLE t_order(order_id INT PRIMARY KEY, user_id INT NOT NULL, status VARCHAR(50) NOT NULL, merchant_id INT NOT NULL, remark VARCHAR(50), creation_date DATE NOT NULL);

-- 5. 创建全局索引（索引名称需要和全局索引表匹配）
CREATE INDEX t_order_user_id_idx ON t_order(user_id);
CREATE INDEX t_order_merchant_id_idx ON t_order(merchant_id);

-- 6. 查看全局索引
SHOW INDEX FROM t_order;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+--------------+-----------------+-------------------+---------+------------+
| Table   | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type   | Comment         | Index_comment     | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+--------------+-----------------+-------------------+---------+------------+
| t_order |          0 | PRIMARY                 |            1 | order_id    | A         |           0 |     NULL |   NULL |      | BTREE        |                 |                   | YES     | NULL       |
| t_order |          1 | t_order_user_id_idx     |            1 | order_id    | A         |           0 |     NULL |   NULL |      | GLOBAL_INDEX | COVERING_COLUMN | STATUS: PUBLISHED | YES     | NULL       |
| t_order |          1 | t_order_user_id_idx     |            1 | user_id     | A         |           0 |     NULL |   NULL |      | GLOBAL_INDEX |                 | STATUS: PUBLISHED | YES     | NULL       |
| t_order |          1 | t_order_merchant_id_idx |            1 | order_id    | A         |           0 |     NULL |   NULL |      | GLOBAL_INDEX | COVERING_COLUMN | STATUS: PUBLISHED | YES     | NULL       |
| t_order |          1 | t_order_merchant_id_idx |            1 | merchant_id | A         |           0 |     NULL |   NULL |      | GLOBAL_INDEX |                 | STATUS: PUBLISHED | YES     | NULL       |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+--------------+-----------------+-------------------+---------+------------+

-- 7. 写入数据
PREVIEW INSERT INTO t_order(order_id, user_id, status, merchant_id, remark, creation_date) VALUES (1, 1, 'SUCCESS', 1, 'remark', '2023-01-22');
+------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                                                                               |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0             | INSERT INTO t_order_1(order_id, user_id, status, merchant_id, remark, creation_date) VALUES (1, 1, 'SUCCESS', 1, 'remark', '2023-01-22') |
| ds_0             | INSERT INTO t_order_user_id_idx_1 (order_id, user_id) VALUES (1, 1)                                                                      |
| ds_0             | INSERT INTO t_order_merchant_id_idx_1 (order_id, merchant_id) VALUES (1, 1)                                                              |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------+

-- 8. 查询数据
PREVIEW SELECT * FROM t_order WHERE merchant_id = 1;
+------------------+-----------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                        |
+------------------+-----------------------------------------------------------------------------------+
| ds_0             | SELECT COUNT(1) AS COUNT FROM t_order_merchant_id_idx_1 WHERE merchant_id = 1     |
| ds_0             | SELECT order_id, merchant_id FROM t_order_merchant_id_idx_1 WHERE merchant_id = 1 |
| ds_0             | SELECT * FROM t_order_1 WHERE (merchant_id = 1) AND (order_id IN (1))             |
+------------------+-----------------------------------------------------------------------------------+

sql> CREATE SHARDING TABLE RULE t_order(
    ->   RESOURCES(ds_3307,ds_3308),
    ->   SHARDING_COLUMN=order_id,TYPE(NAME=hash_mod,PROPERTIES("sharding-count"=4)),
    ->   KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME=uuid,PROPERTIES("worker-id"=123)))
    -> );
Query OK, 0 rows affected (0.03 sec)

sql> CREATE TABLE t_order (
    ->   order_id varchar(50) NOT NULL,
    ->   user_id int NOT NULL,
    ->   status varchar(50) DEFAULT NULL,
    ->   PRIMARY KEY (order_id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.12 sec)

sql> INSERT INTO t_order(user_id, status) VALUES
    -> (1,  'SUCCESS'),
    -> (2,  'FAIL'),
    -> (3,  'FAIL'),
    -> (4,  'FAIL'),
    -> (5,  'FAIL');
Query OK, 5 rows affected (0.01 sec)

sql> select * from t_order;
+----------------------------------+---------+---------+
| order_id                         | user_id | status  |
+----------------------------------+---------+---------+
| 48e10218c7414bdbaf640aea2c079d32 |      12 | FAIL    |
| adcb6343fb43424baed8f297832d6897 |       9 | FAIL    |
| d01fe0d8f99744949f6f734803a6fbad |       1 | SUCCESS |
| 0a52a963254f4c889924fcef27afa497 |      16 | FAIL    |
| 0bee54f2ee8a4af8bb09250f01b2565b |      15 | FAIL    |
+----------------------------------+---------+---------+

-- 启动一致性
sql> SET DIST VARIABLE meta_data_consistency_enabled=true;

-- 确保一致性已启动
sql> SHOW DIST VARIABLE WHERE NAME = meta_data_consistency_enabled;


-- 执行 DDL 
sql> alter table ...


--查看 DDL 作业
sql> show ddl list;

-- 执行正常结束后，提交作业
sql> commit ddl '<id>';

-- 执行异常后，回滚作业
sql> rollback ddl '<id>';

-- 执行中间因故障停止，可选择人工修复后重试作业
sql> start ddl '<id>';

sql> create table t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL,  `test_column` bigint(20) NOT NULL DEFAULT '0',  `test_column2` bigint(20) NOT NULL DEFAULT '0');

-- 模拟故障

sql> alter table `t_order` modify column `test_column` varchar(10), modify column `test_column2` varchar(10);
-- ERROR 30000 (HY000): Unknown exception: Table 'demo_ds_1.t_order_0' doesn't exist, 
-- please use SHOW DDL STATUS 'j5601839ceed1d09d73d9d1f7e7f77de75228' to check status.
-- 此时会有一个存储节点ds_0上执行成功，ds_1上执行失败。由于不支持回滚，会触发job重试操作。

mysql> show ddl list;
+---------------------------------------+---------+--------------+----------------+--------+---------------------+---------------------+
| id                                    | tables  | ddl_type     | job_item_count | active | create_time         | stop_time           |
+---------------------------------------+---------+--------------+----------------+--------+---------------------+---------------------+
| j5601839ceed1d09d73d9d1f7e7f77de75228 | t_order | ALTER_TABLE  | 1              | false  | 2023-02-19 16:27:

-- 修复故障

-- proxy上重试job
sql> start ddl 'j5601839ceed1d09d73d9d1f7e7f77de75228';

-- proxy查看job执行完成并自动删除
mysql> show ddl list;
Empty set (0.02 sec)

props:
  proxy-ddl-strategy: ONLINE 
  # Options: ONLINE, DIRECT, AUTO
  # ONLINE：proxy 干预并保障 ddl 是 online 执行的，
  #    如果用户指定了instant 参数并且底层是 mysql8.0，则会使用 instant 方式执行；
  #    如果用户未指定 instant 参数并且底层是 mysql8.0，则会使用 instant 方式执行；
  #    如果不是 mysql8.0，则使用在线拷贝数据的方式执行。
  # DIRECT：proxy 干预一致性但是直接下发语句到db执行ddl
  # AUTO：依据用户配置由 proxy 决定 online、instant、在线拷贝数据的方式执行
  ghost-ddl-row-threshold: 1000000 # 触发复制表执行 DDL 行数阈值
  ghost-ddl-auto-cutover: true # 自动切换模式。禁用则需要手动执行 COMMIT DDL 完成切换
  ghost-ddl-drop-table: true # DDL 完成后自动删除原表

/* SPHEREEX_HINT: PROXY_DDL_STRATEGY=AUTO, GHOST_DDL_ROW_THRESHOLD=1000000, GHOST_DDL_AUTO_CUTOVER=true, GHOST_DDL_DROP_TABLE=true */ 
ALTER TABLE t MODIFY COLUMN target_col BIGINT;