閱讀筆記

<高效能MySQL>
===MySQL架構===
mysql的架構圖：
客戶端

連線/執行緒處理

查詢快取 解析器

最佳化器

儲存引擎

mysql事務ACID：
1、原子性：automicity 要麼全部執行成功，要麼全部執行失敗，這就是事務的原子性
2、一致性：consistency 從一個一致性的狀態轉換到另外一個一致性的狀態
3、隔離性：isolation 事務在提交之前，對其他事務是不可見的
4、永續性：durability 一旦提交，所做的資料修改就會永遠儲存在資料庫中

隔離級別： set session transaction isolation level read COMMITTED;
1、READ UNCOMMITTED 未提交讀
在READ UNCOMMITTED級別，事務中的修改，即使沒有提交，對其他事務也都是可見的，事務可以讀取未提交的資料，這也被稱為"髒讀"(dirty read)
2、READ COMMITTED 提交讀
大多數的資料庫系統的預設隔離級別都是READ COMMITTED。一個事務從開始直到提交之前，所做的任何操作修改對其他事務是不可見的。也叫做不可重複讀
3、REPEATABLE READ 可重複讀
可重複讀解決了髒讀，但是會存在幻讀的現象。當某個事務在讀取範圍內的記錄時，另外一個事務在該範圍內插入新的資料。
4、SERIALIZABLE 可序列化
透過強制事務序列執行，是最高的隔離級別

死鎖：
死鎖是指兩個或兩個以上的事務在同一個資源上相互佔用，並請求鎖定對方佔用的資源，從而產生惡性迴圈的現象。
INNODB目前將持有最少行級排它鎖的事務進行回滾

事務日誌：
事務日誌可以提高事務的效率。使用事務日誌，儲存引擎在修改表資料時，只需要修改其記憶體複製，再把該修改行為記錄到持久在硬碟上的事務日誌中。而不是每次修改資料本身持久到硬碟上。
事務日誌採用的是追加的方式，因此寫日誌的操作是硬碟上一小塊區域內的順序I/O，而不是隨機I/O。事務日誌持久以後，記憶體被修改的資料在後臺慢慢刷回到磁碟。修改資料需要寫兩次磁碟。
如果在事務日誌中持久化，沒有落盤，系統崩潰，資料庫會自動恢復。

INNODB儲存引擎：
innodb表是基於聚簇索引建立的，聚簇索引對主鍵查詢有很高的效能，不過它的二級索引裡必須包含主鍵列，所以主鍵列很大的話，其他索引都會很大。

--閱讀《innodb事務模型和鎖》--

表修改儲存引擎：
alter table table_name engine=innodb; 按行將資料從原表複製到新表，因此執行時間會很長

效能測試前900s預熱，避免預熱時的IO影響測試結果 

===MySQL基本測試===
sysbench：
1、CPU
2、IO
3、記憶體
4、執行緒
5、OLTP

繪圖工具：gnuplot 或者 R

===伺服器效能剖析===
日誌輪轉工具：log rotation

mysqlslowlog tmpdump pt-query-digest mysql-proxy
pt-query-digest --explian 和 V/M 值 更容易識別出效能低下的查詢

官方mysql和percona server對比慢查詢日誌缺少了很多附加資訊

show profile

使用 SHOW GLOBAL STATUS 捕獲資料
mysqladmin ext -i1 | awk '
/Queries/{q=$4-qp;qp=$4/}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

使用 SHOW PROCESSLIST

innotop工具

每個時間段吞吐量
awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' mysql-slowlog.log

pt-stalt pt-pmp pt-collect工具

gdb 工具對mysql的分析

iostat vmstat new relic工具

===Schema和資料型別最佳化===
1、選擇最佳化的資料型別
更小的資料型別通常更快，因為它們佔用更少的磁碟，記憶體，CPU快取，並且處理時需要的CPU週期也更少
2、簡單就好
3、儘量避免NULL
因為可為NULL的列使得索引，索引統計和值比較更復雜化，可為NULL的列佔用更多的儲存空間。可為NULL的列被索引時，每個索引記錄需要一個額外的位元組，在MyISAM裡甚至可能導致固定大小的索引變為可變大小的索引

