[TOC]

MySQL總結(jié)

一、基本概念

1、為什么要使用數(shù)據(jù)庫？

數(shù)據(jù)存放的位置通常由兩個(gè)地方，內(nèi)存和硬盤，存放在內(nèi)存中的數(shù)據(jù)訪問速度塊，但是系統(tǒng)斷電就會(huì)丟失；保存在硬盤中的數(shù)據(jù)，訪問數(shù)據(jù)慢，但是斷電不會(huì)丟失，而且可以存儲的數(shù)據(jù)量也遠(yuǎn)大于內(nèi)存。

所以就產(chǎn)生了數(shù)據(jù)庫的概念，它使得我們更加高效的使用以及管理數(shù)據(jù)，它有以下三個(gè)突出功能：

• 數(shù)據(jù)永久保存
• 使用SQL語言，查詢方法，效率高
• 數(shù)據(jù)管理更加方便

2、基本概念

SQL：結(jié)構(gòu)化查詢語言(Structured Query Language)簡稱SQL，是一種數(shù)據(jù)庫查詢語言。用于存取數(shù)據(jù)、查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)。

RDBMS：(Relational Database Management Syste）關(guān)系型數(shù)據(jù)庫管理系統(tǒng)

MySQL：MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，屬于 Oracle 旗下產(chǎn)品。MySQL是最好的 RDBMS應(yīng)用軟件之一 。

3、數(shù)據(jù)庫三大范式

第一范式：每個(gè)列都不可以再拆分。案例如下：

表 : 姓名，性別，電話

問題：若某個(gè)人有兩個(gè)電話，家庭電話和手機(jī)，這樣則不符合第一范式。

解決：把電話列分成兩個(gè)列即可。

第二范式：在第一范式的基礎(chǔ)上，非主鍵列完全依賴于主鍵，而不能是依賴于主鍵的一部分。(主要解決多對多的關(guān)系)

表 : 學(xué)號, 姓名, 年齡, 課程名稱, 成績, 學(xué)分;

問題：這個(gè)表明顯說明了兩個(gè)事務(wù):學(xué)生信息, 課程信息，不符合第二范式。

解決：分成學(xué)生表和課程表分別存儲即可，然后用一個(gè)選課表將兩張表關(guān)聯(lián)起來，解決多對多的問題。

第三范式：在第二范式的基礎(chǔ)上，非主鍵列只依賴于主鍵，不依賴于其他非主鍵。（主要解決一對多的關(guān)系）

表：學(xué)號, 姓名, 年齡, 所在學(xué)院, 學(xué)院聯(lián)系電話，關(guān)鍵字為單一關(guān)鍵字"學(xué)號";

問題： 可以看出來一個(gè)學(xué)院可以有多個(gè)學(xué)生，而且但是一個(gè)學(xué)院只有一個(gè)學(xué)院聯(lián)系電話，電話依賴與學(xué)院，不唯一依賴于主鍵，不滿足第三范式

解決：分出一個(gè)學(xué)院表，在學(xué)生表中分出一個(gè)學(xué)院id的外鍵約束

在設(shè)計(jì)數(shù)據(jù)庫結(jié)構(gòu)的時(shí)候，要盡量遵守三范式，如果不遵守，必須有足夠的理由。

二、MySQL入門

1、MySQL的基本數(shù)據(jù)類型

整數(shù)類型：包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1字節(jié)、2字節(jié)、3字節(jié)、4字節(jié)、8字節(jié)整數(shù)。任何整數(shù)類型都可以加上UNSIGNED屬性，表示數(shù)據(jù)是無符號的，即非負(fù)整數(shù)。

浮點(diǎn)型：包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用于存儲比BIGINT還大的整型，能存儲精確的小數(shù)；而FLOAT和DOUBLE是有取值范圍的，并支持使用標(biāo)準(zhǔn)的浮點(diǎn)進(jìn)行近似計(jì)算；計(jì)算時(shí)FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串進(jìn)行處理。

字符串型：包括VARCHAR、CHAR、TEXT、BLOB； 其中TEXT和BLOB又可以細(xì)分成四種規(guī)定的長度；BLOB可以存儲圖片,而TEXT不行。TEXT只能存儲純文本文件

日期類型：包括DATE、YEAR、TIME、DATETIME、TIMESTAMP

枚舉類型：ENUM

2、主鍵和外鍵

主鍵：數(shù)據(jù)庫表中對儲存數(shù)據(jù)對象予以唯一和完整標(biāo)識的數(shù)據(jù)列或?qū)傩缘慕M合。一個(gè)數(shù)據(jù)列只能有一個(gè)主鍵，且主鍵的取值不能缺失，即不能為空值（Null）

外鍵：在一個(gè)表中存在的另一個(gè)表的主鍵稱此表的外鍵。

3、SQL執(zhí)行語句分類

DDL：數(shù)據(jù)定義語言DDL（Data Ddefinition Language）CREATE，DROP，ALTER； 主要對邏輯結(jié)構(gòu)等有操作的，其中包括表結(jié)構(gòu)，視圖和索引。

DQL：數(shù)據(jù)查詢語言DQL（Data Query Language）SELECT；各種簡單查詢，連接查詢等 都屬于DQL。

DML：數(shù)據(jù)操縱語言DML（Data Manipulation Language）INSERT，UPDATE，DELETE； 對應(yīng)上面所說的查詢操作 DQL與DML共同構(gòu)建了多數(shù)初級程序員常用的增刪改查操作。而查詢是較為特殊的一種 被劃分到DQL中。

DCL：數(shù)據(jù)控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK； 主要對數(shù)據(jù)庫安全性完整性等有操作的，可以簡單的理解為權(quán)限控制等。

4、SQL 約束有哪幾種？

NOT NULL: 用于控制字段的內(nèi)容一定不能為空（NULL）。

UNIQUE: 控件字段內(nèi)容不能重復(fù)，一個(gè)表允許有多個(gè) Unique 約束。

PRIMARY KEY: 也是用于控件字段內(nèi)容不能重復(fù)，但它在一個(gè)表只允許出現(xiàn)一個(gè)。

FOREIGN KEY: 用于預(yù)防破壞表之間連接的動(dòng)作，也能防止非法數(shù)據(jù)插入外鍵列，因?yàn)樗仨毷撬赶虻哪莻€(gè)表中的值之一。

CHECK: 用于控制字段的值范圍。

5、幾種關(guān)聯(lián)查詢

1. 內(nèi)連接查詢（INNER JOIN）

   是指所有查詢出的結(jié)果都是能夠在連接的表中有對應(yīng)記錄的

2. 左外連接查詢 （LEFT OUTER JOIN）

   是指以左邊的表的數(shù)據(jù)為基準(zhǔn)，去匹配右邊的表的數(shù)據(jù)，如果匹配到就顯示，匹配不到就顯示為null

3. 右外連接查詢 （RIGHT OUTER JOIN）

   是指以右邊的表的數(shù)據(jù)為基準(zhǔn)，去匹配左邊的表的數(shù)據(jù)，如果匹配到就顯示，匹配不到就顯示為null

4. 全外連接查詢 （LEFT OUTER JOIN #### UNION #### RIGHT OUTER JOIN）

   顧名思義，把兩張表的字段都查出來，沒有對應(yīng)的值就顯示null；mysql是沒有全外連接的(mysql中沒有full outer join關(guān)鍵字)，想要達(dá)到全外連接的效果，可以使用union關(guān)鍵字連接左外連接和右外連接。

5. 自連接查詢

   自連接查詢就是當(dāng)前表與自身的連接查詢，關(guān)鍵點(diǎn)在于虛擬化出一張表給一個(gè)別名

這里可以參考一篇博客——Mysql中的關(guān)聯(lián)查詢(內(nèi)連接，外連接，自連接)

6、SQL關(guān)鍵字補(bǔ)充

varchar與char的區(qū)別

