作為一個寫業(yè)務代碼的 "JAVA CURD BOY" ，具備寫出高效率SQL讓應用高性能訪問數(shù)據(jù)庫的能力非常重要。獲得這個能力的過程我收獲了點知識和經(jīng)驗，今天在這里分享出來，希望大家多多交流指點。

本文內(nèi)容主要包括以下幾個方面：分析查詢SQL，MySQL查詢優(yōu)化器、數(shù)據(jù)庫存儲結(jié)構(gòu)、索引，索引維護，索引設計，SQL優(yōu)化，表結(jié)構(gòu)設計，分庫分表，查詢功能架構(gòu)設計。

(資料圖片僅供參考)

分析查詢SQL

MySQL提供了一個性能分析工具 EXPLAIN ，它可以幫助我們了解SQL語句的執(zhí)行計劃，分析查詢效率低下的原因（eg:是否使用索引，是否做全表掃描...），進而有針對性地對SQL進行優(yōu)化。

EXPLAIN 使用起來很簡單，在你的SQL查詢語句前加上它就可以了。示例如下圖所示：

EXPLAIN 命令執(zhí)行之后，顯示的結(jié)果一共有12列，這里我簡單說一下各個參數(shù)的意思：

1. id：是一個查詢序列號。

2. select_type：查詢類型。

3. table：表示與查詢結(jié)果相關的表的名稱。

4. partition：表示查詢訪問的分區(qū)。

5. key：表示優(yōu)化器最終決定使用的索引是什么。

6. key_len：表示優(yōu)化器選擇的索引字段按字節(jié)計算的長度。如果沒有使用索引，這個值就是空。

7. ref：列與索引的比較。

8. rows：表示為了得到查詢結(jié)果，必須掃描多少行記錄。

9. filtered：表示查詢篩選出的記錄占全部表記錄數(shù)的百分比。

10. possible_key：表示查詢時可能使用的索引。如果這里的值是空，就說明沒有合適的索引可用。

11. Extra：表示MySQL執(zhí)行查詢中的附加信息。

12. type：表的連接類型。

我們還可以通過命令 EXPLAIN的FORMAT=json 和 FORMAT=tree 來查看SQL 執(zhí)行成本。

EXPLAIN FORMAT=json SELECT * FROM USER_TASK_STATUS_LOG WHERE userId = "1" and createTime = "2023-01-13 20:46:27";{  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "409960.06"    },    "table": {      "table_name": "USER_TASK_STATUS_LOG",      "access_type": "ALL",      "rows_examined_per_scan": 3990545,      "rows_produced_per_join": 39905,      "filtered": "1.00",      "cost_info": {        "read_cost": "405969.52",        "eval_cost": "3990.55",        "prefix_cost": "409960.06",        "data_read_per_join": "11M"      },      "used_columns": [        "id",        "userId",        "taskStatus",        "createTime",        "updateTime"      ],      "attached_condition": "((`ds`.`user_task_status_log`.`createTime` = TIMESTAMP"2023-01-13 20:46:27") and (`ds`.`user_task_status_log`.`userId` = "1"))"    }  }

從第 14 行開始，其中：

read_cost 表示就是從 InnoDB 存儲引擎讀取的開銷；eval_cost 表示 Server 層的 CPU 成本；prefix_cost 表示這條 SQL 的總成本；data_read_per_join 表示總的讀取記錄的字節(jié)數(shù)。

如果你不了解這些參數(shù)的意義，或者說不清楚MySQL為什么要計算這些執(zhí)行開銷，那么接著往下看MySQL查詢優(yōu)化器。

查詢優(yōu)化器

先上一張MySQL執(zhí)行過程圖，我們來看看查詢優(yōu)化器在MySQL執(zhí)行過程中的位置。

從上圖可以看出，MySQL 數(shù)據(jù)庫由 Server層和 Engine層兩部分

Server 層負責“邏輯處理”，包括連接器、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務功能，以及所有的內(nèi)置函數(shù)（如日期、時間、數(shù)學和加密函數(shù)等），所有跨存儲引擎的功能都在這一層實現(xiàn)，比如存儲過程、觸發(fā)器、視圖等。

