MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列2：主從復(fù)制同步與查詢性能調(diào)優(yōu)

一广鳍、主從復(fù)制同步部署

1、概念

主從復(fù)制：2臺以上mysql服務(wù)器蝶溶， 做負(fù)載均衡嗜历， 主服務(wù)器負(fù)責(zé)增刪改 ， 從服務(wù)器負(fù)責(zé)查詢

同步原理：mysql開啟bin-log日志身坐，主服務(wù)器所有的增刪改操作會記錄到bin-log日志秸脱；然后主服務(wù)器把bin-log日志發(fā)送 給 從服務(wù)器 ， 從服務(wù)器重放bin-log日志 確保數(shù)據(jù)同步

2部蛇、開啟bin-log日志

配置 my.cnf 文件 并重啟 mysql

[root@localhost etc]# vim /etc/my.cnf

[root@localhost etc]# service mysql restart

開啟之后 mysql-bin對應(yīng)的文件 已經(jīng)出現(xiàn)

[root@localhost var]# cd /usr/local/mysql/var && ll

通過 show master status 命令查看 最新一個(gè)binlog日志 及開始行數(shù)

mysql> show master status;

查看binlog日志內(nèi)容 可見 最新一行日志在位置107

$ /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001

測試刪除數(shù)據(jù) 可見 binlog文件新增日志內(nèi)容

3摊唇、bin-log日志相關(guān)命令

flush logs

新建一個(gè)binlog日志，增刪改日志在新文件中插入涯鲁，新的日志end-log-positon 是107行巷查，107行記錄了mysql內(nèi)部日志有序。

reset master

清空所有bin-log日志 只保留 mysql-bin.000001 文件

mysqlbinlog

查看bin-log日志/usr/local/mysql/bin/mysqlbinlog/usr/local/mysql/var/mysql-bin.00001

show binlog events 查看binlog記錄事件

mysqlbinlog mysql -uroot -psmudge smudge_database

重放bin-log日志、恢復(fù)數(shù)據(jù)：其實(shí)就是再把日志中的sql語句執(zhí)行一邊而已岛请。(注意：select 語句 和delete語句 不可以放在一起重放 因?yàn)槟阕詈筮€是得不到數(shù)據(jù))

恢復(fù)原理就是：執(zhí)行之前的insert語句旭寿，或者之前的update語句

如果你的單純的delete物理刪除，別想恢復(fù)了崇败，因?yàn)樵俅螆?zhí)行的還是delete語句

4盅称、create 創(chuàng)建用戶 + grant用戶授權(quán)

主服務(wù)器查看用戶密碼

mysql> select host，user后室，password from mysql.user;

添加主服務(wù)器用戶密碼

CREATE USER 'kang'@'192.168.206.132' IDENTIFIED BY 'smudge';

創(chuàng)建用戶kang 可以在ip為192.168.206.132主機(jī)上訪問數(shù)據(jù)庫

給用戶kang授權(quán)所有的庫的權(quán)限

5缩膝、主服務(wù)器配置

主服務(wù)器ip：192.168.206.128

配置主服務(wù)器my.cnf 文件

vim /etc/my.cnf

配置之后刷新binlog文件

flush logs with read lock 確保獲得一致性快照，等待主從binlog日志同步完畢達(dá)到數(shù)據(jù)一致

或者使用mysqldump備份sql 文件

將主服務(wù)器一致都是sql文件備份岸霹，傳遞到從服務(wù)器

mysqldump -uroot -psmudge smudge -l -F > '/home/smudge.sql'

-l 是指鎖表 防止新數(shù)據(jù)插入

-F 是刷新 生成一個(gè)新的binlog日志

