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

• 2021 年 2 月 22 日
• 筆記
• 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存儲引擎深度剖析

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請求的回調。

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條件。