數據庫查詢優化

這篇文章適合剛剛入手項目的小伙伴，為大家如何提高數據庫查詢效率提供一些建議。

1.添加索引

1.1 索引是什么

對于索引基礎薄弱的同學，我們可以從 “索引是什么” 簡單類比：索引就像書籍的目錄，能幫數據庫快速定位到需要的數據，而不用逐行掃描全表。下面結合項目具體說明索引的創建邏輯和使用方式。

1.2?索引的作用

索引的核心功能是幫助數據庫快速定位目標數據，避免全表掃描，從而大幅提升查詢效率（類似書籍目錄的作用）。

這里以復合索引舉一個例子：

定義：由多個字段組合創建的索引，遵循 “最左前綴原則”（查詢條件包含索引的前 N 個字段時才能生效）。
項目示例：

-- 訂單表：按“狀態+訂單時間”創建復合索引
CREATE INDEX idx_orders_status_time ON orders(status, order_time DESC);

作用：優化包含?status?和?order_time?的查詢，例如：

-- 能用到上述索引的查詢
SELECT * FROM orders WHERE status = 'PENDING' ORDER BY order_time DESC;
SELECT * FROM orders WHERE status = 'PENDING' AND order_time > '2024-01-01';

1.3 索引的使用方式

1.3.1 索引提示（強制使用指定索引）

當數據庫優化器未選擇最優索引時，通過?/*+ INDEX(表名索引名) */?強制指定：

<!-- 訂單查詢中強制使用 idx_orders_status_time 索引 -->
<select id="pageQuery" resultType="com.sky.entity.Orders">SELECT /*+ INDEX(orders idx_orders_status_time) */ id, number, status, user_id, order_time, amountFROM ordersWHERE status = #{status}ORDER BY order_time DESC
</select>

1.3.2 配合查詢優化使用索引

避免?SELECT *：只查詢需要的字段，讓覆蓋索引生效（否則會觸發回表）：

<!-- 優化后：只查索引包含的字段 -->
<select id="pageQuery" resultType="com.sky.entity.Orders">SELECT id, number, status, order_time  -- 這些字段都在 idx_orders_cover_list 中FROM orders WHERE status = #{status}
</select>

1.3.3 優化分頁查詢

使用游標分頁時，通過索引字段（如?order_time）定位，避免大偏移量導致的全表掃描：

<!-- 基于 order_time 索引的游標分頁 -->
<select id="pageQueryByCursor" resultType="com.sky.entity.Orders">SELECT id, order_time FROM ordersWHERE order_time < #{lastOrderTime}  -- 利用索引快速定位ORDER BY order_time DESCLIMIT #{limit}
</select>

1.4?索引的注意事項

1.4.1 索引不是越多越好

索引會增加寫入（插入 / 更新 / 刪除）的開銷（因為要維護索引結構），項目中通過定期刪除無用索引（如長期未被使用的索引）避免性能損耗。

1.4.2??索引失效場景

不滿足最左前綴原則（如用?idx_orders_status_time?時，查詢條件不含?status）；
在索引字段上使用函數（如?DATE(order_time) = '2024-01-01'，需用函數索引解決）；
模糊查詢以?%?開頭（如?name LIKE '%魚香肉絲'，索引失效，項目中通過?idx_dish_name_status?優化前綴匹配）。

1.4.3??定期維護索引

項目建議通過?ANALYZE TABLE?更新表統計信息，幫助數據庫優化器選擇最優索引：

ANALYZE TABLE orders;  -- 更新訂單表統計信息

ANALYZE TABLE?是數據庫（如 MySQL）的內置命令，作用是重新計算并更新表的統計信息（如行數、數據分布、索引基數等），這些信息會被數據庫優化器直接使用，只需在 SQL 客戶端中直接執行?ANALYZE TABLE 表名；即可完成統計信息更新無需在應用層或數據庫配置文件中做額外設置。

2.SQL查詢優化

通過優化 SQL 語句邏輯，減少不必要的數據處理，提升執行效率：

