今天看到一位朋友寫的mysql筆記總結(jié)戳粒，覺得寫的很詳細(xì)很用心力图，這里轉(zhuǎn)載一下役首，供大家參考下尝丐，也希望大家能關(guān)注他
原文地址: http://www.reibang.com/p/47664afa249e

一、MySQL架構(gòu)與歷史

A.并發(fā)控制

1.共享鎖（shared lock衡奥，讀鎖）：共享的爹袁，相互不阻塞的

2.排他鎖（exclusive lock，寫鎖）：排他的矮固，一個(gè)寫鎖會阻塞其他的寫鎖和讀鎖

B.事務(wù)

1.事務(wù)ACID

• 原子性（atomicity）一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元失息，整個(gè)事務(wù)中所有操作要么全部提交成功，要么全部失敗回滾档址，對于一個(gè)事務(wù)來說盹兢，不可能只執(zhí)行其中的一部分操作

• 一致性（consistency）數(shù)據(jù)庫總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)

• 隔離性（isolation）一個(gè)事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不可見的

• 持久性（durability）一旦事務(wù)提交守伸，則其所做的修改就會永久保存到數(shù)據(jù)庫中

2.四種隔離級別

• READ UNCOMMITTED（未提交讀）绎秒，事務(wù)中的修改，即使沒有提交尼摹，對其他事務(wù)也都是可見的见芹，事務(wù)可以讀取未提交的數(shù)據(jù)，也被稱為臟讀（Dirty Read）蠢涝，這個(gè)級別會導(dǎo)致很多問題

• READ COMMITTED（提交讀）玄呛，大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別，一個(gè)事務(wù)開始時(shí)和二，只能“看見”已經(jīng)提交的事務(wù)所做的修改徘铝，一個(gè)事務(wù)從開始直到提交之前，所做的任何修改對其他事務(wù)都是不可見的儿咱，也叫不可重復(fù)讀（nonrepeatable read）庭砍，有可能出現(xiàn)幻讀（Phantom Read），指的是當(dāng)某個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的記錄時(shí)混埠，另外一個(gè)事務(wù)又在該范圍內(nèi)插入了新的記錄怠缸，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時(shí)，會產(chǎn)生幻行（Phantom Row）

• REPEATABLE READ（可重復(fù)讀）钳宪，通過InnoDB和XtraDB存儲引擎揭北，是MySQL的默認(rèn)事務(wù)隔離級別

• SERIALIZABLE（可串行化）最高級別扳炬，通過強(qiáng)制事務(wù)串行執(zhí)行，避免了幻讀問題搔体，會在讀取的每一行數(shù)據(jù)上都加鎖恨樟，可能導(dǎo)致大量的超時(shí)和鎖爭用的問題

3.死鎖：指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用，并請求鎖定對方占用的資源疚俱，從而導(dǎo)致惡性循環(huán)的現(xiàn)象

4.事務(wù)日志：存儲引擎在修改表的數(shù)據(jù)時(shí)只需要修改其內(nèi)存拷貝劝术，再把該修改行為記錄到持久在硬盤上的事務(wù)日志中，而不用每次都將修改的數(shù)據(jù)本身持久到磁盤呆奕。事務(wù)日志持久以后养晋，內(nèi)存中被修改的數(shù)據(jù)在后臺可以慢慢地刷回到磁盤，稱為預(yù)寫式日志（Write-Ahead Logging）

C.多版本并發(fā)控制

1.多版本并發(fā)控制（MVCC）是行級鎖的一個(gè)變種梁钾，但是它在很多情況下避免了加鎖操作绳泉，因此開銷更低。雖然實(shí)現(xiàn)機(jī)制有所不同姆泻，但大都實(shí)現(xiàn)了非阻塞的讀操作零酪，寫操作也只鎖定必要的行

2.MVCC的實(shí)現(xiàn)，是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照來實(shí)現(xiàn)的拇勃，有樂觀和悲觀兩種四苇，只在REPEATABLE READ和READ COMMITTED兩個(gè)隔離級別下工作

D.MySQL的存儲引擎

1.MySQL的.frm文件保存表的定義，SHOW TABLE STATUS顯示表的相關(guān)信息

2.除非有非常特別的原因需要使用其他的存儲引擎潜秋，否則應(yīng)該優(yōu)先考慮InnoDB引擎

3.不要輕易相信MyISAM比InnoDB快之類的經(jīng)驗(yàn)之談蛔琅，這個(gè)結(jié)論并不是絕對的

二、MySQL基準(zhǔn)測試

A.為什么需要基準(zhǔn)測試

1.基準(zhǔn)測試可以觀察系統(tǒng)在不同壓力下的行為峻呛，評估系統(tǒng)的容量，掌握哪些是重要的變化辜窑，或者觀察系統(tǒng)如何處理不同的數(shù)據(jù)

B.基準(zhǔn)測試的策略

1.兩種主要的策略：

• 針對整個(gè)系統(tǒng)的整體測試（集成式full-stack）

• 單獨(dú)測試MySQL（單組件式single-component）

2.測試何種指標(biāo)：

• 吞吐量钩述，指單位時(shí)間內(nèi)的事務(wù)處理數(shù)，常用的測試單位是每秒事務(wù)數(shù)（TPS）穆碎，或每分鐘事務(wù)數(shù)（TPM）

• 響應(yīng)時(shí)間或者延遲牙勘，用于測試任務(wù)所需的整體時(shí)間，根據(jù)具體的應(yīng)用所禀，測試的時(shí)間單位可能是微秒方面、毫秒、秒或者分鐘色徘。通常使用百分比響應(yīng)時(shí)間（percentile response time）來替代最大響應(yīng)時(shí)間

• 并發(fā)性恭金，需要關(guān)注的是正在工作中的并發(fā)操作，或者是同時(shí)工作中的線程數(shù)或者連接數(shù)褂策，在測試期間記錄MySQL數(shù)據(jù)庫的Threads_running狀態(tài)值

• 可擴(kuò)展性横腿，給系統(tǒng)增加一倍的工作颓屑，在理想情況下就能獲得兩倍的效果（即吞吐量增加一倍），對于容量規(guī)范非常有用耿焊，可以提供其他測試無法提供的信息揪惦，來幫助發(fā)現(xiàn)應(yīng)用的瓶頸

C.基準(zhǔn)測試方法

1.需要避免的一些常見錯(cuò)誤：

• 使用真實(shí)數(shù)據(jù)的子集而不是全集

• 使用錯(cuò)誤的數(shù)據(jù)分布

• 使用不真實(shí)的分布參數(shù)

• 在多用戶場景中，只做單用戶測試

• 在單服務(wù)器上測試分布式應(yīng)用

• 與真實(shí)用戶行為不匹配

• 反復(fù)執(zhí)行同一個(gè)查詢

• 沒有檢查錯(cuò)誤

• 忽略了系統(tǒng)預(yù)熱（warm up）的過程

• 使用默認(rèn)的服務(wù)器配置

• 測試時(shí)間太短

2.應(yīng)該建立將參數(shù)和結(jié)果文檔化的規(guī)范罗侯，每一輪測試都必須進(jìn)行詳細(xì)記錄

3.基準(zhǔn)測試應(yīng)該運(yùn)行足夠長的時(shí)間器腋，需要在穩(wěn)定狀態(tài)下測試并觀察

4.在執(zhí)行基準(zhǔn)測試時(shí)，需要盡可能多地收集被測試系統(tǒng)的信息

5.自動化基準(zhǔn)測試可以防止測試人員偶爾遺漏某些步驟钩杰，或者誤操作纫塌，另外也有助于歸檔整個(gè)測試過程，可以選擇shell榜苫、php护戳、perl等，要盡可能使所有測試過程都自動化垂睬，包括裝載數(shù)據(jù)媳荒、系統(tǒng)預(yù)熱、執(zhí)行測試驹饺、記錄結(jié)果等

D.基準(zhǔn)測試工具

1.集成式測試工具：

• ab钳枕，測試HTTP服務(wù)器每秒最多可以處理多少請求

• http_load，和ab類似赏壹，但更加靈活

• jMeter鱼炒，可以加載其他應(yīng)用并測試其性能

2.單組件式測試工具

• mysqlslap，可以模擬服務(wù)器的負(fù)載蝌借，并輸出計(jì)時(shí)信息

• MySQL Benchmark Suite(sql-bench)昔瞧，單線程的，主要用于測試服務(wù)器執(zhí)行查詢的速度

• Super Smack菩佑，提供壓力測試和負(fù)載生成自晰，是一個(gè)復(fù)雜而強(qiáng)大的工具，可以模擬多用戶訪問稍坯，可以加載測試數(shù)據(jù)到數(shù)據(jù)庫酬荞，并支持使用隨機(jī)數(shù)據(jù)填充測試表

• Database Test Suite，類似某些工業(yè)標(biāo)準(zhǔn)測試的測試工具集

• Percona's TPCC-MySQWL Tool

• sysbench瞧哟，多線程系統(tǒng)壓測工具混巧，可以根據(jù)影響數(shù)據(jù)庫服務(wù)器性能的各種因素來評估系統(tǒng)的性能

三、服務(wù)器性能剖析

A.性能優(yōu)化簡介

1.性能勤揩，為完成某件任務(wù)所需要的時(shí)間度量咧党，性能即響應(yīng)時(shí)間，這是非常重要的原則

2.如果目標(biāo)是降低響應(yīng)時(shí)間雄可，就需要理解為什么服務(wù)器執(zhí)行查詢需要這么多時(shí)間凿傅，然后去減少或者消除那些對獲得查詢結(jié)果來說不必要的工作缠犀。無法測量就無法有效地優(yōu)化

3.性能剖析（profiling）是測量和分析時(shí)間花費(fèi)在哪里的主要方法，一般有兩個(gè)步驟：測量任務(wù)所花費(fèi)的時(shí)間聪舒，對結(jié)果進(jìn)行統(tǒng)計(jì)和排序

B.對應(yīng)用程序進(jìn)行性能剖析

1.性能瓶頸可能的影響因素：

• 外部資源

• 應(yīng)用需要處理大量的數(shù)據(jù)

• 在循環(huán)中執(zhí)行昂貴的操作

• 使用了低效的算法

2.PHP性能剖析工具：New Relic辨液、xhprof、Ifp

C.剖析MySQL查詢

1.剖析服務(wù)器負(fù)載

• 慢查詢?nèi)罩荆?.1后long_query_time為0可以捕獲所有的查詢箱残，查詢的響應(yīng)時(shí)間單位可以做到微秒級

• 生成剖析報(bào)告：pt-query-digest

2.剖析單條查詢：

• SHOW PROFILES;

• SHOW [GLOBAL] STATUS;滔迈，返回一些計(jì)數(shù)器

D.診斷間歇性問題

1.盡量不要用試錯(cuò)的方式來解決問題，如果一時(shí)無法定位被辑，可能是測量的方式不正確燎悍，或者測量的點(diǎn)選擇有誤，或者使用的工具不合適

2.確定單條查詢問題還是服務(wù)器問題

• 使用SHOW GLOBAL STATUS

• 使用SHOW PROCESSLIST

• 使用查詢?nèi)罩?/p>

• 理解發(fā)現(xiàn)的問題：使得gnuplot或R盼理，或其他繪圖工具將結(jié)果繪制成圖形

3.捕獲診斷數(shù)據(jù)

• 診斷觸發(fā)器：在問題出現(xiàn)時(shí)能夠捕獲數(shù)據(jù)的基礎(chǔ)谈山，有兩個(gè)常見問題可能導(dǎo)致無法達(dá)到預(yù)期的結(jié)果：誤報(bào)（false positive）或者漏檢（false negative）,pt-stalk工具

• 收集數(shù)據(jù)：盡可能收集所有能收集的數(shù)據(jù)，但只在需要的時(shí)間段內(nèi)收集宏怔，oprofile奏路、strace、tcpdump臊诊、GDB堆棧跟蹤鸽粉、pt-collect、pt-stalk

• 解釋結(jié)果數(shù)據(jù)：pt-mysql-summary抓艳、pt-summary輸出結(jié)果打包触机，pt-sift得到樣本匯總信息，pt-pmp

E.其他剖析工具

1.使用USER_STATISTICS表

2.使用strace玷或，可以調(diào)查系統(tǒng)調(diào)用的情況

四儡首、Schema與數(shù)據(jù)類型優(yōu)化

A.選擇優(yōu)化的數(shù)據(jù)類型

1.數(shù)據(jù)類型的選擇原則：

• 更小的通常更好

• 簡單就好

• 盡量避免NULL

2.應(yīng)該盡量只在對小數(shù)進(jìn)行精確計(jì)算時(shí)才使用DECIMAL，使用int類型通過程序控制單位效果更好

3.使用VARCHAR合適的情況：字符串列的最大長度比平均長度大很多偏友；列的更新很少椒舵，所以碎片不是問題；使用了像UTF-8這樣復(fù)雜的字符集约谈，每個(gè)字符都使用不同的字節(jié)數(shù)進(jìn)行存儲

4.CHAR適合存儲很短的字符串，或者所有值都接近同一個(gè)長度犁钟；不容易產(chǎn)生碎片棱诱，在存儲空間上更有效率

5.通常應(yīng)該盡量使用TIMESTAMP，它比DATETIME空間效率更高