• char長度固定，varchar可變長
• 如果插入的字符沒有達(dá)到預(yù)設(shè)的長度時(shí)，char會(huì)自動(dòng)補(bǔ)齊，varchar按原始長度插入
• char因?yàn)槎ㄩL的原因，因此存取速度更快，而carchar更加節(jié)省空間

char(10)中10的含義

• 10位固定字符串，不足補(bǔ)空格 最多10個(gè)字符

varchar(50)中50的涵義

• 最多存放50個(gè)字符，varchar(50)和(200)存儲hello所占空間一樣，但后者在排序時(shí)會(huì)消耗更多內(nèi)存

int(20)中20的涵義

• 是指顯示字符的長度。20表示最大顯示寬度為20，但仍占4字節(jié)存儲，存儲范圍不變；

UNION與UNION ALL的區(qū)別？

• 如果使用UNION ALL，不會(huì)合并重復(fù)的記錄行
• 效率 UNION 高于 UNION ALL

drop、delete與truncate的區(qū)別

drop、delete與truncate的區(qū)別

三、存儲引擎

1、常見的存儲引擎

• InnoDB引擎：Innodb引擎提供了對數(shù)據(jù)庫ACID事務(wù)的支持。并且還提供了行級鎖和外鍵的約束。它的設(shè)計(jì)的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫系
• MyIASM引擎(原本Mysql的默認(rèn)引擎)：不提供事務(wù)的支持，也不支持行級鎖和外鍵。
• MEMORY引擎：所有的數(shù)據(jù)都在內(nèi)存中，數(shù)據(jù)的處理速度快，但是安全性不高。

2、InnoDB和MyISAM的區(qū)別

• 功能方面：InnoDB支持行級鎖(row-level locking)和表級鎖,默認(rèn)為行級鎖；InnoDB 提供事務(wù)支持事務(wù)，外部鍵等高級數(shù)據(jù)庫功能；InnoDB支持外鍵約束； InnoDB 支持MVCC（多版本并發(fā)控制）； 以上的所有MyISAM都不支持
• 存儲形式上：MyISAM每張表被存放在三個(gè)文件：frm-表格定義、MYD(MYData)-數(shù)據(jù)文件、MYI(MYIndex)-索引文件； 而InnoDB所有的表都保存在同一個(gè)數(shù)據(jù)文件中
• 性能上： MyISAM查詢更優(yōu)； 而InnoDB增刪改更優(yōu)
• 索引區(qū)別：
  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
  • InnoDB的主鍵索引的葉子節(jié)點(diǎn)存儲著行數(shù)據(jù)，因此主鍵索引非常高效。
  • MyISAM索引的葉子節(jié)點(diǎn)存儲的是行數(shù)據(jù)地址，需要再尋址一次才能得到數(shù)據(jù)。
  • InnoDB非主鍵索引的葉子節(jié)點(diǎn)存儲的是主鍵和其他帶索引的列數(shù)據(jù)，因此查詢時(shí)做到覆蓋索引會(huì)非常高效。

四、索引

1、什么是索引

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)，它們包含著對數(shù)據(jù)表里所有記錄的引用指針。

索引是一種數(shù)據(jù)結(jié)構(gòu)。數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實(shí)現(xiàn)通常使用B樹及其變種B+樹。

更通俗的說，索引就相當(dāng)于目錄。為了方便查找書中的內(nèi)容，通過對內(nèi)容建立索引形成目錄。索引是一個(gè)文件，它是要占據(jù)物理空間的。

2、索引的優(yōu)缺點(diǎn)

索引的優(yōu)點(diǎn)：

• 可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。
• 通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

索引的缺點(diǎn)：

• 時(shí)間方面：創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，具體地，當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)，會(huì)降低增/改/刪的執(zhí)行效率；
• 空間方面：索引需要占物理空間。

3、常見的索引類型

（1）前綴索引

前綴索引也叫局部索引，比如給身份證的前 10 位添加索引，類似這種給某列部分信息添加索引的方式叫做前綴索引。

語法：index(field(10))，使用字段值的前10個(gè)字符建立索引，默認(rèn)是使用字段的全部內(nèi)容建立索引。

（2）聚簇索引

將數(shù)據(jù)存儲與索引放到了一塊，找到索引也就找到了數(shù)據(jù)

（3）非聚簇索引

將數(shù)據(jù)存儲于索引分開結(jié)構(gòu)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)指向了數(shù)據(jù)的對應(yīng)行，myisam通過key_buffer把索引先緩存到內(nèi)存中，當(dāng)需要訪問數(shù)據(jù)時(shí)（通過索引訪問數(shù)據(jù)），在內(nèi)存中直接搜索索引，然后通過索引找到磁盤相應(yīng)數(shù)據(jù)，這也就是為什么索引不在key buffer命中時(shí)，速度慢的原因。

（4）聯(lián)合索引

MySQL可以使用多個(gè)字段同時(shí)建立一個(gè)索引，叫做聯(lián)合索引。在聯(lián)合索引中，如果想要命中索引，需要按照建立索引時(shí)的字段順序挨個(gè)使用，否則無法命中索引。

（5）主鍵索引

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

（6）唯一索引

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

• 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創(chuàng)建唯一索引
• 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創(chuàng)建唯一組合索引

（7）普通索引

基本的索引類型，沒有唯一性的限制，允許為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)建組合索引

（8）全文索引

是目前搜索引擎使用的一種關(guān)鍵技術(shù)?？梢酝ㄟ^ALTER TABLE table_name ADD FULLTEXT (column);創(chuàng)建全文索引

（9）覆蓋索引

如果一個(gè)索引包含（或者說覆蓋）所有需要查詢的字段的值，我們就稱之為“覆蓋索引”。覆蓋索引即需要查詢的字段正好是索引的字段，那么直接根據(jù)該索引，就可以查到數(shù)據(jù)了， 而無需回表查詢。

索引分類

4、（非）聚簇索引優(yōu)缺點(diǎn)分析

聚簇索引的優(yōu)點(diǎn)：

• 聚集索引的查詢速度非常的快，因?yàn)檎麄€(gè)B+樹本身就是一顆多叉平衡樹，葉子節(jié)點(diǎn)也都是有序的，定位到索引的節(jié)點(diǎn)，就相當(dāng)于定位到了數(shù)據(jù)。

聚簇索引的缺點(diǎn)：

• 依賴于有序的數(shù)據(jù) ：因?yàn)锽+樹是多路平衡樹，如果索引的數(shù)據(jù)不是有序的，那么就需要在插入時(shí)排序，如果數(shù)據(jù)是整型還好，否則類似于字符串或UUID這種又長又難比較的數(shù)據(jù)，插入或查找的速度肯定比較慢。
• 更新代價(jià)大 ： 如果對索引列的數(shù)據(jù)被修改時(shí)，那么對應(yīng)的索引也將會(huì)被修改， 而且況聚集索引的葉子節(jié)點(diǎn)還存放著數(shù)據(jù)，修改代價(jià)肯定是較大的， 所以對于主鍵索引來說，主鍵一般都是不可被修改的。

非聚簇索引的優(yōu)點(diǎn)：

• 更新代價(jià)比聚集索引要小 。非聚集索引的更新代價(jià)就沒有聚集索引那么大了，非聚集索引的葉子節(jié)點(diǎn)是不存放數(shù)據(jù)的

非聚簇索引的缺點(diǎn)：

• 跟聚集索引一樣，非聚集索引也依賴于有序的數(shù)據(jù)
• 可能會(huì)二次查詢(回表) :這應(yīng)該是非聚集索引最大的缺點(diǎn)了。 當(dāng)查到索引對應(yīng)的指針或主鍵后，可能還需要根據(jù)指針或主鍵再到數(shù)據(jù)文件或表中查詢。

5、 非聚集索引一定回表查詢嗎(覆蓋索引)?

非聚集索引不一定回表查詢。

