Mysql 高級

1 MySQL 的架構(gòu)介紹

1.1 sql_mode

sql_mode 是一個容易忽視的變量，默認情況下為空，可以忍耐一些非法操作，在生產(chǎn)環(huán)境中，必須將其設置為嚴格模式，在開發(fā)測試環(huán)境中配該變量也是很有必要的，因為這樣可以在生產(chǎn)之前發(fā)現(xiàn)問題。

sql_mode 常用值如下：

• ONLY_FULL_GROUP_BY：對于 GROUP BY 聚合操作，如果在 SELECT 中的列沒有在 GROUP BY 中出現(xiàn)，那么這個 sql 是不合法的
• NO_AUTO_VALUE_ON_ZERO：該值影響自增列的插入，默認情況下，插入 0 或 NULL 代表生成下一個增長值，如果用戶希望插入的主鍵 ID 為0，就可以配置這個值
• STRICT_TRANS_TABLES：如果一個值不能插入到一個事務表中，則中斷當前的操作，非事務表不限制
• NO_ZERO_IN_DATE：在嚴格模式下，不允許日期和月份為零
• NO_ZERO_DATE：不允許插入零日期，否則拋出異常
• ERROR_FOR_DIVISION_BY_ZERO：在插入或更新過程中，如果數(shù)據(jù)被零除，則產(chǎn)生錯誤，如果未配置該參數(shù)，那么數(shù)據(jù)被零除時返回 NULL
• NO_AUTO_CREATE_USER：禁止創(chuàng)建密碼為空的用戶
• NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎未編譯或被禁用，則拋出錯誤
• PIPES_AS_CONCAT：將 || 視為字符串連接操作符
• ANSI_QUOTES：配置該參數(shù)后，不能用 “” 引用字符串

1.2 MySQL 邏輯架構(gòu)

和其它數(shù)據(jù)庫相比，MySQL 有點與眾不同，它的架構(gòu)可以在多種不同場景中應用并發(fā)揮良好作用，主要體現(xiàn)在存儲引擎的架構(gòu)上，插件式的存儲引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務以及數(shù)據(jù)的存儲提取相分離，這種架構(gòu)可以根據(jù)業(yè)務的需求和實際需要選擇合適的存儲引擎。

連接層

最上層是客戶端和連接服務，包含本地 socket 通信和 tcp/ip 通信，主要完成連接處理、授權(quán)認證及相關(guān)的安全方案，該層引入了線程池，為授權(quán)用戶提供線程，還實現(xiàn)了 ssl 安全鏈接。

服務層

• Management Serveices & Utilities：系統(tǒng)管理和控制工具
• SQL Interface：SQL 接口，接收 SQL 命令，并返回查詢結(jié)果
• Parser：解析器，對 SQL 命令進行驗證和解析
• Optimizer：查詢優(yōu)化器，在查詢之前對語句進行優(yōu)化
• Cache 和 Buffer：查詢緩存，如果查詢緩存有命中的查詢結(jié)果，查詢語句就可以直接去查詢緩存中取數(shù)據(jù)，這個緩存機制是由一系列小緩存組成的，比如表緩存，記錄緩存，key緩存，權(quán)限緩存等

引擎層

存儲引擎層，負責了數(shù)據(jù)的存儲和提取，服務器通過 API 與存儲引擎進行通信。

存儲層

數(shù)據(jù)存儲層，主要是將數(shù)據(jù)存儲在運行于裸設備的文件系統(tǒng)之上，并完成與存儲引擎的交互。

1.2.1 SQL 的執(zhí)行周期

開啟診斷分析工具

set profiling=1;

顯示最近的幾條查詢

show profiles;

查看 SQL 的執(zhí)行步驟

show profile cpu,block io for query 1;

1.2.2 查詢流程

• 客戶端和 MySQL 建立連接，發(fā)送查詢語句，先查詢緩存，如果緩存有命中，直接返回結(jié)果，否則進行語句解析
• 也就是說，解析之前先訪問緩存，解析器將對語句進行語法規(guī)則校驗和解析查詢，然后會生成一顆解析樹
• 之后由優(yōu)化器將解析器轉(zhuǎn)換成執(zhí)行計劃，最后執(zhí)行計劃，返回結(jié)果

1.2.3 SQL 執(zhí)行順序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where__condition>
GROUP BY<group_by_list>
HAVING <having__condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by__condition>
LIMIT <limit_number>

1.3 MySQL 存儲引擎

查看支持的存儲引擎

show engines;

查看當前默認的存儲引擎

show variables like '%storage_engine%';

1.3.1 各個引擎簡介

InnoDB