B.MySQL schema設(shè)計(jì)中的陷阱

1.不好的設(shè)計(jì)：

• 太多的列

• 太多的關(guān)聯(lián)

• 全能的枚舉

• 變相的枚舉

• 非此發(fā)明（Not Invent Here）的NULL

C.范式和反范式

1.范式的優(yōu)點(diǎn)：

• 范式化的更新操作通常比反范式化要快

• 當(dāng)數(shù)據(jù)較好地范式化時(shí)涝动，就只有很少或者沒有重復(fù)數(shù)據(jù)迈勋，所以只需要修改更少的數(shù)據(jù)

• 范式化的表通常更小，可以更好地放在內(nèi)存里醋粟，所以執(zhí)行操作會更快

• 很少有多余的數(shù)據(jù)意味著檢索列表數(shù)據(jù)時(shí)更少需要DISTINCT或者GROUP BY語句

2.范式化設(shè)計(jì)的缺點(diǎn)是通常需要關(guān)聯(lián)

3.反范式的優(yōu)點(diǎn)：避免關(guān)聯(lián)靡菇，避免了隨機(jī)I/O，能使用更有效的索引策略

D.緩存表和匯總表

1.有時(shí)提升性能最好的方法是同一張表中保存衍生的冗余數(shù)據(jù)厦凤，有時(shí)也需要創(chuàng)建一張完全獨(dú)立的匯總表或緩存表

2.物化視圖鼻吮，MySQL并不原生支持，F(xiàn)lexviews

3.如果應(yīng)用在表中保存計(jì)數(shù)器较鼓，則在更新計(jì)數(shù)器時(shí)可能踫到并發(fā)問題椎木，創(chuàng)建一張獨(dú)立的表存儲計(jì)數(shù)器，可以幫助避免緩存失效

• 解決獨(dú)立表并發(fā)問題可以建多行博烂，根據(jù)id隨機(jī)更新香椎，然后統(tǒng)計(jì)時(shí)sum()

• 按天或小時(shí)可以單獨(dú)建行，舊時(shí)間可定時(shí)任務(wù)合并到統(tǒng)一的一行

E.加快ALTER TABLE操作的速度

1.兩種方式：

• 一是在一臺不提供服務(wù)的機(jī)器上執(zhí)行ALTER TABLE操作禽篱，然后和提供服務(wù)的主庫進(jìn)行切換

• 二是通過“影子拷貝”畜伐，創(chuàng)建一張新表，然后通過重命名和刪表操作交換兩張表及里面的數(shù)據(jù)

2.快速創(chuàng)建MyISAM索引躺率，先禁用索引玛界，導(dǎo)入數(shù)據(jù)，然后重新啟用索引

五肥照、創(chuàng)建高性能的索引

A.索引基礎(chǔ)

1.索引可以包含一個(gè)或多個(gè)列的值脚仔，如果索引包含多個(gè)列，那么列的順序也十分重要舆绎，因?yàn)镸ySQL只能高效地使用索引的最左前綴列

2.ORM工具能夠產(chǎn)生符合邏輯的鲤脏、合法的查詢，除非只是生成非陈蓝洌基本的查詢猎醇，否則它很難生成適合索引的查詢

3.在MySQL中，索引是在存儲引擎層而不是服務(wù)器層實(shí)現(xiàn)的努溃，所以硫嘶，并沒有統(tǒng)一的索引標(biāo)準(zhǔn)：不同存儲引擎的索引的工作方式并不一樣，也不是所有的存儲引擎都支持所有類型的索引

4.B-Tree意味著所有的值都是按順序存儲的梧税，并且每一個(gè)葉子頁到根的距離相同沦疾，能夠加快訪問數(shù)據(jù)的速度，從索引的根節(jié)點(diǎn)開始進(jìn)行搜索第队，適用于全鍵值哮塞、鍵值范圍或鍵前綴查找

image.png

5.B-Tree索引的限制：

• 如果不是按照索引的最左列開始查找，則無法使用索引

• 不能跳過索引中的列

• 如果查詢中有某個(gè)列的范圍查詢凳谦，則其右邊所有列都無法使用索引優(yōu)化查找

6.哈希索引（hash index）基于哈希表實(shí)現(xiàn)忆畅，只有精確匹配索引所有列的查詢才有效，只有Memory引擎顯式支持哈希索引

7.哈希索引的限制：

• 哈希索引只包含哈希值和行指針尸执，而不存儲字段值家凯，所以不能使用索引中的值來避免讀取行

• 哈希索引數(shù)據(jù)并不是按照索引值順序存儲的缓醋，所以也就無法用于排序

• 哈希索引也不支持部分索引列匹配查找，因?yàn)楣Ｋ饕冀K是使用索引列的全部內(nèi)容來計(jì)算哈希值的

• 只支持等值比較查詢绊诲，不支持任何范圍查詢

• 訪問哈希索引的數(shù)據(jù)非乘土唬快，除非有很多哈希沖突

• 如果哈希沖突很多的話驯镊，一些索引維護(hù)操作的代價(jià)也會很高

8.空間數(shù)據(jù)索引（R-Tree）葫督，MyISAM表支持空間索引，可以用作地理數(shù)據(jù)存儲板惑，開源數(shù)據(jù)庫系統(tǒng)中對GIS的解決方案做得比較好的是PostgreSQL的PostGIS

9.全文索引橄镜，適用于MATCH AGAINST操作侣集，而不是普通的WHERE條件操作

B.索引的優(yōu)點(diǎn)

1.三個(gè)優(yōu)點(diǎn)：

• 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量

• 索引可以幫助服務(wù)器避免排序和臨時(shí)表

• 索引可以將隨機(jī)I/O變?yōu)轫樞騃/O

2.索引三星系統(tǒng)：

• 索引將相關(guān)的記錄放到一起則獲得一星

• 如果索引中的數(shù)據(jù)順序和查找中的排序一致則獲得二星

• 如果索引中的列包含了查詢中需要的全部列則獲得三星

C.高性能的索引策略

1.獨(dú)立的列：如果查詢中的列不是獨(dú)立的现诀，則MySQL不會使用索引枷恕〖“獨(dú)立的列”是指索引列不能是表達(dá)式的一部分捣染，也不能是函數(shù)的參數(shù)

2.前綴索引和索引選擇性

• 通抽涣耍可以索引開始的部分字符葛家，可以大大節(jié)約索引空間蟀伸，但也會降低索引的選擇性

• 索引的選擇性是指喷好，不重復(fù)的索引值（也稱為基數(shù)翔横，cardinality）和數(shù)據(jù)表的記錄總數(shù)（#T）的比值，范圍從1/#T到1之間梗搅，選擇性越高則查詢效率越高禾唁，因?yàn)檫x擇性高的索引可以讓MySQL在查找時(shí)過濾掉更多的行

• MySQL無法使用前綴索引做ORDERY BY和GROUP BY，也無法做覆蓋掃描

3.選擇合適的索引列順序

• 正確的索引列順序依賴于使用該索引的查詢无切，并且同時(shí)需要考慮如何更好地滿足排序和分組的需要

• 在一個(gè)多列B-Tree索引中荡短，索引列的順序意味著索引首先按照最左列進(jìn)行排序，其次是第二列

• 將選擇性最高的列放到索引最前列

4.聚簇索引：并不是一種單獨(dú)的索引類型哆键，而是一種數(shù)據(jù)存儲方式

• 最好避免隨機(jī)的（不連續(xù)且值的分布范圍非常大）聚簇索引掘托，特別是對于I/O密集型的應(yīng)用

5.覆蓋索引：如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，就稱為覆蓋索引

• 覆蓋索引必須要存儲索引列的值籍嘹，

6.如果EXPLAIN出來的type列的值為“index”闪盔，則說明MySQL使用了索引掃描來做排序

7.壓縮（前綴）索引，默認(rèn)只壓縮字符串辱士，減少索引大小锭沟，對于CPU密集型應(yīng)用，因?yàn)閽呙栊枰S機(jī)查找识补，壓縮索引在MyISAM上要慢好幾倍

8.重復(fù)索引是指在相同的列上按照相同的順序創(chuàng)建的相同類型的索引，應(yīng)該避免這樣創(chuàng)建重復(fù)索引

9.索引可以讓查詢鎖定更少的行

D.維護(hù)索引和表

1.CHECK TABLE檢查表是否損壞辫红，ALTER TABLE innodb_tb1 ENGINE=INNODB;修復(fù)表

2.records_in_range()通過向存儲引擎?zhèn)魅雰蓚€(gè)邊界值獲取在這個(gè)范圍大概有多少條記錄凭涂，對于innodb不精確

3.info()返回各種類型的數(shù)據(jù)祝辣，包括索引的基數(shù)

4.可以使用SHOW INDEX FROM命令來查看索引的基數(shù)

5.B-Tree索引可能會碎片化，這會降低查詢的效率

六切油、查詢性能優(yōu)化

A.為什么查詢速度會慢

1.如果要優(yōu)化查詢蝙斜，實(shí)際上要優(yōu)化其子任務(wù)，要么消除其中一些子任務(wù)澎胡，要么減少子任務(wù)的執(zhí)行次數(shù)孕荠，要么讓子任務(wù)運(yùn)行得更快

2.查詢的生命周期大致可以按照順序來看：從客戶端，到服務(wù)器攻谁，然后在服務(wù)器上進(jìn)行解析稚伍，生成執(zhí)行計(jì)劃，執(zhí)行戚宦，并返回結(jié)果給客戶端

B.慢查詢基礎(chǔ)：優(yōu)化數(shù)據(jù)訪問

1.兩個(gè)分析步驟：

• 確認(rèn)應(yīng)用程序是否在檢索大量超過需要的數(shù)據(jù)

• 確認(rèn)MySQL服務(wù)器層是否在分析大量超過需要的數(shù)據(jù)行

2.是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)

• 查詢不需要的記錄

• 多表關(guān)聯(lián)并返回全部列

• 總是取出全部列

• 重復(fù)查詢相同的數(shù)據(jù)

3.MySQL是否在掃描額外的記錄

• 查詢開銷三個(gè)指標(biāo)：響應(yīng)時(shí)間个曙、掃描的行數(shù)、返回的行數(shù)

• 響應(yīng)時(shí)間：服務(wù)時(shí)間和排隊(duì)時(shí)間之和受楼，“快速上限估計(jì)”法

• 掃描的行數(shù)：較短的行的訪問速度更快垦搬，內(nèi)存中的行也比磁盤中的行的訪問 速度要快得多

• 訪問類型：EXPLAIN中的type列反應(yīng)了訪問類型；通過增加合適的索引艳汽；

• 三種方式應(yīng)用WHERE條件：在索引中使用WHERE條件來過濾不匹配的記錄猴贰；使用索引覆蓋掃描（Extra中出現(xiàn)Using index）來返回記錄，直接從索引中過濾不需要的記錄并返回命中結(jié)果河狐；從數(shù)據(jù)表中返回?cái)?shù)據(jù)米绕，然后過濾不滿足條件的記錄（Extra中出現(xiàn)Using Where）

• 需要掃描大量數(shù)據(jù)但只返回少數(shù)的行的優(yōu)化技巧：使用索引覆蓋掃描，改變庫表結(jié)構(gòu)甚牲，重寫復(fù)雜的查詢

C.重構(gòu)查詢的方式

1.MySQL從設(shè)計(jì)上讓連接和斷開連接都很輕量級义郑，在返回一個(gè)小的查詢結(jié)果方面很高效

2.切分查詢，將大查詢切分成小查詢丈钙，每個(gè)查詢功能完全一樣非驮，只完成一小部分，每次只返回一小部分查詢結(jié)果雏赦，可以避免鎖住很多數(shù)據(jù)劫笙、占滿事務(wù)日志、耗盡系統(tǒng)資源星岗、阻塞很多小的但重要的查詢

3.分解關(guān)聯(lián)查詢優(yōu)勢：

• 讓緩存的效率更高

• 將查詢分解后填大，執(zhí)行單個(gè)查詢可以減少鎖的競爭

• 在應(yīng)用層做關(guān)聯(lián)，可以更容易對數(shù)據(jù)庫進(jìn)行拆分俏橘，更容易做到高性能和可擴(kuò)展

• 查詢本身效率也可能會有所提升

• 可以減少冗余記錄的查詢

• 相當(dāng)于在應(yīng)用中實(shí)現(xiàn)了哈希關(guān)聯(lián)允华，而不是使用MySQL的嵌套循環(huán)關(guān)聯(lián)

4.分解關(guān)聯(lián)查詢的場景：

• 當(dāng)應(yīng)用能夠方便地緩存單個(gè)查詢的結(jié)果的時(shí)候

• 當(dāng)可以將數(shù)據(jù)分布到不同的MySQL服務(wù)器上的時(shí)候

• 當(dāng)能夠使用IN()的方式代替關(guān)聯(lián)查詢的時(shí)候

• 當(dāng)查詢中使用同一個(gè)數(shù)據(jù)表的時(shí)候

D.查詢執(zhí)行的基礎(chǔ)

1.查詢執(zhí)行路徑

• 客戶端發(fā)送一條查詢給服務(wù)器

• 服務(wù)器先檢查查詢緩存，如果命中則立刻返回，否則進(jìn)入下一階段

