作者:Snailclimb 鏈接:https://segmentfault.com/a/1190000019619667?utm_source=tuicool&utm_medium=referral
前言:
這篇花文章是我花了幾天時間對之前總結的MySQL知識點做了完善後的產物,這篇文章可以用來回顧MySQL基礎知識以及備戰MySQL常見面試問題。
什麼是MySQL?
MySQL 是一種關係型資料庫,在Java企業級開發中非常常用,因為 MySQL 是開源免費的,並且方便擴展。阿里巴巴資料庫系統也大量用到了 MySQL,因此它的穩定性是有保障的。MySQL是開放源代碼的,因此任何人都可以在 GPL(General Public License) 的許可下下載並根據個性化的需要對其進行修改。MySQL的默認埠號是3306。
事務相關
什麼是事務?
事務是邏輯上的一組操作,要麼都執行,要麼都不執行。
事務最經典也經常被拿出來說例子就是轉賬了。假如小明要給小紅轉賬1000元,這個轉賬會涉及到兩個關鍵操作就是:將小明的餘額減少1000元,將小紅的餘額增加1000元。萬一在這兩個操作之間突然出現錯誤比如銀行系統崩潰,導致小明餘額減少而小紅的餘額沒有增加,這樣就不對了。事務就是保證這兩個關鍵操作要麼都成功,要麼都要失敗。
事物的四大特性(ACID)介紹一下?
並發事務帶來哪些問題?
在典型的應用程序中,多個事務並發運行,經常會操作相同的數據來完成各自的任務(多個用戶對統一數據進行操作)。並發雖然是必須的,但可能會導致以下的問題。
不可重複度和幻讀區別:
不可重複讀的重點是修改,幻讀的重點在於新增或者刪除。
例1(同樣的條件, 你讀取過的數據, 再次讀取出來發現值不一樣了 ):事務1中的A先生讀取自己的工資為 1000的操作還沒完成,事務2中的B先生就修改了A的工資為2000,導 致A再讀自己的工資時工資變為 2000;這就是不可重複讀。
例2(同樣的條件, 第1次和第2次讀出來的記錄數不一樣 ):假某工資單表中工資大於3000的有4人,事務1讀取了所有工資大於3000的人,共查到4條記錄,這時事務2 又插入了一條工資大於3000的記錄,事務1再次讀取時查到的記錄就變為了5條,這樣就導致了幻讀。
事務隔離級別有哪些?MySQL的默認隔離級別是?
SQL 標準定義了四個隔離級別:
隔離級別 臟讀 不可重複讀 幻影讀 READ-UNCOMMITTED √ √ √ READ-COMMITTED × √ √ REPEATABLE-READ × × √ SERIALIZABLE × × × MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ(可重讀)。我們可以通過SELECT @@tx_isolation;命令來查看
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 複製代碼
這裡需要注意的是:與 SQL 標準不同的地方在於InnoDB 存儲引擎在 **REPEATABLE-READ(可重讀)事務隔離級別下使用的是Next-Key Lock 鎖演算法,因此可以避免幻讀的產生,這與其他資料庫系統(如 SQL Server)是不同的。所以說InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ(可重讀) 已經可以完全保證事務的隔離性要求,即達到了 SQL標準的SERIALIZABLE(可串列化)**隔離級別。
因為隔離級別越低,事務請求的鎖越少,所以大部分資料庫系統的隔離級別都是READ-COMMITTED(讀取提交內容):,但是你要知道的是InnoDB 存儲引擎默認使用 **REPEATABLE-READ(可重讀)**並不會有任何性能損失。
InnoDB 存儲引擎在 分散式事務 的情況下一般會用到**SERIALIZABLE(可串列化)**隔離級別。
索引相關
為什麼索引能提高查詢速度
以下內容整理自: 地址: http://juejin.im/post/5b55b8… 作者 :Java3y
先從 MySQL 的基本存儲結構說起
MySQL的基本存儲結構是頁(記錄都存在頁裡邊):
所以說,如果我們寫select * from user where indexname = xxx這樣沒有進行任何優化的sql語句,默認會這樣做:
很明顯,在數據量很大的情況下這樣查找會很慢!這樣的時間複雜度為O(n)。
索引做了些什麼可以讓我們查詢加快速度呢?其實就是將無序的數據變成有序(相對):
要找到id為8的記錄簡要步驟:
很明顯的是:沒有用索引我們是需要遍歷雙向鏈表來定位對應的頁,現在通過 「目錄」 就可以很快地定位到對應的頁上了!(二分查找,時間複雜度近似為O(logn))
其實底層結構就是B+樹,B+樹作為樹的一種實現,能夠讓我們很快地查找出對應的記錄。
以下內容整理自:《Java工程師修鍊之道》
什麼是最左前綴原則?
MySQL中的索引可以以一定順序引用多列,這種索引叫作聯合索引。如User表的name和city加聯合索引就是(name,city),而最左前綴原則指的是,如果查詢的時候查詢條件精確匹配索引的左邊連續一列或幾列,則此列就可以被用到。如下:
select * from user where name=xx and city=xx ; //可以命中索引 select * from user where name=xx ; // 可以命中索引 select * from user where city=xx ; // 無法命中索引 複製代碼
這裡需要注意的是,查詢的時候如果兩個條件都用上了,但是順序不同,如 city= xx and name =xx,那麼現在的查詢引擎會自動優化為匹配聯合索引的順序,這樣是能夠命中索引的。
由於最左前綴原則,在創建聯合索引時,索引欄位的順序需要考慮欄位值去重之後的個數,較多的放前面。ORDER BY子句也遵循此規則。
注意避免冗餘索引
冗餘索引指的是索引的功能相同,能夠命中就肯定能命中 ,那麼 就是冗餘索引如(name,city )和(name )這兩個索引就是冗餘索引,能夠命中後者的查詢肯定是能夠命中前者的 在大多數情況下,都應該盡量擴展已有的索引而不是創建新索引。
MySQLS.7 版本後,可以通過查詢 sys 庫的 schema_redundant_indexes 表來查看冗餘索引
Mysql如何為表欄位添加索引?
1.添加PRIMARY KEY(主鍵索引)
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 複製代碼
2.添加UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 複製代碼
3.添加INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 複製代碼
4.添加FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 複製代碼
5.添加多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) 複製代碼
存儲引擎
一些常用命令
查看MySQL提供的所有存儲引擎
mysql> show engines; 複製代碼
從上圖我們可以查看出 MySQL 當前默認的存儲引擎是InnoDB,並且在5.7版本所有的存儲引擎中只有 InnoDB 是事務性存儲引擎,也就是說只有 InnoDB 支持事務。
查看MySQL當前默認的存儲引擎
我們也可以通過下面的命令查看默認的存儲引擎。
mysql> show variables like %storage_engine%; 複製代碼
查看錶的存儲引擎
show table status like "table_name" ; 複製代碼
MyISAM和InnoDB區別
MyISAM是MySQL的默認資料庫引擎(5.5版之前)。雖然性能極佳,而且提供了大量的特性,包括全文索引、壓縮、空間函數等,但MyISAM不支持事務和行級鎖,而且最大的缺陷就是崩潰後無法安全恢復。不過,5.5版本之後,MySQL引入了InnoDB(事務性資料庫引擎),MySQL 5.5版本後默認的存儲引擎為InnoDB。
大多數時候我們使用的都是 InnoDB 存儲引擎,但是在某些情況下使用 MyISAM 也是合適的比如讀密集的情況下。(如果你不介意 MyISAM 崩潰回復問題的話)。
兩者的對比:
《MySQL高性能》上面有一句話這樣寫到:
不要輕易相信「MyISAM比InnoDB快」之類的經驗之談,這個結論往往不是絕對的。在很多我們已知場景中,InnoDB的速度都可以讓MyISAM望塵莫及,尤其是用到了聚簇索引,或者需要訪問的數據都可以放入內存的應用。
一般情況下我們選擇 InnoDB 都是沒有問題的,但是某事情況下你並不在乎可擴展能力和並發能力,也不需要事務支持,也不在乎崩潰後的安全恢復問題的話,選擇MyISAM也是一個不錯的選擇。但是一般情況下,我們都是需要考慮到這些問題的。
樂觀鎖與悲觀鎖的區別
悲觀鎖
總是假設最壞的情況,每次去拿數據的時候都認為別人會修改,所以每次在拿數據的時候都會上鎖,這樣別人想拿這個數據就會阻塞直到它拿到鎖(共享資源每次只給一個線程使用,其它線程阻塞,用完後再把資源轉讓給其它線程)。傳統的關係型資料庫裡邊就用到了很多這種鎖機制,比如行鎖,表鎖等,讀鎖,寫鎖等,都是在做操作之前先上鎖。Java中synchronized和ReentrantLock等獨佔鎖就是悲觀鎖思想的實現。
樂觀鎖
總是假設最好的情況,每次去拿數據的時候都認為別人不會修改,所以不會上鎖,但是在更新的時候會判斷一下在此期間別人有沒有去更新這個數據,可以使用版本號機制和CAS演算法實現。樂觀鎖適用於多讀的應用類型,這樣可以提高吞吐量,像資料庫提供的類似於write_condition機制,其實都是提供的樂觀鎖。在Java中java.util.concurrent.atomic包下面的原子變數類就是使用了樂觀鎖的一種實現方式CAS實現的。
兩種鎖的使用場景
從上面對兩種鎖的介紹,我們知道兩種鎖各有優缺點,不可認為一種好於另一種,像樂觀鎖適用於寫比較少的情況下(多讀場景),即衝突真的很少發生的時候,這樣可以省去了鎖的開銷,加大了系統的整個吞吐量。但如果是多寫的情況,一般會經常產生衝突,這就會導致上層應用會不斷的進行retry,這樣反倒是降低了性能,所以一般多寫的場景下用悲觀鎖就比較合適。
樂觀鎖常見的兩種實現方式
樂觀鎖一般會使用版本號機制或CAS演算法實現。
1. 版本號機制
一般是在數據表中加上一個數據版本號version欄位,表示數據被修改的次數,當數據被修改時,version值會加一。當線程A要更新數據值時,在讀取數據的同時也會讀取version值,在提交更新時,若剛才讀取到的version值為當前資料庫中的version值相等時才更新,否則重試更新操作,直到更新成功。
舉一個簡單的例子: 假設資料庫中帳戶信息表中有一個 version 欄位,當前值為 1 ;而當前帳戶餘額欄位( balance )為 $100 。
這樣,就避免了操作員 B 用基於 version=1 的舊數據修改的結果覆蓋操作員A 的操作結果的可能。
2. CAS演算法
即compare and swap(比較與交換),是一種有名的無鎖演算法。無鎖編程,即不使用鎖的情況下實現多線程之間的變數同步,也就是在沒有線程被阻塞的情況下實現變數的同步,所以也叫非阻塞同步(Non-blocking Synchronization)。CAS演算法涉及到三個操作數
當且僅當 V 的值等於 A時,CAS通過原子方式用新值B來更新V的值,否則不會執行任何操作(比較和替換是一個原子操作)。一般情況下是一個自旋操作,即不斷的重試。
關於自旋鎖,大家可以看一下這篇文章,非常不錯:《 面試必備之深入理解自旋鎖》
樂觀鎖的缺點
ABA 問題是樂觀鎖一個常見的問題
1 ABA 問題
如果一個變數V初次讀取的時候是A值,並且在準備賦值的時候檢查到它仍然是A值,那我們就能說明它的值沒有被其他線程修改過了嗎?很明顯是不能的,因為在這段時間它的值可能被改為其他值,然後又改回A,那CAS操作就會誤認為它從來沒有被修改過。這個問題被稱為CAS操作的 "ABA"問題。
JDK 1.5 以後的 AtomicStampedReference 類就提供了此種能力,其中的 compareAndSet 方法就是首先檢查當前引用是否等於預期引用,並且當前標誌是否等於預期標誌,如果全部相等,則以原子方式將該引用和該標誌的值設置為給定的更新值。
2 循環時間長開銷大
自旋CAS(也就是不成功就一直循環執行直到成功)如果長時間不成功,會給CPU帶來非常大的執行開銷。 如果JVM能支持處理器提供的pause指令那麼效率會有一定的提升,pause指令有兩個作用,第一它可以延遲流水線執行指令(de-pipeline),使CPU不會消耗過多的執行資源,延遲的時間取決於具體實現的版本,在一些處理器上延遲時間是零。第二它可以避免在退出循環的時候因內存順序衝突(memory order violation)而引起CPU流水線被清空(CPU pipeline flush),從而提高CPU的執行效率。
3 只能保證一個共享變數的原子操作
CAS 只對單個共享變數有效,當操作涉及跨多個共享變數時 CAS 無效。但是從 JDK 1.5開始,提供了AtomicReference類來保證引用對象之間的原子性,你可以把多個變數放在一個對象裏來進行 CAS 操作.所以我們可以使用鎖或者利用AtomicReference類把多個共享變數合併成一個共享變數來操作。
鎖機制與InnoDB鎖演算法
MyISAM和InnoDB存儲引擎使用的鎖:
表級鎖和行級鎖對比:
詳細內容可以參考: Mysql鎖機制簡單瞭解一下
InnoDB存儲引擎的鎖的演算法有三種:
相關知識點:
大表優化
當MySQL單表記錄數過大時,資料庫的CRUD性能會明顯下降,一些常見的優化措施如下:
1. 限定數據的範圍
務必禁止不帶任何限制數據範圍條件的查詢語句。比如:我們當用戶在查詢訂單歷史的時候,我們可以控制在一個月的範圍內;
2. 讀/寫分離
經典的資料庫拆分方案,主庫負責寫,從庫負責讀;
3. 垂直分區
根據資料庫裡面數據表的相關性進行拆分。 例如,用戶表中既有用戶的登錄信息又有用戶的基本信息,可以將用戶表拆分成兩個單獨的表,甚至放到單獨的庫做分庫。
簡單來說垂直拆分是指數據表列的拆分,把一張列比較多的表拆分為多張表。 如下圖所示,這樣來說大家應該就更容易理解了。
4. 水平分區
保持數據表結構不變,通過某種策略存儲數據分片。這樣每一片數據分散到不同的表或者庫中,達到了分散式的目的。 水平拆分可以支撐非常大的數據量。
水平拆分是指數據錶行的拆分,表的行數超過200萬行時,就會變慢,這時可以把一張的表的數據拆成多張表來存放。舉個例子:我們可以將用戶信息表拆分成多個用戶信息表,這樣就可以避免單一表數據量過大對性能造成影響。
水平拆分可以支持非常大的數據量。需要注意的一點是:分表僅僅是解決了單一表數據過大的問題,但由於表的數據還是在同一臺機器上,其實對於提升MySQL並發能力沒有什麼意義,所以 水平拆分最好分庫 。
水平拆分能夠 支持非常大的數據量存儲,應用端改造也少,但 分片事務難以解決 ,跨節點Join性能較差,邏輯複雜。《Java工程師修鍊之道》的作者推薦 盡量不要對數據進行分片,因為拆分會帶來邏輯、部署、運維的各種複雜度 ,一般的數據表在優化得當的情況下支撐千萬以下的數據量是沒有太大問題的。如果實在要分片,盡量選擇客戶端分片架構,這樣可以減少一次和中間件的網路I/O。
下面補充一下資料庫分片的兩種常見方案:
推薦閱讀: