本文概要概述
為什麼要優化
系統的吞吐量瓶頸往往出現在資料庫的訪問速度上隨著應用程式的執行，資料庫的中的資料會越來越多，處理時間會相應變慢資料是存放在磁碟上的，讀寫速度無法和記憶體相比如何優化
設計資料庫時：資料庫表、欄位的設計，儲存引擎利用好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的文章）
第二正規化：消除對主鍵的部分依賴
即在表中加上一個與業務邏輯無關的欄位作為主鍵
主鍵：可以唯一標識記錄的欄位或者欄位集合。
依賴：A欄位可以確定B欄位，則B欄位依賴A欄位。比如知道了下一節課是數學課，就能確定任課老師是誰。於是周幾和下一節課和就能構成複合主鍵，能夠確定去哪個教室上課，任課老師是誰等。但我們常常增加一個id作為主鍵，而消除對主鍵的部分依賴。
對主鍵的部分依賴：某個欄位依賴複合主鍵中的一部分。
解決方案：新增一個獨立欄位作為主鍵。
第三正規化：消除對主鍵的傳遞依賴
傳遞依賴：B欄位依賴於A，C欄位又依賴於B。比如上例中，任課老師是誰取決於是什麼課，是什麼課又取決於主鍵id。因此需要將此表拆分為兩張表日程表和課程表（獨立資料獨立建表）：
這樣就減少了資料的冗餘（即使週一至週日每天都有Java課，也只是course_id:3546出現了7次）
儲存引擎選擇早期問題：如何選擇MyISAM和Innodb？
現在不存在這個問題了，Innodb不斷完善，從各個方面趕超MyISAM，也是MySQL預設使用的。
儲存引擎Storage engine：MySQL中的資料、索引以及其他物件是如何儲存的，是一套檔案系統的實現。
功能差異
show engines
儲存差異
鎖擴充套件
表級鎖（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：以讀寫插入為主的應用程式，比如部落格系統、新聞入口網站。
索引關鍵字與資料的對映關係稱為索引（包含關鍵字和對應的記錄在磁碟中的地址）。關鍵字是從資料當中提取的用於標識、檢索資料的特定內容。
索引檢索為什麼快？
關鍵字相對於資料本身，資料量小關鍵字是有序的，二分查詢可快速確定位置圖書館為每本書都加了索引號（類別-樓層-書架）、字典為詞語解釋按字母順序編寫目錄等都用到了索引。
MySQL中索引型別
普通索引（key），唯一索引（unique key），主鍵索引（primary key），全文索引（fulltext key）
三種索引的索引方式是一樣的，只不過對索引的關鍵字有不同的限制：
普通索引：對關鍵字沒有限制唯一索引：要求記錄提供的關鍵字不能重複主鍵索引：要求關鍵字唯一且不為null索引管理語法
檢視索引
show create table 表名：
desc 表名
建立索引
建立表之後建立索引
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的複合索引，並命名為nameadd key name (first_name,last_name),-- 建立一個id_card的唯一索引，預設以欄位名作為索引名add UNIQUE KEY (id_card),-- 雞肋，全文索引不支援中文add FULLTEXT KEY (information);
show create table user_index：
建立表時指定索引
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 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（因為主鍵只有一個）。這裡值得注意的是，如果主鍵自增長，那麼不能直接執行此操作（自增長依賴於主鍵索引）：
alter table user_index-- 重新定義欄位MODIFY id int,drop PRIMARY KEY
但通常不會刪除主鍵，因為設計主鍵一定與業務邏輯無關。
執行計劃explain
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執行可選的索引只有主鍵索引，如果有多個，最終會選一個較優的作為檢索的依據。
可以嘗試在一個欄位未建立索引時，根據該欄位查詢的效率，然後對該欄位建立索引（alter table 表名 add index(欄位名)），同樣的SQL執行的效率，你會發現查詢效率會有明顯的提升（資料量越大越明顯）。
order by
當我們使用order by將查詢結果按照某個欄位排序時，如果該欄位沒有建立索引，那麼執行計劃會將查詢出的所有資料使用外部排序（將資料從硬碟分批讀取到記憶體使用內部排序，最後合併排序結果），這個操作是很影響效能的，因為需要將查詢涉及到的所有資料從磁碟中讀到記憶體（如果單條資料過大或者資料量過多都會降低效率），更無論讀到記憶體之後的排序了。
但是如果我們對該欄位建立索引alter table 表名 add index(欄位名)，那麼由於索引本身是有序的，因此直接按照索引的順序和對映關係逐條取出資料即可。而且如果分頁的，那麼只用取出索引表某個範圍內的索引對應的資料，而不用像上述那取出所有資料進行排序再返回某個範圍內的資料。（從磁碟取資料是最影響效能的）
join
對join語句匹配關係（on）涉及的欄位建立索引能夠提高效率
索引覆蓋
如果要查詢的欄位都建立過索引，那麼引擎會直接在索引表中查詢而不會訪問原始資料（否則只要有一個欄位沒有建立索引就會做全表掃描），這叫索引覆蓋。因此我們需要儘可能的在select後只寫必要的查詢欄位，以增加索引覆蓋的機率。
這裡值得注意的是不要想著為每個欄位建立索引，因為優先使用索引的優勢就在於其體積小。
語法細節（要點）
在滿足索引使用的場景下（where/order by/join on或索引覆蓋），索引也不一定被使用
欄位要獨立出現
比如下面兩條SQL語句在語義上相同，但是第一條會使用主鍵索引而第二條不會。
select * from user where id = 20-1;select * from user where id+1 = 20;
`like`查詢，不能以萬用字元開頭
比如搜尋標題包含mysql的文章：
select * from article where title like '%mysql%';
這種SQL的執行計劃用不了索引（like語句匹配表示式以萬用字元開頭），因此只能做全表掃描，效率極低，在實際工程中幾乎不被採用。而一般會使用第三方提供的支援中文的全文索引來做。
但是 關鍵字查詢 熱搜提醒功能還是可以做的，比如鍵入mysql之後提醒mysql 教程、mysql 下載、mysql 安裝步驟等。用到的語句是：
select * from article where title like 'mysql%';
這種like是可以利用索引的（當然前提是title欄位建立過索引）。
複合索引只對第一個欄位有效
建立複合索引：
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_cache_type：
0：不開啟1：開啟，預設快取所有，需要在SQL語句中增加select sql-no-cache提示來放棄快取2：開啟，預設都不快取，需要在SQL語句中增加select sql-cache來主動快取（常用）更改配置後需要重啟以使配置生效，重啟後可通過show variables like ‘query_cache_type’;來檢視：
show variables like 'query_cache_type';query_cache_type DEMAND
在客戶端設定快取大小
通過配置項query_cache_size來設定：
show variables like 'query_cache_size';query_cache_size 0set global query_cache_size=64*1024*1024;show variables like 'query_cache_size';query_cache_size 67108864
將查詢結果快取
select sql_cache * from user;
重置快取
reset query cache;
快取失效問題（大問題）
注意事項
應用程式，不應該關心query cache的使用情況。可以嘗試使用，但不能由query cache決定業務邏輯，因為query cache由DBA來管理。快取是以SQL語句為key儲存的，因此即使SQL語句功能相同，但如果多了一個空格或者大小寫有差異都會導致匹配不到快取。分割槽一般情況下我們建立的表對應一組儲存檔案，使用MyISAM儲存引擎時是一個.MYI和.MYD檔案，使用Innodb儲存引擎時是一個.ibd和.frm（表結構）檔案。
當資料量較大時（一般千萬條記錄級別以上），MySQL的效能就會開始下降，這時我們就需要將資料分散到多組儲存檔案，保證其單個檔案的執行效率。
最常見的分割槽方案是按id分割槽，如下將id的雜湊值對10取模將資料均勻分散到10個.ibd儲存檔案中：
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()多了一步從字串中計算出一個整型在做取模操作。
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月分割槽存放：
注意：條件運算子只能使用less than，這以為著較小的範圍要放在前面，比如上述p201808,p201819,p201810分割槽的定義順序依照created_time數值範圍從小到大，不能顛倒。
由於插入的文章的釋出時間1535731180小於1535731199（2018-8-31 23:59:59），因此被儲存到p201808分割槽中，這種演算法的儲存到哪個分割槽取決於資料狀況。
list演算法
也是一種條件分割槽，按照列表值分割槽（in (值列表)）。
分割槽管理語法
range/list
增加分割槽
前文中我們嘗試使用range對文章按照月份歸檔，隨著時間的增加，我們需要增加一個月份：
alter table article_range drop PARTITION p201808
注意：刪除分割槽後，分割槽中原有的資料也會隨之刪除！
key/hash
新增分割槽
銷燬分割槽
alter table article_key coalesce partition 6
key/hash分割槽的管理不會刪除資料，但是每一次調整（新增或銷燬分割槽）都會將所有的資料重寫分配到新的分割槽上。效率極低，最好在設計階段就考慮好分割槽策略。
分割槽的使用
當資料表中的資料量很大時，分割槽帶來的效率提升才會顯現出來。
只有檢索欄位為分割槽欄位時，分割槽帶來的效率提升才會比較明顯。因此，分割槽欄位的選擇很重要，並且業務邏輯要儘可能地根據分割槽欄位做相應調整（儘量使用分割槽欄位作為查詢條件）。
水平分割和垂直分割水平分割：通過建立結構相同的幾張表分別儲存資料
垂直分割：將經常一起使用的欄位放在一個單獨的表中，分割後的表記錄之間是一一對應關係。
分表原因
為資料庫減壓分割槽演算法侷限資料庫支援不完善（5.1之後mysql才支援分割槽操作）id重複的解決方案
借用第三方應用如memcache、redis的id自增器單獨建一張只包含id一個欄位的表，每次自增該欄位作為資料記錄的id叢集橫向擴充套件：從根本上（單機的硬體處理能力有限）提升資料庫效能 。由此而生的相關技術：讀寫分離、負載均衡
安裝和配置主從複製
環境
Red Hat Enterprise Linux Server release 7.0 (Maipo)（虛擬機器）mysql5.7安裝和配置
解壓到對外提供的服務的目錄（我自己專門建立了一個/export/server來存放）
tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/servercd /export/servermv mysql-5.7.23-linux-glibc2.12-x86_64 mysql
新增mysql目錄的所屬組和所屬者：
groupadd mysqluseradd -r -g mysql mysqlcd /export/serverchown -R mysql:mysql mysql/chmod -R 755 mysql/
建立mysql資料存放目錄（其中/export/data是我建立專門用來為各種服務存放資料的目錄）
mkdir /export/data/mysql
初始化mysql服務
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
vim /etc/my.cnf[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10 # 服務id，在叢集時必須唯一，建議設定為IP的第四段port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d
將服務新增到開機自動啟動
cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld
啟動服務
service mysqld start
配置環境變數，在/etc/profile中新增如下內容
# mysql envMYSQL_HOME=/export/server/mysqlMYSQL_PATH=$MYSQL_HOME/binPATH=$PATH:$MYSQL_PATHexport PATH
使配置即可生效
source /etc/profile
使用root登入
mysql -uroot -p# 這裡填寫之前初始化服務時提供的密碼
登入上去之後，更改root賬戶密碼（我為了方便將密碼改為root），否則操作資料庫會報錯
set password=password('root');flush privileges;
設定服務可被所有遠端客戶端訪問
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如下
[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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/Systemdlog-bin=mysql-bin # 開啟二進位制日誌expire-logs-days=7 # 設定日誌過期時間，避免佔滿磁碟binlog-ignore-db=mysql # 不使用主從複製的資料庫binlog-ignore-db=information_schemabinlog-ignore-db=performation_schemabinlog-ignore-db=sysbinlog-do-db=test #使用主從複製的資料庫[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d
重啟master
service mysqld restart
登入master檢視配置是否生效（ON即為開啟，預設為OFF）：
mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+
在master的資料庫中建立備份賬號：backup為使用者名稱，%表示任何遠端地址，使用者back可以使用密碼1234通過任何遠端客戶端連線master
grant replication slave on *.* to 'backup'@'%' identified by '1234'
檢視user表可以看到我們剛建立的使用者：
mysql> use mysqlmysql> select user,authentication_string,host from user;+---------------+-------------------------------------------+-----------+| user | authentication_string | host |+---------------+-------------------------------------------+-----------+| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % || mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |+---------------+-------------------------------------------+-----------+
新建test資料庫，建立一個article表以備後續測試
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表示在此過程中，客戶端只能讀資料，以便獲得一個一致性的快照）
[root@zhenganwen ~]# service mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@zhenganwen mysql]# mysql -uroot -prootmysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)
檢視master上當前的二進位制日誌和偏移量（記一下其中的File和Position）
File表示實現複製功能的日誌，即上圖中的Binary log；Position則表示Binary log日誌檔案的偏移量之後的都會同步到slave中，那麼在偏移量之前的則需要我們手動匯入。
主伺服器上面的任何修改都會儲存在二進位制日誌Binary log裡面，從伺服器上面啟動一個I/O thread（實際上就是一個主伺服器的客戶端程序），連線到主伺服器上面請求讀取二進位制日誌，然後把讀取到的二進位制日誌寫到本地的一個Realy log裡面。從伺服器上面開啟一個SQL thread定時檢查Realy log，如果發現有更改立即把更改的內容在本機上面執行一遍。
如果一主多從的話，這時主庫既要負責寫又要負責為幾個從庫提供二進位制日誌。此時可以稍做調整，將二進位制日誌只給某一從，這一從再開啟二進位制日誌並將自己的二進位制日誌再發給其它從。或者是乾脆這個從不記錄只負責將二進位制日誌轉發給其它從，這樣架構起來效能可能要好得多，而且資料之間的延時應該也稍微要好一些
手動匯入，從master中匯出資料
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql
將test.sql中的內容在slave上執行一遍。
配置slave
修改slave的my.ini檔案中的[mysqld]部分
log-bin=mysqlserver-id=1 #192.168.10.1
儲存修改後重啟slave，WIN+R->services.msc->MySQL5.7->重新啟動
登入slave檢查log_bin是否以被開啟：
show VARIABLES like 'log_bin';
配置與master的同步複製：
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節點並檢視狀態
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: 0Master_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的讀取鎖定
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
向master中插入一條資料
mysql> use testmysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)
檢視slave是否自動同步了資料
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)
至此，主從複製的配置成功！：)
https://blog.csdn.net/xlgen157387/article/details/52452394
讀寫分離
讀寫分離是依賴於主從複製，而主從複製又是為讀寫分離服務的。因為主從複製要求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），那麼就可以做到讀寫分離了。（可以參考：Spring 動態切換、新增資料來源實現以及原始碼淺析）
負載均衡演算法
輪詢加權輪詢：按照處理能力來加權負載分配：依據當前的空閒狀態（但是測試每個節點的記憶體使用率、CPU利用率等，再做比較選出最閒的那個，效率太低）高可用
在伺服器架構時，為了保證伺服器7x24不宕機線上狀態，需要為每臺單點伺服器（由一臺伺服器提供服務的伺服器，如寫伺服器、資料庫中介軟體）提供冗餘機。（推薦：mysql+mycat搭建穩定高可用叢集）
對於寫伺服器來說，需要提供一臺同樣的寫-冗餘伺服器，當寫伺服器健康時（寫-冗餘通過心跳檢測），寫-冗餘作為一個從機的角色複製寫伺服器的內容與其做一個同步；當寫伺服器宕機時，寫-冗餘伺服器便頂上來作為寫伺服器繼續提供服務。對外界來說這個處理過程是透明的，即外界僅通過一個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
待資料匯入完成之後，再開啟索引和約束，一次性建立索引
alter table table-name enable keys
2.資料庫如果使用的引擎是Innodb，那麼它預設會給每條寫指令加上事務（這也會消耗一定的時間），因此建議先手動開啟事務，再執行一定量的批量匯入，最後手動提交事務。
3.如果批量匯入的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內部沒有這樣一個計數器，需要我們手動統計記錄數量，解決思路就是單獨使用一張表：
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執行的詳細資訊都會被自動記錄下來
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
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，最大客戶端連線數
mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
table_open_cache，表文件控制代碼快取（表資料是儲存在磁碟上的，快取磁碟檔案的控制代碼方便開啟檔案讀取資料）
mysql> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+
key_buffer_size，索引快取大小（將從磁碟上讀取的索引快取到記憶體，可以設定大一些，有利於快速檢索）
mysql> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------
innodb_buffer_pool_size，Innodb儲存引擎快取池大小（對於Innodb來說最重要的一個配置，如果所有的表用的都是Innodb，那麼甚至建議將該值設定到實體記憶體的80%，Innodb的很多效能提升如索引都是依靠這個）
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共享表空間）