如果發(fā)生了覆蓋索引的情況，那么就不會(huì)發(fā)生回表的現(xiàn)象，舉例說明如下，先有兩條SQL語句，看看他們是否進(jìn)行了回表操作：

# 只查詢name
SELECT username FROM table WHERE username='guang19';

# 查詢所有
SELECT * FROM table WHERE username='guang19';

• 對于第一條語句，查詢的類容就是索引的類容，所以不會(huì)發(fā)生回表操作
• 對于第二條語句，查詢索引，會(huì)首先通過非聚簇索引查詢出主鍵的值，然后再回表通過主鍵查詢出所需要的值

即使是MYISAM也是這樣，雖然MYISAM的主鍵索引確實(shí)需要回表， 因?yàn)樗闹麈I索引的葉子節(jié)點(diǎn)存放的是指針。但是如果SQL查的就是主鍵呢?

SELECT id FROM table WHERE id=1;

主鍵索引本身的key就是主鍵，查到返回就行了。這種情況就稱之為覆蓋索引了。

6、 索引常用的數(shù)據(jù)結(jié)構(gòu)

（1）哈希索引

對于哈希索引來說，底層的數(shù)據(jù)結(jié)構(gòu)就是哈希表，因此在絕大多數(shù)需求為單條記錄查詢的時(shí)候，可以選擇哈希索引，查詢性能最快；其余大部分場景，建議選擇BTree索引。

（2）BTree索引

B樹索引是Mysql數(shù)據(jù)庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實(shí)際是用B+樹實(shí)現(xiàn)的，因?yàn)樵诓榭幢硭饕龝r(shí)，mysql一律打印BTREE，所以簡稱為B樹索引）

（3）哈希索引和BTree索引的優(yōu)劣分析

• 在單條記錄的查詢效率上，哈希索引的效率是最快的，直接利用Hash函數(shù)計(jì)算出桶位置，接近O（1）的時(shí)間成本取出數(shù)據(jù)
• 在哈希索引中經(jīng)過hash函數(shù)建立索引之后，索引的順序與原順序無法保持一致，不能支持范圍查詢，所以在范圍查找中體現(xiàn)了BTree索引的優(yōu)勢
• 哈希索引沒有BTree索引豐富的索引機(jī)制，通值哈希索引的性能也不是很穩(wěn)定，當(dāng)數(shù)據(jù)量過大的時(shí)候，哈希索引的維護(hù)成本顯得更大。

7、B樹和B+樹的區(qū)別

首先，從數(shù)據(jù)結(jié)構(gòu)上來看：

• 在B樹中，數(shù)據(jù)存放在任意的節(jié)點(diǎn)；但是在B+樹中，只有葉子節(jié)點(diǎn)才會(huì)存放數(shù)據(jù)，而非葉子節(jié)點(diǎn)存放的都是方便搜索數(shù)據(jù)的鍵值。

• B樹中，每個(gè)葉子節(jié)點(diǎn)都是相互獨(dú)立的；但是在B+樹中，葉子節(jié)點(diǎn)之間存在一條相互連接的鏈。

  
  
  B樹和B+樹的區(qū)別

其次，從落地使用的角度來看：

• B樹可以在內(nèi)部節(jié)點(diǎn)同時(shí)存儲鍵和值，因此，把頻繁訪問的數(shù)據(jù)放在靠近根節(jié)點(diǎn)的地方將會(huì)大大提高熱點(diǎn)數(shù)據(jù)的查詢效率。這種特性使得B樹在特定數(shù)據(jù)重復(fù)多次查詢的場景中更加高效。
• 因?yàn)锽+樹的非葉子節(jié)點(diǎn)不存放數(shù)據(jù)，因此占用的內(nèi)存相對來說較小，這樣在B+樹的每一層，就可以劃分出更多的空間存放間隙值，是的最終劃分的間隙較小，降低樹的層數(shù)，減少IO操作；
• B+又因?yàn)樗械臄?shù)據(jù)均存放在葉子節(jié)點(diǎn)的中，所以不管那一次發(fā)起的查找，查到數(shù)據(jù)的時(shí)間成本都是一樣的，因此B+樹又被稱為多路平衡二叉樹。
• B+在進(jìn)行全盤數(shù)據(jù)遍歷的時(shí)候，只需要找到最左邊的葉子節(jié)點(diǎn)，然后通過遍歷的方式全盤掃描，效率很高。但是B數(shù)只能通過傳統(tǒng)樹的遍歷方式

總結(jié)來看：B樹的突出優(yōu)勢在于對應(yīng)靠近根節(jié)點(diǎn)的值查詢效率很高，B+樹的突出優(yōu)勢在于降低樹的層度、均衡查詢效率、減少IO操作（每一層就對應(yīng)一次IO）、提升全盤掃描效率等等。

8、MyISAM和InnoDB實(shí)現(xiàn)BTree索引方式的區(qū)別

（1）MyISAM實(shí)現(xiàn)索引的方式

B+Tree葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。在索引檢索的時(shí)候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，則取出其 data 域的值，然后以 data 域的值為地址讀取相應(yīng)的數(shù)據(jù)記錄。這被稱為“非聚簇索引”。

（2）InnoDB實(shí)現(xiàn)索引的方式

區(qū)別于MyISAM，InnoDB采用了聚簇索引的設(shè)計(jì)思想，非葉子節(jié)點(diǎn)中存放的收拾主鍵的值，葉子節(jié)點(diǎn)中存放了主鍵的值和對應(yīng)的行數(shù)據(jù)

• 當(dāng)使用主鍵索引進(jìn)行搜索的時(shí)候，可以直接通過搜索到主鍵key對應(yīng)得葉子節(jié)點(diǎn)獲取所有得數(shù)據(jù)信息
• 當(dāng)使用輔助索引進(jìn)行搜索得時(shí)候，會(huì)找到葉子節(jié)點(diǎn)中存儲的主鍵key值，然后再進(jìn)行回表操作，找到主鍵對應(yīng)的數(shù)據(jù)記錄。

9、索引的添加方式

（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）添加聯(lián)合索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

10、使用索引的原則

索引雖好，但也不是無限制的使用，最好符合一下幾個(gè)原則：

• 較頻繁作為查詢條件的字段才去創(chuàng)建索引

• 定義有外鍵的數(shù)據(jù)列一定要建立索引。

• 在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。

• 在經(jīng)常用在連接的列上，這 些列主要是一些外鍵，可以加快連接的速度；

• 使用短索引，如果對長字符串列進(jìn)行索引，應(yīng)該指定一個(gè)前綴長度，這樣能夠節(jié)省大量索引空間。

• ========================================================================================

• 區(qū)分度不是很高的列不適合做索引列(如性別，男女未知，最多也就三種，區(qū)分度實(shí)在太低)

• 盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可。

• 更新頻繁字段不適合創(chuàng)建索引

• 對于那些查詢中很少涉及的列，重復(fù)值比較多的列不要建立索引。

• 對于定義為text、image和bit的數(shù)據(jù)類型的列不要建立索引。

• 不要過度索引。索引需要額外的磁盤空間，并降低寫操作的性能。在修改表內(nèi)容的時(shí)候，索引會(huì)進(jìn)行更新甚至重構(gòu)，索引列越多，這個(gè)時(shí)間就會(huì)越長。所以只保持需要的索引有利于查詢即可。

11、什么是最左匹配原則

在聯(lián)合索引下，索引之間滿足一定的順序關(guān)系，尋找索引總是通過聯(lián)合索引的順序去優(yōu)先匹配；如User表的name和city加聯(lián)合索引就是(name,city)，而最左前綴原則指的是，如果查詢的時(shí)候查詢條件精確匹配索引的左邊連續(xù)一列或幾列，則此列就可以被用到。如下：

select * from user where name=xx and city=xx ; //可以命中索引 
select * from user where name=xx ;             // 可以命中索引 
select * from user where city=xx ;             // 無法命中索引           

