Java進階專題(二十六) 資料庫原理研究與優化

前言

在一個大資料量的系統中，這些資料的儲存、處理、搜尋是一個非常棘手的問題。

比如儲存問題：單臺伺服器的儲存能力及資料處理能力都是有限的, 因此需要增加伺服器, 搭建叢集來儲存海量資料。

讀寫效能問題：單臺資料庫伺服器的資料儲存和資料處理能力都是有限的， 而大多數網際網路業務，往往讀多寫少，而網際網路特別是中大型的電商系統，業務都是非常繁忙的， 這個時候最容易出現的就是讀效能瓶頸。

擴容問題：隨著時間的推移，原有的叢集中的機器不能夠儲存這麼多的資料量時，這個時候我們就需要考慮擴容。

資料庫架構設計

可用性設計

在最原始的架構中，是單一資料庫，一旦資料庫當機之後，整個服務都不可用，不存在高可用。解決高可用的思路，就是冗餘、複製。

主從複製：在這種主從的架構中，即使Master節點掛掉，還有Slave節點，整個資料庫的資料依賴存在，但是在
這種架構中，無法保證讀、寫的高可用，而且會存在一致性問題；

為保證“讀”的高可用，還可以對讀（從）庫進行冗餘，但是冗餘讀庫，也會存在副作用： 讀寫有延時，可能存在不一致。保證"寫"高可用，就可以在上述架構的基礎上,再冗餘寫庫，採用雙主雙從模式。而在這種架構下，兩個主庫，都會執行寫請求，而且互相同步。

讀效能設計

在資料庫中，我們為了提高讀的效能，最常用的做法就是建立索引，但是如果索引過多，又會存在其副作用：
降低了增刪改效能；索引佔記憶體多了，放在記憶體中的資料減少，資料命中率降低，IO次數增多；

解決方案：

不同庫建立不同的索引：主庫只提供寫操作， 不建立索引；從庫提供讀操作，在從庫上建立適當的索引 ；

增加從庫，負載均衡：這種做法上面已經提到，會存在主從不一致的問題，從庫數量越多，主從延時越長，不一致問題越嚴重。

增加快取層：①. 發生寫請求時，先淘汰快取，再寫資料庫②. 發生讀請求時，先讀快取，快取命中則直接返回，沒有命中，則查詢資料庫，並將查詢的結果快取在redis中（而此時舊資料可能入快取）。

一致性設計：

引入中介軟體：通過中介軟體將key寫操作路由到主， 在一定時間範圍內，該key上的讀也路由到主，當主從同步完成後再將讀操作路由到從。

讀寫都到主：讀寫都到主，不做讀寫分離，也就不存在主從不一致的情況。

快取兩次淘汰：異常的讀寫時序，或導致舊資料入快取，一次淘汰不夠，要進行二次淘汰

a. 發生寫請求時，先淘汰快取，再寫資料庫，額外增加一個timer，一定時間（主從同步完成的經驗時間）後再次淘汰
b. 發生讀請求時，先讀快取，hit則返回，miss則讀資料庫並將資料入快取（此時可能舊資料入快取，但會被二次淘汰淘汰掉，最終不會引發不一致）

擴充套件性設定

在上述的架構中，針對於單庫的可用性、讀效能、一致性進行了分析，在電商系統的資料庫中，資料量是特別大的，而單臺伺服器的容量、效能都是有限的，如果來完成擴容，則我們需要考慮到擴充性的設計。

垂直拆分：根據業務劃分，將不同的資料庫表切分到不同的資料庫上，以實現擴容的目的；

水平拆分：將同一塊業務的資料庫表，進行拆分，將一張表的資料根據一定的規則（取模，hash等）切分到不同的資料庫上。

平滑、高效擴容：隨著業務系統的擴張，資料庫中的資料量會不斷增加，如果實現擴容，最為直接了當的辦法就是直接增加伺服器，從而實現更多資料的儲存；

如何來完成高效、平滑的擴容呢， 可以按照以下架構進行

Mysql體系結構

索引的使用

索引概述