避免SELECT *：只查詢必要字段，減少數據傳輸量和回表操作。例如OrderMapper.xml中，優化后的查詢明確指定所需字段（如id, number, status等），而非查詢所有字段。
優化 JOIN 查詢：用INNER JOIN替代LEFT JOIN（適用場景），避免返回無效空數據，減少關聯掃描范圍。例如菜品查詢中，通過INNER JOIN關聯菜品表和分類表，確保關聯數據有效性。
索引提示：使用/*+ INDEX(table_name index_name) */強制優化器使用指定索引，避免索引選擇偏差。例如訂單統計查詢中，通過/*+ INDEX(orders idx_orders_status_time) */確保使用復合索引。

3.分頁查詢優化

針對傳統分頁（LIMIT offset, size）在大偏移量時性能差的問題，采用游標分頁：

原理：以上一次查詢的最后一條記錄的排序字段（如order_time）作為條件，避免掃描偏移量前的所有數據。
示例：OrderMapper.xml中pageQueryByCursor方法通過WHERE order_time < #{lastOrderTime}定位分頁起點，配合LIMIT獲取數據，性能不受偏移量影響。

<!-- 游標分頁查詢，避免OFFSET性能問題 --><select id="pageQueryByCursor" resultType="com.sky.entity.Orders">SELECT /*+ INDEX(orders idx_orders_status_time) */id, number, status, user_id, address_book_id, order_time, checkout_time, pay_method, pay_status, amount, remark, phone, address, consignee, estimated_delivery_time, delivery_status, pack_amount, tableware_number, tableware_statusFROM orders<where><if test="lastOrderTime != null">and order_time &lt; #{lastOrderTime}</if><if test="status != null">and status = #{status}</if><if test="userId != null">and user_id = #{userId}</if></where>ORDER BY order_time DESCLIMIT #{limit}</select>

4. 批量操作優化

減少與數據庫的交互次數，降低網絡開銷：

批量插入 / 更新 / 刪除：通過foreach標簽在 XML 中實現批量操作。例如菜品批量插入時，一次性插入多條數據，而非單條循環執行：

