創建表 · k8s原理與實踐

[TOC] # 創建表可以通過執行 DDL 語句 CREATE TABLE 來創建一個表。 ## 使用 CREATE TABLE 語句建表 * 下面示例使用 CREATE TABLE 語句創建訂單表 ware 和 cust 表。 ~~~ obclient> create table ware(w_id int , w_ytd decimal(12,2) , w_tax decimal(4,4) , w_name varchar(10) , w_street_1 varchar(20) , w_street_2 varchar(20) , w_city varchar(20) , w_state char(2) , w_zip char(9) , unique(w_name, w_city) , primary key(w_id) ); Query OK, 0 rows affected (0.06 sec) obclient> create table cust (c_w_id int NOT NULL , c_d_id int NOT null , c_id int NOT null , c_discount decimal(4, 4) , c_credit char(2) , c_last varchar(16) , c_first varchar(16) , c_middle char(2) , c_balance decimal(12, 2) , c_ytd_payment decimal(12, 2) , c_payment_cnt int , c_credit_lim decimal(12, 2) , c_street_1 varchar(20) , c_street_2 varchar(20) , c_city varchar(20) , c_state char(2) , c_zip char(9) , c_phone char(16) , c_since date , c_delivery_cnt int , c_data varchar(500) , index icust(c_last, c_d_id, c_w_id, c_first, c_id) , FOREIGN KEY (c_w_id) REFERENCES ware(w_id) , primary key (c_w_id, c_d_id, c_id) ); Query OK, 0 rows affected (0.06 sec) ~~~ **說明** 由于 ALTER TABLE 語法不支持后期增加主鍵，所以需要在建表的時候設置主鍵。 ## 使用 CREATE TABLE 復制表數據在 MySQL 租戶里，可以使用 CREATE TABLE AS SELECT 復制表的數據，但是結構并不完全一致，會丟失約束、索引、默認值、分區等信息。使用 CREATE TABLE LIKE 可以復制表結構，但是不包括數據。 * 示例：MySQL 租戶的 CREATE TABLE 復制表結構和數據的區別 ~~~ obclient> create table t1( id bigint not null primary KEY , name varchar(50) not NULL , gmt_create timestamp not null default current_timestamp ) partition by hash(id) partitions 8; Query OK, 0 rows affected (0.10 sec) obclient> insert into t1(id,name) values(1,'A'),(2,'B'),(3,'C'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 obclient> create table t1_like like t1; Query OK, 0 rows affected (0.11 sec) obclient> create table t1_copy as select * from t1; Query OK, 3 rows affected (0.12 sec) obclient> show create table t1_like\G *************************** 1. row *************************** Table: t1_like Create Table: CREATE TABLE `t1_like` ( `id` bigint(20) NOT NULL, `name` varchar(50) NOT NULL, `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 PROGRESSIVE_MERGE_NUM = 2 partition by hash(id) (partition p0, partition p1, partition p2, partition p3, partition p4, partition p5, partition p6, partition p7) 1 row in set (0.02 sec) obclient> show create table t1_copy\G *************************** 1. row *************************** Table: t1_copy Create Table: CREATE TABLE `t1_copy` ( `id` bigint(20) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `gmt_create` timestamp NULL DEFAULT NULL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 1 row in set (0.00 sec) obclient> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` bigint(20) NOT NULL, `name` varchar(50) NOT NULL, `gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 PROGRESSIVE_MERGE_NUM = 2 partition by hash(id) (partition p0, partition p1, partition p2, partition p3, partition p4, partition p5, partition p6, partition p7) 1 row in set (0.01 sec) ~~~ ## 關于表和分區在 OceanBase 數據庫中，數據存儲在表中，而數據表示的最小粒度是分區。普通的非分區表，就只有一個分區；而分區表，通常有多個分區，分區名默認以 p 開頭，按數字順序從 0 開始編號。所以分區是表的子集。通常分區對用戶的應用是透明的，應用只需要使用 SQL 讀寫表即可。只有某些場景下，為了提升分區表的查詢性能，應用也可以使用 SQL 直接訪問某個具體的分區，SQL 語法格式是： ~~~ SELECT ... FROM parted_table PARTITION (pN) WHERE query_condition ; ~~~ * 示例：通過 SQL 直接訪問分區表的分區 ~~~ obclient> select o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr partition (p1) where o_w_id=1 and o_d_id=2 and o_id=2100; +------+--------+--------------+----------+-------------+------------+ | o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d | +------+--------+--------------+----------+-------------+------------+ | 2100 | 8 | 8 | 11 | 1 | 2020-02-15 | +------+--------+--------------+----------+-------------+------------+ 1 row in set (0.01 sec) obclient> select ol_o_id, ol_number,ol_delivery_d,ol_amount,ol_i_id,ol_supply_w_id,ol_quantity from ordl partition (p1) where ol_w_id=1 and ol_d_id=2 and ol_o_id=2100; +---------+-----------+---------------+-----------+---------+----------------+-------------+ | ol_o_id | ol_number | ol_delivery_d | ol_amount | ol_i_id | ol_supply_w_id | ol_quantity | +---------+-----------+---------------+-----------+---------+----------------+-------------+ | 2100 | 1 | 2020-02-15 | 0.00 | 87133 | 1 | 5 | | 2100 | 2 | 2020-02-15 | 0.00 | 47413 | 1 | 5 | | 2100 | 3 | 2020-02-15 | 0.00 | 9115 | 1 | 5 | | 2100 | 4 | 2020-02-15 | 0.00 | 42985 | 1 | 5 | | 2100 | 5 | 2020-02-15 | 0.00 | 43621 | 1 | 5 | | 2100 | 6 | 2020-02-15 | 0.00 | 5787 | 1 | 5 | | 2100 | 7 | 2020-02-15 | 0.00 | 62576 | 1 | 5 | | 2100 | 8 | 2020-02-15 | 0.00 | 91592 | 1 | 5 | | 2100 | 9 | 2020-02-15 | 0.00 | 34452 | 1 | 5 | | 2100 | 10 | 2020-02-15 | 0.00 | 13792 | 1 | 5 | | 2100 | 11 | 2020-02-15 | 0.00 | 94326 | 1 | 5 | +---------+-----------+---------------+-----------+---------+----------------+-------------+ 11 rows in set (0.01 sec) ~~~ **說明** 如果是組合分區，可以訪問更細粒度的分區，詳細描述請參考“分區路由”章節。在 OceanBase 數據庫里，節點間的數據遷移的最小粒度是分區，每個分區在集群里有三個副本，內容保持同步，角色上有區分。三副本會有一個主副本（Leader 副本）和兩個備副本（Follower 副本），只有主副本可以提供寫服務，默認也只有主副本可以提供讀服務。主副本上的事務提交時會將事務日志同步到兩個備副本，三副本使用 Paxos 協議表決事務是否提交成功。有時候為了不影響主副本，可以讓備副本承擔部分讀請求，這就是應用常用的讀寫分離的解決方案，這種讀備稱為**弱一致性讀**。使用這種方案，應用讀需要承擔讀延時的風險，這個延時最大允許值會通過參數（max\_stale\_time\_for\_weak\_consistency）控制。 * 示例：使用 SQL Hint 實現讀寫分離。弱一致讀的 Hint 語法是 /\*+ read\_consistency(weak) \*/ 。通常的讀默認是強一致性讀，就不用 Hint 了。 ~~~ obclient> select /*+ read_consistency(weak) */ o_id,o_c_id,o_carrier_id,o_ol_cnt,o_all_local,o_entry_d from ordr where o_w_id=1 and o_d_id=2 and o_id=2100; +------+--------+--------------+----------+-------------+------------+ | o_id | o_c_id | o_carrier_id | o_ol_cnt | o_all_local | o_entry_d | +------+--------+--------------+----------+-------------+------------+ | 2100 | 8 | 8 | 11 | 1 | 2020-02-15 | +------+--------+--------------+----------+-------------+------------+ 1 row in set (0.00 sec) ~~~ ## 復制表復制表是分布式數據庫 OceanBase 的高級優化手段。通常 OceanBase 集群是三副本架構，默認每個表的每個分區在 OceanBase 中會有三個副本數據，角色上分為一個主副本（Leader 副本）和兩個備副本（Follower副本），默認提供讀寫服務的是主副本。復制表可以指定在租戶的每臺機器上都有一個備副本，并且主副本跟所有備份的數據使用全同步策略保持強同步。這樣做的目的是為了讓業務有些 SQL 關聯查詢時能在同一節點內部執行，以獲取更好的性能。復制表的語法是在 CREATE TABLE 語句后增加`DUPLICATE_SCOPE`選項。 * 示例：創建復制表。 ~~~ obclient> create table item (i_id int , i_name varchar(24) , i_price decimal(5,2) , i_data varchar(50) , i_im_id int , primary key(i_id)) pctfree=0 BLOCK_SIZE=16384 duplicate_scope='cluster' locality='F@zone1,F@zone2,R{all_server}@zone3' primary_zone='zone1'; Query OK, 0 rows affected (0.06 sec) ~~~