1、時間型別：
DATETIME 和 TIMESAMP 列都可以儲存相同型別的資料，時間和日期，精確到表。但是 TIMESAMP只使用 DATEIME 一半的儲存空間，並且會根據時區變化，具有特殊的自動更新能力。但是，TIMESAMP允許的時間範圍要小的很多

2、整數型別： 整數 和 實數
整數：tinyint 8
samllint 16
mediumint 24
int 32
bigint 64
整數型別有可選的 unsigned ,表示不允許為負值
例如：tinyint unsigned 0 ~ 255
tinyint 128 ~ 127

MySQL可以為整數型別指定寬度，例如int(11)，對大多數應用是沒有意義的。它不會限制值的合法範圍，只是規定了mysql互動工具用來顯示的字元的個數。對於儲存和計算來說，int(1)和int(20)是相同的。

3、實數型別： 實數是帶有小數部分的數字。
儘量在對小數進行精確計算的時候使用DECIMAL-例如儲存財務資料。 或者可以使用BIGINT,根據小數的位數乘以相應的倍數後儲存在BIGINT裡，避免DECIMAL精確計算代價高的問題。

4、字串型別：
VARCHAR:
a.VARCHAR型別用於儲存可變長字串，它比定長型別更節省空間，因為它僅使用必要的空間。 如果ROW_FORMAT=FIXED建立的話，每一行都是定長儲存，很浪費空間。
b.VARCHAR需要使用1或者2個位元組儲存字串的長度，如果列的最大長度<=255，則使用1個位元組，否則使用2位元組。 VARCHAR(10) 需要11個位元組，VARCHAR(1000) 需要1002個位元組。
VARCHAR節省了儲存空間。但是由於行是變長的，在UPDATE時可能使行變得更長，導致額外的工作。MyISAM 會將行拆分成不同的片段儲存。 INNODB則需要分裂頁來使行放進頁內。
c.慷慨不是明智的：
VARCHAR(5) 和 VARCAHR(200) 儲存'yoon'的空間開銷是一樣的。因此短的有什麼優勢？ 更長的列會消耗更多的記憶體，因為mysql通常會分配固定大小的記憶體塊來儲存內部值。尤其是使用記憶體臨時表排序或者操作時會特別
糟糕。在利用磁碟臨時表排序時也同樣糟糕。 因此分配真正需要的空間。

CHAR:
CHAR的型別是定長的。

BLOB 和 TEXT：
a.BLOB和TEXT都是為儲存很大的資料而設計的字串資料型別，分別採用二進位制和字元方式儲存。 BLOB型別儲存的是二進位制，沒有排序規則或字串， 而TEXT型別有字串和排序規則。
b.如果非要使用BLOB和TEXT，可以將BLOB欄位的地方使用SUBSTRING(column,length)將列值轉換為字串，但是要確保擷取的夠短，否則臨時表的大小會超過max_heap_table_size tmp_table_size
，超過以後mysql會將記憶體臨時錶轉換為磁碟臨時表。

日期和時間型別：
DATETIME: 從1001 ~ 9999，精度為秒。它把日期和時間封裝為 YYYYMMDDHHMMSS 整數中，與時區無關。使用8個位元組的儲存空間。 預設情況下，mysql以一種可排序的，無歧視的格式顯示DATETIME值。

TIMESAMP：從1970 ~ 2038，佔用4個位元組的儲存空間。 
a.TIMESAMP 顯示的值也依賴時區。 mysql伺服器，作業系統，以及客戶端都有時區設定。
b.如果在多個時區儲存或者訪問資料，TIMESAMP和DATETIME的行為很不一樣，TIMESAMP和時區有關，DATETIME則保留文字表示的日期和時間。
c.儲存比秒粒度更小的日期和時間，用mariadb替換mysql

正規化的有點和缺點：
1、正規化化的更新操作要比反正規化化快
2、當資料較好的正規化化時，就有較少的或者沒有重複的資料，所以只需要修改更少的行
3、正規化化的表通常都很小，可以更好的在記憶體裡執行
4、很少有多餘的資料意味著要檢索列表資料時更少需要DISTINCT 或者 GOURP BY語句。 在非正規化化的結構中要 DISTINCT 和 GROUP BY 才能獲得唯一部門的資料
5、正規化化設計的缺點通常需要關聯。

