《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列2：主從復(fù)制同步與查詢性能調(diào)優(yōu)》要點(diǎn)：
本文介紹了MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列2：主從復(fù)制同步與查詢性能調(diào)優(yōu)，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

1、主從復(fù)制同步部署

1、觀點(diǎn)

• 主從復(fù)制：2臺(tái)以上mysql服務(wù)器, 做負(fù)載均衡, 主服務(wù)器負(fù)責(zé)增編削 , 從服務(wù)器負(fù)責(zé)查詢

• 同步原理：mysql開啟bin-log日志,主服務(wù)器所有的增編削操作會(huì)記錄到bin-log日志；然后主服務(wù)器把bin-log日志發(fā)送 給 從服務(wù)器 , 從服務(wù)器重放bin-log日志 確保數(shù)據(jù)同步

2、開啟bin-log日記

• 設(shè)置裝備擺設(shè) my.cnf 文件 并重啟 mysql

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

[root@localhost etc]# service mysql restart

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

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

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

mysql> show master status;

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

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

• 測(cè)試刪除數(shù)據(jù) 可見(jiàn) 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語(yǔ)句執(zhí)行一邊罷了.(注意：select 語(yǔ)句 和delete語(yǔ)句 不可以放在一起重放 因?yàn)槟阕詈筮€是得不到數(shù)據(jù))

恢復(fù)原理便是：執(zhí)行之前的insert語(yǔ)句,或者之前的update語(yǔ)句

如果你的單純的delete物理刪除,別想規(guī)復(fù)了,因?yàn)樵俅螆?zhí)行的還是delete語(yǔ)句

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ù)庫(kù)

給用戶kang受權(quán)所有的庫(kù)的權(quán)限

5、主服務(wù)器設(shè)置裝備擺設(shè)

主服務(wù)器ip：192.168.206.128

• 設(shè)置裝備擺設(shè)主服務(wù)器my.cnf 文件

vim /etc/my.cnf

• 設(shè)置裝備擺設(shè)之后刷新binlog文件

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

• 又或使用mysqldump備份sql 文件

將主服務(wù)器一致都是sql文件備份,通報(bào)到從服務(wù)器

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

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

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

(假如你數(shù)據(jù)庫(kù)中有merge表 容易會(huì)提示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ù)器設(shè)置裝備擺設(shè)

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

新建smudge庫(kù)

mysql導(dǎo)入sql文件

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

• 設(shè)置裝備擺設(shè)從服務(wù)器my.cnf文件

vim /etc/my.cnf

其中用戶名和暗碼就是上述我們?cè)谥鞣?wù)器添加的信息

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

保留并重啟mysql

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

• 啟動(dòng)slave過(guò)程,開啟主從同步

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

• show slave status 查看從服務(wù)器狀況

注解同步功能已經(jīng)開啟

7、從服務(wù)器常用敕令

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

stop slave 結(jié)束復(fù)制線程

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

change master to master_host ,master_user 靜態(tài)切換主數(shù)據(jù)庫(kù)

show processlist 查看運(yùn)行過(guò)程 (主動(dòng)服務(wù)器都適用)

8、常見(jiàn)差錯(cuò)排錯(cuò)

• show slave status 反省主動(dòng)狀態(tài)

20 數(shù)值為NO

21 數(shù)值為

注解同步出現(xiàn)了故障,可能是slave服務(wù)器執(zhí)行了寫操作或者從服務(wù)器重啟有事務(wù)回滾操作.

• 辦理

從服務(wù)器: stop slave 封閉復(fù)制線程

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

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

master_log_file='mysql-bin.000005',master_log_pos=759 敕令

二、查詢機(jī)能優(yōu)化

1、查詢執(zhí)行根基知識(shí)

• mysql執(zhí)行查詢進(jìn)程

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

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

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

④ 執(zhí)行引擎挪用存儲(chǔ)引擎api執(zhí)行查詢

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

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

該協(xié)議是半雙工通信,可以發(fā)送或接收數(shù)據(jù),但是不克不及同時(shí)發(fā)送和接收決定了mysql的溝通簡(jiǎn)單又快捷；

缺點(diǎn)：無(wú)法進(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ù) "推"到客戶端, 客戶端不能說(shuō)不要 是必需全部裝著!

常用的Mysql類庫(kù) 其實(shí)是從客戶端提取數(shù)據(jù) 緩存到array(內(nèi)存)中,然后進(jìn)行 foreach 處置.

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

• 查詢狀況