<insert id="batchInsert">INSERT INTO dish (...) VALUES<foreach collection="list" item="dish" separator=",">(#{dish.name}, #{dish.categoryId}, ...)</foreach>
</insert>

5.數據庫配置優化（spring.datasource.druid）

在配置文件application.yml中配置。

5.1 Druid數據庫連接池優化配置

Druid 是高性能連接池，該部分配置直接決定數據庫連接的利用率、穩定性和查詢效率，是數據庫優化的 “基石”：

spring:    druid:driver-class-name: ${sky.datasource.driver-class-name}#rewriteBatchedStatements: true 優化 MySQL 批量插入 / 更新（將多條 SQL 合并為 1 條，減少網絡交互）url: jdbc:mysql://${sky.datasource.host}:${sky.datasource.port}/${sky.datasource.database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=trueusername: ${sky.datasource.username}password: ${sky.datasource.password}# 連接池優化配置#系統啟動時創建 10 個連接，避免首次請求時 “臨時創建連接” 的延遲initial-size: 10#保留 20 個空閑連接，避免 “頻繁創建 / 銷毀連接” 的資源損耗min-idle: 20#限制并發連接上限，避免連接過多壓垮數據庫max-active: 100#避免線程無限阻塞在 “獲取連接” 上，快速失敗并釋放資源max-wait: 30000time-between-eviction-runs-millis: 30000min-evictable-idle-time-millis: 900000#定期檢查空閑連接是否可用，避免使用 “失效連接”（如網絡波動導致的死連接）test-while-idle: truevalidation-query: SELECT 1test-on-borrow: falsetest-on-return: false#緩存 SQL 預處理語句，避免重復解析 SQL（尤其高頻查詢，如 “查詢在售菜品”）pool-prepared-statements: truemax-pool-prepared-statement-per-connection-size: 20filters: stat,wall,slf4j#記錄執行時間超過 5 秒的 SQL，便于定位性能瓶頸connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000#避免 “連接泄漏”（如代碼未關閉連接）導致連接池耗盡remove-abandoned: true                    # 自動回收超時連接remove-abandoned-timeout: 1800            # 超時時間30分鐘log-abandoned: true                       # 記錄超時連接日志

5.2?MyBatis 執行優化配置(mybatis.configuration)

MyBatis 作為 ORM 框架，其配置直接影響 SQL 執行效率和內存使用，屬于 “數據庫操作層” 的優化：

mybatis:#mapper配置文件mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.sky.entityconfiguration:#開啟駝峰命名，自動將數據庫字段（如 order_time）映射為 Java 駝峰屬性（orderTime），避免手動配置 resultMapmap-underscore-to-camel-case: true# 開啟二級緩存，緩存 MyBatis 執行結果（跨 SqlSession 共享），減少重復查詢數據庫cache-enabled: true#開啟 “按需延遲加載”，關聯查詢時（如 “查詢訂單 + 關聯訂單詳情”），僅在使用關聯數據時才查庫，避免冗余數據加載# 開啟延遲加載lazy-loading-enabled: true# 設置積極的延遲加載aggressive-lazy-loading: false# 設置延遲加載的觸發方法lazy-load-trigger-methods: equals,clone,hashCode,toString# 設置默認執行器為復用，復用 SQL 執行器（避免頻繁創建執行器實例），減少對象創建銷毀的資源損耗default-executor-type: REUSE# 設置默認語句超時時間，SQL執行超時=30秒，限制單條 SQL 的執行時間，避免長耗時查詢阻塞數據庫連接default-statement-timeout: 30# 結果集每次獲取 100 行，分批讀取查詢結果，避免一次性加載大量數據導致內存溢出default-fetch-size: 100

5.3 隱藏參數：數據庫連接URL參數（spring.datasource.druid.url）

spring：  datasource:druid:url: jdbc:mysql://${sky.datasource.host}:${sky.datasource.port}/${sky.datasource.database}?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true

URL 參數	作用	優化效果（外賣場景）
`rewriteBatchedStatements=true`	優化 MySQL 批量操作（將?`INSERT INTO ...; INSERT INTO ...`?合并為?`INSERT INTO ... VALUES (...),(...)`）	外賣系統 “批量添加購物車商品”“批量更新菜品庫存” 時，網絡交互次數從 N 次減少到 1 次，效率提升 10-100 倍
`cachePrepStmts=true`?+?`prepStmtCacheSize=250`?+?`prepStmtCacheSqlLimit=2048`	開啟客戶端 PreparedStatement 緩存，緩存 SQL 模板和參數	高頻查詢（如 “查詢菜品詳情（dish_id=?）”）可復用緩存的 SQL 模板，避免重復解析 SQL 語法，提升查詢速度
`useServerPrepStmts=true`	使用 MySQL 服務器端預處理語句，而非客戶端模擬	進一步減少 SQL 解析開銷，尤其對復雜 SQL（如 “多條件篩選訂單”），提升執行效率
`zeroDateTimeBehavior=convertToNull`	將數據庫中的?`0000-00-00`?時間轉為?`null`	避免因時間格式不兼容導致的 SQL 執行異常，保障訂單時間查詢的穩定性

5.4 總結：這些配置解決了數據庫的哪些核心問題？

連接管理問題：通過 Druid 連接池參數（max-active/min-idle/ 超時回收），避免 “連接耗盡” 或 “無效連接”，支撐外賣高峰的高并發；
SQL 效率問題：通過 MyBatis 二級緩存、延遲加載、執行器復用，減少數據庫訪問次數，降低高頻查詢的壓力；
批量操作問題：rewriteBatchedStatements?等 URL 參數，優化外賣系統的批量場景（如批量導入菜品、批量更新訂單）；
問題定位問題：slowSqlMillis?記錄慢 SQL，便于后續優化（如給慢查詢加索引、調整 SQL 邏輯）。

這些配置與之前提到的 “索引優化”“ANALYZE TABLE 統計信息” 形成互補，共同保障數據庫在高并發下的穩定和高效。

6.數據庫監控實現

數據庫監控是干嘛的？簡單說，數據庫監控就是「給數據庫操作裝個 “記錄儀”」，幫你實時掌握：

數據庫連接夠不夠用？有沒有連接超時、浪費的情況？
執行的 SQL 快不快？有沒有 “拖慢系統” 的慢 SQL？
有沒有危險的 SQL（比如注入攻擊）？
每個 SQL 執行了多少次、查了多少數據、耗時多久？

這里我們通過Druid連接池（阿里開源的數據庫連接池工具）和日志配置來內置這套“記錄儀”。

6.1 核心實現工具：Druid連接池

項目中的數據庫連接使用spring.datasource.druid?相關配置,而Druid本身自帶強大的監控功能，不需要額外集成第三方工具（如Prometheus），只需要通過配置 “開啟” 即可。

在項目application.yml中配置：

spring:datasource:druid:# 1. 開啟監控相關的過濾器（核心！監控的“入口”）filters: stat,wall,slf4j# 2. 監控參數配置（比如“慢SQL”的定義、是否合并重復SQL）connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000# 3. 超時連接監控（記錄“沒人要”的連接）remove-abandoned: true                    # 自動回收超時連接remove-abandoned-timeout: 1800            # 超時時間：30分鐘（1800秒）log-abandoned: true                       # 記錄超時連接的日志

先拆解這幾個核心配置的作用 —— 它們是監控功能的 “開關” 和 “規則”：

配置項	通俗解釋	監控作用
`filters: stat,wall,slf4j`	給 Druid 裝 3 個 “插件”	-?`stat`：SQL 執行監控插件（核心），記錄 SQL 執行耗時、次數、行數 -?`wall`：防 SQL 注入插件（附帶監控），攔截危險 SQL 并記錄 -?`slf4j`：日志輸出插件，把監控數據輸出到日志文件
`druid.stat.slowSqlMillis=5000`	定義 “慢 SQL”：執行時間超過 5000 毫秒（5 秒）的 SQL	自動標記慢 SQL，方便排查 “拖慢系統” 的語句
`druid.stat.mergeSql=true`	把相同的 SQL 合并統計（比如 “SELECT * FROM user WHERE id=1” 和 “id=2” 算一類）	避免重復 SQL 刷屏，更清晰看 “哪類 SQL 執行最多 / 最慢”
`remove-abandoned + log-abandoned`	連接 “沒人要”（比如代碼沒關連接）超過 30 分鐘，就回收并記錄日志	監控 “連接泄露” 問題（連接泄露會導致數據庫連接不夠用）

6.2 具體監控內容

基于上面的配置，項目會監控?3 大類核心數據，每類都能直接幫你排查問題：

1.數據庫連接池監控（看 “連接夠不夠用”）

連接池是 “管理數據庫連接的容器”（比如你配置的?initial-size=10?是初始 10 個連接，max-active=100?是最多 100 個連接）。監控會實時統計：

當前活躍的連接數（正在用的連接）
空閑連接數（沒被用的連接）
等待連接的請求數（連接不夠時，排隊等連接的請求）
連接超時 / 泄露的次數（比如超過 30 分鐘沒關的連接，會被記錄）

這些數據的來源，就是你配置的?initial-size「初始連接數」、max-active「最大連接數」、remove-abandoned-timeout「超時時間」等 ——Druid 會自動統計這些配置對應的實際使用情況。

2.SQL 執行監控（看 “SQL 快不快、多不多”）

這是最常用的監控，stat?過濾器會攔截每一次 SQL 執行，記錄：

基礎信息：執行的 SQL 語句（合并后的，比如 “SELECT * FROM user WHERE id=?”）
性能信息：
- 執行總次數（這個 SQL 跑了多少次）
- 平均耗時（每次執行平均花多久）
- 最大耗時（單次執行最久的時間）
- 慢 SQL 次數（超過 5 秒的次數）
數據量信息：每次 SQL 返回多少行數據、影響多少行數據（比如 INSERT/UPDATE 的行數）

舉個例子：如果監控到 “SELECT * FROM order WHERE create_time < '2024-01-01'” 執行了 100 次，平均耗時 6 秒，那這個就是 “慢 SQL”，需要優化（比如加索引）。

3.SQL 注入防護監控（看 “有沒有危險 SQL”）

wall?過濾器（防注入插件）會監控并攔截危險 SQL，比如：

用戶輸入的惡意語句（比如 “SELECT * FROM user WHERE name='admin' OR 1=1”—— 這會查所有用戶數據）
禁止的操作（比如 DROP TABLE 刪表、ALTER TABLE 改表結構）

如果有危險 SQL 被攔截，slf4j?插件會把這個行為記錄到日志里，方便你知道 “有人在嘗試攻擊數據庫”。

6.3 查看監控數據

兩種方式，新手優先看日志。

1. 看日志文件

我們的logging配置已經指定日志輸出路徑和級別，監控數據會自動寫到我們在application.yml中指定的文件中。

logging:level:com.alibaba.druid: info  # Druid的監控日志輸出級別（info級別的日志會記錄）org.mybatis: debug       # MyBatis的SQL日志（會打印執行的SQL語句）file:name: logs/szj.log  # 日志文件路徑

你只需要打開指定的文件就能看到：

慢 SQL 日志：比如?[DruidDataSourceStatLogger] slow sql 5001ms: SELECT * FROM order WHERE ...（明確標出耗時 5.001 秒的慢 SQL）
連接泄露日志：比如?[DruidDataSource] abandon connection, url:jdbc:mysql://..., timeout:1800秒（標出超時 30 分鐘的連接）
SQL 執行日志：MyBatis 的?debug?級別會打印 “執行的 SQL 語句 + 參數”，比如?==> Preparing: SELECT * FROM user WHERE id=??==> Parameters: 1(Integer)

2.Druid 可視化控制臺(更直觀)

如果覺得看日志不夠直觀，還可以開啟 Druid 自帶的?Web 控制臺（類似一個網頁，能看到圖表化的監控數據）。只需要在你的項目中加一段配置（Spring Boot 項目），就能通過瀏覽器訪問：

spring:datasource:druid:# 開啟Druid控制臺stat-view-servlet:enabled: true        # 開啟控制臺url-pattern: /druid/* # 訪問路徑：http://你的項目地址:8080/druidlogin-username: admin # 控制臺登錄賬號（自己設）login-password: 123456 # 控制臺登錄密碼（自己設）

配置后，打開瀏覽器訪問?http://localhost:8080/druid（如果你項目的端口是 8080），登錄后就能看到：

「數據源」：連接池的實時狀態（活躍連接、空閑連接等）
「SQL 監控」：所有 SQL 的執行次數、耗時排行（能直接按 “慢 SQL” 篩選）
「SQL 防火墻」：被攔截的危險 SQL 記錄
「Web 應用」：請求對應的 SQL 執行情況

這種方式對新手更友好，能一眼看到 “哪個 SQL 最慢”“連接夠不夠用”。

一番猛烈的操作下來，恭喜你！你項目的監控功能已經 “Ready” 了！

7.?高并發場景的進階優化（適合數據量大 / 請求多的業務）

7.1 讀寫分離

7.1.1?介紹

讀寫分離的核心邏輯是：把數據庫的 “寫操作”（插入 / 更新 / 刪除，即 INSERT/UPDATE/DELETE）和 “讀操作”（查詢，即 SELECT）分別交給不同的數據庫實例處理。

負責 “寫操作” 的庫叫?主庫（Master）：所有新增、修改、刪除數據的請求，都只發給主庫。
負責 “讀操作” 的庫叫?從庫（Slave）：所有查詢數據的請求，都發給從庫（可以有多個從庫）。
關鍵動作：主庫會自動把 “寫操作的數據變化” 同步給從庫，保證從庫的數據和主庫一致（這個過程叫 “數據同步”）。

7.1.2 作用

大部分業務場景都是 “讀多寫少” 的：比如電商網站，用戶瀏覽商品（讀）、搜索商品（讀）的次數，遠大于下單（寫）、付款（寫）的次數；再比如新聞 APP，用戶看新聞（讀）的次數遠大于發布新聞（寫）的次數。

如果只用單臺數據庫（單庫）：

寫操作會 “鎖表 / 鎖行”（比如更新一條數據時，其他請求要等它結束），會拖慢同時發生的讀操作；
大量讀請求擠在一臺機器上，CPU、內存、IO 都會被占滿，導致查詢變慢（比如用戶刷商品列表加載半天）。

而讀寫分離能解決這些問題：

主庫只專注 “寫”，不用處理大量讀請求，寫操作更高效；
多個從庫分擔 “讀” 請求，就算讀請求再多，也能分攤壓力（比如 1 個主庫 + 3 個從庫，讀壓力直接減為原來的 1/3）；
從庫掛了不影響寫操作（主庫還在），主庫掛了可以切換從庫當主庫（高可用）。

7.1.3 主庫如何將數據同步給從庫

以最常用的 MySQL 為例，數據同步靠的是?“binlog（二進制日志）” 機制，步驟很簡單：

主庫記錄 binlog：主庫執行任何寫操作后，都會把這個操作的細節（比如 “在 user 表插入一條 id=100 的記錄”）記錄到本地的 binlog 日志里；
從庫拉取 binlog：從庫會啟動一個 “IO 線程”，主動去主庫拉取 binlog 日志，存到自己本地的 “中繼日志（relay log）” 里；
從庫執行中繼日志：從庫再啟動一個 “SQL 線程”，讀取中繼日志里的內容，然后在自己的庫上重復執行這些寫操作（比如同樣插入 id=100 的記錄），最終實現和主庫數據一致。

這個過程會有毫秒級的延遲（比如主庫寫完，從庫要 100ms 才能同步完），但大部分業務能接受（比如用戶下單后，100ms 后才在 “我的訂單” 里看到，完全感知不到）。

7.1.4 具體實現

普通項目不用自己寫代碼，直接用現成的工具 / 中間件就能實現，主要分兩類：

實現方案	核心邏輯	優點	缺點	適合場景
應用層方案	在代碼里判斷請求類型：是寫請求就連主庫，是讀請求就連從庫（比如用 Spring 的`AbstractRoutingDataSource`）	實現簡單，不用額外加中間件	代碼耦合度高（數據庫邏輯侵入業務代碼），新增從庫要改代碼	小型項目、讀從庫數量少
中間件方案	在應用和數據庫之間加一個 “中間件”（比如 Sharding-JDBC、MyCat、ProxySQL），中間件自動判斷請求類型并轉發	業務代碼無感知（不用改代碼），支持動態增刪從庫	需要部署和維護中間件，有一定學習成本	中大型項目、讀從庫多

舉個簡單例子：用 Sharding-JDBC 實現讀寫分離，只需要在配置文件里加幾行配置（指定主庫地址、從庫地址、哪些操作走讀庫），業務代碼里還是正常用 MyBatis 查詢，中間件會自動把 SELECT 請求轉發到從庫。

7.1.5 注意事項

數據一致性問題：如果剛寫完主庫，馬上讀從庫（比如用戶剛下單，立刻刷新 “我的訂單”），可能因為從庫沒同步完導致讀不到數據。解決辦法：
1. 核心業務（如訂單）強制讀主庫；
2. 用 “延遲判斷”（比如寫操作后，等待 100ms 再讀從庫）；
3. 用支持 “讀寫一致性” 的中間件（如 Sharding-JDBC 的 “Hint 強制路由”）。
從庫故障處理：如果某個從庫掛了，要能自動把讀請求轉移到其他從庫（中間件一般自帶 “故障切換” 功能）；
主庫故障處理：主庫掛了后，需要從從庫里選一個 “最優” 的升級為主庫（比如用 MGR、Keepalived 實現主從切換），避免寫操作中斷。

7.2 分庫分表

讀寫分離解決的是 “讀多寫少” 的問題，但如果數據量太大（比如單表數據超過 1000 萬行，單庫數據超過 100GB），就算做了讀寫分離，性能還是會崩 —— 比如查詢一個 1 億行的訂單表，就算走索引，也要掃描幾十萬行數據，耗時幾秒；備份這個表要花幾小時，甚至備份過程中會拖慢數據庫。

這時候就需要 “分庫分表”：把一個 “超大庫” 拆成多個 “小庫”，把一個 “超大表” 拆成多個 “小表”，讓每個小庫 / 小表的數據量保持在 “高效處理范圍”（比如單表 500 萬行以內）。

7.2.1 區分分庫和分表

分庫（Database Sharding）：把一個數據庫（比如order_db）拆成多個數據庫（比如order_db_01、order_db_02、order_db_03），每個小庫都在不同的服務器上（避免單服務器資源瓶頸）。
分表（Table Sharding）：把一個表（比如order表）拆成多個表（比如order_2023、order_2024、order_01、order_02），這些小表可以在同一個庫，也可以在不同庫。

實際項目中，分庫和分表經常一起用（比如先分庫，每個庫再分表），叫 “分庫分表”。

7.2.2 兩種核心拆分方式

分庫分表的關鍵是 “按什么規則拆”，主要有垂直拆分和水平拆分兩種，適用場景完全不同。

（1）垂直拆分：按 “列” 拆（把表拆成 “窄表”）

核心邏輯：根據表的 “列職責”，把一個 “寬表”（列多的表）拆成多個 “窄表”（列少的表），每個表存不同維度的數據。

比如：用戶表（user）有很多列 —— 基本信息（id、name、phone、password）、詳情信息（avatar、address、birthday、hobby）、賬戶信息（balance、points、level）。
垂直拆分后，拆成 3 個表：
- user_base：存基本信息（id、name、phone、password）—— 高頻訪問（比如登錄、查詢用戶姓名）；
- user_profile：存詳情信息（id、avatar、address、birthday、hobby）—— 低頻訪問（比如查看用戶資料）；
- user_account：存賬戶信息（id、balance、points、level）—— 涉及金錢，需要更高安全性。
為什么這么拆？
1. 減少 “無效列加載”：查詢用戶登錄信息時，不用加載 avatar、address 這些無關列，減少 IO 消耗；
2. 按訪問頻率拆分：高頻表和低頻表分開，避免低頻表的大字段（比如 avatar 是圖片 URL，字段長）拖慢高頻查詢；
3. 按安全性拆分：賬戶表單獨存儲，方便單獨加權限控制、備份策略。
垂直分庫：如果拆后的表數據量還是很大，或者訪問量很高，可以把這些表放到不同的庫（比如user_base和user_profile在user_db_01，user_account在user_db_02），避免單庫壓力。

（2）水平拆分：按 “行” 拆（把表拆成 “小表”）

核心邏輯：根據 “行的某個字段（叫拆分鍵 / 分片鍵）”，把一個 “大表”（行多的表）拆成多個 “小表”，每個小表存一部分行數據，結構完全相同。

比如：訂單表（order）有 1 億行數據，按 “訂單創建時間”（拆分鍵）水平拆分，拆成order_2022、order_2023、order_2024三個表，分別存 2022 年、2023 年、2024 年的訂單。
再比如：用戶表（user）按 “用戶 id”（拆分鍵）水平拆分，拆成user_01（id 結尾為 1-3）、user_02（id 結尾為 4-6）、user_03（id 結尾為 7-9/0），每個表存 300 萬行數據。
拆分鍵怎么選？
拆分鍵是水平拆分的 “靈魂”，選不好會導致 “數據傾斜”（比如某個小表存了 80% 的數據，其他表只存 20%，白拆了）。核心原則：選 “查詢頻率高、能均勻分配數據” 的字段。
- 常用拆分鍵：時間（訂單、日志）、用戶 ID（用戶相關表）、區域 ID（地域相關表）。
- 避坑：不要選 “查詢少、分布不均勻” 的字段（比如按 “用戶性別” 拆，男女比例可能 1:1，但查詢時很少按性別查，反而要跨表查所有性別數據）。
水平分庫：如果拆后的小表還是在同一個庫，單庫的 IO、CPU 還是會瓶頸，就把小表分到不同庫（比如order_2022在order_db_01，order_2023在order_db_02，order_2024在order_db_03）。

7.2.3 具體實現

分庫分表比讀寫分離復雜（要判斷 “數據存在哪個庫 / 哪個表”“跨庫跨表查詢怎么處理”），必須用中間件實現，主流中間件有：

中間件	核心特點	優點	缺點	適合場景
Sharding-JDBC	輕量級，是 “JDBC 驅動增強”（嵌入在應用里，不用單獨部署服務）	無額外部署成本，性能好，對業務代碼侵入少	不支持跨庫事務（需要自己處理），不適合超大型分布式系統	中小型分布式項目
MyCat	重量級，是 “數據庫代理”（獨立部署服務，應用連 MyCat，MyCat 連數據庫）	支持跨庫事務、分庫分表規則豐富，適合超大型系統	需要單獨部署維護，性能比 Sharding-JDBC 略低	大型分布式項目、傳統架構遷移

舉個例子：用 Sharding-JDBC 實現訂單表水平分表（按時間拆成 2023、2024 表），只需要在配置文件里指定：

拆分表：order拆成order_2023、order_2024；
拆分鍵：create_time（訂單創建時間）；
拆分規則：2023 年的訂單存order_2023，2024 年的存order_2024。
業務代碼里還是寫SELECT * FROM order WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'，中間件會自動轉發到order_2023表查詢。

7.2.4 注意事項

跨庫跨表查詢問題：比如按時間拆分訂單表后，要查 “2023 年 12 月到 2024 年 1 月的訂單”，需要同時查order_2023和order_2024，中間件會自動 “合并結果”（但性能比單表查慢）。解決辦法：盡量按拆分鍵查詢，避免跨表；
跨庫事務問題：比如用戶下單時，要在order表（order_db_01）和payment表（payment_db_01）分別插數據，萬一一個成功一個失敗，會導致數據不一致。解決辦法：用 “最終一致性” 方案（比如消息隊列重試），或中間件支持的分布式事務（如 MyCat 的 XA 事務）；
數據擴容問題：如果拆了 3 個表，后來數據又滿了，需要拆成 5 個表，怎么把原來的數據遷移到新表？解決辦法：提前規劃 “分片策略”（比如用 “一致性哈希”，擴容時只遷移部分數據），或用工具（如 ShardingSphere 的 DataSphere Studio）自動遷移；
備份和運維復雜度：原來備份 1 個庫 1 個表，現在要備份多個庫多個表，運維成本翻倍，需要用自動化運維工具（如 Ansible、Jenkins）。

7.2.5 總結：讀寫分離 vs 分庫分表，怎么選？

對比維度	讀寫分離	分庫分表
解決的核心問題	讀多寫少的性能瓶頸（讀請求太多）	數據量太大的存儲 / 查詢瓶頸（單庫單表撐不住）
數據拆分方式	不拆分庫表，只拆分 “讀寫請求”	拆分庫或表（垂直 / 水平拆分）
適用場景	數據量不大，但讀請求頻繁（如新聞 APP、博客）	數據量超大（單表千萬級 +）（如電商訂單、支付）
組合使用	可以和分庫分表一起用（先分庫分表，再做讀寫分離）	-