怎么理解并掌握MySQL

本篇內(nèi)容主要講解“怎么理解并掌握MySQL”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“怎么理解并掌握MySQL”吧!

為許昌等地區(qū)用戶提供了全套網(wǎng)頁(yè)設(shè)計(jì)制作服務(wù)，及許昌網(wǎng)站建設(shè)行業(yè)解決方案。主營(yíng)業(yè)務(wù)為成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站、許昌網(wǎng)站設(shè)計(jì)，以傳統(tǒng)方式定制建設(shè)網(wǎng)站，并提供域名空間備案等一條龍服務(wù)，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠(chéng)的服務(wù)。我們深信只要達(dá)到每一位用戶的要求，就會(huì)得到認(rèn)可，從而選擇與我們長(zhǎng)期合作。這樣，我們也可以走得更遠(yuǎn)！

MySQL分為 server 層和存儲(chǔ)引擎

server層

• 連接器：管理連接權(quán)限驗(yàn)證

• 查詢緩存：命中緩存直接換回查詢結(jié)果

• 分析器：分析語(yǔ)法

• 優(yōu)化器：生成執(zhí)行計(jì)劃，選擇索引

• 執(zhí)行器：操作索引返回結(jié)果

存儲(chǔ)引擎

存儲(chǔ)引擎負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提?。黄浼軜?gòu)是插件式的。innodb 在 mysql5.5.5 版本開始成為 mysql 默認(rèn)存儲(chǔ)引擎。

各存儲(chǔ)引擎比對(duì)：

• InnoDB：支持事務(wù)，支持外鍵，InnoDB 是聚集索引，數(shù)據(jù)文件是和索引綁在一起的，必須要有主鍵，通過(guò)主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然后再通過(guò)主鍵查詢到數(shù)據(jù)，不支持全文索引。

• MyISAM：不支持事物，不支持外鍵，MyISAM 是非聚集索引，數(shù)據(jù)文件是分離的，索引保存的是數(shù)據(jù)文件的指針。主鍵索引和輔助索引是獨(dú)立的，查詢效率上 MyISAM 要高于 InnnDB ，因此做讀寫分離的時(shí)候一般選擇用 InnoDB 做主機(jī)，MyISAM 做從機(jī)

• Memory：有比較大的缺陷使用場(chǎng)景很少；文件數(shù)據(jù)都存儲(chǔ)在內(nèi)存中，如果 mysqld 進(jìn)程發(fā)生異常，重啟或關(guān)閉機(jī)器這些數(shù)據(jù)都會(huì)消失。

sql 的執(zhí)行過(guò)程

第一步客戶端連接上 mysql 數(shù)據(jù)庫(kù)的連接器，連接器獲取權(quán)限，維持管理連接；連接完成后如果你沒有后續(xù)的指令這個(gè)連接就會(huì)處于空閑狀態(tài)，如果太長(zhǎng)時(shí)間不使用這個(gè)連接這個(gè)連接就會(huì)斷開，這個(gè)空閑時(shí)長(zhǎng)默認(rèn)是 8 小時(shí)，由 wait_timeout 參數(shù)控制。

第二步你往 mysql 數(shù)據(jù)庫(kù)發(fā)送了一條 sql ，這個(gè)時(shí)候查詢緩存開始工作，看看之前有沒有執(zhí)行過(guò)這個(gè) sql ，如果有則直接返回緩存數(shù)據(jù)到客戶端，只要對(duì)表執(zhí)行過(guò)更新操作緩存都會(huì)失效，因此一些很少更新的數(shù)據(jù)表可考慮使用數(shù)據(jù)庫(kù)緩存，對(duì)頻繁更新的表使用緩存反而弊大于利。使用緩存的方法如以下 sql ，通過(guò) SQL_CACHE 來(lái)指定：

select  SQL_CACHE * from table where xxx=xxx

第三步當(dāng)未命中緩存的時(shí)候，分析器開始工作；分析器判斷你是 select 還是 update 還是 insert ，分析你的語(yǔ)法是否正確。

第四步優(yōu)化器根據(jù)你的表的索引和 sql 語(yǔ)句決定用哪個(gè)索引，決定 join 的順序。

第五步執(zhí)行器執(zhí)行 sql ，調(diào)用存儲(chǔ)引擎的接口，掃描遍歷表或者插入更新數(shù)據(jù)。

MySQL日志

MySQL日志介紹

mysql 有兩個(gè)重要日志—— redolog 和 binlog ，redolog 是獨(dú)屬于 innodb 的日志，binlog 則是屬于 server 層的日志。下面介紹這兩個(gè)日志有什么用：當(dāng)我們更新數(shù)據(jù)庫(kù)數(shù)據(jù)的時(shí)候，這兩個(gè)日志文件也會(huì)被更新，記錄數(shù)據(jù)庫(kù)更新操作。

redolog 又稱作重做日志，用于記錄事務(wù)操作的變化，記錄的是數(shù)據(jù)修改之后的值，不管事務(wù)是否提交都會(huì)記錄下來(lái)。它在數(shù)據(jù)庫(kù)重啟恢復(fù)的時(shí)候被使用，innodb 利用這個(gè)日志恢復(fù)到數(shù)據(jù)庫(kù)宕機(jī)前的狀態(tài)，以此來(lái)保證數(shù)據(jù)的完整性。redolog 是物理日志，記錄的是某個(gè)表的數(shù)據(jù)做了哪些修改，redolog 是固定大小的，也就是說(shuō)后面的日志會(huì)覆蓋前面的日志。

