[[259658]]

互聯(lián)網(wǎng)公司面試必問(wèn)的MySQL題目(上)

01什么是數(shù)據(jù)庫(kù)事務(wù)?如果沒(méi)有事物會(huì)有什么后果?事務(wù)的特性是什么?

事務(wù)是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作，可以被看作一個(gè)單元的一系列SQL語(yǔ)句的集合。要么完全地執(zhí)行，要么完全地不執(zhí)行。

如果不對(duì)數(shù)據(jù)庫(kù)進(jìn)行并發(fā)控制，可能會(huì)產(chǎn)生 臟讀、非重復(fù)讀、幻像讀、丟失修改的異常情況。

事務(wù)的特性(ACID)

A, atomacity 原子性 事務(wù)必須是原子工作單元;對(duì)于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。通常，與某個(gè)事務(wù)關(guān)聯(lián)的操作具有共同的目標(biāo)，并且是相互依賴的。如果系統(tǒng)只執(zhí)行這些操作的一個(gè)子集，則可能會(huì)破壞事務(wù)的總體目標(biāo)。原子性消除了系統(tǒng)處理操作子集的可能性。

C, consistency 一致性

事務(wù)將數(shù)據(jù)庫(kù)從一種一致?tīng)顟B(tài)轉(zhuǎn)變?yōu)橄乱环N一致?tīng)顟B(tài)。也就是說(shuō)，事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)(各種 constraint 不被破壞)。

I, isolation 隔離性 由并發(fā)事務(wù)所作的修改必須與任何其它并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。換句話說(shuō)，一個(gè)事務(wù)的影響在該事務(wù)提交前對(duì)其他事務(wù)都不可見(jiàn)。

D, durability 持久性

事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是***性的。該修改即使出現(xiàn)致命的系統(tǒng)故障也將一直保持。

 

 

 

[[259659]]

 

“A向B匯錢100”

讀出A賬號(hào)余額(500)。

A賬號(hào)扣錢操作(500-100)。

結(jié)果寫回A賬號(hào)(400)。

讀出B賬號(hào)余額(500)。

B賬號(hào)做加法操作(500+100)。

結(jié)果寫回B賬號(hào)(600)。

原子性：

保證1-6所有過(guò)程要么都執(zhí)行，要么都不執(zhí)行。如果異常了那么回滾。

一致性

轉(zhuǎn)賬前，A和B的賬戶中共有500+500=1000元錢。轉(zhuǎn)賬后，A和B的賬戶中共有400+600=1000元。

隔離性

在A向B轉(zhuǎn)賬的整個(gè)過(guò)程中，只要事務(wù)還沒(méi)有提交(commit)，查詢A賬戶和B賬戶的時(shí)候，兩個(gè)賬戶里面的錢的數(shù)量都不會(huì)有變化。

持久性

一旦轉(zhuǎn)賬成功(事務(wù)提交)，兩個(gè)賬戶的里面的錢就會(huì)真的發(fā)生變化

02什么是臟讀?幻讀?不可重復(fù)讀?什么是事務(wù)的隔離級(jí)別?Mysql的默認(rèn)隔離級(jí)別是?

• 臟讀：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)
• 不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù)A多次讀取的過(guò)程中，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果 不一致。
• 幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為ABCDE等級(jí)，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒(méi)有改過(guò)來(lái)，就好像發(fā)生了幻覺(jué)一樣，這就叫幻讀。

Read uncommitted

讀未提交，顧名思義，就是一個(gè)事務(wù)可以讀取另一個(gè)未提交事務(wù)的數(shù)據(jù)。

Read committed

讀提交，顧名思義，就是一個(gè)事務(wù)要等另一個(gè)事務(wù)提交后才能讀取數(shù)據(jù)。

 

[[259659]]

 