反正規化的優點和缺點：
1、資料都在一個表中，因此可以避免關聯
2、如果不需要關聯表，對大部分查詢最差的情況---即使沒有使用索引--是全表掃描。當資料比記憶體大時，這可能比關聯要快的多，避免了隨機IO 。(全表掃描基本上是順序IO)

正規化：俗稱就是將資料拆分細化，查詢時需要關聯多張表進行查詢想要的資料
反正規化：俗稱就是將資料混合存放在一起，查詢時只需要查詢一張表即可，不需要關聯

3、混用正規化和反正規化化

總結：
1、儘量避免過度設計表
2、使用小而簡單的資料型別，避免使用NULL值
3、儘量使用相同的或相似的資料型別儲存相關的值，尤其要在關聯的表中使用的列
4、儘量使用整型定義標識列

===建立高效能的索引===
在mysql中，索引是在儲存引擎層而不是伺服器層實現，索引沒有統一的索引標準。不同儲存引擎的索引的工作方式不同，也不是所有的儲存引擎都支援相同型別的索引。即使儲存引擎支援相同型別的索引，底層實現的也可能不同。

B+Tree索引：
沒有特別指明，多半說的都是B-Tree索引，使用B-Tree資料結構來儲存資料，即每一個葉子節點都包含指向下一個葉子節點的指標，從而方便葉子節點的遍歷搜尋。

儲存引擎以不同的方式使用B-Tree索引，效能也不相同，各有優劣。 myisam使用字首壓縮技術使得索引更小，innodb則按照原資料格式進行儲存。 myisam索引透過資料的物理位置引用被索引的行，innodb則透過主鍵引用被索引的行。

B-Tree通常意味著值都是按順序儲存的，每一個葉子頁到根的距離相同。 根節點存放了指向"葉子節點的指標"，葉子節點"指標指向的是被索引的資料"。

索引對多個值進行排序，是根據 CREATE TABLE 語句定義的索引列的順序，例如： IDX_INDEX(A,B,C) 如果A,B值都一樣，則根據C排序。

如果查詢中有某個列的範圍查詢，則其右邊的列無法使用索引最佳化查詢，例如：IDX_INDEX(A,B,C)
WHERE A = 'YOON' AND B LIKE 'K%' AND C = '1987-7-7'; 這個查詢只能使用所用的前兩個列，因為這裡的 LIKE 是一個範圍條件。

雜湊索引：
雜湊索引基於雜湊表實現，只有精確匹配索引所有列的查詢才有效。對於每一行資料，儲存引擎會對所有的索引列計算一個雜湊碼，雜湊碼是一個較小的值，並且不同的鍵值計算出來的雜湊碼也不相同，雜湊索引將所有雜湊碼儲存在索引中，同事雜湊表中儲存指向每個資料的指標。

雜湊索引限制：
1、雜湊索引只包含雜湊值和指標，而不儲存欄位值。
2、雜湊索引資料不是按照順序儲存的，因此無法排序
3、雜湊索引也不支援部分列匹配查詢
4、雜湊索引只支援等值查詢，不支援範圍查詢
5、當雜湊衝突的時候，必須遍歷連結串列中所有的行，直到找到符合的行。
6、雜湊衝突多的話，索引維護代價也很高。

儲存大量的URL，直接用B-Tree儲存會很大，例如：select id from url where url=' 可以刪除url列上的索引，新增一個rul_crc列，使用CRC32雜湊，就可以如下：
select id from url where url=' and url_crc=CRC32("); 這樣效能就會很高，mysql最佳化器會選擇效能很高而且體積很小的基於rul_crc列的索引來完成。這樣缺陷需要維護雜湊值。

索引的優點：
1、索引大大減少了伺服器需要掃描的資料量
2、索引可以幫助伺服器避免排序和臨時表
3、索引可以將隨機IO變成順序IO
關於索引的書： "Relational Database Index Design and the Optimizers"

