云運(yùn)維涉及的方面比較廣，基礎(chǔ)知識仍然是必不可少：Linux基礎(chǔ)，基本linux命令的使用，Shell腳本編程，Linux操作系統(tǒng)知識（Ubuntu，CentOS系統(tǒng)等）。
目前創(chuàng)新互聯(lián)公司已為上千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、雅安服務(wù)器托管、網(wǎng)站托管維護(hù)、企業(yè)網(wǎng)站設(shè)計(jì)、富順網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。
了解完基礎(chǔ)知識后，可以給自己確定下方向：
1、大數(shù)據(jù)方向： 涉及Hadoop（hdfs，yarn等），spark，hbase，hive，storm等知識
2、虛擬化技術(shù)：openstack，kvm，nova，docker，vmware，xen等
3、應(yīng)用：mysql，redis，memcached，sqlserver
4、對目前的云提供商的業(yè)務(wù)的了解：阿里云，騰訊云，京東云，金山云，AWS等
5、腳本開發(fā)（DevOps必備）：python，ruby
6、比較常用的運(yùn)維工具：jenkins，chef，puppet，ansible等
1、InnoDB存儲引擎
Mysql版本=5.5 默認(rèn)的存儲引擎，MySQL推薦使用的存儲引擎。支持事務(wù)，行級鎖定，外鍵約束。事務(wù)安全型存儲引擎。更加注重?cái)?shù)據(jù)的完整性和安全性。
存儲格式 : 數(shù)據(jù)，索引集中存儲，存儲于同一個表空間文件中。
InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務(wù)對一條行數(shù)據(jù)進(jìn)行讀?。┖?互斥鎖（寫鎖： 允許事務(wù)對一條行數(shù)據(jù)進(jìn)行刪除或更新）， 對于update，insert，delete語句，InnoDB會自動給設(shè)計(jì)的數(shù)據(jù)集加互斥鎖，對于普通的select語句，InnoDB不會加任何鎖。
InnoDB行鎖的實(shí)現(xiàn)方式： InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：如果不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中的所有記錄加鎖，實(shí)際效果跟表鎖一樣。
（1）在不通過索引條件查詢時，InnoDB會鎖定表中的所有記錄。
（2）Mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果使用相同的索引鍵，是會出現(xiàn)沖突的。
（3）當(dāng)表有多個索引的時候，不同的事務(wù)可以使用不同的索引鎖定不同的行，但都是通過行鎖來對數(shù)據(jù)加鎖。
優(yōu)點(diǎn)：
1、支持事務(wù)處理、ACID事務(wù)特性；
2、實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；
3、支持行級鎖和外鍵約束；
4、可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)。
5、鎖級別為行鎖，行鎖優(yōu)點(diǎn)是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點(diǎn)是系統(tǒng)消耗較大。
6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。
缺點(diǎn)：
因?yàn)樗鼪]有保存表的行數(shù)，當(dāng)使用COUNT統(tǒng)計(jì)時會掃描全表。
使用場景：
(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會比較大的情況。
2、 MyISAM存儲引擎
MySQL= 5.5 MySQL默認(rèn)的存儲引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。擅長與處理，高速讀與寫。
功能：
（1）支持?jǐn)?shù)據(jù)壓縮存儲,但壓縮后的表變成了只讀表，不可寫；如果需要更新數(shù)據(jù)，則需要先解壓后更新。
（2）支持表級鎖定，不支持高并發(fā)；
（3）支持并發(fā)插入。寫操作中的插入操作，不會阻塞讀操作（其他操作）；
優(yōu)點(diǎn)：
1.高性能讀?。?/p>
2.因?yàn)樗４媪吮淼男袛?shù)，當(dāng)使用COUNT統(tǒng)計(jì)時不會掃描全表；
缺點(diǎn)：
1、鎖級別為表鎖，表鎖優(yōu)點(diǎn)是開銷小，加鎖快；缺點(diǎn)是鎖粒度大，發(fā)生鎖沖動概率較高，容納并發(fā)能力低，這個引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。
2、此引擎不支持事務(wù)，也不支持外鍵。
3、INSERT和UPDATE操作需要鎖定整個表；
使用場景：
(1)做很多count 的計(jì)算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務(wù)。
InnoDB和MyISAM一些細(xì)節(jié)上的差別：
1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實(shí)驗(yàn)性）。
2、InnoDB中不保存表的 具體行數(shù)，也就是說，執(zhí)行select count() from table時，InnoDB要掃描一遍整個表來計(jì)算有多少行，但是MyISAM只要簡單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語句包含 where條件時，兩種表的操作是一樣的。
3、對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4、DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、另外，InnoDB表的行鎖也不是絕對的，如果在執(zhí)行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表。
1．索引概述
利用關(guān)鍵字，就是記錄的部分?jǐn)?shù)據(jù)（某個字段，某些字段，某個字段的一部分），建立與記錄位置的對應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個沒有建立任何索引的表，就相當(dāng)于一本沒有目錄的書，在每次查詢時就會進(jìn)行全表掃描，這樣會導(dǎo)致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。
2．索引種類
從索引的定義方式和用途中來看：主鍵索引，唯一索引，普通索引，全文索引。
無論任何類型，都是通過建立關(guān)鍵字與位置的對應(yīng)關(guān)系來實(shí)現(xiàn)的。索引是通過關(guān)鍵字找對應(yīng)的記錄的地址。
以上類型的差異：對索引關(guān)鍵字的要求不同。
關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個字段，某些字段，某個字段的一部分）。
普通索引,index：對關(guān)鍵字沒有要求。
唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時增加唯一約束。
主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時增加主鍵約束。
全文索引,fulltext key：關(guān)鍵字的來源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。
PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個，唯一索引可以創(chuàng)建多個。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。
3.索引原則
如果索引不遵循使用原則，則可能導(dǎo)致索引無效。
（1）列獨(dú)立
如果需要某個字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨(dú)立在一側(cè)。否則索引不會用到索引, 例如這條sql就不會用到索引:select * from A where id+1=10;
（2）左原則
Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果業(yè)務(wù)上需要用到'%小明%'這種方式,有兩種方法:1.可以考慮全文索引,但mysql的全文索引不支持中文；2.只查詢索引列或主鍵列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 這三種情況都會用到name的索引;
復(fù)合索引：一個索引關(guān)聯(lián)多個字段，僅僅針對左邊字段有效果，添加復(fù)合索引時，第一個字段很重要，只有包含第一個字段作為查詢條件的情況才會使用復(fù)合索引(必須用到建索引時選擇的第一個字段作為查詢條件,其他字段的順序無關(guān)),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無法使用索引.
（3）OR的使用
必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。
（4）MySQL智能選擇
即使?jié)M足了上面說原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會導(dǎo)致出現(xiàn)大量的隨機(jī)IO，相對于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。
4.索引的使用場景
（1）索引檢索：檢索數(shù)據(jù)時使用索引。
（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。
（3）索引覆蓋: 索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';
建立索引索引時，不能僅僅考慮where檢索，同時考慮其他的使用場景。（在所有的where字段上增加索引，就是不合理的）
5.前綴索引
前綴索引是建立索引關(guān)鍵字一種方案。通常會使用字段的整體作為索引關(guān)鍵字。有時，即使使用字段前部分?jǐn)?shù)據(jù)，也可以去識別某些記錄。就比如一個班級里，我要找王xx，假如姓王的只有1個人，那么就可以建一個關(guān)鍵字為'王'的前綴索引。語法：Index `index_name` (`index_field`(N))使用index_name前N個字符建立的索引。
6.索引失效
（1） 應(yīng)盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；
（2） 應(yīng)盡量避免在 where 子句中使用 or 來連接條件，如果一個字段有索引，一個字段沒有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；
（3） 應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；
（4）應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如select id from t where num/2 = 100；
（5） 應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）應(yīng)盡量避免在where子句中對字段進(jìn)行類型轉(zhuǎn)換，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來,如select id from t where id = 1；如果id字段在表設(shè)計(jì)中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時也一定要用varchar去匹配，sql應(yīng)改為select id from t where id = '1'；
（7）應(yīng)盡量避免在where子句中單獨(dú)引用復(fù)合索引里非第一位置的索引；
join 的兩種算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循環(huán)算法；以如下 SQL 為例：
select * from t1 join t2 on t1.a=t2.a
SQL 執(zhí)行時內(nèi)部流程是這樣的：
1. 先從 t1（假設(shè)這里 t1 被選為驅(qū)動表）中取出一行數(shù)據(jù) X；
2. 從 X 中取出關(guān)聯(lián)字段 a 值，去 t2 中進(jìn)行查找，滿足條件的行取出；
3. 重復(fù)1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。
這就是一個嵌套循環(huán)的過程，如果在被驅(qū)動表上查找數(shù)據(jù)時可以使用索引，總的對比計(jì)算次數(shù)等于驅(qū)動表滿足 where 條件的行數(shù)。假設(shè)這里 t1、t2都是1萬行，則只需要 1萬次計(jì)算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。
如果 t1、t2 的 a 字段都沒有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動表上查找數(shù)據(jù)時都是一次全表掃描，要做1萬次全表掃描，掃描行數(shù)等于 1萬+1萬*1萬，這個效率很低，如果表行數(shù)更多，掃描行數(shù)動輒幾百億，所以優(yōu)化器肯定不會使用這樣的算法，而是選擇 BNL 算法；
BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；
1. 把 t1 表（假設(shè)這里 t1 被選為驅(qū)動表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；
2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進(jìn)行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。
這個算法下，執(zhí)行計(jì)劃的 Extra 中會出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬+1萬。但是由于 joinbuffer 維護(hù)的是一個無序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計(jì)算次數(shù)等于1萬*1萬。另外如果 joinbuffer 不夠大放不下驅(qū)動表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會導(dǎo)致被驅(qū)動表也做多次全表掃描。
BNLJ相對于NLJ的優(yōu)點(diǎn)在于，驅(qū)動層可以先將部分?jǐn)?shù)據(jù)加載進(jìn)buffer，這種方法的直接影響就是將大大減少內(nèi)層循環(huán)的次數(shù)，提高join的效率。
例如：
如果內(nèi)層循環(huán)有100條記錄，外層循環(huán)也有100條記錄，這樣的話，每次外層循環(huán)先將10條記錄放到buffer中，內(nèi)層循環(huán)的100條記錄每條與這個buffer中的10條記錄進(jìn)行匹配，只需要匹配內(nèi)層循環(huán)總記錄數(shù)次即可結(jié)束一次循環(huán)（在這里，即只需要匹配100次即可結(jié)束），然后將匹配成功的記錄連接后放入結(jié)果集中，接著，外層循環(huán)繼續(xù)向buffer中放入10條記錄，同理進(jìn)行匹配，并將成功的記錄連接后放入結(jié)果集。后續(xù)循環(huán)以此類推，直到循環(huán)結(jié)束，將結(jié)果集發(fā)給client為止。
可以發(fā)現(xiàn)，若用NLJ，則需要100 * 100次才可結(jié)束，BNLJ則需要100 / block_size * 100 = 10 * 100次就可結(jié)束,大大減少了循環(huán)次數(shù)。
JOIN 按照功能大致分為如下三類：
JOIN、STRAIGHT_JOIN、INNER JOIN（內(nèi)連接,或等值連接）：取得兩個表中存在連接匹配關(guān)系的記錄。
LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無對應(yīng)匹配記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無匹配對應(yīng)記錄。
注意：mysql不支持Full join,不過可以通過UNION 關(guān)鍵字來合并 LEFT JOIN 與 RIGHT JOIN來模擬FULL join。
mysql 多表連接查詢方式，因?yàn)閙ysql只支持NLJ算法,所以如果是小表驅(qū)動大表則效率更高；反之則效率下降；因此mysql對內(nèi)連接或等值連接的方式做了一個優(yōu)化,會去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動表。
INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因?yàn)镸ySQL 會按照"小表驅(qū)動大表的策略"進(jìn)行優(yōu)化。當(dāng)出現(xiàn)需要排序時，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動表。
兩表JOIN優(yōu)化
a.當(dāng)無order by條件時，根據(jù)實(shí)際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；
b.當(dāng)有order by條件時，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語句；
1）如果第一行的驅(qū)動表為a，則效率會非常高，無需優(yōu)化；
2）否則，因?yàn)橹荒軐︱?qū)動表字段直接排序的緣故，會出現(xiàn)using temporary，所以此時需要使用STRAIGHT_JOIN明確a為驅(qū)動表，來達(dá)到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過濾條件，此時的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動表。注：使用STRAIGHT_JOIN雖然不會using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過b表，那么有可能使用STRAIGHT_JOIN時比原來的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。
在使用left join（或right join）時，應(yīng)該清楚的知道以下幾點(diǎn)：
(1). on與 where的執(zhí)行順序
ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以后，WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。
所以我們要注意：在使用Left (right) join的時候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的數(shù)據(jù)不滿足ON后面的條件,也會在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關(guān)聯(lián)的數(shù)據(jù)也會被過濾掉。
(3).盡量避免子查詢，而用join
往往性能這玩意兒，更多時候體現(xiàn)在數(shù)據(jù)量比較大的時候，此時，我們應(yīng)該避免復(fù)雜的子查詢。
（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)用下面的語句替換：select num from a where exists(select 1 from b where num=a.num)
（2）Update 語句，如果只更改1、2個字段，不要Update全部字段，否則頻繁調(diào)用會引起明顯的性能消耗，同時帶來大量日志。
（3）join語句,MySQL里面的join是用小表去驅(qū)動大表,而由于MySQL join實(shí)現(xiàn)的原理就是做循環(huán)，比如left join就是對左邊的數(shù)據(jù)進(jìn)行循環(huán)去驅(qū)動右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫join的時候盡可能的先估計(jì)兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動大結(jié)果集.值得注意的是在使用left/right join的時候,從表的條件應(yīng)寫在on之后,主表應(yīng)寫在where之后.否則MySQL會當(dāng)作普通的連表查詢；
（4）select count(*) from table；這樣不帶任何條件的count會引起全表掃描，并且沒有任何業(yè)務(wù)意義，是一定要杜絕的；
（5）select * from t 這種語句要盡量避免，使用具體的字段代替*，更有實(shí)際意義，需要什么字段就返回什么字段；
（6）數(shù)據(jù)量大的情況下，limit要慎用，因?yàn)槭褂胠imit m，n方式分頁時，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會非常低，當(dāng)分頁的頁數(shù)大于一定的數(shù)量之后，就可以換種方式來分頁：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
《MySQL是怎樣運(yùn)行的：從根兒上理解 MySQL》采用詼諧幽默的表達(dá)方式，對MySQL的底層運(yùn)行原理進(jìn)行了介紹，內(nèi)容涵蓋了使用MySQL的同學(xué)在求職面試和工作中常見的一些核心概念。總計(jì)22 章，劃分為4個部分。第1部分介紹了MySQL入門的一些知識，比如MySQL的服務(wù)器程序和客戶端程序有哪些、MySQL的啟動選項(xiàng)和系統(tǒng)變量，以及使用的字符集等。第2部分是本書后續(xù)章節(jié)的基礎(chǔ)，介紹了MySQL的一些基礎(chǔ)知識，比如記錄、頁面、索引、表空間的結(jié)構(gòu)和用法等。第3部分則與大家在工作中經(jīng)常遇到的查詢優(yōu)化問題緊密相關(guān)，介紹了單表查詢、連接查詢的執(zhí)行原理，MySQL基于成本和規(guī)則的優(yōu)化具體指什么，并詳細(xì)分析了Explain語句的執(zhí)行結(jié)果。第4部分則是與MySQL中的事務(wù)和鎖相關(guān)，介紹了事務(wù)概念的來源，MySQL是如何實(shí)現(xiàn)事務(wù)的，包括redo日志、undo日志、MVCC、各種鎖的細(xì)節(jié)等。
盡管《MySQL是怎樣運(yùn)行的：從根兒上理解 MySQL》在寫作時參考的MySQL源代碼版本是5.7.22，但是大部分內(nèi)容與具體的版本號并沒有多大關(guān)系。無論是很早之前就已身居MySQL專家的人員，還是希望進(jìn)一步提升技能的DBA，甚至是三五年后才會入行的“萌新”，本書都是他們徹底了解MySQL運(yùn)行原理的優(yōu)秀書
前言:
MYSQL 應(yīng)該是最流行了 WEB 后端數(shù)據(jù)庫。雖然 NOSQL 最近越來越多的被提到，但是相信大部分架構(gòu)師還是會選擇 MYSQL 來做數(shù)據(jù)存儲。本文作者總結(jié)梳理MySQL性能調(diào)優(yōu)的15個重要變量，又不足需要補(bǔ)充的還望大佬指出。
1.DEFAULT_STORAGE_ENGINE
如果你已經(jīng)在用MySQL 5.6或者5.7，并且你的數(shù)據(jù)表都是InnoDB，那么表示你已經(jīng)設(shè)置好了。如果沒有，確保把你的表轉(zhuǎn)換為InnoDB并且設(shè)置default_storage_engine為InnoDB。
為什么？簡而言之，因?yàn)镮nnoDB是MySQL(包括Percona Server和MariaDB)最好的存儲引擎 – 它支持事務(wù)，高并發(fā)，有著非常好的性能表現(xiàn)(當(dāng)配置正確時)。這里有詳細(xì)的版本介紹為什么
2.INNODB_BUFFER_POOL_SIZE
這個是InnoDB最重要變量。實(shí)際上，如果你的主要存儲引擎是InnoDB，那么對于你，這個變量對于MySQL是最重要的。
基本上，innodb_buffer_pool_size指定了MySQL應(yīng)該分配給InnoDB緩沖池多少內(nèi)存，InnoDB緩沖池用來存儲緩存的數(shù)據(jù)，二級索引，臟數(shù)據(jù)(已經(jīng)被更改但沒有刷新到硬盤的數(shù)據(jù))以及各種內(nèi)部結(jié)構(gòu)如自適應(yīng)哈希索引。
根據(jù)經(jīng)驗(yàn)，在一個獨(dú)立的MySQL服務(wù)器應(yīng)該分配給MySQL整個機(jī)器總內(nèi)存的80%。如果你的MySQL運(yùn)行在一個共享服務(wù)器，或者你想知道InnoDB緩沖池大小是否正確設(shè)置，詳細(xì)請看這里。
3.INNODB_LOG_FILE_SIZE
InnoDB重做日志文件的設(shè)置在MySQL社區(qū)也叫做事務(wù)日志。直到MySQL 5.6.8事務(wù)日志默認(rèn)值innodb_log_file_size=5M是唯一最大的InnoDB性能殺手。從MySQL 5.6.8開始，默認(rèn)值提升到48M,但對于許多稍繁忙的系統(tǒng)，還遠(yuǎn)遠(yuǎn)要低。
根據(jù)經(jīng)驗(yàn)，你應(yīng)該設(shè)置的日志大小能在你服務(wù)器繁忙時能存儲1-2小時的寫入量。如果不想這么麻煩，那么設(shè)置1-2G的大小會讓你的性能有一個不錯的表現(xiàn)。這個變量也相當(dāng)重要，更詳細(xì)的介紹請看這里。
當(dāng)然，如果你有大量的大事務(wù)更改，那么，更改比默認(rèn)innodb日志緩沖大小更大的值會對你的性能有一定的提高，但是你使用的是autocommit，或者你的事務(wù)更改小于幾k，那還是保持默認(rèn)的值吧。
4.INNODB_FLUSH_LOG_AT_TRX_COMMIT
默認(rèn)下，innodb_flush_log_at_trx_commit設(shè)置為1表示InnoDB在每次事務(wù)提交后立即刷新同步數(shù)據(jù)到硬盤。如果你使用autocommit，那么你的每一個INSERT, UPDATE或DELETE語句都是一個事務(wù)提交。
同步是一個昂貴的操作(特別是當(dāng)你沒有寫回緩存時)，因?yàn)樗婕皩τ脖P的實(shí)際同步物理寫入。所以如果可能，并不建議使用默認(rèn)值。
兩個可選的值是0和2:
* 0表示刷新到硬盤，但不同步(提交事務(wù)時沒有實(shí)際的IO操作)
* 2表示不刷新和不同步(也沒有實(shí)際的IO操作)
所以你如果設(shè)置它為0或2，則同步操作每秒執(zhí)行一次。所以明顯的缺點(diǎn)是你可能會丟失上一秒的提交數(shù)據(jù)。具體來說，你的事務(wù)已經(jīng)提交了，但服務(wù)器馬上斷電了，那么你的提交相當(dāng)于沒有發(fā)生過。
顯示的，對于金融機(jī)構(gòu)，如銀行，這是無法忍受的。不過對于大多數(shù)網(wǎng)站，可以設(shè)置為innodb_flush_log_at_trx_commit=0|2，即使服務(wù)器最終崩潰也沒有什么大問題。畢竟，僅僅在幾年前有許多網(wǎng)站還是用MyISAM，當(dāng)崩潰時會丟失30s的數(shù)據(jù)(更不要提那令人抓狂的慢修復(fù)進(jìn)程)。
那么，0和2之間的實(shí)際區(qū)別是什么？性能明顯的差異是可以忽略不計(jì)，因?yàn)樗⑿碌讲僮飨到y(tǒng)緩存的操作是非?？斓摹Ｋ院苊黠@應(yīng)該設(shè)置為0，萬一MySQL崩潰(不是整個機(jī)器)，你不會丟失任何數(shù)據(jù)，因?yàn)閿?shù)據(jù)已經(jīng)在OS緩存，最終還是會同步到硬盤的。
5.SYNC_BINLOG
已經(jīng)有大量的文檔寫到sync_binlog，以及它和innodb_flush_log_at_trx_commit的關(guān)系，下面我們來簡單的介紹下：
a) 如果你的服務(wù)器沒有設(shè)置從服務(wù)器，而且你不做備份，那么設(shè)置sync_binlog=0將對性能有好處。
b) 如果你有從服務(wù)器并且做備份，但你不介意當(dāng)主服務(wù)器崩潰時在二進(jìn)制日志丟失一些事件，那么為了更好的性能還是設(shè)置為sync_binlog=0.
c) 如果你有從服務(wù)器并且備份，你非常在意從服務(wù)器的一致性，以及能及時恢復(fù)到一個時間點(diǎn)(通過使用最新的一致性備份和二進(jìn)制日志將數(shù)據(jù)庫恢復(fù)到特定時間點(diǎn)的能力)，那么你應(yīng)該設(shè)置innodb_flush_log_at_trx_commit=1，并且需要認(rèn)真考慮使用sync_binlog=1。
問題是sync_binlog=1代價比較高 – 現(xiàn)在每個事務(wù)也要同步一次到硬盤。你可能會想為什么不把兩次同步合并成一次，想法正確 – 新版本的MySQL(5.6和5.7，MariaDB和Percona Server)已經(jīng)能合并提交，那么在這種情況下sync_binlog=1的操作也不是這么昂貴了，但在舊的mysql版本中仍然會對性能有很大影響。
6.INNODB_FLUSH_METHOD
將innodb_flush_method設(shè)置為O_DIRECT以避免雙重緩沖.唯一一種情況你不應(yīng)該使用O_DIRECT是當(dāng)你操作系統(tǒng)不支持時。但如果你運(yùn)行的是Linux，使用O_DIRECT來激活直接IO。
不用直接IO，雙重緩沖將會發(fā)生，因?yàn)樗械臄?shù)據(jù)庫更改首先會寫入到OS緩存然后才同步到硬盤 – 所以InnoDB緩沖池和OS緩存會同時持有一份相同的數(shù)據(jù)。特別是如果你的緩沖池限制為總內(nèi)存的50%，那意味著在寫密集的環(huán)境中你可能會浪費(fèi)高達(dá)50%的內(nèi)存。如果沒有限制為50%，服務(wù)器可能由于OS緩存的高壓力會使用到swap。
簡單地說，設(shè)置為innodb_flush_method=O_DIRECT。
7.INNODB_BUFFER_POOL_INSTANCES
MySQL 5.5引入了緩沖實(shí)例作為減小內(nèi)部鎖爭用來提高M(jìn)ySQL吞吐量的手段。
在5.5版本這個對提升吞吐量幫助很小，然后在MySQL 5.6版本這個提升就非常大了，所以在MySQL5.5中你可能會保守地設(shè)置innodb_buffer_pool_instances=4，在MySQL 5.6和5.7中你可以設(shè)置為8-16個緩沖池實(shí)例。
你設(shè)置后觀察會覺得性能提高不大，但在大多數(shù)高負(fù)載情況下，它應(yīng)該會有不錯的表現(xiàn)。
對了，不要指望這個設(shè)置能減少你單個查詢的響應(yīng)時間。這個是在高并發(fā)負(fù)載的服務(wù)器上才看得出區(qū)別。比如多個線程同時做許多事情。
8.INNODB_THREAD_CONCURRENCY
InnoDB有一種方法來控制并行執(zhí)行的線程數(shù) – 我們稱為并發(fā)控制機(jī)制。大部分是由innodb_thread_concurrency值來控制的。如果設(shè)置為0，并發(fā)控制就關(guān)閉了，因此InnoDB會立即處理所有進(jìn)來的請求(盡可能多的)。
在你有32CPU核心且只有4個請求時會沒什么問題。不過想像下你只有4CPU核心和32個請求時 – 如果你讓32個請求同時處理，你這個自找麻煩。因?yàn)檫@些32個請求只有4 CPU核心，顯然地會比平常慢至少8倍(實(shí)際上是大于8倍)，而然這些請求每個都有自己的外部和內(nèi)部鎖，這有很大可能堆積請求。
下面介紹如何更改這個變量，在mysql命令行提示符執(zhí)行：
對于大多數(shù)工作負(fù)載和服務(wù)器，設(shè)置為8是一個好開端，然后你可以根據(jù)服務(wù)器達(dá)到了這個限制而資源使用率利用不足時逐漸增加?？梢酝ㄟ^show engine innodb status\G來查看目前查詢處理情況，查找類似如下行：
9.SKIP_NAME_RESOLVE
這一項(xiàng)不得不提及，因?yàn)槿匀挥泻芏嗳藳]有添加這一項(xiàng)。你應(yīng)該添加skip_name_resolve來避免連接時DNS解析。
大多數(shù)情況下你更改這個會沒有什么感覺，因?yàn)榇蠖鄶?shù)情況下DNS服務(wù)器解析會非?？?。不過當(dāng)DNS服務(wù)器失敗時，它會出現(xiàn)在你服務(wù)器上出現(xiàn)“unauthenticated connections” ，而就是為什么所有的請求都突然開始慢下來了。
所以不要等到這種事情發(fā)生才更改?，F(xiàn)在添加這個變量并且避免基于主機(jī)名的授權(quán)。
10.INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX
* innodb_io_capacity：用來當(dāng)刷新臟數(shù)據(jù)時，控制MySQL每秒執(zhí)行的寫IO量。
* innodb_io_capacity_max: 在壓力下，控制當(dāng)刷新臟數(shù)據(jù)時MySQL每秒執(zhí)行的寫IO量
首先，這與讀取無關(guān) – SELECT查詢執(zhí)行的操作。對于讀操作，MySQL會盡最大可能處理并返回結(jié)果。至于寫操作，MySQL在后臺會循環(huán)刷新，在每一個循環(huán)會檢查有多少數(shù)據(jù)需要刷新，并且不會用超過innodb_io_capacity指定的數(shù)來做刷新操作。這也包括更改緩沖區(qū)合并（在它們刷新到磁盤之前，更改緩沖區(qū)是輔助臟頁存儲的關(guān)鍵）。
第二，我需要解釋一下什么叫“在壓力下”，MySQL中稱為”緊急情況”，是當(dāng)MySQL在后臺刷新時，它需要刷新一些數(shù)據(jù)為了讓新的寫操作進(jìn)來。然后，MySQL會用到innodb_io_capacity_max。
那么，應(yīng)該設(shè)置innodb_io_capacity和innodb_io_capacity_max為什么呢？
最好的方法是測量你的存儲設(shè)置的隨機(jī)寫吞吐量，然后給innodb_io_capacity_max設(shè)置為你的設(shè)備能達(dá)到的最大IOPS。innodb_io_capacity就設(shè)置為它的50-75%，特別是你的系統(tǒng)主要是寫操作時。
通常你可以預(yù)測你的系統(tǒng)的IOPS是多少。例如由8 15k硬盤組成的RAID10能做大約每秒1000隨機(jī)寫操作，所以你可以設(shè)置innodb_io_capacity=600和innodb_io_capacity_max=1000。許多廉價企業(yè)SSD可以做4,000-10,000 IOPS等。
這個值設(shè)置得不完美問題不大。但是，要注意默認(rèn)的200和400會限制你的寫吞吐量，因此你可能偶爾會捕捉到刷新進(jìn)程。如果出現(xiàn)這種情況，可能是已經(jīng)達(dá)到你硬盤的寫IO吞吐量，或者這個值設(shè)置得太小限制了吞吐量。
11.INNODB_STATS_ON_METADATA
如果你跑的是MySQL 5.6或5.7，你不需要更改innodb_stats_on_metadata的默認(rèn)值，因?yàn)樗呀?jīng)設(shè)置正確了。
不過在MySQL 5.5或5.1，強(qiáng)烈建議關(guān)閉這個變量 – 如果是開啟，像命令show table status會立即查詢INFORMATION_SCHEMA而不是等幾秒再執(zhí)行，這會使用到額外的IO操作。
從5.1.32版本開始，這個是動態(tài)變量，意味著你不需要重啟MySQL服務(wù)器來關(guān)閉它。
12.INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN INNODB_BUFFER_POOL_LOAD_AT_STARTUP
innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup這兩個變量與性能無關(guān)，不過如果你偶爾重啟mysql服務(wù)器(如生效配置)，那么就有關(guān)。當(dāng)兩個都激活時，MySQL緩沖池的內(nèi)容(更具體地說，是緩存頁)在停止MySQL時存儲到一個文件。當(dāng)你下次啟動MySQL時，它會在后臺啟動一個線程來加載緩沖池的內(nèi)容以提高預(yù)熱速度到3-5倍。
兩件事：
第一，它實(shí)際上沒有在關(guān)閉時復(fù)制緩沖池內(nèi)容到文件，僅僅是復(fù)制表空間ID和頁面ID – 足夠的信息來定位硬盤上的頁面了。然后它就能以大量的順序讀非?？焖俚募虞d那些頁面，而不是需要成千上萬的小隨機(jī)讀。
第二，啟動時是在后臺加載內(nèi)容，因?yàn)镸ySQL不需要等到緩沖池內(nèi)容加載完成再開始接受請求(所以看起來不會有什么影響)。
從MySQL 5.7.7開始，默認(rèn)只有25%的緩沖池頁面在mysql關(guān)閉時存儲到文件，但是你可以控制這個值 – 使用innodb_buffer_pool_dump_pct，建議75-100。
這個特性從MySQL 5.6才開始支持。
13.INNODB_ADAPTIVE_HASH_INDEX_PARTS
如果你運(yùn)行著一個大量SELECT查詢的MySQL服務(wù)器(并且已經(jīng)盡可能優(yōu)化)，那么自適應(yīng)哈希索引將下你的下一個瓶頸。自適應(yīng)哈希索引是InnoDB內(nèi)部維護(hù)的動態(tài)索引，可以提高最常用的查詢模式的性能。這個特性可以重啟服務(wù)器關(guān)閉，不過默認(rèn)下在mysql的所有版本開啟。
這個技術(shù)非常復(fù)雜，在大多數(shù)情況下它會對大多數(shù)類型的查詢直到加速的作用。不過，當(dāng)你有太多的查詢往數(shù)據(jù)庫，在某一個點(diǎn)上它會花過多的時間等待AHI鎖和閂鎖。
如果你的是MySQL 5.7，沒有這個問題 – innodb_adaptive_hash_index_parts默認(rèn)設(shè)置為8，所以自適應(yīng)哈希索引被切割為8個分區(qū)，因?yàn)椴淮嬖谌只コ狻?/p>
不過在mysql 5.7前的版本，沒有AHI分區(qū)數(shù)量的控制。換句話說，有一個全局互斥鎖來保護(hù)AHI，可能導(dǎo)致你的select查詢經(jīng)常撞墻。
所以如果你運(yùn)行的是5.1或5.6，并且有大量的select查詢，最簡單的方案就是切換成同一版本的Percona Server來激活A(yù)HI分區(qū)。
14.QUERY_CACHE_TYPE
如果人認(rèn)為查詢緩存效果很好，肯定應(yīng)該使用它。好吧，有時候是有用的。不過這個只在你在低負(fù)載時有用，特別是在低負(fù)載下大多數(shù)是讀取，小量寫或者沒有。
如果是那樣的情況，設(shè)置query_cache_type=ON和query_cache_size=256M就好了。不過記住不能把256M設(shè)置更高的值了，否則會由于查詢緩存失效時，導(dǎo)致引起嚴(yán)重的服務(wù)器停頓。
如果你的MySQL服務(wù)器高負(fù)載動作，建議設(shè)置query_cache_size=0和query_cache_type=OFF，并重啟服務(wù)器生效。那樣Mysql就會停止在所有的查詢使用查詢緩存互斥鎖。
15.TABLE_OPEN_CACHE_INSTANCES
從MySQL 5.6.6開始，表緩存能分割到多個分區(qū)。
表緩存用來存放目前已打開表的列表，當(dāng)每一個表打開或關(guān)閉互斥體就被鎖定 – 即使這是一個隱式臨時表。使用多個分區(qū)絕對減少了潛在的爭用。
從MySQL 5.7.8開始，table_open_cache_instances=16是默認(rèn)的配置。
歡迎做Java的工程師朋友們私信我資料免費(fèi)獲取免費(fèi)的Java架構(gòu)學(xué)習(xí)資料（里面有高可用、高并發(fā)、高性能及分布式、Jvm性能調(diào)優(yōu)、Spring源碼，MyBatis，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個知識點(diǎn)的架構(gòu)資料）
其中覆蓋了互聯(lián)網(wǎng)的方方面面，期間碰到各種產(chǎn)品各種場景下的各種問題，很值得大家借鑒和學(xué)習(xí)，擴(kuò)展自己的技術(shù)廣度和知識面。
一、熟悉MySQL的運(yùn)行環(huán)境
MySQL數(shù)據(jù)庫是在Linux環(huán)境下運(yùn)行的，建議先熟練Linux系統(tǒng)。選定好架構(gòu)模式之后就可以開始進(jìn)入程序開發(fā)和數(shù)據(jù)處理的環(huán)節(jié)。
二、有扎實(shí)的數(shù)據(jù)庫理論知識
MySQL作為關(guān)系型數(shù)據(jù)庫，在實(shí)際的應(yīng)用中也要學(xué)會分析存儲數(shù)據(jù)的關(guān)系型數(shù)據(jù)結(jié)構(gòu)，關(guān)系操作集合等。只有對這些內(nèi)容有一個明確的認(rèn)識，在設(shè)計(jì)數(shù)據(jù)庫字段、表與表的關(guān)系才能考慮周全，避免出現(xiàn)錯誤。
三、熟練的SQL語言運(yùn)用
任何一種數(shù)據(jù)庫的學(xué)習(xí)，包括MySQL，SQL語句都是位于核心部分的內(nèi)容。需要注意的是SQL語言的使用要力求簡明扼要，能用一個select搞定的問題并不需要寫更多的union。所以在SQL語言的應(yīng)用方面要注意高效。
重慶分公司
重慶分公司
