1、InnoDB存儲(chǔ)引擎
讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛(ài)。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：主機(jī)域名、網(wǎng)站空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、新?lián)峋W(wǎng)站維護(hù)、網(wǎng)站推廣。
Mysql版本=5.5 默認(rèn)的存儲(chǔ)引擎，MySQL推薦使用的存儲(chǔ)引擎。支持事務(wù)，行級(jí)鎖定，外鍵約束。事務(wù)安全型存儲(chǔ)引擎。更加注重?cái)?shù)據(jù)的完整性和安全性。
存儲(chǔ)格式 : 數(shù)據(jù)，索引集中存儲(chǔ)，存儲(chǔ)于同一個(gè)表空間文件中。
InnoDB的行鎖模式及其加鎖方法： InnoDB中有以下兩種類型的行鎖：共享鎖（讀鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行讀取）和 互斥鎖（寫鎖： 允許事務(wù)對(duì)一條行數(shù)據(jù)進(jìn)行刪除或更新）， 對(duì)于update，insert，delete語(yǔ)句，InnoDB會(huì)自動(dòng)給設(shè)計(jì)的數(shù)據(jù)集加互斥鎖，對(duì)于普通的select語(yǔ)句，InnoDB不會(huì)加任何鎖。
InnoDB行鎖的實(shí)現(xiàn)方式： InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，如果沒(méi)有索引，InnoDB將通過(guò)隱藏的聚簇索引來(lái)對(duì)記錄加鎖。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：如果不通過(guò)索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，實(shí)際效果跟表鎖一樣。
（1）在不通過(guò)索引條件查詢時(shí)，InnoDB會(huì)鎖定表中的所有記錄。
（2）Mysql的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖，所以雖然是訪問(wèn)不同行的記錄，但是如果使用相同的索引鍵，是會(huì)出現(xiàn)沖突的。
（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，但都是通過(guò)行鎖來(lái)對(duì)數(shù)據(jù)加鎖。
優(yōu)點(diǎn)：
1、支持事務(wù)處理、ACID事務(wù)特性；
2、實(shí)現(xiàn)了SQL標(biāo)準(zhǔn)的四種隔離級(jí)別（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔離性（Isolation ）和持續(xù)性（Durability ））；
3、支持行級(jí)鎖和外鍵約束；
4、可以利用事務(wù)日志進(jìn)行數(shù)據(jù)恢復(fù)。
5、鎖級(jí)別為行鎖，行鎖優(yōu)點(diǎn)是適用于高并發(fā)的頻繁表修改，高并發(fā)是性能優(yōu)于 MyISAM。缺點(diǎn)是系統(tǒng)消耗較大。
6、索引不僅緩存自身，也緩存數(shù)據(jù)，相比 MyISAM 需要更大的內(nèi)存。
缺點(diǎn)：
因?yàn)樗鼪](méi)有保存表的行數(shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)會(huì)掃描全表。
使用場(chǎng)景：
(1)可靠性要求比較高，或者要求事務(wù)；(2)表更新和查詢都相當(dāng)?shù)念l繁，并且表鎖定的機(jī)會(huì)比較大的情況。
2、 MyISAM存儲(chǔ)引擎
MySQL= 5.5 MySQL默認(rèn)的存儲(chǔ)引擎。ISAM：Indexed Sequential Access Method(索引順序存取方法)的縮寫，是一種文件系統(tǒng)。擅長(zhǎng)與處理，高速讀與寫。
功能：
（1）支持?jǐn)?shù)據(jù)壓縮存儲(chǔ),但壓縮后的表變成了只讀表，不可寫；如果需要更新數(shù)據(jù)，則需要先解壓后更新。
（2）支持表級(jí)鎖定，不支持高并發(fā)；
（3）支持并發(fā)插入。寫操作中的插入操作，不會(huì)阻塞讀操作（其他操作）；
優(yōu)點(diǎn)：
1.高性能讀取；
2.因?yàn)樗４媪吮淼男袛?shù)，當(dāng)使用COUNT統(tǒng)計(jì)時(shí)不會(huì)掃描全表；
缺點(diǎn)：
1、鎖級(jí)別為表鎖，表鎖優(yōu)點(diǎn)是開銷小，加鎖快；缺點(diǎn)是鎖粒度大，發(fā)生鎖沖動(dòng)概率較高，容納并發(fā)能力低，這個(gè)引擎適合查詢?yōu)橹鞯臉I(yè)務(wù)。
2、此引擎不支持事務(wù)，也不支持外鍵。
3、INSERT和UPDATE操作需要鎖定整個(gè)表；
使用場(chǎng)景：
(1)做很多count 的計(jì)算；(2)插入不頻繁，查詢非常頻繁；(3)沒(méi)有事務(wù)。
InnoDB和MyISAM一些細(xì)節(jié)上的差別：
1、InnoDB不支持FULLTEXT類型的索引，MySQL5.6之后已經(jīng)支持（實(shí)驗(yàn)性）。
2、InnoDB中不保存表的 具體行數(shù)，也就是說(shuō)，執(zhí)行select count() from table時(shí)，InnoDB要掃描一遍整個(gè)表來(lái)計(jì)算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語(yǔ)句包含 where條件時(shí)，兩種表的操作是一樣的。
3、對(duì)于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯(lián)合索引。
4、DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表，而是一行一行的刪除。
5、LOAD TABLE FROM MASTER操作對(duì)InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導(dǎo)入數(shù)據(jù)后再改成InnoDB表，但是對(duì)于使用的額外的InnoDB特性（例如外鍵）的表不適用。
6、另外，InnoDB表的行鎖也不是絕對(duì)的，如果在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)MySQL不能確定要掃描的范圍，InnoDB表同樣會(huì)鎖全表。
1．索引概述
利用關(guān)鍵字，就是記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分），建立與記錄位置的對(duì)應(yīng)關(guān)系，就是索引。索引的關(guān)鍵字一定是排序的。索引本質(zhì)上是表字段的有序子集，它是提高查詢速度最有效的方法。一個(gè)沒(méi)有建立任何索引的表，就相當(dāng)于一本沒(méi)有目錄的書，在每次查詢時(shí)就會(huì)進(jìn)行全表掃描，這樣會(huì)導(dǎo)致查詢效率極低、速度也極慢。如果建立索引，那么就好比一本添加的目錄，通過(guò)目錄的指引，迅速翻閱到指定的章節(jié)，提升的查詢性能，節(jié)約了查詢資源。
2．索引種類
從索引的定義方式和用途中來(lái)看：主鍵索引，唯一索引，普通索引，全文索引。
無(wú)論任何類型，都是通過(guò)建立關(guān)鍵字與位置的對(duì)應(yīng)關(guān)系來(lái)實(shí)現(xiàn)的。索引是通過(guò)關(guān)鍵字找對(duì)應(yīng)的記錄的地址。
以上類型的差異：對(duì)索引關(guān)鍵字的要求不同。
關(guān)鍵字：記錄的部分?jǐn)?shù)據(jù)（某個(gè)字段，某些字段，某個(gè)字段的一部分）。
普通索引,index：對(duì)關(guān)鍵字沒(méi)有要求。
唯一索引,unique index：要求關(guān)鍵字不能重復(fù)。同時(shí)增加唯一約束。
主鍵索引,primary key：要求關(guān)鍵字不能重復(fù)，也不能為NULL。同時(shí)增加主鍵約束。
全文索引,fulltext key：關(guān)鍵字的來(lái)源不是所有字段的數(shù)據(jù)，而是從字段中提取的特別關(guān)鍵詞。
PS：這里主鍵索引和唯一索引的區(qū)別在于：主鍵索引不能為空值，唯一索引允許空值；主鍵索引在一張表內(nèi)只能創(chuàng)建一個(gè)，唯一索引可以創(chuàng)建多個(gè)。主鍵索引肯定是唯一索引，但唯一索引不一定是主鍵索引。
3.索引原則
如果索引不遵循使用原則，則可能導(dǎo)致索引無(wú)效。
（1）列獨(dú)立
如果需要某個(gè)字段上使用索引，則需要在字段參與的表達(dá)中，保證字段獨(dú)立在一側(cè)。否則索引不會(huì)用到索引, 例如這條sql就不會(huì)用到索引:select * from A where id+1=10;
（2）左原則
Like：匹配模式必須要左邊確定不能以通配符開頭。例如:select * from A where name like '%小明%' ，不會(huì)用到索引，而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 '%小明%' 這三種情況都會(huì)用到name的索引;
復(fù)合索引：一個(gè)索引關(guān)聯(lián)多個(gè)字段，僅僅針對(duì)左邊字段有效果，添加復(fù)合索引時(shí)，第一個(gè)字段很重要，只有包含第一個(gè)字段作為查詢條件的情況才會(huì)使用復(fù)合索引(必須用到建索引時(shí)選擇的第一個(gè)字段作為查詢條件,其他字段的順序無(wú)關(guān)),而且查詢條件只能出現(xiàn)and拼接,不能用or,否則則無(wú)法使用索引.
（3）OR的使用
必須要保證 OR 兩端的條件都存在可以用的索引，該查詢才可以使用索引。
（4）MySQL智能選擇
即使?jié)M足了上面說(shuō)原則，MySQL也能棄用索引，例如：select * from A where id 1;這里棄用索引的主要原因：查詢即使使用索引，會(huì)導(dǎo)致出現(xiàn)大量的隨機(jī)IO，相對(duì)于從數(shù)據(jù)記錄的第一條遍歷到最后一條的順序IO開銷，還要大。
4.索引的使用場(chǎng)景
（1）索引檢索：檢索數(shù)據(jù)時(shí)使用索引。
（2）索引排序: 如果order by 排序需要的字段上存在索引，則可能使用到索引。
（3）索引覆蓋: 索引擁有的關(guān)鍵字內(nèi)容，覆蓋了查詢所需要的全部數(shù)據(jù)，此時(shí)，就不需要在數(shù)據(jù)區(qū)獲取數(shù)據(jù)，僅僅在索引區(qū)即可。覆蓋就是直接在索引區(qū)獲取內(nèi)容，而不需要在數(shù)據(jù)區(qū)獲取。例如: select name from A where name like '小明%';
建立索引索引時(shí)，不能僅僅考慮where檢索，同時(shí)考慮其他的使用場(chǎng)景。（在所有的where字段上增加索引，就是不合理的）
5.前綴索引
前綴索引是建立索引關(guān)鍵字一種方案。通常會(huì)使用字段的整體作為索引關(guān)鍵字。有時(shí)，即使使用字段前部分?jǐn)?shù)據(jù)，也可以去識(shí)別某些記錄。就比如一個(gè)班級(jí)里，我要找王xx，假如姓王的只有1個(gè)人，那么就可以建一個(gè)關(guān)鍵字為'王'的前綴索引。語(yǔ)法：Index `index_name` (`index_field`(N))使用index_name前N個(gè)字符建立的索引。
6.索引失效
（1） 應(yīng)盡量避免在 where 子句中使用 != 或 操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描；
（2） 應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，如果一個(gè)字段有索引，一個(gè)字段沒(méi)有索引，將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；
（3） 應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；
（4）應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如select id from t where num/2 = 100；
（5） 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行類型轉(zhuǎn)換，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描； 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來(lái),如select id from t where id = 1；如果id字段在表設(shè)計(jì)中是varchar類型，那么即使id列上存的是數(shù)字，在查詢時(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í)行時(shí)內(nèi)部流程是這樣的：
1. 先從 t1（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）中取出一行數(shù)據(jù) X；
2. 從 X 中取出關(guān)聯(lián)字段 a 值，去 t2 中進(jìn)行查找，滿足條件的行取出；
3. 重復(fù)1、2步驟，直到表 t1 最后一行循環(huán)結(jié)束。
這就是一個(gè)嵌套循環(huán)的過(guò)程，如果在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)可以使用索引，總的對(duì)比計(jì)算次數(shù)等于驅(qū)動(dòng)表滿足 where 條件的行數(shù)。假設(shè)這里 t1、t2都是1萬(wàn)行，則只需要 1萬(wàn)次計(jì)算，這里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循環(huán)聯(lián)接）。
如果 t1、t2 的 a 字段都沒(méi)有索引，還按照上述的嵌套循環(huán)流程查找數(shù)據(jù)呢？每次在被驅(qū)動(dòng)表上查找數(shù)據(jù)時(shí)都是一次全表掃描，要做1萬(wàn)次全表掃描，掃描行數(shù)等于 1萬(wàn)+1萬(wàn)*1萬(wàn)，這個(gè)效率很低，如果表行數(shù)更多，掃描行數(shù)動(dòng)輒幾百億，所以優(yōu)化器肯定不會(huì)使用這樣的算法，而是選擇 BNL 算法；
BNLJ（Block Nested Loop Join）塊嵌套循環(huán)算法；
1. 把 t1 表（假設(shè)這里 t1 被選為驅(qū)動(dòng)表）滿足條件的數(shù)據(jù)全部取出放到線程的 join buffer 中；
2. 每次取 t2 表一行數(shù)據(jù)，去 joinbuffer 中進(jìn)行查找，滿足條件的行取出，直到表 t2 最后一行循環(huán)結(jié)束。
這個(gè)算法下，執(zhí)行計(jì)劃的 Extra 中會(huì)出現(xiàn) Using join buffer(Block Nested Loop)，t1、t2 都做了一次全表掃描，總的掃描行數(shù)等于 1萬(wàn)+1萬(wàn)。但是由于 joinbuffer 維護(hù)的是一個(gè)無(wú)序數(shù)組，每次在 joinbuffer 中查找都要遍歷所有行，總的內(nèi)存計(jì)算次數(shù)等于1萬(wàn)*1萬(wàn)。另外如果 joinbuffer 不夠大放不下驅(qū)動(dòng)表的數(shù)據(jù)，則要分多次執(zhí)行上面的流程，會(huì)導(dǎo)致被驅(qū)動(dòng)表也做多次全表掃描。
BNLJ相對(duì)于NLJ的優(yōu)點(diǎn)在于，驅(qū)動(dòng)層可以先將部分?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條記錄每條與這個(gè)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)連接,或等值連接）：取得兩個(gè)表中存在連接匹配關(guān)系的記錄。
LEFT JOIN（左連接）：取得左表（table1）完全記錄，即是右表（table2）并無(wú)對(duì)應(yīng)匹配記錄。
RIGHT JOIN（右連接）：與 LEFT JOIN 相反，取得右表（table2）完全記錄，即是左表（table1）并無(wú)匹配對(duì)應(yīng)記錄。
注意：mysql不支持Full join,不過(guò)可以通過(guò)UNION 關(guān)鍵字來(lái)合并 LEFT JOIN 與 RIGHT JOIN來(lái)模擬FULL join。
mysql 多表連接查詢方式，因?yàn)閙ysql只支持NLJ算法,所以如果是小表驅(qū)動(dòng)大表則效率更高；反之則效率下降；因此mysql對(duì)內(nèi)連接或等值連接的方式做了一個(gè)優(yōu)化,會(huì)去判斷join表的數(shù)據(jù)行大小,然后取數(shù)據(jù)行小的表為驅(qū)動(dòng)表。
INNER JOIN、JOIN、WHERE等值連接和STRAIGHT_JOIN都能表示內(nèi)連接，那平時(shí)如何選擇呢？一般情況下用INNER JOIN、JOIN或者WHERE等值連接，因?yàn)镸ySQL 會(huì)按照"小表驅(qū)動(dòng)大表的策略"進(jìn)行優(yōu)化。當(dāng)出現(xiàn)需要排序時(shí)，才考慮用STRAIGHT_JOIN指定某張表為驅(qū)動(dòng)表。
兩表JOIN優(yōu)化
a.當(dāng)無(wú)order by條件時(shí)，根據(jù)實(shí)際情況，使用left/right/inner join即可，根據(jù)explain優(yōu)化 ；
b.當(dāng)有order by條件時(shí)，如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解釋語(yǔ)句；
1）如果第一行的驅(qū)動(dòng)表為a，則效率會(huì)非常高，無(wú)需優(yōu)化；
2）否則，因?yàn)橹荒軐?duì)驅(qū)動(dòng)表字段直接排序的緣故，會(huì)出現(xiàn)using temporary，所以此時(shí)需要使用STRAIGHT_JOIN明確a為驅(qū)動(dòng)表，來(lái)達(dá)到使用a.col上index的優(yōu)化目的；或者使用left join且Where條件中不含b的過(guò)濾條件，此時(shí)的結(jié)果集為a的全集，而STRAIGHT_JOIN為inner join且使用a作為驅(qū)動(dòng)表。注：使用STRAIGHT_JOIN雖然不會(huì)using temporary，但也不是一定就能提高效率，如果a表數(shù)據(jù)遠(yuǎn)遠(yuǎn)超過(guò)b表，那么有可能使用STRAIGHT_JOIN時(shí)比原來(lái)的sql效率更低，所以怎么使用STRAIGHT_JOIN，還是要視情況而定。
在使用left join（或right join）時(shí)，應(yīng)該清楚的知道以下幾點(diǎn)：
(1). on與 where的執(zhí)行順序
ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來(lái)決定如何從 B 表中檢索數(shù)據(jù)行。如果 B 表中沒(méi)有任何一行數(shù)據(jù)匹配 ON 的條件,將會(huì)額外生成一行所有列為 NULL 的數(shù)據(jù),在匹配階段 WHERE 子句的條件都不會(huì)被使用。僅在匹配階段完成以后，WHERE 子句條件才會(huì)被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過(guò)濾。
所以我們要注意：在使用Left (right) join的時(shí)候，一定要在先給出盡可能多的匹配滿足條件，減少Where的執(zhí)行。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的數(shù)據(jù)不滿足ON后面的條件,也會(huì)在結(jié)果集拼接一條為NULL的數(shù)據(jù)行,但WHERE后面的條件不一樣,右表不滿足WHERE的條件,左表關(guān)聯(lián)的數(shù)據(jù)也會(huì)被過(guò)濾掉。
(3).盡量避免子查詢，而用join
往往性能這玩意兒，更多時(shí)候體現(xiàn)在數(shù)據(jù)量比較大的時(shí)候，此時(shí)，我們應(yīng)該避免復(fù)雜的子查詢。
（1）in 和 not in 要慎用，如：select id from t where num in(1,2,3)對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in：select id from t where num between 1 and 3很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：select num from a where num in(select num from b)用下面的語(yǔ)句替換：select num from a where exists(select 1 from b where num=a.num)
（2）Update 語(yǔ)句，如果只更改1、2個(gè)字段，不要Update全部字段，否則頻繁調(diào)用會(huì)引起明顯的性能消耗，同時(shí)帶來(lái)大量日志。
（3）join語(yǔ)句,MySQL里面的join是用小表去驅(qū)動(dòng)大表,而由于MySQL join實(shí)現(xiàn)的原理就是做循環(huán)，比如left join就是對(duì)左邊的數(shù)據(jù)進(jìn)行循環(huán)去驅(qū)動(dòng)右邊的表,左邊有m條記錄匹配,右邊有n條記錄那么就是做m次循環(huán),每次掃描n行數(shù)據(jù),總掃面行數(shù)是m*n行數(shù)據(jù)。左邊返回的結(jié)果集的大小就決定了循環(huán)的次數(shù),故單純的用小表去驅(qū)動(dòng)大表不一定的正確的,小表的結(jié)果集可能也大于大表的結(jié)果集,所以寫join的時(shí)候盡可能的先估計(jì)兩張表的可能結(jié)果集,用小結(jié)果集去驅(qū)動(dòng)大結(jié)果集.值得注意的是在使用left/right join的時(shí)候,從表的條件應(yīng)寫在on之后,主表應(yīng)寫在where之后.否則MySQL會(huì)當(dāng)作普通的連表查詢；
（4）select count(*) from table；這樣不帶任何條件的count會(huì)引起全表掃描，并且沒(méi)有任何業(yè)務(wù)意義，是一定要杜絕的；
（5）select * from t 這種語(yǔ)句要盡量避免，使用具體的字段代替*，更有實(shí)際意義，需要什么字段就返回什么字段；
（6）數(shù)據(jù)量大的情況下，limit要慎用，因?yàn)槭褂胠imit m，n方式分頁(yè)時(shí)，mysql每次都是查詢前m+n條，然后舍棄前m條，所以m越大，偏移量越大，性能就越差。比如：select * from A limit 1000000,20這鐘，查詢效率就會(huì)非常低，當(dāng)分頁(yè)的頁(yè)數(shù)大于一定的數(shù)量之后，就可以換種方式來(lái)分頁(yè)：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
《MySQL是怎樣運(yùn)行的：從根兒上理解 MySQL》采用詼諧幽默的表達(dá)方式，對(duì)MySQL的底層運(yùn)行原理進(jìn)行了介紹，內(nèi)容涵蓋了使用MySQL的同學(xué)在求職面試和工作中常見(jiàn)的一些核心概念?？傆?jì)22 章，劃分為4個(gè)部分。第1部分介紹了MySQL入門的一些知識(shí)，比如MySQL的服務(wù)器程序和客戶端程序有哪些、MySQL的啟動(dòng)選項(xiàng)和系統(tǒng)變量，以及使用的字符集等。第2部分是本書后續(xù)章節(jié)的基礎(chǔ)，介紹了MySQL的一些基礎(chǔ)知識(shí)，比如記錄、頁(yè)面、索引、表空間的結(jié)構(gòu)和用法等。第3部分則與大家在工作中經(jīng)常遇到的查詢優(yōu)化問(wèn)題緊密相關(guān)，介紹了單表查詢、連接查詢的執(zhí)行原理，MySQL基于成本和規(guī)則的優(yōu)化具體指什么，并詳細(xì)分析了Explain語(yǔ)句的執(zhí)行結(jié)果。第4部分則是與MySQL中的事務(wù)和鎖相關(guān)，介紹了事務(wù)概念的來(lái)源，MySQL是如何實(shí)現(xiàn)事務(wù)的，包括redo日志、undo日志、MVCC、各種鎖的細(xì)節(jié)等。
盡管《MySQL是怎樣運(yùn)行的：從根兒上理解 MySQL》在寫作時(shí)參考的MySQL源代碼版本是5.7.22，但是大部分內(nèi)容與具體的版本號(hào)并沒(méi)有多大關(guān)系。無(wú)論是很早之前就已身居MySQL專家的人員，還是希望進(jìn)一步提升技能的DBA，甚至是三五年后才會(huì)入行的“萌新”，本書都是他們徹底了解MySQL運(yùn)行原理的優(yōu)秀書
1.一條查詢語(yǔ)句如何執(zhí)行？
2.一條更新語(yǔ)句如何執(zhí)行？
3.innodb的redolog是什么？
4.什么是寫緩沖
5.寫緩沖一定好嗎？
6.什么情況會(huì)引發(fā)刷臟頁(yè)
關(guān)于一條mysql查詢語(yǔ)句在mysql中的執(zhí)行流程
如select name from test where id=10;
1.連接器---先與mysql服務(wù)端連接器建立連接，若查詢緩存命中則直接返回 （查詢緩存的弊端：查詢緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。）
2.分析器---詞法分析告訴服務(wù)端你要干什么（我要找 test表中id為10的名字） ( 其中sql語(yǔ)法錯(cuò)誤在這塊暴露 )
3.優(yōu)化器---服務(wù)端會(huì)思考該怎么執(zhí)行最優(yōu)(索引的選擇)
4.執(zhí)行器---檢查用戶對(duì)庫(kù)對(duì)表的權(quán)限
5.存儲(chǔ)引擎--存儲(chǔ)數(shù)據(jù)，提供讀寫接口
以u(píng)pdate a set name=1 where id=1;
主要區(qū)別在于在查詢到數(shù)據(jù)之后(select name from a where id=1)，如果是innodb引擎它會(huì)進(jìn)行日志的兩階段提交:
1.開啟事務(wù)，寫入redolog（innodb引擎特有），并更新內(nèi)存
3.寫入binlog，提交事務(wù)，commit
我們知道m(xù)ysql數(shù)據(jù)存儲(chǔ)包含內(nèi)存與磁盤兩個(gè)部分，innodb是按數(shù)據(jù)頁(yè)(通常為16k)從磁盤讀取到內(nèi)存中的(剩余操作在內(nèi)存中執(zhí)行)，當(dāng)要更新數(shù)據(jù)時(shí)，若目標(biāo)數(shù)據(jù)的數(shù)據(jù)頁(yè)剛好在內(nèi)存中，則直接更新。不在呢？
將這個(gè)更新操作(也可能是插入) 緩存在change buffer中 (redolog也會(huì)記錄這個(gè)change buffer操作)等到下一次查詢要用到這些數(shù)據(jù)時(shí)，再執(zhí)行這些操作，改變數(shù)據(jù)(稱為合并操作記錄稱為merge)。
innodb_change_buffer_max_size
innodb_change_buffering
先介紹兩個(gè)概念
因?yàn)閞edolog是環(huán)形日志，當(dāng)redolog寫滿時(shí)，就需要“擦掉”開頭的一部分?jǐn)?shù)據(jù)來(lái)達(dá)到循環(huán)寫，這里的擦掉指，指將redolog日志的checkpoint位置從 CP推進(jìn)到CP‘ ，同時(shí)將兩點(diǎn)之間的臟頁(yè)刷到磁盤上(flush操作)，此時(shí)系統(tǒng)要停止所有的更新操作（防止更新操作丟失）
1.系統(tǒng)內(nèi)存不足。當(dāng)要讀取新的內(nèi)存頁(yè)時(shí)就要淘汰一些數(shù)據(jù)頁(yè)，如果淘汰的正好是臟頁(yè)，就要執(zhí)行一次flush操作
2.Mysql認(rèn)為系統(tǒng)處于“空閑狀態(tài)”
3.正常關(guān)閉Mysql
上述后兩者場(chǎng)景(系統(tǒng)空閑和正常關(guān)閉)對(duì)于性能都沒(méi)太大影響。
當(dāng)為第一種redolog寫滿時(shí)，系統(tǒng)無(wú)法執(zhí)行更新操作，所有操作都會(huì)堵塞
當(dāng)為第二種內(nèi)存不夠用時(shí)，如果淘汰臟頁(yè)太多，影響mysql響應(yīng)時(shí)間
后兩者刷臟頁(yè)會(huì)影響性能，所以Mysql需要有刷臟頁(yè)控制策略，可以從以下幾個(gè)設(shè)置項(xiàng)考慮
1.設(shè)置innodb_io_capacity告訴innodb所在主機(jī)的IO能力
mysql數(shù)據(jù)庫(kù)的優(yōu)點(diǎn)如下：
1、速度:運(yùn)行速度快。
2、價(jià)格:MySQL對(duì)多數(shù)個(gè)人來(lái)說(shuō)是免費(fèi)的。
3、容易使用;與其他大型數(shù)據(jù)庫(kù)的設(shè)置和管理相比，其復(fù)雜程度較低，容易學(xué)習(xí)。
4、可移植性:能夠工作在眾多不同的系統(tǒng)平臺(tái)上，例如:Windows、Linux、Unix、MacOS等。
5、豐富的接口:提供了用于C、C++、Eiffel、Java、Perl、PHP、Python、Rudy和TCL等語(yǔ)言的APl。6、支持查詢語(yǔ)言:MySQL可以利用標(biāo)準(zhǔn)SQL語(yǔ)法和支持ODBC(開放式數(shù)據(jù)庫(kù)連接）的應(yīng)用程序。
7、安全性和連接性;十分靈活和安全的權(quán)限和密碼系統(tǒng)，允許主機(jī)驗(yàn)證。連接到服務(wù)器時(shí)，所有的密碼均采用加密形式，從而保證了密碼安全。并且由于MySQL時(shí)網(wǎng)絡(luò)化的，因此可以在因特網(wǎng)網(wǎng)上的任何地方訪問(wèn)，提高數(shù)據(jù)共享效率。
重慶分公司
重慶分公司