字首部分索引，alter table sakila.city add key (key(7)); 這種無法透過字首索引進行 ORDER BY , GROUP BY， 覆蓋掃描。

index merge 有時候是一種合併策略的最佳化結果，但實際上糟糕的索引設計。 

optimizer_switch 索引合併功能

聚簇索引：
1、聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式。 innodb的聚簇索引實際上在同一個結構中儲存了B-Tree索引和資料行。
2、聚簇：表示資料行和相鄰的鍵值緊湊的儲存在一起。 因為無法同時把資料行存放在不同的地方，所以一個表只有一個"聚簇索引"。
3、innodb 透過主鍵聚集資料，"被索引的列就是主鍵列"
4、如果沒有主鍵，innodb會選擇一個非空的唯一索引，如果沒有這樣的索引，innodb會隱式定一個主鍵來作為聚簇索引。innodb只聚集在同一個頁面中的記錄，相鄰的可能會相聚很遠。

聚簇的資料一些重要優點：
1、可以把相關資料儲存在一起。 
2、資料訪問更快。 聚簇索引將"索引"和"資料"儲存在同一個B-Tree中，因此從聚簇索引中查詢資料比非聚簇索引中查詢資料要快。
3、使用覆蓋索引掃描的查詢可以直接使用頁節點的主鍵值。

"二級索引需要訪問兩次，而不是一次"：
因為二級索引中儲存的 "行指標" 的實質。 二級索引葉子節點儲存的不是指向物理位置的指標，而是 "行的主鍵值"。。。。這意味著，透過二級索引查詢資料，儲存引擎需要找到二級索引葉子節點對應的主鍵值，再透過主鍵
值去"聚簇索引"中找到對應的行資料。 這樣就減少了出現行移動或者資料頁分裂時，二級索引的維護工作。 但會讓二級索引佔用更多的空間。

當使用非自增作為主鍵，例如用UUID作為主鍵，插入的值不是順序的，而是隨機的。 會導致索引佔用的空間更大，頁分裂和碎片更多。缺點如下：
1、寫入的目標頁可能已經從快取中剔除，或者還沒載入到快取中，innodb在插入之前不得不從磁碟上讀取目標頁到記憶體中，這樣將導致大量的隨機IO
2、因為寫入是亂序的，innodb不得不頻繁的做頁分裂操作，以便為新的行分配空間。頁分裂會導致移動大量資料，一次插入最少需要修改三個頁而不是一個頁。
3、由於頻繁的頁分裂，頁會變得稀疏而不規則，最終資料會有碎片。

innodb_autoinc_lock_mode

覆蓋索引：
1、如果一個索引包含（或者說覆蓋）所要查詢的欄位的值，稱之為"覆蓋索引"
2、如果索引覆蓋了where條件中的欄位，但是不是整個查詢涉及的欄位， mysql5.5之前會回表

使用索引來做排序：
1、如果查詢需要關聯多張表，則只有當 ORDER BY 子句引用的欄位全部為第一個表時，才能使用索引排序，和索引的最左字首列相同。
2、特殊情況下，就是where子句或者join子句中的列指定了常量，如下： idx_index(rental_date,inventory_id,customter_id)
select rental_id,staff_id from rental where rental_date='2005-05-25' order by inventory_id,customter_id;

1、ID為主鍵，索引(A)，擴充套件為索引(A,ID)，這也是冗餘索引，因為主鍵包含在了二級索引中。
2、如果在整數列上有索引，現在需要額外的擴充套件索引增加VARCHAR欄位，有可能會導致效能急劇下降。
例如： Q1:select id,state_id from userinfo where state_id=5; 會很快。
Q2:select state_id,city,address from userinfo where state_id=5; 提升該查詢需要擴充套件索引，讓索引能覆蓋查詢(state_id,city,address), 但是這樣Q1變慢了，所以我們需要兩個索引，即便冗餘。

3、表中的索引越多插入速度就越慢。 增加索引會導致 insert update delete 等操作速度變慢。

4、透過 pt-duplicate-key-checker檢查冗餘和重複索引。 用 pt-upgrade 工具檢查計劃中的索引變更。 pt-index-usage 排查哪些索引沒有使用。

5、Q1: select actor_id from actor where actor_id > 50;
Q2: select actor_id from actor where actor_id in (1,3,5,7);
Q1是屬於範圍查詢，Q2屬於多個等值條件的查詢。 這兩種訪問效率是不同的。對於範圍查詢，mysql無法再使用範圍列後面的其他索引列，多個等值條件查詢沒有這個限制。

===查詢效能最佳化===
Using Where 從好到壞：
1、在索引中使用where條件來過濾不匹配的記錄，在儲存引擎完成
2、使用索引覆蓋掃描，在Etra中顯示Using Index ，直接從索引中過濾不需要的記錄，這是在mysql伺服器層完成。
3、從資料表中返回資料，然後過濾不滿足的條件，在Etra中顯示 Using Where ，在 mysql 伺服器層完成，需要從資料表中讀取記錄然後過濾。

"分解sql語句，拆分成幾個簡單的sql，讓應用層在去做關聯，減少鎖的爭用。"

查詢執行的基礎：
1、客戶端傳送一條sql語句給伺服器
2、伺服器先查詢快取，如果命中快取，則立刻返回儲存在快取中的資料。
3、伺服器端進行解析sql，預處理，再由最佳化器生成執行計劃
4、mysql根據執行計劃，呼叫儲存引擎的API來執行查詢
5、將結果返回給客戶端

MySQL客戶端：
當客戶端從伺服器獲取資料時，看起來是一個拉資料的過程，實際上是 mysql 在向客戶端推送資料的過程。

查詢狀態：
Sleep： 執行緒正在等待客戶端傳送新的請求

Query： 執行緒正在執行查詢或者正在將結果傳送給客戶端

Locked： 在mysql伺服器層，該執行緒正在等待表鎖。在儲存引擎層級別實現的鎖，例如innodb的行鎖，並不會體現線上程狀態中。

Analyze and statistics： 執行緒正在收集儲存引擎的統計資訊，並生成執行計劃

Copying to tmp table【on disk】：執行緒正在執行查詢，並且將結果都複製到一個臨時表中，這種狀態一般要麼是GROUP BY操作，要麼是檔案排序 FILESORT，或者是UNION操作。如果還有ON DISK，表示正在將記憶體臨時表放到磁碟上。

Sorting result： 執行緒正在對結果集進行排序

Sending data：表示多種情況：執行緒可能在多個狀態之間傳送資料，或者在生成結果集，或者在向客戶端返回資料

"要以為你比mysql的最佳化器更聰明"

"查詢最佳化器在伺服器層，統計資訊在儲存引擎層"

排序最佳化：
如果需要的排序資料小於"排序緩衝區"，mysql則在記憶體進行快速排序。 如果記憶體不夠排序，mysql則將資料分塊，對每個獨立的塊使用快速排序進行排序，並將各個塊的排序結果放在磁碟上，然後進行合併merge，返回結果

關聯子查詢：
select * from sakila.film where film_id in (select film_id from sakila.film_actor where actor_id=1);
select * from sakila.film where exists (select * from sakila.film_actor where film.film_id=film_acotr.film_id);

select film_id,language_id from sakila.film where not exists (select * from sakila.film_actor where film_actor.film_id=film.film_id);
一般建議透過左外連線重寫
select film_id,language_id from sakila.film left outer join sakila.film_actor using(film_id) where film_actor.film_id is null;

pt-upgrade 工具可以檢查在升級版本中的sql是否和老版本執行的一樣。

使用者自定義變數：
1、使用自定義變數的查詢，無法使用查詢快取
2、不能在使用常量或者識別符號的地方使用自定義變數，例如表名，列名和LIMIT子句中。
3、使用者自定義變數的生命週期是在一個連結中有效，不能用他們來做連結間的通訊
4、如果使用連線池或者持久化連結，自定義變數可能會讓看起來毫無關係的程式碼發生互動
5、不能顯示的宣告自定義變數。

===MySQL高階特性===
分割槽表