而存儲引擎層負責數(shù)據(jù)的存儲和提取。可插拔式架構(gòu)，支持 InnoDB、MyISAM、Memory 等多個存儲引擎。現(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。

查詢優(yōu)化器的兩種優(yōu)化方式

查詢優(yōu)化器的目標是生成最佳的執(zhí)行計劃，而生成最佳執(zhí)行計劃的策略通常有以下兩種方式。

第一種是基于規(guī)則的優(yōu)化器（RBO，Rule-Based Optimizer），規(guī)則就是人們以往的經(jīng)驗，或者是采用已經(jīng)被證明是有效的方式。通過在優(yōu)化器里面嵌入規(guī)則，對查詢SQL進行重寫。例如，對條件表達式進行等價謂詞重寫、條件簡化，對視圖進行重寫，對子查詢進行優(yōu)化，對連接語義進行了外連接消除、嵌套連接消除等。

第二種是基于代價的優(yōu)化器（CBO，Cost-Based Optimizer，基于成本的優(yōu)化器），SQL 優(yōu)化器會分析所有可能的執(zhí)行計劃，選擇成本最低的執(zhí)行，

在 MySQL中，一條 SQL 的計算成本計算如下所示：

Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

CPU Cost 表示計算的開銷，比如索引鍵值的比較、記錄值的比較、結(jié)果集的排序等等。

IO Cost 表示引擎層 IO 的開銷，MySQL 8.0 可以通過區(qū)分一張表的數(shù)據(jù)是否在內(nèi)存中，分別計算讀取內(nèi)存 IO 開銷以及讀取磁盤 IO 的開銷。

Server Cost 和 Engine Cost 則記錄了對于各種成本的計算。

Server Cost 記錄了 Server 層優(yōu)化器各種操作的成本。

Engine Cost 記錄了存儲引擎層各種操作的成本。

如果想深入了解這里有篇論文可以幫到你。《Access Path Selection-in a Relational Database Management System》

數(shù)據(jù)庫存儲結(jié)構(gòu)

數(shù)據(jù)庫中的存儲結(jié)構(gòu)是怎樣的

記錄是按照行來存儲的，但是數(shù)據(jù)庫的讀取并不以行為單位，否則一次讀取（也就是一次I/O操作）只能處理一行數(shù)據(jù)，效率會非常低。因此InnoDB將數(shù)據(jù)劃分為若干個頁面，不論讀一行，還是讀多行，都是將這些行所在的頁進行加載。也就是說，數(shù)據(jù)庫管理存儲空間的基本單位是頁（Page）。

一個頁中可以存儲多個行記錄（Row），同時在數(shù)據(jù)庫中，還存在著區(qū)（Extent）、段（Segment）和表空間（Tablespace）。行、頁、區(qū)、段、表空間的關系如下圖所示：

區(qū)（Extent）是比頁大一級的存儲結(jié)構(gòu)，在InnoDB存儲引擎中，一個區(qū)會分配64個連續(xù)的頁。因為InnoDB中的頁大小默認是16KB，所以一個區(qū)的大小是64*16KB=1MB。

段（Segment）由一個或多個區(qū)組成，區(qū)在文件系統(tǒng)是一個連續(xù)分配的空間（在InnoDB中是連續(xù)的64個頁），不過在段中不要求區(qū)與區(qū)之間是相鄰的。段是數(shù)據(jù)庫中的分配單位，不同類型的數(shù)據(jù)庫對象以不同的段形式存在。當我們創(chuàng)建數(shù)據(jù)表、索引的時候，就會相應創(chuàng)建對應的段，比如創(chuàng)建一張表時會創(chuàng)建一個表段，創(chuàng)建一個索引時會創(chuàng)建一個索引段。

表空間（Tablespace）是一個邏輯容器，表空間存儲的對象是段，在一個表空間中可以有一個或多個段，但是一個段只能屬于一個表空間。數(shù)據(jù)庫由一個或多個表空間組成，表空間從管理上可以劃分為系統(tǒng)表空間、用戶表空間、撤銷表空間、臨時表空間等。

數(shù)據(jù)頁內(nèi)的結(jié)構(gòu)是怎樣的

頁（Page）如果按類型劃分的話，常見的有數(shù)據(jù)頁（保存B+樹節(jié)點）、系統(tǒng)頁、Undo頁和事務數(shù)據(jù)頁等。數(shù)據(jù)頁是我們最常使用的頁。

數(shù)據(jù)頁包括七個部分，分別是文件頭（File Header）、頁頭（Page Header）、最大最小記錄（Infimum+supremum）、用戶記錄（User Records）、空閑空間（Free Space）、頁目錄（Page Directory）和文件尾（File Tailer）。

頁結(jié)構(gòu)的示意圖如下所示：

實際上，我們可以把這7個數(shù)據(jù)頁分成3個部分。

首先是文件通用部分，也就是文件頭和文件尾。它們類似集裝箱，將頁的內(nèi)容進行封裝，通過文件頭和文件尾校驗的方式來確保頁的傳輸是完整的。

在文件頭中有兩個字段，分別是FIL_PAGE_PREV和FIL_PAGE_NEXT，它們的作用相當于指針，分別指向上一個數(shù)據(jù)頁和下一個數(shù)據(jù)頁。連接起來的頁相當于一個雙向的鏈表，如下圖所示：

需要說明的是采用鏈表的結(jié)構(gòu)讓數(shù)據(jù)頁之間不需要是物理上的連續(xù)，而是邏輯上的連續(xù)。

第二個部分是記錄部分，頁的主要作用是存儲記錄，所以“最小和最大記錄”和“用戶記錄”部分占了頁結(jié)構(gòu)的主要空間。另外空閑空間是個靈活的部分，當有新的記錄插入時，會從空閑空間中進行分配用于存儲新記錄，如下圖所示：

第三部分是索引部分，這部分重點指的是頁目錄，它起到了記錄的索引作用。因為在頁中，記錄是以單向鏈表的形式進行存儲的。單向鏈表的特點就是插入、刪除非常方便，但是檢索效率不高，最差的情況下需要遍歷鏈表上的所有節(jié)點才能完成檢索，因此在頁目錄中提供了二分查找的方式，用來提高記錄的檢索效率。

索引

索引是什么

索引的出現(xiàn)是為了提高數(shù)據(jù)查詢的效率，就像書的目錄一樣。沒有索引，我們就只能一頁一頁去找。而加上索引之后，我們可以根據(jù)目錄來快速查找我們所需要的內(nèi)容。

對于數(shù)據(jù)庫的表而言，索引就是它的“目錄”，它是幫助MySQL系統(tǒng)快速檢索數(shù)據(jù)的一種數(shù)據(jù)結(jié)構(gòu)。索引在插入時會對數(shù)據(jù)進行排序，我們可以在索引中按照查詢條件，檢索索引字段的值，然后快速定位數(shù)據(jù)記錄的位置，這樣就不需要遍歷整個數(shù)據(jù)表了。

索引好壞的評價標準

可以用于提高讀寫效率的數(shù)據(jù)結(jié)構(gòu)很多，那么如何評價一個索引數(shù)據(jù)結(jié)構(gòu)的好壞呢？

數(shù)據(jù)庫服務器有兩種存儲介質(zhì)，分別為硬盤和內(nèi)存。硬盤相當于永久存儲介質(zhì)。內(nèi)存屬于臨時存儲，容量有限，當發(fā)生意外時（比如斷電或者發(fā)生故障重啟）會造成數(shù)據(jù)丟失。

所以盡管內(nèi)存的讀取速度很快，但我們還是需要將索引存放到硬盤上。這樣的話，當我們在硬盤上進行查詢時，也就產(chǎn)生了硬盤的I/O操作，而硬盤I/O耗時是比較高的。

所以好的索引數(shù)據(jù)結(jié)構(gòu)應該盡量減少硬盤的I/O操作，降低耗時。

索引的數(shù)據(jù)結(jié)構(gòu)

MySQL默認存儲引擎是InnoDB存儲引擎,InnoDB存儲引擎使用的是B+樹索引。

B樹

B樹的英文是Balance Tree，也就是平衡的多路搜索樹。在文件系統(tǒng)和數(shù)據(jù)庫系統(tǒng)中的索引結(jié)構(gòu)經(jīng)常采用B樹來實現(xiàn)。

B樹的結(jié)構(gòu)如下圖所示（一棵3階的B樹）：

B+樹

相較于B樹而言，B+樹在兩個方面做出了改進和提升，一方面是查詢的穩(wěn)定性，另一方面是查詢的效率更高。

下圖是一棵3階的B+樹

B+樹和B樹有個根本的差異在于，B+樹的中間節(jié)點并不直接存儲數(shù)據(jù)。這樣的好處都有什么呢？

首先，B+樹查詢效率更穩(wěn)定。因為B+樹每次只有訪問到葉子節(jié)點才能找到對應的數(shù)據(jù)，而在B樹中，非葉子節(jié)點也會存儲數(shù)據(jù)，這樣就會造成查詢效率不穩(wěn)定的情況，有時候訪問到了非葉子節(jié)點就可以找到關鍵字，而有時需要訪問到葉子節(jié)點才能找到關鍵字。

其次，B+樹的查詢效率更高，這是因為通常B+樹比B樹更矮胖（階數(shù)更大，深度更低），查詢所需要的磁盤I/O也會更少。同樣的磁盤頁大小，B+樹可以存儲更多的節(jié)點關鍵字。

不僅是對單個關鍵字的查詢上，在查詢范圍上，B+樹的效率也比B樹高。這是因為所有關鍵字都出現(xiàn)在B+樹的葉子節(jié)點中，并通過有序鏈表進行了鏈接。而在B樹中則需要通過中序遍歷才能完成查詢范圍的查找，效率要低很多。

B+樹是如何進行記錄檢索的？

如果通過B+樹的索引查詢行記錄，首先是從B+樹的根開始，逐層檢索，直到找到葉子節(jié)點，也就是找到對應的數(shù)據(jù)頁為止，將數(shù)據(jù)頁加載到內(nèi)存中，頁目錄中的槽（slot）采用二分查找的方式先找到一個粗略的記錄分組，然后再在分組中通過鏈表遍歷的方式查找記錄。

索引維護

B+樹為了維護索引有序性，在插入新值的時候需要做必要的維護。這里存在兩種不同數(shù)據(jù)類型的插入情況。

數(shù)據(jù)順序（或逆序）插入： B+ 樹索引的維護代價非常小，葉子節(jié)點都是從左往右進行插入，比較典型的是自增 ID 的插入、時間的插入（若在自增 ID 上創(chuàng)建索引，時間列上創(chuàng)建索引，則 B+ 樹插入通常是比較快的）。數(shù)據(jù)無序插入： B+ 樹為了維護排序，需要對頁進行分裂、旋轉(zhuǎn)等開銷較大的操作，另外，即便對于固態(tài)硬盤，隨機寫的性能也不如順序?qū)懀源疟P性能也會收到較大影響。比較典型的是用戶昵稱，每個用戶注冊時，昵稱是隨意取的，若在昵稱上創(chuàng)建索引，插入是無序的，索引維護需要的開銷會比較大。