InnoDB 是 MySQL 默認的事務型引擎，用來處理大量的短期事務，除非有特別的原因需要用到其他存儲引擎，否則優(yōu)先考慮 InnoDB。

MyISAM

MyISAM 提供了大量的特性，包括全文檢索、壓縮、空間函數(shù)等，但 MyISAM 不支持事務和行級鎖，缺點是崩潰后無法安全恢復。

Archive

Archive 檔案存儲引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引；

Archive 表適合日志和數(shù)據(jù)采集類應用；

根據(jù)英文的測試結(jié)論來看，Archive 表比 MyISAM 表要小大約 75%，比支持事務處理的 InnoDB 表小大約 83%。

Blackhole

Blackhole 引擎沒有實現(xiàn)任何存儲機制，它會丟棄所有插入的數(shù)據(jù)，不做任何保存。但服務器會記錄Blackhole表的日志，所以可以用于復制數(shù)據(jù)到備庫，或者簡單地記錄到日志。但這種應用方式會碰到很多問題，因此并不推薦。

CSV

CSV 引擎可以將普通的 CSV 文件作為 MySQL 表來處理，但不支持索引， CSV 可以作為一種數(shù)據(jù)交換的機制，CSV 引擎存儲的數(shù)據(jù)可以被文本編輯器、execl 讀取。

Memory

如果需要快速地訪問數(shù)據(jù)，并且這些數(shù)據(jù)不會被修改，重啟以后丟失也沒有關(guān)系，那么使用Memory表是非常有用，Memory 表至少比 MyISAM 表要快一個數(shù)量級。

Federated

Federated 引擎是訪問其他 MySQL 服務器的一個代理，盡管該引擎看起來提供了一種很好的跨服務器的靈活性，但也經(jīng)常帶來問題，因此默認是禁用的。

1.3.2 InnoDB 和 MyISAM

2 索引優(yōu)化分析

2.1 優(yōu)化步驟

分庫分表

SQL 優(yōu)化

建立索引

調(diào)整 my.cnf 優(yōu)化服務器及配置參數(shù)

2.2 索引簡介

2.2.1 什么是索引？

數(shù)據(jù)本身之外，數(shù)據(jù)庫還維護著一個滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式指向數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)的基礎上實現(xiàn)高級查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引；

一般來說索引本身也很大，不可能全部存儲在內(nèi)存中，因此索引往往以索引文件的形式存儲的磁盤上；

雖然索引提高了查詢的效率，但是也降低了更新的效率，因為更新表時，不僅要插入數(shù)據(jù)，同時還要保存一下索引文件每次更新添加了的索引列的字段，都會調(diào)整因為更新所帶來的鍵值變化后的索引信息；

實際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的。

2.2.2 MySQL 索引結(jié)構(gòu)

2.2.2.1 BTree 索引

如圖所示，磁盤塊 1 包含數(shù)據(jù)項 17 和 35，包含指針 P1、P2、P3

P1 表示小于 17 的磁盤塊，P2 表示介于 17 和 35 之間的磁盤塊，35 表示大于 35 的磁盤塊

查找過程

如果要查找數(shù)據(jù)項 29，首先將磁盤塊 1 加載到內(nèi)存，此時發(fā)生一次 IO，利用二分查找確定 29 在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，通過磁盤塊 1 的 P2 指針的磁盤地址把磁盤塊 3 加載到內(nèi)存，此時發(fā)生一次 IO，利用二分查找確定 29 在26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過磁盤塊 3 的 P2 指針的磁盤地址把磁盤塊 8 加載到內(nèi)存，此時發(fā)生一次 IO，同時利用二分查找到 29，查詢結(jié)束。

2.2.2.2 B+Tree 索引

B+ 樹的非葉子節(jié)點只是存儲 key，占用空間非常小，因此每一層的節(jié)點能索引到的數(shù)據(jù)范圍更加的廣，換句話說，每次 IO 操作可以觀看更多的數(shù)據(jù)；

葉子節(jié)點兩兩相連，符合磁盤的預讀特性。如圖存儲 5、8 、9 的葉子節(jié)點，它有個指針指向了 10、15、18 這個葉子節(jié)點，那么當我們從磁盤讀取5、8、9 對應的數(shù)據(jù)的時候，由于磁盤的預讀特性，會順便把 10、15、18 對應的數(shù)據(jù)讀取出來，這個時候?qū)儆陧樞蜃x取，而不是磁盤尋道了，加快了速度；

支持范圍查詢，而且部分范圍查詢非常高效，原因是數(shù)據(jù)都是存儲在葉子節(jié)點這一層，并且有指針指向其他葉子節(jié)點，這樣范圍查詢只需要遍歷葉子節(jié)點這一層，無需整棵樹遍歷。