這里需要注意的是，查詢的時(shí)候如果兩個(gè)條件都用上了，但是順序不同，如 city= xx and name ＝xx，那么現(xiàn)在的查詢引擎會(huì)自動(dòng)優(yōu)化為匹配聯(lián)合索引的順序，這樣是能夠命中索引的。

總結(jié)一下，假設(shè)有 a 、b、c、d四個(gè)索引構(gòu)成的聯(lián)合索引{a 、b、c、d}

• a 可以匹配到索引，因?yàn)槁?lián)合索引的第一條就是a
• a、b、c 也可以匹配到索引，因?yàn)榱兄g按照聯(lián)合索引的順序進(jìn)行放置了
• c、a 匹配不到索引，首先沒有達(dá)到聯(lián)合索引的全貌，最左邊不是a，滿足不了最左匹配原則

五、事務(wù)

1、什么是事務(wù)

事務(wù)是邏輯上的一組操作，要么都執(zhí)行，要么都不執(zhí)行。

事務(wù)最經(jīng)典也經(jīng)常被拿出來說例子就是轉(zhuǎn)賬了。假如小明要給小紅轉(zhuǎn)賬1000元，這個(gè)轉(zhuǎn)賬會(huì)涉及到兩個(gè)關(guān)鍵操作就是：將小明的余額減少1000元，將小紅的余額增加1000元。萬一在這兩個(gè)操作之間突然出現(xiàn)錯(cuò)誤比如銀行系統(tǒng)崩潰，導(dǎo)致小明余額減少而小紅的余額沒有增加，這樣就不對了。事務(wù)就是保證這兩個(gè)關(guān)鍵操作要么都成功，要么都要失敗。

2、事務(wù)的四大性質(zhì)

[圖片上傳失敗...(image-abe74c-1595819573477)]

1. 原子性（Atomicity）： 事務(wù)是最小的執(zhí)行單位，不允許分割。事務(wù)的原子性確保動(dòng)作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 執(zhí)行事務(wù)前后，數(shù)據(jù)保持一致，多個(gè)事務(wù)對同一個(gè)數(shù)據(jù)讀取的結(jié)果是相同的；
3. 隔離性（Isolation）： 并發(fā)訪問數(shù)據(jù)庫時(shí)，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨(dú)立的；
4. 持久性（Durability）： 一個(gè)事務(wù)被提交之后。它對數(shù)據(jù)庫中數(shù)據(jù)的改變是持久的，即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響。

3、并發(fā)事務(wù)會(huì)帶來哪些問題

并發(fā)事務(wù)的理解 ： 多個(gè)事務(wù)同時(shí)對數(shù)據(jù)庫進(jìn)行操作，比如一讀一寫，一讀一改等，會(huì)造成以下一些嚴(yán)重的問題

臟讀（Dirty read）:當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)并且對數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中；這時(shí)另外一個(gè)事務(wù)也訪問了這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。第二事務(wù)拿到的數(shù)據(jù)不是當(dāng)前數(shù)據(jù)庫中的實(shí)際數(shù)據(jù)，這種現(xiàn)象稱為臟讀。

丟失修改（Lost to modify）: 兩個(gè)事務(wù)同時(shí)對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行修改，比如同時(shí)做加1操作，因?yàn)榈谝粋€(gè)事務(wù)執(zhí)行加1操作還沒有提交的時(shí)候，另一個(gè)事務(wù)也拿到相同的數(shù)據(jù)進(jìn)行加1操作，使得兩次加1操作的結(jié)果就變成了一次，這種現(xiàn)象叫做丟失修改。

不可重復(fù)讀（Unrepeatableread）: 指在一個(gè)事務(wù)內(nèi)多次讀同一數(shù)據(jù)。另一個(gè)事務(wù)在此期間進(jìn)行了修改，當(dāng)時(shí)事務(wù)在前后兩次讀取的數(shù)據(jù)不一樣，這種現(xiàn)象叫做不可重讀。

幻讀（Phantom read）: 幻讀與不可重復(fù)讀類似，區(qū)別在于幻讀在讀取數(shù)據(jù)的時(shí)候，發(fā)現(xiàn)記錄增加了或者減少了，他是一種對記錄數(shù)的變更。比如一個(gè)事務(wù)（T1）讀取了幾行數(shù)據(jù)，接著另一個(gè)并發(fā)事務(wù)（T2）插入了一些數(shù)據(jù)時(shí)。在隨后的查詢中，第一個(gè)事務(wù)（T1）就會(huì)發(fā)現(xiàn)多了一些原本不存在的記錄

4、事務(wù)的隔離級別

• READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數(shù)據(jù)變更，可能會(huì)導(dǎo)致臟讀、幻讀或不可重復(fù)讀。
• READ-COMMITTED(讀取已提交)： 允許讀取并發(fā)事務(wù)已經(jīng)提交的數(shù)據(jù)，可以阻止臟讀，但是幻讀或不可重復(fù)讀仍有可能發(fā)生。
• REPEATABLE-READ(可重復(fù)讀)： 對同一字段的多次讀取結(jié)果都是一致的，除非數(shù)據(jù)是被本身事務(wù)自己所修改，可以阻止臟讀和不可重復(fù)讀，但幻讀仍有可能發(fā)生。
• SERIALIZABLE(可串行化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務(wù)依次逐個(gè)執(zhí)行，這樣事務(wù)之間就完全不可能產(chǎn)生干擾，也就是說，該級別可以防止臟讀、不可重復(fù)讀以及幻讀。

MySQL的默認(rèn)事務(wù)隔離級別是REPEATABLE-READ(可重復(fù)讀)， Oracle默認(rèn)事務(wù)隔離級別是READ-COMMITTED(讀取已提交)

事務(wù)隔離機(jī)制的實(shí)現(xiàn)基于鎖機(jī)制和并發(fā)調(diào)度。其中并發(fā)調(diào)度使用的是MVVC（多版本并發(fā)控制），通過保存修改的舊版本信息來支持并發(fā)一致性讀和回滾等特性。因?yàn)楦綦x級別越低，事務(wù)請求的鎖越少，所以大部分?jǐn)?shù)據(jù)庫系統(tǒng)的隔離級別都是READ-COMMITTED(讀取提交內(nèi)容):，但是你要知道的是InnoDB 存儲引擎默認(rèn)使用 REPEATABLE-READ（可重讀）并不會(huì)有任何性能損失，原因就在于MVCC。

InnoDB 存儲引擎在 分布式事務(wù) 的情況下一般會(huì)用到SERIALIZABLE(可串行化)隔離級別。

5、常用的事務(wù)控制語句

• START TRANSACTION或者BEGIN：顯示的開啟事務(wù)。需要注意的是在存儲過程中只能用START TRANSACTION開啟事務(wù)，因?yàn)榇鎯^程本來有BEGIN…END語法，兩者會(huì)沖突。
• COMMIT：提交事務(wù)。也可以寫成COMMIT WORK。
• ROLLBACK：回滾事務(wù)。也可以寫成ROLLBACK WORK。
• SAVEPOINT identifier：自定義保存點(diǎn)，適用于長事務(wù)，可以回滾到我們自定義的位置。
• RELEASE SAVEPOINT identifier：刪除一定保存點(diǎn)，如果沒有保存點(diǎn)的時(shí)候，會(huì)報(bào)錯(cuò)
• ROLLBACK TO[SAVEPOINT] identifier：回滾到指定保存點(diǎn)。

六、鎖

1、隔離級別與鎖的關(guān)系

在Read Uncommitted級別下，讀取數(shù)據(jù)不需要加共享鎖，這樣就不會(huì)跟被修改的數(shù)據(jù)上的排他鎖沖突；

在Read Committed級別下，讀操作需要加共享鎖，但是在語句執(zhí)行完以后釋放共享鎖；

在Repeatable Read級別下，讀操作需要加共享鎖，但是在事務(wù)提交之前并不釋放共享鎖，也就是必須等待事務(wù)執(zhí)行完畢以后才釋放共享鎖。

SERIALIZABLE 是限制性最強(qiáng)的隔離級別，因?yàn)樵摷墑e鎖定整個(gè)范圍的鍵，并一直持有鎖，直到事務(wù)完成。

2、鎖的分類

（1）按照鎖的粒度劃分

• 行級鎖： 行級鎖是Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對當(dāng)前操作的行進(jìn)行加鎖。行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖 和 排他鎖。特點(diǎn)：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
• 頁級鎖：頁級鎖是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。特點(diǎn)：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
• 表級鎖：表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當(dāng)前操作的整張表加鎖，它實(shí)現(xiàn)簡單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）與表獨(dú)占寫鎖（排他鎖）。特點(diǎn)：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)出鎖沖突的概率最高，并發(fā)度最低。

（2）按照類別進(jìn)行劃分

• 共享鎖：又叫做讀鎖。 當(dāng)用戶要進(jìn)行數(shù)據(jù)的讀取時(shí)，對數(shù)據(jù)加上共享鎖。共享鎖可以同時(shí)加上多個(gè)。
• 排它鎖：又叫做寫鎖。 當(dāng)用戶要進(jìn)行數(shù)據(jù)的寫入時(shí)，對數(shù)據(jù)加上排他鎖。排他鎖只可以加一個(gè)，他和其他的排他鎖，共享鎖都相斥。

3、MySQL中InnoDB引擎的行鎖是怎么實(shí)現(xiàn)的？

InnoDB是基于索引來完成行鎖

例: select * from tab_with_index where id = 1 for update;

for update 可以根據(jù)條件來完成行鎖鎖定，并且 id 是有索引鍵的列，如果 id 不是索引鍵那么InnoDB將完成表鎖，并發(fā)將無從談起

4、InnoDB存儲引擎的鎖的算法

• Record lock：單個(gè)行記錄上的鎖
• Gap lock：間隙鎖，鎖定一個(gè)范圍，不包括記錄本身
• Next-key lock：record+gap 鎖定一個(gè)范圍，包含記錄本身

5、InnoDB對鎖算法的應(yīng)用

• InnoDB對于行查詢使用的是next-key lock
• 因?yàn)閚ext-key lock實(shí)際上就是一種間隙鎖，因此可以有效避免幻讀的產(chǎn)生。
• 當(dāng)查詢的索引含有唯一屬性時(shí)，將next-key lock降級為record key
• Gap鎖設(shè)計(jì)的目的是為了阻止多個(gè)事務(wù)將記錄插入到同一范圍內(nèi)，而這會(huì)導(dǎo)致幻讀問題的產(chǎn)生

6、什么是死鎖？怎么解決？

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

常見的解決死鎖的方法

1、如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序訪問表，可以大大降低死鎖機(jī)會(huì)。

2、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；

3、對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產(chǎn)生的概率；

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

7、數(shù)據(jù)庫的樂觀鎖和悲觀鎖實(shí)現(xiàn)

悲觀鎖：假定會(huì)發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作。在查詢完數(shù)據(jù)的時(shí)候就把事務(wù)鎖起來，直到提交事務(wù)。實(shí)現(xiàn)方式：使用數(shù)據(jù)庫中的鎖機(jī)制

樂觀鎖：假設(shè)不會(huì)發(fā)生并發(fā)沖突，只在提交操作時(shí)檢查是否違反數(shù)據(jù)完整性。在修改數(shù)據(jù)的時(shí)候把事務(wù)鎖起來，通過version的方式來進(jìn)行鎖定。實(shí)現(xiàn)方式：樂觀鎖一般會(huì)使用版本號機(jī)制或CAS算法實(shí)現(xiàn)。

兩種鎖的使用場景

從上面對兩種鎖的介紹，我們知道兩種鎖各有優(yōu)缺點(diǎn)，不可認(rèn)為一種好于另一種，像樂觀鎖適用于寫比較少的情況下（多讀場景），即沖突真的很少發(fā)生的時(shí)候，這樣可以省去了鎖的開銷，加大了系統(tǒng)的整個(gè)吞吐量。

但如果是多寫的情況，一般會(huì)經(jīng)常產(chǎn)生沖突，這就會(huì)導(dǎo)致上層應(yīng)用會(huì)不斷的進(jìn)行retry，這樣反倒是降低了性能，所以一般多寫的場景下用悲觀鎖就比較合適。

七、表優(yōu)化

1、單表記錄過大，怎么解決？

（1）限制數(shù)據(jù)范圍

務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句。比如：我們當(dāng)用戶在查詢訂單歷史的時(shí)候，我們可以控制在一個(gè)月的范圍內(nèi)。

（2）讀/寫分離

經(jīng)典的數(shù)據(jù)庫拆分方案，主庫負(fù)責(zé)寫，從庫負(fù)責(zé)讀；

（3）緩存

使用MySQL的緩存，另外對重量級、更新少的數(shù)據(jù)可以考慮使用應(yīng)用級別的緩存；

（4）垂直分表

根據(jù)數(shù)據(jù)庫里面數(shù)據(jù)表的相關(guān)性進(jìn)行拆分。 把一張表拆分成多張表

（5）水平分表

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略存儲數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫中，達(dá)到了分布式的目的。 水平拆分可以支撐非常大的數(shù)據(jù)量。

2、分庫分表的理解

（1）垂直拆分

根據(jù)數(shù)據(jù)庫里面數(shù)據(jù)表的相關(guān)性進(jìn)行拆分。 例如，用戶表中既有用戶的登錄信息又有用戶的基本信息，可以將用戶表拆分成兩個(gè)單獨(dú)的表，甚至放到單獨(dú)的庫做分庫。簡單來說垂直拆分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。把主鍵和一些列放在一個(gè)表，然后把主鍵和另外的列放在另一個(gè)表中。

垂直拆分

垂直拆分的優(yōu)點(diǎn)： 可以使得行數(shù)據(jù)變小，在查詢時(shí)減少讀取的Block數(shù)，減少I/O次數(shù)。此外，垂直分區(qū)可以簡化表的結(jié)構(gòu)，易于維護(hù)。

垂直拆分的缺點(diǎn)： 主鍵會(huì)出現(xiàn)冗余，需要管理冗余列，并會(huì)引起Join操作，可以通過在應(yīng)用層進(jìn)行Join來解決。此外，垂直分區(qū)會(huì)讓事務(wù)變得更加復(fù)雜；

適用場景：

• 如果一個(gè)表中某些列常用，另外一些列不常用
• 可以使數(shù)據(jù)行變小，一個(gè)數(shù)據(jù)頁能存儲更多數(shù)據(jù)，查詢時(shí)減少I/O次數(shù)

（2）水平拆分

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略存儲數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫中，達(dá)到了分布式的目的。 水平拆分可以支撐非常大的數(shù)據(jù)量。

水平拆分是指數(shù)據(jù)表行的拆分，表的行數(shù)超過200萬行時(shí)，就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來存放。舉個(gè)例子：我們可以將用戶信息表拆分成多個(gè)用戶信息表，這樣就可以避免單一表數(shù)據(jù)量過大對性能造成影響。

水平拆分

水平拆分可以支持非常大的數(shù)據(jù)量。需要注意的一點(diǎn)是:分表僅僅是解決了單一表數(shù)據(jù)過大的問題，但由于表的數(shù)據(jù)還是在同一臺機(jī)器上，其實(shí)對于提升MySQL并發(fā)能力沒有什么意義，所以水平拆分最好分庫 。

水平拆分的缺點(diǎn)：

• 給應(yīng)用增加復(fù)雜度，通常查詢時(shí)需要多個(gè)表名，查詢所有數(shù)據(jù)都需UNION操作；
• 在許多數(shù)據(jù)庫應(yīng)用中，這種復(fù)雜度會(huì)超過它帶來的優(yōu)點(diǎn)，查詢時(shí)會(huì)增加讀一個(gè)索引層的磁盤次數(shù)