binlog 又稱作歸檔日志，它記錄了對(duì) MySQL 數(shù)據(jù)庫(kù)執(zhí)行更改的所有操作，但是不包括 SELECT 和 SHOW 這類操作。binlog 是邏輯日志，記錄的是某個(gè)表執(zhí)行了哪些操作。binlog 是追加形式的寫入日志，后面的日志不會(huì)被前面的覆蓋。

數(shù)據(jù)更新過(guò)程

我們執(zhí)行一個(gè)更新操作是這樣的：讀取對(duì)應(yīng)的數(shù)據(jù)到內(nèi)存—>更新數(shù)據(jù)—>寫 redolog 日志—> redolog 狀態(tài)為 prepare —>寫 binlog 日志—>提交事務(wù)—> redolog 狀態(tài)為 commit ，數(shù)據(jù)正式寫入日志文件。我們發(fā)現(xiàn) redolog 的提交方式為“兩段式提交”，這樣做的目的是為了數(shù)據(jù)恢復(fù)的時(shí)候確保數(shù)據(jù)恢復(fù)的準(zhǔn)確性，因?yàn)閿?shù)據(jù)恢復(fù)是通過(guò)備份的 binlog 來(lái)完成的，所以要確保 redolog 要和 binlog 一致。

MySQL的MVCC

事務(wù)隔離級(jí)別在此略過(guò)，相信大部分小伙伴都知道相關(guān)的知識(shí)了，在這里單單只介紹 mysql 實(shí)現(xiàn)事務(wù)隔離的原理—— mvcc(多版本并發(fā)控制)。在學(xué)習(xí) mvcc 之前我需要先介紹快照讀和當(dāng)前讀。

快照讀和當(dāng)前讀

快照讀就是一個(gè) select 語(yǔ)句，形如：

select * from table

在 Repeatableread 事務(wù)隔離級(jí)別下，快照讀的特點(diǎn)是獲取當(dāng)前數(shù)據(jù)庫(kù)的快照數(shù)據(jù)，對(duì)于所有未 commit 的數(shù)據(jù)都不可見，快照讀不會(huì)對(duì)數(shù)據(jù)上鎖。

當(dāng)前讀是對(duì)所讀數(shù)據(jù)上悲觀鎖使其他當(dāng)前讀無(wú)法操作數(shù)據(jù)。當(dāng)前讀 sql 包括:

select ... lock in share modeselect ... for updateinsertupdatedelete

其中后面三個(gè) sql 都是給數(shù)據(jù)庫(kù)上排他鎖（X鎖），而第一個(gè) sql 是給數(shù)據(jù)庫(kù)上共享鎖（S鎖）。X 鎖是一旦某個(gè)當(dāng)前讀到這個(gè)鎖，其他當(dāng)前讀則沒有對(duì)這個(gè)事務(wù)讀寫的權(quán)利，其他當(dāng)前讀會(huì)被阻塞住。而 S 鎖是當(dāng)一個(gè)當(dāng)前讀對(duì)某條數(shù)據(jù)上 S 鎖，其他當(dāng)前讀可以對(duì)該數(shù)據(jù)也上 S 鎖但不能上 X 鎖，拿到 S 鎖的當(dāng)前讀可以讀數(shù)據(jù)不能改數(shù)據(jù)。（關(guān)于數(shù)據(jù)庫(kù)悲觀鎖樂(lè)觀鎖并發(fā)章節(jié)會(huì)介紹）。

MVCC 原理

innodb 實(shí)現(xiàn)快照讀和當(dāng)前讀悲觀鎖的技術(shù)就是 mvcc 。innodb 在插入一條數(shù)據(jù)的時(shí)候會(huì)在后面跟上兩個(gè)隱藏的列，這兩個(gè)列，一個(gè)保存了這個(gè)行的創(chuàng)建時(shí)系統(tǒng)版本號(hào)，一個(gè)保存的是行的刪除的系統(tǒng)版本號(hào)。每開始一個(gè)新的事務(wù)，系統(tǒng)版本號(hào)就會(huì)自動(dòng)遞增，事務(wù)開始時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的 ID。innodb 更新一條數(shù)據(jù)是設(shè)置舊數(shù)據(jù)刪除版本號(hào)，然后插入一條新的數(shù)據(jù)并設(shè)置創(chuàng)建版本號(hào)，然后刪除舊的數(shù)據(jù)。那么怎么保證快照讀是讀取到未 commit 的數(shù)據(jù)呢，兩個(gè)條件：

• InnoDB 只查找創(chuàng)建版本早于當(dāng)前事務(wù)版本的數(shù)據(jù)行，即，行的系統(tǒng)版本號(hào)小于或等于事務(wù)的系統(tǒng)版本號(hào)，這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始前已經(jīng)存在的，要么是事務(wù)自身插入或者修改過(guò)的。

• 行的刪除版本，要么未定義，要么大于當(dāng)前事務(wù)版本號(hào)。這樣可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除。只有符合上述兩個(gè)條件的紀(jì)錄，才能作為查詢結(jié)果返回。

而數(shù)據(jù)庫(kù)鎖也是通過(guò)比對(duì)版本號(hào)來(lái)決定是否阻塞某個(gè)事物。

MySQL索引

索引介紹

索引按數(shù)據(jù)結(jié)構(gòu)分可分為哈希表，有序數(shù)組，搜索樹，跳表：