2.2.2.3 聚簇索引與非聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式，聚簇表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲在一起；

按照聚簇索引排列順序，查詢顯示一定范圍數(shù)據(jù)的時候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫不不用從多個數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量的 IO 操作；

對于 MySQL 數(shù)據(jù)庫目前只有 InnoDB 數(shù)據(jù)引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引；

由于數(shù)據(jù)物理存儲排序方式只能有一種，所以每個 MySQL 的表只能有一個聚簇索引，一般情況下就是該表的主鍵；

為了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主鍵列盡量選用有序的順序 ID，而不建議用無序的 ID，比如 UUID這種。

2.2.3 MySQL 索引分類

2.2.2.3.1 單值索引

即一個索引只包含單個列，一個表可以有多個單列索引

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name)
);

單獨建單值索引： 
CREATE INDEX idx_customer_name ON customer (customer_name);

刪除索引： 
DROP INDEX idx_customer_name ON customer;

2.2.3.2 唯一索引

索引列的值必須唯一，但可以為空

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name),
    UNIQUE (customer_no)
);

單獨建唯一索引： 
CREATE UNIQUE INDEX idx_customer_no ON customer (customer_no);

刪除索引： 
DROP INDEX idx_customer_no ON customer;

2.2.3.3 主鍵索引

設為主鍵后自動創(chuàng)建主鍵索引

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id)
);

單獨建主鍵索引： 
ALTER TABLE customer ADD PRIMARY KEY customer (customer_no);

刪除建主鍵索引： 
ALTER TABLE customer DROP PRIMARY KEY;

修改建主鍵索引： 必須先刪除掉 (DROP) 原索引，再新建 (ADD) 索引

2.2.3.4 復合索引

一個索引包含單個列

隨表一起建索引： 
CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id),
    KEY (customer_name),
    UNIQUE (customer_name),
    KEY (customer_no, customer_name)
);

單獨建索引： 
CREATE INDEX idx_no_name ON customer (customer_no, customer_name);

刪除索引： 
DROP INDEX idx_no_name ON customer;

2.2.4 創(chuàng)建索引的時機

哪些情況需要創(chuàng)建索引？

• 主鍵自動創(chuàng)建唯一索引
• 頻繁作為查詢條件的字段應該創(chuàng)建索引
• 查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引
• 組合索引比單值索引性價比更高
• 查詢排序的字段，排序字段若通過索引去訪問將大大提高排序速度
• 查詢中統(tǒng)計或分組字段

哪些情況不需要創(chuàng)建索引？

• 表記錄太少
• 經(jīng)常更新的表
• where 條件里用不到的字段不需要創(chuàng)建索引
• 過濾性不好的字段不需要創(chuàng)建索引

2.3 性能分析

2.3.1 EXPLAN

使用 EXPLAIN 關(guān)鍵字可以模擬優(yōu)化器執(zhí)行 SQL 查詢語句，從而知道 MySQL 是如何處理 SQL 語句的，分析查詢語句或是表結(jié)構(gòu)的性能瓶頸。

EXPLAN 的作用：

查看表的讀取順序

查看哪些索引可以被使用

數(shù)據(jù)讀取操作的操作類型

哪些索引被實際使用

表之間的引用

使用方式：

Explain + SQL

Explain SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
    -> UNION
    -> SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

2.3.2 各字段解釋

• id：

  SELECT 查詢的序列號,包含一組數(shù)字，表示查詢中執(zhí)行 SELECT 子句或操作表的順序

  • id 相同：執(zhí)行順序由上至下

  • id 不同：如果是子查詢，id 的序號會遞增，id 值越大優(yōu)先級越高，越先被執(zhí)行

  • id 相同不同，同時存在：id如果相同，可以認為是一組，從上往下順序執(zhí)行；在所有組中，id值越大，優(yōu)先級越高，越先執(zhí)行

  每個 id 表示一趟獨立的查詢，一個 SQL 的查詢趟數(shù)越少越好

• select_type

  查詢的類型，主要是用于區(qū)別普通查詢、聯(lián)合查詢、子查詢等的復雜查詢

  • SIMPLE：最簡單的查詢，不包含 UNION 和子查詢

  • PRIMARY：查詢中若包含復雜的子部分，最外層查詢被標記為 PRIMARY

  • DERIVED：在 FROM 列表中包含的子查詢被標記為 DERIVED，MySQL 會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時表里

  • SUBQUERY：在 SELECT 或 WHERE 列表中包含子查詢

  • DEPENDENT SUB：在 SELECT 或 WHERE 列表中包含子查詢，子查詢基于外層

  • UNCACHEABLE SUBQUREY：結(jié)果集不能被緩存的子查詢，必須重新為外層查詢的每一行進行評估

  • UNION：若第二個 SELECT 出現(xiàn)在 UNION 之后，則被標記為 UNION

  • UNION RESULT：從 UNION 表獲取結(jié)果的 SELECT