(如果你數(shù)據(jù)庫中有merge表 容易會提示Unable to open underlying table which is differently defined or ofnon-MyISAM type ordoesn't exist when using LOCK TABLES )

使用scp隧道傳輸命令 傳遞文件

scp /home/smudge.sql 192.168.206.132:/home

6疾层、從服務(wù)器配置

恢復(fù)一部分主服務(wù)器備份的數(shù)據(jù)

新建smudge庫

mysql導(dǎo)入sql文件

[root@localhost~]# mysql -uroot -psmudge smudge < /home/smudge.sql

配置從服務(wù)器my.cnf文件

vim /etc/my.cnf

其中用戶名和密碼就是上述我們在主服務(wù)器添加的信息

(如果你的mysql版本5.1(mysql>status查看)之前的，配置這4項(xiàng)贡避，啟動之后就不必使用change master 命令 進(jìn)行主動同步)

保存并重啟mysql

查看主服務(wù)器master binlog文

啟動slave進(jìn)程痛黎，開啟主從同步

因?yàn)槲业膍ysql版本是5.7的， 所以我使用change master命令

show slave status 查看從服務(wù)器狀態(tài)

表明同步功能已經(jīng)開啟

7刮吧、從服務(wù)器常用命令

start slave 啟動復(fù)制線程

stop slave 停止復(fù)制線程

show master logs 查看主數(shù)據(jù)庫日志

change master to master_host 湖饱，master_user 動態(tài)切換主數(shù)據(jù)庫

show processlist 查看運(yùn)行進(jìn)程 (主動服務(wù)器都適用)

8、常見錯(cuò)誤排錯(cuò)

show slave status 檢查主動狀態(tài)

20?數(shù)值為NO

21?數(shù)值為NULL

表明同步出現(xiàn)了故障皇筛，可能是slave服務(wù)器執(zhí)行了寫操作或者從服務(wù)器重啟有事務(wù)回滾操作琉历。

解決

從服務(wù)器:stop slave?關(guān)閉復(fù)制線程

主服務(wù)器：show master status?查看最新二進(jìn)制文件和位置偏移量

從服務(wù)器執(zhí)行：change master to master_host ...

master_log_file='mysql-bin.000005'坠七，master_log_pos=759?命令

二水醋、查詢性能優(yōu)化

1、查詢執(zhí)行基礎(chǔ)知識

mysql執(zhí)行查詢過程

①客戶端將查詢發(fā)送到服務(wù)器

② 服務(wù)器檢查查詢緩存 如果找到了就從緩存返回結(jié)果 否則進(jìn)行下一步

③ 服務(wù)器解析彪置，預(yù)處理和優(yōu)化查詢拄踪，生成執(zhí)行計(jì)劃

④ 執(zhí)行引擎調(diào)用存儲引擎api執(zhí)行查詢

⑤ 服務(wù)器將結(jié)果發(fā)送回客戶端

mysql客戶端/服務(wù)器協(xié)議

該協(xié)議是半雙工通信郑诺，可以發(fā)送或接收數(shù)據(jù)竖共，但是不能同時(shí)發(fā)送和接收決定了mysql的溝通簡單又快捷；

缺點(diǎn)：無法進(jìn)行流程控制淀歇，一旦一方發(fā)送消息潘懊，另一方在發(fā)送回復(fù)之前必須提取完整的消息姚糊，就像拋球游戲，任意時(shí)間授舟，只有某一方有球救恨，而且有球在手上，否則就不能把球拋出去(發(fā)送消息)

mysql客戶端發(fā)送/服務(wù)器響應(yīng)

可以設(shè)定max_packet_size這個(gè)參數(shù)控制客戶端發(fā)送的數(shù)據(jù)包(一旦發(fā)送數(shù)據(jù)包释树，唯一做的就是等待結(jié)果)

服務(wù)器發(fā)送的響應(yīng)由多個(gè)數(shù)據(jù)包組成肠槽， 客戶端必須完整接收結(jié)果擎淤，即使只需要幾行數(shù)據(jù)，也得等到全部接收 然后丟掉秸仙，或者強(qiáng)制斷開連接嘴拢。(這兩個(gè)方法好挫，所以我們使用limit子句呀＜偶汀席吴！)

