TiDB技術分享
點擊上方“IT那活兒”,關注后了解更多精彩內容??!
1. 目的
本文檔旨在為使用 TiDB 數據庫的應用和系統提供統一規范參考,標準化 TiDB 數據庫 的開發使用及SQL優化流程,提高業務開發系統的規范性和代碼的可讀性,減輕維護工作量, 提高工作效率。2. 適用范圍
3. 注意事項
用于規范數據庫對象的名命,如數據庫(DATABASE)、表(TABLE)、索引(INDEX)、用戶(USER)等的命名約定。1. 原則
1)命名建議使用具有意義的英文詞匯,詞匯中間以下劃線分隔;3)避免用 TiDB 的保留字如:group,order 等作為單個字段名;2. 數據庫命名規范
建議按照業務、產品線或者其它指標進行區分,一般不要超過 20 個字符。如:臨時庫( db_tmp)、測試庫(db_test)。3. 表
3.1. 表命名規范
1)同一業務或者模塊的表盡可能使用相同的前綴,表名稱盡可能表達含義。3)建議對表的用途進行注釋說明,以便于統一認識。如:● 臨時表(tbl_tmp_crm_relation_0425);● 備份表(tbl_bak_crm_relation_20170425)。4)不同業務模塊的表多帶帶建立 DATABASE,并增加相應注釋。5)目前 TiDB 只支持將 lower-case-table-names 值設為 2,即按照大小寫來保存表名,按照小 寫來比較(不區分大小寫)。3.2. 表的設計
1)在進行 TiDB 表設計過程中,有以下幾點需要注意:● 為了保障主從集群復制以及增量備份的冪等性,表需要有主鍵。建議采用自然主鍵,而非業務字段作為主鍵。TIDB為避免熱點問題,可以用AutoRandom替換AUTOINCREMENT,隨機分配,采用unsigned bigint 作為該列的列屬性。建議采用聚簇索引,非聚簇索引主鍵實際為唯一索引,存儲仍以隱式的rowid構成。聚簇索引不支持增刪改操作。● 出于為性能考慮,盡量避免存儲超寬表,表字段數不建議超過 60 個,建議單行的總數 據大小不要超過 64K,數據長度過大字段最好拆到另外的表。● 表及字段要加commet屬性,方便業務的理解。● 單表數據規模建議在1000萬以內。對于大型業務表,建議建表初期設計分區以及清理備份策略。● 需要 join 的字段,數據類型保障絕對一致,避免隱式轉換。● 字段屬性盡量加上NOT NULL約束以及默認值,使用NULL值會導致如下副作用:----含義不明,對很多運算符不管用,增加復雜度。2)TiDB 字符集默認就是 UTF8 ,而且目前只支持 UTF8:● 使用 utf8mb4 編碼,它是 utf8 的超集,除了將編碼改為 utf8mb4 外不需要做其他轉換。● TiDB 中,utf8mb4 的默認排序規則為 utf8mb4_bin(區分大小寫)。● 4.0 支持 utf8mb4_general_ci(不區分大小寫) ,需要初始化集群前配置 new_collations_enabled_on_first_bootstrap = true 。4. 字段
4.1. 字段命名規范
3)字段需要添加注釋,枚舉型需指明主要值的含義,如”0 - 離線,1 - 在線”;4)布爾值列命名為 [is_描述]。如 member 表上表示為 enabled 的會員的列命名為 is_enabled ;5)字段名不建議超過 30 個字符,字段個數不建議大于 60;6)盡量避免使用保留字,如 order、from、desc 等,請參考官方保留字。4.2. 字段的設計
● TiDB 支持 MySQL 所有的整數類型,包括 INTEGER/INT、TINYINT、SMALLIN、T MEDIUMINT 以及 BIGINT;● INT:所有整數類型的字段推薦只使用 INT 或者 BIGINT;● 推薦使用 INT(10) UNSIGNED 存儲 IPv4 格式 IP 地址;● TINYINT(1)、TINYINT(4) 都是存儲一個字節,并不會因為括號里的數字改變。例如 TINYINT(4) 存儲 22 則會顯示 0022,因為最大寬度為4,達不到的情況下用0來補充。● TiDB 支持 MySQL 所有的浮點類型,包括 FLOAT、DOUBLE,DECIMAL、NUMERIC 等。● DECIMAL(M,D):推薦使用 DECIMAL 類型。float 和 double 在存儲的時候,存在精度 損失的問題,很可能在值的比較時,得到不正確的結果。DECIMAL 在與 VARCHAR、 CHAR 類型比較時會轉換為 DOUBLE 類型進行比較,也存在精度損失問題,建議在進 行比較時使用顯示類型轉換,如 CAST 避免精度損失。● TiDB 支持 MySQL 所有的日期時間類型,包括 DATE、DATETIME、TIMESTAM、P TIME 以及 YEAR;● DATE:所有只需要精確到天的字段全部使用 DATE 類型,而不應該使用 TIMESTAMP 或者DATETIME 類型;● DATETIME:所有需要精確到時間(時分秒)的字段均使用 DATETIME,不要使用 TIMESTAMP 類型;● 時間字段使用時間日期類型,不要使用字符串類型存儲。否則無法利用日期函數對日 期字段進行操作,而需要使用字符串函數進行復雜的操作。● 盡管 TiDB 嘗試解釋不同的格式,日期部分必須是按 年-月-日 的順序(比如,’ 98-09-04’),而不是 月-日-年 或者 日-月-年 的順序;● 要求年份必須是四位數字。日期值中包含兩位數字的年份是有歧義的,TiDB 按下面規則解釋:范圍在 70-99 之間的被轉換成 1970-1999 范圍在 00-69 之間的被轉換成 2000-2069 。● TiDB 支持 MySQL 所有的字符串類型,包括 CHAR、VARCHAR、BINARY、 VARBINARY、BLOB、TEXT、ENUM 以及 SET;● VARCHAR(N):所有動態長度字符串全部使用 VARCHAR 類型,N 表示的是字符數不 是字節數,比如 VARCHAR(256),需要根據實際的寬度來選擇 N,N 盡可能??;● CHAR:僅僅只有單個字符的字段使用 CHAR(1) 類型,例如性別字段;● TEXT:僅僅當字符數量可能超過 20000 個的時候,才建議使用TEXT類型來存放字符 類數據,因為所有 TiDB 數據庫都會使用 UTF8 字符集。所有使用 TEXT 類型的字段 建議和原表進行分拆,與原表主鍵多帶帶組成另外一個表進行存放;● 不建議使用 ENUM、SET 類型,盡量使用 TINYINT 來代替。5. 索引
5.1. 索引命名規范
1)主鍵索引:
2)唯一索引:
3)普通索引:
idx_[表名稱簡寫]_[字段名簡寫] 4)多單詞組成的 column_name,取盡可能代表意義的縮寫。5.2. 索引設計
1)選擇區分度大的列建立索引,不在低基數列上建立索引,例如:“性別”,“是否是 XXX”;2)單張表的索引數量控制在 5 個以內,避免冗余索引;6)對于確定需要組成組合索引的多個字段,建議將選擇性高的字段靠前放;7)最左前綴原則,使用聯合索引時,從左向右匹配,比如索引 idx_c1_c2_c3 (c1,c2,c3,)相當 于創建了 (c1)、(c1,c2)、(c1,c2,c3) 三個索引,where 條件包含上面三種情況的字段比較則可 以用到索引,但像 where c1=a and c3=c 只能用到 c1 列的索引,像 c2=b and c3=c 等情況就 完全用不到這個索引;8)很長的 VARCHAR 字段建立索引時,指定索引長度,沒必要對全字段建立索引,根據 實際 文本區分度決定索引長度即可。idx_table_name (name(10));10)ORDER BY,GROUP BY,DISTINCT 的字段需要添加在索引的后面,形成覆蓋索引;11)不建議在 where 條件索引列上使用函數,會導致索引失效,如 lower(email);12)使用 like 模糊匹配,% 不要放首位,會導致索引失效。5.3. TiDB 中的索引
索引也是數據,也要占用存儲空間。和表中的數據一樣,TiDB 中表的索引在存儲引擎 中也被作為 kv 來存儲,一行索引是一個 kv 對。例如一張有 10 個索引的表,每插入一行數據 的時候,會寫入 11 個 kv 對。TiDB 支持主鍵索引,唯一索引,也支持二級索引,構成以上索引的可以是單一列,也 可以是多個列(復合索引)。TiDB 目前(v5.0)還不支持反向/雙向索引,全文索引,分區表的全局索引。TiDB 中在查詢的謂詞是 =,>,=,<=,like ‘...%’,not like ‘.,..i%n,’ not in,<>,!=, is null,is not null,between…and … 時能夠使用索引,使用與否由優化器來決策。TiDB 中在查詢的謂詞是 like ‘%...’,like ‘%...%’,not like ‘%..,.’not like ‘%...%’,<=> 時 ,都無法使用索引。目前版本(v5.0)還沒有對 <=> 做處理,無法像 is null 一樣使用索引。1. 賬號權限
TiDB 在數據庫初始化時會生成一個 root@% 的默認賬戶,生產環境建議調整 root 用戶為強密碼,且不對外開放。線上所需用戶建議按照用戶或者業務場景劃分,根據實際情況對每個用戶授予相應權限,例如:1
| root | 超級用戶 | 全局管理,禁止對外開放 |
2
| dba | 數據庫管理員 | 數據庫 DBA |
3
| app | 應用開發 | 應用開發 |
4
| tempuser | 臨時統計 | 線上業務臨時統計,只讀用戶 |
5
| other | 其他用戶 | 第三方人員訪問 |
2. 角色
角色是一系列權限的集合。用戶可以創建角色、刪除角色、將權限賦予角色;也可以將角色授予給其他用戶,被授予的用戶在啟用角色后,可以得到角色所包含的權限。2.1. 創建角色
CREATE ROLE app_developer, app_read, app_write;
角色會被保存在 mysql.user 表中,角色名稱的主機名部分(如果省略)默認為 %。如果表中有同名角色或用戶,角色會創建失敗并報錯。創建角色的用戶需要擁有 CREATE ROLE 或 CREATE USER 權限。2.2. 角色授權
為 app_read 角色授予數據庫 app_db 的讀權限:GRANT SELECT ON app_db.* TO app_read@%;
2.3. 角色賦給用戶
GRANT app_read TO dev1@localhost;
2.4. 默認角色
角色在授予給用戶之后,并不會生效;只有在用戶啟用了某些角色之后,才可以使用角色擁有的權限。可以對用戶設置默認啟用的角色;用戶在登錄時,默認啟用的角色會被自動啟用。比如將 app_read 和 app_wirte 設置為 rw_user1@localhost 的默認啟用角色:SET DEFAULT ROLE app_read, app_write TO rw_user1@localhost;
將 dev1@localhost 的所有角色,設為其默認啟用角色:SET DEFAULT ROLE ALL TO dev1@localhost;
2.5. 啟用角色
為當前用戶啟用角色 app_read 和 app_write ,僅在當前 session 有效:SET ROLE app_read, app_write;
SET ROLE ALL EXCEPT app_read
1. 建表刪表規范
1.1. 基本原則:表的建立在遵循表命名規范前提下,建議業務應用內部封裝建表刪表語句增加判斷邏輯,防止業務流程異常中斷。1.2. 詳細說明:create table if not exists table_name 或 者 drop table if exists table_name 語句建議增加 if 判 斷,避免應用側由于表的改動造成的異常中斷。1.3. 不支持 create table as select 語法,需要改寫為表結構復制 create table like … 和將數據寫入的 insert into select … 的組合語句。2. select * 使用規范
2.1. 基本原則:禁止使用 select * 進行查詢。2.2. 詳細說明:按需求選擇合適的字段列,杜絕直接 SELECT * 讀取全部字段,減少網絡帶寬消耗,有效利用 覆蓋索引。3. Region 熱點
● 這條 SQL 涉及到的 region 的 leader 全部在這個 TiKV 上;● 這條 SQL 只涉及到一個 region,并且有大量的請求使用同樣或者類似的 SQL。3.1. 基本原則:如果表的數據量比較小,數據存儲大概率只涉及到一個 region,大量請求對該表進行寫入或者讀取都會造成該 region 熱點,可以通過手工拆分 region 方式進行調整,也可以在建表時預先進行 split region。●Handle ID 設計 避免連續自增主鍵的設計,建議采用AutoRandom替換AUTOINCREMENT,隨機分配;● TiDB Partition 通過設置表分區方式來避免熱點,支持按照 Hash 以及按照 Range 分區。4. 字段上使用函數規范
4.1. 基本原則:在取出字段上可以使用相關函數,但是在 Where 條件中的過濾條件字段上嚴禁使用任何函數, 包括數據類型轉換函數。``` select gmt_create from ... where
date_format(gmt_create,%Y%m%d %H:%i:%s) = 20090101 00:00:0 ```
``` select date_format(gmt_create,%Y%m%d %H:%i:%s) from .. .
where gmt_create = str_to_date(20090101 00:00:00,%Y%m%d %H:%i:s);```
5. 數據刪除規范
刪除表中全部的數據時,使用 TRUNCATE 或者 DROP 后重建方式,不要使用 DELETE;DELETE,TRUNCATE 和 DROP 都不會立即釋放空間,對于 TRUNCATE 和 DROP操作,在 達到 TiDB 的 GC (garbage collection) 時間后(默認 10 分鐘),TiDB的 GC 機制會刪除數據 并釋放空間。對于 DELETE 操作 TiDB 的 GC 機制會刪除數據,但不會釋放空間,而是當后 續數據寫入 RocksDB 且進行 compact 時對空間重新利用。TiDB 支持的隔離級別是 Snapshot Isolation(SI),和 RepeatabRleead(RR) 隔離級別 基本等價。基于日志的數據庫在面對大事務時,需要手動調大可用日志的容量,以避免日志被單 一事務占滿。TiDB 處理大事務的性能相較于處理并發的小事務的性能要差,因此 TiDB 中對于事務 量設定了一些限制:● 最大單行記錄容量為 120MB(v5.0 及更高的版本可通過 tidb-server 配置項 performance.txn-entry-size-limit 調整,低于 v5.0 的版本支持的單行容量為 6MB);● 支持的最大單個事務容量為 10GB(v4.0 及更高版本可通過 tidb-server 配置項 performance.txn-total-size-limit 調整,低于 v4.0 的版本支持的最大單個事務容量為 100MB)。1. group by
出于便捷的考量,MySQL “擴展” 了 group by 語法,使 select 子句可以引用未在 group by 子句中聲明的非聚集字段,也就是 non-full group by 語法,在其他數據庫中,這被認為是一種語法錯誤,因為這會導致結果集不穩定。想保障 group by 語句結果集的穩定,請使用 full group by 語法。2. order by
在 SQL 的語義中,只有使用了 order by 語法才會保障結果集的順序輸出。而單機數據 庫由于數據都存儲在一臺服務器上,在不進行數據重組時,多次執行的結果往往是穩定的,有些數據庫(尤其是 MySQL InnoDB 存儲引擎)還會按照主鍵或索引的順序進行結果集的輸出。TiDB 是分布式數據庫,數據被存儲在多臺服務器上,另外 TiDB 層不緩存數據頁,因此不含 order by 的 SQL 語句的結果集展現順序容易被感知到不穩定。想要按順序輸出的結果集,需 明確的把要排序的字段添加到 order by 子句中,這符合 SQL 的語義。1. GC 超時
TiDB的事務的實現采用了 MVCC(多版本并發控制)機制,當新寫入的數據覆蓋舊的數據時,舊的數據不會被替換掉,而是與新寫入的數據同時保留,并以時間戳來區分版本。TiDB 通過定期 GC 的機制來清理不再需要的舊數據。默認配置下 TiDB 可以保障每個 MVCC 版本(一致性快照)保存 10 分鐘,讀取時間超 過 10 分鐘的事務,會收到報錯GC life time is shorter than transaction duration 當用戶確信自己需要更長的讀取時間時,比如在使用了 Mydumper 做全量備份的場景 中(Mydumper 備份的是一致性的快照),可以通過調整 TiDB 中 mysql.tidb 表中的 tikv_gc_life_time 的值來調大 MVCC 版本保留時間,需要注意的是 tikv_gc_life_time 的配置是立刻影響全局的,調大它會為當前所有存在的快照增加生命時長,調小它會立即縮短所有快照的生命時長。過多的 MVCC 版本會拖慢 TiKV 的處理效率,在使用 Mydumper 做完全量備份 后需要及時把 tikv_gc_life_time 調整回之前的設置。2. 事務超時
含 DML 語句的事務,除了受tikv_gc_life_time 限制之外,還受到另外一個參數 max-txn-time-use 的影響,這個參數位于 tidb-server 的配置文件 tidb.toml 中,用于控制單個事 務允許的最大執行時間。該參數的默認值為 590(秒),需要注意必須控制該參數的值小于 tikv_gc_life_time 的值。形如 insert into t10 select * from t1 的 SQL 語句,即使執行時間沒有達到 tikv_gc_life_time 限制,但超過了 max-txn-time-use 的限制,會由于超時而回滾。3. SQL 超時
TiDB 還提供了一個系統變量來限制單條 SQL 語句的執行時間:max_execution_time, 它的默認值為 0,表示無限制。max_execution_time 目前對所有類型的 statement 生效,并非只 對 SELECT 語句生效。其單位為 ms,但實際精度在 100ms 級別,而非更準確的毫秒級別。1. TiDB 服務端兼容
TiDB 服務端兼容 MySQL 5.7,客戶端推薦使用 5.1.36 或更高版本 的 5.1.x jdbc 驅動。2. JDBC 參數設置
Java 應用常用的數據庫連接池包括 weblogic、c3p0、Druid等。使用連接池配置數據源 時,需要配置一系列參數,其中比較重要的包括 jdbc 的 url 配置,超時探活機制等。充分認識并理解各項參數有助于讓 TiDB 發揮出更高的性能。spring.datasource.url=JDBC:mysql://{TiDBIP}:
{TiDBPort}/{DBName}?characterEncoding=
utf8&useSSL=false&useServerPrepStmts=true&prepStmtCacheSqlLi
mit=10000000000&useCon
figs=maxPerformance&rewriteBatchedStatements=true&defaultfet
chsize=-214783648
序列是一種數據庫對象,應用程序通過調用某個序列可以產生遞增的序列值,應用程序可以靈活的使用這個序列值為一張表或多張表賦值,也可以使用序列值進行更復雜的加工,來實現文本和數字的組合,來賦予代理鍵以一定的跟蹤和分類的意義。TiDB 從 v4.0 版本開 始提供序列功能,詳情請參考官方文檔。CREATE [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[ INCREMENT [ BY | = ] increment ]
[ MINVALUE [=] minvalue | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] maxvalue | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] start ]
[ CACHE [=] cache | NOCACHE | NO CACHE]
[ CYCLE | NOCYCLE | NO CYCLE]
[ ORDER | NOORDER | NO ORDER]
[table_options]
參數 | 默認值
| 描述
|
TEMPORARY | FALSE | TiDB 暫時不支持 TEMPORARY 選項,僅在語法上做兼容。 |
INCREMENT | 1 | 指定序列的步長。其正負值可以控制序列的增長方向。 |
MINVALUE | 1 或 -9223372036854775807 | 指定序列的最小值。當 INCREMENT > 0 時,默認值為 1;當 INCREMENT < 0 時,默認值為 -9223372036854775807。 |
MAXVALUE | 9223372036854775806 或 -1 | 指定序列的最大值。當 INCREMENT > 0 時,默認值為 9223372036854775806;當 INCREMENT < 0 時,默認值為 -1。 |
START | MINVALUE 或 MAXVALUE | 指定序列的初始值。當 INCREMENT > 0 時,默認值為 MINVALUE; 當 INCREMENT < 0 時,默認值為 MAXVALUE。 |
CACHE | 1000 | 指定每個 TiDB 本地緩存序列的大小。 |
CYCLE | NO CYCLE | 指定序列用完之后是否要循環使用。在 CYCLE 的情況下,當 INCREMENT > 0 時,序列用完后的后續起始值為 MINVALUE;當 INCREMENT < 0 時,序列用完后的后續起始值為 MAXVALUE。 |
ORDER | NO ORDER | TiDB 暫時不支持 ORDER 選項,僅在語法上做兼容。 |
1. SEQUENCE函數
● NEXTVAL 或 NEXT VALUE FOR
本質上都是 nextval() 函數,獲取序列對象的下一個有效值,其參數為序列的 identifier。● LASTVAL
lastval() 函數,用于獲取本會話上一個使用過的值。如果沒有值,則為 NULL,其參數為序列的 identifier。● SETVAL
setval() 函數,用于設置序列的增長。其第一參數為序列的 identifier,第二個參數為 num。2. 示例
2.1. 創建一個默認參數的序列對象
CREATE SEQUENCE seq;
Query OK, 0 rows affected (0.06 sec)
2.2. 使用 nextval() 函數獲取序列對象的下一個值
SELECT nextval(seq);
+--------------+
| nextval(seq) |
+--------------+
| 1 |
+--------------+
1 row in set (0.02 sec)
2.3. 使用 lastval() 函數獲取本會話上一次調用序列對象所產生的值
SELECT lastval(seq);
+--------------+
| lastval(seq) |
+--------------+
| 1 |
+--------------+
row in set (0.02 sec)
2.4. 使用 setval() 函數設置序列對象當前值的位置
SELECT setval(seq, 10);
+-----------------+
| setval(seq, 10) |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.01 sec)
2.5. 使用 next value for 語法獲取序列的下一個值
SELECT next value for seq;
+--------------------+
| next value for seq |
+--------------------+
| 11 |
+--------------------+
1 row in set (0.00 sec)
1. ADD COLUMN
ALTER TABLE.. ADD COLUMN 語句用于在已有表中添加列。在 TiDB 中,ADD COLUMN 為在線操作,不會阻塞表中的數據讀寫。● 不支持將新添加的列設為 PRIMARY KEY;● 不支持將新添加的列設為 AUTO_INCREMENT。2. ADD INDEX
ALTER TABLE.. ADD INDEX 語句用于在已有表中添加一個索引。在 TiDB 中,ADD INDEX 為在線操作,不會阻塞表中的數據讀寫。● 不支持 FULLTEXT,HASH 和 SPATIAL 索引;● 不支持降序索引(類似于 MySQL 5.7);● 無法向表中添加 CLUSTERED 類型的 PRIMARY KEY。3. ALTER TABLE
3.1 ALTER TABLE 語句用于對已有表進行修改,以符合新表結構。ALTER TABLE 語句可用于:● ADD,DROP,MODIFY 或 CHANGE 列。3.2 TiDB 中的 ALTER TABLE 語法主要存在以下限制:● 不支持在單個 ALTER TABLE 語句中進行多個更改;● 不支持主鍵列上 Reorg-Data 類型的變更;● 不支持部分數據類型(例如,部分時間類型、Bit、Set、Enum、JSON 等)的變更,因為 TiDB 中的 CAST 函數與 MySQL 的行為存在兼容性問題;4. ALTER INDEX
ALTER INDEX 語句用于修改索引的可見性,可以將索引設置為 Visible 或者 Invisible。設置為 Invisible 的索引即不可見索引 (Invisible Index) 由 DML 語句維護,不會被查詢優化器使用。5. CHANGE COLUMN
ALTER TABLE.. CHANGE COLUMN 語句用于在已有表上更改列,包括對列進行重命名,和將數據改為兼容類型。從 v5.1.0 版本起,TiDB 開始支持 Reorg 數據的類型變更,包括但不限于:● 從 varchar(10) 到 varchar(5) 的長度壓縮。6. MySQL 兼容性
● 不支持在單個 ALTER TABLE 語句中進行多個更改;●不支持主鍵列上 [Reorg-Data](/sql-statements/sql-statement-modify-column.md#Reorg-Data Change) 類型的變更;● 不支持部分數據類型(例如,部分時間類型、Bit、Set、Enum、JSON 等)的變更,因為TiDB 中 CAST 函數與 MySQL 的行為存在兼容性問題。
本 文 原 創 來 源:IT那活兒微信公眾號(上海新炬王翦團隊)
文章版權歸作者所有,未經允許請勿轉載,若此文章存在違規行為,您可以聯系管理員刪除。
轉載請注明本文地址:http://specialneedsforspecialkids.com/yun/129680.html