一弦讽、存儲(chǔ)引擎

1侣颂、SQL語(yǔ)句執(zhí)行流程
MySQL大體上可分為Server層和存儲(chǔ)引擎層兩部分胯甩。
Server層：

• 連接器：TCP握手后服務(wù)器來(lái)驗(yàn)證登陸用戶身份啃沪，A用戶創(chuàng)建連接后粘拾，管理員對(duì)A用戶權(quán)限修改了也不會(huì)影響到已經(jīng)創(chuàng)建的鏈接權(quán)限，必須重新登陸创千。
• 查詢緩存：查詢后的結(jié)果存儲(chǔ)位置缰雇，MySQL8.0版本以后已經(jīng)取消，因?yàn)椴樵兙彺媸l繁追驴，得不償失械哟。
• 分析器：根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè)SQL語(yǔ)句是否滿足MySQL語(yǔ)法殿雪。
• 優(yōu)化器：多種執(zhí)行策略可實(shí)現(xiàn)目標(biāo)暇咆，系統(tǒng)自動(dòng)選擇最優(yōu)進(jìn)行執(zhí)行。
• 執(zhí)行器：判斷是否有權(quán)限，將最終任務(wù)提交到存儲(chǔ)引擎爸业。

存儲(chǔ)引擎：

• Innodb（默認(rèn)）：innodb里面完整數(shù)據(jù)都在主鍵索引的葉子節(jié)點(diǎn)上面其骄。主鍵索引：存儲(chǔ)索引和數(shù)據(jù)；輔助索引：存儲(chǔ)索引和主鍵值
  • （1）支持事務(wù)（redo log和undo log）扯旷；
  • （2）行級(jí)鎖年栓；支持外鍵薄霜；
  • （3）支持崩潰后的安全恢復(fù)（bin log）某抓；
  • （4）支持MVCC；
  • （5）不保存表的具體行數(shù)惰瓜，執(zhí)行select count(*) from table時(shí)需要全表掃描否副；
• MyISAM：有兩個(gè)存儲(chǔ)文件，data和index分開崎坊；主鍵索引和輔助索引的方式相同备禀，需要再index文件上面拿到數(shù)據(jù)的磁盤地址
  • （1）不支持事務(wù)；
  • （2）默認(rèn)表級(jí)鎖;不支持外鍵奈揍；
  • （3）不支持崩潰后的安全恢復(fù)曲尸；
  • （4）支持MVCC；
  • （5）MyISAM用一個(gè)變量保存了整個(gè)表的行數(shù)男翰，執(zhí)行select count(*) from table時(shí)只需要讀出該變量即可另患，速度很快；
• Memory：也叫 HEAP（堆內(nèi)存）蛾绎，使用存在內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表昆箕。
  • 不允許使用BLOB或TEXT字段
  • 只能使用比較運(yùn)算符=，<租冠，>鹏倘，=>，= <
  • HEAP表不支持AUTO_INCREMENT
  • 索引不可為NULL

2顽爹、一張表里面有ID自增主鍵纤泵，當(dāng)insert了17條記錄之后，刪除了第15,16,17條記錄镜粤，再把mysql重啟捏题，再insert一條記錄，這條記錄的ID是18還是15 繁仁？

一般情況下涉馅，我們創(chuàng)建的表的類型是InnoDB归园，如果新增一條記錄（不重啟mysql的情況下）黄虱，這條記錄的id是18；但是如果重啟（文中提到的）MySQL的話庸诱，這條記錄的ID是15捻浦。因?yàn)镮nnoDB表只把自增主鍵的最大ID記錄到內(nèi)存中晤揣，所以重啟數(shù)據(jù)庫(kù)或者對(duì)表OPTIMIZE操作，都會(huì)使最大ID丟失朱灿。
但是昧识，如果我們使用表的類型是MylSAM，那么這條記錄的ID就是18盗扒。因?yàn)镸ylSAM表會(huì)把自增主鍵的最大ID記錄到數(shù)據(jù)文件里面跪楞，重啟MYSQL后，自增主鍵的最大ID也不會(huì)丟失侣灶。

二甸祭、索引

1、索引為什么使用B+樹而不是B樹褥影？

