背景介紹

今天方才就以財務系統的科目余額相關需求為例，給大家展示下在實際的企業(yè)級項目中，如何應用該方法論進行數據庫表結構設計。

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

image-本文目錄導航

步驟1：需求分析

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

先看方法論中關于需求分析的可選維度：

圖片

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

業(yè)務概念

相關的概念如下：

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

業(yè)務行為

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

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

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

圖片

相關數據量預估

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

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

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

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

步驟2：擬定技術方案

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

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

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

圖片

結合之前的業(yè)務分析，會計科目余額相關的依賴如下：

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

所以整個方案的關鍵就是：

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

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

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

方案的指標對比：

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

步驟3：表結構設計

有了確定的技術方案后，就進入到了完整的表結構設計階段。

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

圖片

先看完整的DDL

先簡單看下完整的表結構，然后我們再完整講解表結構設計一些技巧。

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'本期數-金額（借-貸，結合科目方向計算得到）',
`bqs_sl`decimal(22,2) NOTNULLDEFAULT'0.00'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',
  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;

數據庫范式設計

1. 三范式

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

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

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

2. 反范式設計

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

命名規(guī)范

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

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

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

必備字段

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

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

這個可以根據實際情況，自己去約定，比如方才建的表就有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），這個特性通常用于記錄數據最后被修改的時間。
• create_user_id 是varchar(32)類型，在此處是為了兼容歷史數據，一般情況也應該是  bigint(20) UNSIGNED。

字段選型

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

整數類型

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

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

按最小存儲長度原則：

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

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

小數類型（比如金額字段）

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

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

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

字符串類型

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

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

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

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

圖片

image-20250122151028028

1. 如果你需要存儲更大字節(jié)的內容，就可以使用``LONGTEXT 類型，最大列長度為 4,294,967,295 字節(jié)；或者 使用二進制大文件LONGBLOB` 類型，最大列長度為 4,294,967,295 字節(jié)。
2. 注意：對于字符串字段，若長度超過5000，就建議使用text類型，且獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率（后續(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 數據類型受 2038 年問題的影響。如果存儲的值大于 2038，需使用 DATETIME 類型。

b.時區(qū)的問題：當存儲 TIMESTAMP 時，MySQL 會將當前時區(qū)的 TIMESTAMP 值轉換為 UTC 時區(qū)。當讀取 TIMESTAMP 時，MySQL 將存儲的 TIMESTAMP 值從 UTC 時區(qū)轉換為當前時區(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 '更新時間',

邏輯刪除與物理刪除

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

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

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

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

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

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

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

字段個數

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

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

字符集的選擇

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

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

• latin1：MySQL 默認字符集，1 個字節(jié)長度，所以容易出現亂碼問題；
• 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ī)則會影響對字符類型的排序以及查詢，是非常重要的，一不小心就容易出現bug，且很難定位。

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

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

下面這個示例，通過COLLATE=utf8mb4_general_ci 和COLLATE=utf8mb4_bin分別設置不同的排序規(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語句設置，區(qū)分大小寫，確保數據的準確性和一致性。

步驟4：索引的設計

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

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

圖片

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

關于主鍵

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

常用的主鍵生成機制有：

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

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

-- 省略了無效內容
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數據庫，如果表沒有主鍵，TiDB 會自動生成一個隱式的 _tidb_rowid 列作為行 ID。這個列的值是單調遞增的。

索引創(chuàng)建依據

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

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

唯一索引

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

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

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

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

圖片

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

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

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

組合索引

關于組合索引，方才就提兩個技巧：

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

普通索引

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

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

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