MySQL官方對索引的定義為：索引（index）是幫助MySQL高效獲取資料的資料結構（有序）。在資料之外，資料庫系統還維護者滿足特定查詢演算法的資料結構，這些資料結構以某種方式引用（指向）資料， 這樣就可以在這些資料結構上實現高階查詢演算法，這種資料結構就是索引。
優勢：
1） 類似於書籍的目錄索引，提高資料檢索的效率，降低資料庫的IO成本。
2） 通過索引列對資料進行排序，降低資料排序的成本，降低CPU的消耗。
劣勢：
1） 實際上索引也是一張表，該表中儲存了主鍵與索引欄位，並指向實體類的記錄，所以索引列也是要佔用空間的。
2） 雖然索引大大提高了查詢效率，同時卻也降低更新表的速度，如對錶進行INSERT、UPDATE、DELETE。因為更新表時，MySQL 不僅要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位，都會調整因為更新所帶來的鍵值變化後的索引資訊。

索引結構

MySQL資料庫中預設的儲存引擎InnoDB的索引結構為B+樹，而根據葉子節點的記憶體儲存不同，索引型別分為主鍵索引和非主鍵索引。

主鍵索引的葉子節點儲存的是整行資料，在InnoDB中主鍵索引頁被稱為聚簇索引。其結構如下：

而非主鍵索引的葉子節點內容儲存時的主鍵的值，在InnoDB中，非主鍵索引也被稱為二級索引或輔助索引。其結構如下：

索引使用規則

建立索引:

--為user表的name, age, sex三個欄位建立聯合索引,索引名為:idx_user_name_age_sex
CREATE INDEX idx_user_name_age_sex ON USER(NAME, age, sex);

1、全值匹配 ，對索引中所有列都指定具體值。

該情況下，索引生效，執行效率高。

2、最左字首法則

如果索引了多列，要遵守最左字首法則。指的是查詢從索引的最左前列開始，並且不跳過索引中的列。

3、範圍查詢右邊的列，不能使用索引 。

4、不要在索引列上進行運算操作， 索引將失效。

5、字串不加單引號，造成索引失效。

6、用or分割開的條件， 如果or前的條件中的列有索引，而後面的列中沒有索引，那麼涉及的索引都不會被用到。

7、以%開頭的Like模糊查詢，索引失效。

8、如果MySQL評估使用索引比全表更慢，則不使用索引。

9、is NULL ， is NOT NULL 有時索引失效。

10、in ， not in 有時索引失效。

**11、儘量使用覆蓋索引，避免select ***

儘量使用覆蓋索引（只訪問索引的查詢（索引列完全包含查詢列）），減少select * 。

12、如果查詢列，超出索引列，也會降低效能。

TIP :
using index ：使用覆蓋索引的時候就會出現
using where：在查詢使用索引的情況下，需要回表去查詢所需的資料
using index condition：查詢使用了索引，但是需要回表查詢資料
using index ; using where：查詢使用了索引，但是需要的資料都在索引列中能找到，所以不需要
回表查詢資料

索引設計原則

索引的設計可以遵循一些已有的原則，建立索引的時候請儘量考慮符合這些原則，便於提升索引的使用效率，更高效的使用索引。

• 對查詢頻次較高，且資料量比較大的表建立索引。

• 索引欄位的選擇，最佳候選列應當從where子句的條件中提取，如果where子句中的組合比較多，那麼應當挑選最常用、過濾效果最好的列的組合。

• 使用唯一索引，區分度越高，使用索引的效率越高。

• 索引可以有效的提升查詢資料的效率，但索引數量不是多多益善，索引越多，維護索引的代價自然也就水漲船高。對於插入、更新、刪除等DML操作比較頻繁的表來說，索引過多，會引入相當高的維護代價，降低DML操作的效率，增加相應操作的時間消耗。另外索引過多的話，MySQL也會犯選擇困難病，雖然最終仍然會找到一個可用的索引，但無疑提高了選擇的代價。

• 使用短索引，索引建立之後也是使用硬碟來儲存的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的欄位總長度比較短，那麼在給定大小的儲存塊內可以儲存更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率。

• 利用最左字首，N個列組合而成的組合索引，那麼相當於是建立了N個索引，如果查詢時where子句中使用了組成該索引的前幾個欄位，那麼這條查詢SQL可以利用組合索引來提升查詢效率。

儲存引擎

MySQL體系架構

整個MySQL Server由以下組成
Connection Pool : 連線池元件
Management Services & Utilities : 管理服務和工具元件
SQL Interface : SQL介面元件
Parser : 查詢分析器元件
Optimizer : 優化器元件
Caches & Buffers : 緩衝池元件
Pluggable Storage Engines : 儲存引擎
File System : 檔案系統