• IO次數(shù)少：B+樹的中間結(jié)點(diǎn)只存放索引池户，數(shù)據(jù)都存在葉結(jié)點(diǎn)中，因此中間結(jié)點(diǎn)可以存更多的數(shù)據(jù)凡怎，讓索引樹更加矮胖校焦；
• 范圍查詢效率更高：B樹需要中序遍歷整個(gè)樹，只B+樹需要遍歷葉結(jié)點(diǎn)中的鏈表统倒；
• 查詢效率更加穩(wěn)定：每次查詢都需要從根結(jié)點(diǎn)到葉結(jié)點(diǎn)寨典，路徑長(zhǎng)度相同，所以每次查詢的效率都差不多房匆；

2凝赛、索引的幾個(gè)概念：

• 聚簇索引：B+樹的非葉子節(jié)點(diǎn)只存儲(chǔ)索引，而葉子節(jié)點(diǎn)存儲(chǔ)索引和數(shù)據(jù)坛缕，這種索引和數(shù)據(jù)在一起存儲(chǔ)的索引方式叫做聚簇索引墓猎。一張表只能有一個(gè)聚簇索引。
  （1）如果表定義了主鍵赚楚，則之間就是聚集索引毙沾；
  （2）如果表沒有定義主鍵，則第一個(gè)非空唯一索引是聚集索引宠页；
  （3）如果沒有這樣的非空索引左胞，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的row_id作為聚集索引；
• 非聚簇索引：非聚簇索引(二級(jí)索引)保存的是主鍵值举户，這一點(diǎn)和myisam保存的是數(shù)據(jù)地址是不同的烤宙。
• 覆蓋索引：覆蓋索引指的是在一次查詢中，如果一個(gè)索引包含或者說(shuō)覆蓋所有需要查詢的字段的值俭嘁，我們就稱之為覆蓋索引躺枕，而不再需要回表查詢。
• 回表：先通過(guò)普通索引的值定位聚簇索引值，再通過(guò)聚簇索引的值定位行數(shù)據(jù)拐云，去取普通索引中未能提供的數(shù)據(jù)罢猪，即為回表。
  explain select * from user where age=1; //查詢的name無(wú)法從索引數(shù)據(jù)獲取 （回表）
  explain select id,age from user where age=1; //可以直接從索引獲炔娲瘛（覆蓋索引）
• 聯(lián)合索引：相對(duì)單列索引膳帕，組合索引是用多個(gè)列組合構(gòu)建的索引。
• 最左匹配原則：對(duì)多個(gè)字段同時(shí)建立的組合索引(有順序薇缅，ABC危彩，ACB是完全不同的兩種聯(lián)合索引) 以聯(lián)合索引(a,b,c)為例，建立這樣的索引相當(dāng)于建立了索引a泳桦、ab恬砂、abc三個(gè)索引。另外組合索引實(shí)際還是一個(gè)索引蓬痒，并非真的創(chuàng)建了多個(gè)索引泻骤，只是產(chǎn)生的效果等價(jià)于產(chǎn)生多個(gè)索引。

3梧奢、索引的缺點(diǎn)：

• （1）創(chuàng)建跟維護(hù)索引都需要消耗時(shí)間
• （2）創(chuàng)建索引時(shí)狱掂，需要對(duì)表加鎖，在鎖表的同時(shí)亲轨，可能會(huì)影響到其他的數(shù)據(jù)操作
• （3）索引文件會(huì)占用磁盤空間
• （4）當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行CRUD的時(shí)趋惨，也會(huì)觸發(fā)索引的維護(hù)，而維護(hù)索引需要時(shí)間惦蚊，可能會(huì)降低數(shù)據(jù)操作性能

4器虾、索引設(shè)計(jì)的原則：

• （1）頻繁增刪改的列不要建立索引
• （2）有大量重復(fù)的列不要建立索引
• （3）數(shù)據(jù)量小的表不要建立索引

5、索引的失效場(chǎng)景：

• （1）模糊搜索：左模糊或全模糊都會(huì)導(dǎo)致索引失效蹦锋，比如'%a'和'%a%'兆沙。但是右模糊是可以利用索引的，比如'a%' 莉掂。
• （2）隱式類型轉(zhuǎn)換：比如select * from t where name = xxx , name是字符串類型葛圃，但是沒有加引號(hào)，所以是由MySQL隱式轉(zhuǎn)換的憎妙，所以會(huì)讓索引失效
• （3）當(dāng)語(yǔ)句中帶有or的時(shí)候：比如select * from t where name=‘sw’ or age=14
• （4）不符合聯(lián)合索引的最左前綴匹配：(A,B,C)的聯(lián)合索引库正，你只where了C或B或只有B,C
• （5）如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引。例如
  ① 某些范圍查詢 select * from table_name where key>1 and key<90
  ② 或者數(shù)據(jù)量極少的表