• 哈希表適用于只有等值查詢的場(chǎng)景

• 有序數(shù)組適用于有等值查詢和范圍查詢的場(chǎng)景，但有序數(shù)組索引的更新代價(jià)很大，所以最好用于靜態(tài)數(shù)據(jù)表

• 搜索樹的搜索效率穩(wěn)定，不會(huì)出現(xiàn)大幅波動(dòng)，而且基于索引的順序掃描時(shí)，也可以利用雙向指針快速左右移動(dòng)，效率非常高

• 跳表可以理解為優(yōu)化的哈希索引

innodb 使用了 B+ 樹索引模型，而且是多叉樹。雖然二叉樹是索引效率最高的，但是索引需要寫入磁盤，如果使用二叉樹磁盤 io 會(huì)變得很頻繁。在 innodb 索引中分為主鍵索引（聚簇索引）和非主鍵索引（二級(jí)索引）。主鍵索引保存了該行數(shù)據(jù)的全部信息，二級(jí)索引保存了該行數(shù)據(jù)的主鍵；所以使用二級(jí)索引的時(shí)候會(huì)先查出主鍵值，然后回表查詢出數(shù)據(jù)，而使用主鍵索引則不需要回表。

對(duì)二級(jí)索引而言可使用覆蓋索引來(lái)優(yōu)化 sql，看下面兩條 sql

select * from table where key=1;select id from table where key=1;

key 是一個(gè)二級(jí)索引，第一條 sql 是先查詢出 id ，然后根據(jù) id 回表查詢出真正的數(shù)據(jù)。而第二條查詢索引后直接返回?cái)?shù)據(jù)不需要回表。第二條 sql 索引 key 覆蓋了我們的查詢需求，稱作覆蓋索引

普通索引和唯一索引

innoDB 是按數(shù)據(jù)頁(yè)來(lái)讀寫數(shù)據(jù)的，當(dāng)要讀取一條數(shù)據(jù)的時(shí)候是先將本頁(yè)數(shù)據(jù)全部讀入內(nèi)存，然后找到對(duì)應(yīng)數(shù)據(jù)，而不是直接讀取，每頁(yè)數(shù)據(jù)的默認(rèn)大小為 16KB。

當(dāng)一個(gè)數(shù)據(jù)頁(yè)需要更新的時(shí)候，如果內(nèi)存中有該數(shù)據(jù)頁(yè)就直接更新，如果沒有該數(shù)據(jù)頁(yè)則在不影響數(shù)據(jù)一致性的前提下將；更新操作先緩存到 change buffer 中，在下次查詢需要訪問(wèn)這個(gè)數(shù)據(jù)頁(yè)的時(shí)候再寫入更新操作除了查詢會(huì)將 change buffer 寫入磁盤，后臺(tái)線程線程也會(huì)定期將 change buffer 寫入到磁盤中。對(duì)于唯一索引來(lái)說(shuō)所有的更新操作都要先判斷這個(gè)操作是否會(huì)違反唯一性約束，因此唯一索引的更新無(wú)法使用 change buffer 而普通索引可以，唯一索引更新比普通索引更新多一個(gè)唯一性校驗(yàn)的過(guò)程。

聯(lián)合索引

兩個(gè)或更多個(gè)列上的索引被稱作聯(lián)合索引（復(fù)合索引）。聯(lián)合索引可減少索引開銷，以聯(lián)合索引 (a,b,c) 為例，建立這樣的索引相當(dāng)于建立了索引 a、ab、abc 三個(gè)索引—— Mysql 從左到右的使用索引中的字段，一個(gè)查詢可以只使用索引中的一部份，但只能是最左側(cè)部分，而且當(dāng)最左側(cè)字段是常量引用時(shí)，索引就十分有效，這就是最左前綴原則。由最左前綴原則可知，組合索引是有順序的，那么哪個(gè)索引放在前面就比較有講究了。對(duì)于組合索引還有一個(gè)知識(shí)點(diǎn)——索引下推，假設(shè)有組合索引（a，b，c）有如下 sql:

selet * from table where a=xxx and b=xxx

這個(gè) sql 會(huì)進(jìn)行兩次篩選第一次查出 a=xxx 數(shù)據(jù) 再?gòu)?a=xxx 中查出 b=xxx 的數(shù)據(jù)。使用索引下推和不使用索引下推的區(qū)別在于不使用索引下推會(huì)先查出 a=xxx 數(shù)據(jù)的主鍵然后根據(jù)查詢出的主鍵回表查詢出全行數(shù)據(jù)，再在全行數(shù)據(jù)上查出 b=xxx 的數(shù)據(jù)；而索引下推的執(zhí)行過(guò)程是先查出 a=xxx 數(shù)據(jù)的主鍵，然后在這些主鍵上二次查詢 b=xxx 的主鍵，然后回表。

索引下推的特點(diǎn)：

• innodb 引擎的表，索引下推只能用于二級(jí)索引

• 索引下推一般可用于所查詢字段不全是聯(lián)合索引的字段，查詢條件為多條件查詢且查詢條件子句字段全是聯(lián)合索引。

優(yōu)化器與索引