所以對于 B+ 樹索引，在 MySQL 數(shù)據(jù)庫設計中，建議主鍵的索引設計為順序，比如使用自增，或使用函數(shù) UUID_TO_BIN 排序的 UUID，而不用無序值做主鍵。

索引組織表

MySQL InnoDB 存儲引擎是索引組織表的存儲方式。

在索引組織表中，數(shù)據(jù)即索引，索引即數(shù)據(jù)，數(shù)據(jù)根據(jù)主鍵排序存放在索引中。

索引組織表示例：

表 User 的主鍵是 id，所以表中的數(shù)據(jù)根據(jù) id 排序存儲，葉子節(jié)點存放了表中完整的記錄，可以看到表中的數(shù)據(jù)存放在索引中，即表就是索引，索引就是表。

二級索引

InnoDB 存儲引擎的數(shù)據(jù)是根據(jù)主鍵索引排序存儲的，除了主鍵索引（聚集索引）外，其他的索引都稱之為二級索引， 或非聚集索引。

二級索引也是一顆 B+ 樹索引，但它和主鍵索引不同的是葉子節(jié)點存放的是索引鍵值、主鍵值。

通過二級索引 idx_name 只能定位主鍵值，需要額外再通過主鍵索引進行查詢，才能得到最終的結(jié)果。這種“二級索引通過主鍵索引進行再一次查詢”的操作叫作“回表”，你可以通過下圖理解二級索引的查詢：