6厘唾、InnoDB引擎的行鎖是通過(guò)加在什么上實(shí)現(xiàn)的褥符？

答：基于索引；

7抚垃、使用like 'a%' 喷楣、like'%a'趟大、like'%a%'查詢時(shí)是否會(huì)使用索引

答：'a%'會(huì)，其他兩個(gè)不會(huì)抡蛙；

8、為什么用自增列作為主鍵魂迄？

各條數(shù)據(jù)記錄按主鍵順序存放于主索引的葉子節(jié)點(diǎn)上（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）粗截，每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置捣炬，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16）熊昌，則開辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）。
如果表使用自增主鍵湿酸，那么每次插入新的記錄婿屹，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁(yè)寫滿推溃，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)昂利。
如果使用非自增主鍵，由于每次插入主鍵的值近似于隨機(jī)铁坎，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置蜂奸。此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉硬萍，此時(shí)又要從磁盤上讀回來(lái)扩所，這增加了很多開銷。同時(shí)頻繁的移動(dòng)朴乖、分頁(yè)操作造成了大量的碎片祖屏，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面买羞。

三袁勺、事務(wù)

1、說(shuō)一下事務(wù)的基本特性和隔離級(jí)別畜普？

基本特性：ACID(原子性魁兼、一致性焕妙、隔離性寄啼、持久性)

• 原子性：原子性指的是一個(gè)事務(wù)中的操作要么全部成功，要么全部失敗蠢挡。
• 一致性：一致性指的是數(shù)據(jù)庫(kù)總是從一個(gè)一致性的狀態(tài)轉(zhuǎn)換到另外一個(gè)一致性的狀態(tài)儒鹿。比如A轉(zhuǎn)賬給B100塊錢化撕，假設(shè)中間sql執(zhí)行過(guò)程中系統(tǒng)崩潰A也不會(huì)損失100塊，因?yàn)槭聞?wù)沒有提交约炎，修改也就不會(huì)保存到數(shù)據(jù)庫(kù)植阴。
• 隔離性：隔離性指的是一個(gè)事務(wù)的修改在最終提交前蟹瘾，對(duì)其他事務(wù)是不可見的。
• 持久性：持久性指的是一旦事務(wù)提交掠手，所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中憾朴。

隔離級(jí)別：而隔離性有4個(gè)隔離級(jí)別，分別是：

• 讀未提交：read uncommit 喷鸽，可能會(huì)讀到其他事務(wù)未提交的數(shù)據(jù)众雷，也叫做臟讀。
• 讀已提交：read commit 做祝，兩次讀取結(jié)果不一致砾省，叫做不可重復(fù)讀。（不可重復(fù)讀現(xiàn)象指的是混槐，在一個(gè)事務(wù)內(nèi)编兄，連續(xù)兩次查詢同一條數(shù)據(jù)，查到的結(jié)果前后不一樣）
• 可重復(fù)讀：repeatable read 声登，這是mysql的默認(rèn)級(jí)別狠鸳，就是每次讀取結(jié)果都一樣，但是有可能產(chǎn)生幻讀悯嗓。
• 串行化：serializable 碰煌，一般是不會(huì)使用的，他會(huì)給每一行讀取的數(shù)據(jù)加鎖绅作，會(huì)導(dǎo)致大量超時(shí)和鎖競(jìng)爭(zhēng)的問題芦圾。

2、那ACID靠什么保證的呢俄认？

• 原子性由undo log日志保證个少，它記錄了需要回滾的日志信息，事務(wù)回滾時(shí)撤銷已經(jīng)執(zhí)行成功的sql
• 一致性一般由代碼層面來(lái)保證
• 隔離性由MVCC來(lái)保證
• 持久性由內(nèi)存+redo log來(lái)保證眯杏，mysql修改數(shù)據(jù)同時(shí)在內(nèi)存和redo log記錄這次操作夜焦，事務(wù)提交的時(shí)候通過(guò)redo log刷盤，宕機(jī)的時(shí)候可以從redo log恢復(fù)

3岂贩、那你說(shuō)說(shuō)什么是幻讀茫经？