也可以理解，客戶端從服務(wù)器 "拉" 數(shù)據(jù) 捞蛋，實(shí)際是服務(wù)器產(chǎn)生數(shù)據(jù) "推"到客戶端抢腐， 客戶端不能說不要 是必須全部裝著！

常用的Mysql類庫 其實(shí)是從客戶端提取數(shù)據(jù) 緩存到array(內(nèi)存)中襟交，然后進(jìn)行 foreach 處理迈倍。

但是對于龐大的結(jié)果集裝載在內(nèi)存中需要很長時(shí)間，如果不緩存捣域，使用較少的內(nèi)存并且可以盡快工作啼染，但是應(yīng)用程序和類庫交互時(shí)候，服務(wù)器端的鎖和資源都是被鎖定的焕梅。

查詢狀態(tài)

每個(gè)mysql連接都是mysql服務(wù)器的一個(gè)線程 任意一個(gè)給定的時(shí)間都有一個(gè)狀態(tài)來標(biāo)識正在發(fā)生的事情迹鹅。

使用 show full processlist 命令查看

mysql中一共有12個(gè)狀態(tài)：休眠、查詢贞言、鎖定斜棚、分析和統(tǒng)計(jì)、拷貝到磁盤上的臨時(shí)表该窗、排序結(jié)果弟蚀、發(fā)送數(shù)據(jù)，通過這些狀態(tài) 知道 "球在誰手上"酗失。

查詢緩存

解析一個(gè)查詢义钉，如果開啟了緩存，mysql會檢查查詢緩存规肴，發(fā)現(xiàn)緩存匹配捶闸，返回緩存之前，檢查查詢的權(quán)限拖刃。

2删壮、優(yōu)化數(shù)據(jù)訪問

查詢性能低下最基本的原因是訪問了太多的數(shù)據(jù)，分析兩方面：

① 查明應(yīng)用程序是否獲取超過需要的數(shù)據(jù) 通常意味著訪問了過多的行或列

②查明mysql服務(wù)器是否分析了超過需要的行

向服務(wù)器請求了不需要的數(shù)據(jù)

一般請求不需要的數(shù)據(jù)兑牡，再丟掉他們央碟，造成服務(wù)器額外的負(fù)擔(dān)，增加網(wǎng)絡(luò)開銷发绢，消耗了內(nèi)存和cpu硬耍。

典型的錯(cuò)誤：

① 提取超過需要的行 => 添加 limit 10 控制獲取行數(shù)

② 多表聯(lián)接提取所有列 => select fruit.* from fruit left join fruit_juice where

.....

③ 提取所有的列 => select id垄琐，name... from fruit ... (有時(shí)提取超過需要的數(shù)據(jù)便于復(fù)用)

mysql檢查了太多數(shù)據(jù)

簡單的開銷指標(biāo)：執(zhí)行時(shí)間、檢查的行數(shù)经柴、返回的行數(shù)狸窘。

以上三個(gè)指標(biāo)寫入了慢查詢?nèi)罩?可以使用 mysqlsla工具進(jìn)行日志分析：

① 執(zhí)行時(shí)間：執(zhí)行時(shí)間只是參考 不可一概而論 因?yàn)閳?zhí)行時(shí)間 和服務(wù)器當(dāng)時(shí)負(fù)載有關(guān)

② 檢查和返回的行：理想情況下返回的行和檢查的行一樣，但是顯示基本不可能 比如聯(lián)接查詢

③檢查的行和訪問類型： 使用explain sq語句坯认，觀察typ列

typ列：(訪問速度依次遞增)

① 全表掃描(full table scan)

② 索引掃描(index scan)

③ 范圍掃描(range scan)

④ 唯一索引查找(unique index lookup)

⑤ 常量(constant)

可見type列為index即sql語句翻擒，基于索引掃描：

rows列為12731，即掃描了12731行 extra列為using index牛哺，即使用索引過濾不需要的行

mysql會在3種情況下使用where子句陋气，從最好到最壞依次是：

① 對索引查找應(yīng)用where子句來消除不匹配的行 這發(fā)生在存儲層