（3）水平拆分的常見方案

• 客戶端代理： 分片邏輯在應(yīng)用端，封裝在jar包中，通過修改或者封裝JDBC層來實(shí)現(xiàn)。 當(dāng)當(dāng)網(wǎng)的 Sharding-JDBC 、阿里的TDDL是兩種比較常用的實(shí)現(xiàn)。
• 中間件代理： 在應(yīng)用和數(shù)據(jù)中間加了一個(gè)代理層。分片邏輯統(tǒng)一維護(hù)在中間件服務(wù)中。 我們現(xiàn)在談的 Mycat 、360的Atlas、網(wǎng)易的DDB等等都是這種架構(gòu)的實(shí)現(xiàn)。

（4）分庫分表后面臨的問題

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

• 跨庫join：只要是進(jìn)行切分，跨節(jié)點(diǎn)Join的問題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類情況的發(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ù)問題： 這些是一類問題，因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案：與解決跨節(jié)點(diǎn)join問題的類似，分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行，因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大，對應(yīng)用程序內(nèi)存的消耗是一個(gè)問題。

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

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

3、MySQL的復(fù)制原理以及流程

（1）什么是主從復(fù)制

將主數(shù)據(jù)庫中的DDL和DML操作通過二進(jìn)制日志（BINLOG）傳輸?shù)綇臄?shù)據(jù)庫上，然后將這些日志重新執(zhí)行（重做）；從而使得從數(shù)據(jù)庫的數(shù)據(jù)與主數(shù)據(jù)庫保持一致。

（2）基本流程

主從復(fù)制

• 主表master在每個(gè)事務(wù)更新數(shù)據(jù)完成之前，將該操作記錄串行地寫入到binlog文件中。
• 從表salve開啟一個(gè)I/O Thread，讀取主表中的binlog日志
• 從表salve通過I/O操作經(jīng)讀取到的日志寫進(jìn)自己的Relaylog中
• 然后交給從表的執(zhí)行引擎進(jìn)行執(zhí)行

（3）MySQL主從復(fù)制解決的問題

• 主數(shù)據(jù)庫出現(xiàn)問題，可以切換到從數(shù)據(jù)庫。
• 可以進(jìn)行數(shù)據(jù)庫層面的讀寫分離。
• 負(fù)載均衡：降低單個(gè)服務(wù)器的壓力
• 可以在從數(shù)據(jù)庫上進(jìn)行日常備份。

4、讀寫分離有哪些解決方案？

讀寫分離是依賴于主從復(fù)制，而主從復(fù)制又是為讀寫分離服務(wù)的。因?yàn)橹鲝膹?fù)制要求slave不能寫只能讀（如果對slave執(zhí)行寫操作，那么show slave status將會(huì)呈現(xiàn)Slave_SQL_Running=NO，此時(shí)你需要按照前面提到的手動(dòng)同步一下slave）。

（1）方案一

使用mysql-proxy代理

優(yōu)點(diǎn)：直接實(shí)現(xiàn)讀寫分離和負(fù)載均衡，不用修改代碼，master和slave用一樣的帳號，mysql官方不建議實(shí)際生產(chǎn)中使用

缺點(diǎn)：降低性能， 不支持事務(wù)

（2）方案二

使用AbstractRoutingDataSource+aop+annotation在dao層決定數(shù)據(jù)源。

如果采用了mybatis， 可以將讀寫分離放在ORM層，比如mybatis可以通過mybatis plugin攔截sql語句，所有的insert/update/delete都訪問master庫，所有的select 都訪問salve庫，這樣對于dao層都是透明。 plugin實(shí)現(xiàn)時(shí)可以通過注解或者分析語句是讀寫方法來選定主從庫。不過這樣依然有一個(gè)問題， 也就是不支持事務(wù)， 所以我們還需要重寫一下DataSourceTransactionManager， 將read-only的事務(wù)扔進(jìn)讀庫， 其余的有讀有寫的扔進(jìn)寫庫。

(3）方案三

使用AbstractRoutingDataSource+aop+annotation在service層決定數(shù)據(jù)源，可以支持事務(wù).

缺點(diǎn)：類內(nèi)部方法通過this.xx()方式相互調(diào)用時(shí)，aop不會(huì)進(jìn)行攔截，需進(jìn)行特殊處理。

八、執(zhí)行流程

1、MySQL 基本架構(gòu)

下圖是 MySQL 的一個(gè)簡要架構(gòu)圖 ，首先簡單介紹一下下圖涉及的一些組件的基本作用

MySQL 基本架構(gòu)

• 連接器：身份認(rèn)證和權(quán)限相關(guān)(登錄 MySQL 的時(shí)候)。
• 查詢緩存: 執(zhí)行查詢語句的時(shí)候，會(huì)先查詢緩存（MySQL 8.0 版本后移除，因?yàn)檫@個(gè)功能不太實(shí)用）。
• 分析器: 沒有命中緩存的話，SQL 語句就會(huì)經(jīng)過分析器，分析器說白了就是要先看你的 SQL 語句要干嘛，再檢查你的 SQL 語句語法是否正確。
• 優(yōu)化器： 按照 MySQL 認(rèn)為最優(yōu)的方案去執(zhí)行。
• 執(zhí)行器: 執(zhí)行語句，然后從存儲引擎返回?cái)?shù)據(jù)。

簡單來說 MySQL 主要分為 Server 層和存儲引擎層：

• Server 層：主要包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，所有跨存儲引擎的功能都在這一層實(shí)現(xiàn)，比如存儲過程、觸發(fā)器、視圖，函數(shù)等，還有一個(gè)通用的日志模塊 binglog 日志模塊。
• 存儲引擎： 主要負(fù)責(zé)數(shù)據(jù)的存儲和讀取，采用可以替換的插件式架構(gòu)，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲引擎，其中 InnoDB 引擎有自有的日志模塊 redolog 模塊。現(xiàn)在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始就被當(dāng)做默認(rèn)存儲引擎了。

2、SQL 語句的生命周期

（1）查詢語句

sql 可以分為兩種，一種是查詢，一種是更新（增加，更新，刪除）。我們先分析下查詢語句，語句如下：

select * from tb_student  A where A.age='18' and A.name=' 張三 ';

分析下這個(gè)語句的執(zhí)行流程：

• 先檢查該語句是否有權(quán)限，如果沒有權(quán)限，直接返回錯(cuò)誤信息，如果有權(quán)限，在 MySQL8.0 版本以前，會(huì)先查詢緩存，以這條 sql 語句為 key 在內(nèi)存中查詢是否有結(jié)果，如果有直接緩存，如果沒有，執(zhí)行下一步。

• 通過分析器進(jìn)行詞法分析，提取 sql 語句的關(guān)鍵元素，比如提取上面這個(gè)語句是查詢 select，提取需要查詢的表名為 tb_student,需要查詢所有的列，查詢條件是這個(gè)表的 id='1'。然后判斷這個(gè) sql 語句是否有語法錯(cuò)誤，比如關(guān)鍵詞是否正確等等，如果檢查沒問題就執(zhí)行下一步。

• 接下來就是優(yōu)化器進(jìn)行確定執(zhí)行方案，上面的 sql 語句，可以有兩種執(zhí)行方案：

  a.先查詢學(xué)生表中姓名為“張三”的學(xué)生，然后判斷是否年齡是 18。

  b.先找出學(xué)生中年齡 18 歲的學(xué)生，然后再查詢姓名為“張三”的學(xué)生。

• 那么優(yōu)化器根據(jù)自己的優(yōu)化算法進(jìn)行選擇執(zhí)行效率最好的一個(gè)方案（優(yōu)化器認(rèn)為，有時(shí)候不一定最好）。那么確認(rèn)了執(zhí)行計(jì)劃后就準(zhǔn)備開始執(zhí)行了。

