背景介紹

今天方才就以財務(wù)系統(tǒng)的科目余額相關(guān)需求為例，給大家展示下在實際的企業(yè)級項目中，如何應(yīng)用該方法論進行數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計。

通過這個示例，我相信大家會發(fā)現(xiàn)業(yè)務(wù)需求分析和技術(shù)方案的設(shè)計才是表結(jié)構(gòu)設(shè)計的關(guān)鍵，最終關(guān)于表結(jié)構(gòu)和索引的設(shè)計是可以通過參考人家的經(jīng)驗貼快速掌握的，但業(yè)務(wù)分析能力和技術(shù)方案設(shè)計能力，需要長期的刻意練習以及在業(yè)務(wù)領(lǐng)域的深耕才能有所成就。

image-本文目錄導(dǎo)航

步驟1：需求分析

方才想再次強調(diào)一下：技術(shù)是為業(yè)務(wù)服務(wù)的，所以對業(yè)務(wù)需求的分析是最最最重要的，只有理解了需求，才有可能設(shè)計出合適的技術(shù)方案，從而設(shè)計出相對最優(yōu)的表結(jié)構(gòu)。

先看方法論中關(guān)于需求分析的可選維度：

圖片

接下來參考上圖，我們開始對財務(wù)系統(tǒng)的科目余額相關(guān)需求進行分析（考慮大家可能沒有接觸過財務(wù)系統(tǒng)，以下分析更多會用大白話解釋，而不是專業(yè)的業(yè)務(wù)概念去解釋）：

業(yè)務(wù)概念

相關(guān)的概念如下：

• 賬套：指一個獨立的會計核算單位的全部賬簿體系，用于記錄企業(yè)或組織的財務(wù)信息；
• 會計科目：可以簡單理解為就是一個具有層級關(guān)系的分類類目；
• 科目余額：是某個會計科目在某一特定會計期間上的金額，通常包括期初余額、本期增加額、本期減少額和期末余額；就好比個人銀行賬戶，按月記賬，會有一個月初余額、本月收入支出、月末余額一樣。
• 會計期間：記賬的時間周期，可以簡單理解為每個月就是一個會計期間，每年就有12個會計期間；
• 會計憑證：發(fā)生在會計科目上的財務(wù)行為記錄，就好比個人銀行賬戶的一個賬單記錄，記錄了收支雙方的信息、發(fā)生金額、備注等；
• 憑證分錄：多個憑證分錄組成一個完整的會計憑證，是具體發(fā)生在某個會計科目上的財務(wù)行為記錄；
• 會計賬簿：以科目余額和會計憑證數(shù)據(jù)為依據(jù)，形成的數(shù)據(jù)報表；
• 會計報表：以科目余額和會計憑證數(shù)據(jù)為依據(jù)，形成的數(shù)據(jù)報表；

業(yè)務(wù)行為

和會計科目余額相關(guān)的業(yè)務(wù)行為（其他無關(guān)的，方才就省略掉了）：

• 會計科目維護：國家對會計科目有制度約束，標準的會計科目大概有167個左右，同時允許在標準的會計科目下新增下級子科目；
• 科目初始化：用戶去維護記賬初期的數(shù)據(jù)；比如你要開始記賬，肯定需要先記錄你有哪些賬戶，每個賬戶的當前余額有多少；
• 憑證記錄：會實時影響到會計科目的余額；比如你給朋友轉(zhuǎn)賬了，那你的賬戶的余額會減少，你朋友賬戶的余額會增加；
• 會計賬簿&報表的查詢：按會計期間范圍，查詢對應(yīng)期間的科目余額數(shù)據(jù)，并按一定的規(guī)則計算得到的報表；
• 某個會計期間的科目余額：通常包括期初余額（等于上一期的期末）、本期發(fā)生額（根據(jù)憑證數(shù)據(jù)計算）和期末余額（根據(jù)期初和發(fā)生額計算）等等指標；

業(yè)務(wù)模型如下圖（ps：業(yè)務(wù)概念的分析，是為了后續(xù)的技術(shù)方案的設(shè)計）：

圖片

相關(guān)數(shù)據(jù)量預(yù)估