• table

  顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

• type

  顯示連接使用的類型，按最優(yōu)到最差的類型排序

  • system：表只有一行記錄

  • const：表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引，因為只匹配一行數(shù)據(jù)，所以很快
    如將主鍵置于 where 列表中，MySQL 就能將該查詢轉(zhuǎn)換為一個常量

  • eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配，常見于主鍵或唯一索引掃描

  • ref：非唯一性索引掃描，返回匹配某個單獨值的所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以它應該屬于查找和掃描的混合體

  • range：只檢索給定范圍的行，使用一個索引來選擇行，key 列顯示使用了哪個索引，一般就是在 where 語句中出現(xiàn)了 between、<、>、in 等的查詢，這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束語另一點，不用掃描全部索引

  • index：出現(xiàn) index 是 SQL 使用了索引但是沒用通過索引進行過濾，一般是使用了覆蓋索引或者是利用索引進行了排序分組

  • all：Full Table Scan，將遍歷全表以找到匹配的行

  • index_merge：在查詢過程中需要多個索引組合使用，通常出現(xiàn)在有 or 的關(guān)鍵字的 SQL 中

  • ref_or_null：對于某個字段既需要關(guān)聯(lián)條件，也需要 null 值的情況下，查詢優(yōu)化器會選擇用 ref_or_null 連接查詢

  • index_subquery：利用索引來關(guān)聯(lián)子查詢，不再全表掃描

  • unique_subquery：該聯(lián)接類型類似于 index_subquery，子查詢中的唯一索引

  一般來說，得保證查詢至少達到 range 級別，最好能達到 ref

• possible_keys

  顯示可能應用在這張表中的索引，一個或多個，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實際使用

• key

  實際使用的索引，如果為NULL，則沒有使用索引，查詢中若使用了覆蓋索引，則該索引和查詢的 select 字段重疊

• key_len

  表示索引中使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引的長度， key_len 字段能夠檢查是否充分的利用上了索引

• ref

  顯示索引的哪一列被使用了，如果可能的話，是一個常數(shù)，哪些列或常量被用于查找索引列上的值

• rows

  rows 列顯示 MySQL 認為它執(zhí)行查詢時必須檢查的行數(shù)

• Extra

  包含不適合在其他列中顯示但十分重要的額外信息

  • Using filesort：說明 mysql 會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取，MySQL 中無法利用索引完成的排序操作稱為文件排序
  • Using temporary：使了用臨時表保存中間結(jié)果，MySQL 在對查詢結(jié)果排序時使用臨時表，常見于排序 order by 和分組查詢 group by
  • USING index：表示相應的 select 操作中使用了覆蓋索引(Covering Index)，避免訪問了表的數(shù)據(jù)行，效率不錯，如果同時出現(xiàn) using where，表明索引被用來執(zhí)行索引鍵值的查找，如果沒有同時出現(xiàn) using where，表明索引只是用來讀取數(shù)據(jù)而非利用索引執(zhí)行查找
  • Using where：表明使用了 where 過濾
  • using join buffer：使用了連接緩存：
  • impossible where：where 子句的值總是 false，不能用來獲取任何元組
  • select tables optimized away：在沒有 GROUP BY 子句的情況下，基于索引優(yōu)化 MIN/MAX 操作或者對于MyISAM 存儲引擎優(yōu)化 COUNT(*) 操作，不必等到執(zhí)行階段再進行計算，查詢執(zhí)行計劃生成的階段即完成優(yōu)化。

2.4 查詢優(yōu)化

2.4.1 單表使用索引失效問題

index(a,b,c)

創(chuàng)建索引的建議：

對于單值索引，盡量選擇針對當前查詢過濾性更高的字段

選擇組合索引，當前查詢過濾性最高的字段在索引的位置越靠前越好

選擇組合索引，盡量選擇可以能夠包含當前查詢中的 where 字句中更多字段的索引

在選擇組合索引的時候，如果某個字段可能出現(xiàn)范圍查詢時，盡量把這個字段放在索引次序的最后面

2.4.2 關(guān)聯(lián)查詢優(yōu)化

保證被驅(qū)動表的 join 字段已經(jīng)被索引

left join 時，選擇小表作為驅(qū)動表，大表作為被驅(qū)動表