在索引建立之后，一條語(yǔ)句可能會(huì)命中多個(gè)索引，這時(shí)，就會(huì)交由優(yōu)化器來(lái)選擇合適的索引。優(yōu)化器選擇索引的目的，是找到一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行語(yǔ)句。那么優(yōu)化器是怎么去確定索引的呢？?jī)?yōu)化器會(huì)優(yōu)先選擇掃描行數(shù)最少的索引，同時(shí)還會(huì)結(jié)合是否使用臨時(shí)表、是否排序等因素進(jìn)行綜合判斷。MySQL 在開始執(zhí)行 sql 之前，并不知道滿足這個(gè)條件的記錄有多少條，而只能根據(jù) mysql 的統(tǒng)計(jì)信息來(lái)估計(jì)，而統(tǒng)計(jì)信息是通過(guò)數(shù)據(jù)采樣得出來(lái)的。

其他索引知識(shí)點(diǎn)

有時(shí)候需要索引很長(zhǎng)的字符列，這會(huì)讓索引變得很大很慢還占內(nèi)存。通?？梢砸蚤_始的部分字符作為索引，這就是前綴索引。這樣可以大大節(jié)約索引空間，從而提高索引效率，但這樣也會(huì)降低索引的選擇性。

臟頁(yè)對(duì)數(shù)據(jù)庫(kù)的影響：

當(dāng)內(nèi)存數(shù)據(jù)頁(yè)和磁盤的數(shù)據(jù)不一致的時(shí)候我們稱這個(gè)內(nèi)存頁(yè)為臟頁(yè)，內(nèi)存數(shù)據(jù)寫入磁盤后數(shù)據(jù)一致，稱為干凈頁(yè)。當(dāng)要讀入數(shù)據(jù)而數(shù)據(jù)庫(kù)沒有內(nèi)存的時(shí)候，這個(gè)時(shí)候需要淘汰內(nèi)存中的數(shù)據(jù)頁(yè)——干凈頁(yè)可以直接淘汰掉，而臟頁(yè)需要先刷入磁盤再淘汰。如果一個(gè)查詢要淘汰的臟頁(yè)太多會(huì)導(dǎo)致查詢的時(shí)間變長(zhǎng)。為了減少臟頁(yè)對(duì)數(shù)據(jù)庫(kù)性能影響，innodb 會(huì)控制臟頁(yè)的比例和臟頁(yè)刷新時(shí)機(jī)。

MySQL語(yǔ)法分析及優(yōu)化

count(*)

count(*) 對(duì) innodb 而言，它需要把數(shù)據(jù)從磁盤中讀取出來(lái)然后累計(jì)計(jì)數(shù)；而 MyISAM 引擎把一個(gè)表的總行數(shù)存在了磁盤上，所以執(zhí)行 count(*) 會(huì)直接返回這個(gè)數(shù)，如果有 where 條件則和 innodb一樣。那么如何優(yōu)化 count(*) ？一個(gè)思路是使用緩存，但是需要注意雙寫一致的問(wèn)題（雙寫一致性后文緩存章節(jié)會(huì)做介紹）。還可以專門設(shè)計(jì)一張表用以存儲(chǔ) count(*)。

對(duì)于 count(主鍵 id )來(lái)說(shuō)，InnoDB 引擎會(huì)遍歷整張表，把每一行的 id 值都取出來(lái)，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。對(duì)于 count(1) 來(lái)說(shuō)，InnoDB 引擎遍歷整張表，但不取值。server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1” 進(jìn)去，判斷是不可能為空的，按行累加。單看這兩個(gè)用法的差別的話，你能對(duì)比出來(lái)，count(1) 執(zhí)行得要比 count(主鍵 id)快。因?yàn)閺囊?返回 id 會(huì)涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。對(duì)于 count(字段)來(lái)說(shuō)：如果這個(gè)“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個(gè)字段，判斷不能為 null，按行累加；如果這個(gè)“字段”定義允許為 null，那么執(zhí)行的時(shí)候，判斷到有可能是 null，還要把值取出來(lái)再 判斷一下，不是 null 才累加。而對(duì)于 count(*) 來(lái)說(shuō)，并不會(huì)把全部字段取出來(lái)，而是專門做了優(yōu)化，不取值，按行累加。所以排序效率：

count(*)=count(1)>count(id)>count(字段)

order by

Mysql 會(huì)給每個(gè)線程分配一塊內(nèi)存用于做排序處理，稱為 sort_buffer ,一個(gè)包含排序的 sql 執(zhí)行過(guò)程為：申請(qǐng)排序內(nèi)存 sort_buffer ,然后一條條查詢出整行數(shù)據(jù)，然后將需要的字段數(shù)據(jù)放入到排序內(nèi)存中，染回對(duì)排序內(nèi)存中的數(shù)據(jù)做一個(gè)快速排序，然后返回到客戶端。當(dāng)數(shù)據(jù)量過(guò)大，排序內(nèi)存盛不下的時(shí)候就會(huì)利用磁盤臨時(shí)文件來(lái)輔助排序。當(dāng)我們排序內(nèi)存盛不下數(shù)據(jù)的時(shí)候，mysql 會(huì)使用 rowid 排序來(lái)優(yōu)化。rowid 排序相對(duì)于全字段排序，不會(huì)把所有字段都放入 sort_buffer，所以在 sort buffer 中進(jìn)行排序之后還得回表查詢。在少數(shù)情況下，可以使用聯(lián)合索引+索引覆蓋的方式來(lái)優(yōu)化 order by。

join

