TiDB生産環境使用必須遵守的開發規範

（關注“資料庫架構師”公衆号，提升資料庫技能，助力職業發展）

庫表命名規範

1. 表名規範
2. 表名小寫，禁止駝峰，比如 ad_Audit,jobSeq 等等，過長的可以用下劃線（_）分割
3. 字段名規範 禁止使用 mysql 的關鍵字，比如 order，group、show、slave 等
4. 詳見mysql官網：https://dev.mysql.com/doc/refman/8.0/en/keywords.html
5. 索引命名規範
6. 普通索引：idx_開頭，唯一索引：uniq_開頭，簡寫/縮寫，簡明扼要。 舉例說明：給corp_id,corp_name這2個字段加聯合索引，普通索引為：idx_corpid_name，唯一索引：uniq_corpid_name 不建議：idx_corp_id_corp_name

注：庫名、表名、字段名、索引名都小寫，長度都限制在64字元以内（TiDB限制）

表結構設計規範

1、TiDB 表主鍵

每張表一定要有一個主鍵，跟 MySQL 建表不同，主鍵不一定是 int/bigint 自增，如果有寫入性能問題更不見建議使用自增主鍵（寫熱點），可以使用 UUID、字元串、聯合字段來做主鍵時需要在建表語句後面添加下面2個參數來打散 region：

SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS=3           

另外對于寫入量大的可以使用 auto random 主鍵來提升寫入性能，如下

id bigint PRIMARY KEY AUTO_RANDOM。           

2、必須标注表和字段的 comment

比如：`mobile` varchar(20) DEFAULT NULL COMMENT '聯系手機’           

3、建表時提供表示建立時間和更新時間的 createdat updatedat 字段，并使用 mysql 内建的 CURRENT_TIMESTAMP 作為預設值，數倉的增量資料抽取依賴這2個字段。

4、字段能定義為非空的就定義為非空

比如：user_name varchar(20) not null default ‘’ comment ‘使用者名’

       uid int(10) not null default ‘0’ comment ‘使用者id’

      注意：text類型必須default null           

5、字段設定了 NOT NULL 的，一定要指定預設值，否則字段寫入時肯定報錯。

6、對于内容類字段優先考慮使用 utf8mb4 編碼以支援 emoji 表情文字，如果預期資料量較大，盡量将内容較長且不用于查詢的 BLOB、TEXT 列單獨建表。

7、關于分區表使用，一般日志類、報表類業務都喜歡用基于時間的 range 分區表(可以用)，Hash 分區（用的少）可以用于大量寫入場景下的資料打散，List 分區（5.X版本才有，實驗特性，慎用！）

為啥用分區表？drop/truncate partition這種快速清理資料不比delete from 大事務報錯香？？

    分區表的限制：4.0版本最多支援1024個分區，5.X版本支援8192個分區。           

8、字段類型選擇，目的：合适的類型，合适的大小

(1)能用 tinyint 不用 int，why？

     tinyint 能存-128~127，對于一些 status\type\gender等 業務字段完全夠用

   (2)能用數值類型不用 varchar

     比如存手機号 bigint 就夠了，存 ip 使用 int 類型來存。

   (3)字元串類型選擇，char 還是 varchar ？

      定長用 char，比如像固定的 open_id char(32)，jid char(36)，cid char(36)，md5值

      變長用 varchar，比如 name varchar(40)

   (4)對于字元串類型長度夠用即可。

      比如存 major，有的人用表結構生成器，表字段一水的 varchar(255)，其實varchar(50)足夠

   (5)盡量不用 TEXT 類型（能用varchar(10000)也不要用text）
      需要強調：mediumtext 在 mysql 能支援最大 16M 的單行資料，tidb 因為 KV size 的限制，隻能支援到 6M，超過這個 size 寫入報錯。如果必須要用，那就将涉及 text 的字段獨立成表。           

SQL 使用規範

1、TIDB 索引使用

• 聯合索引使用：如果線上存在複合條件查詢，務必通過複合索引，如果 SQL 查詢的字段以及 where 條件覆寫到查詢中的所有條件字段形成覆寫索引的話，性能更佳。
• 關于聯合索引使用的問題：（A，B，C）的聯合索引建立的情況，下面的SQL都可以使用到 （1）where A=xxx （2）where A=XXX and B=xxx （3）where A=XXX and B=xxx and C=xxx PS：經常遇到有了（A，B，C）還單獨建立A或者A+B聯合索引的，這樣就屬于重複索引
• 務必将 ORDER BY 中的列覆寫在索引中，不然很容易出現對性能影響sort。
• 不推薦建立過多的索引，禁止備援的索引、不使用的索引需要及時删除。推薦擴充現有索引，而不是建立新的索引。過多的索引容易影響優化器決策而形成嚴重性能問題。1)單張表中索引數量不超過5個；
• 2)單個索引中的字段數不超過5個； 3)對長字元串使用字首索引，如：char(100)； 4) 對區分度較低（重複值很多）的字段一般不建立單獨索引，如：type字段(取值隻有幾個)