• 服務(wù)器端進(jìn)行SQL解析靴寂、預(yù)處理磷蜀，再由優(yōu)化器生成對應(yīng)的執(zhí)行計(jì)劃

• MySQL根據(jù)優(yōu)化器生成的執(zhí)行計(jì)劃，調(diào)用存儲引擎的API來執(zhí)行查詢

• 將結(jié)果返回給客戶端

2.MySQL客戶端和服務(wù)器之間的通信協(xié)議是“半雙工”的百炬，無法將一個(gè)消息切成小塊獨(dú)立來發(fā)送褐隆，沒法進(jìn)行流量控制，一旦一端開始發(fā)生消息剖踊，另一端要接收完整個(gè)消息才能響應(yīng)它

3.MySQL通常需要等所有的數(shù)據(jù)都已經(jīng)發(fā)送給客戶端才能釋放這條查詢所占用的資源庶弃，所以接收全部結(jié)果并緩存通常可以減少服務(wù)器的壓力

4.查詢狀態(tài)德澈，SHOW FULL PROCESSLIST命令查看：

• Sleep歇攻，線程正在等待客戶端發(fā)送新的請求

• Query，線程正在執(zhí)行查詢或者正在將結(jié)果發(fā)送給客戶端

• Locked圃验，在MySQL服務(wù)器層掉伏，該線程正在等待表鎖

• Analyzing and statistics，線程正在收集存儲引擎的統(tǒng)計(jì)信息澳窑，并生成查詢的執(zhí)行計(jì)劃

• Copying to tmp table [on disk]斧散，線程正在執(zhí)行查詢，并且將其結(jié)果集都復(fù)制到一個(gè)臨時(shí)表中摊聋，要么是在做GROUP BY操作鸡捐，要么是文件排序操作，或者是UNION操作

• Sorting result麻裁，線程正在對結(jié)果集進(jìn)行排序

• Sending data箍镜，線程可能在多個(gè)狀態(tài)之間傳送數(shù)據(jù)，或者在生成結(jié)果集煎源，或者在向客戶端返回?cái)?shù)據(jù)

5.語法解析器和預(yù)處理色迂，通過關(guān)鍵字將SQL語句進(jìn)行解析，并生成一棵對應(yīng)的“解析樹”手销，解析器將使用MySQL語法規(guī)則驗(yàn)證和解析查詢歇僧，預(yù)處理器則根據(jù)一些MySQL規(guī)則進(jìn)一步檢查解析樹是否合法

6.查詢優(yōu)化器，找到最好的執(zhí)行計(jì)劃锋拖，使用基本成本的優(yōu)化器诈悍，將嘗試預(yù)測一個(gè)查詢使用某種執(zhí)行計(jì)劃時(shí)的成本，并選擇其中成本最小的一個(gè)兽埃，使用SHOW STATUS LIKE 'Last_query_cost';查看需要多少個(gè)數(shù)據(jù)頁的隨機(jī)查找

7.導(dǎo)致MySQL查詢優(yōu)化器選擇錯(cuò)誤的原因：

• 統(tǒng)計(jì)信息不準(zhǔn)確侥钳，Innodb不能維護(hù)一個(gè)數(shù)據(jù)表的行數(shù)的精確統(tǒng)計(jì)信息

• 執(zhí)行計(jì)劃中的成本估算不等同于實(shí)際執(zhí)行的成本

• MySQL的最優(yōu)可能和你想的最優(yōu)不一樣

• MySQL從不考慮其他并發(fā)執(zhí)行的查詢

• MySQL也并不是任何時(shí)候都是基于成本的優(yōu)化

• MySQL不會考慮不受其控制的操作的成本

• 優(yōu)化器有時(shí)候無法去估算所有可能的執(zhí)行計(jì)劃

8.MySQL能處理的優(yōu)化類型：

• 重新定義關(guān)聯(lián)表的順序

• 將外鏈接轉(zhuǎn)化成內(nèi)鏈接

• 使用等價(jià)變換規(guī)則

• 優(yōu)化COUNT()、MIN()和MAX()柄错，在EXPLAIN中可以看到“Select tables optimized away”

• 預(yù)估并轉(zhuǎn)化為常數(shù)表達(dá)式舷夺，當(dāng)檢測到一個(gè)表達(dá)式可以轉(zhuǎn)化為常數(shù)的時(shí)候苦酱，就會一直把該表達(dá)式作為常數(shù)進(jìn)行優(yōu)化處理

• 覆蓋索引掃描，當(dāng)索引中的列包含所有查詢中需要使用的列的時(shí)候冕房，就可以使用索引返回需要的數(shù)據(jù)躏啰，而無須查詢對應(yīng)的數(shù)據(jù)行

• 子查詢優(yōu)化

• 提前終止查詢，在發(fā)現(xiàn)已經(jīng)滿足查詢需求的時(shí)候耙册，MySQL總是能夠立刻終止查詢

• 等值傳播，如果兩個(gè)列的值通過等式關(guān)聯(lián)毫捣，那么MySQL能夠把其中一個(gè)列的WHERE條件傳遞到另一列上

• 列表IN()的比較详拙，MySQL將IN()列表中的數(shù)據(jù)先進(jìn)行排序，然后通過二分查找的方式來確定列表中的值是否滿足條件

9.在服務(wù)器層有查詢優(yōu)化器蔓同，卻沒有保存數(shù)據(jù)和索引的統(tǒng)計(jì)信息饶辙，統(tǒng)計(jì)信息由存儲引擎實(shí)現(xiàn)，不同的存儲引擎可能會存儲不同的統(tǒng)計(jì)信息

10.在MySQL中斑粱，每一個(gè)查詢弃揽，每一個(gè)片段（包括子查詢，甚至基于單表的SELECT）都可能是關(guān)聯(lián)

11.對于UNION查詢则北，MySQL先將一系列的單個(gè)查詢結(jié)果放到一個(gè)臨時(shí)表中矿微，然后再重新讀出臨時(shí)表數(shù)據(jù)來完成UNION查詢

12.MySQL對任何關(guān)聯(lián)都執(zhí)行“嵌套循環(huán)關(guān)聯(lián)”操作，即MySQL先在一個(gè)表中循環(huán)取出單條數(shù)據(jù)尚揣，然后再嵌套到下一個(gè)表中尋找匹配的行涌矢，依次下去，直到找到所有表中匹配的行為止

13.全外連接就無法通過嵌套循環(huán)和回溯的方式完成快骗，當(dāng)發(fā)現(xiàn)關(guān)聯(lián)表中沒有找到任何匹配行的時(shí)候娜庇，則可能是因?yàn)殛P(guān)聯(lián)恰好從一個(gè)沒有任何匹配的表開始，MySQL不支持全外連接

14.關(guān)聯(lián)查詢優(yōu)化器方篮，會嘗試在所有的關(guān)聯(lián)順序中選擇一個(gè)成本最小的來生成執(zhí)行計(jì)劃樹名秀，如果可能，優(yōu)化器會遍歷每一個(gè)表然后逐個(gè)做嵌套循環(huán)計(jì)算每一棵可能的執(zhí)行樹的成本藕溅，最后返回一個(gè)最優(yōu)的執(zhí)行計(jì)劃

15.如果有超過n個(gè)表的關(guān)聯(lián)匕得，那么需要檢查n的階乘關(guān)聯(lián)順序，稱為“搜索空間”蜈垮，搜索空間的增長速度非澈孽耍快

16.無論如何排序都是一個(gè)成本很高的操作，所以從性能角度考慮攒发，應(yīng)盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進(jìn)行排序

17.當(dāng)不能使用索引生成排序結(jié)果的時(shí)候调塌，MySQL需要自己進(jìn)行排序，如果數(shù)據(jù)量小則在內(nèi)存中進(jìn)行惠猿，如果數(shù)據(jù)量大則需要使用磁盤羔砾，MySQL將這個(gè)過程稱為文件排序（filesort），即使完全是內(nèi)存排序不需要任何磁盤文件時(shí)也是如此

E.MySQL查詢優(yōu)化器的局限性

1.關(guān)聯(lián)子查詢：MySQL的子查詢實(shí)現(xiàn)得非常糟糕，最糟糕的一類查詢是WHERE條件中包含IN()的子查詢語句姜凄，使用GROUP_CONCAT()在IN()中構(gòu)造一個(gè)由逗號分隔的列表政溃，或者使用EXISTS()來改寫

2.UNION的限制：有時(shí)，MySQL無法將限制條件從外層“下推”到內(nèi)層态秧，這使得原本能夠限制部分返回結(jié)果的條件無法應(yīng)用到內(nèi)層查詢的優(yōu)化上

3.MySQL無法利用多核特性來并行執(zhí)行查詢

4.MySQL不支持哈希關(guān)聯(lián)董虱，MariaDB已經(jīng)實(shí)現(xiàn)了哈希關(guān)聯(lián)

5.MySQL不支持松散索引掃描，5.0后版本在分組查詢中需要找到分組的最大值和最小值時(shí)可以使用松散索引掃描

6.對于MIN()和MAX()查詢申鱼，MySQL的優(yōu)化做得并不好

F.查詢優(yōu)化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY愤诱，當(dāng)多個(gè)語句同時(shí)訪問某一個(gè)表的時(shí)候，哪些語句的優(yōu)先級相對高些捐友、哪些語句的優(yōu)先級相對低些

2.DELAYED淫半，對INSERT和REPLACE有效，會將使用該提示的語句立即返回給客戶端匣砖，并將插入的行數(shù)據(jù)放入到緩沖區(qū)科吭，然后在表空閑時(shí)批量將數(shù)據(jù)寫入，并不是所有的存儲引擎都支持猴鲫，并且該提示會導(dǎo)致函數(shù)LAST_INSERT_ID()無法正常工作

3.STRAIGHT_JOIN对人，可以放置在SELECT語句的SELECT關(guān)鍵字之后，也可以放置在任何兩個(gè)關(guān)聯(lián)表的名字之間变隔。第一個(gè)用法是讓查詢中所有的表按照在語句中出現(xiàn)的順序進(jìn)行關(guān)聯(lián)规伐，第二個(gè)用法則是固定其前后兩個(gè)表的關(guān)聯(lián)順序

4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只對SELECT語句有效匣缘，它們告訴優(yōu)化器對GROUP BY或者DISTINCT查詢?nèi)绾问褂门R時(shí)表及排序

5.SQL_BUFFER_RESULT猖闪，告訴優(yōu)化器將查詢結(jié)果放入到一個(gè)臨時(shí)表，然后盡可能快地釋放表鎖

6.SQL_CACHE和SQL_NO_CACHE肌厨，告訴MySQL這個(gè)結(jié)果集是否應(yīng)該緩存在查詢緩存中

7.SQL_CALC_FOUND_ROWS培慌，會計(jì)算除去LIMIT子句后這個(gè)查詢要返回的結(jié)果集的總數(shù)，而實(shí)際上只返回LIMIT要求的結(jié)果集柑爸，可以通過函數(shù)FOUND_ROW()獲得這個(gè)值

8.FOR UPDATE和LOCK IN SHARE MODE吵护，主要控制SELECT語句的鎖機(jī)制，但只對實(shí)現(xiàn)了行級鎖的存儲引擎有效表鳍，僅InnoDB支持

9.USE INDEX馅而、IGNORE INDEX和FORCE INDEX，告訴優(yōu)化器使用或者不使用哪些索引來查詢記錄

10.MySQL5.0后新增的用來控制優(yōu)化器行為的參數(shù)：

• optimizer_search_depth譬圣，控制優(yōu)化器在窮舉執(zhí)行時(shí)的限度

• optimizer_prune_level瓮恭，讓優(yōu)化器會根據(jù)需要掃描的行數(shù)來決定是否跳過某些執(zhí)行計(jì)劃

• optimizer_switch，包含了一些開啟/關(guān)閉優(yōu)化器特性的標(biāo)志位

G.優(yōu)化特定類型的查詢

1.優(yōu)化COUNT()查詢

• COUNT()是一個(gè)特殊的函數(shù)厘熟，有兩種非常不同的作用：可以統(tǒng)計(jì)某個(gè)列值的數(shù)量屯蹦，也可以統(tǒng)計(jì)行數(shù)维哈，在統(tǒng)計(jì)列值時(shí)要求列值是非空的（不統(tǒng)計(jì)NULL）

• COUNT(*)并不是會像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上登澜，它會忽略所有的列而直接統(tǒng)計(jì)所有的行數(shù)阔挠，當(dāng)MySQL確認(rèn)括號內(nèi)的表達(dá)值不可能為空時(shí)，實(shí)際上就是在統(tǒng)計(jì)行數(shù)

• MyISAM的COUNT()函數(shù)只有沒有任何WHERE條件下的COUNT(*)才非衬匀洌快

• 使用近似值购撼，如EXPLAIN出來的優(yōu)化器估算行數(shù)

• 使用索引覆蓋

• 使用匯總表

• 使用外部緩存系統(tǒng)

2.優(yōu)化關(guān)聯(lián)查詢

• 確保ON或者USING子句中的列上有索引

• 確保任何的GROUP BY和ORDER BY中的表達(dá)式只涉及到一個(gè)表中的列

• 當(dāng)升級MySQL的時(shí)候需要注意：關(guān)聯(lián)語法、運(yùn)算符優(yōu)先級等其他可能會發(fā)生變化的地方