在了解 join 之前我們應(yīng)該先了解驅(qū)動(dòng)表這個(gè)概念——當(dāng)兩表發(fā)生關(guān)聯(lián)的時(shí)候就會(huì)有驅(qū)動(dòng)表和被驅(qū)動(dòng)表之分，驅(qū)動(dòng)表也叫外表（R 表），被驅(qū)動(dòng)表也叫做內(nèi)表（S 表）。一般我們將小表當(dāng)做驅(qū)動(dòng)表（指定了聯(lián)接條件時(shí)，滿足查詢條件的記錄行數(shù)少的表為「驅(qū)動(dòng)表」,未指定聯(lián)接條件時(shí)，行數(shù)少的表為「驅(qū)動(dòng)表」；MySQL 內(nèi)部?jī)?yōu)化器也是這么做的）。

假設(shè)有這樣一句 sql（xxx 為索引）:

select * from table1 left join tablet2 on table1.xxx=table2.xxx

這條語(yǔ)句執(zhí)行過(guò)程是先遍歷表 table1 ，然后根據(jù)從表 table1 中取出的每行數(shù)據(jù)中的 xxx 值，去表 table2 中查找滿足條件的 記錄。這個(gè)過(guò)程就跟我們寫程序時(shí)的嵌套查詢類似，并且能夠用上被驅(qū)動(dòng)表的索引，這種查詢方式叫 NLJ 。當(dāng) xxx 不是索引的時(shí)候，再使用 NLJ 的話就會(huì)對(duì) table2 做多次的全表掃描（每從 table1 取一條數(shù)據(jù)就全表掃描一次 table2），掃描數(shù)暴漲。這個(gè)時(shí)候 mysql 會(huì)采用另外一個(gè)查詢策略。Mysql 會(huì)先把 table1 的數(shù)據(jù)讀入到一個(gè) join_buffer 的內(nèi)存空間里面去，然后依次取出 table2 的每一行數(shù)據(jù)，跟 join_buffer 中的數(shù)據(jù)做對(duì)比，滿足 join 條件的作為結(jié)果集的一部分返回。

我們?cè)谑褂?join 的時(shí)候,要遵循以下幾點(diǎn)：

• 小表驅(qū)動(dòng)大表。

• 被驅(qū)動(dòng)表走索引的情況下（走 NLJ 查詢方式）的時(shí)候才考慮用join

sql 的優(yōu)化

1） 在 mysql 中，如果對(duì)字段做了函數(shù)計(jì)算，就用不上索引了。

如以下 sql(data 為索引):

select *  from tradelog where month(data)=1;

優(yōu)化器對(duì)這樣的 sql 會(huì)放棄走搜索樹，因?yàn)樗鼰o(wú)法知道 data 的區(qū)間。

2）隱式的類型轉(zhuǎn)換會(huì)導(dǎo)致索引失效。

如以下 sql:

select * from table where xxx=110717;

其中 xxx 為 varchar 型，在 mysql 中，字符串和數(shù)字做比較的話，將字符串轉(zhuǎn)換成數(shù)字再進(jìn)行比較，這里相當(dāng)于使用了 CAST(xxx ASsigned) 導(dǎo)致無(wú)法走索引。

3）索引列參與了計(jì)算不會(huì)走索引

4）like %xxx 不會(huì)走索引，like xxx% 會(huì)走索引

5）在 where 子句中使用 or ，在 innodb 中不會(huì)走索引，而 MyISAM 會(huì)。

執(zhí)行計(jì)劃和慢查詢?nèi)罩?/strong>

執(zhí)行計(jì)劃

在查詢 sql 之前加上 explain 可查看該條 sql 的執(zhí)行計(jì)劃,如：

EXPLAIN SELECT * FROM table

這條 sql 會(huì)返回這樣一個(gè)表：

這個(gè)表便是 sql 的執(zhí)行計(jì)劃，我們可以通過(guò)分析這個(gè)執(zhí)行計(jì)劃來(lái)知道我們 sql 的運(yùn)行情況。現(xiàn)對(duì)各列進(jìn)行解釋：

1）id：查詢中執(zhí)行 select 子句或操作表的順序。

2）select_type：查詢中每個(gè) select 子句的類型（簡(jiǎn)單 到復(fù)雜）包括：

• SIMPLE：查詢中不包含子查詢或者UNION；

• PRIMARY：查詢中包含復(fù)雜的子部分；

• SUBQUERY：在SELECT或WHERE列表中包含了子查詢，該子查詢被標(biāo)記為SUBQUERY；

• DERIVED：衍生，在FROM列表中包含的子查詢被標(biāo)記為DERIVED；

• UNION：若第二個(gè)SELECT出現(xiàn)在UNION之后，則被標(biāo)記為UNION；

• UNION RESULT：從UNION表獲取結(jié)果的SELECT被標(biāo)記為UNION RESULT；

3） type：表示 MySQL 在表中找到所需行的方式，又稱“訪問(wèn)類型”，包括：

• ALL：Full Table Scan， MySQL 將遍歷全表以找到匹配的行；

• index：Full Index Scan，index 與 ALL 區(qū)別為 index 類型只遍歷索引樹；

• range：索引范圍掃描，對(duì)索引的掃描開始于某一點(diǎn)，返回匹配值域的行，常見于 between < > 等查詢；

• ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。常見于使用非唯一索引即唯一索引的非唯一前綴進(jìn)行的查找；

• eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描；

• onst 和 system：當(dāng) MySQL 對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問(wèn)。如將主鍵置于 where 列表中，MySQL 就能將該查詢轉(zhuǎn)換為一個(gè)常量，system 是 const 類型的特例，當(dāng)查詢的表只有一行的情況下， 使用 system；

