[[259658]]

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

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

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

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

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

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

C, consistency 一致性

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

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

D, durability 持久性

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

 

 

 

[[259659]]

 

“A向B匯錢100”

讀出A賬號余額(500)。

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

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

讀出B賬號余額(500)。

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

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

原子性：

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

一致性

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

隔離性

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

持久性

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

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

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

Read uncommitted

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

Read committed

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

 

[[259659]]

 

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

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

Repeatable read

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

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

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

什么時候會出現(xiàn)幻讀?

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

Serializable 序列化

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

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

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

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

有哪些鎖?分別介紹下

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

行級鎖

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

特點(diǎn)

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

表級鎖

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

特點(diǎn)

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

頁級鎖

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

特點(diǎn)

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

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

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

常見的解決死鎖的方法

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

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

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

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

 

 

 

 

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

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

悲觀鎖：

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

樂觀鎖：

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

一般是加一個版本號字段 每次更新時候比較版本號

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

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

 

 

 

 

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

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

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

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

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

主鍵索引:

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

唯一索引:

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

可以通過 

ALTER TABLE table_name ADD UNIQUE (column); 

創(chuàng)建唯一索引

可以通過 

ALTER TABLE table_name ADD UNIQUE (column1,column2); 

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

普通索引:

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

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

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

全文索引：

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

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

最左前綴

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

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    沒有使用索引 
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ù)庫索引算法，也是mysql默認(rèn)的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個不以通配符開頭的常量，

例如： 

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

如果一通配符開頭，或者沒有使用常量，則不會使用索引，例如： 

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

Hash

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

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

例如：

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

Hash

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

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

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

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

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

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

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

 

 

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

id

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

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

select_type

每個子查詢的查詢類型，一些常見的查詢類型。

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

table

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

partitions

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

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(非常重要，可以看到有沒有走索引)

訪問類型

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

possible_keys

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

key

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

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

key_length

索引長度

ref

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

rows

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

extra

extra的信息非常豐富，常見的有：

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

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

 

 

 

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

數(shù)據(jù)***別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續(xù)的物理空間上，而是鏈?zhǔn)酱鎯υ诙鄠€碎片的物理空間上?？赡軐τ陂L字符串的比較，就用更多的時間查找與比較，這就導(dǎo)致用更多的時間。

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

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

 

 

 

垂直分表

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

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

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

水平分表

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

水平分庫分表

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

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

        1.RANGE從

0到10000一個表，10001到20000一個表;

        2.HASH取模

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

        3.地理區(qū)域

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

        4.時間

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

分庫分表后面臨的問題

• 事務(wù)支持

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

• 跨庫join

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

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

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

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

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

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

• ID問題

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

一些常見的主鍵生成策略

UUID

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

Twitter的分布式自增ID算法Snowflake

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

跨分片的排序分頁

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

 

 

 

 

中間件推薦

 

 

 

mysql中in 和exists 區(qū)別

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

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