3.優(yōu)化子查詢：盡可能使用關(guān)聯(lián)查詢代替谴仙，如果使用MySQL5.6以上或MariaDB則可以忽略這個(gè)建議

4.優(yōu)化GROUP BY和DISTINCT

• 使用索引優(yōu)化

• 當(dāng)無法使用索引時(shí)份招，GROUP BY使用兩種策略來完成：使用臨時(shí)表或者文件排序來做分組

• 盡可能的將WITH ROLLUP（超級聚合）功能移動應(yīng)用程序中處理

5.優(yōu)化LIMIT分頁

• 最簡單的辦法是盡可能地使用索引覆蓋掃描，而不是查詢所有的列狞甚，然后根據(jù)需要做一次關(guān)聯(lián)操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);

• offset會導(dǎo)致MySQL掃描大量不需要的行然后再拋棄掉廓旬，如果可以記錄上次取數(shù)據(jù)的位置哼审，下次就可以直接從該記錄的位置開始掃描，可以避免使用offset

• 使用預(yù)先計(jì)算的匯總表孕豹，或者關(guān)聯(lián)到一個(gè)冗余表

6.優(yōu)化UNION查詢

• 通過創(chuàng)建并填充臨時(shí)表的方式來執(zhí)行UNION查詢涩盾，因此很多優(yōu)化策略在UNION查詢中都沒法很好地使用，經(jīng)常需要手工地將WHERE励背、LIMIT春霍、ORDER BY等子句下推到UNION的各個(gè)子查詢中

• 除非確實(shí)需要服務(wù)器消除重復(fù)的行，否則就一定要使用UNION ALL

七叶眉、MySQL高級特性

A.分區(qū)表

1.對用戶來說址儒，分區(qū)表是一個(gè)獨(dú)立的邏輯表，但是底層由多個(gè)物理子表組成衅疙，實(shí)際上是對一組底層表的句柄對象（Handler Object）的封裝

2.適用場景：

• 表非常大以至于無法全部都放在內(nèi)存中莲趣，或者只在表的最后部分有熱點(diǎn)數(shù)據(jù)，其他均是歷史數(shù)據(jù)

• 分區(qū)表的數(shù)據(jù)更容易維護(hù)

• 分區(qū)表的數(shù)據(jù)可以分布在不同的物理設(shè)備上饱溢，從而高效地利用多個(gè)硬件設(shè)備

• 可以使用分區(qū)表來避免某些特殊的瓶頸

• 如果需要喧伞，還可以備份和恢復(fù)獨(dú)立的分區(qū)

3.使用限制：

• 一個(gè)表最多只能有1024個(gè)分區(qū)

• 在MySQL5.1中，分區(qū)表達(dá)式必須是整數(shù)绩郎，或者是返回整數(shù)的表達(dá)式潘鲫。在MySQL5.5中，某些場景中可以直接使用列來進(jìn)行分區(qū)

• 如果分區(qū)字段中有主鍵或者唯一索引的列肋杖，那么所有主鍵列和唯一索引列都必須包含進(jìn)來

• 分區(qū)表中無法使用外鍵約束

4.使用分區(qū)表

• 在數(shù)據(jù)量超大的時(shí)候溉仑，B-Tree索引就無法起作用了，除非是索引覆蓋查詢兽愤，否則數(shù)據(jù)庫服務(wù)器需要根據(jù)索引掃描的結(jié)果回表彼念，查詢所有符合條件的記錄挪圾，如果數(shù)據(jù)量巨大，將產(chǎn)生大量隨機(jī)I/O

5.保證大數(shù)據(jù)量的可擴(kuò)展性兩個(gè)策略：

• 命題掃描數(shù)據(jù)逐沙，不要任何索引

• 索引數(shù)據(jù)哲思，并分離熱點(diǎn)

6.分區(qū)策略的問題：

• NULL值會使分區(qū)過濾無效

• 分區(qū)列和索引列不匹配

• 選擇分區(qū)的成本可能很高

• 打開并鎖住所有底層表的成本可能很高

• 維護(hù)分區(qū)的成本可能很高

• 所有分區(qū)都必須使用相同的存儲引擎

• 分區(qū)函數(shù)中可以使用的函數(shù)和表達(dá)式也有一些限制

• 某些存儲引擎不支持分區(qū)

• 對于MyISAM的分區(qū)表，不能再使用LOAD INDEX INTO CACHE操作

• 對于MyISAM表吩案，使用分區(qū)表時(shí)需要打開更多多的文件描述符

7.查詢優(yōu)化

• 很重要的一點(diǎn)是要在WHERE條件中帶入分區(qū)列

• 只能在使用分區(qū)函數(shù)的列本身進(jìn)行比較時(shí)才能過濾分區(qū)棚赔，而不能根據(jù)表達(dá)式的值去過濾分區(qū)，即使這個(gè)表達(dá)式是分區(qū)函數(shù)也不行

B.視圖

1.視圖本身是一個(gè)虛擬表徘郭，不存放任何數(shù)據(jù)靠益，返回的數(shù)據(jù)是MySQL從其他表中生成的

2.MySQL使用兩種算法：合并算法（MERGE）和臨時(shí)表算法（TEMPTABLE），會盡可能地使用合并算法

3.如果視圖中包含GROUP BY残揉、DISTINCT胧后、任何聚合函數(shù)、UNION抱环、子查詢等壳快，只要無法在原表記錄和視圖記錄中建立一一映射的場景中，MySQL都將使用臨時(shí)表算法來實(shí)現(xiàn)視圖

4.可更新視圖（updatable view）是指可以通過更新這個(gè)視圖來更新視圖涉及的相關(guān)表镇草，CHECK OPTION表示任何通過視圖更新的行眶痰，都必須符合視圖本身的WHERE條件定義

5.在重構(gòu)schema的時(shí)候可以使用視圖，使得在修改視圖底層表結(jié)構(gòu)的時(shí)候梯啤，應(yīng)用代碼還可能繼續(xù)不報(bào)錯(cuò)運(yùn)行

6.MySQL中不支持物化視圖（指將視圖結(jié)果數(shù)據(jù)存放在一個(gè)可以查看的表中竖伯，并定期從原始表中刷新數(shù)據(jù)到這個(gè)表中）

7.不會保存視圖定義的原始SQL語句

C.外鍵約束

1.使用外鍵是有成本的，通常要求每次在修改數(shù)據(jù)時(shí)都要在另外一張表中多執(zhí)行一次查找操作

2.如果想確保兩個(gè)相關(guān)表始終有一致的數(shù)據(jù)因宇，那么使用外鍵比在應(yīng)用程序中檢查一致性的性能要高得多七婴，在相關(guān)數(shù)據(jù)的刪除和更新上，也比在應(yīng)用中維護(hù)要更高效

3.外鍵會帶來很大的額外消耗

D.在MySQL內(nèi)部存儲代碼

1.MySQL允許通過觸發(fā)器羽嫡、存儲過程本姥、函數(shù)的形式來存儲代碼，從5.1開始還可以在定時(shí)任務(wù)中存放代碼杭棵，這個(gè)定時(shí)任務(wù)也被稱為“事件”溉浙。存儲過程和存儲函數(shù)都被統(tǒng)稱為“存儲程序”

2.存儲代碼的優(yōu)點(diǎn)：

• 它在服務(wù)器內(nèi)部執(zhí)行烤黍，離數(shù)據(jù)最近籍琳，另外在服務(wù)器上執(zhí)行還可以節(jié)省帶寬和網(wǎng)絡(luò)延遲

• 這是一種代碼重用哩盲，可以方便地統(tǒng)一業(yè)務(wù)規(guī)則，保證某些行為總是一致滓侍，所以也可以為應(yīng)用提供一定的安全性

• 它可以簡化代碼的維護(hù)和版本更新

• 可以幫助提升安全蒋川，比如提供更細(xì)粒度的權(quán)限控制

• 服務(wù)器端可以緩存存儲過程的執(zhí)行計(jì)劃，這對于需要反復(fù)調(diào)用的過程撩笆，會大大降低消耗

• 因?yàn)槭窃诜?wù)器端部署的捺球，所以備份缸浦、維護(hù)都可以在服務(wù)器端完成

• 可以在應(yīng)用開發(fā)和數(shù)據(jù)庫開發(fā)人員之間更好地分工

3.存儲代碼的缺點(diǎn)：

• MySQL本身沒有提供好用的開發(fā)和調(diào)試工具

• 較之應(yīng)用程序的代碼氮兵，存儲代碼效率要稍微差些

• 存儲代碼可能會給應(yīng)用程序代碼的部署帶來額外的復(fù)雜性

• 因?yàn)榇鎯Τ绦蚨疾渴鹪诜?wù)器內(nèi)裂逐，所以可能有安全隱患

• 存儲過程會給數(shù)據(jù)庫服務(wù)器增加額外的壓力，而數(shù)據(jù)庫服務(wù)器的擴(kuò)展性相比應(yīng)用服務(wù)器要差很多

• MySQL并沒有什么選項(xiàng)可以控制存儲程序的資源消耗泣栈，所以在存儲過程中的一個(gè)小錯(cuò)誤卜高，可能直接把服務(wù)器拖死

• 存儲代碼在MySQL中的實(shí)現(xiàn)也有很多限制——執(zhí)行計(jì)劃緩存是連接級別的，游標(biāo)的物化和臨時(shí)表相同南片，異常處理也非常困難

• 調(diào)試MySQL的存儲過程是一件很困難的事情

• 它和基于語句的二進(jìn)投影日志復(fù)制合作得并不好

4.存儲過程和函數(shù)的限制：

• 優(yōu)化器無法使用關(guān)鍵字DETERMINISTIC來優(yōu)化單個(gè)查詢中多次調(diào)用存儲函數(shù)的情況

• 優(yōu)化器無法評估存儲函數(shù)的執(zhí)行成本

• 每個(gè)連接都有獨(dú)立的存儲過程的執(zhí)行計(jì)劃緩存

• 存儲程序和復(fù)制是一組詭異組合

5.觸發(fā)器：可以讓你在執(zhí)行INSERT掺涛、UPDATE或者DELETE的時(shí)候，執(zhí)行一些特定的操作疼进，可以在MySQL中指定是在SQL語句執(zhí)行前觸發(fā)還是在執(zhí)行后觸發(fā)薪缆，可以使用觸發(fā)器實(shí)現(xiàn)一些強(qiáng)制限制，或者某些業(yè)務(wù)邏輯伞广，否則矮燎，就需要在應(yīng)用程序中實(shí)現(xiàn)這些邏輯

6.觸發(fā)器的注意和限制：

• 對每一個(gè)表的每一個(gè)事件，最多只能定義一個(gè)觸發(fā)器

• 只支持“基于行的觸發(fā)”赔癌，也就是說，觸發(fā)器是針對一條記錄的澜沟，而不是針對整個(gè)SQL語句的灾票，如果變更的數(shù)據(jù)集非常大的話，效率會很低

• 觸發(fā)器可以掩蓋服務(wù)器背后的工作

• 觸發(fā)器可以掩蓋服務(wù)器背后的工作茫虽，一個(gè)簡單的SQL語句背后可能包含了很多看不見的工作

• 觸發(fā)器的問題也很難排查刊苍，如果某個(gè)性能問題和觸發(fā)器相關(guān)，會很難分析和定位

• 觸發(fā)器可能導(dǎo)致死鎖和鎖等待

• 觸發(fā)器并不能一定保證更新的原子性

7.觸發(fā)器的用處：

• 實(shí)現(xiàn)一些約束濒析、系統(tǒng)維護(hù)任務(wù)正什，以及更新反范式化數(shù)據(jù)的時(shí)候

• 記錄數(shù)據(jù)變更日志

8.事件：類似于Linux的定時(shí)任務(wù)，指定MySQL在某個(gè)時(shí)候執(zhí)行一段SQL代碼号杏，或者每隔一個(gè)時(shí)間間隔執(zhí)行一段SQL代碼

E.游標(biāo)

1.MySQL在服務(wù)器端提供提供只讀的婴氮、單向的游標(biāo)，而且只能在存儲過程或者更底層的客戶端API中使用盾致，指向的對象都是存儲在臨時(shí)表中而不是實(shí)際查詢到的數(shù)據(jù)主经，所以總是只讀的

2.會帶來額外的性能開銷

3.不支持客戶端的游標(biāo)

F.綁定變量

1.當(dāng)創(chuàng)建一個(gè)綁定變量SQL時(shí)，客戶端向服務(wù)器發(fā)送了一個(gè)SQL語句的原型庭惜。服務(wù)器端收到這個(gè)SQL語句框架后罩驻，解析并存儲這個(gè)SQL語句的部分執(zhí)行計(jì)劃，返回給客戶端一個(gè)SQL語句處理句柄护赊。以后每次執(zhí)行這類查詢惠遏，客戶端都指定使用這個(gè)句柄

2.可以更高效地執(zhí)行大量的重復(fù)語句：

• 在服務(wù)器端只需要解析一次SQL語句

• 在服務(wù)器端某些優(yōu)化項(xiàng)的工作只需要執(zhí)行一次砾跃，因?yàn)樗鼤彺嬉徊糠值膱?zhí)行計(jì)劃