幻讀指當(dāng)用戶讀取某一范圍的數(shù)據(jù)時(shí)，另一個(gè)事務(wù)又在該范圍內(nèi)插入了新行萎津，當(dāng)用戶再讀取該范圍的數(shù)據(jù)行時(shí)卸伞，會(huì)發(fā)現(xiàn)有新的幻影行。InnoDB存儲(chǔ)引擎通過(guò)多版本并發(fā)控制（MVCC）機(jī)制解決了該問題锉屈。注：其實(shí)多版本只是解決不可重復(fù)讀問題荤傲，而加上間隙鎖（也就是這里所謂的并發(fā)控制）才解決了幻讀問題。

4颈渊、什么是MVCC?

MVCC叫做多版本并發(fā)控制遂黍，實(shí)際上就是保存了數(shù)據(jù)在某個(gè)時(shí)間節(jié)點(diǎn)的快照终佛。我們每行數(shù)實(shí)際上隱藏了兩列，創(chuàng)建時(shí)間版本號(hào)雾家，過(guò)期(刪除)時(shí)間版本號(hào)铃彰，每開始一個(gè)新的事務(wù)，版本號(hào)都會(huì)自動(dòng)遞增芯咧。

在MVCC并發(fā)控制中牙捉，讀操作可以分成兩類：快照讀 (snapshot read)與當(dāng)前讀 (current read)』５常快照讀鹃共，讀取的是記錄的可見版本 (有可能是歷史版本)鬼佣，不用加鎖驶拱。當(dāng)前讀，讀取的是記錄的最新版本晶衷，并且蓝纲，當(dāng)前讀返回的記錄，都會(huì)加上鎖晌纫，保證其他事務(wù)不會(huì)再并發(fā)修改這條記錄税迷。

• 快照讀：簡(jiǎn)單的select操作，屬于快照讀锹漱，不加鎖箭养。

select * from table where ?

• 當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作哥牍，屬于當(dāng)前讀毕泌，需要加鎖。除了第一條語(yǔ)句嗅辣，對(duì)讀取記錄加S鎖 (共享鎖)外撼泛，其他的操作，都加的是X鎖 (排它鎖)

select * from table where ? lock in share mode
select * from table where ? for update
insert into table values (…)
update table set ? where ?
delete from table where ?

5澡谭、InnoDB的行鎖模式及加鎖方法愿题？

• 共享鎖（s）：又稱讀鎖。允許一個(gè)事務(wù)去讀一行蛙奖，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖潘酗。若事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加上S鎖，則事務(wù)T可以讀A但不能修改A雁仲，其他事務(wù)只能再對(duì)A加S鎖崎脉，而不能加X鎖，直到T釋放A上的S鎖伯顶。這保證了其他事務(wù)可以讀A囚灼，但在T釋放A上的S鎖之前不能對(duì)A做任何修改骆膝。
• 排他鎖（Ｘ）：又稱寫鎖。允許獲取排他鎖的事務(wù)更新數(shù)據(jù)灶体，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖阅签。若事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加上X鎖，事務(wù)T可以讀A也可以修改A蝎抽，其他事務(wù)不能再對(duì)A加任何鎖政钟，直到T釋放A上的鎖。

對(duì)于排他鎖大家的理解可能就有些差別樟结，我當(dāng)初就犯了一個(gè)錯(cuò)誤养交，以為排他鎖鎖住一行數(shù)據(jù)后，其他事務(wù)就不能讀取和修改該行數(shù)據(jù)瓢宦，其實(shí)不是這樣的碎连。排他鎖指的是一個(gè)事務(wù)在一行數(shù)據(jù)加上排他鎖后，其他事務(wù)不能再在其上加其他的鎖驮履。mysql InnoDB引擎默認(rèn)的修改數(shù)據(jù)語(yǔ)句：update,delete,insert都會(huì)自動(dòng)給涉及到的數(shù)據(jù)加上排他鎖鱼辙，select語(yǔ)句默認(rèn)不會(huì)加任何鎖類型，如果加排他鎖可以使用select …for update語(yǔ)句玫镐，加共享鎖可以使用select … lock in share mode語(yǔ)句倒戏。所以加過(guò)排他鎖的數(shù)據(jù)行在其他事務(wù)中是不能修改數(shù)據(jù)的，也不能通過(guò)for update和lock in share mode鎖的方式查詢數(shù)據(jù)恐似，但可以直接通過(guò)select …from…查詢數(shù)據(jù)杜跷，因?yàn)槠胀ú樵儧]有任何鎖機(jī)制。