• NULL：MySQL 在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引。

4）possible_keys：指出 MySQL 能使用哪個(gè)索引在表中找到行，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用。

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

6）key_len：表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中使用的索引的長(zhǎng)度。

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

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

9）Extra：其他重要信息 包括：

• Using index：該值表示相應(yīng)的 select 操作中使用了覆蓋索引；

• Using where：MySQL 將用 where 子句來(lái)過(guò)濾結(jié)果集；

• Using temporary：表示 MySQL 需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見于排序和分組查詢；

• Using filesort：MySQL 中無(wú)法利用索引完成的排序操作稱為“文件排序”。

慢查詢?nèi)罩?/strong>

mysql 支持慢查詢?nèi)罩竟δ堋?mysql 會(huì)將查詢時(shí)間過(guò)長(zhǎng)的 sql 相關(guān)信息寫入日志。這個(gè)查詢時(shí)間閥值由參數(shù) long_query_time 指定， long_query_time 的默認(rèn)值為 10，運(yùn)行 10S 以上的查詢 sql 會(huì)被記錄到慢查詢?nèi)罩局?。默認(rèn)情況下，Mysql 數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢?nèi)罩荆枰覀兪謩?dòng)來(lái)設(shè)置這個(gè)參數(shù)。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，也支持將日志記錄寫入?shù)據(jù)庫(kù)表。

可通過(guò)以下 sql 查看慢查詢?nèi)罩臼欠耖_啟：

show variables  like '%slow_query_log%';

通過(guò)以下 sql 開啟慢查詢：

set global slow_query_log=1;

使用 sql 修改慢查詢?nèi)罩驹O(shè)置只對(duì)當(dāng)前數(shù)據(jù)庫(kù)生效，如果 MySQL 重啟后則會(huì)失效。如果要永久生效，就必須修改配置文件 my.cnf。

通過(guò)以下 sql 查看修改慢查詢的閾值：

show variables like 'long_query_time%';set global long_query_time=4;

主從備份

主從備份原理

主從復(fù)制是指一臺(tái)服務(wù)器充當(dāng)主數(shù)據(jù)庫(kù)服務(wù)器，另一臺(tái)或多臺(tái)服務(wù)器充當(dāng)從數(shù)據(jù)庫(kù)服務(wù)器，主服務(wù)器中的數(shù)據(jù)自動(dòng)復(fù)制到從服務(wù)器之中。通過(guò)這種手段我們可以做到讀寫分離，主庫(kù)寫數(shù)據(jù)，從庫(kù)讀數(shù)據(jù)，從而提高數(shù)據(jù)庫(kù)的可用。MySQL 主從復(fù)制涉及到三個(gè)線程，一個(gè)運(yùn)行在主節(jié)點(diǎn)（log dump thread），其余兩個(gè)(I/O thread, SQL thread)運(yùn)行在從節(jié)點(diǎn)。

主節(jié)點(diǎn) binary log dump 線程：

當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會(huì)創(chuàng)建一個(gè) logdump 線程，用于發(fā)送 binlog 的內(nèi)容。在讀取 binlog 中的操作時(shí)，此線程會(huì)對(duì)主節(jié)點(diǎn)上的 binlog 加鎖，當(dāng)讀取完成，甚至在發(fā)動(dòng)給從節(jié)點(diǎn)之前，鎖會(huì)被釋放。

從節(jié)點(diǎn)I/O線程:用于從庫(kù)將主庫(kù)的 binlog復(fù)制到本地的 relay log中，首先，從庫(kù)庫(kù)會(huì)先啟動(dòng)一個(gè)工作線程，稱為IO工作線程，負(fù)責(zé)和主庫(kù)建立一個(gè)普通的客戶端連接。如果該進(jìn)程追趕上了主庫(kù)，它將進(jìn)入睡眠狀態(tài)，直到主庫(kù)有新的事件產(chǎn)生通知它，他才會(huì)被喚醒，將接收到的事件記錄到 relay log(中繼日志)中。

從節(jié)點(diǎn) SQL 線程:

SQL 線程負(fù)責(zé)讀取 relay log 中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。

主從備份延遲

主備延遲最直接的表現(xiàn)是，備庫(kù)消費(fèi)中繼日志（ relay log）的速度，比主庫(kù)生產(chǎn) binlog 的速度要慢?？赡軐?dǎo)致的原因有：

• 大事務(wù)，主庫(kù)上必須等事務(wù)執(zhí)行完成才會(huì)寫入 binlog，再傳給備庫(kù)，當(dāng)一個(gè)事物用時(shí)很久的時(shí)候，在從庫(kù)上會(huì)因?yàn)檫@個(gè)事物的執(zhí)行產(chǎn)生延遲。

• 從庫(kù)壓力大。

主備延遲當(dāng)然是不好的，那么有哪些辦法盡量減小主備延遲呢？有下面幾個(gè)辦法：

• 一主多從——多接幾個(gè)從庫(kù)，讓這些從庫(kù)來(lái)分擔(dān)讀的壓力。這樣方法適用于從庫(kù)讀壓力大的時(shí)候。

• 通過(guò) binlog 輸出到外部系統(tǒng)，比如 Hadoop 這類系統(tǒng)，讓外部系統(tǒng)提供統(tǒng)計(jì)類查詢的能力

分布式事務(wù)