• 以二進(jìn)制的方式只發(fā)送參數(shù)和句柄，比起每次都發(fā)送ASC2碼文本效率更高

• 僅僅是參數(shù)——而不是整個(gè)查詢語句——需要發(fā)送到服務(wù)器端节吮，所以網(wǎng)絡(luò)開銷會更小

• MySQL在存儲參數(shù)的時(shí)候抽高，直接將其存放到緩存中，不再需要在內(nèi)存中多次復(fù)制

3.綁定變量相對也更安全课锌。無須在應(yīng)用程序中處理轉(zhuǎn)義厨内，一則更簡單了，二則也大大減少了SQL注入和攻擊的風(fēng)險(xiǎn)

4.最主要的用途就是在存儲過程中使用渺贤，構(gòu)建并執(zhí)行“動態(tài)”的SQL語句

5.綁定變量的限制：

• 綁定變量是會話級別的雏胃，所以連接之間不能共用綁定變量句柄

• 在5.1版本之前，綁定變量的SQL是不能使用查詢緩存的

• 并不是所有的時(shí)候使用綁定變量都能獲得更好的性能

• 如果總是忘記釋放綁定變量資源志鞍，則在服務(wù)器端很容易發(fā)生資源“泄漏”

• 有些操作瞭亮，比如BEGIN，無法在綁定變量中完成

G.用戶自定義函數(shù)

1.用戶自定義函數(shù)（UDF）必須事先編譯好并動態(tài)鏈接到服務(wù)器上固棚，這種平臺相關(guān)性使得UDF在很多方面都很強(qiáng)大统翩，但一個(gè)錯(cuò)誤也很可能讓服務(wù)器直接崩潰，甚至擾亂服務(wù)器的內(nèi)存或者數(shù)據(jù)

H.插件

1.插件可以在MySQL中新增啟動選項(xiàng)和狀態(tài)值此洲，還可以新增INFORMATION_SCHEMA表厂汗，或者在MySQL的后臺執(zhí)行任務(wù)等等

2.在5.1后支持的插件接口：

• 存儲過程插件

• 后臺插件，可以讓程序在MySQL中運(yùn)行呜师，可以實(shí)現(xiàn)自己的網(wǎng)絡(luò)監(jiān)聽娶桦、執(zhí)行自己的定期任務(wù)

• INFORMATION_SCHEMA插件，提供一個(gè)新的內(nèi)存INFORMATION_SCHEMA表

• 全文解析插件汁汗，提供一種處理文本的功能衷畦，可以根據(jù)自己的需求來對一個(gè)文檔進(jìn)行分詞

• 審計(jì)插件，在查詢執(zhí)行的過程中的某些固定點(diǎn)被調(diào)用知牌，可以記錄MySQL的事件日志

• 認(rèn)證插件祈争，既可可以在MySQL客戶端也可在它的服務(wù)器端，可以使用這類插件來擴(kuò)展MySQL的認(rèn)證功能

I.字符集和校對

1.字符集是一種從二進(jìn)制編碼到某類字符符號的映射角寸，可以參考如何使用一個(gè)字節(jié)來表示英文字母菩混。“校對”是指一組用于某個(gè)字符集的排序規(guī)則

2.每種字符集都可能有多種校對規(guī)則扁藕，并且都有一個(gè)默認(rèn)的校對規(guī)則墨吓，每個(gè)校對規(guī)則都是針對某個(gè)特定的字符集的，和其他的字符集沒有關(guān)系

3.MySQL有很多的選項(xiàng)用于控制字符集纹磺，這些選項(xiàng)和字符集很容易混淆帖烘，只有基于字符的值才真正的“有”字符集的概念

4.MySQL的兩類設(shè)置：創(chuàng)建對象時(shí)的默認(rèn)設(shè)置、服務(wù)器和客戶端通信時(shí)的設(shè)置

5.如果比較的兩個(gè)字符串的字符集不同橄杨，MySQL會先將其轉(zhuǎn)成同一個(gè)字符集再進(jìn)行比較

6.一些需要注意的地方：

• 詭異的character_set_database設(shè)置秘症，當(dāng)改變默認(rèn)數(shù)據(jù)庫的時(shí)候照卦，這個(gè)變量也會跟著變，所以當(dāng)連接到MySQL實(shí)例上又沒有指定要使用的數(shù)據(jù)庫時(shí)乡摹，默認(rèn)值會和character_set_server相同

• LOAD DATA INFILE役耕，當(dāng)使用時(shí)，數(shù)據(jù)庫總是將文件中的字符按照字符集character_set_database來解析

• SELECT INTO OUTFILE聪廉，MySQL會將結(jié)果不做任何轉(zhuǎn)碼地寫入文件

• 嵌入式轉(zhuǎn)義序列瞬痘，MySQL會根據(jù)character_set_client的設(shè)置來解析轉(zhuǎn)義序列

7.某些字符集和校對規(guī)則可能會需要更多的CPU操作，可能會消耗更多的內(nèi)存和存儲空間板熊，甚至還會影響索引的正常使用

• 不同的字符集和校對規(guī)則之間的轉(zhuǎn)換可能會帶來額外的系統(tǒng)開銷

• 只有排序查詢要求的字符集與服務(wù)器數(shù)據(jù)的字符集相同的時(shí)候框全，才能使用索引進(jìn)行排序

• 為了能夠適應(yīng)各種字符集，包括客戶端字符集干签、在查詢中顯式指定的字符集津辩，MySQL會在需要的時(shí)候進(jìn)行字符集轉(zhuǎn)換

J.全文索引

1.MyISAM的全文索引作用對象是一個(gè)“全文集合”，這可能是某個(gè)數(shù)據(jù)表的一列容劳，也可能是多個(gè)列

2.可以根據(jù)WHERE子句中的MATCH AGAINST來區(qū)分查詢是否使用全文索引

3.在使用全文索引進(jìn)行排序的時(shí)候喘沿，MySQL無法再使用索引排序，如果不想使用文件排序的話竭贩，就不要在查詢中使用ORDER BY子句

4.在布爾搜索中蚜印，用戶可以在查詢中自定義某個(gè)被搜索的詞語的相關(guān)性，可能通過一些前綴修飾符來定制搜索

5.全文索引在INSERT留量、UPDATE晒哄、DELETE中的操作代價(jià)很大

6.全文索引會影響索引選擇、WHERE子句肪获、ORDER BY等：

• 如果查詢中使用了MATCH AGAINST子句，而對應(yīng)列上又有可用的全文索引柒傻，那么MySQL就一定會使用這個(gè)全文索引

• 全文索引只能用作全文搜索匹配

• 全文索引不存儲索引列的實(shí)際值孝赫，也就不可能用作索引覆蓋掃描

• 除了相關(guān)性排序，全文索引不能用作其他的排序

7.全文索引的配置和優(yōu)化：

• 經(jīng)常使用OPTIMIZE TABLE來減少碎片红符，如果是I/O密集型的定期進(jìn)行全文索引重建

• 保證索引緩存足夠大

• 提供一個(gè)好的停用詞表

• 忽略一些太短的單詞

• 導(dǎo)入大量數(shù)據(jù)時(shí)青柄，最好通過命令DISABLE KEYS來禁用全文索引，然后導(dǎo)入結(jié)束后使用ENABLE KEYS來建立全文索引

• 如果數(shù)據(jù)集特別大预侯，則需要對數(shù)據(jù)進(jìn)行手動分區(qū)致开，然后將數(shù)據(jù)分布到不同的節(jié)點(diǎn)，再做并行的搜索

K.分布式（XA）事務(wù)

1.XA事務(wù)中需要有一個(gè)事務(wù)協(xié)調(diào)器來保證所有的事務(wù)參與者都完成了準(zhǔn)備工作萎馅。如果協(xié)調(diào)器收到所有的參與者都準(zhǔn)備好的消息双戳，就會告訴所有的事務(wù)可以提交了，MySQL在這個(gè)XA事務(wù)過程中扮演一個(gè)參與者的角色糜芳，而不是協(xié)調(diào)者

2.因?yàn)橥ㄐ叛舆t和參與者本身可能失敗飒货，所以外部XA事務(wù)比內(nèi)部消耗會更大

L.查詢緩存

1.MySQL查詢緩存保存查詢返回的完整結(jié)果魄衅，當(dāng)查詢命中該緩存，MySQL會立刻返回結(jié)果塘辅，跳過了解析晃虫、優(yōu)化和執(zhí)行階段

2.MySQL判斷緩存命中的方法很簡單：緩存放在一個(gè)引用表中，通過一個(gè)哈希值引用扣墩，這個(gè)哈希值包括了如下因素哲银，即查詢本身、當(dāng)前要查詢的數(shù)據(jù)庫呻惕、客戶端協(xié)議的版本等一些其他可能會影響返回結(jié)果的信息

3.當(dāng)判斷緩存是否命中時(shí)荆责，MySQL不會解析、“正規(guī)化”或者參數(shù)化查詢語句蟆融，而是直接使用SQL語句和客戶端發(fā)送過來的其他原始信息草巡。任何字符上的不同，例如空格型酥、注釋——都會導(dǎo)致緩存的不命中

4.當(dāng)查詢語句中有一些不確定的數(shù)據(jù)時(shí)山憨，則不會被緩存，例如包含函數(shù)NOW()或者CURRENT_DATE()的查詢不會被緩存弥喉，只要包含任何用戶自定義函數(shù)郁竟、存儲函數(shù)、用戶變量由境、臨時(shí)表棚亩、mysql庫中的系統(tǒng)表，或者任何包含列級別權(quán)限的表虏杰，都不會被緩存

5.打開查詢緩存對讀和寫操作都會帶來額外的消耗：

• 讀查詢在開始之前必須先檢查是否命中緩存

• 如果這個(gè)讀查詢可以被緩存讥蟆，那么當(dāng)完成執(zhí)行后，MySQL若發(fā)現(xiàn)查詢緩存中沒有這個(gè)查詢纺阔，會將其結(jié)果存入查詢緩存瘸彤，這會帶來額外的系統(tǒng)消耗

• 當(dāng)向某個(gè)表寫入數(shù)據(jù)的時(shí)候，MySQL必須將對應(yīng)表的所有緩存都設(shè)置失效笛钝，如果查詢緩存非常大或者碎片很多质况，這個(gè)操作就可能會帶來很大系統(tǒng)消耗

6.對于需要消耗大量資源的查詢通常都是非常適合緩存的

7.緩存未命中：

• 查詢語句無法被緩存

• MySQL從未處理這個(gè)查詢

• 查詢緩存的內(nèi)存用完了

• 查詢緩存還沒有完成預(yù)熱

• 查詢語句之前從未執(zhí)行過

• 緩存失效操作太多了

8.緩存參數(shù)配置：

• query_cache_type，是否打開查詢緩存

• query_cache_size玻靡，查詢緩存使用的總內(nèi)存空間

• query_cache_min_res_unit结榄，在查詢緩存中分配內(nèi)存塊時(shí)的最小單位，可以幫助減少由碎片導(dǎo)致的內(nèi)存空間浪費(fèi)

• query_cache_limit囤捻，MySQL能夠緩存的最大查詢結(jié)果

• query_cache_wlock_invalidate臼朗，如果某個(gè)數(shù)據(jù)表被其他的連接鎖住，是否仍然從查詢緩存中返回結(jié)果

9.InnoDB和查詢緩存

• 事務(wù)是否可以訪問查詢緩存取決于當(dāng)前事務(wù)ID，以及對應(yīng)的數(shù)據(jù)表上是否有鎖

• 如果表上有任何的鎖依溯，那么對這個(gè)表的任何查詢語句都是無法被緩存的

10.通用查詢緩存優(yōu)化：

• 用多個(gè)小表代替一個(gè)大表對查詢緩存有好處

• 批量寫入時(shí)只需要做一次緩存失效老厌，所以相比單條寫入效率更好

• 因?yàn)榫彺婵臻g太大，在過期操作的時(shí)候可能會導(dǎo)致服務(wù)器僵死黎炉，控制緩存空間的大小

• 無法在數(shù)據(jù)庫或者表級別控制查詢緩存枝秤，但是可以通過SQL_CACHE和SQL_NO_CACHE來控制某個(gè)SELECT語句是否需要進(jìn)行緩存

• 對于 寫密集型的應(yīng)用來說，直接禁用查詢緩存可能會提高系統(tǒng)的性能

• 因?yàn)閷コ庑盘柫康母偁幙妒龋袝r(shí)直接關(guān)閉查詢緩存對讀密集型的應(yīng)用也會有好處

八淀弹、優(yōu)化服務(wù)器設(shè)置

A.MySQL配置的工作原理

1.任何打算長期使用的設(shè)置都應(yīng)該寫到全局配置文件，而不是在命令行特別指定

2.常用變量和動態(tài)修改它們的效果：

• key_buffer_size庆械，可以一次性為鍵緩沖區(qū)（key buffer薇溃，也叫鍵緩存key cache）分配所有指定的空間

• table_cache_size，不會立即生效——會延遲到下次有線程打開表才有效果缭乘，如果值大于緩存中表的數(shù)量沐序，線程可以把最新打開的表放入緩存，如果比緩存中的表數(shù)小堕绩，將從緩存中刪除不常使用的表