每個(gè)mysql連接都是mysql服務(wù)器的一個(gè)線程 任意一個(gè)給定的時(shí)間都有一個(gè)狀態(tài)來(lái)標(biāo)識(shí)正在產(chǎn)生的事情.

使用 show full processlist 敕令查看

mysql中一共有12個(gè)狀態(tài)：休眠、查詢、鎖定、闡發(fā)和統(tǒng)計(jì)、拷貝到磁盤上的臨時(shí)表、排序結(jié)果、發(fā)送數(shù)據(jù),通過(guò)這些狀態(tài) 知道 "球在誰(shuí)手上".

• 查詢緩存

解析一個(gè)查詢,如果開啟了緩存,mysql會(huì)反省查詢緩存,發(fā)現(xiàn)緩存匹配,返回緩存之前,反省查詢的權(quán)限.

2、優(yōu)化數(shù)據(jù)拜訪

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

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

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

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

一般哀求不需要的數(shù)據(jù),再丟掉他們,造成服務(wù)器額外的負(fù)擔(dān),增加網(wǎng)絡(luò)開銷,消耗了內(nèi)存和cpu.

典型的差錯(cuò)：

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

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

.....

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

• mysql檢查了太多半據(jù)

簡(jiǎn)單的開銷指標(biāo)：執(zhí)行時(shí)間、反省的行數(shù)、返回的行數(shù).

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

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

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

③ 檢查的行和拜訪類型： 使用explain sq語(yǔ)句,觀察typ列

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

① 全表掃描(full table scan)

② 索引掃描(index scan)

③ 規(guī)模掃描(range scan)

④ 獨(dú)一索引查找(unique index lookup)

⑤ 常量(constant)

可見(jiàn)type列為index即sql語(yǔ)句,基于索引掃描：

rows列為12731,即掃描了12731行 extra列為using index,即使用索引過(guò)濾不必要的行

mysql會(huì)在3種環(huán)境下使用where子句,從最好到最壞依次是：

① 對(duì)索引查找應(yīng)用where子句來(lái)消除不匹配的行 這產(chǎn)生在存儲(chǔ)層

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

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

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

① 使用籠罩索引 ,存儲(chǔ)了數(shù)據(jù) 存儲(chǔ)引擎不會(huì)讀取完整的行

② 變動(dòng)架構(gòu)使用匯總表

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

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

優(yōu)化有問(wèn)題的查詢,其實(shí)也可以找到替代計(jì)劃,提供更高的效率.

• 繁雜查詢和多個(gè)查詢

mysql一般服務(wù)器可以每秒50000個(gè)查詢,慣例情況下,使用盡可能少的查詢 有時(shí)候分解查詢得到更高的效率.

• 縮短查詢

分治法,查詢本色上不變,每次執(zhí)行一小部分,以減少受影響的行數(shù).比如清理陳舊的數(shù)據(jù),每次清理1000條：

delete from message where create < date_sub(now,inteval 3 month)

limit 1000

防止長(zhǎng)光陰鎖住很多行的數(shù)據(jù).

• 分化聯(lián)接

把一個(gè)多表聯(lián)接分解成多個(gè)單個(gè)查詢 然后在應(yīng)用法式實(shí)現(xiàn)聯(lián)接操作

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

① 緩存效率高,應(yīng)用法式直接緩存了表 類似第一個(gè)查詢直接跳過(guò)

② 對(duì)付myisam表來(lái)說(shuō) 每個(gè)表一個(gè)查詢有效利用表鎖 查詢鎖住表的時(shí)間縮短

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

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

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

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

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

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

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

④ 對(duì)于年夜表使用in 替換聯(lián)接

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

• 提取隨機(jī)行

• 分組查詢

• 外鍵

只有Innodb引擎支持外鍵,myisam可以添加外鍵然則沒(méi)有效果.

主表添加主鍵id,從表添加外鍵id援用主表的id.

表student

表student_extend

為student_extend添加外鍵,外鍵指向student表中的id列,在delete時(shí)觸發(fā)外鍵.

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

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

刪除了表student一條數(shù)據(jù),則外鍵表就會(huì)觸發(fā)外鍵,刪除了對(duì)應(yīng)數(shù)據(jù)：

delete from student where id = 2;

• 優(yōu)化結(jié)合查詢

• 優(yōu)化max min

此中 name 沒(méi)有索引

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

維易PHP培訓(xùn)學(xué)院每天發(fā)布《MySQL架構(gòu)優(yōu)化實(shí)戰(zhàn)系列2：主從復(fù)制同步與查詢性能調(diào)優(yōu)》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/7830.html