理解了業(yè)務(wù)概念和業(yè)務(wù)行為后，就需要對相關(guān)業(yè)務(wù)進行數(shù)據(jù)量預(yù)估，這樣在設(shè)計技術(shù)方案時，才能建立有效的評估指標，在本次分析的財務(wù)系統(tǒng)的會計科目余額相關(guān)的業(yè)務(wù)，核心的業(yè)務(wù)數(shù)據(jù)量情況如下：

• 賬套數(shù)：前期5-10w，后期每年100w左右的增量；
• 會計科目數(shù)：以單賬套平均300個會計科目為計算，每年在 300*100w = 3 億的增量；
• 憑證數(shù)：單賬套憑證總數(shù)平均為1000計算，每年在 1000*100w = 10 億的增量；
• 憑證分錄數(shù)：單個憑證按平均4條分錄計算，每年在 10億*4 =40 億的增量；
• 會計期間維度的科目余額數(shù)據(jù)：若按會計期間維度全量存儲，每年 3億會計科目*12個月 = 36億的增量（這個就是本文后續(xù)討論分析的點）；

ps：數(shù)據(jù)預(yù)估基礎(chǔ)指標來源是商務(wù)和業(yè)務(wù)資料的綜合分析得出，這里就不細說了。

考慮整體篇幅，后續(xù)方才就僅討論會計期間維度的科目余額的表結(jié)構(gòu)設(shè)計。所以只需要關(guān)注 會計科目數(shù) 10億 和 憑證分錄數(shù) 40億 這兩個指標了。

步驟2：擬定技術(shù)方案

前提說明：在這個量級的系統(tǒng)設(shè)計中，會先有業(yè)務(wù)架構(gòu)設(shè)計-》應(yīng)用架構(gòu)設(shè)計&數(shù)據(jù)架構(gòu)設(shè)計-》部署架構(gòu)設(shè)計，此處講述的技術(shù)方案屬于應(yīng)用架構(gòu)設(shè)計中代碼級別的設(shè)計。

本文的重點是講解表結(jié)構(gòu)設(shè)計，所以這里先明確一個前提：應(yīng)用架構(gòu)已經(jīng)設(shè)計完成，數(shù)據(jù)庫選型為分布式數(shù)據(jù)庫TiDB，不需要分庫分表（大家若對海量數(shù)據(jù)下分布式數(shù)據(jù)庫TiDB的實戰(zhàn)經(jīng)驗感興趣的，可以在評論區(qū)告訴方才喲）。

基于上面的前提條件，我們就直接開始擬定關(guān)于會計科目余額計算相關(guān)的技術(shù)方案了。整個思路如下：

圖片

結(jié)合之前的業(yè)務(wù)分析，會計科目余額相關(guān)的依賴如下：

1. 會計科目余額基于 科目初始化數(shù)據(jù)+會計憑證&分錄數(shù)據(jù)計算生成；
2. 會計報表&會計賬簿 是基于科目余額數(shù)據(jù)進行計算生成；等價替換下，也可以直接基于  科目初始化數(shù)據(jù)+會計憑證&分錄數(shù)據(jù)計算生成；

所以整個方案的關(guān)鍵就是：

1. 會計科目余額數(shù)據(jù)是否需要落庫；
2. 若需要落庫，那落庫的時機和落庫的指標字段有哪些；
3. 不同的方案，對應(yīng)的會計報表&會計賬簿生成邏輯是怎樣的。

經(jīng)過頭腦風暴，擬定3個可選方案（ps：這里更多是感知整個分析的過程，對于方案的具體的內(nèi)容和邏輯，方才這里做了省略）：

• 方案1：會計科目余額數(shù)據(jù)不落庫，只是一個邏輯概念，通過接口基于科目初始化數(shù)據(jù)+會計憑證&分錄數(shù)據(jù)實時計算生成。
• 方案2：會計科目余額數(shù)據(jù)按會計期間維度，在憑證數(shù)據(jù)更新時，記錄所有會計科目的所有指標；會計報表&賬簿的生成直接查詢需要的數(shù)據(jù)即可；
• 方案3：會計科目余額數(shù)據(jù)按會計期間維度僅保存憑證分錄的會計科目+本期發(fā)生額相關(guān)的指標，其他指標根據(jù) 科目初始化數(shù)據(jù) + 會計期間維度的本期發(fā)生額 計算得來；

方案的指標對比：

通過多維度對方案的對比，最終確定使用方案3進行落地實施（通過對比，可以感知到不同的方案，會計科目余額表的結(jié)構(gòu)是不一樣的，甚至都不需要有）。

步驟3：表結(jié)構(gòu)設(shè)計

有了確定的技術(shù)方案后，就進入到了完整的表結(jié)構(gòu)設(shè)計階段。

主要思路是參考數(shù)據(jù)庫范式&反范式設(shè)計，結(jié)合阿里巴巴規(guī)約，以及歷史經(jīng)驗的總結(jié)，完成從表名、字段名、字段類型的定義。

圖片

先看完整的DDL

先簡單看下完整的表結(jié)構(gòu)，然后我們再完整講解表結(jié)構(gòu)設(shè)計一些技巧。

CREATE TABLE`kjkm_fse` (
`id`bigint(20) UNSIGNEDNOTNULL AUTO_INCREMENT COMMENT'主鍵',
`zt_id`bigint(20) UNSIGNEDNOTNULLCOMMENT'賬套id',
`kjkm_id`bigint(20) UNSIGNEDNOTNULLCOMMENT'會計科目id',
`kjkm_bm`varchar(64) NOTNULLCOMMENT'會計科目編碼-冗余字段，便于查詢',
`kjqj_id`bigint(20) UNSIGNEDNOTNULLCOMMENT'會計期間id',
`ljjf_je`decimal(22,2) NOTNULLDEFAULT'0.00'COMMENT'本期借方發(fā)生額',
`ljdf_je`decimal(22,2) NOTNULLDEFAULT'0.00'COMMENT'本期貸方發(fā)生額',
`bqs_je`decimal(22,2) NOTNULLDEFAULT'0.00'COMMENT'本期數(shù)-金額（借-貸，結(jié)合科目方向計算得到）',
`bqs_sl`decimal(22,2) NOTNULLDEFAULT'0.00'COMMENT'本期數(shù)-數(shù)量',
`created_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時間',
`updated_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
`create_user_id`varchar(32) NOTNULLCOMMENT'創(chuàng)建人用戶id',
`update_user_id`varchar(32) NOTNULLCOMMENT'更新人用戶id',
  PRIMARY KEY (`id`,`zt_id`),
UNIQUEKEY`uk_zt_kjqj_kjkm_id` (`zt_id`,`kjqj_id`,`kjkm_id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1COMMENT='會計科目-發(fā)生額'
PARTITIONBYHASH (`zt_id`) PARTITIONS3;

數(shù)據(jù)庫范式設(shè)計

1. 三范式

a.第一范式（1NF）：確保表中的每一列都是不可再分的原子數(shù)據(jù)項。例如，ljjf_je（本期借方發(fā)生額）和ljdf_je（本期貸方發(fā)生額）分別獨立存儲，每一列都是不可分割的原子數(shù)據(jù)項。

b.第二范式（2NF）：在滿足1NF的基礎(chǔ)上，確保表中的非主屬性完全依賴于主鍵（這個主鍵大家可以理解為業(yè)務(wù)主鍵，而非ID）。通過將zt_id（賬套ID）、kjqj_id（會計期間ID）和kjkm_id（會計科目ID）組合成唯一鍵，確保了表中的數(shù)據(jù)與這些關(guān)鍵字段的強關(guān)聯(lián)性。

c.第三范式（3NF）：在滿足2NF的基礎(chǔ)上，確保表中的非主屬性之間不存在傳遞依賴關(guān)系。在上面的表里，冗余了 kjkm_bm 字段，不符合該范式。

2. 反范式設(shè)計

• 反范式設(shè)計（Denormalization）是數(shù)據(jù)庫設(shè)計中一種有意引入數(shù)據(jù)冗余的技術(shù)，旨在提高查詢性能。
• 注意點：反范式設(shè)計會增加數(shù)據(jù)存儲和數(shù)據(jù)的一致性維護成本。
• 適用于：冗余存儲的字段需要用于查詢，且更新頻率較低或是通過預(yù)計算得到的擴展字段等情況。
• 例如，本表中同時存儲了 kjkm_id（會計科目Id） 和 kjkm_bm（會計科目編碼）。這種設(shè)計雖然增加了存儲空間，但減少了查詢時的連表操作或計算成本，能提高查詢性能。
• 例如，本表的 bqs_je= ljjf_je - ljdf_je 是通過預(yù)計算得到的擴展字段，也是為了滿足查詢需求。

命名規(guī)范

包括表名和字段名，參考以下幾點：

1. 必須使用小寫字母或數(shù)字，使用下劃線分割；
2. 盡可能顧明思議，表字段的注釋要及時更新，特別是枚舉字段；
3. 禁用保留字，如 desc、range、match、delayed 等；
4. **索引命名規(guī)范，名稱前綴 uk_/ idx_**：唯一索引名為 uk_字段名；普通索引名則為 idx_字段名；

命名規(guī)范還是很好理解的，大家日常應(yīng)該是使用英語單詞更多點，財務(wù)這塊名詞太長，所以方才使用的是中文的首字母縮寫（只要整個庫保持一個風格，可讀性也是很高的）。

必備字段

參考阿里規(guī)約，建議表的必備字段有3個：id, create_time, update_time。

說明：其中 id 必為主鍵，類型為 bigint unsigned、單表時自增、步長為 1。create_time, update_time 的類型均為 datetime 類型，前者現(xiàn)在時表示主動式創(chuàng)建，后者過去分詞表示被動式更新。

這個可以根據(jù)實際情況，自己去約定，比如方才建的表就有5個必備的字段：

`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`created_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時間',
`updated_time` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新時間',
`create_user_id`varchar(32) NOTNULLCOMMENT'創(chuàng)建人用戶id',
`update_user_id`varchar(32) NOTNULLCOMMENT'更新人用戶id',

注意：

• updated_time 字段要有  ON UPDATE CURRENT_TIMESTAMP屬性，該屬性用于指定當表中的記錄被更新時，該字段的值會自動更新為當前的時間戳（CURRENT_TIMESTAMP），這個特性通常用于記錄數(shù)據(jù)最后被修改的時間。
• create_user_id 是varchar(32)類型，在此處是為了兼容歷史數(shù)據(jù)，一般情況也應(yīng)該是  bigint(20) UNSIGNED。

字段選型

字段類型要盡量和實際類型保持一致，然后優(yōu)先選擇最小的存儲長度類型即可。

整數(shù)類型

參考MySQL官網(wǎng)：https://dev.mysql.com/doc/refman/8.4/en/integer-types.html 。

不同整數(shù)類型的存儲大小和取值范圍：

按最小存儲長度原則：

• 枚舉類型的字段一般使用 TINYINT；
• 注意字段可能的上下限，避免溢出，比如國民級軟件的點贊數(shù)這種，就建議使用BIGINT（上次抖音出現(xiàn)點贊數(shù)為負數(shù)，就是因為 int溢出了）；
• 表的主鍵，明確數(shù)據(jù)量會持續(xù)增加，且無上限，就建議使用BIGINT；若是字典維護表這種內(nèi)部使用的，主鍵就可以用 INT。

補充：關(guān)于整數(shù)類型在DDL中定義時括號中的數(shù)字的含義是顯示寬度：比如 int(11) 中的 (11) 是一個 顯示寬度（主要用于 ZEROFILL 選項時，指定數(shù)字顯示時前面填充的零的數(shù)量），而不是數(shù)據(jù)類型的實際存儲長度或精度，整數(shù)類型的存儲長度是固定了的，int類型的儲空間始終是 4 字節(jié)。

小數(shù)類型（比如金額字段）

對于小數(shù)類型，建議遵循阿里巴巴規(guī)約，使用decimal類型（如decimal(22,2)），禁止使用float和double，以確保數(shù)據(jù)的精確性。

注意： DECIMAL（5,2） 表示能存儲任何具有 5 位數(shù)字和 2 位小數(shù)的值，值范圍為 -999.99 到 999.99。

當然對于金額字段的存儲，一般有兩種方案，一種是使用 decimal類型 保留2位小數(shù)，單位一般為元及以上；另一種就是使用BIGINT類型，將單位轉(zhuǎn)為分，進行存儲。

字符串類型

MySQL中字符串類型，包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET。這里方才就只講幾個重點了：

1. CHAR(M) 是定長字符串。CHAR 列的長度固定為創(chuàng)建表時聲明的長度。M 表示列長度（是字符的個數(shù)，不是字節(jié)的個數(shù)），當存儲字符長度不足M時，會用空格右填充到指定長度。適用于存儲枚舉code等場景。
2. VARCHAR 是變長字符串。M 表示最大列長度（字符的最大個數(shù)）。VARCHAR 的空間占用大小不得超過 65535 字節(jié)。在選擇 VARCHAR 長度時，應(yīng)當根據(jù)最長的行的大小和使用的字符集確定。是最常用的字符串類型。

• 下表是單個字符占用的字節(jié)數(shù)，以及 VARCHAR 列長度的取值范圍（關(guān)于字符集后續(xù)會講）：

也就是說字符集為utf8mb4的 varchar類型的最大長度只能是 16383，若超過，你建表時會報錯：

圖片

image-20250122151028028

1. 如果你需要存儲更大字節(jié)的內(nèi)容，就可以使用``LONGTEXT 類型，最大列長度為 4,294,967,295 字節(jié)；或者 使用二進制大文件LONGBLOB` 類型，最大列長度為 4,294,967,295 字節(jié)。
2. 注意：對于字符串字段，若長度超過5000，就建議使用text類型，且獨立出來一張表，用主鍵來對應(yīng)，避免影響其它字段索引效率（后續(xù)講解了聚簇索引就可以理解為什么了）。

在我們當前這個示例中，使用varchar即可：

`kjkm_bm` varchar(64) NOT NULL COMMENT '會計科目編碼-冗余字段，便于查詢',

日期和時間類型

MySQL 的日期和時間類型，包括 DATE、TIME、DATETIME、TIMESTAMP 以及 YEAR。

• DATE 類型只包含日期部分，不包含時間部分。DATE 類型的格式為 YYYY-MM-DD，支持的范圍是 0000-01-01 到 9999-12-31。
• TIME 類型的格式為 HH:MM:SS[.fraction]，支持的范圍是 -838:59:59.000000 到 838:59:59.000000。
• DATETIME 類型是日期和時間的組合，格式為 YYYY-MM-DD HH:MM:SS[.fraction]。支持的范圍是 0000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999。
• TIMESTAMP 類型是日期和時間的組合，支持的范圍是 UTC 時間從 1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999。注意：

a.范圍上限問題：TIMESTAMP 數(shù)據(jù)類型受 2038 年問題的影響。如果存儲的值大于 2038，需使用 DATETIME 類型。

b.時區(qū)的問題：當存儲 TIMESTAMP 時，MySQL 會將當前時區(qū)的 TIMESTAMP 值轉(zhuǎn)換為 UTC 時區(qū)。當讀取 TIMESTAMP 時，MySQL 將存儲的 TIMESTAMP 值從 UTC 時區(qū)轉(zhuǎn)換為當前時區(qū)（DATETIME` 不會這樣處理）。

• YEAR 類型的格式為 YYYY，支持的值范圍是 1901 到 2155，也支持零值 0000。

所以，方才建議，日期和時間類型字段，優(yōu)先選用DATETIME，同時要合理利用其自動初始化或更新為當前時間的特性，比如說創(chuàng)建時間和更新時間：

`created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',

邏輯刪除與物理刪除

什么是物理刪除？什么是邏輯刪除？

• 物理刪除：把數(shù)據(jù)從硬盤中刪除，可釋放存儲空間
• 邏輯刪除：給數(shù)據(jù)添加一個字段，比如is_deleted，以標記該數(shù)據(jù)已經(jīng)邏輯刪除。

根據(jù)方才的經(jīng)驗，建議是優(yōu)先采用邏輯刪除。更方便去最終一些問題或者手動回滾數(shù)據(jù)等。

但若滿足以下場景，是更適合使用物理刪除的：

• 目標表的數(shù)據(jù)量較高，比如超過500萬；
• 且刪除操作頻繁，導(dǎo)致被刪除的數(shù)據(jù)占比較高，比如超過 1/10；
• 建議：對于核心業(yè)務(wù)數(shù)據(jù)，且無法通過其他數(shù)據(jù)派生而來，可以將刪除的數(shù)據(jù)插入到額外的表中，用做備份。

這個場景下，無用的數(shù)據(jù)太多，會影響到查詢和更新的效率了。

而剛好，會計科目余額表就符合這個場景，數(shù)據(jù)量超10億，憑證更新會導(dǎo)致數(shù)據(jù)的頻繁覆蓋寫入，同時本身數(shù)據(jù)就是可以通過期初數(shù)據(jù)+憑證數(shù)據(jù)計算得來，所以可以看到kjkm_fse這個表是沒有is_deleted字段的。

字段個數(shù)

個人建議表中字段盡量不超過20個，最多不超過50個。

理由是：因為MySQL的聚簇索引特征，過多的字段會導(dǎo)致回表操作成本過高，影響查詢性能。

字符集的選擇

字符集影響的是字符串類型的存儲，包括能否存儲以及一個字符對應(yīng)的字節(jié)長度。

MySQL 支持的字符集有utf8、utf8mb4、GBK、latin1等。

• latin1：MySQL 默認字符集，1 個字節(jié)長度，所以容易出現(xiàn)亂碼問題；
• GBK ：支持中文，但是不支持國際通用字符集，2 個字節(jié)長度；
• utf8：支持中英文混合場景，國際通過，3 個字節(jié)長度；
• utf8mb4: 完全兼容 utf8，4 個字節(jié)長度，可存儲更多的字符；

方才推薦字符集優(yōu)先選擇utf8mb4，支持更廣泛的字符集范圍，通過建表語句 CHARSET=utf8mb4可以指定。

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT='會計科目-發(fā)生額'
PARTITION BY HASH (`zt_id`) PARTITIONS 3;

排序規(guī)則的選擇

排序規(guī)則會影響對字符類型的排序以及查詢，是非常重要的，一不小心就容易出現(xiàn)bug，且很難定位。

一個字符集可以有多種排序規(guī)則。排序規(guī)則的命名格式為 <character_set>_<collation_properties>。例如，utf8mb4 字符集有一個名為 utf8mb4_bin 的排序規(guī)則，它是 utf8mb4 字符集的二進制排序規(guī)則。下表是常見字符集和排序規(guī)則的后綴和含義：

簡單看兩個示例就會理解深刻了。

下面這個示例，通過COLLATE=utf8mb4_general_ci 和COLLATE=utf8mb4_bin分別設(shè)置不同的排序規(guī)則：

CREATE TABLE`fc_test` (
`id`bigint(20) UNSIGNEDNOTNULL AUTO_INCREMENT COMMENT'主鍵',
`mc`varchar(64) NOTNULLCOMMENT'名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='不區(qū)分大小的測試';

INSERTINTO fc_test( mc) VALUES('fangcaicoding'),('Fangcaicoding'),('cdoing');


CREATETABLE`fc_test2` (
`id`bigint(20) UNSIGNEDNOTNULL AUTO_INCREMENT COMMENT'主鍵',
`mc`varchar(64) NOTNULLCOMMENT'名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='區(qū)分大小的測試';

INSERTINTO fc_test2
( mc)
VALUES('fangcaicoding'),('Fangcaicoding'),('cdoing');

查詢和排序下：

-- 不區(qū)分大小寫
select * from fc_test where mc = "Fangcaicoding" order by mc;
select * from fc_test order by mc desc;
-- 區(qū)分大小寫
select * from fc_test2 where mc = "Fangcaicoding" order by mc;
select * from fc_test order by mc desc;

運行截圖如下：

圖片

通過這個示例，我想大家都一定理解了字符集的排序規(guī)則的影響點了。

一般情況，方才推薦排序規(guī)則優(yōu)先選擇utf8mb4_bin，通過COLLATE=utf8mb4_bin語句設(shè)置，區(qū)分大小寫，確保數(shù)據(jù)的準確性和一致性。

步驟4：索引的設(shè)計

關(guān)于索引，方才發(fā)現(xiàn)很多初中級程序員都沒有形成一個方法論。很容易走兩個極端，要么是除了主鍵沒有其他任何索引，要么就是索引一大堆。

方才結(jié)合自己針對數(shù)十億表的索引優(yōu)化經(jīng)驗，總結(jié)如下：

圖片

結(jié)合上面的腦圖，針對kjkm_fse這個表，我們來一一分析下。

關(guān)于主鍵

方才建議所有的表均應(yīng)該有主鍵，優(yōu)先為數(shù)字類型，且保持自增性（若是輔助表，主鍵可以直接使用主表的）。

常用的主鍵生成機制有：

• 數(shù)據(jù)庫自增 auto_increment ；
• 基于外部算法代碼實現(xiàn)：比如雪花算法、百度Uid-Generator、美團Leaf等；

在kjkm_fse表是有自增id的，但因為數(shù)據(jù)量較大，使用了分區(qū)表（關(guān)于TiDB的分區(qū)表，大家若有興趣，可以在評論區(qū)告訴方才喲），分區(qū)字段按規(guī)范需要作為主鍵的組合字段之一，所以該表的主鍵如下：

-- 省略了無效內(nèi)容
CREATE TABLE `kjkm_fse` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵'
  PRIMARY KEY (`id`,`zt_id`)
) PARTITION BY HASH (`zt_id`) PARTITIONS 3;

補充：如果一個表沒有顯示定義主鍵：

• MySQL的 InnoDB 引擎會嘗試使用第一個非空的唯一索引（NOT NULL 和 UNIQUE INDEX）作為聚簇索引。如果沒有合適的唯一索引，InnoDB 會自動生成一個隱藏的 ROW_ID 列作為聚簇索引，這個隱藏列是遞增的。
• TiDB數(shù)據(jù)庫，如果表沒有主鍵，TiDB 會自動生成一個隱式的 _tidb_rowid 列作為行 ID。這個列的值是單調(diào)遞增的。

索引創(chuàng)建依據(jù)

除了主鍵建議均有，其他索引的創(chuàng)建，是需要跟進實際情況進行判斷的，方才總結(jié)規(guī)則如下：

1. 前提：目標表的數(shù)據(jù)量級會超過10萬；
2. 對唯一性要求的字段；
3. 必要的查詢字段；
4. 索引不是越多越好，過多的索引，會影響數(shù)據(jù)更新效率，同時會導(dǎo)致sql的自動優(yōu)化出現(xiàn)非預(yù)期行為；
5. 注意不是所有查詢字段均需要：若可以通過其他必傳查詢字段，使得過濾后的數(shù)據(jù)量級小于10萬，就可以不用創(chuàng)建；

唯一索引

方才建議，業(yè)務(wù)上有唯一特性的字段，必須建唯一索引或組合唯一索引：

理由：唯一鍵對 insert、update的性能損耗較小，對查詢速度的提升是很明顯的；同時根據(jù)墨菲定律，比如會產(chǎn)生臟數(shù)據(jù)

一定要注意：組合唯一鍵的所有字段均不能為空，否則可能導(dǎo)致唯一鍵約束失效。

• 原因：根據(jù)MySQL官方文檔，NULL表示“缺失的未知值”，它與任何其他值（包括另一個NULL值）進行比較時都不會返回真值。這種特性導(dǎo)致了NULL在唯一性約束中的特殊行為。
• 效果直接看下面的示例截圖就知道了，uk_typeId_mc2 是沒有鎖住的，數(shù)據(jù)1-3-8在邏輯上都是重復(fù)的：

圖片

回到kjkm_fse這個表，在業(yè)務(wù)上，就要求一個賬套下，一個會計期間，同一個科目的余額一定是只能有一條數(shù)據(jù)，所以就創(chuàng)建了組合唯一索引：

UNIQUE KEY `uk_zt_kjqj_kjkm_id` (`zt_id`,`kjqj_id`,`kjkm_id`)

ps：業(yè)務(wù)上是有根據(jù) kjkm_bm會計科目編碼查詢需求的，但這里方才并沒有針對該字段創(chuàng)建索引，是因為什么呢？可以參考索引的創(chuàng)建依據(jù)說明，業(yè)務(wù)上明確所有的查詢，一定都會攜帶zt_id賬套id參數(shù)，根據(jù)該參數(shù)，已經(jīng)可以將數(shù)據(jù)量過濾至5000以內(nèi)了，就沒有必要再創(chuàng)建了。

組合索引

關(guān)于組合索引，方才就提兩個技巧：

• 盡量將區(qū)分度高的字段放在前面（后續(xù)分享了B+樹索引后，就可以理解了）；
• 高頻查詢語句，可創(chuàng)建組合索引，利用索引覆蓋機制優(yōu)化sql性能。

普通索引

關(guān)于普通索引的創(chuàng)建，就優(yōu)先參考上面的內(nèi)容，有必要再創(chuàng)建。

需要注意的是，參考阿里規(guī)約，在 varchar 字段上建立索引時，必須指定索引長度，沒必要對全字段建立索引，根據(jù)實際文本區(qū)分度決定索引長度。（防止字段太長，索引內(nèi)容過大，導(dǎo)致其他問題）。

說明：索引的長度與區(qū)分度是一對矛盾體，一般對字符串類型數(shù)據(jù)，長度為 20 的索引，區(qū)分度會高達 90%以上，可以使用 count(distinct left(列名, 索引長度))/count(*)的區(qū)分度來確定。