索引組織表這樣的二級索引設計有一個非常大的好處：若記錄發(fā)生了修改，則其他索引無須進行維護，除非記錄的主鍵發(fā)生了修改。

與堆表的索引實現(xiàn)對比著看，你會發(fā)現(xiàn)索引組織表在存在大量變更的場景下，性能優(yōu)勢會非常明顯，因為大部分情況下都不需要維護其他二級索引。

索引設計

覆蓋索引

覆蓋索引是select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，換句話說查詢列要被所建的索引覆蓋。

由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優(yōu)化手段。

當然，索引字段的維護總是有代價的。因此，在建立冗余索引來支持覆蓋索引時就需要權(quán)衡考慮了。

函數(shù)索引

函數(shù)索引即索引鍵是一個函數(shù)表達式。

舉個例子，假設User表中創(chuàng)建了register_date索引。

SELECT * FROM User WHERE DATE_FORMAT(register_date,"%Y-%m") = "2021-01"

這條 SQL 因為索引 register_date 只對 register_date 的數(shù)據(jù)排序，沒有對DATE_FORMAT(register_date) 排序，

因此上述 SQL 無法使用二級索引register_date。此時我們可以創(chuàng)建一個DATE_FORMAT(register_date) 索引，來提升SQL的查詢性能。