在下面的場景中，分割槽表可以起到非常大作用，如下：
1、表非常大至於無法全部都放在記憶體中，或者只在表的最後部分有熱點資料，其他均是歷史資料
2、分割槽表的資料更容易維護。
3、分割槽表的資料可以分佈在不同的物理裝置上，從而高效的利用裝置
4、可以使用分割槽表避免某些特殊的瓶頸，例如innodb的單個索引的互斥訪問，ext3檔案系統的inode鎖競爭等待
5、還可以備份和恢復獨立的分割槽。

分割槽表的一些限制，如下：
1、一個表最多有1024個分割槽
2、在 5.5 中，某些場景中可以直接使用列來就行分割槽
3、如果分割槽欄位中有主鍵或者唯一索引，那麼所有主鍵和唯一索引必須包含進來。
4、分割槽表中無法使用外來鍵。

分割槽表的原理
分割槽表由多個相關的底層表實現，這些底層表也是控制程式碼物件（Handler object）表示。儲存引擎層管理分割槽的各個底層表和管理普通表一樣。從儲存引擎角度來看，底層表和普通表沒什麼區別。

分割槽select語句：
當查詢一個表的時候，分割槽層先開啟並鎖住所有底層表，最佳化器判斷是否可以過濾部分分割槽，然後再呼叫儲存引擎介面各個分割槽的資料。

分割槽insert操作：
當寫入一條資料時，分割槽層開啟並鎖住所有底層表，然後確定哪個分割槽接收這條資料，再將記錄寫入對應底層表

分割槽delete操作：
當刪除一條資料時，分割槽層開啟並鎖住所有底層表，然後確定資料分割槽，最後刪除

分割槽update操作：
當更新一條資料時，分割槽層開啟並鎖住所有底層表，確定資料在哪個分割槽，取出來然後更新，再判斷更新後的資料放入哪個分割槽，最後對底層表進行寫入操作，並對原資料所在的底層表進行刪除操作。

分割槽表的型別
1、根據鍵值進行分割槽，來減少innodb的互斥量競爭
2、使用數學模函式來進行分割槽

使用分割槽表從5.5版本開始，5.6對於分割槽表做了更多的最佳化

目前分割槽中的一些其他限制：
1、所有分割槽都必須使用相同的儲存引擎
2、分割槽函式中使用的函式和表示式也有一些限制
3、某些儲存引擎不支援分割槽
4、對於MYisAM的分割槽表，不能再使用 LOAD INDEX INTO CACHE操作
5、對於MyISAM表，使用分割槽表時需要開啟更多的檔案描述符。

觸發器：
1、對每一個表的每一個時間，最多隻能定義一個觸發器，換句話說，不能在AFTER INSERT上定義兩個觸發器
2、mysql只支援基於行的觸發---也就是說，觸發器始終是針對一條記錄的。而不是針對整個sql語句的。變成的資料集非常大的話，效率會很低。

===最佳化伺服器設定===
key_buffer_size：設定這個變數可以一次性為鍵緩衝區(也叫鍵快取)分配所有指定的空間。

table_cache_size：設定變數不會立即生效 --- 會延遲到下次有執行緒開啟表才有效果。當有執行緒開啟表時，mysql會檢查變數的值，如果值大於快取中的表的數量，執行緒可以直接把最新開啟的表放入快取中，
如果值小於開啟的表的數量，mysql將從快取中刪除不經常開啟的表。

thread_cache_size：設定這個變數不會立即生效 --- 將在下次有連線被關閉的時才生效。當有連線關閉時，mysql會檢查執行緒快取中是否有空間來快取執行緒，如果有空間，則快取該執行緒，以備下次重用，如果沒有
空間，它將銷燬該執行緒不再快取。快取中的執行緒數以及執行緒快取使用的記憶體都不會立刻減少，只有在新的連線刪除快取中的一個執行緒並使用後才減少。（mysql只有在關閉連線時才會在快取中增加執行緒，在建立新連線時
才從快取中刪除執行緒）

query_cache_size：mysql在啟動的時候，一次性的分配並初始化這塊記憶體。 如果修改這個值，mysql會立刻刪除所有快取的查詢，重新分配指定大小的記憶體。mysql是逐個清理查詢的，不是一次性全部刪除。

read_buffer_size：mysql只有在查詢時才為該快取分配記憶體，並且會一次性分配引數指定大小的記憶體