inner join 時，MySQL 會自己把小結(jié)果集的表選為驅(qū)動表

子查詢盡量不要放在被驅(qū)動表，有可能使用不到索引

能夠直接多表關(guān)聯(lián)的盡量直接關(guān)聯(lián)，不用子查詢

2.4.3 子查詢優(yōu)化

盡量不要使用not in 或者 not exists，用 left join on xxx is null 替代

2.4.4 排序分組優(yōu)化

ORDER BY子句，盡量使用Index方式排序,避免使用FileSort方式排序

如果不在索引列上，filesort 有兩種算法：

雙路排序

• MySQL 4.1 之前是使用雙路排序,字面意思就是兩次掃描磁盤，最終得到數(shù)據(jù)，讀取行指針和 order by 列，對他們進行排序，然后掃描已經(jīng)排序好的列表，按照列表中的值重新從列表中讀取對應的數(shù)據(jù)輸出
• 從磁盤取排序字段，在 buffer 進行排序，再從磁盤取其他字段
• 取一批數(shù)據(jù)，要對磁盤進行了兩次掃描，眾所周知，IO 是很耗時的，所以在 MySQL 4.1 之后，出現(xiàn)了第二種改進的算法，就是單路排序

單路排序：

• 從磁盤讀取查詢需要的所有列，按照 order by 列在 buffer 對它們進行排序，然后掃描排序后的列表進行輸出，它的效率更快一些，避免了第二次讀取數(shù)據(jù)，并且把隨機 IO 變成了順序 IO，但是它會使用更多的空間，因為它把每一行都保存在內(nèi)存中了
• 單路排序是把所有的字段都取出，所以有可能取出的數(shù)據(jù)的總大小超過了 sort_buffer 的容量，導致每次只能取 sort_buffer 容量大小的數(shù)據(jù)進行排序，排序完再取一部分，反而增大了 IO
• 優(yōu)化策略
  • 增大sort_buffer_size參數(shù)的設置
  • 增大max_length_for_sort_data參數(shù)的設置
  • 減少 select 后面的查詢的字段
  • 禁止使用 select *

group by 使用索引的原則幾乎跟 order by 一致 ，唯一區(qū)別是 group by 即使沒有過濾條件用到索引，也可以直接使用索引

3 查詢截取分析

什么是慢查詢?nèi)罩荆?/p>

慢查詢?nèi)罩臼?MySQL 提供的一種日志記錄，它用來記錄在 MySQL 中響應時間超過閾值的語句，具體指運行時間超過long_query_time 值的 SQL，則會被記錄到慢查詢?nèi)罩局校?/p>

long_query_time 的默認值為10，意思是運行10秒以上的語句。

默認慢查詢?nèi)罩臼顷P(guān)閉的，需要手動開啟

查看慢查詢?nèi)罩臼欠耖_啟
SHOW VARIABLES LIKE '%slow_query_log%';
開啟慢查詢?nèi)罩?set global slow_query_log=1;

查看并配置 long_query_time

查看long_query_time
SHOW VARIABLES LIKE 'long_query_time%';
set  long_query_time=1

日志分析工具 mysqldumpslow

常用參考：

hadoop100得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log

得到訪問次數(shù)最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hadoop100-slow.log

得到按照時間排序的前10條里面含有左連接的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hadoop100-slow.log

另外建議在使用這些命令時結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/hadoop100-slow.log | more

4 主從復制

復制的基本原理

master 將改變記錄到二進制日志（binary log），這些記錄過程叫做二進制日志事件，binary log events；

slave 將 master 的 binary log events 拷貝到它的中繼日志（relay log）；

slave 重做中繼日志中的事件，將改變應用到自己的數(shù)據(jù)庫中，MySQL 復制是異步的且串行化的。

復制的基本原則

每個 slave 只有一個 master

每個 slave 只能有一個唯一的服務器 ID

每個 master 可以有多個salve

4.1 配置主從復制

1、配置主數(shù)據(jù)庫

vim /etc/my.cnf

server-id=1
log-bin=mysql-bin
binlog_format=mixed

為從服務分配賬號

查看主服務器 BIN 日志的信息

show master status;

重啟主數(shù)據(jù)庫

systemctl restart mariadb

2、配置從數(shù)據(jù)庫

連接主數(shù)據(jù)庫

CHANGE MASTER TO 
    -> MASTER_HOST="192.168.10.100",
    -> MASTER_USER="slave",
    -> MASTER_PASSWORD="123456",
    -> MASTER_LOG_FILE="mysql-bin.000001",
    -> MASTER_LOG_POS=388;

啟動從數(shù)據(jù)庫

start slave;