原文鏈接：http://www.zhenganwen.top/articles/2018/12/25/1565048860202.html
作者：Anwen~
鏈接：https://www.nowcoder.com/discuss/150059?type=0&order=0&pos=8&page=0
來源：牛客網
概述
為什么要優化
- 系統的吞吐量瓶頸往往出現在資料庫的訪問速度上
- 隨著應用程式的運行，資料庫的中的資料會越來越多，處理時間會相應變慢
- 資料是存放在磁盤上的，讀寫速度無法和記憶體相比
如何優化
- 設計資料庫時：資料庫表、欄位的設計，存盤引擎
- 利用好MySQL自身提供的功能，如索引等
- 橫向擴展：MySQL集群、負載均衡、讀寫分離
- SQL陳述句的優化（收效甚微）
欄位設計
欄位型別的選擇，設計規范，范式，常見設計案例
原則：盡量使用整型表示字串
存盤IP
INET_ATON(str)，address to number
INET_NTOA(number)，number to address
MySQL內部的列舉型別（單選）和集合（多選）型別
但是因為維護成本較高因此不常使用，使用關聯表的方式來替代enum
原則：定長和非定長資料型別的選擇
decimal不會損失精度，存盤空間會隨資料的增大而增大，double占用固定空間，較大數的存盤會損失精度，非定長的還有varchar、text
金額
對資料的精度要求較高，小數的運算和存盤存在精度問題（不能將所有小數轉換成二進制）
定點數decimal
price decimal(8,2)有2位小數的定點數，定點數支持很大的數（甚至是超過int,bigint存盤范圍的數）
小單位大數額避免出現小數
元->分
字串存盤
定長char，非定長varchar、text（上限65535，其中varchar還會消耗1-3位元組記錄長度，而text使用額外空間記錄長度）
原則：盡可能選擇小的資料型別和指定短的長度
原則：盡可能使用 not null
非null欄位的處理要比null欄位的處理高效些！且不需要判斷是否為null，
null在MySQL中，不好處理，存盤需要額外空間，運算也需要特殊的運算子，如select null = null和select null <> null（<>為不等號）有著同樣的結果，只能通過is null和is not null來判斷欄位是否為null，
如何存盤？MySQL中每條記錄都需要額外的存盤空間，表示每個欄位是否為null，因此通常使用特殊的資料進行占位，比如int not null default 0、string not null default ‘’
原則：欄位注釋要完整，見名知意
原則：單表欄位不宜過多
二三十個就極限了
原則：可以預留欄位
在使用以上原則之前首先要滿足業務需求
關聯表的設計
外鍵foreign key只能實作一對一或一對多的映射
一對多
使用外鍵
多對多
單獨新建一張表將多對多拆分成兩個一對多
一對一
如商品的基本資訊（item）和商品的詳細資訊（item_intro），通常使用相同的主鍵或者增加一個外鍵欄位（item_id）
范式 Normal Format
資料表的設計規范，一套越來越嚴格的規范體系（如果需要滿足N范式，首先要滿足N-1范式），N
第一范式1NF：欄位原子性
欄位原子性，欄位不可再分割，
關系型資料庫，默認滿足第一范式
注意比較容易出錯的一點，在一對多的設計中使用逗號分隔多個外鍵，這種方法雖然存盤方便，但不利于維護和索引（比如查找帶標簽java的文章）
第二范式：消除對主鍵的部分依賴
即在表中加上一個與業務邏輯無關的欄位作為主鍵
主鍵：可以唯一標識記錄的欄位或者欄位集合，
| course_name | course_class | weekday（周幾） | course_teacher |
|---|---|---|---|
| MySQL | 教育大樓1525 | 周一 | 張三 |
| Java | 教育大樓1521 | 周三 | 李四 |
| MySQL | 教育大樓1521 | 周五 | 張三 |
依賴：A欄位可以確定B欄位，則B欄位依賴A欄位，比如知道了下一節課是數學課，就能確定任課老師是誰，于是周幾和下一節課和就能構成復合主鍵，能夠確定去哪個教室上課，任課老師是誰等，但我們常常增加一個id作為主鍵，而消除對主鍵的部分依賴，
對主鍵的部分依賴：某個欄位依賴復合主鍵中的一部分，
解決方案：新增一個獨立欄位作為主鍵，
第三范式：消除對主鍵的傳遞依賴
傳遞依賴：B欄位依賴于A，C欄位又依賴于B，比如上例中，任課老師是誰取決于是什么課，是什么課又取決于主鍵id，因此需要將此表拆分為兩張表日程表和課程表（獨立資料獨立建表）：
| id | weekday | course_class | course_id |
|---|---|---|---|
| 1001 | 周一 | 教育大樓1521 | 3546 |
| course_id | course_name | course_teacher |
|---|---|---|
| 3546 | Java | 張三 |
這樣就減少了資料的冗余（即使周一至周日每天都有Java課，也只是course_id:3546出現了7次）
存盤引擎選擇
早期問題：如何選擇MyISAM和Innodb？
現在不存在這個問題了，Innodb不斷完善，從各個方面趕超MyISAM，也是MySQL默認使用的，
存盤引擎Storage engine：MySQL中的資料、索引以及其他物件是如何存盤的，是一套檔案系統的實作，
功能差異
show engines
| Engine | Support | Comment |
|---|---|---|
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
| MyISAM | YES | MyISAM storage engine |
存盤差異
| MyISAM | Innodb | |
|---|---|---|
| 檔案格式 | 資料和索引是分別存盤的，資料.MYD，索引.MYI | 資料和索引是集中存盤的，.ibd |
| 檔案能否移動 | 能，一張表就對應.frm、MYD、MYI3個檔案 | 否，因為關聯的還有data下的其它檔案 |
| 記錄存盤順序 | 按記錄插入順序保存 | 按主鍵大小有序插入 |
| 空間碎片（洗掉記錄并flush table 表名之后，表檔案大小不變） | 產生，定時整理：使用命令optimize table 表名實作 | 不產生 |
| 事務 | 不支持 | 支持 |
| 外鍵 | 不支持 | 支持 |
| 鎖支持（鎖是避免資源爭用的一個機制，MySQL鎖對用戶幾乎是透明的） | 表級鎖定 | 行級鎖定、表級鎖定，鎖定力度小并發能力高 |
鎖擴展
表級鎖（table-level lock）：lock tables <table_name1>,<table_name2>... read/write，unlock tables <table_name1>,<table_name2>...，其中read是共享鎖，一旦鎖定任何客戶端都不可讀；write是獨占/寫鎖，只有加鎖的客戶端可讀可寫，其他客戶端既不可讀也不可寫，鎖定的是一張表或幾張表，
行級鎖（row-level lock）：鎖定的是一行或幾行記錄，共享鎖：select * from <table_name> where <條件> LOCK IN SHARE MODE;，對查詢的記錄增加共享鎖；select * from <table_name> where <條件> FOR UPDATE;，對查詢的記錄增加排他鎖，這里值得注意的是：innodb的行鎖，其實是一個子范圍鎖，依據條件鎖定部分范圍，而不是就映射到具體的行上，因此還有一個學名：間隙鎖，比如select * from stu where id < 20 LOCK IN SHARE MODE會鎖定id在20左右以下的范圍，你可能無法插入id為18或22的一條新紀錄，
選擇依據
如果沒有特別的需求，使用默認的Innodb即可，
MyISAM：以讀寫插入為主的應用程式，比如博客系統、新聞門戶網站，
Innodb：更新（洗掉）操作頻率也高，或者要保證資料的完整性；并發量高，支持事務和外鍵保證資料完整性，比如OA自動化辦公系統，
索引
關鍵字與資料的映射關系稱為索引（==包含關鍵字和對應的記錄在磁盤中的地址==），關鍵字是從資料當中提取的用于標識、檢索資料的特定內容，
索引檢索為什么快？
- 關鍵字相對于資料本身，==資料量小==
- 關鍵字是==有序==的，二分查找可快速確定位置
圖書館為每本書都加了索引號（類別-樓層-書架）、字典為詞語解釋按字母順序撰寫目錄等都用到了索引，
MySQL中索引型別
普通索引（key），唯一索引（unique key），主鍵索引（primary key），全文索引（fulltext key）
三種索引的索引方式是一樣的，只不過對索引的關鍵字有不同的限制：
- 普通索引：對關鍵字沒有限制
- 唯一索引：要求記錄提供的關鍵字不能重復
- 主鍵索引：要求關鍵字唯一且不為null
索引管理語法
查看索引
show create table 表名：
desc 表名
創建索引
創建表之后建立索引
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
create TABLE user_index(
id int auto_increment primary key,
first_name varchar(16),
last_name VARCHAR(16),
id_card VARCHAR(18),
information text
);
-- 更改表結構
alter table user_index
-- 創建一個first_name和last_name的復合索引，并命名為name
add key name (first_name,last_name),
-- 創建一個id_card的唯一索引，默認以欄位名作為索引名
add UNIQUE KEY (id_card),
-- 雞肋，全文索引不支持中文
add FULLTEXT KEY (information);
|
show create table user_index：
創建表時指定索引
| 1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
|
洗掉索引
根據索引名洗掉普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名
| 1 2 3 |
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
|
洗掉主鍵索引：alter table 表名 drop primary key（因為主鍵只有一個），這里值得注意的是，如果主鍵自增長，那么不能直接執行此操作（自增長依賴于主鍵索引）：
需要取消自增長再行洗掉：
| 1 2 3 4 |
alter table user_index
-- 重新定義欄位
MODIFY id int,
drop PRIMARY KEY
|
但通常不會洗掉主鍵，因為設計主鍵一定與業務邏輯無關，
執行計劃explain
| 1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE innodb1 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
insert into innodb1 (first_name,last_name,id_card,information) values ('張','三','1001','華山派');
|
我們可以通過explain selelct來分析SQL陳述句執行前的執行計劃：
由上圖可看出此SQL陳述句是按照主鍵索引來檢索的，
執行計劃是：當執行SQL陳述句時，首先會分析、優化，形成執行計劃，在按照執行計劃執行，
索引使用場景（重點）
where
上圖中，根據id查詢記錄，因為id欄位僅建立了主鍵索引，因此此SQL執行可選的索引只有主鍵索引，如果有多個，最侄訓選一個較優的作為檢索的依據，
| 1 2 3 4 |
-- 增加一個沒有建立索引的欄位
alter table innodb1 add sex char(1);
-- 按sex檢索時可選的索引為null
EXPLAIN SELECT * from innodb1 where sex='男';
|
可以嘗試在一個欄位未建立索引時，根據該欄位查詢的效率，然后對該欄位建立索引（alter table 表名 add index(欄位名)），同樣的SQL執行的效率，你會發現查詢效率會有明顯的提升（資料量越大越明顯），
order by
當我們使用order by將查詢結果按照某個欄位排序時，如果該欄位沒有建立索引，那么執行計劃會將查詢出的所有資料使用外部排序（將資料從硬碟分批讀取到記憶體使用內部排序，最后合并排序結果），這個操作是很影響性能的，因為需要將查詢涉及到的所有資料從磁盤中讀到記憶體（如果單條資料過大或者資料量過多都會降低效率），更無論讀到記憶體之后的排序了，
但是如果我們對該欄位建立索引alter table 表名 add index(欄位名)，那么由于索引本身是有序的，因此直接按照索引的順序和映射關系逐條取出資料即可，而且如果分頁的，那么只用取出索引表某個范圍內的索引對應的資料，而不用像上述那取出所有資料進行排序再回傳某個范圍內的資料，（從磁盤取資料是最影響性能的）
join
對join陳述句匹配關系（on）涉及的欄位建立索引能夠提高效率
索引覆寫
如果要查詢的欄位都建立過索引，那么引擎會直接在索引表中查詢而不會訪問原始資料（否則只要有一個欄位沒有建立索引就會做全表掃描），這叫索引覆寫，因此我們需要盡可能的在select后==只寫必要的查詢欄位==，以增加索引覆寫的幾率，
這里值得注意的是不要想著為每個欄位建立索引，因為優先使用索引的優勢就在于其體積小，
語法細節（要點）
在滿足索引使用的場景下（where/order by/join on或索引覆寫），索引也不一定被使用
欄位要獨立出現
比如下面兩條SQL陳述句在語意上相同，但是第一潭訓使用主鍵索引而第二條不會，
| 1 2 |
select * from user where id = 20-1;
select * from user where id+1 = 20;
|
like查詢，不能以通配符開頭
比如搜索標題包含mysql的文章：
| 1 |
select * from article where title like '%mysql%';
|
這種SQL的執行計劃用不了索引（like陳述句匹配運算式以通配符開頭），因此只能做全表掃描，效率極低，在實際工程中幾乎不被采用，而一般會使用第三方提供的支持中文的全文索引來做，
但是 關鍵字查詢 熱搜提醒功能還是可以做的，比如鍵入mysql之后提醒mysql 教程、mysql 下載、mysql 安裝步驟等，用到的陳述句是：
| 1 |
select * from article where title like 'mysql%';
|
這種like是可以利用索引的（當然前提是title欄位建立過索引），
復合索引只對第一個欄位有效
建立復合索引：
| 1 |
alter table person add index(first_name,last_name);
|
其原理就是將索引先按照從first_name中提取的關鍵字排序，如果無法確定先后再按照從last_name提取的關鍵字排序，也就是說該索引表只是按照記錄的first_name欄位值有序，
因此select * from person where first_name = ?是可以利用索引的，而select * from person where last_name = ?無法利用索引，
那么該復合索引的應用場景是什么？==組合查詢==
比如對于select * person from first_name = ? and last_name = ?，復合索引就比對first_name和last_name單獨建立索引要高效些，很好理解，復合索引首先二分查找與first_name = ?匹配的記錄，再在這些記錄中二分查找與last_name匹配的記錄，只涉及到一張索引表，而分別單獨建立索引則是在first_name索引表中二分找出與first_name = ?匹配的記錄，再在last_name索引表中二分找出與last_name = ?的記錄，兩者取交集，
or，兩邊條件都有索引可用
一但有一邊無索引可用就會導致整個SQL陳述句的全表掃描
狀態值，不容易使用到索引
如性別、支付狀態等狀態值欄位往往只有極少的幾種取值可能，這種欄位即使建立索引，也往往利用不上，這是因為，一個狀態值可能匹配大量的記錄，這種情況MySQL會認為利用索引比全表掃描的效率低，從而棄用索引，索引是隨機訪問磁盤，而全表掃描是順序訪問磁盤，這就好比有一棟20層樓的寫字樓，樓底下的索引牌上寫著某個公司對應不相鄰的幾層樓，你去公司找人，與其按照索引牌的提示去其中一層樓沒找到再下來看索引牌再上樓，不如從1樓挨個往上找到頂樓，
如何創建索引
- 建立基礎索引：在where、order by、join欄位上建立索引，
- 優化，組合索引：基于業務邏輯
- 如果條件經常性出現在一起，那么可以考慮將多欄位索引升級為==復合索引==
- 如果通過增加個別欄位的索引，就可以出現==索引覆寫==，那么可以考慮為該欄位建立索引
- 查詢時，不常用到的索引，應該洗掉掉
前綴索引
語法：index(field(10))，使用欄位值的前10個字符建立索引，默認是使用欄位的全部內容建立索引，
前提：前綴的標識度高，比如密碼就適合建立前綴索引，因為密碼幾乎各不相同，
==實操的難度==：在于前綴截取的長度，
我們可以利用select count(*)/count(distinct left(password,prefixLen));，通過從調整prefixLen的值（從1自增）查看不同前綴長度的一個平均匹配度，接近1時就可以了（表示一個密碼的前prefixLen個字符幾乎能確定唯一一條記錄）
索引的存盤結構
BTree
btree（多路平衡查找樹）是一種廣泛應用于==磁盤上實作索引功能==的一種資料結構，也是大多數資料庫索引表的實作，
以add index(first_name,last_name)為例：
BTree的一個node可以存盤多個關鍵字，node的大小取決于計算機的檔案系統，因此我們可以通過減小索引欄位的長度使結點存盤更多的關鍵字，如果node中的關鍵字已滿，那么可以通過每個關鍵字之間的子節點指標來拓展索引表，但是不能破壞結構的有序性，比如按照first_name第一有序、last_name第二有序的規則，新添加的韓香就可以插到韓康之后，白起 < 韓飛 < 韓康 < 李世民 < 趙奢 < 李尋歡 < 王語嫣 < 楊不悔，這與二叉搜索樹的思想是一樣的，只不過二叉搜索樹的查找效率是log(2,N)（以2為底N的對數），而BTree的查找效率是log(x,N)（其中x為node的關鍵字數量，可以達到1000以上），
從log(1000+,N)可以看出，少量的磁盤讀取即可做到大量資料的遍歷，這也是btree的設計目的，
B+Tree聚簇結構
聚簇結構（也是在BTree上升級改造的）中，關鍵字和記錄是存放在一起的，
在MySQL中，僅僅只有Innodb的==主鍵索引為聚簇結構==，其它的索引包括Innodb的非主鍵索引都是典型的BTree結構，
哈希索引
在索引被載入記憶體時，使用哈希結構來存盤，
查詢快取
快取select陳述句的查詢結果
在組態檔中開啟快取
windows上是my.ini，linux上是my.cnf
在[mysqld]段中配置query_***_type：
- 0：不開啟
- 1：開啟，默認快取所有，需要在SQL陳述句中增加select sql-no-***提示來放棄快取
- 2：開啟，默認都不快取，需要在SQL陳述句中增加select sql-***來主動快取（==常用==）
更改配置后需要重啟以使配置生效，重啟后可通過show variables like ‘query_***_type’;來查看：
| 1 2 |
show variables like 'query_***_type';
query_***_type DEMAND
|
在客戶端設定快取大小
通過配置項query_***_size來設定：
| 1 2 3 4 5 6 |
show variables like 'query_***_size';
query_***_size 0
set global query_***_size=64*1024*1024;
show variables like 'query_***_size';
query_***_size 67108864
|
將查詢結果快取
select sql_*** * from user;
重置快取
reset query ***;
快取失效問題（大問題）
當資料表改動時，基于該資料表的任何快取都會被洗掉，（表層面的管理，不是記錄層面的管理，因此失效率較高）
注意事項
- 應用程式，不應該關心query ***的使用情況，可以嘗試使用，但不能由query ***決定業務邏輯，因為query ***由DBA來管理，
- 快取是以SQL陳述句為key存盤的，因此即使SQL陳述句功能相同，但如果多了一個空格或者大小寫有差異都會導致匹配不到快取，
磁區
一般情況下我們創建的表對應一組存盤檔案，使用MyISAM存盤引擎時是一個.MYI和.MYD檔案，使用Innodb存盤引擎時是一個.ibd和.frm（表結構）檔案，
當資料量較大時（一般千萬條記錄級別以上），MySQL的性能就會開始下降，這時我們就需要將資料分散到多組存盤檔案，==保證其單個檔案的執行效率==，
最常見的磁區方案是按id磁區，如下將id的哈希值對10取模將資料均勻分散到10個.ibd存盤檔案中：
| 1 2 3 4 5 |
create table article(
id int auto_increment PRIMARY KEY,
title varchar(64),
content text
)PARTITION by HASH(id) PARTITIONS 10
|
查看data目錄：
==服務端的表磁區對于客戶端是透明的==，客戶端還是照常插入資料，但服務端會按照磁區演算法分散存盤資料，
MySQL提供的磁區演算法
==磁區依據的欄位必須是主鍵的一部分==，磁區是為了快速定位資料，因此該欄位的搜索頻次較高應作為強檢索欄位，否則依照該欄位磁區毫無意義
hash(field)
相同的輸入得到相同的輸出，輸出的結果跟輸入是否具有規律無關，==僅適用于整型欄位==
key(field)
和hash(field)的性質一樣，只不過key是==處理字串==的，比hash()多了一步從字串中計算出一個整型在做取模操作，
| 1 2 3 4 5 6 |
create table article_key(
id int auto_increment,
title varchar(64),
content text,
PRIMARY KEY (id,title) -- 要求磁區依據欄位必須是主鍵的一部分
)PARTITION by KEY(title) PARTITIONS 10
|
range演算法
是一種==條件磁區==演算法，按照資料大小范圍磁區（將資料使用某種條件，分散到不同的磁區中），
如下，按文章的發布時間將資料按照2018年8月、9月、10月磁區存放：
| 1 2 3 4 5 6 7 8 9 10 11 12 |
create table article_range(
id int auto_increment,
title varchar(64),
content text,
created_time int, -- 發布時間到1970-1-1的毫秒數
PRIMARY KEY (id,created_time) -- 要求磁區依據欄位必須是主鍵的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(
PARTITION p201808 VALUES less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
PARTITION p201809 VALUES less than (1538323199), -- 2018-9-30 23:59:59
PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59
);
|
注意：條件運算子只能使用==less than==，這以為著較小的范圍要放在前面，比如上述p201808,p201819,p201810磁區的定義順序依照created_time數值范圍從小到大，不能顛倒，
| 1 2 |
insert into article_range values(null,'MySQL優化','內容示例',1535731180);
flush tables; -- 使操作立即重繪到磁盤檔案
|
由于插入的文章的發布時間1535731180小于1535731199（2018-8-31 23:59:59），因此被存盤到p201808磁區中，這種演算法的存盤到哪個磁區取決于資料狀況，
list演算法
也是一種條件磁區，按照串列值磁區（in (值串列)），
| 1 2 3 4 5 6 7 8 9 10 11 |
create table article_list(
id int auto_increment,
title varchar(64),
content text,
status TINYINT(1), -- 文章狀態：0-草稿，1-完成但未發布，2-已發布
PRIMARY KEY (id,status) -- 要求磁區依據欄位必須是主鍵的一部分
)charset=utf8
PARTITION BY list(status)(
PARTITION writing values in(0,1), -- 未發布的放在一個磁區
PARTITION published values in (2) -- 已發布的放在一個磁區
);
|
| 1 2 |
insert into article_list values(null,'mysql優化','內容示例',0);
flush tables;
|
磁區管理語法
range/list
增加磁區
前文中我們嘗試使用range對文章按照月份歸檔，隨著時間的增加，我們需要增加一個月份：
| 1 2 3 4 |
alter table article_range add partition(
partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
-- more
);
|
洗掉磁區
| 1 |
alter table article_range drop PARTITION p201808
|
注意：==洗掉磁區后，磁區中原有的資料也會隨之洗掉！==
key/hash
新增磁區
| 1 |
alter table article_key add partition partitions 4
|
銷毀磁區
| 1 |
alter table article_key coalesce partition 6
|
key/hash磁區的管理不會洗掉資料，但是每一次調整（新增或銷毀磁區）都會將所有的資料重寫分配到新的磁區上，==效率極低==，最好在設計階段就考慮好磁區策略，
磁區的使用
當資料表中的資料量很大時，磁區帶來的效率提升才會顯現出來，
只有檢索欄位為磁區欄位時，磁區帶來的效率提升才會比較明顯，因此，==磁區欄位的選擇很重要==，并且==業務邏輯要盡可能地根據磁區欄位做相應調整==（盡量使用磁區欄位作為查詢條件），
水平分割和垂直分割
水平分割：通過建立結構相同的幾張表分別存盤資料
垂直分割：將經常一起使用的欄位放在一個單獨的表中，分割后的表記錄之間是一一對應關系，
分表原因
- 為資料庫減壓
- 磁區演算法局限
- 資料庫支持不完善（5.1之后mysql才支持磁區操作）
id重復的解決方案
- 借用第三方應用如mem***、redis的id自增器
- 單獨建一張只包含id一個欄位的表，每次自增該欄位作為資料記錄的id
集群
橫向擴展：從根本上（單機的硬體處理能力有限）提升資料庫性能 ，由此而生的相關技術：==讀寫分離、負載均衡==
安裝和配置主從復制
環境
- Red Hat Enterprise Linux Server release 7.0 (Maipo)（虛擬機）
- mysql5.7（下載地址）
安裝和配置
解壓到對外提供的服務的目錄（我自己專門創建了一個/export/server來存放）
| 1 2 3 |
tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/server
cd /export/server
mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql
|
添加mysql目錄的所屬組和所屬者：
| 1 2 3 4 5 |
groupadd mysql
useradd -r -g mysql mysql
cd /export/server
chown -R mysql:mysql mysql/
chmod -R 755 mysql/
|
創建mysql資料存放目錄（其中/export/data是我創建專門用來為各種服務存放資料的目錄）
| 1 |
mkdir /export/data/mysql
|
初始化mysql服務
| 1 2 |
cd /export/server/mysql
./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize
|
如果成功會顯示mysql的root賬戶的初始密碼，記下來以備后續登錄，如果報錯缺少依賴，則使用yum instally依次安裝即可
配置my.cnf
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
vim /etc/my.cnf
[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10 # 服務id，在集群時必須唯一，建議設定為IP的第四段
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
|
將服務添加到開機自動啟動
| 1 |
cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld
|
啟動服務
| 1 |
service mysqld start
|
配置環境變數，在/etc/profile中添加如下內容
| 1 2 3 4 5 |
# mysql env
MYSQL_HOME=/export/server/mysql
MYSQL_PATH=$MYSQL_HOME/bin
PATH=$PATH:$MYSQL_PATH
export PATH
|
使配置即可生效
| 1 |
source /etc/profile
|
使用root登錄
| 1 2 |
mysql -uroot -p
# 這里填寫之前初始化服務時提供的密碼
|
登錄上去之后，更改root賬戶密碼（我為了方便將密碼改為root），否則操作資料庫會報錯
| 1 2 |
set password=password('root');
flush privileges;
|
設定服務可被所有遠程客戶端訪問
| 1 2 3 |
use mysql;
update user set host='%' where user='root';
flush privileges;
|
這樣就可以在宿主機使用navicat遠程連接虛擬機linux上的mysql了
配置主從節點
配置master
以linux（192.168.10.10）上的mysql為master，宿主機（192.168.10.1）上的mysql為slave配置主從復制，
修改master的my.cnf如下
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=mysql-bin # 開啟二進制日志
expire-logs-days=7 # 設定日志過期時間，避免占滿磁盤
binlog-ignore-db=mysql # 不使用主從復制的資料庫
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-do-db=test #使用主從復制的資料庫
[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
|
重啟master
| 1 |
service mysqld restart
|
登錄master查看配置是否生效（ON即為開啟，默認為OFF）：
| 1 2 3 4 5 6 |
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
|
在master的資料庫中建立備份賬號：backup為用戶名，%表示任何遠程地址，用戶back可以使用密碼1234通過任何遠程客戶端連接master
| 1 |
grant replication slave on *.* to 'backup'@'%' identified by '1234'
|
查看user表可以看到我們剛創建的用戶：
| 1 2 3 4 5 6 7 8 9 10 |
mysql> use mysql
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string | host |
+---------------+-------------------------------------------+-----------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % |
| mysql.session | *TH***NOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *TH***NOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |
+---------------+-------------------------------------------+-----------+
|
新建test資料庫，創建一個article表以備后續測驗
| 1 2 3 4 5 6 |
CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(64) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`)
) CHARSET=utf8;
|
重啟服務并重繪資料庫狀態到存盤檔案中（with read lock表示在此程序中，客戶端只能讀資料，以便獲得一個一致性的快照）
| 1 2 3 4 5 6 |
[root@zhenganwen ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@zhenganwen mysql]# mysql -uroot -proot
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
|
查看master上當前的二進制日志和偏移量（記一下其中的File和Position）
| 1 2 3 4 5 6 7 8 |
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 154
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
|
File表示實作復制功能的日志，即上圖中的Binary log；Position則表示Binary log日志檔案的偏移量之后的都會同步到slave中，那么在偏移量之前的則需要我們手動匯入，
主服務器上面的任何修改都會保存在二進制日志Binary log里面，從服務器上面啟動一個I/O thread（實際上就是一個主服務器的客戶端行程），連接到主服務器上面請求讀取二進制日志，然后把讀取到的二進制日志寫到本地的一個Realy log里面，從服務器上面開啟一個SQL thread定時檢查Realy log，如果發現有更改立即把更改的內容在本機上面執行一遍，
如果一主多從的話，這時主庫既要負責寫又要負責為幾個從庫提供二進制日志，此時可以稍做調整，將二進制日志只給某一從，這一從再開啟二進制日志并將自己的二進制日志再發給其它從，或者是干脆這個從不記錄只負責將二進制日志轉發給其它從，這樣架構起來性能可能要好得多，而且資料之間的延時應該也稍微要好一些
手動匯入，從master中匯出資料
| 1 |
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql
|
將test.sql中的內容在slave上執行一遍，
配置slave
修改slave的my.ini檔案中的[mysqld]部分
| 1 2 |
log-bin=mysql
server-id=1 #192.168.10.1
|
保存修改后重啟slave，WIN+R->services.msc->MySQL5.7->重新啟動
登錄slave檢查log_bin是否以被開啟：
| 1 |
show VARIABLES like 'log_bin';
|
配置與master的同步復制：
| 1 2 3 4 5 6 7 |
stop slave;
change master to
master_host='192.168.10.10', -- master的IP
master_user='backup', -- 之前在master上創建的用戶
master_password='1234',
master_log_file='mysql-bin.000002', -- master上 show master status \G 提供的資訊
master_log_pos=154;
|
啟用slave節點并查看狀態
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.10
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 537
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05
Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
|
注意查看第4、14、15三行，若與我一致，表示slave配置成功
測驗
關閉master的讀取鎖定
| 1 2 |
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
|
向master中插入一條資料
| 1 2 3 |
mysql> use test
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)
|
查看slave是否自動同步了資料
| 1 2 |
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)
|
至此，主從復制的配置成功！：)
使用mysqlreplicate命令快速搭建 Mysql 主從復制
讀寫分離
讀寫分離是依賴于主從復制，而主從復制又是為讀寫分離服務的，因為主從復制要求slave不能寫只能讀（如果對slave執行寫操作，那么show slave status將會呈現Slave_SQL_Running=NO，此時你需要按照前面提到的手動同步一下slave），
方案一、定義兩種連接
就像我們在學JDBC時定義的DataBase一樣，我們可以抽取出ReadDataBase,WriteDataBase implements DataBase，但是這種方式無法利用優秀的執行緒池技術如DruidDataSource幫我們管理連接，也無法利用Spring AOP讓連接對DAO層透明，
方案二、使用Spring AOP
如果能夠使用Spring AOP解決資料源切換的問題，那么就可以和Mybatis、Druid整合到一起了，
我們在整合Spring1和Mybatis時，我們只需寫DAO介面和對應的SQL陳述句，那么DAO實體是由誰創建的呢？實際上就是Spring幫我們創建的，它通過我們注入的資料源，幫我們完成從中獲取資料庫連接、使用連接執行SQL陳述句的程序以及最后歸還連接給資料源的程序，
如果我們能在呼叫DAO介面時根據介面方法命名規范（增addXXX/createXXX、刪deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX）動態地選擇資料源（讀資料源對應連接master而寫資料源對應連接slave），那么就可以做到讀寫分離了，
專案結構
引入依賴
其中，為了方便訪問資料庫引入了mybatis和druid，實作資料源動態切換主要依賴spring-aop和spring-aspects
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.2</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.22</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
|
資料類
| 1 2 3 4 5 6 7 8 9 10 11 12 |
package top.zhenganwen.mysqloptimize.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor; <a href=https://www.cnblogs.com/morganlin/p/"/profile/1954537" data-card-uid="1954537" class="" target="_blank" style="color: #25bb9b" data-card-index="3">@Data @AllArgsConstructor
@NoArgsConstructor
public class Article {
private int id;
private String title;
private String content;
}</a>
|
spring組態檔
其中RoutingDataSourceImpl是實作動態切換功能的核心類，稍后介紹，
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<context:property-placeholder location="db.properties"></context:property-placeholder>
<context:component-scan base-package="top.zhenganwen.mysqloptimize"/>
<bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value=https://www.cnblogs.com/morganlin/p/"${db.driverClass}"/>
<property name="url" value=https://www.cnblogs.com/morganlin/p/"${master.db.url}"></property>
<property name="username" value=https://www.cnblogs.com/morganlin/p/"${master.db.username}"></property>
<property name="password" value=https://www.cnblogs.com/morganlin/p/"${master.db.password}"></property>
</bean>
<bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value=https://www.cnblogs.com/morganlin/p/"${db.driverClass}"/>
<property name="url" value=https://www.cnblogs.com/morganlin/p/"${slave.db.url}"></property>
<property name="username" value=https://www.cnblogs.com/morganlin/p/"${slave.db.username}"></property>
<property name="password" value=https://www.cnblogs.com/morganlin/p/"${slave.db.password}"></property>
</bean>
<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
<property name="defaultTargetDataSource" ref="masterDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String" value-type="javax.sql.DataSource">
<entry key="read" value-ref="slaveDataSource"/>
<entry key="write" value-ref="masterDataSource"/>
</map>
</property>
<property name="methodType">
<map key-type="java.lang.String" value-type="java.lang.String">
<entry key="read" value=https://www.cnblogs.com/morganlin/p/"query,find,select,get,load,"></entry>
<entry key="write" value=https://www.cnblogs.com/morganlin/p/"update,add,create,delete,remove,modify"/>
</map>
</property>
</bean>
<!-- Mybatis檔案 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value=https://www.cnblogs.com/morganlin/p/"classpath:mybatis-config.xml" />
<property name="dataSource" ref="dataSourceRouting" />
<property name="mapperLocations" value=https://www.cnblogs.com/morganlin/p/"mapper/*.xml"/>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value=https://www.cnblogs.com/morganlin/p/"top.zhenganwen.mysqloptimize.mapper" />
<property name="sqlSessionFactoryBeanName" value=https://www.cnblogs.com/morganlin/p/"sqlSessionFactory" />
</bean>
</beans>
|
dp.properties
| 1 2 3 4 5 6 7 8 9 |
master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
master.db.username=root
master.db.password=root
slave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
slave.db.username=root
slave.db.password=root
db.driverClass=com.mysql.jdbc.Driver
|
mybatis-config.xml
| 1 2 3 4 5 6 7 8 9 |
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias type="top.zhenganwen.mysqloptimize.entity.Article" alias="Article"/>
</typeAliases>
</configuration>
|
mapper介面和組態檔
ArticleMapper.java
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
package top.zhenganwen.mysqloptimize.mapper;
import org.springframework.stereotype.Repository;
import top.zhenganwen.mysqloptimize.entity.Article;
import java.util.List;
@Repository
public interface ArticleMapper {
List<Article> findAll();
void add(Article article);
void delete(int id);
}
|
ArticleMapper.xml
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="top.zhenganwen.mysqloptimize.mapper.ArticleMapper">
<select id="findAll" resultType="Article">
select * from article
</select>
<insert id="add" parameterType="Article">
insert into article (title,content) values (#{title},#{content})
</insert>
<delete id="delete" parameterType="int">
delete from article where id=#{id}
</delete>
</mapper>
|
核心類
RoutingDataSourceImpl
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
package top.zhenganwen.mysqloptimize.dataSource;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.*;
/**
* RoutingDataSourceImpl class
* 資料源路由
*
* @author zhenganwen, blog:zhenganwen.top
* @date 2018/12/29
*/
public class RoutingDataSourceImpl extends AbstractRoutingDataSource {
/**
* key為read或write
* value為DAO方法的前綴
* 什么前綴開頭的方法使用讀資料員，什么開頭的方法使用寫資料源
*/
public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();
/**
* 由我們指定資料源的id，由Spring切換資料源
*
* <a href="https://www.cnblogs.com/profile/547241" data-card-uid="547241" target="_blank" style="color: #25bb9b" data-card-index="4">@return */ </a><a href=https://www.cnblogs.com/morganlin/p/"/profile/992988" data-card-uid="992988" class="" target="_blank" style="color: #25bb9b" data-card-index="5">@Override protected Object determineCurrentLookupKey() {
System.out.println("資料源為："+DataSourceHandler.getDataSource());
return DataSourceHandler.getDataSource();
}
public void setMethodType(Map<String, String> map) {
for (String type : map.keySet()) {
String methodPrefixList = map.get(type);
if (methodPrefixList != null) {
METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(",")));
}
}
}
}</a>
|
它的主要功能是，本來我們只配置一個資料源，因此Spring動態***DAO介面時直接使用該資料源，現在我們有了讀、寫兩個資料源，我們需要加入一些自己的邏輯來告訴呼叫哪個介面使用哪個資料源（讀資料的介面使用slave，寫資料的介面使用master，這個告訴Spring該使用哪個資料源的類就是AbstractRoutingDataSource，必須重寫的方法determineCurrentLookupKey回傳資料源的標識，結合spring組態檔（下段代碼的5，6兩行）
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">
<property name="defaultTargetDataSource" ref="masterDataSource"></property>
<property name="targetDataSources">
<map key-type="java.lang.String" value-type="javax.sql.DataSource">
<entry key="read" value-ref="slaveDataSource"/>
<entry key="write" value-ref="masterDataSource"/>
</map>
</property>
<property name="methodType">
<map key-type="java.lang.String" value-type="java.lang.String">
<entry key="read" value=https://www.cnblogs.com/morganlin/p/"query,find,select,get,load,"></entry>
<entry key="write" value=https://www.cnblogs.com/morganlin/p/"update,add,create,delete,remove,modify"/>
</map>
</property>
</bean>
|
如果determineCurrentLookupKey回傳read那么使用slaveDataSource，如果回傳write就使用masterDataSource，
DataSourceHandler
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
package top.zhenganwen.mysqloptimize.dataSource;
/**
* DataSourceHandler class
* <p>
* 將資料源與執行緒系結，需要時根據執行緒獲取
*
* @author zhenganwen, blog:zhenganwen.top
* @date 2018/12/29
*/
public class DataSourceHandler {
/**
* 系結的是read或write，表示使用讀或寫資料源
*/
private static final ThreadLocal<String> holder = new ThreadLocal<String>();
public static void setDataSource(String dataSource) {
System.out.println(Thread.currentThread().getName()+"設定了資料源型別");
holder.set(dataSource);
}
public static String getDataSource() {
System.out.println(Thread.currentThread().getName()+"獲取了資料源型別");
return holder.get();
}
}
|
DataSourceAspect
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
package top.zhenganwen.mysqloptimize.dataSource;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Set;
import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP;
/**
* DataSourceAspect class
*
* 配置切面，根據方法前綴設定讀、寫資料源
* 專案啟動時會加載該bean，并按照配置的切面（哪些切入點、如何增強）確定動態***邏輯
* @author zhenganwen,blog:zhenganwen.top
* @date 2018/12/29
*/ <a href=https://www.cnblogs.com/morganlin/p/"/profile/664319079" data-card-uid="664319079" class="" target="_blank" style="color: #25bb9b" data-card-index="6">@Component //宣告這是一個切面，這樣Spring才會做相應的配置，否則只會當做簡單的bean注入
@Aspect
@EnableAspectJAutoProxy
public class DataSourceAspect {
/**
* 配置切入點：DAO包下的所有類的所有方法
*/
@Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))")
public void aspect() {
}
/**
* 配置前置增強，物件是aspect()方法上配置的切入點
*/
@Before("aspect()")
public void before(JoinPoint point) {
String className = point.getTarget().getClass().getName();
String invokedMethod = point.getSignature().getName();
System.out.println("對 "+className+"$"+invokedMethod+" 做了前置增強，確定了要使用的資料源型別");
Set<String> dataSourceType = METHOD_TYPE_MAP.keySet();
for (String type : dataSourceType) {
List<String> prefixList = METHOD_TYPE_MAP.get(type);
for (String prefix : prefixList) {
if (invokedMethod.startsWith(prefix)) {
DataSourceHandler.setDataSource(type);
System.out.println("資料源為："+type);
return;
}
}
}
}
}</a>
|
測驗讀寫分離
如何測驗讀是從slave中讀的呢？可以將寫后復制到slave中的資料更改，再讀該資料就知道是從slave中讀了，==注意==，一但對slave做了寫操作就要重新手動將slave與master同步一下，否則主從復制就會失效，
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
package top.zhenganwen.mysqloptimize.dataSource;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import top.zhenganwen.mysqloptimize.entity.Article;
import top.zhenganwen.mysqloptimize.mapper.ArticleMapper;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring-mybatis.xml")
public class RoutingDataSourceTest {
@Autowired
ArticleMapper articleMapper;
@Test
public void testRead() {
System.out.println(articleMapper.findAll());
}
@Test
public void testAdd() {
Article article = new Article(0, "我是新插入的文章", "測驗是否能夠寫到master并且復制到slave中");
articleMapper.add(article);
}
@Test
public void testDelete() {
articleMapper.delete(2);
}
}
|
負載均衡
負載均衡演算法
- 輪詢
- 加權輪詢：按照處理能力來加權
- 負載分配：依據當前的空閑狀態（但是測驗每個節點的記憶體使用率、CPU利用率等，再做比較選出最閑的那個，效率太低）
高可用
在服務器架構時，為了保證服務器7x24不宕機在線狀態，需要為每臺單點服務器（由一臺服務器提供服務的服務器，如寫服務器、資料庫中間件）提供冗余機，
對于寫服務器來說，需要提供一臺同樣的寫-冗余服務器，當寫服務器健康時（寫-冗余通過心跳檢測），寫-冗余作為一個從機的角色復制寫服務器的內容與其做一個同步；當寫服務器宕機時，寫-冗余服務器便頂上來作為寫服務器繼續提供服務，對外界來說這個處理程序是透明的，即外界僅通過一個IP訪問服務，
典型SQL
線上DDL
DDL(Database Definition Language)是指資料庫表結構的定義（create table）和維護（alter table）的語言，在線上執行DDL，在低于MySQL5.6版本時會導致全表被獨占鎖定，此時表處于維護、不可操作狀態，這會導致該期間對該表的所有訪問無法回應，但是在MySQL5.6之后，支持Online DDL，大大縮短了鎖定時間，
優化技巧是采用的維護表結構的DDL（比如增加一列，或者增加一個索引），是==copy==策略，思路：創建一個滿足新結構的新表，將舊表資料==逐條==匯入（復制）到新表中，以保證==一次性鎖定的內容少==（鎖定的是正在匯入的資料），同時舊表上可以執行其他任務，匯入的程序中，將對舊表的所有操作以日志的形式記錄下來，匯入完畢后，將更新日志在新表上再執行一遍（確保一致性），最后，新表替換舊表（在應用程式中完成，或者是資料庫的rename，視圖完成），
但隨著MySQL的升級，這個問題幾乎淡化了，
資料庫匯入陳述句
在恢復資料時，可能會匯入大量的資料，此時為了快速匯入，需要掌握一些技巧：
- 匯入時==先禁用索引和約束==：
| 1 |
alter table table-name disable keys
|
待資料匯入完成之后，再開啟索引和約束，一次性創建索引
| 1 |
alter table table-name enable keys
|
- 資料庫如果使用的引擎是Innodb，那么它==默認會給每條寫指令加上事務==（這也會消耗一定的時間），因此建議先手動開啟事務，再執行一定量的批量匯入，最后手動提交事務，
- 如果批量匯入的SQL指令格式相同只是資料不同，那么你應該先prepare==預編譯==一下，這樣也能節省很多重復編譯的時間，
limit offset,rows
盡量保證不要出現大的offset，比如limit 10000,10相當于對已查詢出來的行數棄掉前10000行后再取10行，完全可以加一些條件過濾一下（完成篩選），而不應該使用limit跳過已查詢到的資料，這是一個==offset做無用功==的問題，對應實際工程中，要避免出現大頁碼的情況，盡量引導用戶做條件過濾，
select * 要少用
即盡量選擇自己需要的欄位select，但這個影響不是很大，因為網路傳輸多了幾十上百位元組也沒多少延時，并且現在流行的ORM框架都是用的select *，只是我們在設計表的時候注意將大資料量的欄位分離，比如商品詳情可以單獨抽離出一張商品詳情表，這樣在查看商品簡略頁面時的加載速度就不會有影響了，
order by rand()不要用
它的邏輯就是隨機排序（為每條資料生成一個亂數，然后根據亂數大小進行排序），如select * from student order by rand() limit 5的執行效率就很低，因為它為表中的每條資料都生成亂數并進行排序，而我們只要前5條，
解決思路：在應用程式中，將隨機的主鍵生成好，去資料庫中利用主鍵檢索，
單表和多表查詢
多表查詢：join、子查詢都是涉及到多表的查詢，如果你使用explain分析執行計劃你會發現多表查詢也是一個表一個表的處理，最后合并結果，因此可以說單表查詢將計算壓力放在了應用程式上，而多表查詢將計算壓力放在了資料庫上，
現在有ORM框架幫我們解決了單表查詢帶來的物件映射問題（查詢單表時，如果發現有外鍵自動再去查詢關聯表，是一個表一個表查的），
count(*)
在MyISAM存盤引擎中，會自動記錄表的行數，因此使用count(*)能夠快速回傳，而Innodb內部沒有這樣一個計數器，需要我們手動統計記錄數量，解決思路就是單獨使用一張表：
| id | table | count |
|---|---|---|
| 1 | student | 100 |
limit 1
如果可以確定僅僅檢索一條，建議加上limit 1，其實ORM框架幫我們做到了這一點（查詢單條的操作都會自動加上limit 1），
慢查詢日志
用于記錄執行時間超過某個臨界值的SQL日志，用于快速定位慢查詢，為我們的優化做參考，
開啟慢查詢日志
配置項：slow_query_log
可以使用show variables like ‘slov_query_log’查看是否開啟，如果狀態值為OFF，可以使用set GLOBAL slow_query_log = on來開啟，它會在datadir下產生一個xxx-slow.log的檔案，
設定臨界時間
配置項：long_query_time
查看：show VARIABLES like 'long_query_time'，單位秒
設定：set long_query_time=0.5
實操時應該從長時間設定到短的時間，即將最慢的SQL優化掉
查看日志
一旦SQL超過了我們設定的臨界時間就會被記錄到xxx-slow.log中
profile資訊
配置項：profiling
開啟profile
set profiling=on
開啟后，所有的SQL執行的詳細資訊都會被自動記錄下來
| 1 2 3 4 5 6 7 8 9 10 |
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
查看profile資訊
show profiles
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> insert into article values (null,'test profile',':)');
Query OK, 1 row affected (0.15 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------+
| 1 | 0.00086150 | show variables like 'profiling' |
| 2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+
|
通過Query_ID查看某條SQL所有詳細步驟的時間
show profile for query Query_ID
上面show profiles的結果中，每個SQL有一個Query_ID，可以通過它查看執行該SQL經過了哪些步驟，各消耗了多場時間
典型的服務器配置
以下的配置全都取決于實際的運行環境
-
max_connections，最大客戶端連接數
1 2 3 4 5 6 mysql> show variables like'max_connections';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| max_connections |151|+-----------------+-------+ -
table_open_***，表檔案句柄快取（表資料是存盤在磁盤上的，快取磁盤檔案的句柄方便打開檔案讀取資料）
1 2 3 4 5 6 mysql> show variables like'table_open_***';+------------------+-------+| Variable_name | Value |+------------------+-------+| table_open_*** |2000|+------------------+-------+ -
key_buffer_size，索引快取大小（將從磁盤上讀取的索引快取到記憶體，可以設定大一些，有利于快速檢索）
1 2 3 4 5 6 mysql> show variables like'key_buffer_size';+-----------------+---------+| Variable_name | Value |+-----------------+---------+| key_buffer_size |8388608|+-----------------+---------+ -
innodb_buffer_pool_size，Innodb存盤引擎快取池大小（對于Innodb來說最重要的一個配置，如果所有的表用的都是Innodb，那么甚至建議將該值設定到物理記憶體的80%，Innodb的很多性能提升如索引都是依靠這個）
1 2 3 4 5 6 mysql> show variables like'innodb_buffer_pool_size';+-------------------------+---------+| Variable_name | Value |+-------------------------+---------+| innodb_buffer_pool_size |8388608|+-------------------------+---------+ -
innodb_file_per_table（innodb中，表資料存放在.ibd檔案中，如果將該配置項設定為ON，那么一個表對應一個ibd檔案，否則所有innodb共享表空間）
壓測工具mysqlslap
安裝MySQL時附帶了一個壓力測驗工具mysqlslap（位于bin目錄下）
自動生成sql測驗
| 1 2 3 4 5 6 7 8 |
C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 1.219 seconds
Minimum number of seconds to run all queries: 1.219 seconds
Maximum number of seconds to run all queries: 1.219 seconds
Number of clients running queries: 1
Average number of queries per client: 0
|
并發測驗
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 3.578 seconds
Minimum number of seconds to run all queries: 3.578 seconds
Maximum number of seconds to run all queries: 3.578 seconds
Number of clients running queries: 100
Average number of queries per client: 0
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.718 seconds
Minimum number of seconds to run all queries: 5.718 seconds
Maximum number of seconds to run all queries: 5.718 seconds
Number of clients running queries: 150
Average number of queries per client: 0
|
多輪測驗
| 1 2 3 4 5 6 7 8 |
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 5.398 seconds
Minimum number of seconds to run all queries: 4.313 seconds
Maximum number of seconds to run all queries: 6.265 seconds
Number of clients running queries: 150
Average number of queries per client: 0
|
存盤引擎測驗
| 1 2 3 4 5 6 7 8 9 |
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 5.911 seconds
Minimum number of seconds to run all queries: 5.485 seconds
Maximum number of seconds to run all queries: 6.703 seconds
Number of clients running queries: 150
Average number of queries per client: 0
|
| 1 2 3 4 5 6 7 8 9 |
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 53.104 seconds
Minimum number of seconds to run all queries: 46.843 seconds
Maximum number of seconds to run all queries: 60.781 seconds
Number of clients running queries: 150
Average number of queries per client: 0
|
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101356.html
標籤：MySQL
上一篇：SQL基礎（2）