ALTER TABLE User ADD INDEX func_register_date((DATE_FORMAT(register_date,"%Y-%m")));

前綴索引

MySQL是支持前綴索引的，也就是說，你可以定義字符串的一部分作為索引。從而創(chuàng)建占用空間更小、查詢效率相同的字段。

示例代碼：

alter table user add index user_uuid_index(user_uuid(10));

組合索引最左前綴原則

組合索引的多個字段是有序的，遵循左對齊的原則。假設我們創(chuàng)建一個組合索引，排序的方式是sex、age和height。此時，篩選的條件也要遵循從左向右的原則。如果中斷，那么，斷點后面的條件就沒有辦法利用索引了。

假設查詢條件為 "WHERE sex = "男" AND age = 25 AND height = 180"，包含了從左到右的所有字段，所以可以最大限度使用全部組合索引。

假如把條件換成“WHERE age = 25 AND height = 180”，最左邊的字段 sex 沒有包含到條件當中，中斷了，所以這個條件完全不能使用組合索引。

創(chuàng)建索引的規(guī)律

我之前講了索引的使用和它的底層原理，今天我來講一講索引的使用原則。既然我們的目標是提升SQL的查詢效率，那么該如何通過索引讓效率最大化呢？

1.字段的數(shù)值有唯一性的限制，比如用戶名

索引本身可以起到約束的作用，比如唯一索引、主鍵索引都是可以起到唯一性約束的，因此在我們的數(shù)據(jù)表中，如果某個字段是唯一性的，就可以直接創(chuàng)建唯一性索引，或者主鍵索引。

2.頻繁作為WHERE查詢條件的字段，尤其在數(shù)據(jù)表大的情況下

在數(shù)據(jù)量大的情況下，某個字段在SQL查詢的WHERE條件中經(jīng)常被使用到，那么就需要給這個字段創(chuàng)建索引了。創(chuàng)建普通索引就可以大幅提升數(shù)據(jù)查詢的效率。

3.需要經(jīng)常GROUP BY和ORDER BY的列

索引就是讓數(shù)據(jù)按照某種順序進行存儲或檢索，因此當我們使用GROUP BY對數(shù)據(jù)進行分組查詢，或者使用ORDER BY對數(shù)據(jù)進行排序的時候，就需要對分組或者排序的字段進行索引。