1） 連線層

最上層是一些客戶端和連結服務，包含本地socket 通訊和大多數基於客戶端/服務端工具實現的類似於
TCP/IP的通訊。主要完成一些類似於連線處理、授權認證、及相關的安全方案。在該層上引入了執行緒
池的概念，為通過認證安全接入的客戶端提供執行緒。同樣在該層上可以實現基於SSL的安全連結。服務
器也會為安全接入的每個客戶端驗證它所具有的操作許可權。
2） 服務層

第二層架構主要完成大多數的核心服務功能，如SQL介面，並完成快取的查詢，SQL的分析和優化，部
分內建函式的執行。所有跨儲存引擎的功能也在這一層實現，如 過程、函式等。在該層，伺服器會解
析查詢並建立相應的內部解析樹，並對其完成相應的優化如確定表的查詢的順序，是否利用索引等，
最後生成相應的執行操作。如果是select語句，伺服器還會查詢內部的快取，如果快取空間足夠大，
這樣在解決大量讀操作的環境中能夠很好的提升系統的效能。
3） 引擎層

儲存引擎層， 儲存引擎真正的負責了MySQL中資料的儲存和提取，伺服器通過API和儲存引擎進行通
信。不同的儲存引擎具有不同的功能，這樣我們可以根據自己的需要，來選取合適的儲存引擎。
4）儲存層

資料儲存層， 主要是將資料儲存在檔案系統之上，並完成與儲存引擎的互動。
和其他資料庫相比，MySQL有點與眾不同，它的架構可以在多種不同場景中應用併發揮良好作用。主要
體現在儲存引擎上，外掛式的儲存引擎架構，將查詢處理和其他的系統任務以及資料的儲存提取分離。
這種架構可以根據業務的需求和實際需要選擇合適的儲存引擎。

儲存引擎介紹

MySQL中支援的儲存引擎比較多,我們這裡重點講解兩種, InnoDB 與 MyISAM

特點	InnoDB	MyISAM
儲存限制	64TB	256TB
事務安全	支援	-
鎖機制	行鎖(適合高併發)	表鎖
B+樹索引	支援	支援
雜湊索引	-（具有自適應雜湊索引功能）	-
全文索引	支援(5.6版本之後)	支援
叢集索引	支援	-
資料索引	支援	-
索引快取	支援	支援
資料可壓縮	支援	支援
空間使用	高	低
記憶體使用	高	低
批量插入速度	低	高
支援外來鍵	支援	-

InnoDB儲存引擎深度剖析

InnoDB體系結構

緩衝池模組
1). 介紹
InnoDB儲存引擎基於磁碟檔案儲存，訪問物理硬碟和在記憶體中進行訪問，速度相差很大，為了儘可能彌補這兩者之間的I/O效率的差值，就需要把經常使用的資料載入到緩衝池中，避免每次訪問都進行磁碟I/O。
在InnoDB的緩衝池中不僅快取了索引頁和資料頁，還包含了undo頁、插入快取、自適應雜湊索引以及InnoDB的鎖資訊等等。

2). 讀取
在資料庫中進行讀取頁的操作時， 首先將磁碟中讀取到的頁資料存放在緩衝池中， 下一次再讀相同的頁時， 首先判斷緩衝池中是否存在，如果緩衝池被命中，則直接讀取資料， 如果沒有，則讀取磁碟中的頁資料。

3). 更新
而對於資料庫中頁的修改操作，則首先修改在緩衝池中的頁，然後再以一定的頻率重新整理到磁碟上，從而保證緩衝池中的資料與磁碟中的資料一致。頁從緩衝池重新整理回磁碟的操作並不是在每次頁發生更新時，都需要觸發，出於整體的效能考慮，而是通過checkpoint機制重新整理回磁碟。

4). 引數配置
在專用伺服器上，通常將多達80％的實體記憶體分配給緩衝池。引數設定：

在InnoDB引擎中，允許有多個緩衝池例項，根據頁的雜湊值分配到不同的緩衝池例項中，從而減少資料庫內部的資源競爭， 提升併發處理能力。 引數配置：

引數配置:
vi /etc/my.conf

innodb_buffer_pool_size=268435456

後臺執行緒模組

1). Master Thread

主要負責將緩衝池中的資料非同步重新整理到磁碟中, 保持資料的一致性， 還包括髒頁的重新整理、合併插入快取、undo頁的回收 。

2). IO Thread

在InnoDB儲存引擎中大量使用了AIO來處理IO請求, 這樣可以極大地提高資料庫的效能，而IO Thread主要負責這些IO請求的回撥。

Thread	執行緒數	引數配置
read thread	4	innodb_read_io_threads
write thread	4	innodb_write_io_threads
insert buffer thread	1	-
log thread	1	-

3). Purge Thread
主要用於回收事務已經提交了的undo log，在事務提交之後，undo log可能不用了，就用它來回收。

4). Pager Cleaner Thread

新引入的一個用於協助 Master Thread 重新整理髒頁到磁碟的執行緒，它可以減輕 Master Thread 的工作壓力，減少阻塞。

檔案模組

1). frm檔案
該檔案是用來儲存每個表的後設資料資訊的， 主要包含表結構定義 。

2). 系統表空間
系統表空間是InnoDB資料字典，二次寫緩衝區，更改緩衝區和撤消日誌的儲存區 。系統表空間可以具有一個或多個資料檔案， 預設情況下會在資料存放目錄中建立一個名為 ibdata1 表空間資料檔案。該檔名稱可以通過引數 innodb_data_file_path 指定。

3). 獨佔表空間
innodb中設定了引數 innodb_file_per_table 為 1/ON，則會將儲存的資料、索引等資訊單獨儲存在一個獨佔表空間，因此也會產生一個獨佔表空間檔案（ibd）

4). redo log
重做日誌， 用於恢復提交事務修改的頁操作 ， 用來保證事務的原子性和永續性。主要是解決 提交的事務沒有執行完成但是資料庫崩潰了，當資料庫恢復之後，可以完整的恢復資料。在執行操作時，InnoDB儲存引擎會首先將重做日誌資訊放到這個緩衝區 redo log buffer,然後按照不同的策略和頻率將buffer中的資料重新整理到重做日誌中。redo log在磁碟中儲存的名稱為 ib_logfile0，ib_logfile1。

5). bin log
二進位制日誌，其中記錄表結構中的資料變更，包含DDL與DML。

6). 其他
錯誤日誌、查詢日誌、慢查詢日誌等。

InnoDB邏輯儲存結構

1). 表空間
表空間是InnoDB儲存引擎邏輯結構的最高層， 大部分資料都存在於共享表空間ibdata1中。如果使用者啟用了引數 innodb_file_per_table ，則每張表都會有一個表空間（xxx.ibd）,裡面存放表中的資料、索引和插入快取Bitmap頁。其他的資料如undo log、插入快取索引頁、系統事務資訊、二次寫快取都是在共享表空間中。

2). 段
表空間是由各個段組成的， 常見的段有資料段、索引段、回滾段等。InnoDB儲存引擎是基於索引組織的，因此資料即是索引，索引即資料。資料段就是B+樹的葉子節點， 索引段即為B+樹的非葉子節點。InnoDB中對於段的管理，都是引擎自身完成，不需要人為對其控制。

3). 區
區是表空間的單元結構，每個區的大小為1M。 預設情況下， InnoDB儲存引擎頁大小為16K， 即一個區中一共有64個連續的頁。

4). 頁
頁是組成區的最小單元，頁也是InnoDB 儲存引擎磁碟管理的最小單元，每個頁的大小預設為 16KB。為了保證頁的連續性，InnoDB 儲存引擎每次從磁碟申請 4-5 個區。

5). 行
InnoDB 儲存引擎是面向行的（row-oriented)，也就是說資料是按行進行存放的，每個頁存放的行記錄也是有硬性定義的，最多允許存放 16KB/2-200 行，即 7992 行記錄。

checkpoint

1). 介紹
由於日常的DML語句操作時，首先操作的是緩衝池，並沒有直接寫入到磁碟，這有可能會導致記憶體中的資料與磁碟中的資料產生不一致的情況，而與磁碟中資料不一致的頁我們成為"髒頁"。 而checkpoint的工作，就是將記憶體中的髒頁，在一定條件下重新整理到磁碟。

如果在從緩衝池將頁資料重新整理到磁碟的過程中發生當機，那麼資料就無法恢復了；為了避免這種情況的發生，採用了Write Ahead Log(WAL)策略，即當事務提交時，先寫重做日誌(redo log)，再修改緩衝池資料頁，最後通過Checkpoint重新整理到磁碟（事務提交會觸發checkpoint）。這樣正在執行的事務，因為存在日誌都可以被恢復，沒有日誌的事務還沒有執行也不會丟失資料。

2). 作用
A. 縮短資料恢復時間
當資料庫發生當機時，資料庫不用重做所有的日誌，因為Checkpoint之前的頁都已經重新整理會磁碟了，故資料庫只需要重做Checkpoint之後的日誌就好，這樣就大大縮短了恢復時間。

B. 緩衝池不夠用時，需要先將髒頁資料重新整理到磁碟中；
當緩衝池不夠用時, 根據LRU演算法溢位最近最少使用的頁, 如果此頁是髒頁,則強制執行Checkpoint, 重新整理髒頁到磁碟。

C. 重做日誌不可用時，重新整理髒頁到磁碟；
redo log大小是固定的, 當前的InnoDB引擎中， 重做日誌的設計都是迴圈使用的，並不是無限增大的。重做日誌可以被重用的部分是已經不再需要的， 資料庫發生當機也不需要這部分的重做日誌，因此可以被覆蓋使用， 如果此時重做日誌還需要使用，那麼必須強制執行Checkpoint，將緩衝池中的頁至少重新整理磁碟, checkpoint移動到當前重做日誌的位置。

3). 分類
A. Sharp Checkpoint
Sharp Checkpoint 發生在資料庫關閉時，將所有的髒頁都重新整理回磁碟，這是預設的工作方式，引數：innodb_fast_shutdown=1。

B. Fuzzy Checkpoint
在InnoDB儲存引擎執行時，使用Fuzzy Checkpoint進行頁重新整理，只重新整理一部分髒頁。

InnoDB主要特性

插入快取

插入緩衝是InnoDB儲存引擎關鍵特性中最令人激動的。
主鍵是行唯一的識別符號，在應用程式中行記錄的插入順序一般是按照主鍵遞增的順序進行插入的。因此，插入聚集索引一般是順序的，不需要磁碟的隨機讀取。因此，在這樣的情況下，插入操作一般很快就能完成。

但是，不可能每張表上只有一個聚集索引，在更多的情況下，一張表上有多個非聚集的輔助索引（secondary index）。比如，我們還需要按照name這個欄位進行查詢，並且name這個欄位不是唯一的, 這樣的情況下產生了一個非聚集的並且不是唯一的索引。在進行插入操作時，資料頁的存放還是按主鍵id的執行順序存放，但是對於非聚集索引，葉子節點的插入不再是順序的了。這時就需要離散地訪問非聚集索引頁，插入效能在這裡變低了。然而這並不是這個name欄位上索引的錯誤，因為B+樹的特性決定了非聚集索引插入的離散性。

InnoDB儲存引擎開創性地設計了插入緩衝，對於非聚集索引的插入或更新操作，不是每一次直接插入索引頁中，而是先判斷插入的非聚集索引頁是否在緩衝池中。如果在，則直接插入；如果不在，則先放入一個插入緩衝區中，好似欺騙資料庫這個非聚集的索引已經插到葉子節點了，然後再以一定的頻率執行插入緩衝和非聚集索引葉子節點的合併操作，這時通常能將多個插入合併到一個操作中（因為在一個索引頁中），這就大大提高了對非聚集索引執行插入和修改操作的效能。

兩次寫

當資料庫寫物理頁時，如果當機了，那麼可能會導致物理頁的一致性被破壞。
可能有人會說，重做日誌不是可以恢復物理頁嗎？實際上是的，但是要求是在物理頁一致的情況下。
也就是說，如果物理頁完全是未寫之前的狀態，則可以用重做日誌恢復。如果物理頁已經完全寫完了，那麼也可以用重做日誌恢復。但是如果物理頁前面2K寫了新的資料，但是後面2K還是舊的資料，則種情況下就無法使用重做日誌恢復了。

這裡的兩次寫就是保證了物理頁的一致性，使得即使當機，也可以用重做日誌恢復。
在寫物理頁時，並不是直接寫到真正的物理頁上去，而是先寫到一個臨時頁上去，臨時頁寫完後，再寫物理頁。這樣一來：
A. 如果寫臨時頁時當機了，物理頁還是完全未寫之前的狀態，可以用重做日誌恢復
B. 如果寫物理頁時當機了，則可以使用臨時頁來恢復物理頁
InnoDB中共享表空間中劃了2M的空間，叫做double write，專門存放臨時頁。
InnoDB還從記憶體中劃出了2M的快取空間，叫做double write buffer，專門快取臨時頁。