小A去買東西(卡里有1萬(wàn)元)，當(dāng)他買單時(shí)(事務(wù)開(kāi)啟)，系統(tǒng)事先檢測(cè)到他的卡里有1萬(wàn)，就在這個(gè)時(shí)候!!小A的妻子要把錢全部轉(zhuǎn)出充當(dāng)家用，并提交。當(dāng)系統(tǒng)準(zhǔn)備扣款時(shí)，再檢測(cè)卡里的金額，發(fā)現(xiàn)已經(jīng)沒(méi)錢了(第二次檢測(cè)金額當(dāng)然要等待妻子轉(zhuǎn)出金額事務(wù)提交完)。A就會(huì)很郁悶

分析：這就是讀提交，若有事務(wù)對(duì)數(shù)據(jù)進(jìn)行更新(UPDATE)操作時(shí)，讀操作事務(wù)要等待這個(gè)更新操作事務(wù)提交后才能讀取數(shù)據(jù)，可以解決臟讀問(wèn)題。但在這個(gè)事例中，出現(xiàn)了一個(gè)事務(wù)范圍內(nèi)兩個(gè)相同的查詢卻返回了不同數(shù)據(jù)，這就是不可重復(fù)讀。

Repeatable read

重復(fù)讀，就是在開(kāi)始讀取數(shù)據(jù)(事務(wù)開(kāi)啟)時(shí)，不再允許修改操作

事例：小A去買東西(卡里有1萬(wàn)元)，當(dāng)他買單時(shí)(事務(wù)開(kāi)啟，不允許其他事務(wù)的UPDATE修改操作)，收費(fèi)系統(tǒng)事先檢測(cè)到他的卡里有1萬(wàn)。這時(shí)候他的妻子不能轉(zhuǎn)出金額了。接下來(lái)收費(fèi)系統(tǒng)就可以扣款了。

分析：重復(fù)讀可以解決不可重復(fù)讀問(wèn)題。寫到這里，應(yīng)該明白的一點(diǎn)就是，不可重復(fù)讀對(duì)應(yīng)的是修改，即UPDATE操作。但是可能還會(huì)有幻讀問(wèn)題。因?yàn)榛米x問(wèn)題對(duì)應(yīng)的是插入INSERT操作，而不是UPDATE操作。

什么時(shí)候會(huì)出現(xiàn)幻讀?

事例：小A去買東西，花了2千元，然后他的妻子去查看他的消費(fèi)記錄(全表掃描FTS，妻事務(wù)開(kāi)啟)，看到確實(shí)是花了2千元，就在這個(gè)時(shí)候，小A花了1萬(wàn)買了一部電腦，INSERT了一條消費(fèi)記錄，并提交。當(dāng)妻子打印小A的消費(fèi)記錄清單時(shí)(妻子事務(wù)提交)，發(fā)現(xiàn)花了1.2萬(wàn)元，似乎出現(xiàn)了幻覺(jué)，這就是幻讀。

Serializable 序列化

Serializable 是***的事務(wù)隔離級(jí)別，在該級(jí)別下，事務(wù)串行化順序執(zhí)行，可以避免臟讀、不可重復(fù)讀與幻讀。但是這種事務(wù)隔離級(jí)別效率低下，比較耗數(shù)據(jù)庫(kù)性能，一般不使用。

Mysql的默認(rèn)隔離級(jí)別是Repeatable read。

03事物隔離是怎么實(shí)現(xiàn)的?

是基于鎖實(shí)現(xiàn)的.

有哪些鎖?分別介紹下

在DBMS中，可以按照鎖的粒度把數(shù)據(jù)庫(kù)鎖分為行級(jí)鎖(INNODB引擎)、表級(jí)鎖(MYISAM引擎)和頁(yè)級(jí)鎖(BDB引擎 )。

行級(jí)鎖

行級(jí)鎖是Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫(kù)操作的沖突。其加鎖粒度最小，但加鎖的開(kāi)銷也***。行級(jí)鎖分為共享鎖 和 排他鎖。

特點(diǎn)

開(kāi)銷大，加鎖慢;會(huì)出現(xiàn)死鎖;鎖定粒度最小，發(fā)生鎖沖突的概率***，并發(fā)度也***。

表級(jí)鎖

表級(jí)鎖是MySQL中鎖定粒度***的一種鎖，表示對(duì)當(dāng)前操作的整張表加鎖，它實(shí)現(xiàn)簡(jiǎn)單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級(jí)鎖定。表級(jí)鎖定分為表共享讀鎖(共享鎖)與表獨(dú)占寫鎖(排他鎖)。

特點(diǎn)

開(kāi)銷小，加鎖快;不會(huì)出現(xiàn)死鎖;鎖定粒度大，發(fā)出鎖沖突的概率***，并發(fā)度***。

頁(yè)級(jí)鎖

頁(yè)級(jí)鎖是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖。表級(jí)鎖速度快，但沖突多，行級(jí)沖突少，但速度慢。所以取了折衷的頁(yè)級(jí)，一次鎖定相鄰的一組記錄。

特點(diǎn)

開(kāi)銷和加鎖時(shí)間界于表鎖和行鎖之間;會(huì)出現(xiàn)死鎖;鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

04什么是死鎖?怎么解決?(前幾問(wèn)題是我個(gè)人最喜歡的連環(huán)炮，基本可以看出面試者的基礎(chǔ)功)

死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)付的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。

常見(jiàn)的解決死鎖的方法

1. 如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)。
2. 在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率;
3. 對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率;

如果業(yè)務(wù)處理不好可以用分布式事務(wù)鎖或者使用樂(lè)觀鎖

05SQL的生命周期?關(guān)鍵字的先后順序?

1. 應(yīng)用服務(wù)器與數(shù)據(jù)庫(kù)服務(wù)器建立一個(gè)連接
2. 數(shù)據(jù)庫(kù)進(jìn)程拿到請(qǐng)求sql
3. 解析并生成執(zhí)行計(jì)劃，執(zhí)行
4. 讀取數(shù)據(jù)到內(nèi)存并進(jìn)行邏輯處理
5. 通過(guò)步驟一的連接，發(fā)送結(jié)果到客戶端
6. 關(guān)掉連接，釋放資源

 

 

 

 

1. FROM：對(duì) FROM 子句中的前兩個(gè)表執(zhí)行笛卡爾積(交叉聯(lián)接)，生成虛擬表 VT1。
2. ON：對(duì) VT1 應(yīng)用 ON 篩選器，只有那些使為真才被插入到 TV2。
3. OUTER (JOIN):如果指定了 OUTER JOIN(相對(duì)于 CROSS JOIN 或 INNER JOIN)，保留表中未找到匹配的行將作為外部行添加到 VT2，生成 TV3。如果 FROM 子句包含兩個(gè)以上的表，則對(duì)上一個(gè)聯(lián)接生成的結(jié)果表和下一個(gè)表重復(fù)執(zhí)行步驟 1 到步驟 3，直到處理完所有的表位置。
4. WHERE：對(duì) TV3 應(yīng)用 WHERE 篩選器，只有使為 true 的行才插入 TV4。
5. GROUP BY：按 GROUP BY 子句中的列列表對(duì) TV4 中的行進(jìn)行分組，生成 TV5。
6. CUTE|ROLLUP：把超組插入 VT5，生成 VT6。
7. HAVING：對(duì) VT6 應(yīng)用 HAVING 篩選器，只有使為 true 的組插入到 VT7。
8. SELECT：處理 SELECT 列表，產(chǎn)生 VT8。
9. DISTINCT：將重復(fù)的行從 VT8 中刪除，產(chǎn)品 VT9。
10. ORDER BY：將 VT9 中的行按 ORDER BY 子句中的列列表順序，生成一個(gè)游標(biāo)(VC10)。
11. TOP：從 VC10 的開(kāi)始處選擇指定數(shù)量或比例的行，生成表 TV11，并返回給調(diào)用者。

06什么是樂(lè)觀鎖?悲觀鎖?實(shí)現(xiàn)方式?

悲觀鎖：

悲觀鎖指對(duì)數(shù)據(jù)被意外修改持保守態(tài)度，依賴數(shù)據(jù)庫(kù)原生支持的鎖機(jī)制來(lái)保證當(dāng)前事務(wù)處理的安全性，防止其他并發(fā)事務(wù)對(duì)目標(biāo)數(shù)據(jù)的破壞或破壞其他并發(fā)事務(wù)數(shù)據(jù)，將在事務(wù)開(kāi)始執(zhí)行前或執(zhí)行中申請(qǐng)鎖定，執(zhí)行完后再釋放鎖定。這對(duì)于長(zhǎng)事務(wù)來(lái)講，可能會(huì)嚴(yán)重影響系統(tǒng)的并發(fā)處理能力。 自帶的數(shù)據(jù)庫(kù)事務(wù)就是典型的悲觀鎖。

樂(lè)觀鎖：

樂(lè)觀鎖(Optimistic Lock)，顧名思義，就是很樂(lè)觀，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改，所以不會(huì)上鎖，但是在提交更新的時(shí)候會(huì)判斷一下在此期間別人有沒(méi)有去更新這個(gè)數(shù)據(jù)。樂(lè)觀鎖適用于讀多寫少的應(yīng)用場(chǎng)景，這樣可以提高吞吐量。

一般是加一個(gè)版本號(hào)字段 每次更新時(shí)候比較版本號(hào)

07大數(shù)據(jù)情況下如何做分頁(yè)?

可以參考阿里巴巴java開(kāi)發(fā)手冊(cè)上的答案

 

 

 

 

08什么是數(shù)據(jù)庫(kù)連接池?

從上一個(gè)sql生命周期題目，可以看到其中的連接在里面發(fā)揮著重大作用，但頻繁的創(chuàng)建和銷毀，非常浪費(fèi)系統(tǒng)資源。由于數(shù)據(jù)庫(kù)更適合長(zhǎng)連接，也就有個(gè)連接池，能對(duì)連接復(fù)用，維護(hù)連接對(duì)象、分配、管理、釋放，也可以避免創(chuàng)建大量的連接對(duì)DB引發(fā)的各種問(wèn)題;另外通過(guò)請(qǐng)求排隊(duì)，也緩解對(duì)DB的沖擊。

互聯(lián)網(wǎng)公司面試必問(wèn)的MySQL題目(下)

什么是數(shù)據(jù)庫(kù)索引?索引有哪幾種類型?什么是最左前綴原則?索引算法有哪些?有什么區(qū)別?

索引是對(duì)數(shù)據(jù)庫(kù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。一個(gè)非常恰當(dāng)?shù)谋扔骶褪菚哪夸涰?yè)與書的正文內(nèi)容之間的關(guān)系，為了方便查找書中的內(nèi)容，通過(guò)對(duì)內(nèi)容建立索引形成目錄。索引是一個(gè)文件，它是要占據(jù)物理空間的。

主鍵索引:

數(shù)據(jù)列不允許重復(fù)，不允許為NULL.一個(gè)表只能有一個(gè)主鍵。

唯一索引:

數(shù)據(jù)列不允許重復(fù)，允許為NULL值，一個(gè)表允許多個(gè)列創(chuàng)建唯一索引。

可以通過(guò) 

ALTER TABLE table_name ADD UNIQUE (column); 

創(chuàng)建唯一索引

可以通過(guò) 

ALTER TABLE table_name ADD UNIQUE (column1,column2); 

創(chuàng)建唯一組合索引

普通索引:

基本的索引類型，沒(méi)有唯一性的限制，允許為NULL值。

可以通過(guò)ALTER TABLE table_name ADD INDEX index_name (column);創(chuàng)建普通索引

可以通過(guò)ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創(chuàng)建組合索引

全文索引：

是目前搜索引擎使用的一種關(guān)鍵技術(shù)。

可以通過(guò)ALTER TABLE table_name ADD FULLTEXT (column);創(chuàng)建全文索引

最左前綴

• 顧名思義，就是最左優(yōu)先，在創(chuàng)建多列索引時(shí)，要根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊。
• 還有一個(gè)就是生效原則 比如 

index(a,b,c) 
where a=3    只使用了a 
where a=3 and b=5    使用了a,b 
where a=3 and b=5 and c=4    使用了a,b,c 
where b=3 or where c=4    沒(méi)有使用索引 
where a=3 and c=4    僅使用了a 
where a=3 and b>10 and c=7    使用了a,b 
where a=3 and b like ' xx%' and c=7    使用了a,b 

索引算法有 BTree Hash

BTree是最常用的mysql數(shù)據(jù)庫(kù)索引算法，也是mysql默認(rèn)的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個(gè)不以通配符開(kāi)頭的常量，

例如： 

select * from user where name like 'jack%'; 

如果一通配符開(kāi)頭，或者沒(méi)有使用常量，則不會(huì)使用索引，例如： 

select * from user where name like '%jack'; 

Hash

Hash索引只能用于對(duì)等比較，例如=,<=>(相當(dāng)于=)操作符。由于是一次定位數(shù)據(jù)，不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，***才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次IO訪問(wèn)，所以檢索效率遠(yuǎn)高于BTree索引。

BTree索引是最常用的mysql數(shù)據(jù)庫(kù)索引算法，也是mysql默認(rèn)的算法。因?yàn)樗粌H可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符

例如：

只要它的查詢條件是一個(gè)不以通配符開(kāi)頭的常量select * from user where name like 'jack%'; 如果一通配符開(kāi)頭，或者沒(méi)有使用常量，則不會(huì)使用索引，例如： select * from user where name like '%jack';

Hash

Hash索引只能用于對(duì)等比較，例如=,<=>(相當(dāng)于=)操作符。由于是一次定位數(shù)據(jù)，不像BTree索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，***才能訪問(wèn)到頁(yè)節(jié)點(diǎn)這樣多次IO訪問(wèn)，所以檢索效率遠(yuǎn)高于BTree索引。

索引設(shè)計(jì)的原則?

1. 適合索引的列是出現(xiàn)在where子句中的列，或者連接子句中指定的列
2. 基數(shù)較小的類，索引效果較差，沒(méi)有必要在此列建立索引
3. 使用短索引，如果對(duì)長(zhǎng)字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長(zhǎng)度，這樣能夠節(jié)省大量索引空間
4. 不要過(guò)度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內(nèi)容的時(shí)候，索引會(huì)進(jìn)行更新甚至重構(gòu)，索引列越多，這個(gè)時(shí)間就會(huì)越長(zhǎng)。所以只保持需要的索引有利于查詢即可。

如何定位及優(yōu)化SQL語(yǔ)句的性能問(wèn)題?

對(duì)于低性能的SQL語(yǔ)句的定位，最重要也是最有效的方法就是使用執(zhí)行計(jì)劃。

我們知道，不管是哪種數(shù)據(jù)庫(kù)，或者是哪種數(shù)據(jù)庫(kù)引擎，在對(duì)一條SQL語(yǔ)句進(jìn)行執(zhí)行的過(guò)程中都會(huì)做很多相關(guān)的優(yōu)化，對(duì)于查詢語(yǔ)句，最重要的優(yōu)化方式就是使用索引。

而執(zhí)行計(jì)劃，就是顯示數(shù)據(jù)庫(kù)引擎對(duì)于SQL語(yǔ)句的執(zhí)行的詳細(xì)情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關(guān)信息等。 

 

 

執(zhí)行計(jì)劃包含的信息

id

有一組數(shù)字組成。表示一個(gè)查詢中各個(gè)子查詢的執(zhí)行順序;

1. id相同執(zhí)行順序由上至下。
2. id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行。
3. id為null時(shí)表示一個(gè)結(jié)果集，不需要使用它查詢，常出現(xiàn)在包含union等查詢語(yǔ)句中。

select_type

每個(gè)子查詢的查詢類型，一些常見(jiàn)的查詢類型。

id	select_type	description
1	SIMPLE	不包含任何子查詢或union等查詢
2	PRIMARY	包含子查詢最外層查詢就顯示為 PRIMARY
3	SUBQUERY	在select或 where字句中包含的查詢
4	DERIVED	from字句中包含的查詢
5	UNION	出現(xiàn)在union后的查詢語(yǔ)句中
6	UNION RESULT	從UNION中獲取結(jié)果集，例如上文的第三個(gè)例子

table

查詢的數(shù)據(jù)表，當(dāng)從衍生表中查數(shù)據(jù)時(shí)會(huì)顯示 x 表示對(duì)應(yīng)的執(zhí)行計(jì)劃id

partitions

表分區(qū)、表創(chuàng)建的時(shí)候可以指定通過(guò)那個(gè)列進(jìn)行表分區(qū)。 舉個(gè)例子： 

create table tmp ( 
    id int unsigned not null AUTO_INCREMENT, 
    name varchar(255), 
    PRIMARY KEY (id) 
) engine = innodb 
partition by key (id) partitions 5;  

 

 

type(非常重要，可以看到有沒(méi)有走索引)

訪問(wèn)類型

• ALL 掃描全表數(shù)據(jù)
• index 遍歷索引
• range 索引范圍查找
• index_subquery 在子查詢中使用 ref
• unique_subquery 在子查詢中使用 eq_ref
• ref_or_null 對(duì)Null進(jìn)行索引的優(yōu)化的 ref
• fulltext 使用全文索引
• ref 使用非唯一索引查找數(shù)據(jù)
• eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關(guān)聯(lián)。

possible_keys

可能使用的索引，注意不一定會(huì)使用。查詢涉及到的字段上若存在索引，則該索引將被列出來(lái)。當(dāng)該列為 NULL時(shí)就要考慮當(dāng)前的SQL是否需要優(yōu)化了。

key

顯示MySQL在查詢中實(shí)際使用的索引，若沒(méi)有使用索引，顯示為NULL。

TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數(shù)據(jù)覆蓋了需要查詢的所有數(shù)據(jù))，則該索引僅出現(xiàn)在key列表中

key_length

索引長(zhǎng)度

ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值

rows

返回估算的結(jié)果集數(shù)目，并不是一個(gè)準(zhǔn)確的值。

extra

extra的信息非常豐富，常見(jiàn)的有：

1. Using index 使用覆蓋索引
2. Using where 使用了用where子句來(lái)過(guò)濾結(jié)果集
3. Using filesort 使用文件排序，使用非索引列進(jìn)行排序時(shí)出現(xiàn)，非常消耗性能，盡量?jī)?yōu)化。
4. Using temporary 使用了臨時(shí)表

sql優(yōu)化的目標(biāo)可以參考阿里開(kāi)發(fā)手冊(cè)

 

 

 

某個(gè)表有近千萬(wàn)數(shù)據(jù)，CRUD比較慢，如何優(yōu)化?分庫(kù)分表了是怎么做的?分表分庫(kù)了有什么問(wèn)題?有用到中間件么?他們的原理知道么?

數(shù)據(jù)***別之多，占用的存儲(chǔ)空間也比較大，可想而知它不會(huì)存儲(chǔ)在一塊連續(xù)的物理空間上，而是鏈?zhǔn)酱鎯?chǔ)在多個(gè)碎片的物理空間上?？赡軐?duì)于長(zhǎng)字符串的比較，就用更多的時(shí)間查找與比較，這就導(dǎo)致用更多的時(shí)間。

• 可以做表拆分，減少單表字段數(shù)量，優(yōu)化表結(jié)構(gòu)。
• 在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語(yǔ)句中條件的字段順序和主鍵索引的字段順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

 

 

 

垂直分表

也就是“大表拆小表”，基于列字段進(jìn)行的。一般是表中的字段較多，將不常用的， 數(shù)據(jù)較大，長(zhǎng)度較長(zhǎng)(比如text類型字段)的拆分到“擴(kuò)展表“。 一般是針對(duì)那種幾百列的大表，也避免查詢時(shí)，數(shù)據(jù)量太大造成的“跨頁(yè)”問(wèn)題。

垂直分庫(kù)針對(duì)的是一個(gè)系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分，比如用戶User一個(gè)庫(kù)，商品Producet一個(gè)庫(kù)，訂單Order一個(gè)庫(kù)。 切分后，要放在多個(gè)服務(wù)器上，而不是一個(gè)服務(wù)器上。為什么? 我們想象一下，一個(gè)購(gòu)物網(wǎng)站對(duì)外提供服務(wù)，會(huì)有用戶，商品，訂單等的CRUD。沒(méi)拆分之前， 全部都是落到單一的庫(kù)上的，這會(huì)讓數(shù)據(jù)庫(kù)的單庫(kù)處理能力成為瓶頸。按垂直分庫(kù)后，如果還是放在一個(gè)數(shù)據(jù)庫(kù)服務(wù)器上， 隨著用戶量增大，這會(huì)讓單個(gè)數(shù)據(jù)庫(kù)的處理能力成為瓶頸，還有單個(gè)服務(wù)器的磁盤空間，內(nèi)存，tps等非常吃緊。 所以我們要拆分到多個(gè)服務(wù)器上，這樣上面的問(wèn)題都解決了，以后也不會(huì)面對(duì)單機(jī)資源問(wèn)題。

數(shù)據(jù)庫(kù)業(yè)務(wù)層面的拆分，和服務(wù)的“治理”，“降級(jí)”機(jī)制類似，也能對(duì)不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理，維護(hù)，監(jiān)控，擴(kuò)展等。 數(shù)據(jù)庫(kù)往往最容易成為應(yīng)用系統(tǒng)的瓶頸，而數(shù)據(jù)庫(kù)本身屬于“有狀態(tài)”的，相對(duì)于Web和應(yīng)用服務(wù)器來(lái)講，是比較難實(shí)現(xiàn)“橫向擴(kuò)展”的。 數(shù)據(jù)庫(kù)的連接資源比較寶貴且單機(jī)處理能力也有限，在高并發(fā)場(chǎng)景下，垂直分庫(kù)一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。

水平分表

針對(duì)數(shù)據(jù)量巨大的單張表(比如訂單表)，按照某種規(guī)則(RANGE,HASH取模等)，切分到多張表里面去。 但是這些表還是在同一個(gè)庫(kù)中，所以庫(kù)級(jí)別的數(shù)據(jù)庫(kù)操作還是有IO瓶頸。不建議采用。

水平分庫(kù)分表

將單張表的數(shù)據(jù)切分到多個(gè)服務(wù)器上去，每個(gè)服務(wù)器具有相應(yīng)的庫(kù)與表，只是表中數(shù)據(jù)集合不同。 水平分庫(kù)分表能夠有效的緩解單機(jī)和單庫(kù)的性能瓶頸和壓力，突破IO、連接數(shù)、硬件資源等的瓶頸。

水平分庫(kù)分表切分規(guī)則

        1.RANGE從

0到10000一個(gè)表，10001到20000一個(gè)表;

        2.HASH取模

一個(gè)商場(chǎng)系統(tǒng)，一般都是將用戶，訂單作為主表，然后將和它們相關(guān)的作為附表，這樣不會(huì)造成跨庫(kù)事務(wù)之類的問(wèn)題。 取用戶id，然后hash取模，分配到不同的數(shù)據(jù)庫(kù)上。

        3.地理區(qū)域

比如按照華東，華南，華北這樣來(lái)區(qū)分業(yè)務(wù)，七牛云應(yīng)該就是如此。

        4.時(shí)間

按照時(shí)間切分，就是將6個(gè)月前，甚至一年前的數(shù)據(jù)切出去放到另外的一張表，因?yàn)殡S著時(shí)間流逝，這些表的數(shù)據(jù) 被查詢的概率變小，所以沒(méi)必要和“熱數(shù)據(jù)”放在一起，這個(gè)也是“冷熱數(shù)據(jù)分離”。

分庫(kù)分表后面臨的問(wèn)題

• 事務(wù)支持

分庫(kù)分表后，就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià); 如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

• 跨庫(kù)join

只要是進(jìn)行切分，跨節(jié)點(diǎn)Join的問(wèn)題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類情況的發(fā)生。解決這一問(wèn)題的普遍做法是分兩次查詢實(shí)現(xiàn)。在***次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請(qǐng)求得到關(guān)聯(lián)數(shù)據(jù)。

分庫(kù)分表方案產(chǎn)品

• 跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問(wèn)題

這些是一類問(wèn)題，因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案：與解決跨節(jié)點(diǎn)join問(wèn)題的類似，分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行，因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大，對(duì)應(yīng)用程序內(nèi)存的消耗是一個(gè)問(wèn)題。

• 數(shù)據(jù)遷移，容量規(guī)劃，擴(kuò)容等問(wèn)題

來(lái)自淘寶綜合業(yè)務(wù)平臺(tái)團(tuán)隊(duì)，它利用對(duì)2的倍數(shù)取余具有向前兼容的特性(如對(duì)4取余得1的數(shù)對(duì)2取余也是1)來(lái)分配數(shù)據(jù)，避免了行級(jí)別的數(shù)據(jù)遷移，但是依然需要進(jìn)行表級(jí)別的遷移，同時(shí)對(duì)擴(kuò)容規(guī)模和分表數(shù)量都有限制。總得來(lái)說(shuō)，這些方案都不是十分的理想，多多少少都存在一些缺點(diǎn)，這也從一個(gè)側(cè)面反映出了Sharding擴(kuò)容的難度。

• ID問(wèn)題

一旦數(shù)據(jù)庫(kù)被切分到多個(gè)物理結(jié)點(diǎn)上，我們將不能再依賴數(shù)據(jù)庫(kù)自身的主鍵生成機(jī)制。一方面，某個(gè)分區(qū)數(shù)據(jù)庫(kù)自生成的ID無(wú)法保證在全局上是唯一的;另一方面，應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.

一些常見(jiàn)的主鍵生成策略

UUID

使用UUID作主鍵是最簡(jiǎn)單的方案，但是缺點(diǎn)也是非常明顯的。由于UUID非常的長(zhǎng)，除占用大量存儲(chǔ)空間外，最主要的問(wèn)題是在索引上，在建立索引和基于索引進(jìn)行查詢時(shí)都存在性能問(wèn)題。

Twitter的分布式自增ID算法Snowflake

在分布式系統(tǒng)中，需要生成全局UID的場(chǎng)合還是比較多的，twitter的snowflake解決了這種需求，實(shí)現(xiàn)也還是很簡(jiǎn)單的，除去配置信息，核心代碼就是毫秒級(jí)時(shí)間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。

跨分片的排序分頁(yè)

般來(lái)講，分頁(yè)時(shí)需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時(shí)候，我們通過(guò)分片規(guī)則可以比較容易定位到指定的分片，而當(dāng)排序字段非分片字段的時(shí)候，情況就會(huì)變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性，我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序，***再返回給用戶。如下圖所示：

 

 

 

 

中間件推薦

 

 

 

mysql中in 和exists 區(qū)別

mysql中的in語(yǔ)句是把外表和內(nèi)表作hash 連接，而exists語(yǔ)句是對(duì)外表作loop循環(huán)，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。一直大家都認(rèn)為exists比in語(yǔ)句的效率要高，這種說(shuō)法其實(shí)是不準(zhǔn)確的。這個(gè)是要區(qū)分環(huán)境的。

1. 如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。
2. 如果兩個(gè)表中一個(gè)較小，一個(gè)是大表，則子查詢表大的用exists，子查詢表小的用in。
3. not in 和not exists如果查詢語(yǔ)句使用了not in 那么內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引;而not extsts的子查詢依然能用到表上的索引。所以無(wú)論那個(gè)表大，用not exists都比not in要快。