• 進(jìn)行權(quán)限校驗(yàn)，如果沒有權(quán)限就會(huì)返回錯(cuò)誤信息，如果有權(quán)限就會(huì)調(diào)用數(shù)據(jù)庫引擎接口，返回引擎的執(zhí)行結(jié)果。

（2）更新語句

以上就是一條查詢 sql 的執(zhí)行流程，那么接下來我們看看一條更新語句如何執(zhí)行的呢？sql 語句如下：

update tb_student A set A.age='19' where A.name=' 張三 ';

我們來給張三修改下年齡，在實(shí)際數(shù)據(jù)庫肯定不會(huì)設(shè)置年齡這個(gè)字段的，不然要被技術(shù)負(fù)責(zé)人打的。其實(shí)更新語句也基本上會(huì)沿著上一個(gè)查詢的流程走，只不過執(zhí)行更新的時(shí)候肯定要記錄日志啦，這就會(huì)引入日志模塊了，MySQL 自帶的日志模塊式 binlog（歸檔日志） ，所有的存儲引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個(gè)日志模塊 redo log（重做日志），我們就以 InnoDB 模式下來探討這個(gè)語句的執(zhí)行流程。流程如下：

先查詢到張三這一條數(shù)據(jù)，如果有緩存，也是會(huì)用到緩存。

• 然后拿到查詢的語句，把 age 改為 19，然后調(diào)用引擎 API 接口，寫入這一行數(shù)據(jù)，InnoDB 引擎把數(shù)據(jù)保存在內(nèi)存中，同時(shí)記錄 redo log，此時(shí) redo log 進(jìn)入 prepare 狀態(tài)，然后告訴執(zhí)行器，執(zhí)行完成了，隨時(shí)可以提交。
• 執(zhí)行器收到通知后記錄 binlog，然后調(diào)用引擎接口，提交 redo log 為提交狀態(tài)。
• 更新完成。

（3）為什么要用兩個(gè)日志模塊，用一個(gè)日志模塊不行嗎?

這是因?yàn)樽铋_始 MySQL 并沒與 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自帶的引擎是 MyISAM，但是我們知道 redo log 是 InnoDB 引擎特有的，其他存儲引擎都沒有，這就導(dǎo)致會(huì)沒有 crash-safe 的能力(crash-safe 的能力即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失)，binlog 日志只能用來歸檔。

并不是說只用一個(gè)日志模塊不可以，只是 InnoDB 引擎就是通過 redo log 來支持事務(wù)的。那么，又會(huì)有同學(xué)問，我用兩個(gè)日志模塊，但是不要這么復(fù)雜行不行，為什么 redo log 要引入 prepare 預(yù)提交狀態(tài)？這里我們用反證法來說明下為什么要這么做？

• 先寫 redo log 直接提交，然后寫 binlog，假設(shè)寫完 redo log 后，機(jī)器掛了，binlog 日志沒有被寫入，那么機(jī)器重啟后，這臺機(jī)器會(huì)通過 redo log 恢復(fù)數(shù)據(jù)，但是這個(gè)時(shí)候 bingog 并沒有記錄該數(shù)據(jù)，后續(xù)進(jìn)行機(jī)器備份的時(shí)候，就會(huì)丟失這一條數(shù)據(jù)，同時(shí)主從同步也會(huì)丟失這一條數(shù)據(jù)。
• 先寫 binlog，然后寫 redo log，假設(shè)寫完了 binlog，機(jī)器異常重啟了，由于沒有 redo log，本機(jī)是無法恢復(fù)這一條記錄的，但是 binlog 又有記錄，那么和上面同樣的道理，就會(huì)產(chǎn)生數(shù)據(jù)不一致的情況。

如果采用 redo log 兩階段提交的方式就不一樣了，寫完 binglog 后，然后再提交 redo log 就會(huì)防止出現(xiàn)上述的問題，從而保證了數(shù)據(jù)的一致性。那么問題來了，有沒有一個(gè)極端的情況呢？假設(shè) redo log 處于預(yù)提交狀態(tài)，binglog 也已經(jīng)寫完了，這個(gè)時(shí)候發(fā)生了異常重啟會(huì)怎么樣呢？ 這個(gè)就要依賴于 MySQL 的處理機(jī)制了，MySQL 的處理過程如下：

• 判斷 redo log 是否完整，如果判斷是完整的，就立即提交。
• 如果 redo log 只是預(yù)提交但不是 commit 狀態(tài)，這個(gè)時(shí)候就會(huì)去判斷 binlog 是否完整，如果完整就提交 redo log, 不完整就回滾事務(wù)。

這樣就解決了數(shù)據(jù)一致性的問題

3、SQL執(zhí)行很慢的原因

參考自：騰訊面試：一條SQL語句執(zhí)行得很慢的原因有哪些？---不看后悔系列

（1）SQL刷臟頁機(jī)制

當(dāng)我們要往數(shù)據(jù)庫插入一條數(shù)據(jù)、或者要更新一條數(shù)據(jù)的時(shí)候，我們知道數(shù)據(jù)庫會(huì)在內(nèi)存中把對應(yīng)字段的數(shù)據(jù)更新了，但是更新之后，這些更新的字段并不會(huì)馬上同步持久化到磁盤中去，而是把這些更新的記錄寫入到 redo log 日記中去，等到空閑的時(shí)候，在通過 redo log 里的日記把最新的數(shù)據(jù)同步到磁盤中去。

刷臟頁有下面4種場景（后兩種不用太關(guān)注“性能”問題）：

• redolog寫滿了：redo log 里的容量是有限的，如果數(shù)據(jù)庫一直很忙，更新又很頻繁，這個(gè)時(shí)候 redo log 很快就會(huì)被寫滿了，這個(gè)時(shí)候就沒辦法等到空閑的時(shí)候再把數(shù)據(jù)同步到磁盤的，只能暫停其他操作，全身心來把數(shù)據(jù)同步到磁盤中去的，而這個(gè)時(shí)候，就會(huì)導(dǎo)致我們平時(shí)正常的SQL語句突然執(zhí)行的很慢，所以說，數(shù)據(jù)庫在在同步數(shù)據(jù)到磁盤的時(shí)候，就有可能導(dǎo)致我們的SQL語句執(zhí)行的很慢了。
• 內(nèi)存不夠用了：如果一次查詢較多的數(shù)據(jù)，恰好碰到所查數(shù)據(jù)頁不在內(nèi)存中時(shí)，需要申請內(nèi)存，而此時(shí)恰好內(nèi)存不足的時(shí)候就需要淘汰一部分內(nèi)存數(shù)據(jù)頁，如果是干凈頁，就直接釋放，如果恰好是臟頁就需要刷臟頁。
• MySQL 認(rèn)為系統(tǒng)“空閑”的時(shí)候：這時(shí)系統(tǒng)沒什么壓力。
• MySQL 正常關(guān)閉的時(shí)候：這時(shí)候，MySQL 會(huì)把內(nèi)存的臟頁都 flush 到磁盤上，這樣下次 MySQL 啟動(dòng)的時(shí)候，就可以直接從磁盤上讀數(shù)據(jù)，啟動(dòng)速度會(huì)很快。

（2）鎖機(jī)制

這個(gè)就比較容易想到了，我們要執(zhí)行的這條語句，剛好這條語句涉及到的表，別人在用，并且加鎖了，我們拿不到鎖，只能慢慢等待別人釋放鎖了。或者，表沒有加鎖，但要使用到的某個(gè)一行被加鎖了，這個(gè)時(shí)候，我也沒辦法啊。

如果要判斷是否真的在等待鎖，我們可以用 show processlist這個(gè)命令來查看當(dāng)前的狀態(tài)哦，這里我要提醒一下，有些命令最好記錄一下，反正，我被問了好幾個(gè)命令，都不知道怎么寫，呵呵。

（3）選錯(cuò)索引

我們先來假設(shè)我們有一個(gè)表，表里有下面兩個(gè)字段,分別是主鍵 id，和兩個(gè)普通字段 c 和 d。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

沒有用上索引，我覺得這個(gè)原因是很多人都能想到的，例如你要查詢這條語句

1） 字段沒有索引

select * from t where 100 <c and c < 100000;

剛好你的 c 字段上沒有索引，那么抱歉，只能走全表掃描了，你就體驗(yàn)不會(huì)索引帶來的樂趣了，所以，這回導(dǎo)致這條查詢語句很慢。

2） 字段有索引，但卻沒有用索引

這個(gè)時(shí)候給 c 這個(gè)字段加上了索引，然后又查詢了一條語句

select * from t where c - 1 = 1000;

這里查詢用不到c的索引，因?yàn)樵诘忍栕筮厡進(jìn)行了計(jì)算

3） 字段有索引，且用到了索引

正確的查詢應(yīng)該如下

select * from t where c = 1000 + 1;

4 ） 函數(shù)操作導(dǎo)致沒有用上索引

如果我們在查詢的時(shí)候，對字段進(jìn)行了函數(shù)操作，也是會(huì)導(dǎo)致沒有用上索引的，例如

select * from t where pow(c,2) = 1000;

所以呢，一條語句執(zhí)行都很慢的時(shí)候，可能是該語句沒有用上索引了，不過具體是啥原因?qū)е聸]有用上索引的呢，你就要會(huì)分析了，我上面列舉的三個(gè)原因，應(yīng)該是出現(xiàn)的比較多的吧。

（4）數(shù)據(jù)庫自己選錯(cuò)索引

由于統(tǒng)計(jì)的失誤，導(dǎo)致系統(tǒng)沒有走索引，而是走了全表掃描，而這，也是導(dǎo)致我們 SQL 語句執(zhí)行的很慢的原因。

（5）總結(jié)

通過以上分析可以發(fā)現(xiàn)SQL執(zhí)行很慢大致可以分為兩種情況：

• 大多數(shù)情況下很正常，偶爾很慢，則有如下原因：

  • 數(shù)據(jù)庫在刷新臟頁，例如 redo log 寫滿了需要同步到磁盤。
  • 執(zhí)行的時(shí)候，遇到鎖，如表鎖、行鎖。

• 這條 SQL 語句一直執(zhí)行的很慢，則有如下原因

  • 沒有用上索引：例如該字段沒有索引；由于對字段進(jìn)行運(yùn)算、函數(shù)操作導(dǎo)致無法用索引。
  • 數(shù)據(jù)庫選錯(cuò)了索引。

九、補(bǔ)充概念

1、日志文件

（1）MySQL中常見的日志

• bin log（二進(jìn)制日志）：記錄增刪改的一些操作，不記錄查詢?nèi)罩?，配合relay log進(jìn)行主從復(fù)制。
• relayl log （中繼日志）：主從復(fù)制時(shí)使用的日志，讀取從主表中讀取到的bin log，然后執(zhí)行相應(yīng)語句
• error log （錯(cuò)誤日志）：記錄了MySQL服務(wù)啟動(dòng)和停止正確和錯(cuò)誤的信息，還記錄了mysqld實(shí)例運(yùn)行過程中發(fā)生的錯(cuò)誤事件信息。
• slow log （慢查詢?nèi)罩荆?/strong>查詢超出變量 long_query_time 指定時(shí)間值的為慢查詢。但是查詢獲取鎖(包括鎖等待)的時(shí)間不計(jì)入查詢時(shí)間內(nèi)。
• general log（一般查詢?nèi)罩荆?/strong>指定時(shí)間內(nèi)的查詢?nèi)罩?/li>
• redo log （重做日志）：屬于InnoDB執(zhí)行引擎，數(shù)據(jù)庫關(guān)閉重啟后，執(zhí)行redo log日志，重新啟動(dòng)；在增刪改操作的時(shí)候，與bin log聯(lián)合使用，執(zhí)行語句之后，先將redo log置為預(yù)提交狀態(tài)，讓后等待寫入bin log日志，寫完之后提交redo log日志
• undo log（回滾日志）：屬于InnoDB執(zhí)行引擎，執(zhí)行回滾操作時(shí)使用，多版本控制（MVCC）頁用到了undo log

日志

（2）bin log

binlog記錄了數(shù)據(jù)庫表結(jié)構(gòu)和表數(shù)據(jù)變更,比如update/delete/insert/truncate/create。它不會(huì)記錄select，因?yàn)檫@個(gè)沒有對表進(jìn)行變更。binlog我們可以理解位存儲著每條變更的SQL語句。

bin log主要的兩個(gè)作用：復(fù)制和恢復(fù)數(shù)據(jù)

(3) redo log

我們來看一條sql語句

update user_table set name='java3y' where id = '3';

MySQL執(zhí)行這條語句，肯定先把id=3這條語句查出來，然后將name字段給改掉。這沒問題吧？

實(shí)際上MySQL的基本存儲結(jié)構(gòu)是頁，所以MySQL是先把這條記錄所在的頁找到，然后把該頁加載到內(nèi)存中，將對應(yīng)記錄進(jìn)行修改。

現(xiàn)在就可能出現(xiàn)一個(gè)問題；如果在內(nèi)存中把數(shù)據(jù)改了還沒來得及寫入磁盤，而此時(shí)的數(shù)據(jù)庫掛了怎么辦，顯然這次修改就丟失了。

如果每個(gè)請求都需要將數(shù)據(jù)立馬寫入磁盤之后，那速度會(huì)很慢，MySQL可能也頂不住，所以MySQL引入了redo log, 內(nèi)存寫完了，然后會(huì)寫一份redo log, 這份redo log 記載著這次在某個(gè)頁做了什么修改。

其實(shí)寫redo log的時(shí)候，也會(huì)有buffer, 是先寫buffer，在真正寫入到磁盤中的，至于從buffer什么時(shí)候?qū)懭氪疟P，會(huì)有配置供我們配置。

寫redo log也是需要寫磁盤的，但它的好處就是順序IO，所以，redo log 的存在為了當(dāng)我們修改的時(shí)候，寫完內(nèi)存了，但數(shù)據(jù)還沒真正寫到磁盤的時(shí)候，此時(shí)數(shù)據(jù)庫掛了，我們可以對數(shù)據(jù)進(jìn)行恢復(fù)。因?yàn)閞edo log 是順序IO，所以寫入速度很快，并且redo log 記載的是物理變化，文件的體積小，恢復(fù)速度很快。

redo log的作用是為了持久化而生的。寫完內(nèi)存，如果數(shù)據(jù)庫掛了，那我們可以通過redo log來恢復(fù)內(nèi)存還沒來得及寫入磁盤的數(shù)據(jù)，將redo log加載到內(nèi)存里面，那內(nèi)存就能恢復(fù)到掛掉之前的數(shù)據(jù)了

(4) undo log

undo log主要有兩個(gè)作用：回滾和多版本控制(MVCC)

在數(shù)據(jù)修改的時(shí)候，不僅記錄了redo log，還記錄undo log，如果因?yàn)槟承┰驅(qū)е率聞?wù)失敗或回滾了，可以用undo log進(jìn)行回滾

undo log主要存儲的也是邏輯日志，比如我們要insert一條數(shù)據(jù)了，那undo log會(huì)記錄的一條對應(yīng)的delete日志。我們要update一條記錄時(shí)，它會(huì)記錄一條對應(yīng)相反的update記錄。

這也應(yīng)該容易理解，畢竟回滾嘛，跟需要修改的操作相反就好，這樣就能達(dá)到回滾的目的。因?yàn)橹С只貪L操作，所以我們就能保證：“一個(gè)事務(wù)包含多個(gè)操作，這些操作要么全部執(zhí)行，要么全都不執(zhí)行”。

---

參考博主：

本文在大佬博客的基礎(chǔ)之上進(jìn)行了一些整理和理解，非常感謝大佬的分享，附上兩位大佬的開源空間。

ThinkWon

JavaGuide