一、主從複制同步部署
1、概念
主從複制：2台以上mysql伺服器， 做負載均衡， 主伺服器負責增删改 ， 從伺服器負責查詢
同步原理：mysql開啟bin-log日志，主伺服器所有的增删改操作會記錄到bin-log日志；然後主伺服器把bin-log日志發送 給 從伺服器 ， 從伺服器重放bin-log日志 確定資料同步
2、開啟bin-log日志
配置 my.cnf 檔案 并重新開機 mysql
[root@localhost etc]# vim /etc/my.cnf
[root@localhost etc]# service mysql restart
開啟之後 mysql-bin對應的檔案 已經出現
[root@localhost var]# cd /usr/local/mysql/var && ll
通過 show master status 指令檢視 最新一個binlog日志 及開始行數
mysql> show master status;
檢視binlog日志内容 可見 最新一行日志在位置107
$ /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000001
測試删除資料 可見 binlog檔案新增日志内容
3、bin-log日志相關指令
flush logs
建立一個binlog日志，增删改日志在新檔案中插入，新的日志end-log-positon 是107行，107行記錄了mysql内部日志。
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日志、恢複資料：其實就是再把日志中的sql語句執行一邊而已。(注意：select 語句 和delete語句 不可以放在一起重放 因為你最後還是得不到資料)
恢複原理就是：執行之前的insert語句，或者之前的update語句
如果你的單純的delete實體删除，别想恢複了，因為再次執行的還是delete語句
4、create 建立使用者 + grant使用者授權
主伺服器檢視使用者密碼
mysql> select host，user，password from mysql.user;
添加主伺服器使用者密碼
create user 'kang'@'192.168.206.132' identified by 'smudge';
建立使用者kang 可以在ip為192.168.206.132主機上通路資料庫
給使用者kang授權所有的庫的權限
5、主伺服器配置
主伺服器ip：192.168.206.128
配置主伺服器my.cnf 檔案
vim /etc/my.cnf
配置之後重新整理binlog檔案
flush logs with read lock 確定獲得一緻性快照，等待主從binlog日志同步完畢達到資料一緻
或者使用mysqldump備份sql 檔案
将主伺服器一緻都是sql檔案備份，傳遞到從伺服器
mysqldump -uroot -psmudge smudge -l -f > '/home/smudge.sql'
-l 是指鎖表 防止新資料插入
-f 是重新整理 生成一個新的binlog日志
(如果你資料庫中有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、從伺服器配置
恢複一部分主伺服器備份的資料
建立smudge庫
mysql導入sql檔案
配置從伺服器my.cnf檔案
其中使用者名和密碼就是上述我們在主伺服器添加的資訊
(如果你的mysql版本5.1(mysql>status檢視)之前的，配置這4項，啟動之後就不必使用change master 指令 進行主動同步)
儲存并重新開機mysql
檢視主伺服器master binlog文
啟動slave程序，開啟主從同步
因為我的mysql版本是5.7的， 是以我使用change master指令
show slave status 檢視從伺服器狀态
表明同步功能已經開啟
7、從伺服器常用指令
start slave 啟動複制線程
stop slave 停止複制線程
show master logs 檢視主資料庫日志
change master to master_host ，master_user 動态切換主資料庫
show processlist 檢視運作程序 (主動伺服器都适用)
8、常見錯誤排錯
show slave status 檢查主動狀态
20 數值為no
21 數值為null
表明同步出現了故障，可能是slave伺服器執行了寫操作或者從伺服器重新開機有事務復原操作。
解決
從伺服器: stop slave 關閉複制線程
主伺服器：show master status 檢視最新二進制檔案和位置偏移量
從伺服器執行：change master to master_host ...
master_log_file='mysql-bin.000005'，master_log_pos=759 指令
二、查詢性能優化
1、查詢執行基礎知識
mysql執行查詢過程
① 用戶端将查詢發送到伺服器
② 伺服器檢查查詢緩存 如果找到了就從緩存傳回結果 否則進行下一步
③ 伺服器解析，預處理和優化查詢，生成執行計劃
④ 執行引擎調用存儲引擎api執行查詢
⑤ 伺服器将結果發送回用戶端
mysql用戶端/伺服器協定
該協定是半雙工通信，可以發送或接收資料，但是不能同時發送和接收決定了mysql的溝通簡單又快捷；
缺點：無法進行流程控制，一旦一方發送消息，另一方在發送回複之前必須提取完整的消息，就像抛球遊戲，任意時間，隻有某一方有球，而且有球在手上，否則就不能把球抛出去(發送消息)
mysql用戶端發送/伺服器響應
可以設定max_packet_size這個參數控制用戶端發送的資料包(一旦發送資料包，唯一做的就是等待結果)
伺服器發送的響應由多個資料包組成， 用戶端必須完整接收結果，即使隻需要幾行資料，也得等到全部接收 然後丢掉，或者強制斷開連接配接。(這兩個方法好挫，是以我們使用limit子句呀！！)
也可以了解，用戶端從伺服器 "拉" 資料 ，實際是伺服器産生資料 "推"到用戶端， 用戶端不能說不要 是必須全部裝着！
常用的mysql類庫 其實是從用戶端提取資料 緩存到array(記憶體)中，然後進行 foreach 處理。
但是對于龐大的結果集裝載在記憶體中需要很長時間，如果不緩存，使用較少的記憶體并且可以盡快工作，但是應用程式和類庫互動時候，伺服器端的鎖和資源都是被鎖定的。
查詢狀态
每個mysql連接配接都是mysql伺服器的一個線程 任意一個給定的時間都有一個狀态來辨別正在發生的事情。
使用 show full processlist 指令檢視
mysql中一共有12個狀态：休眠、查詢、鎖定、分析和統計、拷貝到磁盤上的臨時表、排序結果、發送資料，通過這些狀态 知道 "球在誰手上"。
查詢緩存
解析一個查詢，如果開啟了緩存，mysql會檢查查詢緩存，發現緩存比對，傳回緩存之前，檢查查詢的權限。
2、優化資料通路
查詢性能低下最基本的原因是通路了太多的資料，分析兩方面：
① 查明應用程式是否擷取超過需要的資料 通常意味着通路了過多的行或列
② 查明mysql伺服器是否分析了超過需要的行
向伺服器請求了不需要的資料
一般請求不需要的資料，再丢掉他們，造成伺服器額外的負擔，增加網絡開銷，消耗了記憶體和cpu。
典型的錯誤：
① 提取超過需要的行 => 添加 limit 10 控制擷取行數
② 多表聯接提取所有列 => select fruit.* from fruit left join fruit_juice where
.....
③ 提取所有的列 => select id，name... from fruit ... (有時提取超過需要的資料便于複用)
mysql檢查了太多資料
簡單的開銷名額：執行時間、檢查的行數、傳回的行數。
以上三個名額寫入了慢查詢日志 可以使用 mysqlsla工具進行日志分析：
① 執行時間：執行時間隻是參考 不可一概而論 因為執行時間 和伺服器當時負載有關
② 檢查和傳回的行：理想情況下傳回的行和檢查的行一樣，但是顯示基本不可能 比如聯接查詢
③ 檢查的行和通路類型： 使用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子句，從最好到最壞依次是：
① 對索引查找應用where子句來消除不比對的行 這發生在存儲層
② 使用覆寫索引(extra 列 "using index") 避免通路行 從索引取得資料過濾不比對的行 這發生在服務層不需要從表中讀取行
③ 從表中檢索出資料 過濾不比對的行(extra:using where)
如果發現通路資料行數很大，嘗試以下措施：
① 使用覆寫索引 ，存儲了資料 存儲引擎不會讀取完整的行
② 更改架構使用彙總表
③ 重寫複雜的查詢 讓mysql優化器優化執行它
3、重構查詢的方式
優化有問題的查詢，其實也可以找到替代方案，提供更高的效率。
複雜查詢和多個查詢
mysql一般伺服器可以每秒50000個查詢，正常情況下，使用盡可能少的查詢 有時候分解查詢得到更高的效率。
縮短查詢
分治法，查詢本質上不變，每次執行一小部分，以減少受影響的行數。比如清理陳舊的資料，每次清理1000條：
delete from message where create < date_sub(now()，inteval 3 month)
limit 1000
防止長時間鎖住很多行的資料。
分解聯接
把一個多表聯接分解成多個單個查詢 然後在應用程式實作聯接操作
第一眼看上去比較浪費，因為增加了查詢數量，但是有重大的性能優勢：
① 緩存效率高，應用程式直接緩存了表 類似第一個查詢直接跳過
② 對于myisam表來說 每個表一個查詢有效利用表鎖 查詢鎖住表的時間縮短
③ 應用程端進行聯接更友善擴充資料庫
④ 使用in() 避免聯表查詢id排序的耗費
⑤ 減少多餘行的通路 ， 意味着每行資料隻通路一次 避免聯接查詢的非正則化的架構帶來的反複通路同一行的弊端
分解聯接應用場景：
① 可以緩存早期查詢的大量的資料
② 使用了多個myisam表(mysiam表鎖 并發時候 一條sql鎖住多個表 是以要分解)
③ 資料分布在不同的伺服器上
④ 對于大表使用in() 替換聯接
④ 一個聯接引用了同一個表很多次
提取随機行
分組查詢
外鍵
隻有innodb引擎支援外鍵，myisam可以添加外鍵但是沒有效果。
主表添加主鍵id，從表添加外鍵id引用主表的id。
表student
表student_extend
為student_extend添加外鍵，外鍵指向student表中的id列，在delete時觸發外鍵。
表student資料
表student_extend資料
删除表student一條資料，則外鍵表就會觸發外鍵，删除對應資料：
delete from student where id = 2;
優化聯合查詢
優化max() min()
其中 name 沒有索引。
對一個表同時進行select和update。
<b>本文來自雲栖社群合作夥伴"dbaplus"，原文釋出時間：2016-06-24</b>