• thread_cache_size策幼，不會立即生效——將在下次有連接被關(guān)閉時(shí)產(chǎn)生效果，檢查緩存中是否還有空間來緩存線程奴紧，如果有空間特姐，則緩存該線程以備下次連接征用，如果沒空間黍氮，將銷毀該線程而不再緩存

• query_cache_size唐含，一次性分配并初始化這塊內(nèi)存

• read_buffer_size，只在有查詢需要使用時(shí)才會為該緩存分配內(nèi)存

• read_rnd_buffer_size沫浆，只在有查詢需要使用時(shí)才會為該緩存分配內(nèi)存捷枯，并且只會分配需要的內(nèi)存大小而不是全部指定的大小

• sort_buffer_size，只會在有查詢需要做排序時(shí)才會為該緩存分配內(nèi)存

3.對于連接級別的設(shè)置专执，不要輕易地在全局級別增加它們的值淮捆，除非確認(rèn)這樣做是對的

4.設(shè)置變量時(shí)請小心，并不是值越大就越好他炊，而且如果設(shè)置的值太高，可能更容易導(dǎo)致問題：可能會由于內(nèi)存不足導(dǎo)致服務(wù)器內(nèi)存交換已艰，或者超過地址空間

5.不要期望通過建立一套基準(zhǔn)測試方案痊末，然后不斷迭代地驗(yàn)證對配置項(xiàng)的修改來找到最佳配置方案，而要把時(shí)間花在檢查備份哩掺、監(jiān)控執(zhí)行計(jì)劃的變動之類的事情上凿叠，可能會更有意義

B.什么不該做

1.不要根據(jù)一些“比率”來調(diào)優(yōu)：例如緩存命中率跟緩存是否過大或過小沒有關(guān)系2.不要使用調(diào)優(yōu)腳本

3.不要相信很流行的內(nèi)存消耗公式

C.創(chuàng)建MySQL配置文件

1.MySQL編譯的默認(rèn)設(shè)置并不都是靠譜的，雖然其中大部分都比較合適

2.從一個(gè)比默認(rèn)值大一點(diǎn)但不是大得很離譜的安全值開始是比較好的，MySQL的內(nèi)存利用率并不總是可以預(yù)測的：它可能依賴很多的因素盒件，例如查詢的復(fù)雜性和并發(fā)性

3.配置服務(wù)器的首選途徑：了解它內(nèi)部做了什么蹬碧，以及參數(shù)之間如何相互影響，然后再決定

4.open_files_limit炒刁，在Linux系統(tǒng)上設(shè)置得盡可能大恩沽，如果參數(shù)不夠大，將會踫到24號錯(cuò)誤“打開的文件太多（too many open files）”

5.每隔60秒查看狀態(tài)變量的增量變化：mysqladmin extended-status ri60

D.配置內(nèi)存使用

1.配置MySQL正確地使用內(nèi)存量對高性能是至關(guān)重要的翔始，內(nèi)存消耗分為兩類：可以控制的內(nèi)存和不可以控制的內(nèi)存

2.配置內(nèi)存：

• 確定可以使用的內(nèi)存上限

• 確定每個(gè)連接MySQL需要使用多少內(nèi)存

• 確定操作系統(tǒng)需要多少內(nèi)存才夠用

• 把剩下的內(nèi)存全部給MySQL的緩存

3.MySQL保持一個(gè)連接（線程）只需要少量的內(nèi)存罗心，它還需要一個(gè)基本量的內(nèi)存來執(zhí)行任何給定查詢，需要為高峰時(shí)期執(zhí)行的大量查詢預(yù)留好足夠的內(nèi)存城瞎，否則渤闷，查詢執(zhí)行可能因?yàn)槿狈?nèi)存而導(dǎo)致執(zhí)行效率不佳或執(zhí)行失敗

4.跟查詢一樣，操作系統(tǒng)也需要保留足夠的內(nèi)存給它工作脖镀，如果沒有虛擬內(nèi)存正在交換（Paging）到磁盤飒箭，就是表明操作系統(tǒng)內(nèi)存足夠的最佳跡象

5.如果服務(wù)器只運(yùn)行MySQL，所有不需要為操作系統(tǒng)以及查詢處理保留的內(nèi)存都可以用作MySQL緩存

6.大部分情況下最重要的緩存：

• InnoDB緩沖池

• InnoDB日志文件和MyISAM數(shù)據(jù)的操作系統(tǒng)緩存

• MyISAM鍵緩存

• 查詢緩存

• 無法手工配置的緩存蜒灰，例如二進(jìn)制日志和表定義文件的操作系統(tǒng)緩存

7.InnoDB緩沖池并不僅僅緩存索引：它還會緩存行的數(shù)據(jù)弦蹂、自適應(yīng)哈希索引、插入緩沖（Insert Buffer）卷员、鎖盈匾，以及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)，還使用緩沖池來幫助延遲寫入毕骡，InnoDB嚴(yán)重依賴緩沖池

8.如果事先知道什么時(shí)候需要關(guān)閉InnoDB削饵，可以在運(yùn)行時(shí)修改innodb_max_dirty_pages_pct變量，將值改小未巫，等待刷新純種清理緩沖池窿撬，然后在臟頁數(shù)量較少時(shí)關(guān)閉，可以監(jiān)控the Innodb_buffer_pool_pages_dirty狀態(tài)變量或者使用innotop來監(jiān)控SHOW INNODB STATUS來觀察臟頁的刷新量

7.MyISAM的鍵緩存也被稱為鍵緩沖叙凡，默認(rèn)只有一個(gè)鍵緩存劈伴，但也可以創(chuàng)建多個(gè)，MyISAM自身只緩存索引握爷，不緩存數(shù)據(jù)跛璧，最重要的配置項(xiàng)是key_buffer_size，不要超過索引的總大小新啼，或者不超過操作系統(tǒng)緩存保留總內(nèi)存的25%-50%追城，以更小的為準(zhǔn)

8.了解MyISAM索引實(shí)際上占用多少磁盤空間，查詢INFORMATION_SCHEMA表的INDEX_LENGTH字段燥撞，把它們的值相加座柱，就可以得到索引存儲占用空間

9.塊大小也是很重要的（特別是寫密集型負(fù)載）迷帜，因?yàn)樗绊懥薓yISAM、操作系統(tǒng)緩存色洞，以及文件系統(tǒng)之間的交互戏锹，如果緩存塊太小，可能會踫到寫時(shí)讀取

10.線程緩存保存那些當(dāng)前沒有與連接關(guān)聯(lián)但是準(zhǔn)備為后面新的連接服務(wù)的線程火诸，當(dāng)一個(gè)新的連接創(chuàng)建時(shí)锦针，如果緩存中有線程存在，MySQL從緩存中刪除一個(gè)線程惭蹂，并且把它分配給這個(gè)新的連接伞插，當(dāng)連接關(guān)閉時(shí)，如果線程緩存還有空間的話盾碗，MySQL又會把線程放回緩存媚污，如果沒有空間的話，MySQL會銷毀這個(gè)線程

11.thread_cache_size變量指定了MySQL可以保持在緩存中的線程數(shù)廷雅，一般不需要配置這個(gè)值耗美，除非服務(wù)器會有很多連接請求

12.表緩存（Table Cache）和線程緩存的概念是相似的，但存儲的對象代表的是表航缀，緩存對象包含相關(guān)表.frm文件的解析結(jié)果商架，加上其他數(shù)據(jù)。表緩存可以重用資源芥玉，讓服務(wù)器避免修改MyISAM文件頭來標(biāo)記表“正在使用中”蛇摸，對InnoDB的重要性要小得多

12.表緩存的缺點(diǎn)是，當(dāng)服務(wù)器有很多MyISAM表時(shí)灿巧，可能會導(dǎo)致關(guān)機(jī)時(shí)間較長赶袄，因?yàn)殛P(guān)機(jī)前索引塊必須完成刷新，表都必須標(biāo)記為不再打開

13.InnoDB數(shù)據(jù)字典（Data Dictionary）抠藕，InnoDB自己的表緩存，當(dāng)InnoDB打開一張表溉跃，就增加了一個(gè)對應(yīng)的對象到數(shù)據(jù)字典

14.InnoDB沒有將統(tǒng)計(jì)信息持久化，而是在每次打開表時(shí)重新計(jì)算爸舒，5.6以后可以打開innodb_use_sys_stats_table選項(xiàng)來持久化存儲統(tǒng)計(jì)信息到磁盤

15.可以關(guān)閉InnoDB的innodb_stats_on_metadata選項(xiàng)來避免耗時(shí)的表統(tǒng)計(jì)信息刷新

16.如果可以，最好把innodb_open_files的值設(shè)置得足夠大以使服務(wù)器可以保持所有的.ibd文件同時(shí)打開

E.配置MySQL的I/O行為

1.InnoDB I/O配置

• InnoDB不僅允許控制怎么恢復(fù)，還允許控制怎么打開和刷新數(shù)據(jù)（文件），這會對恢復(fù)和整體性能產(chǎn)生巨大的影響

• 對于常見的應(yīng)用，最重要的一小部分內(nèi)容是InnoDB日志文件大小踏志、InnoDB怎樣刷新它的日志緩沖驱负，以及InnoDB怎樣執(zhí)行I/O

• 整體的日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group兩個(gè)參數(shù)，對寫性能非常重要

• 通常不需要把日志緩沖區(qū)設(shè)置得非常大，推薦的范圍是1MB-8MB谱煤，除非要寫很多相當(dāng)大的BLOB記錄

• 可以通過檢查SHOW INNODB STATUS的輸出中LOG部分來監(jiān)控InnoDB的日志和日志緩沖區(qū)的I/O性能党巾，通過觀察Innodb_os_log_written狀態(tài)變量來查看InnodDB對日志文件寫出了多少數(shù)據(jù)唾那。日志文件的全部大小胜茧，應(yīng)該足夠容納服務(wù)器一個(gè)小時(shí)的活動內(nèi)容

• 如果和持久相比更在乎性能，可以修改innodb_flush_log_at_trx_commit變量來控制日志緩沖刷新的頻繁程度

• 使用innodb_flush_method選項(xiàng)可以配置InnoDB如何跟文件系統(tǒng)相互作用

• InnoDB用表空間并不只是存儲表和索引睹栖，還保存了回滾日志、插入緩沖（Insert Buffer）茧痕、雙寫緩沖（Doublerite Buffer）及其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)

• 為了控制寫入速度磨淌，可以設(shè)置innodb_max_purge_lag變量為一個(gè)大于0的值，這個(gè)值表示InnoDB開始延遲后面的語句更新數(shù)據(jù)之前凿渊，可以等待被清除的最大的事務(wù)數(shù)量

• 雙寫緩沖是表空間的一個(gè)特殊的保留區(qū)域，在一些連續(xù)的塊中足夠保存100個(gè)頁缚柳，本質(zhì)上是一個(gè)最近寫回的頁面的備份拷貝埃脏，當(dāng)InnoDB從緩沖池刷新頁面到磁盤時(shí)，首先把它們寫（或者刷新）到雙寫緩沖秋忙，然后再把它們寫到其所屬的數(shù)據(jù)區(qū)域中彩掐，這可以保證每個(gè)頁面的寫入都是原子并且持久化的

• 設(shè)置innodb_doublewrite為0來關(guān)閉雙寫緩沖

• sync_binlog選項(xiàng)控制MySQL怎么刷新二進(jìn)制日志到磁盤

• 二進(jìn)制日志，如果希望使用expire_logs_days選項(xiàng)來自動清理舊的二進(jìn)制日志灰追，就不要用rm命令去刪

2.MyISAM的I/O配置

• MyISAM通常每次寫操作之后就把索引變更刷新磁盤堵幽，批量操作會更快一些

• 通過設(shè)置delay_key_write變量，可以延遲索引的寫入弹澎，修改的鍵緩沖塊直到表被關(guān)閉才會刷新

• myisam_recover選項(xiàng)控制MyISAM怎樣尋找和修復(fù)錯(cuò)誤

• 內(nèi)存映射使得MyISAM直接通過操作系統(tǒng)的頁面緩存訪問.MYD文件朴下，避免系統(tǒng)調(diào)用的開銷，5.1后可以通過myisam_use_mmap選項(xiàng)打開內(nèi)存映射

F.配置MySQL并發(fā)

1.InnoDB并發(fā)配置

• InnoDB有自己的“線程調(diào)度器”控制線程怎么進(jìn)入內(nèi)核訪問數(shù)據(jù)苦蒿，以及它們在內(nèi)核中一次可以做哪些事殴胧，最基本的限制并發(fā)的方式是使用innodb_thread_concurrency變量，它會限制一次性可以有多少線程進(jìn)入內(nèi)核

• 并發(fā)值 = CPU數(shù)量 * 磁盤數(shù)量 * 2佩迟，在實(shí)踐中使用更小的值會更好一點(diǎn)

2.MyISAM并發(fā)配置

• 盡管MyISAM是表級鎖团滥，它依然可以一邊讀取，一邊并發(fā)追加新行报强，這種情況下只能讀取到查詢開始時(shí)的所有數(shù)據(jù)灸姊，新插入的數(shù)據(jù)是不可見的，這樣可以避免不一致讀

• 通過設(shè)置concurrent_insert這個(gè)變量秉溉，可以配置MyISAM打開并發(fā)插入