read_rnd_buffer_size：mysql只有在查詢才會為該快取分配記憶體，而且只會分配需要的記憶體大小，而不是引數指定的大小。

sort_buffer_size：查詢需要排序操作時才會為該快取分配記憶體，一旦排序，mysql會立刻分配引數指定大小的記憶體。

innodb事務日誌：ib_logfile0 ib_logfile1
innodb用日誌把隨機IO變成順序IO。 一旦日誌完全寫入磁碟，事務就持久化了。 即使變更還沒有寫到資料檔案，innodb也可以衝放日誌並且恢復已經提交的事務。
innodb最後還是把變更寫入到資料檔案，因為日誌有固定的大小。 innodb的環形的，當寫到尾部，會重新跳到開頭繼續寫。 但是不會覆蓋還沒有寫到資料檔案的日誌記錄。

innodb_log_buffer_size 控制日誌緩衝區大小

innodb_flush_log_at_trx_commit：
1、為 1 時，每秒鐘重新整理一次，但是事務提交時不做什麼操作
2、為 0 時，把日誌快取寫到日誌檔案並且持久化儲存，這是最安全的。
3、為 2 時，每次提交把日誌快取到日誌，但是並不重新整理。
***在大部分作業系統中，把緩衝寫到日誌只是簡單的把資料從innodb快取轉移到了系統快取，也是在記憶體裡，並沒有把資料持久化儲存***

max_allow_packet 設定的太小，有可能會出現複製問題，備庫不能接收主庫傳送過來的資料

inodb_io_capacity：像 PCI-E SSD 需要把值設定的很高。 引數定義了innodb後臺任務每秒可用的IO運算元

線上配置工具：

===複製===
在mysql5.0及之前都是基於語句複製，也就是邏輯複製。 更新必須是序列的，而且需要更多的鎖。

IO執行緒 SQL執行緒 特別執行緒binlog dump執行緒

mysql5.1開始基於行復制。

*_db_db *_ignore_db：
use test;
delete from sakila.film;

*_do_db *_ignore_db 都會在資料庫test下過濾delete語句，而不是在sakila下過濾。 會導致主從不一致。

binlog_do_db binlog_ignore_db 不僅可能會破壞複製，還可能導致某個時間點的備份進行恢復時失敗。

主-主複製，也就是一對主庫。

主主複製更新步驟：
1、在一主伺服器上更新，更新記錄到二進位制日誌中
2、透過複製傳遞給二主的伺服器的中繼日誌，二主讀取中繼日誌後並將記錄到自己的二進位制日誌中（因為開啟了log_slave_updates）
3、由於"事件的伺服器ID"與一主的伺服器的ID相同，因此一主忽略此事件

採用blackhole引擎的分發主庫，在某些情況下會忘記將自增ID寫入到二進位制日誌中。並且無法使用備庫來替代主庫，因為分發主庫的原因，導致各個備庫與原始主庫的二進位制日誌座標已經不相同。

測量備庫延遲，Seconds_behind_mater 是參考值，最好使用pt-heartbeat 指令碼，是複製心跳的一種實現。

在主從複製斷開時，有一張表資料不同時，可以透過 pt-table-sync 來修復，但是要保證主從同步正常。

mysqlbinlog mysql-binlog.000113 | grep '^# at ' 檢視偏移量

strings -n 2 -t d mysql-bin.000113

===應用層最佳化===
1、應用建立了沒必要的sql連線嗎？
2、應用對一個mysql例項建立了太多的連線？
3、做了太多的垃圾查詢？
4、應用使用了連線池嗎？
5、應用是否是長連線？
6、應用是否在不使用的時候還開啟？

長連線和連線池的區別：
1、長連線是在每個連線的基礎上建立連線，不會在程式間共享
2、連線池通常不會導致連線過多，因為他們在程式間排隊和共享連線

explain：
index 和 user index區別：
index和全表掃描一樣，掃描表時按照索引次序進行而不是行，優點是避免了排序，缺點是承擔按索引次序讀取整個表的開銷。
use index 說明mysql正在使用覆蓋索引，只掃描索引的資料。