2、SQL 語句編寫規範

• 避免使用select *，就算要用所有的表字段也建議都列出來，因為如果程式沒有table字段對應關系的配置，表的字段增加删除都會導緻業務取到的結果有問題，另外隻查自己想要的字段也能降低SQL執行時間中的網絡傳輸時間（可以拿帶text類型的表對比測試）。
• 禁止執行沒有where條件的表select/DML
• 避免在查詢中使用 OR，OR兩邊的條件都需要有索引并且會産生會使用到性能較差的index merge
• 對于核心的OLTP業務，線上不建議使用 JOIN 操作，有可能引發叢集抖動。
• 對于一些重要資料的“删除”，不推薦使用 DELETE，對于内容類資料優先考慮update軟删除。
• 推薦Batch insert，根據表字段的情況，batch size控制在一定的數量，不建議太多(事務過大，引發性能問題或者報錯)。
• DML SQL要避免TiDB的大事務限制(單KV：6M，預設事務100M可調)
• 業務RD喜歡begin；多個DML SQL；commit；在樂觀事務的情況下，預設隻支援5000條DML，可以通過stmt-count-limit調整。另外也不建議多DML SQL一次commit這種方式寫入資料。
• TIDB的DDL不支援多列操作，是以：alter table不支援添加多個字段、多個索引。

最後強調下：禁止RD直連線上DB進行SQL操作(如果是DBA，肯定在職業經曆中碰到過RD誤删除要恢複的事故)，公司需要提供自研 or 開源的SQL稽核和執行平台來解決問題。

3、不能用到索引的6種情況

(1)字元串轉義
      隻在于表中是varchar、char字元串類型，執行時指派為數值類型
  (2)函數包含
     各種函數比如常用的date函數，date_add,date_sub等等
     錯誤的方式：
     explain select  * from tb_dxl_test where date(update_date)='2016-10-06';
  (3)運算
    比如select * from t1 where start-end=10
  (4)Like ‘%dai’ / like ‘%dai%’ ,即最左的模糊比對
      錯誤的方式：
       explain select  * from tb_dxl_test where name like '%好';
      正确的方式：
      explain select  * from tb_dxl_test where name like '代%';
  (5)對比對度底的字段建立索引，也可能用不到
       比如一個type類型取值隻有0、1
  (6)隐式轉換
             表中字段是varchar/char字元串類型，業務将“數值”存入，然後基于數值查詢
             explain select  * from tb_dxl_test where self_numb=110           

TIDB與MySQL相容性差別

推進大容量 mysql 或者分庫分表業務遷移 TiDB 本來是好事兒，但是還是需要将相容性差別也列入到 TIDB 規範中，這樣業務會提前了解并對自己業務進行修改。

1、TiDB 的自增 id 不連續，存在 id 為1/30001/60001的資料都是同一時刻寫入的，是以業務基于id order by 的規則需要調整為基于時間排序。

2、不支援外鍵、存儲過程、觸發器、全文索引等

3、排序規則不同( collation 是在字元集中比較字元以及字元排序順序的規則）。在預設的二進制排序規則( utf8mb4_bin )中，比較 A 和 a 的結果是不一樣的，mysql的排序規則是：utf8mb4_general_ci，where str=‘A’跟‘a’都能查到相同的結果,TiDB 在4.0的高版本和5.X支援了大小寫不區分的排序規則，建立表時需要“顯示”設定排序規則。

4、再次強調：TiDB 不能在單條 ALTER TABLE 語句中完成多字段操作。例如，不能在單個語句中添加多個列或索引，否則，可能會輸出 Unsupported multi schema change 的錯誤;這個在使用基于 mysql 的稽核平台時會經常遇到，需要修改平台進行相容。

5、4.0 的 TiDB 不支援添加/删除主鍵，除非開啟了 alter-primary-key 配置項;

6、不支援将字段類型修改為其超集，例如不支援從 INTEGER 修改為 VARCHAR，或者從 TIMESTAMP 修改為 DATETIME

7、更改/修改資料類型時，不支援“有損更改”，比如bigint→int，varchar(200)→varchar(100)

8、TIDB的事務限制，單kv最大支援6M，也就是說mysql表中mediumtext類型(最大支援16M)遷移到tidb時可能會因為記錄過大而寫入失敗。

9、TIDB預設支援 100M size 的事務，這個預設值可以通過配置檔案中的配置項 txn-total-size-limit 進行修改，最大支援 10 GB 的事務。

更詳細的相容性差別，詳見官網連結 https://docs.pingcap.com/zh/tidb/stable/tidb-limitations

備注：本文參考自TiDB 社群使用者文章

如果這篇文章對你有幫助，還請幫忙點贊、轉發 以下，你的支援會激勵我們輸出更多高品質的文章！

如果你還想看更多優質文章，歡迎關注我的公衆号「資料庫架構師」，提升資料庫技能，助力職業發展。