每次寫物理頁時，先寫到double write buffer中，然後從double write buffer寫到double write上去。最後再從double write buffer寫到物理頁上去。

自適應雜湊索引

在InnoDB中預設支援的索引結構為 B+ 樹，B+ 樹索引可以使用到範圍查詢，同時是按照順序的方式對資料進行儲存，因此很容易對資料進行排序操作，在聯合索引中也可以利用部分索引鍵進行查詢 。
而對於Hash索引則只能滿足 =，<>,in查詢，不能使用範圍查詢， 而且資料的儲存是沒有順序的。MySQL 預設使用 B+ 樹作為索引，因為 B+ 樹有著 Hash 索引沒有的優點，那麼為什麼還需要自適應 Hash 索引呢？

這是因為B+樹的查詢次數，取決於B+樹的高度，在生產環境中，B+樹的高度一般為3-4層，故需要3-4次查詢。而 Hash 索引在進行資料檢索的時候效率非常高，通常只需要 O(1) 的複雜度，也就是一次就可以完成資料的檢索。雖然 Hash 索引的使用場景有很多限制，但是優點也很明顯。InnoDB儲存引擎會監控對錶上各索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引（Adaptive Hash Index，AHI）。
注意，這裡的自適應指的是不需要人工來指定，系統會根據情況自動完成。

什麼情況下才會使用自適應 Hash 索引呢？如果某個資料經常被訪問，當滿足一定條件的時候，就會將這個資料頁的地址存放到 Hash 表中。這樣下次查詢的時候，就可以直接找到這個頁面的所在位置。值得注意的是，hash索引只能用於= ，in的查詢，對於其他的查詢型別，如範圍匹配等是不能使用hash索引的。而且自適應 Hash 索引只儲存熱資料（經常被使用到的資料），並非全表資料。因此資料量並不會很大，因此自適應 Hash 也是存放到緩衝池中，這樣也進一步提升了查詢效率。

非同步IO

為了提高磁碟的操作效能，在InnoDB儲存引擎中使用非同步非阻塞AIO的方式來操作磁碟。
與AIO對應的是Sync IO，如果是同步IO操作，則每進行一次IO操作，需要等待此次操作結束後才可以進行接下來的操作。但是如果使用者發出的是一條索引掃描的查詢，那麼這條SQL查詢語句可能需要掃描多個索引頁，也就是需要進行多次的IO操作。每掃描一個頁並等待其完成之後，再進行下一次掃描，這是沒有必要的。
使用者可以在發出一個IO請求後立即再發出另一個IO請求，當全部的IO請求傳送完畢後，等待所有的IO操作完成，這就是AIO。

InnoDB事務

redo log

redo log叫做重做日誌，是用來實現事務的永續性。該日誌檔案由兩部分組成：重做日誌緩衝（redo log buffer）以及重做日誌檔案（redo log）,前者是在記憶體中，後者在磁碟中。當事務提交之後會把所有修改資訊都會存到該日誌中, 用於在重新整理髒頁到磁碟時,發生錯誤時, 進行資料恢復使用。

start transaction;
select balance from bank where name="Tom";
-- 生成 重做日誌 balance=8000
update bank set balance = balance - 2000;
-- 生成 重做日誌 account=2000
update finance set account = account + 2000;
commit;

mysql 為了提升效能不會把每次的修改都實時同步到磁碟，而是會先存到Buffer Pool(緩衝池)裡頭，把這個當作快取來用。然後使用後臺執行緒將快取池重新整理到磁碟。
當在執行重新整理時，當機或者斷電，可能會丟失部分資料。所以引入了redo log來記錄已成功提交事務的修改資訊，並且在事務提交時會把redo log持久化到磁碟，系統重啟之後在讀取redo log恢復最新資料。
簡單來說 ， redo log是用來恢復資料的 用於保障，已提交事務的持久化特性 ;

undo log

undo log 叫做回滾日誌，用於記錄資料被修改前的資訊。他正好跟前面所說的重做日誌所記錄的相反，重做日誌記錄資料被修改後的資訊。undo log主要記錄的是資料的邏輯變化，為了在發生錯誤時回滾之前的操作，需要將之前的操作都記錄下來，然後在發生錯誤時才可以回滾。

