MySQL儲存引擎
MySQL體系結構
-
體系結構的概念
- 任何一套系統當中，每個部件都能起到一定的作用！
-
MySQL的體系結構
- 體系結構詳解
- 客戶端連線
- 支援介面：支援的客戶端連線，例如C、Java、PHP等語言來連線MySQL資料庫
- 第一層：網路連線層
- 連線池：管理、緩衝使用者的連線，執行緒處理等需要快取的需求。
- 例如：當客戶端傳送一個請求連線，會從連線池中獲取一個連線進行使用。
- 第二層：核心服務層
- 管理服務和工具：系統的管理和控制工具，例如備份恢復、複製、叢集等。
- SQL介面：接受SQL命令，並且返回查詢結果。
- 查詢解析器：驗證和解析SQL命令，例如過濾條件、語法結構等。
- 查詢優化器：在執行查詢之前，使用預設的一套優化機制進行優化sql語句
- 快取：如果快取當中有想查詢的資料，則直接將快取中的資料返回。沒有的話再重新查詢！
- 第三層：儲存引擎層
- 外掛式儲存引擎：管理和運算元據的一種機制，包括(儲存資料、如何更新、查詢資料等)
- 第四層：系統檔案層
- 檔案系統：配置檔案、資料檔案、日誌檔案、錯誤檔案、二進位制檔案等等的儲存
- 客戶端連線
MySQL儲存引擎
-
引擎的概念
- 生活中，引擎就是整個機器執行的核心，不同的引擎具備不同的功能。
-
MySQL儲存引擎的概念
- MySQL資料庫使用不同的機制存取表檔案 , 機制的差別在於不同的儲存方式、索引技巧、鎖定水平以及廣泛的不同的功能和能力，在MySQL中 , 將這些不同的技術及配套的功能稱為儲存引擎
- 在關係型資料庫中資料的儲存是以表的形式存進行儲的，所以儲存引擎也可以稱為表型別（即儲存和操作此表的型別）。
- Oracle , SqlServer等資料庫只有一種儲存引擎 , 而MySQL針對不同的需求, 配置MySQL的不同的儲存引擎 , 就會讓資料庫採取了不同的處理資料的方式和擴充套件功能。
- 通過選擇不同的引擎 ,能夠獲取最佳的方案 , 也能夠獲得額外的速度或者功能，提高程式的整體效果。所以瞭解引擎的特性 , 才能貼合我們的需求 , 更好的發揮資料庫的效能。
-
MySQL支援的儲存引擎
- MySQL5.7支援的引擎包括：InnoDB、MyISAM、MEMORY、Archive、Federate、CSV、BLACKHOLE等
- 其中較為常用的有三種：InnoDB、MyISAM、MEMORY
常用引擎的特性對比
- 常用的儲存引擎
- MyISAM儲存引擎
- 訪問快,不支援事務和外來鍵。表結構儲存在.frm檔案中，表資料儲存在.MYD檔案中，索引儲存在.MYI檔案中。
- InnoDB儲存引擎(MySQL5.5版本後預設的儲存引擎)
- 支援事務 ,佔用磁碟空間大 ,支援併發控制。表結構儲存在.frm檔案中，如果是共享表空間，資料和索引儲存在 innodb_data_home_dir 和 innodb_data_file_path定義的表空間中，可以是多個檔案。如果是多表空間儲存，每個表的資料和索引單獨儲存在 .ibd 中。
- MEMORY儲存引擎
- 記憶體儲存 , 速度快 ,不安全 ,適合小量快速訪問的資料。表結構儲存在.frm中。
- MyISAM儲存引擎
- 特性對比
| 特性 | MyISAM | InnoDB | MEMORY |
|---|---|---|---|
| 儲存限制 | 有(平臺對檔案系統大小的限制) | 64TB | 有(平臺的記憶體限制) |
| 事務安全 | 不支援 | 支援 | 不支援 |
| 鎖機制 | 表鎖 | 表鎖/行鎖 | 表鎖 |
| B+Tree索引 | 支援 | 支援 | 支援 |
| 雜湊索引 | 不支援 | 不支援 | 支援 |
| 全文索引 | 支援 | 支援 | 不支援 |
| 叢集索引 | 不支援 | 支援 | 不支援 |
| 資料索引 | 不支援 | 支援 | 支援 |
| 資料快取 | 不支援 | 支援 | N/A |
| 索引快取 | 支援 | 支援 | N/A |
| 資料可壓縮 | 支援 | 不支援 | 不支援 |
| 空間使用 | 低 | 高 | N/A |
| 記憶體使用 | 低 | 高 | 中等 |
| 批量插入速度 | 高 | 低 | 高 |
| 外來鍵 | 不支援 | 支援 | 不支援 |
引擎的操作
- 查詢資料庫支援的引擎
-- 標準語法
SHOW ENGINES;
-- 查詢資料庫支援的儲存引擎
SHOW ENGINES;
-- 表含義:
- support : 指伺服器是否支援該儲存引擎
- transactions : 指儲存引擎是否支援事務
- XA : 指儲存引擎是否支援分散式事務處理
- Savepoints : 指儲存引擎是否支援儲存點
- 查詢某個資料庫中所有資料表的引擎
-- 標準語法
SHOW TABLE STATUS FROM 資料庫名稱;
-- 檢視db9資料庫所有表的儲存引擎
SHOW TABLE STATUS FROM db9;
- 查詢某個資料庫中某個資料表的引擎
-- 標準語法
SHOW TABLE STATUS FROM 資料庫名稱 WHERE NAME = '資料表名稱';
-- 檢視db9資料庫中stu_score表的儲存引擎
SHOW TABLE STATUS FROM db9 WHERE NAME = 'stu_score';
- 建立資料表，指定儲存引擎
-- 標準語法
CREATE TABLE 表名(
列名,資料型別,
...
)ENGINE = 引擎名稱;
-- 建立db11資料庫
CREATE DATABASE db11;
-- 使用db11資料庫
USE db11;
-- 建立engine_test表，指定儲存引擎為MyISAM
CREATE TABLE engine_test(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10)
)ENGINE = MYISAM;
-- 查詢engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';
- 修改表的儲存引擎
-- 標準語法
ALTER TABLE 表名 ENGINE = 引擎名稱;
-- 修改engine_test表的引擎為InnoDB
ALTER TABLE engine_test ENGINE = INNODB;
-- 查詢engine_test表的引擎
SHOW TABLE STATUS FROM db11 WHERE NAME = 'engine_test';
總結：引擎的選擇
- MyISAM ：由於MyISAM不支援事務、不支援外來鍵、支援全文檢索和表級鎖定，讀寫相互阻塞，讀取速度快，節約資源，所以如果應用是以查詢操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性、併發性要求不是很高，那麼選擇這個儲存引擎是非常合適的。
- InnoDB : 是MySQL的預設儲存引擎， 由於InnoDB支援事務、支援外來鍵、行級鎖定 ，支援所有輔助索引(5.5.5後不支援全文檢索)，高快取，所以用於對事務的完整性有比較高的要求，在併發條件下要求資料的一致性，讀寫頻繁的操作，那麼InnoDB儲存引擎是比較合適的選擇，比如BBS、計費系統、充值轉賬等
- MEMORY：將所有資料儲存在RAM中，在需要快速定位記錄和其他類似資料環境下，可以提供更快的訪問。MEMORY的缺陷就是對錶的大小有限制，太大的表無法快取在記憶體中，其次是要確保表的資料可以恢復，資料庫異常終止後表中的資料是可以恢復的。MEMORY表通常用於更新不太頻繁的小表，用以快速得到訪問結果。
- 總結：針對不同的需求場景，來選擇最適合的儲存引擎即可！如果不確定、則使用資料庫預設的儲存引擎！
MySQL索引
索引的概念
- 我們之前學習過集合，其中的ArrayList集合的特點之一就是有索引。那麼有索引會帶來哪些好處呢？
- 沒錯，查詢資料快！我們可以通過索引來快速查詢到想要的資料。那麼對於我們的MySQL資料庫中的索引功能也是類似的！
- MySQL資料庫中的索引：是幫助MySQL高效獲取資料的一種資料結構！所以，索引的本質就是資料結構。
- 在表資料之外，資料庫系統還維護著滿足特定查詢演算法的資料結構，這些資料結構以某種方式指向資料， 這樣就可以在這些資料結構上實現高階查詢演算法，這種資料結構就是索引。
- 一張資料表，用於儲存資料。 一個索引配置檔案，用於儲存索引，每個索引都去指向了某一個資料(表格演示)
- 舉例，無索引和有索引的查詢原理
索引的分類
- 功能分類
- 普通索引： 最基本的索引，它沒有任何限制。
- 唯一索引：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值組合必須唯一。
- 主鍵索引：一種特殊的唯一索引，不允許有空值。一般在建表時同時建立主鍵索引。
- 組合索引：顧名思義，就是將單列索引進行組合。
- 外來鍵索引：只有InnoDB引擎支援外來鍵索引，用來保證資料的一致性、完整性和實現級聯操作。
- 全文索引：快速匹配全部文件的方式。InnoDB引擎5.6版本後才支援全文索引。MEMORY引擎不支援。
- 結構分類
- B+Tree索引 ：MySQL使用最頻繁的一個索引資料結構，是InnoDB和MyISAM儲存引擎預設的索引型別。
- Hash索引 : MySQL中Memory儲存引擎預設支援的索引型別。
索引的操作
- 資料準備
-- 建立db12資料庫
CREATE DATABASE db12;
-- 使用db12資料庫
USE db12;
-- 建立student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
score INT
);
-- 新增資料
INSERT INTO student VALUES (NULL,'張三',23,98),(NULL,'李四',24,95),
(NULL,'王五',25,96),(NULL,'趙六',26,94),(NULL,'周七',27,99);
- 建立索引
- 注意：如果一個表中有一列是主鍵，那麼就會預設為其建立主鍵索引！(主鍵列不需要單獨建立索引)
-- 標準語法
CREATE [UNIQUE|FULLTEXT] INDEX 索引名稱
[USING 索引型別] -- 預設是B+TREE
ON 表名(列名...);
-- 為student表中姓名列建立一個普通索引
CREATE INDEX idx_name ON student(NAME);
-- 為student表中年齡列建立一個唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);
- 檢視索引
-- 標準語法
SHOW INDEX FROM 表名;
-- 檢視student表中的索引
SHOW INDEX FROM student;
- alter語句新增索引
-- 普通索引
ALTER TABLE 表名 ADD INDEX 索引名稱(列名);
-- 組合索引
ALTER TABLE 表名 ADD INDEX 索引名稱(列名1,列名2,...);
-- 主鍵索引
ALTER TABLE 表名 ADD PRIMARY KEY(主鍵列名);
-- 外來鍵索引(新增外來鍵約束，就是外來鍵索引)
ALTER TABLE 表名 ADD CONSTRAINT 外來鍵名 FOREIGN KEY (本表外來鍵列名) REFERENCES 主表名(主鍵列名);
-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名稱(列名);
-- 全文索引(mysql只支援文字型別)
ALTER TABLE 表名 ADD FULLTEXT 索引名稱(列名);
-- 為student表中name列新增全文索引
ALTER TABLE student ADD FULLTEXT idx_fulltext_name(name);
-- 檢視student表中的索引
SHOW INDEX FROM student;
- 刪除索引
-- 標準語法
DROP INDEX 索引名稱 ON 表名;
-- 刪除student表中的idx_score索引
DROP INDEX idx_score ON student;
-- 檢視student表中的索引
SHOW INDEX FROM student;
索引效率的測試
-- 建立product商品表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT, -- 商品id
NAME VARCHAR(10), -- 商品名稱
price INT -- 商品價格
);
-- 定義儲存函式，生成長度為10的隨機字串並返回
DELIMITER $
CREATE FUNCTION rand_string()
RETURNS VARCHAR(255)
BEGIN
DECLARE big_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE small_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
SET small_str =CONCAT(small_str,SUBSTRING(big_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN small_str;
END$
DELIMITER ;
-- 定義儲存過程，新增100萬條資料到product表中
DELIMITER $
CREATE PROCEDURE pro_test()
BEGIN
DECLARE num INT DEFAULT 1;
WHILE num <= 1000000 DO
INSERT INTO product VALUES (NULL,rand_string(),num);
SET num = num + 1;
END WHILE;
END$
DELIMITER ;
-- 呼叫儲存過程
CALL pro_test();
-- 查詢總記錄條數
SELECT COUNT(*) FROM product;
-- 查詢product表的索引
SHOW INDEX FROM product;
-- 查詢name為OkIKDLVwtG的資料 (0.049)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
-- 通過id列查詢OkIKDLVwtG的資料 (1毫秒)
SELECT * FROM product WHERE id=999998;
-- 為name列新增索引
ALTER TABLE product ADD INDEX idx_name(NAME);
-- 查詢name為OkIKDLVwtG的資料 (0.001)
SELECT * FROM product WHERE NAME='OkIKDLVwtG';
/*
範圍查詢
*/
-- 查詢價格為800~1000之間的所有資料 (0.052)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
/*
排序查詢
*/
-- 查詢價格為800~1000之間的所有資料,降序排列 (0.083)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
-- 為price列新增索引
ALTER TABLE product ADD INDEX idx_price(price);
-- 查詢價格為800~1000之間的所有資料 (0.011)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000;
-- 查詢價格為800~1000之間的所有資料,降序排列 (0.001)
SELECT * FROM product WHERE price BETWEEN 800 AND 1000 ORDER BY price DESC;
索引的實現原則
- 索引是在MySQL的儲存引擎中實現的，所以每種儲存引擎的索引不一定完全相同，也不是所有的引擎支援所有的索引型別。這裡我們主要介紹InnoDB引擎的實現的B+Tree索引。
- B+Tree是一種樹型資料結構，是B-Tree的變種。通常使用在資料庫和作業系統中的檔案系統，特點是能夠保持資料穩定有序。我們逐步的來了解一下。
磁碟儲存
- 系統從磁碟讀取資料到記憶體時是以磁碟塊（block）為基本單位的
- 位於同一個磁碟塊中的資料會被一次性讀取出來，而不是需要什麼取什麼。
- InnoDB儲存引擎中有頁（Page）的概念，頁是其磁碟管理的最小單位。InnoDB儲存引擎中預設每個頁的大小為16KB。
- InnoDB引擎將若干個地址連線磁碟塊，以此來達到頁的大小16KB，在查詢資料時如果一個頁中的每條資料都能有助於定位資料記錄的位置，這將會減少磁碟I/O次數，提高查詢效率。
BTree
- BTree結構的資料可以讓系統高效的找到資料所在的磁碟塊。為了描述BTree，首先定義一條記錄為一個二元組[key, data] ，key為記錄的鍵值，對應表中的主鍵值，data為一行記錄中除主鍵外的資料。對於不同的記錄，key值互不相同。BTree中的每個節點根據實際情況可以包含大量的關鍵字資訊和分支，如下圖所示為一個3階的BTree：
- 根據圖中結構顯示，每個節點佔用一個盤塊的磁碟空間，一個節點上有兩個升序排序的關鍵字和三個指向子樹根節點的指標，指標儲存的是子節點所在磁碟塊的地址。兩個關鍵詞劃分成的三個範圍域對應三個指標指向的子樹的資料的範圍域。以根節點為例，關鍵字為17和35，P1指標指向的子樹的資料範圍為小於17，P2指標指向的子樹的資料範圍為17~35，P3指標指向的子樹的資料範圍為大於35。
查詢順序：
模擬查詢15的過程 :
1.根節點找到磁碟塊1，讀入記憶體。【磁碟I/O操作第1次】
比較關鍵字15在區間（<17），找到磁碟塊1的指標P1。
2.P1指標找到磁碟塊2，讀入記憶體。【磁碟I/O操作第2次】
比較關鍵字15在區間（>12），找到磁碟塊2的指標P3。
3.P3指標找到磁碟塊7，讀入記憶體。【磁碟I/O操作第3次】
在磁碟塊7中找到關鍵字15。
-- 分析上面過程，發現需要3次磁碟I/O操作，和3次記憶體查詢操作。
-- 由於記憶體中的關鍵字是一個有序表結構，可以利用二分法查詢提高效率。而3次磁碟I/O操作是影響整個BTree查詢效率的決定因素。BTree使用較少的節點個數，使每次磁碟I/O取到記憶體的資料都發揮了作用，從而提高了查詢效率。
B+Tree
- B+Tree是在BTree基礎上的一種優化，使其更適合實現外儲存索引結構，InnoDB儲存引擎就是用B+Tree實現其索引結構。
- 從上一節中的BTree結構圖中可以看到每個節點中不僅包含資料的key值，還有data值。而每一個頁的儲存空間是有限的，如果data資料較大時將會導致每個節點（即一個頁）能儲存的key的數量很小，當儲存的資料量很大時同樣會導致B-Tree的深度較大，增大查詢時的磁碟I/O次數，進而影響查詢效率。在B+Tree中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只儲存key值資訊，這樣可以大大加大每個節點儲存的key值數量，降低B+Tree的高度。
- B+Tree相對於BTree區別：
- 非葉子節點只儲存鍵值資訊。
- 所有葉子節點之間都有一個連線指標。
- 資料記錄都存放在葉子節點中。
- 將上一節中的BTree優化，由於B+Tree的非葉子節點只儲存鍵值資訊，假設每個磁碟塊能儲存4個鍵值及指標資訊，則變成B+Tree後其結構如下圖所示：
通常在B+Tree上有兩個頭指標，一個指向根節點，另一個指向關鍵字最小的葉子節點，而且所有葉子節點（即資料節點）之間是一種鏈式環結構。因此可以對B+Tree進行兩種查詢運算：
- 【有範圍】對於主鍵的範圍查詢和分頁查詢
- 【有順序】從根節點開始，進行隨機查詢
實際情況中每個節點可能不能填充滿，因此在資料庫中，B+Tree的高度一般都在24層。MySQL的InnoDB儲存引擎在設計時是將根節點常駐記憶體的，也就是說查詢某一鍵值的行記錄時最多隻需要13次磁碟I/O操作。
總結：索引的設計原則
索引的設計可以遵循一些已有的原則，建立索引的時候請儘量考慮符合這些原則，便於提升索引的使用效率，更高效的使用索引。
- 建立索引時的原則
- 對查詢頻次較高，且資料量比較大的表建立索引。
- 使用唯一索引，區分度越高，使用索引的效率越高。
- 索引欄位的選擇，最佳候選列應當從where子句的條件中提取，如果where子句中的組合比較多，那麼應當挑選最常用、過濾效果最好的列的組合。
- 使用短索引，索引建立之後也是使用硬碟來儲存的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的欄位總長度比較短，那麼在給定大小的儲存塊內可以儲存更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率。
- 索引可以有效的提升查詢資料的效率，但索引數量不是多多益善，索引越多，維護索引的代價自然也就水漲船高。對於插入、更新、刪除等DML操作比較頻繁的表來說，索引過多，會引入相當高的維護代價，降低DML操作的效率，增加相應操作的時間消耗。另外索引過多的話，MySQL也會犯選擇困難病，雖然最終仍然會找到一個可用的索引，但無疑提高了選擇的代價。
- 聯合索引的特點
在mysql建立聯合索引時會遵循最左字首匹配的原則，即最左優先，在檢索資料時從聯合索引的最左邊開始匹配，
對列name列、address和列phone列建一個聯合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
聯合索引index_three實際建立了(name)、(name,address)、(name,address,phone)三個索引。所以下面的三個SQL語句都可以命中索引。
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三';
SELECT * FROM user WHERE name = '張三' AND address = '北京';
SELECT * FROM user WHERE name = '張三';
上面三個查詢語句執行時會依照最左字首匹配原則，檢索時分別會使用索引
(name,address,phone)
(name,address)
(name)
進行資料匹配。
索引的欄位可以是任意順序的，如：
-- 優化器會幫助我們調整順序，下面的SQL語句都可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '張三';
Mysql的優化器會幫助我們調整where條件中的順序，以匹配我們建立的索引。
聯合索引中最左邊的列不包含在條件查詢中，所以根據上面的原則，下面的SQL語句就不會命中索引。
-- 聯合索引中最左邊的列不包含在條件查詢中，下面的SQL語句就不會命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345';
MySQL鎖
鎖的概念
-
之前我們學習過多執行緒，多執行緒當中如果想保證資料的準確性是如何實現的呢？沒錯，通過同步實現。同步就相當於是加鎖。加了鎖以後有什麼好處呢？當一個執行緒真正在運算元據的時候，其他執行緒只能等待。當一個執行緒執行完畢後，釋放鎖。其他執行緒才能進行操作！
-
那麼我們的MySQL資料庫中的鎖的功能也是類似的。在我們學習事務的時候，講解過事務的隔離性，可能會出現髒讀、不可重複讀、幻讀的問題，當時我們的解決方式是通過修改事務的隔離級別來控制，但是資料庫的隔離級別呢我們並不推薦修改。所以，鎖的作用也可以解決掉之前的問題！
-
鎖機制 : 資料庫為了保證資料的一致性，而使用各種共享的資源在被併發訪問時變得有序所設計的一種規則。
-
舉例，在電商網站購買商品時，商品表中只存有1個商品，而此時又有兩個人同時購買，那麼誰能買到就是一個關鍵的問題。
這裡會用到事務進行一系列的操作：
- 先從商品表中取出物品的資料
- 然後插入訂單
- 付款後，再插入付款表資訊
- 更新商品表中商品的數量
以上過程中，使用鎖可以對商品數量資料資訊進行保護，實現隔離，即只允許第一位使用者完成整套購買流程，而其他使用者只能等待，這樣就解決了併發中的矛盾問題。
-
在資料庫中，資料是一種供許多使用者共享訪問的資源，如何保證資料併發訪問的一致性、有效性，是所有資料庫必須解決的一個問題，MySQL由於自身架構的特點，在不同的儲存引擎中，都設計了面對特定場景的鎖定機制，所以引擎的差別，導致鎖機制也是有很大差別的。
鎖的分類
- 按操作分類：
- 共享鎖：也叫讀鎖。針對同一份資料，多個事務讀取操作可以同時加鎖而不互相影響 ，但是不能修改資料記錄。
- 排他鎖：也叫寫鎖。當前的操作沒有完成前,會阻斷其他操作的讀取和寫入
- 按粒度分類：
- 表級鎖：操作時，會鎖定整個表。開銷小，加鎖快；不會出現死鎖；鎖定力度大，發生鎖衝突概率高，併發度最低。偏向於MyISAM儲存引擎！
- 行級鎖：操作時，會鎖定當前操作行。開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的概率低，併發度高。偏向於InnoDB儲存引擎！
- 頁級鎖：鎖的粒度、發生衝突的概率和加鎖的開銷介於表鎖和行鎖之間，會出現死鎖，併發效能一般。
- 按使用方式分類：
- 悲觀鎖：每次查詢資料時都認為別人會修改，很悲觀，所以查詢時加鎖。
- 樂觀鎖：每次查詢資料時都認為別人不會修改，很樂觀，但是更新時會判斷一下在此期間別人有沒有去更新這個資料
- 不同儲存引擎支援的鎖
| 儲存引擎 | 表級鎖 | 行級鎖 | 頁級鎖 |
|---|---|---|---|
| MyISAM | 支援 | 不支援 | 不支援 |
| InnoDB | 支援 | 支援 | 不支援 |
| MEMORY | 支援 | 不支援 | 不支援 |
| BDB | 支援 | 不支援 | 支援 |
演示InnoDB鎖
- 資料準備
-- 建立db13資料庫
CREATE DATABASE db13;
-- 使用db13資料庫
USE db13;
-- 建立student表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
age INT,
score INT
);
-- 新增資料
INSERT INTO student VALUES (NULL,'張三',23,99),(NULL,'李四',24,95),
(NULL,'王五',25,98),(NULL,'趙六',26,97);
- 共享鎖
-- 標準語法
SELECT語句 LOCK IN SHARE MODE;
-- 視窗1
/*
共享鎖：資料可以被多個事務查詢，但是不能修改
*/
-- 開啟事務
START TRANSACTION;
-- 查詢id為1的資料記錄。加入共享鎖
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查詢分數為99分的資料記錄。加入共享鎖
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
-- 提交事務
COMMIT;
-- 視窗2
-- 開啟事務
START TRANSACTION;
-- 查詢id為1的資料記錄(普通查詢，可以查詢)
SELECT * FROM student WHERE id=1;
-- 查詢id為1的資料記錄，並加入共享鎖(可以查詢。共享鎖和共享鎖相容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 修改id為1的姓名為張三三(不能修改，會出現鎖的情況。只有視窗1提交事務後，才能修改成功)
UPDATE student SET NAME='張三三' WHERE id = 1;
-- 修改id為2的姓名為李四四(修改成功，InnoDB引擎預設是行鎖)
UPDATE student SET NAME='李四四' WHERE id = 2;
-- 修改id為3的姓名為王五五(注意：InnoDB引擎如果不採用帶索引的列。則會提升為表鎖)
UPDATE student SET NAME='王五五' WHERE id = 3;
-- 提交事務
COMMIT;
- 排他鎖
-- 標準語法
SELECT語句 FOR UPDATE;
-- 視窗1
/*
排他鎖：加鎖的資料，不能被其他事務加鎖查詢或修改
*/
-- 開啟事務
START TRANSACTION;
-- 查詢id為1的資料記錄，並加入排他鎖
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 提交事務
COMMIT;
-- 視窗2
-- 開啟事務
START TRANSACTION;
-- 查詢id為1的資料記錄(普通查詢沒問題)
SELECT * FROM student WHERE id=1;
-- 查詢id為1的資料記錄，並加入共享鎖(不能查詢。因為排他鎖不能和其他鎖共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查詢id為1的資料記錄，並加入排他鎖(不能查詢。因為排他鎖不能和其他鎖共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 修改id為1的姓名為張三(不能修改，會出現鎖的情況。只有視窗1提交事務後，才能修改成功)
UPDATE student SET NAME='張三' WHERE id=1;
-- 提交事務
COMMIT;
- 注意：鎖的相容性
- 共享鎖和共享鎖 相容
- 共享鎖和排他鎖 衝突
- 排他鎖和排他鎖 衝突
- 排他鎖和共享鎖 衝突
演示MyISAM鎖
- 資料準備
-- 建立product表
CREATE TABLE product(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
price INT
)ENGINE = MYISAM; -- 指定儲存引擎為MyISAM
-- 新增資料
INSERT INTO product VALUES (NULL,'華為手機',4999),(NULL,'小米手機',2999),
(NULL,'蘋果',8999),(NULL,'中興',1999);
- 讀鎖
-- 標準語法
-- 加鎖
LOCK TABLE 表名 READ;
-- 解鎖(將當前會話所有的表進行解鎖)
UNLOCK TABLES;
-- 視窗1
/*
讀鎖：所有連線只能讀取資料，不能修改
*/
-- 為product表加入讀鎖
LOCK TABLE product READ;
-- 查詢product表(查詢成功)
SELECT * FROM product;
-- 修改華為手機的價格為5999(修改失敗)
UPDATE product SET price=5999 WHERE id=1;
-- 解鎖
UNLOCK TABLES;
-- 視窗2
-- 查詢product表(查詢成功)
SELECT * FROM product;
-- 修改華為手機的價格為5999(不能修改，視窗1解鎖後才能修改成功)
UPDATE product SET price=5999 WHERE id=1;
- 寫鎖
-- 標準語法
-- 加鎖
LOCK TABLE 表名 WRITE;
-- 解鎖(將當前會話所有的表進行解鎖)
UNLOCK TABLES;
-- 視窗1
/*
寫鎖：其他連線不能查詢和修改資料
*/
-- 為product表新增寫鎖
LOCK TABLE product WRITE;
-- 查詢product表(查詢成功)
SELECT * FROM product;
-- 修改小米手機的金額為3999(修改成功)
UPDATE product SET price=3999 WHERE id=2;
-- 解鎖
UNLOCK TABLES;
-- 視窗2
-- 查詢product表(不能查詢。只有視窗1解鎖後才能查詢成功)
SELECT * FROM product;
-- 修改小米手機的金額為2999(不能修改。只有視窗1解鎖後才能修改成功)
UPDATE product SET price=2999 WHERE id=2;
演示悲觀鎖和樂觀鎖
-
悲觀鎖的概念
- 就是很悲觀，它對於資料被外界修改的操作持保守態度，認為資料隨時會修改。
- 整個資料處理中需要將資料加鎖。悲觀鎖一般都是依靠關係型資料庫提供的鎖機制。
- 我們之前所學的行鎖，表鎖不論是讀寫鎖都是悲觀鎖。
-
樂觀鎖的概念
- 就是很樂觀，每次自己運算元據的時候認為沒有人會來修改它，所以不去加鎖。
- 但是在更新的時候會去判斷在此期間資料有沒有被修改。
- 需要使用者自己去實現，不會發生併發搶佔資源，只有在提交操作的時候檢查是否違反資料完整性。
-
悲觀鎖和樂觀鎖使用前提
- 對於讀的操作遠多於寫的操作的時候，這時候一個更新操作加鎖會阻塞所有的讀取操作，降低了吞吐量。最後還要釋放鎖，鎖是需要一些開銷的，這時候可以選擇樂觀鎖。
- 如果是讀寫比例差距不是非常大或者系統沒有響應不及時，吞吐量瓶頸的問題，那就不要去使用樂觀鎖，它增加了複雜度，也帶來了業務額外的風險。這時候可以選擇悲觀鎖。
-
樂觀鎖的實現方式
-
版本號
- 給資料表中新增一個version列，每次更新後都將這個列的值加1。
- 讀取資料時，將版本號讀取出來，在執行更新的時候，比較版本號。
- 如果相同則執行更新，如果不相同，說明此條資料已經發生了變化。
- 使用者自行根據這個通知來決定怎麼處理，比如重新開始一遍，或者放棄本次更新。
-- 建立city表 CREATE TABLE city( id INT PRIMARY KEY AUTO_INCREMENT, -- 城市id NAME VARCHAR(20), -- 城市名稱 VERSION INT -- 版本號 ); -- 新增資料 INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'廣州',1),(NULL,'深圳',1); -- 修改北京為北京市 -- 1.查詢北京的version SELECT VERSION FROM city WHERE NAME='北京'; -- 2.修改北京為北京市，版本號+1。並對比版本號 UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1; -
時間戳
- 和版本號方式基本一樣，給資料表中新增一個列，名稱無所謂，資料型別需要是timestamp
- 每次更新後都將最新時間插入到此列。
- 讀取資料時，將時間讀取出來，在執行更新的時候，比較時間。
- 如果相同則執行更新，如果不相同，說明此條資料已經發生了變化。
-
鎖的總結
-
表鎖和行鎖
- 行鎖：鎖的粒度更細，加行鎖的效能損耗較大。併發處理能力較高。InnoDB引擎預設支援！
- 表鎖：鎖的粒度較粗，加表鎖的效能損耗較小。併發處理能力較低。InnoDB、MyISAM引擎支援！
-
InnoDB鎖優化建議
-
儘量通過帶索引的列來完成資料查詢，從而避免InnoDB無法加行鎖而升級為表鎖。
-
合理設計索引，索引要儘可能準確，儘可能的縮小鎖定範圍，避免造成不必要的鎖定。
-
儘可能減少基於範圍的資料檢索過濾條件。
-
儘量控制事務的大小，減少鎖定的資源量和鎖定時間長度。
-
在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率。
-
對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖的產生。
-
叢集
叢集的概念
- 如今隨著網際網路的發展，資料的量級也是成指數的增長，從GB到TB到PB。對資料的各種操作也是愈加的困難，傳統的關係型資料庫已經無法滿足快速查詢與插入資料的需求。一臺資料庫伺服器已經無法滿足海量資料的儲存需求，所以由多臺資料庫構成的資料庫叢集成了必然的方式。不過，為了保證資料的一致性，查詢效率等，同時又要解決多臺伺服器間的通訊、負載均衡等問題。
- MyCat是一款資料庫叢集軟體，是阿里曾經開源的知名產品——Cobar，簡單的說，MyCAT就是：一個新穎的資料庫中介軟體產品支援MySQL叢集，提供高可用性資料分片叢集。你可以像使用mysql一樣使用mycat。對於開發人員來說根本感覺不到mycat的存在。MyCat不單單是支援MySQL，像常用的關係型資料庫Oracle、SqlServer都支援。
叢集的原理
- 我們來說個例子，大海撈針和一個水瓶裡撈針，毋庸置疑水瓶裡一定能更快找到針，因為它需要檢索的範圍更小。資料庫叢集也是如此原理，我們可以將一個資料量為300G的資料庫資料平均拆分成3部分，每個資料庫中只儲存100G資料，此時使用者搜尋，先經過我們中間代理層，中間代理層同時發出3個請求執行查詢，比如第1臺返回100條資料，耗時3秒，第2臺返回200條資料，耗時3秒，第3臺返回500條資料，耗時3秒，此時中介軟體只需要在800條記錄中進行篩選，即可檢索出使用者要的結果，此時耗時其實一共只有3秒，因為每臺機器做運算的時候，都是同時執行。如果我們此時直接在300G的資料庫查詢，耗時10秒，那使用中介軟體進行叢集的效率就非常明顯
- MyCat的實現流程和這個流程大致相似。MyCat自身不儲存資料，但使用者每次連結資料庫的時候，直接連線MyCat即可.所以我們MyCat自身其實就是個邏輯資料庫，它自身還有表結構，表結構叫邏輯表。
Mycat環境搭建
Mycat下載和安裝
-
官網：
http://www.mycat.io/
-
下載地址 :
http://dl.mycat.io/
-
選擇1.6.7.1的版本，下載到D盤，安裝包入下圖：
-
上傳：使用SecureCRT的SFTP命令，將檔案傳送到Linux虛擬機器root目錄下：
sftp> put D:\Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -
解壓：解壓mycat.tar.gz並檢視
tar -zxvf mycat.tar.gz cd mycat ll
-
授權：設定mycat許可權
chmod -R 777 mycat -
環境變數：配置環境變數
vi /etc/profile // 新增 export MYCAT_HOME=/root/mycat // 使環境變數生效 source /etc/profile -
啟動mycat
// 進入bin目錄 [root@localhost]# cd /root/mycat/bin // 執行啟動命令 [root@localhost bin]# ./mycat start -
檢視：檢測埠監聽狀況，Mycat的埠號是8066
[root@localhost bin]# netstat -ant|grep 8066
-
連線：使用SQLYog連線Mycat
-
連線後顯示：
環境準備
- 配置模型
- 克隆虛擬機器
-
修改配置網路卡
- 在第二個虛擬機器中，生成全新mac地址
- 重啟網路
// 重啟網路 service network restart //檢視ip地址 ip addr -
修改mysql配置檔案,更改uuid
- 在第二個伺服器上，修改mysql的uuid
// 編輯配置檔案 vi /var/lib/mysql/auto.cnf // 將server-uuid更改 -
啟動MySQL並檢視
//將兩臺伺服器的防火牆關閉
systemctl stop firewalld
//啟動兩臺伺服器的mysql
service mysqld restart
//啟動兩臺伺服器的mycat
cd /root/mycat/bin
./mycat restart
//檢視監聽埠
netstat -ant|grep 3306
netstat -ant|grep 8066
//使用sqlyog測試連線
主從複製
-
主從複製的概念
- 為了使用Mycat進行讀寫分離，我們先要配置MySQL資料庫的主從複製。
- 從伺服器自動同步主伺服器的資料，從而達到資料一致。
- 進而，我們可以寫操作時，只操作主伺服器，而讀操作，就可以操作從伺服器了。
- 原理：主伺服器在處理資料時，生成binlog日誌，通過對日誌的備份，實現從伺服器的資料同步。
-
主伺服器的配置
- 在第一個伺服器上，編輯mysql配置檔案
// 編輯mysql配置檔案 vi /etc/my.cnf //在[mysqld]下面加上： log-bin=mysql-bin # 開啟複製操作 server-id=1 # master is 1 innodb_flush_log_at_trx_commit=1 sync_binlog=1- 登入mysql，建立使用者並授權
// 登入mysql mysql -u root -p // 去除密碼許可權 SET GLOBAL validate_password_policy=0; SET GLOBAL validate_password_length=1; // 建立使用者 CREATE USER 'user'@'%' IDENTIFIED BY 'gonghr'; // 授權 GRANT ALL ON *.* TO 'user'@'%';- 重啟mysql服務，登入mysql服務
// 重啟mysql service mysqld restart // 登入mysql mysql -u root -p- 檢視主伺服器的配置
// 檢視主伺服器配置 show master status;
-
從伺服器的配置
- 在第二個伺服器上，編輯mysql配置檔案
// 編輯mysql配置檔案 vi /etc/my.cnf // 在[mysqld]下面加上： server-id=2- 登入mysql
// 登入mysql mysql -u root -p // 執行 use mysql; drop table slave_master_info; drop table slave_relay_log_info; drop table slave_worker_info; drop table innodb_index_stats; drop table innodb_table_stats; source /usr/share/mysql/mysql_system_tables.sql;- 重啟mysql，重新登入，配置從節點
// 重啟mysql service mysqld restart // 重新登入mysql mysql -u root -p // 執行 change master to master_host='主伺服器ip地址',master_port=3306,master_user='user',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=4642;- 重啟mysql，重新登入，開啟從節點
// 重啟mysql service mysqld restart // 重新登入mysql mysql -u root -p // 開啟從節點 start slave; // 查詢結果 show slave status\G; //Slave_IO_Running和Slave_SQL_Running都為yes才表示同步成功。
-
測試
- sqlyog連線主伺服器
-- 主伺服器建立db1資料庫,從伺服器會自動同步 CREATE DATABASE db1;- sqlyog連線從伺服器
-- 從伺服器建立db2資料庫,主伺服器不會自動同步 CREATE DATABASE db2; -
啟動失敗的解決方案
啟動失敗：Slave_IO_Running為 NO
方法一:重置slave
slave stop;
reset slave;
start slave ;
方法二:重設同步日誌檔案及讀取位置
slave stop;
change master to master_log_file=’mysql-bin.000001’, master_log_pos=1;
start slave ;
讀寫分離
-
讀寫分離的概念
- 寫操作只寫入主伺服器，讀操作讀取從伺服器。
-
在主伺服器上修改server.xml
- user標籤主要用於定義登入mycat的使用者和許可權。如上面定義使用者名稱mycat和密碼123456，該使用者可以訪問的schema的HEIMADB邏輯庫。
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">HEIMADB</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>
- 在主伺服器上修改schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 主伺服器進行寫操作 -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- 從伺服器負責讀操作 -->
<readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
-
配置詳解
-
schema標籤邏輯庫的概念和mysql資料庫中Datebase的概念相同，我們在查詢這兩個邏輯庫中的表的時候，需要切換到該邏輯庫下才可以查到所需要的表。
-
dataNode屬性：該屬性用於繫結邏輯庫到某個具體的database上。
-
dataNode標籤： dataNode標籤定義了mycat中的資料節點，也就是資料分片。一個dataNode標籤就是一個獨立的資料分片。
-
name屬性：定義資料節點的名字，這個名字需要是唯一的，我們需要在table標籤上應用這個名字，來建立表與分片對應的關係。
-
dataHost屬性：該屬性用於定義該分片屬於那個資料庫例項，屬性值是引用datahost標籤定義的name屬性。
-
database屬性：該屬性用於定義該分片屬於那個具體資料庫例項上的具體庫，因為這裡使用兩個緯度來定義分片，就是：例項+具體的庫。因為每個庫上建立的表和表結構是一樣的。所以這樣做就可以輕鬆的對錶進行水平拆分。
-
dataHost標籤：該標籤在mycat邏輯庫中也是作為最底層的標籤存在，直接定義了具體的資料庫例項、讀寫分離配置和心跳語句。
-
balance屬性： 負載均衡型別
balance=0: 不開啟讀寫分離，所有讀操作都傳送到當前可用的writeHost上。
balance=1: 全部的readHost與Stand by writeHost都參與select語句的負載均衡
balance=2: 所有的讀操作都隨機在writeHost，readHost上分發。
balance=3: 所有的讀請求都隨機分配到writeHost對應的readHost上執行，writeHost不負擔讀壓力。 -
switchType屬性：
-1：表示不自動切換。
1 ：預設值，表示自動切換
2：表示基於MySQL主從同步狀態決定是否切換，心跳語句： show slave status.
3:表示基於mysql galary cluster的切換機制，適合mycat1.4之上的版本，心跳語句show status like "%esrep%"; -
writeHost標籤，readHost標籤：這兩個標籤指定後端資料庫的相關配置給mycat，用於例項化後端連線池。唯一不同的是，writeHost指定寫例項、readHost指定讀例項，組合這些讀寫例項來滿足系統的要求。
- host屬性：用於標識不同的例項，對於writehost，一般使用M1；對於readhost一般使用S1.
- url屬性：後端例項連線地址，如果使用native的dbDriver，則一般為address:port這種形式，用JDBC或其他的dbDriver，則需要特殊指定。當使用JDBC時則可以這麼寫：jdbc:mysql://localhost:3306/。
- user屬性：後端儲存例項的使用者名稱。
- password屬性：後端儲存例項的密碼
-
-
測試
- 重啟主伺服器的mycat
// 重啟mycat cd /root/mycat/bin ./mycat restart // 檢視埠監聽 netstat -ant|grep 8066- sqlyog連線mycat
-- 建立學生表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 查詢學生表 SELECT * FROM student; -- 新增兩條記錄 INSERT INTO student VALUES (NULL,'張三'),(NULL,'李四'); -- 停止主從複製後，新增的資料只會儲存到主伺服器上。 INSERT INTO student VALUES (NULL,'王五');- sqlyog連線主伺服器
-- 主伺服器：查詢學生表，可以看到資料 SELECT * FROM student;- sqlyog連線從伺服器
-- 從伺服器：查詢學生表，可以看到資料(因為有主從複製) SELECT * FROM student; -- 從伺服器：刪除一條記錄。(主伺服器並沒有刪除，mycat中介軟體查詢的結果是從伺服器的資料) DELETE FROM student WHERE id=2;
分庫分表
-
分庫分表的概念
- 將龐大的資料進行拆分
- 水平拆分：根據表的資料邏輯關係，將同一表中的資料按照某種條件，拆分到多臺資料庫伺服器上，也叫做橫向拆分。例如：一張1000萬的大表，按照一模一樣的結構，拆分成4個250萬的小表，分別儲存到4個資料庫中。
- 垂直拆分：根據業務的維度，將不同的表切分到不同的資料庫之上，也叫做縱向拆分。例如：所有的訂單都儲存到訂單庫中，所有的使用者都儲存到使用者庫中，同型別的表儲存在同一庫，不同的表分散在不同的庫中。
-
Mycat水平拆分
-
修改主伺服器的server.xml
-
0：本地檔案方式
在mycat/conf/sequence_conf.properties檔案中：
GLOBAL.MINDI=10000最小值
GLOBAL.MAXID=20000最大值，建議修改到9999999999 -
1：資料庫方式
分庫分表中保證全域性主鍵自增唯一，但是需要執行mycat函式，配置sequence_db_conf.properties
-
2：時間戳方式
mycat實現的時間戳，建議varchar型別，要注意id的長度
-
<!-- 修改主鍵的方式 --> <property name="sequnceHandlerType">0</property>- 修改主伺服器的sequence_conf.properties
#default global sequence GLOBAL.HISIDS= # 可以自定義關鍵字 GLOBAL.MINID=10001 # 最小值 GLOBAL.MAXID=20000 # 最大值 GLOBAL.CURID=10000- 修改主伺服器的schema.xml
- table標籤定義了邏輯表，所有需要拆分的表都需要在這個標籤中定義。
- rule屬性：拆分規則。mod-long是拆分規則之一，主鍵根據伺服器數量取模，在rule.xml中指定。如果是3個資料庫，那麼資料取模後，平均分配到三個庫中。
- name屬性：定義邏輯表的表名，這個名字就如同在資料庫中執行create table命令指定的名字一樣，同一個schema標籤中定義的表名必須是唯一的。
- dataNode屬性： 定義這個邏輯表所屬的dataNode，該屬性的值需要和dataNode標籤中name屬性的值相互對應。
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100"> <table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/> </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- write --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- read --> <readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>- 修改主伺服器的rule.xml
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- 資料庫的數量 --> <property name="count">3</property> </function>-
測試
- mycat操作
-- 建立product表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), price INT ); -- 新增6條資料 INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'蘋果手機',6999); INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'華為手機',5999); INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'三星手機',4999); INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'小米手機',3999); INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'中興手機',2999); INSERT INTO product(id,NAME,price) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'OOPO手機',1999); -- 查詢product表 SELECT * FROM product;- 主伺服器操作
-- 在不同資料庫中查詢product表 SELECT * FROM product;- 從伺服器操作
-- 在不同資料庫中查詢product表 SELECT * FROM product;
-
-
Mycat垂直拆分
- 修改主伺服器的schema
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="HEIMADB" checkSQLschema="false" sqlMaxLimit="100"> <table name="product" primaryKey="id" dataNode="dn1,dn2,dn3" rule="mod-long"/> <!-- 動物類資料表 --> <table name="dog" primaryKey="id" autoIncrement="true" dataNode="dn4" /> <table name="cat" primaryKey="id" autoIncrement="true" dataNode="dn4" /> <!-- 水果類資料表 --> <table name="apple" primaryKey="id" autoIncrement="true" dataNode="dn5" /> <table name="banana" primaryKey="id" autoIncrement="true" dataNode="dn5" /> </schema> <dataNode name="dn1" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" database="db3" /> <dataNode name="dn4" dataHost="localhost1" database="db4" /> <dataNode name="dn5" dataHost="localhost1" database="db5" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- write --> <writeHost host="hostM1" url="localhost:3306" user="root" password="123456"> <!-- read --> <readHost host="hostS1" url="192.168.203.135:3306" user="root" password="123456" /> </writeHost> </dataHost> </mycat:schema>-
測試
- sqlyog連線mycat
-- 建立dog表 CREATE TABLE dog( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 新增資料 INSERT INTO dog(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'哈士奇'); -- 查詢dog表 SELECT * FROM dog; -- 建立cat表 CREATE TABLE cat( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 新增資料 INSERT INTO cat(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'波斯貓'); -- 查詢cat表 SELECT * FROM cat; -- 建立apple表 CREATE TABLE apple( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 新增資料 INSERT INTO apple(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'紅富士'); -- 查詢apple表 SELECT * FROM apple; -- 建立banana表 CREATE TABLE banana( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10) ); -- 新增資料 INSERT INTO banana(id,NAME) VALUES (NEXT VALUE FOR MYCATSEQ_GLOBAL,'香蕉'); -- 查詢banana表 SELECT * FROM banana;- sqlyog連線主伺服器
-- 查詢dog表 SELECT * FROM dog; -- 查詢cat表 SELECT * FROM cat; -- 查詢apple表 SELECT * FROM apple; -- 查詢banana表 SELECT * FROM banana;- sqlyog連線從伺服器
-- 查詢dog表 SELECT * FROM dog; -- 查詢cat表 SELECT * FROM cat; -- 查詢apple表 SELECT * FROM apple; -- 查詢banana表 SELECT * FROM banana;