由于篇幅問(wèn)題，這里不再對(duì)分布式事物的概念做普及，直接介紹兩種分布式事務(wù): XA 分布式事務(wù)和 TCC 分布式事務(wù)。

XA 分布式事務(wù)

XA 是兩階段提交的強(qiáng)一致性事物。在 MySQL 5.7.7 版本中，Oracle 官方將 MySQL XA 一直存在的一個(gè) “bug” 進(jìn)行了修復(fù)，使得MySQL XA 的實(shí)現(xiàn)符合了分布式事務(wù)的標(biāo)準(zhǔn)。

XA 事務(wù)中的角色：

• 資源管理器（resource manager）：用來(lái)管理系統(tǒng)資源，是通向事務(wù)資源的途徑。數(shù)據(jù)庫(kù)就是一種資源管理器。資源管理還應(yīng)該具有管理事務(wù)提交或回滾的能力。

• 事務(wù)管理器（transaction manager）：事務(wù)管理器是分布式事務(wù)的核心管理者。事務(wù)管理器與每個(gè)資源管理器（resource manager）進(jìn)行通信，協(xié)調(diào)并完成事務(wù)的處理。事務(wù)的各個(gè)分支由唯一命名進(jìn)行標(biāo)識(shí)。

XA 規(guī)范的基礎(chǔ)是兩階段提交協(xié)議：

在第一階段，交易中間件請(qǐng)求所有相關(guān)數(shù)據(jù)庫(kù)準(zhǔn)備提交（預(yù)提交）各自的事務(wù)分支，以確認(rèn)是否所有相關(guān)數(shù)據(jù)庫(kù)都可以提交各自的事務(wù)分支。當(dāng)某一數(shù)據(jù)庫(kù)收到預(yù)提交后，如果可以提交屬于自己的事務(wù)分支，則將自己在該事務(wù)分支中所做的操作固定記錄下來(lái)，并給交易中間件一個(gè)同意提交的應(yīng)答，此時(shí)數(shù)據(jù)庫(kù)將不能再在該事務(wù)分支中加入任何操作，但此時(shí)數(shù)據(jù)庫(kù)并沒有真正提交該事務(wù)，數(shù)據(jù)庫(kù)對(duì)共享資源的操作還未釋放（處于鎖定狀態(tài)）。如果由于某種原因數(shù)據(jù)庫(kù)無(wú)法提交屬于自己的事務(wù)分支，它將回滾自己的所有操作，釋放對(duì)共享資源上的鎖，并返回給交易中間件失敗應(yīng)答。

在第二階段，交易中間件審查所有數(shù)據(jù)庫(kù)返回的預(yù)提交結(jié)果，如所有數(shù)據(jù)庫(kù)都可以提交，交易中間件將要求所有數(shù)據(jù)庫(kù)做正式提交，這樣該全局事務(wù)被提交。而如果有任一數(shù)據(jù)庫(kù)預(yù)提交返回失敗，交易中間件將要求所有其它數(shù)據(jù)庫(kù)回滾其操作，這樣該全局事務(wù)被回滾。

mysql 允許多個(gè)數(shù)據(jù)庫(kù)實(shí)例參與一個(gè)全局的事務(wù)。MySQL XA 的命令集合如下：

-- 開啟一個(gè)事務(wù)，并將事務(wù)置于 ACTIVE 狀態(tài)，此后執(zhí)行的 SQL 語(yǔ)句都將置于該是事務(wù)中。XA START xid-- 將事務(wù)置于 IDLE 狀態(tài)，表示事務(wù)內(nèi)的 SQL 操作完成。XA END xid-- 事務(wù)提交的準(zhǔn)備動(dòng)作，事務(wù)狀態(tài)置于 PREPARED 狀態(tài)。事務(wù)如果無(wú)法完成提交前的準(zhǔn)備操作，該語(yǔ)句會(huì)執(zhí)行失敗。XA PREPARE xid-- 事務(wù)最終提交，完成持久化。XA COMMIT xid-- 事務(wù)回滾終止XA ROLLBACK xid-- 查看 MySQL 中存在的 PREPARED 狀態(tài)的 xa 事務(wù)。XA RECOVER

MySQL 在 XA 事務(wù)中扮演的是參與者的角色，被事務(wù)協(xié)調(diào)器所支配。XA 事務(wù)比普通本地事務(wù)多了一個(gè) PREPARE 狀態(tài)，普通事務(wù)是 begin-> commit 而分布式事務(wù)是 begin->PREPARE 等其他數(shù)據(jù)庫(kù)事務(wù)都到 PREPARE 狀態(tài)的時(shí)候再 PREPARE->commit。分布式事務(wù) sql 示例：

 xa start 'aaa'; insert into table(xxx) values(xxx); xa end 'aaa'; xa prepare 'aaa'; xa commit 'aaa';

XA 事務(wù)存在的問(wèn)題：

• 單點(diǎn)問(wèn)題:事務(wù)管理器在整個(gè)流程中扮演的角色很關(guān)鍵，如果其宕機(jī)，比如在第一階段已經(jīng)完成，在第二階段正準(zhǔn)備提交的時(shí)候事務(wù)管理器宕機(jī)，資源管理器就會(huì)一直阻塞，導(dǎo)致數(shù)據(jù)庫(kù)無(wú)法使用。

• 同步阻塞:在準(zhǔn)備就緒之后，資源管理器中的資源一直處于阻塞狀態(tài)，直到提交完成才能釋放資源。