4.UPDATE、DELETE的WHERE條件列，一般也需要創(chuàng)建索引

先根據(jù)WHERE條件列檢索出來這條記錄，然后再對它進行更新或刪除。如果進行更新的時候，更新的字段是非索引字段，提升的效率會更明顯，這是因為非索引字段更新不需要對索引進行維護。

不過在實際工作中，我們也需要注意平衡，如果索引太多了，在更新數(shù)據(jù)的時候，如果涉及到索引更新，就會造成負擔。

5.DISTINCT字段需要創(chuàng)建索引

有時候我們需要對某個字段進行去重，使用DISTINCT，那么對這個字段創(chuàng)建索引，也會提升查詢效率。

6.做多表JOIN連接操作時，創(chuàng)建索引需要注意以下的原則

首先，連接表的數(shù)量盡量不要超過3張，因為每增加一張表就相當于增加了一次嵌套的循環(huán)，數(shù)量級增長會非常快，嚴重影響查詢的效率。

其次，對WHERE條件創(chuàng)建索引，因為WHERE才是對數(shù)據(jù)條件的過濾。如果在數(shù)據(jù)量非常大的情況下，沒有WHERE條件過濾是非常可怕的。

最后，對用于連接的字段創(chuàng)建索引，并且該字段在多張表中的類型必須一致。

從表結(jié)構(gòu)入手設計提高性能

數(shù)據(jù)類型優(yōu)化

盡量使用占用存儲空間更少的數(shù)據(jù)類型，例如字段既可以用文本類型，也可以用整數(shù)類型時，盡量使用整數(shù)類型。

需要注意的是：修改數(shù)據(jù)類型，節(jié)省存儲空間的同時，你要考慮到數(shù)據(jù)不能超過取值范圍；

合理增加冗余字段以提高效率

在數(shù)據(jù)量大，而且需要頻繁進行連接的時候，為了提升效率，可以考慮增加冗余字段來減少連接。

需要注意的是：增加冗余字段的時候，不要忘了確保數(shù)據(jù)一致性；

拆分表

把1個包含很多字段的表拆分成2個或者多個相對較小的表。這樣做的原因是，把這個大表拆分開，把使用頻率高的字段放在一起形成一個表，把剩下的使用頻率低的字段放在一起形成一個表，這樣查詢操作每次讀取的記錄比較小，查詢效率自然也就提高了。

需要注意的是：把大表拆分，也意味著你的查詢會增加新的連接，從而增加額外的開銷和運維的成本。

分庫分表

分庫分表的目的就是為了解決由于數(shù)據(jù)量過而導致數(shù)據(jù)庫性能降低的問題，將原來獨立的數(shù)據(jù)庫拆分為若干數(shù)據(jù)庫組成，將數(shù)據(jù)大表拆分成若干數(shù)據(jù)表，使得單一數(shù)據(jù)庫、單一數(shù)據(jù)表的數(shù)據(jù)量變小，從而達到提升數(shù)據(jù)庫性能的目的。

一般來說，在系統(tǒng)設計階段就應該根據(jù)業(yè)務耦合程度來確定用哪種分庫分表的方式(方案)，在數(shù)據(jù)量及訪問壓力不是特別大的情況，首先考慮緩存、讀寫分離、索引技術(shù)等方案。若數(shù)據(jù)量極大，且連續(xù)增長，再考慮水平分庫水平分表的方案。

分庫分表的方式

分庫分表的方式在生產(chǎn)中通常包括：垂直分庫、垂直分表、水平分庫和水平分表四種。

垂直分表

定義：將一個表按字段分成多表，每個表存儲其中一部分字段。

帶來的提升是：

為了避免IO爭搶并減少鎖表的幾率。充分發(fā)揮熱門數(shù)據(jù)的操作效率。

需要注意的是：拆分后，盡量從業(yè)務角度避免聯(lián)查，否則性能方面將得不償失。

為什么大字段表的IO效率低：

第一由于數(shù)據(jù)量本身大，需要更長的讀取時間；