• 讓INSERT力惯、REPLACE、DELETE召嘶、UPDATE語句的優(yōu)先級比SELECT語句更低夯膀，設(shè)置low_priority_updates選項(xiàng)就可以

G.基于工作負(fù)載的配置

1.當(dāng)服務(wù)器滿載情況下運(yùn)行時(shí)，請嘗試記錄所有的查詢語句苍蔬，因?yàn)檫@是最好的方式來查看哪種類型的查詢語句占用資源最多诱建，同時(shí)創(chuàng)建processlist快照，通過state或者command字段來聚合它們

2.優(yōu)化BLOB和TEXT場景

• BLOB有幾個(gè)限制使得服務(wù)器對它的處理跟其他類型不一樣碟绑，不能在內(nèi)存臨時(shí)表中存儲BLOB值俺猿，效率很低

• 通過SUBSTRING()函數(shù)把值轉(zhuǎn)換為VARCHAR

• 讓臨時(shí)表更快一些：放在基于內(nèi)存的文件系統(tǒng)

• 如果使用的是InnoDB茎匠，也可以調(diào)大InnoDB日志緩沖大小

• 大字段在InnoDB里可能浪費(fèi)大量空間

• 擴(kuò)展存儲禁用了自適應(yīng)哈希，因?yàn)樾枰暾乇容^列的整個(gè)長度押袍，才能發(fā)現(xiàn)是不是正確的數(shù)據(jù)

• 太長的值可能使得查詢中作為WHERE條件不能使用索引

• 如果一張表里有很多大字段诵冒，最好是把它們組合起來單獨(dú)存到一個(gè)列里面

• 有時(shí)候可以把大字段用COMPRESS()壓縮后再存為BLOB，或者發(fā)送到MySQL前在應(yīng)用程序中進(jìn)行壓縮

3.優(yōu)化排序（Filesorts）：當(dāng)MySQL必須排序BLOG或TEXT字段時(shí)谊惭，它只會使用前綴汽馋，然后忽略剩下部分的值

H.完成基本配置

1.tmp_table_size和max_heap_table_size，這兩個(gè)設(shè)置控制使得Memory引擎的內(nèi)存臨時(shí)表能使用多大的內(nèi)存

2.max_connections圈盔，這個(gè)設(shè)置的作用就像一個(gè)緊急剎車豹芯，以保證服務(wù)器不會因應(yīng)用程序激增的連接而不堪重負(fù)，設(shè)置得以容納正城茫可能達(dá)到的負(fù)載铁蹈，并且要足夠安全，能保證允許你登錄和管理服務(wù)器

3.thread_cache_size众眨，可以通過觀察服務(wù)器一段時(shí)間的活動握牧，來計(jì)算一個(gè)有理有據(jù)的值，250的上限是一個(gè)不錯(cuò)的估算值

4.table_cache_size娩梨，應(yīng)該被設(shè)置得足夠大沿腰，以避免總是需要重新打開和重新解析表的定義，可能通過觀察Open_tables的值及其在一段時(shí)間的變化來檢查該變量

I.安全和穩(wěn)定的設(shè)置

1.expire_logs_days狈定，如果啟用了二進(jìn)制日志矫俺，應(yīng)該打開這個(gè)選項(xiàng)，可以讓服務(wù)器在指定的天數(shù)之后清理舊的二進(jìn)制日志

2.max_allowed_packet掸冤，防止服務(wù)器發(fā)送太大的包厘托，也會控制多大的包可以被接收

3.max_connect_errors，如果知道服務(wù)器可以充分抵御蠻力攻擊稿湿，可以把這個(gè)值設(shè)得非常大铅匹，以有效地禁用主機(jī)黑名單

4.skip_name_resolve，禁用了另一個(gè)網(wǎng)絡(luò)相關(guān)和鑒權(quán)誰相關(guān)的陷阱：DNS查找

5.sql_mode包斑，不建議修改

6.sysdate_is_now萌抵，可能導(dǎo)致與應(yīng)用預(yù)期向后不兼容的選項(xiàng)

7.read_only讨永，禁止沒有特權(quán)的用戶在備庫做變更，只接受從主庫傳輸過來的變更，不接受從應(yīng)用來的變更蚌铜，可以把備庫設(shè)置為只讀模式

8.skip_slave_start审葬，阻止MySQL試圖自動啟動復(fù)制

9.slave_net_timeout官册，控制備庫發(fā)現(xiàn)跟主庫的連接已經(jīng)失敗并且需要重連之前等待的時(shí)間，設(shè)置為一分鐘或更短

10.sync_master_info、sync_relay_log挤聘、sync_relay_log_info，5.5以后版本可用，解決了復(fù)制中備庫長期存在的問題：不把它們的狀態(tài)文件同步到磁盤缝其，所以服務(wù)器崩潰后可能需要人來猜測復(fù)制的位置實(shí)際上在主庫是哪個(gè)位置嘴高，并且可能在中繼日志（Relay Log）里有損壞

J.高級InnoDB設(shè)置

1.innodb莹汤，如果設(shè)置為FORCE止潮，只有在InnoDB可以啟動時(shí)唆樊，服務(wù)器才會啟動

2.innodb_autoinc_lock_mode，控制InnoDB如何生成自增主鍵值

3.innodb_buffer_pool_instances召调，在5.5以后壮虫，可以把緩沖池切分為多段徐伐，在高負(fù)載的多核機(jī)器上提升MySQL可擴(kuò)展性的一個(gè)重要方式

4.innodb_io_capacity，有時(shí)需要把這個(gè)設(shè)置得相當(dāng)高，才能穩(wěn)定地刷新臟頁

5.innodb_read_io_threads和innodb_write_io_threads谋减，控制有多少后臺線程可以被I/O操作使用

6.innodb_strict_mode霸褒，讓MySQL在某些條件下把警告改成拋錯(cuò)，尤其是無效的或者可能有風(fēng)險(xiǎn)的CREATE TABLE選項(xiàng)

7.innodb_old_blocks_time芋哭，指定一個(gè)頁面從LRU鏈表的“年輕”部分轉(zhuǎn)移到“年老”部分之前必須經(jīng)過的毫秒數(shù)，默認(rèn)為0诫钓，設(shè)置為1000毫秒（1秒）非常有效

九、操作系統(tǒng)和硬件優(yōu)化

A.什么限制了MySQL的性能

1.當(dāng)數(shù)據(jù)可以放在內(nèi)存中或者可以從磁盤中以足夠快的速度讀取時(shí)，CPU可能出現(xiàn)瓶頸朗兵，把大量的數(shù)據(jù)集完全放到大容量的內(nèi)存中，以現(xiàn)在的硬件條件完全是可行的

2.I/O瓶頸礁鲁，一般發(fā)生在工作所需的數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過有效內(nèi)存容量的時(shí)候盐欺，如果應(yīng)用程序是分布在網(wǎng)絡(luò)上的，或者如果有大量的查詢和低延遲的要求仅醇，瓶頸可能轉(zhuǎn)移到網(wǎng)絡(luò)上

B.如何為MySQL選擇CPU

1.可以通過檢查CPU利用率來判斷是否是CPU密集型的工作負(fù)載冗美，還需要看看CPU使用率和大多數(shù)重要的查詢的I/O之間的平衡，并注意CPU負(fù)載是否分配均勻

2.當(dāng)遇到CPU密集型的工作時(shí)析二，MySQL通撤弁荩可以從更快的CPU中獲益节预，但還依賴于負(fù)載情況和CPU數(shù)量

3.MySQL復(fù)制也能在高速CPU下工作得非常好，而多CPU對復(fù)制的幫助卻不大

4.多CPU在聯(lián)機(jī)事務(wù)處理（OLTP）系統(tǒng)的場景中非常有用属韧，在這樣的環(huán)境中安拟，并發(fā)可能成為瓶頸

C.平衡內(nèi)存和磁盤資源

1.配置大量內(nèi)存最終目的是避免磁盤I/O，最關(guān)鍵的是平衡磁盤的大小宵喂、速度糠赦、成本和其他因素，以便為工作負(fù)載提供高性能的表現(xiàn)

2.設(shè)計(jì)良好的數(shù)據(jù)庫緩存（如InnoDB緩沖池）锅棕，其效率通常超過操作系統(tǒng)的緩存拙泽，因?yàn)椴僮飨到y(tǒng)緩存是為通用任務(wù)設(shè)計(jì)的

3.數(shù)據(jù)庫服務(wù)器同時(shí)使用順序和隨機(jī)I/O，隨機(jī)I/O從緩存從受益最多

4.每個(gè)應(yīng)用程序都有一個(gè)數(shù)據(jù)的“工作集”——就是這個(gè)工作確實(shí)需要用到的數(shù)據(jù)

5.工作集包括數(shù)據(jù)和索引裸燎，所以應(yīng)該采用緩存單位來計(jì)數(shù)顾瞻，一個(gè)緩存單位是存儲引擎工作的數(shù)據(jù)最小單位

6.找到一個(gè)良好的內(nèi)存/磁盤比例最好的方式是通過試驗(yàn)和基準(zhǔn)測試

7.硬盤選擇考慮因素：存儲容量、傳輸速度德绿、訪問時(shí)間朋其、主軸轉(zhuǎn)速、物理尺寸

8.MySQL如何擴(kuò)展到多個(gè)磁盤上取決于存儲引擎和工作負(fù)載脆炎，InnoDB能很好地?cái)U(kuò)展到多個(gè)硬盤驅(qū)動器梅猿，然而，MyISAM的表鎖限制其寫的可擴(kuò)展性秒裕，因此寫繁重的工作加在MyISAM上袱蚓，可能無法從多個(gè)驅(qū)動器中收益

D.固態(tài)存儲

1.高質(zhì)量閃存設(shè)備具備：

• 相比硬盤有更好的隨機(jī)讀寫性能

• 相比硬盤有更好的順序讀寫性能

• 相比硬盤能更好地支持并發(fā)

• 提升隨機(jī)I/O和并發(fā)性

2.閃存的最重要特征是可以迅速完成多次小單位讀取，但是寫入更有挑戰(zhàn)性几蜻。閃存不能在沒有做擦除操作前改寫一個(gè)單元（Cell）喇潘，并且一次必須擦除一個(gè)大塊。擦除周期是緩慢的梭稚，并且最終會磨損整個(gè)塊

3.垃圾收集對理解閃存很重要颖低。為了保持一些塊是干凈的并且可以被寫入，設(shè)備需要回收臟塊弧烤。這需要設(shè)備上有一些空閑空間

4.許多設(shè)備被填滿后會開始變慢忱屑，速度下降是由于沒有空閑塊時(shí)必須等待擦寫完成所造成的

5.固態(tài)存儲最適合使用在任何有著大量隨機(jī)I/O工作負(fù)載的場景下，隨機(jī)I/O通常是由于數(shù)據(jù)大于服務(wù)器的內(nèi)存導(dǎo)致的暇昂，閃存設(shè)備可能大大緩解這種問題

6.單線程工作負(fù)載也是另一個(gè)閃存的潛在應(yīng)用場景

7.閃存也可以為服務(wù)器整合提供巨大的幫助

8.Flashcache莺戒，磁盤和內(nèi)存技術(shù)的結(jié)合，適合以讀為主的I/O密集型負(fù)載急波，并且工作集太大从铲，用內(nèi)存優(yōu)化并不經(jīng)濟(jì)的情況

9.優(yōu)化固態(tài)存儲上的MySQL

• 增加InnoDB的I/O容量

• 讓InnoDB日志文件更大

• 把一些文件從閃存轉(zhuǎn)移到RAID

• 禁用預(yù)讀

• 配置InnoDB刷新算法

• 禁用雙寫緩沖的可能

• 限制插入緩沖大小，插入緩沖設(shè)計(jì)來用于減少當(dāng)更新行時(shí)不在內(nèi)存中的非唯一索引引起的隨機(jī)I/O

• InnoDB的頁大小

• 優(yōu)化InnoDB頁面校驗(yàn)（Checksum）的替代算法

E.為備庫選擇硬件

1.通常需要跟主庫差不多的配置

F.RAID性能優(yōu)化

1.RAID可以幫助做冗余澄暮、擴(kuò)展存儲容量名段、緩存阱扬，以及加速

2.RAID 0：如果只是簡單的評估成本和性能，是成本最低和性能最高的RAID配置

3.RAID 1：在很多情況下提供很好的讀性能伸辟，并且在不同的磁盤間冗余數(shù)據(jù)价认，所以有很好的冗余性，非常適合用來存放日志或者類似的工作

4.RAID 5：通過分布奇偶校驗(yàn)把數(shù)據(jù)分散到多個(gè)磁盤自娩，如果任何一個(gè)盤的數(shù)據(jù)失效用踩，都可以從奇偶校驗(yàn)塊中重建，但如果有兩個(gè)磁盤失效了忙迁，則整個(gè)卷的數(shù)據(jù)無法恢復(fù)脐彩，最經(jīng)濟(jì)的冗余配置。隨機(jī)寫是昂貴的姊扔，存放數(shù)據(jù)或者日志是一種可接受的選擇惠奸，或者是以讀為主的業(yè)務(wù)

5.RAID 10：對數(shù)據(jù)存儲是個(gè)非常好的選擇，由分片的鏡像組成恰梢，對讀和寫都有良好的擴(kuò)展性

