隨著業務變得越來越複雜，使用者越來越多，集中式的架構效能會出現巨大的問題，比如系統會越來越慢，而且時不時會當機，所以必須要解決高效能和可用性的問題。這個時候資料庫的優化就顯得尤為重要，在說優化方案前，先分析下資料庫效能瓶頸的原因有哪些；
1.1資料庫效能瓶頸的分析
比如說在高併發的情況下連線數不夠了。或者資料量太大，查詢效率變得越來越低。或者是因為儲存的問題，資料庫所在的機器效能下降了。這些問題，歸根結底都是受到了硬體的限制，比如 CPU，記憶體，磁碟，網路等等。在集中式的架構裡面，我們一般是增加硬體設施來解決這些問題的，比喻換CPU，升級記憶體，擴充套件磁碟，升級頻寬等等。但我們本次要說的不是硬體的優化，作為一個程式設計師，如果只會增加硬體那也沒有啥優越感；言歸正傳，下面來說下資料庫的軟優化方案；
1.2資料庫優化方案對比
1.2.1、重啟
可能有很多朋友覺得這有點搞笑，但重啟真是釋放資源的最好方法；對於很久都沒關閉的資料庫伺服器，重啟會使其釋放資源，導致反應 速度會很多；所以對於夜間伺服器空隙時間長的公司，可以寫一個指令碼，讓資料庫在夜間空隙時進行自動重啟；
1.2.1 SQL 與索引
當 SQL 語句寫得非常複雜，比如關聯的表非常多，條件非常多，查詢所消耗的時間非常長，這樣的一個 SQL 就叫慢 SQL，關於慢SQL我 在去年的文章中有講解，有興趣可以自己去看下。因為 SQL 語句是我們自己編寫的，可控性是最高的，所以第一步就是檢查 SQL。在很多情 況下我們優化的目標是為了用到索引。
1.2.2 表與儲存引擎
如果 SQL 本身沒有什麼大問題，我們接著就要檢查我們查詢的目標，也就是表結構的設計有沒有問題。比如你對於欄位型別和長度的選 擇，或者表結構是不是需要拆分或者合併，不同的表應該選擇什麼儲存引擎，是不是要分割槽等等。
1.2.3 架構
表結構如果也沒有問題，那就要上升到資料庫服務的層面，從架構層面進行優化。因為資料都是在磁碟上儲存，如果加了索引還是很慢，乾脆可以把資料在記憶體裡面快取起來，這個時候可以部署快取伺服器。查詢資料先查快取，沒有再查資料庫，例如（布隆過濾器）。這樣既可以減少資料庫的壓力，又可以提升查詢速度。如果一臺資料庫伺服器承受不了訪問壓力，可以部署叢集做負載均衡。當然這些資料庫節點應該有自動同步的機制。有了主從同步之後，就可以主從複製實現讀寫分離，讓寫的服務都訪問 master 伺服器，讀的請求都訪問從伺服器。有了讀寫分離之後，問題並沒有完全解決：1、只有一個 master，寫的壓力沒有得到分攤；2、所有的節點都儲存相同的資料，在一個節點出現儲存瓶頸的時候，磁碟不夠用了其他的節點也一樣會遇到這個問題。所以這個時候我們要用到分散式環境中一個非常重要的手段：分片，每個節點都只儲存總體資料的一部分，那這個就是我們今天要說分庫分表。分片以後，為了提升可用性，可以再對分片做冗餘。
1.2.4 資料庫配置
如果通過架構層面沒有解決問題，或者機器雖然配置很高但是效能沒有發揮到極致，還可以優化資料庫的配置，比如連線數，緩衝區大小等等。
二、 分庫分表的型別和特點
拆分一共就兩種，一種叫垂直拆分，一種叫水平拆分。
垂直切分：基於表或欄位劃分，表結構不同。我們有單庫的分表，也有多庫的分庫。
水平切分：基於資料劃分，表結構相同，資料不同，也有同庫的水平切分和多庫的切分。
2.1垂直切分
垂直分表有兩種，一種是單庫的，一種是多庫的。欄位太多了，就要拆表，表太多了，就要拆庫。
2.1.1 單庫垂直分表
單庫分表，比如：使用者資訊表，拆分成基本資訊表，聯絡方式表等等。
2.1.2 多庫垂直分表
多庫垂直分表就是把原來儲存在一個庫的不同的表，拆分到不同的資料庫。比喻說當如果資料庫中有一個表的增長速度非常快，當垂直切 分並沒有從根本上解決單庫單表資料量過大的問題。在這個時候，我們還需要對我們的資料做一個水平的切分。這個時候，一個應用需要多個資料庫。
2.2水平切分
水平切分就是按照資料的維度分佈不同的表中，可以是單庫的，也可以是多庫的。
2.2.1 單庫水平分表
這個拿銀行的交易系統講解最容易，銀行的每天交易流水非常大，但是大部分客戶只會查近一年或近一個月的流水單，對於歷史非常長的流水訪問量會少很多，這時就可以對流水錶進行水平拆分了。
2.2.2 多庫水平分表
另一種是多庫的水平分表。比如客戶表，我們拆分到多個庫儲存，表結構是完全一樣的。
2.3分庫分錶帶來的問題
前面說了很多分庫分表的場景及好處，但世間萬物都是有利就有弊；下面就來說下他的弊端。
2.3.1 跨庫關聯查詢
比如在跨庫關聯時，由於要關聯的表是在不同的資料庫，那麼我們肯定不能直接使用 join 的這種方式去做關聯查詢。但我們有幾個解決方案，例如欄位冗餘、mycat等。
2.3.2 分散式事務
如果是在一個資料庫裡面，我們可以用本地事務來控制，但是在不同的資料庫裡面就不行了。這裡必須要出現一個協調者的角色，讓大家統一行動，而且要分成多個階段，一般是先確定都能成功才成功，只要有一個人不能成功，就要全部失敗。
三、 Mycat 概念與配置
3.1 Mycat 介紹與核心概念
Mycat的官網網址：http://www.mycat.org.cn/；mycat執行在應用和資料庫之間，可以當做一個 MySQL 伺服器使用（不論是在工具還是在程式碼或者命令列中都可以直接連線）。實現對 MySQL 資料庫的分庫分表，也可以通過 JDBC 支援其他的資料庫。
Mycat 的關鍵特性：
-
- 可以當做一個 MySQL 資料庫來使用
- 支援 MySQL 之外的資料庫，通過 JDBC 實現
- 解決了我們提到的所有問題，多表 join、分散式事務、全域性序列號、翻頁排序
- 支援 ZK 配置，帶監控 mycat-web（已經停止維護）
- 2.0 已經發布；文件許久沒有更新
3.2 Mycat 配置詳解
我們先從官網下載Mycat包，有各種版本，我為了方便下了win
Mycat 解壓以後有 5 個目錄：
主要的配置檔案 server.xml、schema.xml、rule.xml 和具體的分片配置檔案。
3.2.1 server.xml
包含系統配置資訊。
system 標籤：例如字符集、執行緒數、心跳、分散式事務開關等等。
user 標籤：配置登入使用者和許可權。
<user name="root" defaultAccount="true">
<property name="password">root</property>
<property name="schemas">ghymycat,ljxmycat</property>
<!-- 表級 DML 許可權設定 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
3.2.2 schema.xml
schema 在 MySQL 裡面跟資料庫是等價的。schema.xml 包括邏輯庫、表、分片規則、分片節點和資料來源，可以定義多個 schema。
這裡面有三個主要的標籤（table、dataNode、dataHost）：
<table/>
表名和庫名最好都用小寫
定義了邏輯表，以及邏輯表分佈的節點和分片規則：
<schema name="ghymycat" checkSQLschema="false" sqlMaxLimit="100">
<!--範圍分片表-->
<table name="customer" primaryKey="id" dataNode="103-ghymycat,104-ghymycat,105-ghymycat" rule="auto-sharding-long" />
<!--ER分片表-->
<table name="er_scope" dataNode="103-ghymycat,104-ghymycat,105-ghymycat" rule="mod-long-order" >
<childTable name="er_detail" primaryKey="id" joinKey="er_id" parentKey="er_id"/>
</table>
<table name="mycat_sequence" dataNode="103-ghymycat" autoIncrement="true" primaryKey="id"></table>
</schema>
<schema name="ljxmycat" checkSQLschema="false" sqlMaxLimit="100">
<!--取模分片表-->
<table name="student" primaryKey="sid" dataNode="103-ljxmycat,104-ljxmycat,105-ljxmycat" rule="mod-long" />
<!--非分片表-->
<table name="noshard" primaryKey="id" autoIncrement="true" dataNode="103-ljxmycat" />
<!--全域性表-->
<table name="dict" primaryKey="id" type="global" dataNode="103-ljxmycat,104-ljxmycat,105-ljxmycat" />
<!--單庫分片表-->
<table name="fee" primaryKey="id" subTables="fee2025$1-3" dataNode="103-ljxmycat" rule="sharding-by-month" />
</schema>
|
配置 |
作用 |
|
checkSQLschema |
在查詢 SQL 中去掉邏輯庫名 |
| sqlMaxLimit |
自動加上 limit 控制資料的返回
|
|
primaryKey |
指定該邏輯表對應真實表的主鍵。MyCat 會快取主鍵（通過 primaryKey 屬性配置）與具體 dataNode 的資訊。
primaryKey 當分片規則（rule）使用非主鍵進行分片時，那麼在使用主鍵進行查詢時，MyCat 就會通過快取先確定記錄在哪個 dataNode 上，然後再在該 dataNode 上執行查詢。
如果沒有快取/快取並沒有命中的話，還是會傳送語句給所有的 dataNode。 |
|
dataNode |
資料分片的節點 |
|
autoIncrement |
自增長（全域性序列），true 代表主鍵使用自增長策略 |
|
type |
全域性表：global。其他：不配置 |
<!--資料節點與物理資料庫的對應關係-->
<dataNode name="103-ghymycat" dataHost="host103" database="ghymycat" />
<dataNode name="104-ghymycat" dataHost="host104" database="ghymycat" />
<dataNode name="105-ghymycat" dataHost="host105" database="ghymycat" />
<dataNode name="103-ljxmycat" dataHost="host103" database="ljxmycat" />
<dataNode name="104-ljxmycat" dataHost="host104" database="ljxmycat" />
<dataNode name="105-ljxmycat" dataHost="host105" database="ljxmycat" />
配置物理主機的資訊，readhost 是從屬於 writehost 的。
<dataHost name="host103" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.2.103:3306" user="root" password="root"> </writeHost> </dataHost> <dataHost name="host104" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.2.104:3306" user="root" password="root"> <!-- <readHost host="hostS1"></readHost> --> </writeHost> </dataHost> <dataHost name="host105" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.2.105:3306" user="root" password="root"> </writeHost> </dataHost>
balance：負載的配置，決定 select 語句的負載
writeType：讀寫分離的配置，決定 update、delete、insert 語句的負載
switchType：主從切換配置
3.2.3 rule.xml
定義了分片規則和演算法分片規則：
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
分片演算法：
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
分片配置：
autopartition-long.txt
10001-20000=1 0-10000=0 20001-100000=2
3.3 Mycat 分片驗證
先準備三個資料庫，我是在建了三個虛擬機器，分別在每臺上裝了mysql
--在所有資料庫節點上建立資料庫ghymycat，建立3張表 -- 範圍分片表 CREATE TABLE `scope` ( `id` int(11) DEFAULT NULL, `name` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ER分片表 CREATE TABLE `er_scope` ( `er_id` int(11) NOT NULL , `uid` int(11) DEFAULT NULL , `nums` int(11) DEFAULT NULL, `state` int(2) DEFAULT NULL, `create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '建立時間', `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', PRIMARY KEY (`er_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ER分片表 CREATE TABLE `er_detail` ( `er_id` int(11) NOT NULL, `id` int(11) NOT NULL, `goods_id` int(11) DEFAULT NULL, `price` decimal(10,2) DEFAULT NULL, `is_pay` int(2) DEFAULT NULL, `is_ship` int(2) DEFAULT NULL, `status` int(2) DEFAULT NULL, PRIMARY KEY (`er_id`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 建立表，在三個ljxmycat庫中建立dict及student表 CREATE TABLE `dict` ( `id` int(11) DEFAULT NULL, `param_code` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `param_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE `student` ( `sid` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `qq` varchar(255) DEFAULT NULL, PRIMARY KEY (`sid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 在第一個資料庫ljxmycat節點（103）資料庫建立非分片表 CREATE TABLE `noshard` ( `id` bigint(30) DEFAULT NULL, `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; truncate table noshard; -- 庫內分表 -- 在第一個資料庫ljxmycat節點（103）資料庫建立單庫分片表 CREATE TABLE `fee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; CREATE TABLE `fee20251` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `fee20252` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `fee20253` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) );
啟動mycat
如上圖紅框所示就代表啟動成功，mycat的預設埠是8066，我們用Navicat premium連線成功所會如下圖所示，他會把所有表內容進行一個聚合；
3.3.1 範圍分片
在mycat的ghymycat中執行分片測試資料，scpoe是按範圍進行分配的，分配的規則前面也有配置
-- 範圍分片scope表 INSERT INTO `scope` (`id`, `name`) VALUES (6666, '張三'); INSERT INTO `scope` (`id`, `name`) VALUES (7777, '李四'); INSERT INTO `scope` (`id`, `name`) VALUES (16666, '王五'); INSERT INTO `scope` (`id`, `name`) VALUES (17777, '孫六'); INSERT INTO `scope` (`id`, `name`) VALUES (26666, '王二麻子'); INSERT INTO `scope` (`id`, `name`) VALUES (27777, '趙七')
插入資料完成後我們檢視 ，會發現是分散在三個資料庫上的
3.3.2 取模分片表
ljxmycat庫中的student表，我們從下圖可知，我們配置的模數是3
-- 取模分片表(ljxmycat庫中的student表) -- 測試取模分片（在mycat連線中ljxmycat資料庫中執行） INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (1, '張三', '166669999'); INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (4, '李四', '655556666'); INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (2, '王五', '466669999'); INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (5, '趙六', '265286999'); INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (3, '李大郎', '368828888'); INSERT INTO `student` (`sid`, `name`, `qq`) VALUES (6, '孫子', '516895555');
插入完成後自己可以查下，會發現模餘數0的在第一個接點，餘數1的在第二個節點上，餘數2的在第三個節點上
3.3.3 取模分片（ER 表）
在實際生產環境中我們有些表的資料是存在邏輯的主外來鍵關係的，比如訂單表 er_scope和er_detail，有主外來鍵的配置如下圖
執行插入資料
INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (1, 1000001, 1, 2, '2025-9-23 14:35:37', '2025-9-23 14:35:37'); INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (2, 1000002, 1, 2, '2025-9-24 14:35:37', '2025-9-24 14:35:37'); INSERT INTO `er_scope` (`er_id`, `uid`, `nums`, `state`, `create_time`, `update_time`) VALUES (3, 1000003, 3, 1, '2025-9-25 11:35:49', '2025-9-25 11:35:49'); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (3, 20180001, 85114752, 19.99, 1, 1, 1); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180002, 25411251, 1280.00, 1, 1, 0); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (1, 20180003, 62145412, 288.00, 1, 1, 2); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180004, 21456985, 399.00, 1, 1, 2); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180005, 21457452, 1680.00, 1, 1, 2); INSERT INTO `er_detail` (`er_id`, `id`, `goods_id`, `price`, `is_pay`, `is_ship`, `status`) VALUES (2, 20180006, 65214789, 9999.00, 1, 1, 3);
插入完成後我們會發現模數分佈規則和上一個例子一樣，唯一區別的是，關聯的外來鍵表資料存放會和主表放在同一個庫裡面
3.3.4 全域性表
ljxmycat資料庫，dict 表：全域性表
執行下面語句插入
INSERT INTO `dict` (`id`, `param_code`, `param_name`) VALUES (1, '111', '全域性就是所有庫都有一樣的');
3.3.5 非分片表
ljxmycat資料庫，noshard 表
INSERT INTO `noshard` (`id`, `name`) VALUES (1, '分片的資料');
3.3.6 庫內分表
插入資料
INSERT INTO `fee` (`id`, `create_time`) VALUES (1, '2025-1-1 14:46:19'); INSERT INTO `fee` (`id`, `create_time`) VALUES (2, '2025-2-1 14:46:19'); INSERT INTO `fee` (`id`, `create_time`) VALUES (3, '2025-3-1 14:46:19');
然後自己在對應的物理庫檢視可以看到在一個庫裡分表了
3.4 Mycat 全域性 ID
關於全域性ID這塊網上有篇文章寫的挺全的，如果要配置可以參照文章中配置,網址：https://blog.51cto.com/mynode/1910570