意向鎖：另外矫夷，為了允許行鎖和表鎖共存葛闷，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks）口四，這兩種意向鎖都是表鎖孵运。

• 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖蔓彩。
• 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加排他鎖治笨，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

加鎖方法：意向鎖是InnoDB自動(dòng)加的赤嚼，不需用戶干預(yù)旷赖。對(duì)于UPDATE、DELETE和INSERT語(yǔ)句更卒，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)等孵；對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖蹂空。
事務(wù)可以通過(guò)以下語(yǔ)句顯式給記錄集加共享鎖或排他鎖：

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X）：SELECT * FROM table_name WHERE ... FOR UPDATE

行鎖實(shí)現(xiàn)方式：InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的俯萌，只有通過(guò)索引條件檢索數(shù)據(jù)果录，InnoDB才使用行級(jí)鎖，否則咐熙，InnoDB將使用表鎖弱恒！
（1）在不通過(guò)索引條件查詢的時(shí)候，InnoDB確實(shí)使用的是表鎖棋恼，而不是行鎖返弹。
（2）由于MySQL的行鎖是針對(duì)索引加的鎖，不是針對(duì)記錄加的鎖爪飘，所以雖然是訪問不同行的記錄义起，但是如果是使用相同的索引鍵，是會(huì)出現(xiàn)鎖沖突的师崎。
（3）當(dāng)表有多個(gè)索引的時(shí)候默终，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外抡诞，不論是使用主鍵索引穷蛹、唯一索引或普通索引土陪，InnoDB都會(huì)使用行鎖來(lái)對(duì)數(shù)據(jù)加鎖昼汗。
（4）即便在條件中使用了索引字段，但是否使用索引來(lái)檢索數(shù)據(jù)是由MySQL通過(guò)判斷不同執(zhí)行計(jì)劃的代價(jià)來(lái)決 定的鬼雀，如果MySQL認(rèn)為全表掃描效率更高顷窒，比如對(duì)一些很小的表，它就不會(huì)使用索引源哩，這種情況下InnoDB將使用表鎖鞋吉，而不是行鎖。

6励烦、那你知道什么是間隙鎖嗎谓着？

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)坛掠，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的 索引項(xiàng)加鎖赊锚；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”屉栓，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖舷蒲，這種鎖機(jī)制就是所謂的間隙鎖 （Next-Key鎖）。

（1）舉例來(lái)說(shuō)友多，假如emp表中只有101條記錄牲平，其empid的值分別是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一個(gè)范圍條件的檢索域滥，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖纵柿，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖蜈抓。

（2）InnoDB除了通過(guò)范圍條件加鎖時(shí)使用間隙鎖外，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖昂儒，InnoDB也會(huì)使用間隙鎖资昧！
假設(shè)emp表中只有101條記錄，其empid的值分別是1,2,……,100,101荆忍。 在一個(gè)事務(wù)中執(zhí)行如下語(yǔ)句加鎖：

Select * from  emp where empid > 100 for update;

在另一個(gè)事務(wù)中格带，執(zhí)行如下語(yǔ)句會(huì)出現(xiàn)鎖等待的情況

insert into emp(empid,...) values (201,...);

7、InnoDB使用間隙鎖的目的：

• （1）防止幻讀刹枉，以滿足相關(guān)隔離級(jí)別的要求叽唱；
• （2）滿足恢復(fù)和復(fù)制的需要：
  MySQL 通過(guò) BINLOG 錄入執(zhí)行成功的 INSERT、UPDATE微宝、DELETE 等更新數(shù)據(jù)的 SQL 語(yǔ)句棺亭，并由此實(shí)現(xiàn) MySQL 數(shù)據(jù)庫(kù)的恢復(fù)和主從復(fù)制。MySQL 的恢復(fù)機(jī)制（復(fù)制其實(shí)就是在 Slave Mysql 不斷做基于 BINLOG 的恢復(fù)）有以下特點(diǎn)：

一是 MySQL 的恢復(fù)是 SQL 語(yǔ)句級(jí)的蟋软，也就是重新執(zhí)行 BINLOG 中的 SQL 語(yǔ)句镶摘。
二是 MySQL 的 Binlog 是按照事務(wù)提交的先后順序記錄的， 恢復(fù)也是按這個(gè)順序進(jìn)行的岳守。