ndo log 記錄事務修改之前版本的資料資訊，因此假如由於系統錯誤或者rollback操作而回滾的話可以根據undo log的資訊來進行回滾到沒被修改前的狀態。

常見的SQL優化

資料準備:

CREATE TABLE `emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`age` INT(3) NOT NULL,
`salary` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8mb4;
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('1','Tom','25','2300');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('2','Jerry','30','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('3','Luci','25','2800');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('4','Jay','36','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('5','Tom2','21','2200');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('6','Jerry2','31','3300');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('7','Luci2','26','2700');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('8','Jay2','33','3500');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('9','Tom3','23','2400');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('10','Jerry3','32','3100');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('11','Luci3','26','2900');
INSERT INTO `emp` (`id`, `name`, `age`, `salary`) VALUES('12','Jay3','37','4500');
CREATE INDEX idx_emp_age_salary ON emp(age,salary);

Order By優化

第一種是通過對返回資料進行排序，也就是通常說的 filesort 排序，所有不是通過索引直接返回排序結果的排序都叫 FileSort 排序。

第二種通過有序索引順序掃描直接返回有序資料，這種情況即為 using index，不需要額外排序，操作效率高。

多欄位排序

瞭解了MySQL的排序方式，優化目標就清晰了：儘量減少額外的排序，通過索引直接返回有序資料。where 條件和Order by 使用相同的索引，並且Order By 的順序和索引順序相同， 並且Orderby 的欄位都是升序，或者都是降序。否則肯定需要額外的操作，這樣就會出現FileSort。

Filesort 的優化
通過建立合適的索引，能夠減少 Filesort 的出現，但是在某些情況下，條件限制不能讓Filesort消失，那就需要加快 Filesort的排序操作。對於Filesort ， MySQL 現在採用的是一次掃描演算法：一次性取出滿足條件的所有欄位，然後在排序區 sort buffer 中排序後直接輸出結果集。排序時記憶體開銷較大，但是排序效率比兩次掃描演算法要高。
MySQL 通過比較系統變數 max_length_for_sort_data 的大小和Query語句取出的欄位總大小， 來判定是否那種排序演算法，如果max_length_for_sort_data 更大，那麼使用第二種優化之後的演算法；否則使用第一種。
可以適當提高 sort_buffer_size 和 max_length_for_sort_data 系統變數，來增大排序區的大小，提高排序的效率。

Group by優化

由於GROUP BY 實際上也同樣會進行排序操作，而且與ORDER BY 相比，GROUP BY 主要只是多了排序之後的分組操作。當然，如果在分組的時候還使用了其他的一些聚合函式，那麼還需要一些聚合函式的計算。所以，在GROUP BY 的實現過程中，與 ORDER BY 一樣也可以利用到索引。

如果查詢包含 group by 但是使用者想要避免排序結果的消耗， 則可以執行order by null 禁止排序。如下 ：

優化後

從上面的例子可以看出，第一個SQL語句需要進行"filesort"，而第二個SQL由於order by null 不需要進行 "filesort"， 而上文提過Filesort往往非常耗費時間。

limit優化

一般分頁查詢時，通過建立覆蓋索引能夠比較好地提高效能。一個常見又非常頭疼的問題就是 limit 5000000,10 ，此時需要MySQL排序前5000010 記錄，僅僅返回5000000 - 5000010 的記錄，其他記錄丟棄，查詢排序的代價非常大 。
limit分頁操作, 越往後, 效能越低

優化方案：

--可以通過只查主鍵,再通過關聯主鍵查詢結果再查詢出具體行資料
select * from tb_sku t , (select id from tb_sku order by id limit 9000000,1) a where t.id = a.id;

count優化

在很多的業務系統中，都需要考慮進行分頁操作，但是當我們執行分頁操作時，都需要進行一次count操作，求取總記錄數，如果資料庫表的資料量大，在InnoDB引擎中，執行count操作的效能是比較低的，需要遍歷全表資料，對計數進行累加。
優化方案：
①. 在大資料量的查詢中，只查詢資料， 而不展示總記錄數 ；
②. 通過快取redis維護一個表的計數，來記錄資料庫表的總記錄數，在執行插入/刪除時，需要動態更新；但是這種帶where條件的就沒法子了。
③. 在資料庫表中定義一個大資料量的計數表，在執行插入/刪除時，需要動態更新。同上一樣無法帶where條件。