• 數(shù)據(jù)不一致:兩階段提交協(xié)議雖然為分布式數(shù)據(jù)強(qiáng)一致性所設(shè)計(jì)，但仍然存在數(shù)據(jù)不一致性的可能，比如在第二階段中，假設(shè)協(xié)調(diào)者發(fā)出了事務(wù) commit 的通知，但是因?yàn)榫W(wǎng)絡(luò)問(wèn)題該通知僅被一部分參與者所收到并執(zhí)行了 commit 操作，其余的參與者則因?yàn)闆]有收到通知一直處于阻塞狀態(tài)，這時(shí)候就產(chǎn)生了數(shù)據(jù)的不一致性。

TCC 分布式事務(wù)

TCC 又被稱作柔性事務(wù)，通過(guò)事務(wù)補(bǔ)償機(jī)制來(lái)達(dá)到事務(wù)的最終一致性，它不是強(qiáng)一致性的事務(wù)。TCC 將事務(wù)分為兩個(gè)階段，或者說(shuō)是由兩個(gè)事務(wù)組成的。相對(duì)于 XA 事務(wù)來(lái)說(shuō) TCC 的并發(fā)性更好，XA 是全局性的事務(wù)，而 TCC 是由兩個(gè)本地事務(wù)組成。

假設(shè)我們購(gòu)買一件商品，后臺(tái)需要操作兩張表——積分表加積分而庫(kù)存表扣庫(kù)存，這兩張表存在于兩個(gè)數(shù)據(jù)庫(kù)中，使用 TCC 事務(wù)執(zhí)行這一事務(wù)：

1）TCC 實(shí)現(xiàn)階段一：Try

在 try 階段并不是直接減庫(kù)存加積分，而是將相關(guān)數(shù)據(jù)改變?yōu)轭A(yù)備的狀態(tài)。庫(kù)存表先鎖定一個(gè)庫(kù)存，鎖定的方式可以預(yù)留一個(gè)鎖定字段，當(dāng)這個(gè)字段為一的時(shí)候表示這個(gè)商品被鎖定。積分表加一個(gè)數(shù)據(jù)，這個(gè)數(shù)據(jù)也是被鎖定狀態(tài)，鎖定方式和庫(kù)存表一樣。其 sql 形如：

update stock set lock=1 where id=1;insert into credits (lock,...) values (1,...)

這兩條 sql 如果都執(zhí)行成功則進(jìn)入 Confirm 階段，如果執(zhí)行不成功則進(jìn)入 Cancel 階段

2）TCC 實(shí)現(xiàn)階段二：Confirm

這一階段正式減庫(kù)存加積分訂單狀態(tài)改為已支付。執(zhí)行 sql 將鎖定的庫(kù)存扣除，為累加積分累加，以及一些其他的邏輯。

3）TCC 實(shí)現(xiàn)階段三：Cancel

當(dāng) try 階段執(zhí)行不成功，就會(huì)執(zhí)行這一階段，這個(gè)階段將鎖定的庫(kù)存還原，鎖定的積分刪除掉。退回到事務(wù)執(zhí)行前的狀態(tài)。

TCC 事務(wù)原理很簡(jiǎn)單，使用起來(lái)卻不簡(jiǎn)單。首先 TCC 事務(wù)對(duì)系統(tǒng)侵入性很大，其次是讓業(yè)務(wù)邏輯變得復(fù)雜。在實(shí)際使用中我們必須依賴 TCC 事務(wù)中間件才能讓 TCC 事務(wù)得以實(shí)現(xiàn)。通常一個(gè) TCC 事務(wù)實(shí)現(xiàn)大概是這樣子的：某個(gè)服務(wù)向外暴露了一個(gè)服務(wù)，這個(gè)服務(wù)對(duì)外正常調(diào)用，其他服務(wù)并不能感知到 TCC 事務(wù)的存在，而其服務(wù)內(nèi)部，分別實(shí)現(xiàn)了 Try,Confirm,Cancel 三個(gè)接口，注冊(cè)到 TCC 中間件上去。當(dāng)調(diào)用這個(gè)服務(wù)的時(shí)候，其事務(wù)操作由該服務(wù)和 TCC 中間件共同完成。

而 TCC 事務(wù)中間件還要做好其他事情，比如確保 Confirm 或者 Cancel 執(zhí)行成功，如果發(fā)現(xiàn)某個(gè)服務(wù)的 Cancel 或者 Confirm 一直沒成功，會(huì)不停的重試調(diào)用他的 Cancel 或者 Confirm 邏輯，務(wù)必要他成功！即使在嘗試多次后無(wú)法成功也能通知到系統(tǒng)需要人工排查異常。TCC 事務(wù)還要考慮一些異常情況的處理，比如說(shuō)訂單服務(wù)突然掛了，然后再次重啟，TCC 分布式事務(wù)框架要能夠保證之前沒執(zhí)行完的分布式事務(wù)繼續(xù)執(zhí)行。TCC 分布式事務(wù)框架還需要做好日志的記錄，保存下來(lái)分布式事務(wù)運(yùn)行的各個(gè)階段和狀態(tài)，以便系統(tǒng)上線后能夠排查異常，恢復(fù)數(shù)據(jù)。目前開源的 TCC 事務(wù)框架有：Seata ByteTCC tcc-transaction 等。

到此，相信大家對(duì)“怎么理解并掌握MySQL”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！