由此可見凄敢，MySQL 的恢復(fù)機(jī)制要求：在一個(gè)事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄湿痢，也就是不允許出現(xiàn)幻讀涝缝。

四、Mysql優(yōu)化

1譬重、SQL優(yōu)化主要分5個(gè)方向：SQL語(yǔ)句跟索引拒逮、表結(jié)構(gòu)設(shè)計(jì)、系統(tǒng)配置臀规、硬件配置滩援、數(shù)據(jù)庫(kù)架構(gòu)擴(kuò)展

• （1）SQL語(yǔ)句跟索引：①最大化利用索引 ②盡可能避免全表掃描 ③減少無(wú)效數(shù)據(jù)的查詢
• （2）表結(jié)構(gòu)設(shè)計(jì)：①表設(shè)計(jì)符合三范式 ②選擇合適的數(shù)據(jù)類型
• （3）系統(tǒng)配置：①選擇合適的存儲(chǔ)引擎 ②系統(tǒng)內(nèi)核優(yōu)化（Mysql主要運(yùn)行在Linux上）
• （4）硬件配置：①加大物理內(nèi)存 ②使用SSD硬盤代替SAS硬盤 ，畢竟數(shù)據(jù)庫(kù)的壓力主要來(lái)自磁盤I/O方面
• （5）數(shù)據(jù)庫(kù)架構(gòu)擴(kuò)展：①讀寫分離 ②分庫(kù)分表分區(qū) ③給數(shù)據(jù)庫(kù)增加緩存系統(tǒng)

2塔嬉、你們數(shù)據(jù)量級(jí)多大玩徊？分庫(kù)分表分區(qū)怎么做的？

分區(qū)：就是把一張表的數(shù)據(jù)分成N個(gè)區(qū)塊邑遏，在邏輯上看最終只是一張表佣赖，但底層是由N個(gè)物理區(qū)塊組成的。
分表：就是把一張表按一定的規(guī)則分解成N個(gè)具有獨(dú)立存儲(chǔ)空間的實(shí)體表记盒。
分庫(kù)：一旦分表憎蛤，一個(gè)庫(kù)中的表會(huì)越來(lái)越多。
首先分庫(kù)分表分為垂直和水平兩個(gè)方式，一般來(lái)說(shuō)我們拆分的順序是先垂直后水平俩檬。

3萎胰、那分表后的ID怎么保證唯一性的呢？

（1）設(shè)定步長(zhǎng)棚辽，比如1-1024張表我們分別設(shè)定1-1024的基礎(chǔ)步長(zhǎng)技竟，這樣主鍵落到不同的表就不會(huì)沖突了。
（2）分布式ID屈藐，自己實(shí)現(xiàn)一套分布式ID生成算法或者使用開源的比如雪花算法這種榔组。
（3）分表后不使用主鍵作為查詢依據(jù)，而是每張表單獨(dú)新增一個(gè)字段作為唯一主鍵使用联逻，比如訂單表訂單號(hào)是唯一的搓扯，不管最終落在哪張表都基于訂單號(hào)作為查詢依據(jù)，更新也一樣包归。

4锨推、數(shù)據(jù)庫(kù)三大范式

• 第一范式：保證數(shù)據(jù)的原子性，數(shù)據(jù)庫(kù)表中的字段不可再分
• 第二范式：消除部分依賴公壤，非主鍵列完全依賴于主鍵,而不能是依賴于主鍵的一部分（主要針對(duì)聯(lián)合主鍵而言）
• 第三范式：消除傳遞依賴换可，非主鍵列只依賴于主鍵,不依賴于其他非主鍵

五、Mysql主從同步（讀寫分離厦幅、數(shù)據(jù)備份）

1沾鳄、說(shuō)說(shuō)mysql主從同步怎么做的吧？

（1）master數(shù)據(jù)發(fā)生變化慨削，提交完事務(wù)后洞渔，寫入binlog
（2）slave連接到master套媚，獲取binlog
（3）master創(chuàng)建dump線程缚态，推送binglog到slave
（4）slave啟動(dòng)一個(gè)IO線程讀取同步過(guò)來(lái)的master的binlog，記錄到relay log中繼日志中
（5）slave再開啟一個(gè)sql線程讀取relay log事件并在slave執(zhí)行堤瘤，完成同步
（6）slave記錄自己的binglog