② 使用覆蓋索引(extra 列 "using index") 避免訪問行 從索引取得數(shù)據(jù)過濾不匹配的行 這發(fā)生在服務(wù)層不需要從表中讀取行

③ 從表中檢索出數(shù)據(jù) 過濾不匹配的行(extra:using where)

如果發(fā)現(xiàn)訪問數(shù)據(jù)行數(shù)很大，嘗試以下措施：

① 使用覆蓋索引 引润，存儲了數(shù)據(jù) 存儲引擎不會讀取完整的行

② 更改架構(gòu)使用匯總表

③ 重寫復(fù)雜的查詢 讓mysql優(yōu)化器優(yōu)化執(zhí)行它

3巩趁、重構(gòu)查詢的方式

優(yōu)化有問題的查詢，其實(shí)也可以找到替代方案淳附，提供更高的效率议慰。

復(fù)雜查詢和多個(gè)查詢

mysql一般服務(wù)器可以每秒50000個(gè)查詢，常規(guī)情況下奴曙，使用盡可能少的查詢 有時(shí)候分解查詢得到更高的效率别凹。

縮短查詢

分治法，查詢本質(zhì)上不變洽糟，每次執(zhí)行一小部分炉菲，以減少受影響的行數(shù)。比如清理陳舊的數(shù)據(jù)坤溃，每次清理1000條：

delete from message where create < date_sub(now()拍霜，inteval 3 month)

limit 1000

防止長時(shí)間鎖住很多行的數(shù)據(jù)。

分解聯(lián)接

把一個(gè)多表聯(lián)接分解成多個(gè)單個(gè)查詢 然后在應(yīng)用程序?qū)崿F(xiàn)聯(lián)接操作

第一眼看上去比較浪費(fèi)浇雹，因?yàn)樵黾恿瞬樵償?shù)量沉御，但是有重大的性能優(yōu)勢：

① 緩存效率高，應(yīng)用程序直接緩存了表 類似第一個(gè)查詢直接跳過

② 對于myisam表來說 每個(gè)表一個(gè)查詢有效利用表鎖 查詢鎖住表的時(shí)間縮短

③ 應(yīng)用程端進(jìn)行聯(lián)接更方便擴(kuò)展數(shù)據(jù)庫

④ 使用in() 避免聯(lián)表查詢id排序的耗費(fèi)

⑤ 減少多余行的訪問 昭灵， 意味著每行數(shù)據(jù)只訪問一次 避免聯(lián)接查詢的非正則化的架構(gòu)帶來的反復(fù)訪問同一行的弊端

分解聯(lián)接應(yīng)用場景：

① 可以緩存早期查詢的大量的數(shù)據(jù)

② 使用了多個(gè)myisam表(mysiam表鎖 并發(fā)時(shí)候 一條sql鎖住多個(gè)表 所以要分解)

③ 數(shù)據(jù)分布在不同的服務(wù)器上

④ 對于大表使用in() 替換聯(lián)接

④一個(gè)聯(lián)接引用了同一個(gè)表很多次

提取隨機(jī)行

分組查詢

外鍵

只有Innodb引擎支持外鍵，myisam可以添加外鍵但是沒有效果伐谈。

主表添加主鍵id烂完，從表添加外鍵id引用主表的id。

表student

表student_extend

為student_extend添加外鍵诵棵，外鍵指向student表中的id列抠蚣，在delete時(shí)觸發(fā)外鍵。

表student數(shù)據(jù)

表student_extend數(shù)據(jù)

刪除表student一條數(shù)據(jù)履澳，則外鍵表就會觸發(fā)外鍵嘶窄，刪除對應(yīng)數(shù)據(jù)：

delete from student where id = 2;

優(yōu)化聯(lián)合查詢

優(yōu)化max() min()

其中 name 沒有索引

對一個(gè)表同時(shí)進(jìn)行select和update

上期回顧：《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列1：數(shù)據(jù)類型與索引調(diào)優(yōu)全解析》