6.RAID 50：由條帶化的RAID 5組成

G.SAN和NAS

1.SAN（Storage Area Network）和NAS（Network-Attached Storage）是兩個(gè)外部文件存儲設(shè)備加載到服務(wù)器的方法佛南，訪問SAN設(shè)備時(shí)通過塊接口，NAS設(shè)備通過基于文件的協(xié)議來訪問

2.SAN允許服務(wù)器訪問非常大量的硬盤驅(qū)動器嵌言，并且通常配置大容量智能高速緩存來緩沖寫入

3.哪些工作放在SAN上不合適：執(zhí)行大量的隨機(jī)I/O的單線程任務(wù)

4.SAN的應(yīng)用：

• 備份嗅回，可以只備份SAN

• 簡化容量規(guī)劃

• 存儲整合還是服務(wù)器整合

• 高可用

• 服務(wù)器之間的交互

• 成本

H.使用多磁盤卷

1.二進(jìn)制日志和數(shù)據(jù)文件分離的真正的優(yōu)勢，是減少事故中同時(shí)丟失數(shù)據(jù)和日志文件的可能性

2.如果有很多磁盤摧茴，投入一些給事務(wù)日志可能會從中受益

I.網(wǎng)絡(luò)配置

1.在生產(chǎn)服務(wù)器上啟用skip_name_resolve是個(gè)好主意绵载，損壞或緩慢的DNS解析對許多應(yīng)用程序都是個(gè)問題，對MySQL尤嚴(yán)重苛白，如果啟用skip_name_resolve選項(xiàng)娃豹，MySQL將不會做任何DNS查找的工作

2.可以通過MySQL的back_log選項(xiàng)控制MySQL的傳入TCP連接隊(duì)列的大小，在每秒有很多連接創(chuàng)建和銷毀的環(huán)境中购裙，默認(rèn)值50是不夠的

3.網(wǎng)絡(luò)物理隔離也是很重要的因素懂版，盡可能避免實(shí)時(shí)的跨數(shù)據(jù)中心的操作是明智的

J.選擇操作系統(tǒng)

1.一般企業(yè)級的MySQL部署在Windows上，但一般的企業(yè)級MySQL更多的還是部署在類UNIX操作系統(tǒng)上

K.選擇文件系統(tǒng)

1.如果可能躏率，最好使用日志文件系統(tǒng)躯畴，如ext3、ext4禾锤、XFS私股、ZFS或者JFS

2.可以調(diào)整文件系統(tǒng)的預(yù)讀行為摹察，因?yàn)檫@可能也是多余的

L.選擇磁盤隊(duì)列調(diào)度策略

1.在GUN/Linux上恩掷，隊(duì)列調(diào)度決定了到塊設(shè)備的請求實(shí)際上發(fā)送到底層設(shè)備的順序，默認(rèn)情況下使用cfq（Completely Fair Queueing供嚎，完全公平排隊(duì)）策略黄娘，在MySQL的工作負(fù)載類型下峭状，cfq會導(dǎo)致很差的響應(yīng)時(shí)間，因?yàn)闀陉?duì)列中延遲一些不必要的請求

2.cfq之外的兩個(gè)選項(xiàng)都適合服務(wù)器級的硬件逼争，noop調(diào)度適合沒有自己的調(diào)度算法的設(shè)備优床，deadline則對RAID控制器和直接使用的磁盤都工作良好

M.線程

1.MySQL每個(gè)連接使用一個(gè)線程，另外還有內(nèi)部處理線程誓焦、特殊用途的線程胆敞，以及所有存儲引擎創(chuàng)建的線程

2.MySQL確實(shí)需要內(nèi)核級線程的支持，而不只是用戶級線程杂伟，這樣才能更有效地使用多個(gè)CPU移层，另外也需要有效的同步原子

N.內(nèi)存交換區(qū)

1.內(nèi)存交換對MySQL性能影響是很糟糕的，它破壞了緩存在內(nèi)存的目的赫粥，并且相對于使用很小的內(nèi)存做緩存观话，使用交換區(qū)的性能更差

2.在GNU/Linux上，可以用vmstat來監(jiān)控內(nèi)存交換越平，最好查看si和so列報(bào)告的內(nèi)存交換I/O活動频蛔，這比看swpd列報(bào)告的交換區(qū)利用率更重要，最佳為0

3.設(shè)置/proc/sys/vm/swappiness為一個(gè)很小的值

4.修改存儲引擎怎么讀取和寫入數(shù)據(jù)秦叛，使用innodb_flush_method=0_DIRECT減輕I/O壓力

5.使用MySQL的memlock配置項(xiàng)晦溪，可以把MySQL鎖定在內(nèi)存

O.操作系統(tǒng)狀態(tài)

1.vmstat

• vmstat 5，每隔5秒刷新一次

• procs挣跋，r列顯示多少進(jìn)程正在等待CPU尼变，b列顯示多少進(jìn)程正在不可中斷地休眠

• memory，swpd列顯示多少塊被換出到了磁盤浆劲，剩下的三個(gè)列顯示了多少塊是空閑的嫌术、多少塊正在被用作緩沖，以及多少正在被用作操作系統(tǒng)的緩存

• swap牌借，顯示頁面交換活動

• io度气，顯示有多少塊從塊設(shè)備讀取（ bi）和寫出（bo）

• system膨报，顯示了每秒中斷（in）和上下文切換（cs）的數(shù)量

• cpu磷籍，顯示所有的CPU時(shí)間花費(fèi)在各類操作的百分比

2.iostat

• iostats -dx 5，每5秒刷新

• rrqm/s和wrqm/s现柠，每秒合并的讀和寫請求院领，意味著操作系統(tǒng)從隊(duì)列中拿出多個(gè)邏輯請求合并為一個(gè)請求到實(shí)際磁盤

• r/s和w/s，每秒發(fā)送到設(shè)備的讀和寫請求

• rsec/s和wsec/s够吩，每秒讀和寫的扇區(qū)數(shù)

• avgrq-sz比然，請求的扇區(qū)數(shù)

• avgqu-sz，在設(shè)備隊(duì)列中等待的請求數(shù)

• await周循，磁盤排除上花費(fèi)的毫秒數(shù)

• svctm强法，服務(wù)請求花費(fèi)的毫秒數(shù)万俗，不包括排除時(shí)間

• %util，至少有一個(gè)活躍請求所占時(shí)間的百分比

3.CPU密集型的機(jī)器饮怯，vmstat輸出通常在us列會有一個(gè)很高的值闰歪，也可能在sy列有很高的值

4.I/O密集型工作負(fù)載下，vmstat會顯示很多處理器在非中斷休眠（b列）狀態(tài)蓖墅，并且wa這一列的值很高

5.發(fā)生內(nèi)存交換的機(jī)器可能在swpd列有一個(gè)很高的值

十库倘、復(fù)制

A.復(fù)制概述

1.MySQL支持兩種復(fù)制方式：基于行的復(fù)制和基于語句的復(fù)制，都是通過在主庫上記錄二進(jìn)制日志论矾、在備庫重放日志的方式來實(shí)現(xiàn)異步的數(shù)據(jù)復(fù)制

2.復(fù)制通常不會增加主庫的開銷于樟，主要是啟用二進(jìn)制日志帶來的開銷，但出于備份或及時(shí)從崩潰中恢復(fù)的目的拇囊，這點(diǎn)開銷也是必要的

3.通過復(fù)制可以將讀操作指向備庫來獲得更好的讀擴(kuò)展迂曲，但對于寫操作，除非設(shè)計(jì)得當(dāng)寥袭，否則并不適合通過寫復(fù)制來擴(kuò)展寫操作

4.復(fù)制解決的問題：

• 數(shù)據(jù)分布

• 負(fù)載均衡

• 備份

• 高可用性和故障切換

• MySQL升級測試

5.復(fù)制如何工作

• 在主庫上把數(shù)據(jù)更新記錄到二進(jìn)制日志（Binary Log）中

• 備庫將主庫上的日志復(fù)制到自己的中繼日志（Relay Log）中

• 備庫讀取中繼日志中的事件路捧，將其重放到備庫數(shù)據(jù)之上

B.配置復(fù)制

1.在每臺服務(wù)器上創(chuàng)建復(fù)制帳號

• 用來監(jiān)控和管理復(fù)制的帳號需要REPLICATION CLIENT權(quán)限，并且針對這兩種目的使用同一個(gè)帳號更加容易

• 如果在主庫上建立了帳號传黄，然后從主庫將數(shù)據(jù)克隆到備庫時(shí)杰扫，備庫也就設(shè)置好了——變成主庫所需要的配置

2.配置主庫和備庫

• 必須明確地指定一個(gè)唯一的服務(wù)器ID

• 有時(shí)候只開啟了二進(jìn)制日志，但卻沒有開啟log_slave_updates膘掰，可能會踫到一些奇怪的現(xiàn)象

• 如果可能的話章姓，最好使用read_only配置選項(xiàng)，會阻止任何沒有特權(quán)權(quán)限的線程修改數(shù)據(jù)

3.通知備庫連接到主庫并從主庫復(fù)制數(shù)據(jù)

4.推薦的復(fù)制配置

• sync_binlog =1识埋，在提交事務(wù)前會將二進(jìn)制日志同步到磁盤上凡伊，保證在服務(wù)器崩潰時(shí)不會丟失事件

• 如果無法容忍服務(wù)器崩潰導(dǎo)致表損壞，推薦使用InnoDB

• 推薦明確指定二進(jìn)制日志的名字窒舟，log_bin=/var/lib/mysql/mysql-bin

• 在備庫上為中繼日志指定絕對路徑系忙，relay_log

• 如果正在使用5.5并且不介意額外的fsync()導(dǎo)致的性能開銷，最好設(shè)置：sync_master_info惠豺，sync_relay_log银还，sync_relay_log_info

C.復(fù)制的原理

1.基于語句的復(fù)制

• 5.0之前只支持基于語句的復(fù)制（也稱為邏輯復(fù)制），主庫會記錄那些造成數(shù)據(jù)更改的查詢洁墙，當(dāng)備庫讀取并重放這些事件時(shí)蛹疯，實(shí)際上只是把主庫上執(zhí)行過的SQL再執(zhí)行一遍

• 好處是實(shí)現(xiàn)相當(dāng)簡單，日志更加緊湊热监，不會占用太多帶寬

• 問題是基于語句的方式可能并不如其看起來那么便利捺弦，還存在一些無法被正確復(fù)制的SQL，更新必須是串行的這需要更多的鎖

2.基于行的復(fù)制

• 5.1開始支持，會將實(shí)際數(shù)據(jù)記錄在二進(jìn)制日志中羹呵，跟其他數(shù)據(jù)庫的實(shí)現(xiàn)比較想像

• 好處是可以正確地復(fù)制每一行骂际，一些語句可以被更加有效地復(fù)制

• 如果使用全表更新疗琉，則開銷會很大冈欢，因?yàn)槊恳恍械臄?shù)據(jù)都會被記錄到二進(jìn)制日志中，這使得二進(jìn)制日志事件非常龐大盈简，并且會給主庫上記錄日志和復(fù)制增加額外的負(fù)載凑耻，更慢的日志記錄則會降低并發(fā)度

3.基于行或基于語句：哪種更優(yōu)

• 基于語句的復(fù)制模式的優(yōu)點(diǎn)：當(dāng)主備的模式不同時(shí)，邏輯復(fù)制能夠在多種情況下工作柠贤；基于語句的方式執(zhí)行復(fù)制的過程基本上就是執(zhí)行SQL語句

• 基于語句的復(fù)制模式的缺點(diǎn)：很多情況下通過基于語句的模式無法正確復(fù)制香浩，如果正在使用觸發(fā)器或者存儲過程，就不要使用基于語句的復(fù)制模式臼勉，除非能夠清楚地確定不會踫到復(fù)制的問題

• 基于行的復(fù)制模式的優(yōu)點(diǎn)：幾乎沒有基于行的復(fù)制模式無法處理的場景邻吭；可能減少鎖的使用，并不要求這種強(qiáng)串行化是可重復(fù)的宴霸；會記錄數(shù)據(jù)變更囱晴；占用更少的CPU；能夠幫助更快地找到并解決數(shù)據(jù)不致的情況

• 基于行的復(fù)制模式的缺點(diǎn)：無法判斷執(zhí)行了哪些SQL瓢谢；無法知道服務(wù)器在做什么畸写；在某些情況下，例如找不到要修改的行時(shí)氓扛，基于行的復(fù)制可能會導(dǎo)致復(fù)制停止

4.復(fù)制文件

• mysql-bin.index枯芬，二進(jìn)制日志文件

• mysql-relay-bin-index，中繼日志的索引文件

• master.info采郎，保存?zhèn)鋷爝B接到主庫所需要的信息

• relay-log.info千所，包含了當(dāng)前備庫復(fù)制的二進(jìn)制日志和中繼日志坐標(biāo)

5.發(fā)送復(fù)制事件到其他備庫：log_slave_updates，可以讓備庫變成其他服務(wù)器的主庫

6.復(fù)制過濾選項(xiàng)

• 在主庫上過濾記錄到二進(jìn)制日志中的事件

• 在備庫上過濾記錄到中繼日志的事件

D.復(fù)制拓?fù)?/strong>