第二是跨頁，頁是數(shù)據(jù)存儲單位，很多查找及定位操作都是以頁為單位，單頁內(nèi)的數(shù)據(jù)行越來越多數(shù)據(jù)庫整體性能越好，而大字段占用空間大，單頁內(nèi)存儲行數(shù)少，因此IO效率低。

第三，數(shù)據(jù)庫以行為單位將數(shù)據(jù)加載到內(nèi)存中，這樣表中字段長度較短且訪問頻率較高，內(nèi)存能加載更多的數(shù)據(jù)，命中率高，減少了磁盤IO，從而提升了數(shù)據(jù)庫性能。

垂直分庫

垂直分庫是指按照業(yè)務將表進行分類，分布到不同的數(shù)據(jù)庫上面，每個庫可以放在不同的服務器上，從而使訪問壓力被分攤在多個服務器，大大提高性能，同時能提高整體架構(gòu)的業(yè)務清晰度，不同的業(yè)務庫可根據(jù)自身情況定制優(yōu)化方案，它的核心理念是專庫專用。

帶來的提升是：

解決業(yè)務層面的耦合，業(yè)務清晰能對不同業(yè)務的數(shù)據(jù)進行分級管理、維護、監(jiān)控和擴展等高并發(fā)場景下，垂直分庫一定程度上提升IO、數(shù)據(jù)庫連接和降低單機硬件資源的瓶頸

水平分庫

水平分庫就是把同一個表的數(shù)據(jù)按一定規(guī)則拆到不同的數(shù)據(jù)庫中，每個庫可以放在不同的服務器上。

帶來的提升是：

解決了單庫大數(shù)據(jù)，高并發(fā)的性能瓶頸。提高了系統(tǒng)的穩(wěn)定性和可用性。

需要注意的是：使用水平分庫不僅需要解決跨庫帶來的問題，還需要解決數(shù)據(jù)路由的問題。

水平分表

水平分表就是在同一個數(shù)據(jù)庫內(nèi)，把同一個表的數(shù)據(jù)按一定規(guī)則拆到多個表中（對數(shù)據(jù)的拆分，不影響表結(jié)構(gòu)）。

它帶來的提升是：

優(yōu)化單一表數(shù)據(jù)量過大而產(chǎn)生的性能問題避免IO爭搶并減少鎖表的幾率

從架構(gòu)設計上優(yōu)化查詢性能

比學習知識更重要的是靈活地調(diào)用知識儲備高效解決實際問題的能力。想要提高應用程序的查詢性能，還要結(jié)合實際的業(yè)務需求設計合理的功能架構(gòu)。

舉個簡單例子來說明一下：

假設我們現(xiàn)在需要提供一個接口供前端顯示統(tǒng)計數(shù)據(jù)。用于統(tǒng)計的數(shù)據(jù)存在一張千萬級數(shù)據(jù)的表中，并且數(shù)據(jù)量級在快速增加。

你會怎么設計這個接口，分庫分表？

首先會想到的是設計合適的索引來加快查詢和統(tǒng)計速度。但是因為表的數(shù)據(jù)級很大，如果每次統(tǒng)計都在這張表中進行，處理耗時依然會很長。

此時我們可以想辦法把統(tǒng)計表的數(shù)據(jù)縮減，例如將統(tǒng)計表的數(shù)據(jù)提前統(tǒng)計好存入“統(tǒng)計表2”，使用定時任務將統(tǒng)計表新插入的數(shù)據(jù)集也統(tǒng)計合并到“統(tǒng)計表2”，需要統(tǒng)計數(shù)據(jù)時就在這張”統(tǒng)計表2”中進行。因為表的數(shù)據(jù)量小了，所以查詢性能可以提高很多。

但是需要注意的是，這樣做會額外占用了很多存儲空間，并且返回的統(tǒng)計數(shù)據(jù)并不精準，這就要看實際業(yè)務來做取舍了。

作者簡介

鑫茂，深圳，Java開發(fā)工程師，2022年3月參加工作。

喜讀思維方法、哲學心理學以及歷史等方面的書，偶爾寫些文字。

希望通過文章，結(jié)識更多同道中人。