.Master 數(shù)據(jù)庫(kù)只要發(fā)生變化玫芦，立馬記錄到Binary log 日志文件中
2.Slave數(shù)據(jù)庫(kù)啟動(dòng)一個(gè)I/O thread連接Master數(shù)據(jù)庫(kù)，請(qǐng)求Master變化的二進(jìn)制日志
3.Slave I/O獲取到的二進(jìn)制日志本辐，保存到自己的Relay log 日志文件中桥帆。
4.Slave 有一個(gè) SQL thread定時(shí)檢查Realy log是否變化，變化那么就更新數(shù)據(jù)

由于mysql默認(rèn)的復(fù)制方式是異步的慎皱，主庫(kù)把日志發(fā)送給從庫(kù)后不關(guān)心從庫(kù)是否已經(jīng)處理老虫，這樣會(huì)產(chǎn)生一個(gè)問題就是假設(shè)主庫(kù)掛了，從庫(kù)處理失敗了茫多，這時(shí)候從庫(kù)升為主庫(kù)后祈匙，日志就丟失了。由此產(chǎn)生兩個(gè)概念。

• 全同步復(fù)制：主庫(kù)寫入binlog后強(qiáng)制同步日志到從庫(kù)夺欲，所有的從庫(kù)都執(zhí)行完成后才返回給客戶端跪帝，但是很顯然這個(gè)方式的話性能會(huì)受到嚴(yán)重影響。
• 半同步復(fù)制：和全同步不同的是些阅，半同步復(fù)制的邏輯是這樣伞剑，從庫(kù)寫入日志成功后返回ACK確認(rèn)給主庫(kù)，主庫(kù)收到至少一個(gè)從庫(kù)的確認(rèn)就認(rèn)為寫操作完成市埋。

2黎泣、主從同步/復(fù)制的好處

• （1）實(shí)現(xiàn)服務(wù)器負(fù)載均衡（讀寫分離）
• （2）通過(guò)復(fù)制實(shí)現(xiàn)數(shù)據(jù)的異地備份
• （3）提高數(shù)據(jù)庫(kù)系統(tǒng)的可用性

六、執(zhí)行計(jì)劃

使用 MySQL 的執(zhí)行計(jì)劃來(lái)查看 SQL 語(yǔ)句的執(zhí)行效率

EXPLAIN SELECT * FROM test_aa WHERE id = 1

1缤谎、select_type：查詢的類型聘裁，主要是用于區(qū)分普通查詢、聯(lián)合查詢弓千、子查詢等衡便。簡(jiǎn)單的 select 查詢，查詢中不包含子查詢或者 union

• PRIMARY：查詢中包含子部分洋访，最外層查詢則被標(biāo)記為 primary
• SUBQUERY：SUBQUERY 表示在 select 或 where 列表中包含了子查詢镣陕，
• UNION：表示 union 中的第二個(gè)或后面的 select 語(yǔ)句

2、table：查詢涉及到的表姻政。

3呆抑、type ：訪問類型，SQL 查詢優(yōu)化中一個(gè)很重要的指標(biāo)汁展，結(jié)果值從好到壞依次是：system > const > eq_ref > ref > range > index > ALL鹊碍。

• system：系統(tǒng)表，少量數(shù)據(jù)食绿，往往不需要進(jìn)行磁盤IO
• const：常量連接
• eq_ref：主鍵索引（primary key）或者非空唯一索引（unique not null）等值掃描
• ref：非主鍵非唯一索引等值掃描
• range：范圍掃描
• index：索引樹掃描
• ALL：全表掃描（full table scan）

4侈咕、possible_keys：查詢過(guò)程中有可能用到的索引。

5器紧、key：實(shí)際使用的索引耀销，如果為 NULL ，則沒有使用索引铲汪。

6熊尉、rows：根據(jù)表統(tǒng)計(jì)信息或者索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)掌腰。

7狰住、filtered：表示返回結(jié)果的行數(shù)占需讀取行數(shù)的百分比， filtered 的值越大越好齿梁。

8催植、Extra ：十分重要的額外信息。

• Using index：表示 SQL 操作中使用了覆蓋索引（Covering Index），避免了訪問表的數(shù)據(jù)行查邢，效率高蔗崎。
• Using index condition：表示 SQL 操作命中了索引，但不是所有的列數(shù)據(jù)都在索引樹上扰藕，還需要訪問實(shí)際的行記錄缓苛。
• Using where：表示 SQL 操作使用了 where 過(guò)濾條件。