互聯網公司面試必問的MySQL題目
互聯網公司面試必問的MySQL題目(上)
01什么是數據庫事務?如果沒有事物會有什么后果?事務的特性是什么?
事務是指作為單個邏輯工作單元執行的一系列操作，可以被看作一個單元的一系列SQL語句的集合。要么完全地執行，要么完全地不執行。
如果不對數據庫進行并發控制，可能會產生 臟讀、非重復讀、幻像讀、丟失修改的異常情況。
事務的特性(ACID)
A, atomacity 原子性 事務必須是原子工作單元;對于其數據修改，要么全都執行，要么全都不執行。通常，與某個事務關聯的操作具有共同的目標，并且是相互依賴的。如果系統只執行這些操作的一個子集，則可能會破壞事務的總體目標。原子性消除了系統處理操作子集的可能性。
C, consistency 一致性
事務將數據庫從一種一致狀態轉變為下一種一致狀態。也就是說，事務在完成時，必須使所有的數據都保持一致狀態(各種 constraint 不被破壞)。
I, isolation 隔離性 由并發事務所作的修改必須與任何其它并發事務所作的修改隔離。事務查看數據時數據所處的狀態，要么是另一并發事務修改它之前的狀態，要么是另一事務修改它之后的狀態，事務不會查看中間狀態的數據。換句話說，一個事務的影響在該事務提交前對其他事務都不可見。
D, durability 持久性
事務完成之后，它對于系統的影響是***性的。該修改即使出現致命的系統故障也將一直保持。
“A向B匯錢100”
讀出A賬號余額(500)。
A賬號扣錢操作(500-100)。
結果寫回A賬號(400)。
讀出B賬號余額(500)。
B賬號做加法操作(500+100)。
結果寫回B賬號(600)。
原子性：
保證1-6所有過程要么都執行，要么都不執行。如果異常了那么回滾。
一致性
轉賬前，A和B的賬戶中共有500+500=1000元錢。轉賬后，A和B的賬戶中共有400+600=1000元。
隔離性
在A向B轉賬的整個過程中，只要事務還沒有提交(commit)，查詢A賬戶和B賬戶的時候，兩個賬戶里面的錢的數量都不會有變化。
持久性
一旦轉賬成功(事務提交)，兩個賬戶的里面的錢就會真的發生變化
02什么是臟讀?幻讀?不可重復讀?什么是事務的隔離級別?Mysql的默認隔離級別是?
- 臟讀：事務A讀取了事務B更新的數據，然后B回滾操作，那么A讀取到的數據是臟數據
- 不可重復讀：事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新并提交，導致事務A多次讀取同一數據時，結果 不一致。
- 幻讀：系統管理員A將數據庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束后發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。
Read uncommitted
讀未提交，顧名思義，就是一個事務可以讀取另一個未提交事務的數據。
Read committed
讀提交，顧名思義，就是一個事務要等另一個事務提交后才能讀取數據。
小A去買東西(卡里有1萬元)，當他買單時(事務開啟)，系統事先檢測到他的卡里有1萬，就在這個時候!!小A的妻子要把錢全部轉出充當家用，并提交。當系統準備扣款時，再檢測卡里的金額，發現已經沒錢了(第二次檢測金額當然要等待妻子轉出金額事務提交完)。A就會很郁悶
分析：這就是讀提交，若有事務對數據進行更新(UPDATE)操作時，讀操作事務要等待這個更新操作事務提交后才能讀取數據，可以解決臟讀問題。但在這個事例中，出現了一個事務范圍內兩個相同的查詢卻返回了不同數據，這就是不可重復讀。
Repeatable read
重復讀，就是在開始讀取數據(事務開啟)時，不再允許修改操作
事例：小A去買東西(卡里有1萬元)，當他買單時(事務開啟，不允許其他事務的UPDATE修改操作)，收費系統事先檢測到他的卡里有1萬。這時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。
分析：重復讀可以解決不可重復讀問題。寫到這里，應該明白的一點就是，不可重復讀對應的是修改，即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作，而不是UPDATE操作。
什么時候會出現幻讀?
事例：小A去買東西，花了2千元，然后他的妻子去查看他的消費記錄(全表掃描FTS，妻事務開啟)，看到確實是花了2千元，就在這個時候，小A花了1萬買了一部電腦，INSERT了一條消費記錄，并提交。當妻子打印小A的消費記錄清單時(妻子事務提交)，發現花了1.2萬元，似乎出現了幻覺，這就是幻讀。
Serializable 序列化
Serializable 是***的事務隔離級別，在該級別下，事務串行化順序執行，可以避免臟讀、不可重復讀與幻讀。但是這種事務隔離級別效率低下，比較耗數據庫性能，一般不使用。
Mysql的默認隔離級別是Repeatable read。
03事物隔離是怎么實現的?
是基于鎖實現的.
有哪些鎖?分別介紹下
在DBMS中，可以按照鎖的粒度把數據庫鎖分為行級鎖(INNODB引擎)、表級鎖(MYISAM引擎)和頁級鎖(BDB引擎 )。
行級鎖
行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也***。行級鎖分為共享鎖 和 排他鎖。
特點
開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖沖突的概率***，并發度也***。
表級鎖
表級鎖是MySQL中鎖定粒度***的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級鎖定。表級鎖定分為表共享讀鎖(共享鎖)與表獨占寫鎖(排他鎖)。
特點
開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發出鎖沖突的概率***，并發度***。
頁級鎖
頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
特點
開銷和加鎖時間界于表鎖和行鎖之間;會出現死鎖;鎖定粒度界于表鎖和行鎖之間，并發度一般
04什么是死鎖?怎么解決?(前幾問題是我個人最喜歡的連環炮，基本可以看出面試者的基礎功)
死鎖是指兩個或多個事務在同一資源上相互占用，并請求鎖定對付的資源，從而導致惡性循環的現象。
常見的解決死鎖的方法
- 如果不同程序會并發存取多個表，盡量約定以相同的順序訪問表，可以大大降低死鎖機會。
- 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率;
- 對于非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率;
如果業務處理不好可以用分布式事務鎖或者使用樂觀鎖
05SQL的生命周期?關鍵字的先后順序?
- 應用服務器與數據庫服務器建立一個連接
- 數據庫進程拿到請求sql
- 解析并生成執行計劃，執行
- 讀取數據到內存并進行邏輯處理
- 通過步驟一的連接，發送結果到客戶端
- 關掉連接，釋放資源
- FROM：對 FROM 子句中的前兩個表執行笛卡爾積(交叉聯接)，生成虛擬表 VT1。
- ON：對 VT1 應用 ON 篩選器，只有那些使為真才被插入到 TV2。
- OUTER (JOIN):如果指定了 OUTER JOIN(相對于 CROSS JOIN 或 INNER JOIN)，保留表中未找到匹配的行將作為外部行添加到 VT2，生成 TV3。如果 FROM 子句包含兩個以上的表，則對上一個聯接生成的結果表和下一個表重復執行步驟 1 到步驟 3，直到處理完所有的表位置。
- WHERE：對 TV3 應用 WHERE 篩選器，只有使為 true 的行才插入 TV4。
- GROUP BY：按 GROUP BY 子句中的列列表對 TV4 中的行進行分組，生成 TV5。
- CUTE|ROLLUP：把超組插入 VT5，生成 VT6。
- HAVING：對 VT6 應用 HAVING 篩選器，只有使為 true 的組插入到 VT7。
- SELECT：處理 SELECT 列表，產生 VT8。
- DISTINCT：將重復的行從 VT8 中刪除，產品 VT9。
- ORDER BY：將 VT9 中的行按 ORDER BY 子句中的列列表順序，生成一個游標(VC10)。
- TOP：從 VC10 的開始處選擇指定數量或比例的行，生成表 TV11，并返回給調用者。
06什么是樂觀鎖?悲觀鎖?實現方式?
悲觀鎖：
悲觀鎖指對數據被意外修改持保守態度，依賴數據庫原生支持的鎖機制來保證當前事務處理的安全性，防止其他并發事務對目標數據的破壞或破壞其他并發事務數據，將在事務開始執行前或執行中申請鎖定，執行完后再釋放鎖定。這對于長事務來講，可能會嚴重影響系統的并發處理能力。 自帶的數據庫事務就是典型的悲觀鎖。
樂觀鎖：
樂觀鎖(Optimistic Lock)，顧名思義，就是很樂觀，每次去拿數據的時候都認為別人不會修改，所以不會上鎖，但是在提交更新的時候會判斷一下在此期間別人有沒有去更新這個數據。樂觀鎖適用于讀多寫少的應用場景，這樣可以提高吞吐量。
一般是加一個版本號字段 每次更新時候比較版本號
07大數據情況下如何做分頁?
可以參考阿里巴巴java開發手冊上的答案
08什么是數據庫連接池?
從上一個sql生命周期題目，可以看到其中的連接在里面發揮著重大作用，但頻繁的創建和銷毀，非常浪費系統資源。由于數據庫更適合長連接，也就有個連接池，能對連接復用，維護連接對象、分配、管理、釋放，也可以避免創建大量的連接對DB引發的各種問題;另外通過請求排隊，也緩解對DB的沖擊。
互聯網公司面試必問的MySQL題目(下)
什么是數據庫索引?索引有哪幾種類型?什么是最左前綴原則?索引算法有哪些?有什么區別?
索引是對數據庫表中一列或多列的值進行排序的一種結構。一個非常恰當的比喻就是書的目錄頁與書的正文內容之間的關系，為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。
主鍵索引:
數據列不允許重復，不允許為NULL.一個表只能有一個主鍵。
唯一索引:
數據列不允許重復，允許為NULL值，一個表允許多個列創建唯一索引。
可以通過
- ALTER TABLE table_name ADD UNIQUE (column);
創建唯一索引
可以通過
- ALTER TABLE table_name ADD UNIQUE (column1,column2);
創建唯一組合索引
普通索引:
基本的索引類型，沒有唯一性的限制，允許為NULL值。
可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引
可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引
全文索引：
是目前搜索引擎使用的一種關鍵技術。
可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引
最左前綴
- 顧名思義，就是最左優先，在創建多列索引時，要根據業務需求，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數據庫索引算法，也是mysql默認的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符，只要它的查詢條件是一個不以通配符開頭的常量，
例如：
- select * from user where name like 'jack%';
如果一通配符開頭，或者沒有使用常量，則不會使用索引，例如：
- select * from user where name like '%jack';
Hash
Hash索引只能用于對等比較，例如=,<=>(相當于=)操作符。由于是一次定位數據，不像BTree索引需要從根節點到枝節點，***才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高于BTree索引。
BTree索引是最常用的mysql數據庫索引算法，也是mysql默認的算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用于like操作符
例如：
只要它的查詢條件是一個不以通配符開頭的常量select * from user where name like 'jack%'; 如果一通配符開頭，或者沒有使用常量，則不會使用索引，例如： select * from user where name like '%jack';
Hash
Hash索引只能用于對等比較，例如=,<=>(相當于=)操作符。由于是一次定位數據，不像BTree索引需要從根節點到枝節點，***才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高于BTree索引。
索引設計的原則?
- 適合索引的列是出現在where子句中的列，或者連接子句中指定的列
- 基數較小的類，索引效果較差，沒有必要在此列建立索引
- 使用短索引，如果對長字符串列進行索引，應該指定一個前綴長度，這樣能夠節省大量索引空間
- 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利于查詢即可。
如何定位及優化SQL語句的性能問題?
對于低性能的SQL語句的定位，最重要也是最有效的方法就是使用執行計劃。
我們知道，不管是哪種數據庫，或者是哪種數據庫引擎，在對一條SQL語句進行執行的過程中都會做很多相關的優化，對于查詢語句，最重要的優化方式就是使用索引。
而執行計劃，就是顯示數據庫引擎對于SQL語句的執行的詳細情況，其中包含了是否使用索引，使用什么索引，使用的索引的相關信息等。
執行計劃包含的信息
id
有一組數字組成。表示一個查詢中各個子查詢的執行順序;
- id相同執行順序由上至下。
- id不同，id值越大優先級越高，越先被執行。
- id為null時表示一個結果集，不需要使用它查詢，常出現在包含union等查詢語句中。
select_type
每個子查詢的查詢類型，一些常見的查詢類型。
| id | select_type | description |
|---|---|---|
| 1 | SIMPLE | 不包含任何子查詢或union等查詢 |
| 2 | PRIMARY | 包含子查詢最外層查詢就顯示為 PRIMARY |
| 3 | SUBQUERY | 在select或 where字句中包含的查詢 |
| 4 | DERIVED | from字句中包含的查詢 |
| 5 | UNION | 出現在union后的查詢語句中 |
| 6 | UNION RESULT | 從UNION中獲取結果集，例如上文的第三個例子 |
table
查詢的數據表，當從衍生表中查數據時會顯示 x 表示對應的執行計劃id
partitions
表分區、表創建的時候可以指定通過那個列進行表分區。 舉個例子：
- 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 掃描全表數據
- index 遍歷索引
- range 索引范圍查找
- index_subquery 在子查詢中使用 ref
- unique_subquery 在子查詢中使用 eq_ref
- ref_or_null 對Null進行索引的優化的 ref
- fulltext 使用全文索引
- ref 使用非唯一索引查找數據
- eq_ref 在join查詢中使用PRIMARY KEYorUNIQUE NOT NULL索引關聯。
possible_keys
可能使用的索引，注意不一定會使用。查詢涉及到的字段上若存在索引，則該索引將被列出來。當該列為 NULL時就要考慮當前的SQL是否需要優化了。
key
顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示為NULL。
TIPS:查詢中若使用了覆蓋索引(覆蓋索引：索引的數據覆蓋了需要查詢的所有數據)，則該索引僅出現在key列表中
key_length
索引長度
ref
表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
rows
返回估算的結果集數目，并不是一個準確的值。
extra
extra的信息非常豐富，常見的有：
- Using index 使用覆蓋索引
- Using where 使用了用where子句來過濾結果集
- Using filesort 使用文件排序，使用非索引列進行排序時出現，非常消耗性能，盡量優化。
- Using temporary 使用了臨時表
sql優化的目標可以參考阿里開發手冊
某個表有近千萬數據，CRUD比較慢，如何優化?分庫分表了是怎么做的?分表分庫了有什么問題?有用到中間件么?他們的原理知道么?
數據***別之多，占用的存儲空間也比較大，可想而知它不會存儲在一塊連續的物理空間上，而是鏈式存儲在多個碎片的物理空間上。可能對于長字符串的比較，就用更多的時間查找與比較，這就導致用更多的時間。
- 可以做表拆分，減少單表字段數量，優化表結構。
- 在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。
主要兩種拆分 垂直拆分，水平拆分。
垂直分表
也就是“大表拆小表”，基于列字段進行的。一般是表中的字段較多，將不常用的， 數據較大，長度較長(比如text類型字段)的拆分到“擴展表“。 一般是針對那種幾百列的大表，也避免查詢時，數據量太大造成的“跨頁”問題。
垂直分庫針對的是一個系統中的不同業務進行拆分，比如用戶User一個庫，商品Producet一個庫，訂單Order一個庫。 切分后，要放在多個服務器上，而不是一個服務器上。為什么? 我們想象一下，一個購物網站對外提供服務，會有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫上的，這會讓數據庫的單庫處理能力成為瓶頸。按垂直分庫后，如果還是放在一個數據庫服務器上， 隨著用戶量增大，這會讓單個數據庫的處理能力成為瓶頸，還有單個服務器的磁盤空間，內存，tps等非常吃緊。 所以我們要拆分到多個服務器上，這樣上面的問題都解決了，以后也不會面對單機資源問題。
數據庫業務層面的拆分，和服務的“治理”，“降級”機制類似，也能對不同業務的數據分別的進行管理，維護，監控，擴展等。 數據庫往往最容易成為應用系統的瓶頸，而數據庫本身屬于“有狀態”的，相對于Web和應用服務器來講，是比較難實現“橫向擴展”的。 數據庫的連接資源比較寶貴且單機處理能力也有限，在高并發場景下，垂直分庫一定程度上能夠突破IO、連接數及單機硬件資源的瓶頸。
水平分表
針對數據量巨大的單張表(比如訂單表)，按照某種規則(RANGE,HASH取模等)，切分到多張表里面去。 但是這些表還是在同一個庫中，所以庫級別的數據庫操作還是有IO瓶頸。不建議采用。
水平分庫分表
將單張表的數據切分到多個服務器上去，每個服務器具有相應的庫與表，只是表中數據集合不同。 水平分庫分表能夠有效的緩解單機和單庫的性能瓶頸和壓力，突破IO、連接數、硬件資源等的瓶頸。
水平分庫分表切分規則
1.RANGE從
0到10000一個表，10001到20000一個表;
2.HASH取模
一個商場系統，一般都是將用戶，訂單作為主表，然后將和它們相關的作為附表，這樣不會造成跨庫事務之類的問題。 取用戶id，然后hash取模，分配到不同的數據庫上。
3.地理區域
比如按照華東，華南，華北這樣來區分業務，七牛云應該就是如此。
4.時間
按照時間切分，就是將6個月前，甚至一年前的數據切出去放到另外的一張表，因為隨著時間流逝，這些表的數據 被查詢的概率變小，所以沒必要和“熱數據”放在一起，這個也是“冷熱數據分離”。
分庫分表后面臨的問題
- 事務支持
分庫分表后，就成了分布式事務了。如果依賴數據庫本身的分布式事務管理功能去執行事務，將付出高昂的性能代價; 如果由應用程序去協助控制，形成程序邏輯上的事務，又會造成編程方面的負擔。
- 跨庫join
只要是進行切分，跨節點Join的問題是不可避免的。但是良好的設計和切分卻可以減少此類情況的發生。解決這一問題的普遍做法是分兩次查詢實現。在***次查詢的結果集中找出關聯數據的id,根據這些id發起第二次請求得到關聯數據。
分庫分表方案產品
- 跨節點的count,order by,group by以及聚合函數問題
這些是一類問題，因為它們都需要基于全部數據集合進行計算。多數的代理都不會自動處理合并工作。解決方案：與解決跨節點join問題的類似，分別在各個節點上得到結果后在應用程序端進行合并。和join不同的是每個結點的查詢可以并行執行，因此很多時候它的速度要比單一大表快很多。但如果結果集很大，對應用程序內存的消耗是一個問題。
- 數據遷移，容量規劃，擴容等問題
來自淘寶綜合業務平臺團隊，它利用對2的倍數取余具有向前兼容的特性(如對4取余得1的數對2取余也是1)來分配數據，避免了行級別的數據遷移，但是依然需要進行表級別的遷移，同時對擴容規模和分表數量都有限制。總得來說，這些方案都不是十分的理想，多多少少都存在一些缺點，這也從一個側面反映出了Sharding擴容的難度。
- ID問題
一旦數據庫被切分到多個物理結點上，我們將不能再依賴數據庫自身的主鍵生成機制。一方面，某個分區數據庫自生成的ID無法保證在全局上是唯一的;另一方面，應用程序在插入數據之前需要先獲得ID,以便進行SQL路由.
一些常見的主鍵生成策略
UUID
使用UUID作主鍵是最簡單的方案，但是缺點也是非常明顯的。由于UUID非常的長，除占用大量存儲空間外，最主要的問題是在索引上，在建立索引和基于索引進行查詢時都存在性能問題。
Twitter的分布式自增ID算法Snowflake
在分布式系統中，需要生成全局UID的場合還是比較多的，twitter的snowflake解決了這種需求，實現也還是很簡單的，除去配置信息，核心代碼就是毫秒級時間41位 機器ID 10位 毫秒內序列12位。
跨分片的排序分頁
般來講，分頁時需要按照指定字段進行排序。當排序字段就是分片字段的時候，我們通過分片規則可以比較容易定位到指定的分片，而當排序字段非分片字段的時候，情況就會變得比較復雜了。為了最終結果的準確性，我們需要在不同的分片節點中將數據進行排序并返回，并將不同分片返回的結果集進行匯總和再次排序，***再返回給用戶。如下圖所示：
中間件推薦
mysql中in 和exists 區別
mysql中的in語句是把外表和內表作hash 連接，而exists語句是對外表作loop循環，每次loop循環再對內表進行查詢。一直大家都認為exists比in語句的效率要高，這種說法其實是不準確的。這個是要區分環境的。
- 如果查詢的兩個表大小相當，那么用in和exists差別不大。
- 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in。
- not in 和not exists如果查詢語句使用了not in 那么內外表都進行全表掃描，沒有用到索引;而not extsts的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。
