前言
Mysql作為目前互聯(lián)網(wǎng)工作的主流數(shù)據(jù)庫孵延,有著其不容撼動(dòng)的地位,之前面試過一些公司亲配,對(duì)于程序員來說尘应,企業(yè)對(duì)于Mysql的技能要求還是比較高的。所以有必要系統(tǒng)深入研究下Mysql吼虎。于是乎就有了這一系列博客的誕生犬钢。這些博客內(nèi)容主要是結(jié)合我自己工作中所用的的東西,以及《高性能Myql》這本書的讀書筆記思灰。
購買地址玷犹,請(qǐng)支持正版圖書。
1.1洒疚、Mysql邏輯結(jié)構(gòu)
-
Mysql是三層的邏輯架構(gòu)歹颓,如下圖
- 第一層:服務(wù)層(為客戶端服務(wù)):為請(qǐng)求做連接處理,授權(quán)認(rèn)證油湖,安全等巍扛。
- 第二層:Mysql核心服務(wù)層:主要提供,查詢解析乏德、分析撤奸、優(yōu)化、緩存以及內(nèi)置函數(shù)喊括,跨存儲(chǔ)引擎功能(存儲(chǔ)過程寂呛、視圖、觸發(fā)器)
- 第三層:存儲(chǔ)引擎層瘾晃,負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取
1.1.1、連接管理與安全性
這里有第一層處理幻妓,每個(gè)客戶端的連接都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程蹦误,連接的查詢?cè)谶@個(gè)線程中單獨(dú)進(jìn)行。
1.1.2肉津、優(yōu)化與執(zhí)行
此處由第二層强胰,中間層處理,該處的優(yōu)化器負(fù)責(zé)創(chuàng)建內(nèi)部數(shù)據(jù)結(jié)構(gòu)妹沙,然后優(yōu)化偶洋,包括重寫查詢,決定表的讀取順序距糖,以及選擇合適的索引玄窝。
1.2牵寺、并發(fā)控制
并發(fā)是個(gè)老生常談的問題,不管在java還是mysql以及oracle中恩脂,都需要保證并發(fā)時(shí)數(shù)據(jù)的一致性帽氓。面對(duì)并發(fā)問題,不可避免的都需要用到鎖俩块,并發(fā)的優(yōu)化很多時(shí)候也就是鎖的優(yōu)化黎休。
1.2.1、讀寫鎖
- 讀鎖:共享玉凯,互不阻塞势腮,即多用戶同一時(shí)刻讀統(tǒng)一資源,互不干擾漫仆。
- 寫鎖:排他捎拯,一個(gè)寫鎖會(huì)阻塞其他的讀和寫,安全策略只有這樣才能保證同一時(shí)刻只有一個(gè)用戶能寫入歹啼,并防止其他用戶讀取正在寫入的同一資源玄渗,避免臟讀。
1.2.2狸眼、鎖力度
一種優(yōu)化的策略藤树,對(duì)于不同的鎖提供不同的力度,讓鎖定對(duì)象更有選擇性拓萌。當(dāng)然加鎖的操作也增加系統(tǒng)的開銷岁钓。包括(獲得鎖,檢查鎖是否解除微王,是否鎖)屡限。下面介紹兩種最重要的鎖力度
- 表鎖(table lock)
顧名思義就是將整張表鎖定,Mysql中最基本的鎖策略炕倘,并且是開銷最小的策略钧大,加鎖之后,整個(gè)表數(shù)據(jù)受到影響罩旋,不利于并發(fā)啊央,寫鎖優(yōu)先級(jí)高于讀鎖,因此一個(gè)寫鎖請(qǐng)求可能會(huì)被插入到讀鎖的隊(duì)列前面涨醋。服務(wù)器也會(huì)使用ALTER TABLE之類的語句使用表鎖瓜饥。 - 行級(jí)鎖(row lock)
支持高并發(fā),同事帶來最大的鎖開銷浴骂。只有存儲(chǔ)引擎實(shí)現(xiàn)乓土,第二層不會(huì)實(shí)現(xiàn)。
1.3、事務(wù)
事務(wù)也是數(shù)據(jù)庫中一個(gè)老生常談的問題趣苏,我們常辰葡啵口頭說的ACID,或者可以說是一個(gè)獨(dú)立的工作單元拦键,對(duì)外只有成功和失敗的結(jié)果谣光,不會(huì)出現(xiàn)部分成功或者失敗。也即提交(commit)或者回退(rollback)兩種操作芬为。
- A(Atomicity-原子性):不可分割的單元萄金,要么成功,要么失敗媚朦。
- C(Consistency-一致性):主要強(qiáng)調(diào)的是氧敢,如果在執(zhí)行事務(wù)之前數(shù)據(jù)庫是一致的,那么在執(zhí)行事務(wù)之后數(shù)據(jù)庫也還是一致的
- I(Isolation-隔離性):事務(wù)彼此之間沒有影響询张,即在最終提交前對(duì)其他事務(wù)不可見
- D(Durability-持久性):一旦事務(wù)提交孙乖,其修改會(huì)永久保存到數(shù)據(jù)庫中。
不是所有的數(shù)據(jù)庫都支持事務(wù)份氧,像現(xiàn)在大火的nosql大多都不支持事務(wù)唯袄,而mysql不同的引擎對(duì)事務(wù)的支持也不一樣,像默認(rèn)的InnoDB是支持事務(wù)的蜗帜,而Myisam這一的引擎就不支持事務(wù)恋拷。這個(gè)需要根據(jù)業(yè)務(wù)去做相應(yīng)的選擇
1.3.1、隔離級(jí)別
- 數(shù)據(jù)庫提供了四種事務(wù)隔離級(jí)別, 不同的隔離級(jí)別采用不同的鎖類開來實(shí)現(xiàn).
隔離級(jí)別 | 臟讀可能性 | 不可重復(fù)讀可能性 | 幻讀可能性 | 加鎖讀 |
---|---|---|---|---|
READ UNCOMMITTED | YES | YES | YES | NO |
READ COMMITED | NO | YES | YES | NO |
REPEATABLE READ | NO | NO | YES | NO |
SERIALIZABLE | NO | NO | NO | YES |
相關(guān)概念
-
臟讀
:事務(wù)中的修改厅缺,即使未提交蔬顾,對(duì)其他事務(wù)也是課件的。事務(wù)可以讀取未提交的數(shù)據(jù)湘捎。 -
不可重復(fù)讀
:在同一個(gè)事務(wù)中诀豁,再次讀取數(shù)據(jù)時(shí),所讀取的數(shù)據(jù)窥妇,和第1次讀取的數(shù)據(jù)舷胜,不一樣了 -
幻讀
:幻讀的重點(diǎn)在于新增或者刪除,同樣的條件, 第1次和第2次讀出來的記錄數(shù)不一樣活翩。
幻讀是指當(dāng)一個(gè)事務(wù)在讀取某個(gè)范圍內(nèi)的數(shù)據(jù)時(shí)逞带,另一個(gè)事務(wù)在這個(gè)范圍內(nèi)插入了一行記錄并提交,于是當(dāng)前一個(gè)事務(wù)再次讀取該范圍內(nèi)的數(shù)據(jù)時(shí)纱新,發(fā)現(xiàn)多出了一行,即幻行穆趴。
臟讀脸爱、不可重復(fù)讀、幻讀的級(jí)別高低是:
臟讀 < 不可重復(fù)讀 < 幻讀
所以未妹,設(shè)置了最高級(jí)別的SERIALIZABLE_READ就不用在設(shè)置REPEATABLE_READ和READ_COMMITTED了
- READ UNCOMMITTED(未提交讀):數(shù)據(jù)庫中幾乎不用這種事務(wù)簿废。
- READ COMMITED(已提交讀):大多數(shù)數(shù)據(jù)庫的默認(rèn)隔離級(jí)別(MySQL不是)空入。這個(gè)級(jí)別代表事務(wù)開始后,只能讀到其他事務(wù)提交后的修改族檬,未提交的修改是不可見的歪赢。顯然這樣就解決了臟讀的問題。但是還是會(huì)遇到不可重復(fù)讀的問題单料。
- REPEATABLE READ(可重復(fù)讀):Mysql的默認(rèn)隔離級(jí)別埋凯,該級(jí)別保證了同一個(gè)事務(wù)中多次讀取同樣記錄的結(jié)果是一致的。
- SERIALIZABLE(可序列化讀):最高的隔離級(jí)別扫尖,通過強(qiáng)制事務(wù)串行執(zhí)行白对,避免了前面說的幻讀問題。該級(jí)別會(huì)在讀取的每一行上都加鎖换怖,所以可能導(dǎo)致大量的超時(shí)和鎖爭(zhēng)用問題甩恼。
1.3.2、死鎖
兩個(gè)活多個(gè)事務(wù)在同一資源上相互占用沉颂,并請(qǐng)求鎖定對(duì)方占用的資源条摸,從而導(dǎo)致惡性循環(huán)的現(xiàn)象。
為解決這種問題铸屉,數(shù)據(jù)庫都是些了各種死鎖檢測(cè)和死鎖超時(shí)機(jī)制钉蒲。
- 如InnoDB若檢測(cè)到死鎖循環(huán)依賴,就立即返回一個(gè)錯(cuò)誤抬探。
- 當(dāng)查詢時(shí)間到鎖等待超時(shí)的設(shè)定后放棄鎖清秋子巾。
InnoDB的處理方式是,將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾小压。
1.4线梗、事務(wù)日志
由于事務(wù)日志的寫入是順序I/O操作,會(huì)比隨機(jī)I/O快的多怠益。(因?yàn)闄C(jī)械硬盤讀寫最費(fèi)時(shí)間的就是磁頭的定位和移動(dòng)過程)
當(dāng)數(shù)據(jù)持久化到事務(wù)日志以后仪搔,再慢慢地刷寫回真正的數(shù)據(jù)庫。即使途中服務(wù)器掛了蜻牢,重啟后還是可以根據(jù)事務(wù)日志來恢復(fù)數(shù)據(jù)烤咧。
1.5、MySQL中的事務(wù)
MySQL提供了兩種事務(wù)的存儲(chǔ)引擎:InnoDB和NDB cluster抢呆。另外還有一些第三方的事務(wù)煮嫌,比較知名的有XtraDB和PBXT。
- 自動(dòng)提交(AutoCommit):Mysql默認(rèn)是自動(dòng)提交模式抱虐。如果你不顯式地開始一個(gè)事務(wù)昌阿,那么每次查詢都當(dāng)做一個(gè)事務(wù)。
可通過以下方式查詢活修改自動(dòng)提交模式
SHOW VARIABLES LIKE 'AUTOCOMMIT';
SET AUTOCOMMIT = 0;--1表示啟用,0表示禁用
- 設(shè)置隔離級(jí)別:可以配置文件中修改整個(gè)數(shù)據(jù)庫的隔離級(jí)別懦冰,也可以只改變當(dāng)前會(huì)話的隔離
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1.6灶轰、多版本并發(fā)控制(MVCC)
不同的存儲(chǔ)引擎MVCC實(shí)現(xiàn)不同,典型的有樂觀并發(fā)和悲觀并發(fā)控制刷钢。下面結(jié)合InnoDB說明下笋颤。
InnoDB的MVCC,通過在每行記錄后面保存兩個(gè)隱藏列實(shí)現(xiàn)内地,這兩個(gè)列一個(gè)保存行的創(chuàng)建時(shí)間伴澄,一個(gè)保存過期(刪除時(shí)間),當(dāng)然這里存儲(chǔ)的創(chuàng)建時(shí)間不是真正的時(shí)間瓤鼻,而是系統(tǒng)版本號(hào)秉版。
- REPEATABLE READ級(jí)別下,MVCC操作如下:
- select :InnoDB只查找版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行(即創(chuàng)建版本號(hào)《=當(dāng)前事務(wù)版本號(hào))茬祷,這樣保證事務(wù)讀取的行是早于事務(wù)開始前就已經(jīng)存在的清焕。刪除版本號(hào)》當(dāng)前事務(wù)版本號(hào),保證事情讀取到的行在事務(wù)開始前未被刪除祭犯。
- insert:插入新行的時(shí)候秸妥,將事務(wù)分配到的版本號(hào)賦給創(chuàng)建版本號(hào)那個(gè)列屬性。
- delete:為刪除的每一行保存當(dāng)前系統(tǒng)版本號(hào)為行刪除標(biāo)識(shí)沃粗,即將該版本號(hào)存入刪除版本號(hào)的那個(gè)列屬性
- update:實(shí)際上是新插入一條記錄粥惧,然后將事務(wù)分配到的版本號(hào)賦給舊記錄的刪除版本號(hào)列以及新記錄的創(chuàng)建版本號(hào)列。
MVCC只在REPEATABLE READ和READ COMMITTED兩個(gè)隔離級(jí)別下工作最盅。
1.7突雪、MySQL存儲(chǔ)引擎
查詢表相關(guān)信息,命令如下
mysql> show table status like 'city' \G
*************************** 1. row ***************************
Name: city //表名
Engine: InnoDB//引擎名
Version: 10
Row_format: Compact//行的格式
Rows: 600//行數(shù)涡贱,對(duì)于InnoDB咏删,該行是估計(jì)值,其他引擎為準(zhǔn)確值
Avg_row_length: 81//平均每行字節(jié)數(shù)
Data_length: 49152//表數(shù)據(jù)大小问词,byte為單位
Max_data_length: 0//
Index_length: 16384
Data_free: 0
Auto_increment: 601
Create_time: 2017-10-09 19:59:23
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
修改表的引擎采用如下語句:
ALTER TABLE city ENGINE = InnoDB;
下面簡(jiǎn)單介紹下相關(guān)存儲(chǔ)引擎的優(yōu)缺點(diǎn)
1.7.1督函、InnoDB
優(yōu)點(diǎn):Mysql當(dāng)前的默認(rèn)引擎,事務(wù)型引擎激挪,用MVCC支持高并發(fā)辰狡,通過間隙鎖在REPEATABLE READ級(jí)別下就能防止幻讀。支持熱備份垄分。
缺點(diǎn):非常復(fù)雜宛篇,性能較一些簡(jiǎn)單的引擎要差一點(diǎn)兒”∈空間占用比較多叫倍。
1.7.2豌鸡、MyISAM:
優(yōu)點(diǎn):Mysql 5.1之前版本的默認(rèn)引擎。全文索引段标、壓縮、空間函數(shù)(GIS)炉奴、并發(fā)插入逼庞、某些場(chǎng)景下性能很好
缺點(diǎn):非事務(wù)型、不支持行鎖瞻赶、崩潰后數(shù)據(jù)不容易修復(fù)
1.7.3赛糟、Archive:
優(yōu)點(diǎn):支持高并發(fā)插入,解決不可重復(fù)讀砸逊,針對(duì)高速插入和壓縮做了優(yōu)化的簡(jiǎn)單引擎
缺點(diǎn):只支持查詢和插入操作璧南,非事務(wù)型,僅適合日志和數(shù)據(jù)采集的應(yīng)用場(chǎng)景
1.7.4师逸、CSV引擎:
優(yōu)點(diǎn):有效支持CSV格式文件的導(dǎo)入導(dǎo)出司倚。
缺點(diǎn):作者沒說
1.7.5、Memory引擎:
優(yōu)點(diǎn):用來快速地訪問數(shù)據(jù)的篓像,比MyISAM快一個(gè)數(shù)量級(jí)动知。支持Hash索引,因此查詢操作非吃北纾快盒粮。
缺點(diǎn):所有數(shù)據(jù)保存在內(nèi)存里,重啟只留下表結(jié)構(gòu)奠滑。只支持表級(jí)鎖丹皱,并發(fā)能力低下。不支持BLOB或TEXT類型的列宋税。且行長度固定摊崭,容易導(dǎo)致內(nèi)存浪費(fèi)。
1.7.6弃甥、NDB集群引擎:
作者沒有細(xì)講爽室,后文會(huì)細(xì)講的吧,總之就是支持建立集群淆攻。
1.7.7阔墩、XtraDB和PBXT等OLTP類引擎:
優(yōu)點(diǎn):可完全替代InnoDB,或者高度相似。還額外提供了一些性能優(yōu)化瓶珊、可測(cè)量性啸箫、操作靈活性
缺點(diǎn):第三方的引擎,社區(qū)支持的存儲(chǔ)引擎伞芹,可能不能保證質(zhì)量
其實(shí)最常用的是InnoDB和MyISAM
- 相關(guān)參數(shù)對(duì)比
特性 | InnoDB | MyISAM | MEMORY | ARCHIVE |
---|---|---|---|---|
存儲(chǔ)限制(Storage limits) | 64TB | No | YES | No |
支持事物(Transactions) | Yes | No | No | No |
鎖機(jī)制(Locking granularity) | 行鎖 | 表鎖 | 表鎖 | 行鎖 |
B樹索引(B-tree indexes) | Yes | Yes | Yes | No |
T樹索引(T-tree indexes) | No | No | No | No |
哈希索引(Hash indexes) | Yes | No | Yes | No |
全文索引(Full-text indexes) | Yes | Yes | No | No |
集群索引(Clustered indexes) | Yes | No | No | No |
數(shù)據(jù)緩存(Data caches) | Yes | No | N/A | No |
索引緩存(Index caches) | Yes | Yes | N/A | No |
數(shù)據(jù)可壓縮(Compressed data) | Yes | Yes | No | Yes |
加密傳輸(Encrypted data[1]) | Yes | Yes | Yes | Yes |
集群數(shù)據(jù)庫支持(Cluster databases support) | No | No | No | No |
復(fù)制支持(Replication support[2]) | Yes | No | No | Yes |
外鍵支持(Foreign key support) | Yes | No | No | No |
存儲(chǔ)空間消耗(Storage Cost) | 高 | 低 | N/A | 非常低 |
內(nèi)存消耗(Memory Cost) | 高 | 低 | N/A | 低 |
數(shù)據(jù)字典更新(Update statistics for data dictionary) | Yes | Yes | Yes | Yes |
備份/時(shí)間點(diǎn)恢復(fù)(backup/point-in-time recovery[3]) | Yes | Yes | Yes | Yes |
多版本并發(fā)控制(Multi-Version Concurrency Control/MVCC) | Yes | No | No | No |
批量數(shù)據(jù)寫入效率(Bulk insert speed) | 慢 | 快 | 快 | 非惩粒快 |
地理信息數(shù)據(jù)類型(Geospatial datatype support) | Yes | Yes | No | Yes |
地理信息索引(Geospatial indexing support[4]) | Yes | Yes | No | Yes |
- 在服務(wù)器中實(shí)現(xiàn)(通過加密功能)蝉娜。在其他表空間加密數(shù)據(jù)在MySQL 5.7或更高版本兼容。
- 在服務(wù)中實(shí)現(xiàn)的扎唾,而不是在存儲(chǔ)引擎中實(shí)現(xiàn)的召川。
- 在服務(wù)中實(shí)現(xiàn)的,而不是在存儲(chǔ)引擎中實(shí)現(xiàn)的胸遇。
- 地理位置索引荧呐,InnoDB支持可mysql5.7.5或更高版本兼容
下一章開始MySQL基準(zhǔn)測(cè)試,當(dāng)然前幾章相關(guān)的內(nèi)容會(huì)相對(duì)少纸镊,這些主要是相關(guān)的介紹倍阐,讓大家大致有個(gè)了解。