博客 - TIDB PCSD学习，了解TIDB和OB的SQL语法差异

最近跟着TIDB的8-9月份的免费认证活动一路跟下来，跟了1个多月，顺利拿到PCSD，简单想和OB分布式数据库进行一下简单的mysql语法兼容性的对比测试，了解下双方的差异。

直白一点就是用MySQL的通用SQL语法，测试了下对1、主外键关联表 2、普通分区表 3、复合分区表 4、索引等对象的DDL、DML的简单测试，以及简单存储过程的支持情况。

测试目的：对比TIDB VS OB 那个对MySQL兼容性好，对现行业务可以直接迁移合库到分布式，且无需应用层的SQL改造调整。

TIDB节点信息

OB版本信息：

一、外键约束支持情况测试

主外键关联表的支持SQL语句如下：

父表：部门表

DROP TABLE IF EXISTS emp_test_fk;

DROP TABLE IF EXISTS dept_test_fk;

CREATE TABLE dept_test_fk (

dept_id INT NOT NULL,

dept_name VARCHAR(50) NOT NULL,

dept_loc VARCHAR(100),

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (dept_id)

);

-- 子表：员工表（外键指向 dept_test_fk.dept_id）

-- 外键策略：ON DELETE CASCADE ON UPDATE CASCADE

CREATE TABLE emp_test_fk (

emp_id INT NOT NULL,

emp_name VARCHAR(50) NOT NULL,

emp_email VARCHAR(100),

salary DECIMAL(10,2) DEFAULT 0.00,

dept_id INT,

hire_date DATE,

create_time DATETIME DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (emp_id),

CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)

REFERENCES dept_test_fk(dept_id)

ON DELETE CASCADE

ON UPDATE CASCADE

);

-- 2.1 先插父表（部门）

INSERT INTO dept_test_fk (dept_id, dept_name, dept_loc) VALUES

(1, '研发中心', '北京海淀'),

(2, '产品部', '上海张江'),

(3, '市场部', '深圳南山'),

(4, '财务部', '北京朝阳'),

(5, '人力资源', '广州天河');

-- 2.2 再插子表（员工）—— dept_id 必须在父表中存在

INSERT INTO emp_test_fk (emp_id, emp_name, emp_email, salary, dept_id, hire_date) VALUES

(1001, '张三', 'zhangsan@demo.com', 15000.00, 1, '2022-03-15'),

(1002, '李四', 'lisi@demo.com', 18000.00, 1, '2022-06-20'),

(1003, '王五', 'wangwu@demo.com', 20000.00, 2, '2023-01-10'),

(1004, '赵六', 'zhaoliu@demo.com', 16000.00, 3, '2023-08-25'),

(1005, '钱七', 'qianqi@demo.com', 22000.00, 2, '2024-02-14'),

(1006, '孙八', 'sunba@demo.com', 19000.00, 4, '2024-05-30'),

(1007, '周九', 'zhoujiu@demo.com', 17000.00, 3, '2024-09-01'),

(1008, '吴十', 'wushi@demo.com', 25000.00, 1, '2025-01-05');

测试方法：

1、约束违规测试：插入子表不存在的外键值 INSERT INTO emp_test_fk (emp_id, emp_name, emp_email, salary, dept_id, hire_date) VALUES (1009, '测试员', 'test@demo.com', 12000.00, 999, '2025-03-01');

2、更新子表外键列为无效值：

UPDATE emp_test_fk SET dept_id = 888 WHERE emp_id = 1003;

TIDB：测试截图

OB测试截图

结论：二者均支持主外键关联表的约束，但触发的提醒错误TIDB更完整一些。

二、分区表支持情况测试range 、list、hash、range+hash

2.1 RANGE范围分区表

mysql> CREATE TABLE range_part_test ( -> id BIGINT NOT NULL AUTO_INCREMENT, -> emp_name VARCHAR(50) NOT NULL, -> emp_no VARCHAR(20) NOT NULL, -> hire_date DATE NOT NULL, -> salary DECIMAL(10,2) NOT NULL, -> dept_id INT NOT NULL, -> PRIMARY KEY (id, hire_date) -> ) -> PARTITION BY RANGE (YEAR(hire_date)) ( -> PARTITION p2021 VALUES LESS THAN (2022), -> PARTITION p2022 VALUES LESS THAN (2023), -> PARTITION p2023 VALUES LESS THAN (2024), -> PARTITION p_max VALUES LESS THAN MAXVALUE -> ); Query OK, 0 rows affected (0.58 sec)

-- 1. 向 RANGE 分区表写入数据 INSERT INTO range_part_test (emp_name, emp_no, hire_date, salary, dept_id) VALUES ('张三', 'EMP0001', '2022-03-15', 15000.00, 1), ('李四', 'EMP0002', '2022-06-20', 18000.00, 4), ('王五', 'EMP0003', '2023-01-10', 20000.00, 7), ('赵六', 'EMP0004', '2023-08-25', 16000.00, 2), ('钱七', 'EMP0005', '2024-02-14', 22000.00, 13), ('孙八', 'EMP0006', '2024-05-30', 19000.00, 5);

mysql> show create table range_part_test\G *************************** 1. row *************************** Table: range_part_test Create Table: CREATE TABLE range_part_test ( id bigint(20) NOT NULL AUTO_INCREMENT, emp_name varchar(50) NOT NULL, emp_no varchar(20) NOT NULL, hire_date date NOT NULL, salary decimal(10,2) NOT NULL, dept_id int(11) NOT NULL, PRIMARY KEY (id,hire_date) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin PARTITION BY RANGE (YEAR(hire_date)) (PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p_max VALUES LESS THAN (MAXVALUE)) 1 row in set (0.00 sec)

mysql>

OB ：show 查看表结构信息

obclient(root@(none))[test]> show create table range_part_test\G *************************** 1. row *************************** Table: range_part_test Create Table: CREATE TABLE range_part_test ( id bigint(20) NOT NULL AUTO_INCREMENT, emp_name varchar(50) NOT NULL, emp_no varchar(20) NOT NULL, hire_date date NOT NULL, salary decimal(10,2) NOT NULL, dept_id int(11) NOT NULL, PRIMARY KEY (id, hire_date) ) ORGANIZATION INDEX AUTO_INCREMENT = 7 AUTO_INCREMENT_MODE = 'ORDER' DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 partition by range(YEAR(hire_date)) (partition p2021 values less than (2022), partition p2022 values less than (2023), partition p2023 values less than (2024), partition p_max values less than (MAXVALUE)) 1 row in set (0.005 sec)

obclient(root@(none))[test]>

2.2 复合分区表 list+range

CREATE TABLE subpart_test ( id BIGINT NOT NULL AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL, emp_no VARCHAR(20) NOT NULL, province_id INT NOT NULL, order_date DATE NOT NULL, amount DECIMAL(12,2) NOT NULL, PRIMARY KEY (id, province_id, order_date) ) PARTITION BY LIST(province_id) SUBPARTITION BY RANGE (YEAR(order_date)) ( PARTITION p_east VALUES IN (11, 12, 31, 50) ( SUBPARTITION p_east_2021 VALUES LESS THAN (2022), SUBPARTITION p_east_2022 VALUES LESS THAN (2023), SUBPARTITION p_east_2023 VALUES LESS THAN (2024) ), PARTITION p_south VALUES IN (35, 44, 45, 46) ( SUBPARTITION p_south_2021 VALUES LESS THAN (2022), SUBPARTITION p_south_2022 VALUES LESS THAN (2023), SUBPARTITION p_south_2023 VALUES LESS THAN (2024) ), PARTITION p_west VALUES IN (51, 52, 53, 54) ( SUBPARTITION p_west_2021 VALUES LESS THAN (2022), SUBPARTITION p_west_2022 VALUES LESS THAN (2023), SUBPARTITION p_west_2023 VALUES LESS THAN (2024) ));

TIDB:不支持

TiDB 的产品限制——它只支持单级分区，不支持任何形式的SUBPARTITION BY

OB：支持

2.3 LIST分区表

CREATE TABLE list_part_test ( id BIGINT NOT NULL AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL, emp_no VARCHAR(20) NOT NULL, hire_date DATE NOT NULL, salary DECIMAL(10,2) NOT NULL, dept_id INT NOT NULL ) PARTITION BY LIST(dept_id) ( PARTITION p_dev VALUES IN (1, 2, 3), PARTITION p_sales VALUES IN (4, 5, 6), PARTITION p_hr VALUES IN (7, 8, 9), PARTITION p_ops VALUES IN (10, 11, 12,13) );

2.4 HASH分区表

CREATE TABLE hash_part_test ( id BIGINT NOT NULL AUTO_INCREMENT, emp_name VARCHAR(50) NOT NULL, emp_no VARCHAR(20) NOT NULL, hire_date DATE NOT NULL, salary DECIMAL(10,2) NOT NULL, dept_id INT NOT NULL, PRIMARY KEY (id) ) PARTITION BY HASH(id) PARTITIONS 4;

-- 2. 向 LIST 分区表写入数据（覆盖不同部门分区，不含已删除的 p_ops 部门 10-12） INSERT INTO list_part_test (emp_name, emp_no, hire_date, salary, dept_id) VALUES ('张三', 'EMP0001', '2022-03-15', 15000.00, 1), ('李四', 'EMP0002', '2022-06-20', 18000.00, 4), ('王五', 'EMP0003', '2023-01-10', 20000.00, 7), ('赵六', 'EMP0004', '2023-08-25', 16000.00, 2), ('钱七', 'EMP0005', '2024-02-14', 22000.00, 13), ('孙八', 'EMP0006', '2024-05-30', 19000.00, 5);

-- 3. 向 HASH 分区表写入数据 INSERT INTO hash_part_test (emp_name, emp_no, hire_date, salary, dept_id) VALUES ('周九', 'EMP0007', '2022-03-15', 15000.00, 1), ('吴十', 'EMP0008', '2022-06-20', 18000.00, 4), ('郑十一', 'EMP0009', '2023-01-10', 20000.00, 7), ('王十二', 'EMP0010', '2023-08-25', 16000.00, 2);

结论：二者均支持

查看表分区分布TIDB

OB表分区分布

三、索引支持情况测试，以及索引explain 差异

创建测试表（非分区表，用于纯在线 DDL 测试） CREATE TABLE online_idx_test ( id BIGINT NOT NULL AUTO_INCREMENT, user_name VARCHAR(50) NOT NULL, email VARCHAR(100), age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );

使用交叉连接生成 0~99999 序列，一次性插入 10 万行测试数据 INSERT INTO online_idx_test (user_name, email, age, created_at) SELECT CONCAT('user_', LPAD(seq.n, 6, '0')), CONCAT('user_', LPAD(seq.n, 6, '0'), '@test.com'), FLOOR(18 + RAND() * 62), DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY) FROM ( SELECT (a.n + b.n * 10 + c.n * 100 + d.n * 1000 + e.n * 10000) AS n FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d CROSS JOIN (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e ) seq WHERE seq.n < 100000;

添加索引时，不加锁

ALTER TABLE online_idx_test ADD INDEX idx_email(email), ALGORITHM=INPLACE, LOCK=NONE;

TIDB: