一. 存儲引擎

1.1 innodb

支持事務安全的引擎，支持外鍵、行鎖、事務是他的最大特點。如果有大量的update和insert，建議使用InnoDB，特別是針對多個並發和QPS較高的情況。

1.2 myisam

它是基於傳統的ISAM類型，ISAM是Indexed Sequential Access Method (有索引的順序訪問方法) 的縮寫，它是存儲記錄和文件的標準方法。不支持事務，而且不支持外鍵，如果執行大量的select MyISAM比較適合。

1.3 InnoDB 和 MyISAM 的比較

1、事務：InnoDB 是事務型的，可以使用 Commit 和 Rollback 語句。
2、並發：MyISAM 只支持表級鎖，而 InnoDB 還支持行級鎖。
3、外鍵：InnoDB 支持外鍵。
4、備份：InnoDB 支持在線熱備份。
5、崩潰恢復：MyISAM 崩潰後發生損壞的概率比 InnoDB 高很多，而且恢復的速度也更慢。
6、其它特性：MyISAM 支持壓縮表和空間數據索引。

1.4 其他存儲引擎

1、ARCHIVE：用於數據存檔的引擎，數據被插入後就不能在修改了，且不支持索引。
2、CSV ：在存儲數據時，會以逗號作為數據項之間的分隔符。
3、BLACKHOLE：會丟棄寫操作，該操作會返回空內容。
4、FEDERATED：將數據存儲在遠程資料庫中，用來訪問遠程表的存儲引擎。
5、MEMORY：置於內存的表
6、MERGE：用來管理由多個 MyISAM 表構成的表集合

二. 索引

2.1 B+ Tree 原理

1、B Tree 指的是 Balance Tree，也就是平衡樹，平衡樹是一顆查找樹，並且所有葉子節點位於同一層。
2、B+ Tree 是 B 樹的一種變形，它是基於 B Tree 和葉子節點順序訪問指針進行實現，通常用於資料庫和作業系統的文件系統中。
3、B+ 樹有兩種類型的節點：內部節點（也稱索引節點）和葉子節點，內部節點就是非葉子節點，內部節點不存儲數據，只存儲索引，數據都存在葉子節點。
4、內部節點中的 key 都按照從小到大的順序排列，葉子節點的記錄也是按照從小到大排列的。
5、每個葉子節點都存有相鄰葉子節點的指針。

2.2 B-Tree

1、所有鍵值分布在整個樹中（區別與B+樹，B+樹的值只分部在葉子節點上）
2、任何關鍵字出現且只出現在一個節點中（區別與B+樹）
3、搜索有可能在非葉子節點結束（區別與B+樹，因為值都在葉子節點上，只有搜到葉子節點才能拿到值）

2.3 B + 樹與 B 樹的對比

1、B+ 樹的磁碟 IO 更低B+ 樹的內部節點並沒有指向關鍵字具體信息的指針。因此其內部節點相對 B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
2、B+ 樹的查詢效率更加穩定由於非葉子結點並不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。
3、B 樹元素遍歷的效率低下。正是為了解決這個問題，B+樹應運而生。B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的，而 B 樹不支持這樣的操作（或者說效率太低）。

2.4 哈希索引

2.4.1 特點

Hash索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以Hash索引的查詢效率要遠高於B-Tree索引。

2.4.2 弊端

1、Hash索引只能精確匹配，不能使用範圍查詢。
2、Hash索引無法被用來數據的排序操作。
3、Hash索引不能利用部分索引鍵查詢。
4、Hash索引在任何時候都不能避免表掃描。
5、Hash索引遇到大量Hash值相等的情況後性能並不一定就會比BTree索引高。

2.5 聚簇索引和非聚簇索引

2.5.1 介紹

聚簇索引：並不是一種單獨的索引類型，而是一種數據存儲方式。將數據存儲與索引放到了一塊，找到索引也就找到了數據

非聚簇索引：將數據存儲於索引分開結構，索引結構的葉子節點指向了數據的對應行，myisam通過key_buffer把索引先緩存到內存中，當需要訪問數據時（通過索引訪問數據），在內存中直接搜索索引，然後通過索引找到磁碟相應數據。

2.5.2 好處與限制

聚簇索引的好處：按照聚簇索引排列順序，查詢顯示一定範圍數據的時候，由於數據都是緊密相連，資料庫不不用從多個數據塊中提取數據，所以節省了大量的io操作。

2.6 索引優化

2.6.1 索引的優缺點及使用條件

優點：
1、提高數據檢索的效率，降低資料庫的IO成本。
2、通過索引列對數據進行排序，降低數據排序的成本，降低了CPU的消耗。

缺點：
1、雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件每次更新添加了索引列的欄位。實際上索引也是一張表，該表保存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要占用空間。

使用條件：
哪些情況需要創建索引？
1、主鍵自動建立唯一索引
2、頻繁作為查詢條件的欄位應該創建索引
3、查詢中與其它表關聯的欄位，外鍵關係建立索引
4、查詢中排序的欄位，排序欄位若通過索引去訪問將大大提高排序速度
5、查詢中統計或者分組欄位

哪些情況不需要創建索引？
1、表記錄太少。
2、經常增刪改的表或者欄位。Why:提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE。因為更新表時，Mysql不僅要保存數據，還要保存一下索引文件。
3、Where條件里用不到的欄位不創建索引。

問題：為什麼對於非常小的表，大部分情況下簡單的全表掃描比建立索引更高效？如果一個表比較小，那麼顯然直接遍歷表比走索引要快（因為需要回表）。

2.7 前綴索引

當索引是很長的字符序列時，這個索引將會很占內存，而且會很慢，這時候就會用到前綴索引了。所謂的前綴索引就是去索引的前面幾個字母作為索引，但是要降低索引的重複率，索引我們還必須要判斷前綴索引的重複率

2.8 覆蓋索引

索引包含所有需要查詢的欄位的值。
具有以下優點：
1、索引通常遠小於數據行的大小，只讀取索引能大大減少數據訪問量。
2、一些存儲引擎（例如 MyISAM）在內存中只緩存索引，而數據依賴於作業系統來緩存。因此，只訪問索引可以不使用系統調用（通常比較費時）。
3、對於 InnoDB 引擎，若覆蓋索引能夠覆蓋查詢，則無需訪問主索引。

2.9 查詢性能分析 - EXPLAIN

用法：explain+SQL語句

EXPLAIN欄位解析：

三. 優化的一些方向

優化數據訪問
減少請求的數據量：
1、只返回必要的列：最好不要使用 SELECT * 語句。
2、只返回必要的行：使用 LIMIT 語句來限制返回的數據。
3、緩存重複查詢的數據：使用緩存可以避免在資料庫中進行查詢，特別在要查詢的數據經常被重複查詢時，緩存帶來的查詢性能提升將會是非常明顯的。減少伺服器端掃描的行數：1、最有效的方式是使用索引來覆蓋查詢。

重構查詢方式
1、切分大查詢：一個大查詢如果一次性執行的話，可能一次鎖住很多數據、占滿整個事務日誌、耗盡系統資源、阻塞很多小的但重要的查詢。
2、分解大連接查詢：將一個大連接查詢分解成對每一個表進行一次單表查詢，然後在應用程式中進行關聯，這樣做的好處有：① 讓緩存更高效。對於連接查詢，如果其中一個表發生變化，那麼整個查詢緩存就無法使用。而分解後的多個查詢，即使其中一個表發生變化，對其它表的查詢緩存依然可以使用。② 分解成多個單表查詢，這些單表查詢的緩存結果更可能被其它查詢使用到，從而減少冗餘記錄的查詢。③ 減少鎖競爭；④ 在應用層進行連接，可以更容易對資料庫進行拆分，從而更容易做到高性能和可伸縮。

索引失效的一些情況

1、查詢條件中有or
2、like查詢是以'%'開頭
3、對查詢的列上有運算或者函數的
4、如果列類型是字符串，那一定要在條件中將數據使用引號引用起來,否則不使用索引
5、左連接查詢或者右連接查詢查詢關聯的欄位編碼格式不一樣
6、如果mysql估計使用全表掃描要比使用索引快,則不使用索引
7、如果查詢中沒有用到聯合索引的第一個欄位，則不會走索引

四. 事務

4.1 描述

事務是指滿足 ACID 特性的一組操作，可以通過 Commit 提交一個事務，也可以使用 Rollback 進行回滾。

4.2 特性

ACID 四個特性了，四個特性分別是：Atomicity：原子性；Consistency：一致性；Isolation：隔離性；Durability：持久性。
原子性：事務被視為不可分割的最小單元，事務的所有操作要麼全部成功，要麼全部失敗回滾。
一致性：資料庫在事務執行前後都保持一致性狀態，在一致性狀態下，所有事務對一個數據的讀取結果都是相同的。
隔離性：一個事務所做的修改在最終提交以前，對其他事務是不可見的。
持久性：一旦事務提交，則其所做的修改將會永遠保存到資料庫中。即使系統發生崩潰，事務執行的結果也不能丟。

4.3 ACID 之間的關係

事務的 ACID 特性概念很簡單，但不好理解，主要是因為這幾個特性不是一種平級關係：·只有滿足一致性，事務的結果才是正確的。·在無並發的情況下，事務串行執行，隔離性一定能夠滿足。此時只要能滿足原子性，就一定能滿足一致性。·在並發的情況下，多個事務並行執行，事務不僅要滿足原子性，還需要滿足隔離性，才能滿足一致性。·事務滿足持久化是為了能應對資料庫崩潰的情況。

4.4 隔離級別

1、讀未提交讀（READ UNCOMMITTED）事務中的修改，即使沒有提交，對其他事務也是可見的。
2、讀已提交（READ COMMITTED）一個事務只能讀取已經提交的事務所做的修改。換句話說，一個事務所做的修改在提交之前對其他事務是不可見的。
3、可重複讀（REPEATABLE READ）保證在同一個事務中多次讀取同樣數據的結果是一樣的。
4、可串行化（SERIALIZABLE）強制事務串行執行。需要加鎖實現，而其它隔離級別通常不需要。
默認的隔離級別READ-COMMITTED，可通過SELECT @@tx_isolation; 查詢

五. mysql鎖機制

5.1 介紹

鎖是資料庫系統區別於文件系統的一個關鍵特性。鎖機制用於管理對共享資源的並發訪問。

5.2 分類

從對數據操作的類型（讀\寫）

讀鎖(共享鎖)：針對同一份數據，多個讀操作可以同時進行而不會互相影響。
寫鎖（排它鎖）：當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。

從對數據操作的粒度分:表鎖、行鎖

表鎖(偏讀)
偏向MyISAM存儲引擎，開銷小，加鎖快；無死鎖；鎖定粒度大，發生鎖衝突的概率最高,並發度最低。
1、對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。
2、對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。簡而言之，就是讀鎖會阻塞寫，但是不會堵塞讀。而寫鎖則會把讀和寫都堵塞

行鎖(偏寫)
偏向InnoDB存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,並發度也最高。InnoDB與MyISAM的最大不同有兩點：一是支持事務（TRANSACTION）；二是採用了行級鎖

InnoDB行鎖實現方式：InnoDB行鎖是通過給索引上的索引項加鎖 來實現的，這一點MySQL與Oracle不同，後者是通過在數據塊中對相應數據行加鎖來實現的。
InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖在不通過索引條件查詢的時候，InnoDB確實使用的是表鎖，而不是行鎖。
而且即便在條件中使用了索引欄位，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。

InnoDB間隙鎖（Next-Key鎖）
當我們用範圍條件而不是相等條件檢索數據，並請求共享或排他鎖時，InnoDB會給符合條件的已有數據記錄的索引項加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做「間隙（GAP)」，InnoDB也會對這個「間隙」加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）。InnoDB使用間隙鎖的目的：防止幻讀，以滿足相關隔離級別的要求.

5.3 MVCC（多版本並發控制）

介紹

MVCC全稱是： Multiversion concurrency control，多版本並發控制，提供並發訪問資料庫時，對事務內讀取的到的內存做處理，用來避免寫操作堵塞讀操作的並發問題。

舉例

程式設計師A正在讀資料庫中某些內容，而程式設計師B正在給這些內容做修改（假設是在一個事務內修改，大概持續10s左右），A在這10s內 則可能看到一個不一致的數據，在B沒有提交前，如何讓A能夠一直讀到的數據都是一致的呢？

有幾種處理方法，第一種： 基於鎖的並發控制，程式設計師B開始修改數據時，給這些數據加上鎖，程式設計師A這時再讀，就發現讀取不了，處於等待情況，只能等B操作完才能讀數據，這保證A不會讀到一個不一致的數據，但是這個會影響程序的運行效率。還有一種就是：MVCC，每個用戶連接資料庫時，看到的都是某一特定時刻的資料庫快照，在B的事務沒有提交之前，A始終讀到的是某一特定時刻的資料庫快照，不會讀到B事務中的數據修改情況，直到B事務提交，才會讀取B的修改內容。

思路

一個支持MVCC的資料庫，在更新某些數據時，並非使用新數據覆蓋舊數據，而是標記舊數據是過時的，同時在其他地方新增一個數據版本。因此，同一份數據有多個版本存儲，但只有一個是最新的。

MVCC提供了 時間一致性的 處理思路，在MVCC下讀事務時，通常使用一個時間戳或者事務ID來確定訪問哪個狀態的資料庫及哪些版本的數據。讀事務跟寫事務彼此是隔離開來的，彼此之間不會影響。假設同一份數據，既有讀事務訪問，又有寫事務操作，實際上，寫事務會新建一個新的數據版本，而讀事務訪問的是舊的數據版本，直到寫事務提交，讀事務才會訪問到這個新的數據版本。

MVCC有兩種實現方式

第一種實現方式是將數據記錄的多個版本保存在資料庫中，當這些不同版本數據不再需要時，垃圾收集器回收這些記錄。

第二種實現方式只在資料庫保存最新版本的數據，但是會在使用undo時動態重構舊版本數據，這種方式被Oracle和MySQL/InnoDB使用。
Undo 日誌MVCC 使用到的快照存儲在 Undo 日誌中，該日誌通過回滾指針把一個數據行（Record）的所有快照連接起來。

實現機制

簡述
MVCC可以認為是行級鎖的一個變種，它可以在很多情況下避免加鎖操作，因此開銷更低。MVCC的實現大都都實現了非阻塞的讀操作，寫操作也只鎖定必要的行。 InnoDB的MVCC實現，是通過保存數據在某個時間點的快照來實現的。一個事務，不管其執行多長時間，其內部看到的數據是一致的。也就是事務在執行的過程中不會相互影響。

機制
InnoDB的MVCC，通過在每行記錄後面保存兩個隱藏的列來實現：一個保存了行的創建時間，一個保存行的過期時間（刪除時間），當然，這裡的時間並不是時間戳，而是系統版本號，每開始一個新的事務，系統版本號就會遞增。在RR隔離級別下，MVCC的操作如下：

1、select操作。 ·InnoDB只查找版本早於（包含等於）當前事務版本的數據行。可以確保事務讀取的行，要麼是事務開始前就已存在，或者事務自身插入或修改的記錄。 ·行的刪除版本要麼未定義，要麼大於當前事務版本號。可以確保事務讀取的行，在事務開始之前未刪除。
2、insert操作。將新插入的行保存當前版本號為行版本號。
3、delete操作。將刪除的行保存當前版本號為刪除標識。
4、update操作。變為insert和delete操作的組合，insert的行保存當前版本號為行版本號，delete則保存當前版本號到原來的行作為刪除標識。 由於舊數據並不真正的刪除，所以必須對這些數據進行清理，innodb會開啟一個後台線程執行清理工作，具體的規則是將刪除版本號小於當前系統版本的行刪除，這個過程叫做purge。

六. 複製

主從複製

主要涉及三個線程：binlog 線程、I/O 線程和 SQL 線程。binlog 線程 ：負責將主伺服器上的數據更改寫入二進位日誌（Binary log）中。I/O 線程 ：負責從主伺服器上讀取- 二進位日誌，並寫入從伺服器的中繼日誌（Relay log）。SQL 線程 ：負責讀取中繼日誌，解析出主伺服器已經執行的數據更改並在從伺服器中重放（Replay）。

讀寫分離

主伺服器處理寫操作以及實時性要求比較高的讀操作，而從伺服器處理讀操作。讀寫分離能提高性能的原因在於：
1、主從伺服器負責各自的讀和寫，極大程度緩解了鎖的爭用；
2、從伺服器可以使用 MyISAM，提升查詢性能以及節約系統開銷；
3、增加冗餘，提高可用性。 讀寫分離常用代理方式來實現，代理伺服器接收應用層傳來的讀寫請求，然後決定轉發到哪個伺服器。

七. 面試題

7.1 什麼是覆蓋索引

只需要在一棵索引樹上就能獲取SQL所需的所有列數據，無需回表，速度更快。

7.2 主鍵索引和普通索引有什麼不一樣？

主鍵索引也被稱為聚簇索引,葉子節點存放的是整行數據; 而非主鍵索引被稱為二級索引,葉子節點存放的是主鍵的值.如果根據主鍵查詢, 只需要搜索ID這顆B+樹而如果通過非主鍵索引查詢, 需要先搜索k索引樹, 找到對應的主鍵, 然後再到ID索引樹搜索一次, 這個過程叫做回表。

總結：非主鍵索引的查詢需要多掃描一顆索引樹, 效率相對更低。

7.3 什麼情況下mysql會回表查詢？

MySQL innodb的主鍵索引是簇集索引，也就是索引的葉子節點存的是整個單條記錄的所有欄位值，不是主鍵索引的就是非簇集索引，非簇集索引的葉子節點存的是主鍵欄位的值。

回表是什麼意思？就是你執行一條sql語句，需要從兩個b+索引中去取數據。舉個例子：表tbl有a,b,c三個欄位，其中a是主鍵，b上建了索引，然後編寫sql語句SELECT b FROM tbl WHERE a=1 這樣不會產生回表，因為所有的數據在a的索引樹中均能找到。SELECT * FROM tbl WHERE b=1 這樣就會產生回表，因為where條件是b欄位，那麼會去b的索引樹里查找數據，但b的索引裡面只有a,b兩個欄位的值，沒有c，那麼這個查詢為了取到c欄位，就要取出主鍵a的值，然後去a的索引樹去找c欄位的數據。查了兩個索引樹，這就叫回表。
索引覆蓋就是查這個索引能查到你所需要的所有數據，不需要去另外的數據結構去查。其實就是不用回表。怎麼避免？不是必須的欄位就不要出現在SELECT裡面。或者b,c建聯合索引。但具體情況要具體分析，索引欄位多了，存儲和插入數據時的消耗會更大。這是個平衡問題。

7.4 大表怎麼DDL變更

Online DDL

MySQL 5.6到5.7
對於MySQL 5.6到5.7的版本，可以使用OnLine DDL的方式變更，對於大表來說，執行時間會很長，好處是在Master上DML操作不受影響，但是會導致主從延時。

假如Master上添加欄位執行了20分鐘，相應的Slave也要執行20分鐘，在這期間Slave一直處於延遲狀態，會造成業務數據不一致，比如用戶在Master下單成功，由於Slave延遲查詢不到訂單信息，用戶誤以為網絡原因沒有下單成功，又下了一單，導致重複下單的情況。這種方式會導致主從延遲，根據業務情況，只能選擇在業務低峰期執行了。
MySQL 8.0變更方式

用過Oracle的都知道，DDL變更都是修改元數據，上億的表在Oracle中DDL變更都是瞬間完成。MySQL 8.0也推出了INSTANT方式，真正的只修改MetaData，不影響表數據，所以它的執行效率跟表大小几乎沒有關係。建議新系統上線用MySQL的話儘量使用MySQL 8.0，老的資料庫也可以升級到MySQL 8.0獲取更好的性能。

7.5 模糊匹配like %%怎麼優化

1、嘗試利用覆蓋索引特性，先查出ID，再反查。
2、使用複合索引，利用前面的欄位先過濾一些數據。
3、創建全文索引。MySQL 5.6 及以後的版本，MyISAM 和 InnoDB 存儲引擎均支持全文索引

7.6 資料庫連接池到底應該設多大？

計算公式下面的公式是由PostgreSQL提供的，不過我們認為可以廣泛地應用於大多數資料庫產品。你應該模擬預期的訪問量，並從這一公式開始測試你的應用，尋找最合適的連接數值。

連接數 = ((核心數 2) + 有效磁碟數)核心數不應包含超線程(hyper thread)，即使打開了hyperthreading也是。如果活躍數據全部被緩存了，那麼有效磁碟數是0，隨著緩存命中率的下降，有效磁碟數逐漸趨近於實際的磁碟數。這一公式作用於SSD時的效果如何尚未有分析。按這個公式，你的4核i7資料庫伺服器的連接池大小應該為((4 2) + 1) = 9。取個整就算是是10吧。這個配置就能輕鬆搞定3000用戶以6000TPS的速率並發執行簡單查詢的場景。如果連接池大小超過10，會看到響應時長開始增加，TPS開始下降。

why ? 一顆CPU核心同一時刻只能執行一個線程，然後作業系統切換上下文，核心開始執行另一個線程的代碼，以此類推。給定一顆CPU核心，其順序執行A和B永遠比通過時間分片「同時」執行A和B要快，這是一條計算機科學的基本法則。一旦線程的數量超過了CPU核心的數量，再增加線程數系統就只會更慢，而不是更快。

7.7 撞庫、脫庫和洗庫

撞庫」撞庫」是黑客通過收集網際網路已泄露的用戶和密碼信息，生成對應的字典表，嘗試批量登陸其他網站後，得到一系列可以登錄的用戶。很多用戶在不同網站使用的是相同的帳號密碼，因此黑客可以通過獲取用戶在A網站的帳戶從而嘗試登錄B網址，這就可以理解為撞庫攻擊。

說得簡單一點，就是一個小偷，入室盜竊後偷到了一串鑰匙，然後他拿著這串鑰匙，在整個小區裡面挨家挨戶地進行開鎖。這個過程就是撞庫。拖庫拖庫本來是資料庫領域的術語，指從資料庫中導出數據。到了黑客攻擊泛濫的今天，它被用來指網站遭到入侵後，黑客竊取其資料庫。黑客通過技術手段竊取資料庫的過程叫做拖庫。就像小偷偷東西是一樣的。「拖庫」的通常步驟為：

1、黑客對目標網站進行掃描，查找其存在的漏洞，常見漏洞包括SQL注入、文件上傳漏洞等。（小偷蹲點）

2、通過該漏洞在網站伺服器上建立「後門(webshell)」，通過該後門獲取伺服器作業系統的權限。（小偷想辦法進入室內）

3、利用系統權限直接下載備份資料庫，或查找資料庫連結，將其導出到本地。（小偷盜走值錢的東西）洗庫「洗庫」，屬於黑客入侵的一種，就是黑客入侵網站，通過技術手段將有價值的用戶數據歸納分析，售賣變現。說的簡單一點，就是一個小偷，入室盜竊後偷到了很多東西，他對這些贓物分類，然後進行銷贓的過程。

7.8 為什麼MySQL不建議使用delete刪除數據

經過測試，發現執行完delete語句後，再執行sql語句效率變慢，可能是因為delete語句會產生碎片數據存儲在文件系統上的，總是不能100%利用分配給它的物理空間，刪除數據會在頁面上留下一些」空洞」。或者隨機寫入（聚集索引非線性增加）會導致頁分裂，頁分裂導致頁面的利用空間少於50%，另外對表進行增刪改會引起對應的二級索引值的隨機的增刪改，也會導致索引結構中的數據頁面上留下一些"空洞"，雖然這些空洞有可能會被重複利用，但終究會導致部分物理空間未被解決方案1、數據歸檔 2、改為邏輯刪除

7.9 SQL調優的經驗之談

SQL規範性檢查

select檢查
1、UDF用戶自定義函數SQL語句的select後面使用了自定義函數UDF，SQL返回多少行，那麼UDF函數就會被調用多少次，這是非常影響性能的。
2、text類型檢查如果select出現text類型的欄位，就會消耗大量的網絡和IO帶寬，由於返回的內容過大超過max_allowed_packet設置會導致程序報錯，需要評估謹慎使用。
3、內聯子查詢在select後面有子查詢的情況稱為內聯子查詢，SQL返回多少行，子查詢就需要執行過多少次，嚴重影響SQL性能。

from檢查
1、表的連結方式在MySQL中不建議使用Left Join，即使ON過濾條件列索引，一些情況也不會走索引，導致大量的數據行被掃描，SQL性能變得很差，同時要清楚ON和Where的區別。
2、子查詢由於MySQL的基於成本的優化器CBO對子查詢的處理能力比較弱，不建議使用子查詢，可以改寫成Inner Join。

where檢查
1、索引列被運算當一個欄位被索引，同時出現where條件後面，是不能進行任何運算，會導致索引失效。
2、類型轉換對於Int類型的欄位，傳varchar類型的值是可以走索引，MySQL內部自動做了隱式類型轉換；相反對於varchar類型欄位傳入Int值是無法走索引的，應該做到對應的欄位類型傳對應的值總是對的。
3、列字符集從MySQL 5.6開始建議所有對象字符集應該使用用utf8mb4，包括MySQL實例字符集，資料庫字符集，表字符集，列字符集。避免在關聯查詢Join時欄位字符集不匹配導致索引失效，同時目前只有utf8mb4支持emoji表情存儲。

group by檢查
1、前綴索引group by後面的列有索引，索引可以消除排序帶來的CPU開銷，如果是前綴索引，是不能消除排序的。
2、函數運算

order by檢查
1、前綴索引order by後面的列有索引，索引可以消除排序帶來的CPU開銷，如果是前綴索引，是不能消除排序的。
2、欄位順序排序欄位順序，asc/desc升降要跟索引保持一致，充分利用索引的有序性來消除排序帶來的CPU開銷。

limit檢查
對於limit m, n分頁查詢，越往後面翻頁即m越大的情況下SQL的耗時會越來越長，對於這種應該先取出主鍵id，然後通過主鍵id跟原表進行關聯查詢。
例如使用select * 的情況下直接用limit 600000，10 掃描的是約60萬條數據，並且是需要回表60W次，也就是說大部分性能都耗在隨機訪問上，到頭來只用到10條數據，如果先查出來ID，再關聯去查詢記錄，就會快很多，因為索引查找符合條件的ID很快，然後再回表10次。就可以拿到我們想要的數據。

表結構檢查
1、NOT NULL屬性根據業務含義，儘量將欄位都添加上NOT NULL DEFAULT VALUE屬性，如果列值存儲了大量的NULL，會影響索引的穩定性。
2、DEFAULT屬性在創建表的時候，建議每個欄位儘量都有默認值，禁止DEFAULT NULL，而是對欄位類型填充響應的默認值。
3、TEXT類型不建議使用Text數據類型，一方面由於傳輸大量的數據包可能會超過max_allowed_packet設置導致程序報錯，另一方面表上的DML操作都會變的很慢，建議採用es或者對象存儲OSS來存儲和檢索。

7.10 資料庫自增ID用完了會怎麼樣？

自增ID達到上限用完了之後，分為兩種情況：1、如果設置了主鍵，那麼將會報錯主鍵衝突。2、如果沒有設置主鍵，資料庫則會幫我們自動生成一個全局的row_id，新數據會覆蓋老數據解決方案：表儘可能都要設置主鍵，主鍵儘量使用bigint類型，21億的上限還是有可能達到的，但是覆蓋數據顯然是不可接受的。

7.11 undo log、binlog、redo log、undo loghttps://www.yuque.com/haolonglong/msvmro/tbfzo8

7.12 MySQL 普通索引和唯一索引的區別

查詢和更新上的區別

這兩類索引在查詢能力上是沒差別的，主要考慮的是對更新性能的影響。建議儘量選擇普通索引。

MySQL 的查詢操作

• 普通索引
  查找到第一個滿足條件的記錄後，繼續向後遍歷，直到第一個不滿足條件的記錄。
• 唯一索引
  由於索引定義了唯一性，查找到第一個滿足條件的記錄後，直接停止繼續檢索。

普通索引會多次檢索，不過因為 InnoDB 的數據是按照數據頁為單位進行讀寫的，需要讀取數據時，並不是直接從磁碟讀取記錄，而是先把數據頁讀到內存，再去內存中檢索。所以性能的影響就是微乎其微。

除非在數據頁的最後一條記錄還是符合要求的數據，就需要再讀一個數據頁，這種情況很少，對CPU的消耗基本可以忽略了。

因此說，在查詢數據方面，普通索引和唯一索引沒差別。

MySQL 的更新操作

當需要更新一個數據時，如果數據在內存中就直接更新，而如果這個數據還沒有在內存中的話，在不影響數據一致性的 前提下，InooDB會將這些更新操作緩存在change buffer中，這樣就不需要從磁碟中讀入這個數據了。在下次查詢需要訪問這個數據的時候，將數據讀入內存，然後執行change buffer中與這個有關的操作。通過這種方式就能保證這個數據邏 輯的正確性。

將change buffer中的操作應用到原數據，得到最新結果的過程稱為merge。除了訪問這個數據會觸發merge外，系統有後 台線程會定期merge。在資料庫正常關閉(shutdown)的過程中，也會執行merge操作。

如果能夠將更新操作先記錄在change buffer，減少讀磁碟，語句的執行速度會得到明顯的提升。而且，數據讀入內存 是需要占用buffer pool的，所以這種方式還能夠避免占用內存，提高內存利用率。

什麼情況下會使用change buffer？

對於唯一索引來說，所有的更新操作都要先判斷這個操作是否違反唯一性約束。比如，要插入(4,400)這個記錄，就要先判斷 現在表中是否已經存在k=4的記錄，而這必須要將數據讀入內存才能判斷。如果都已經讀入到內存了，那直接更新內存會更快，就沒必要使用change buffer了。

唯一索引的更新就不能使用change buffer，實際上也只有普通索引可以使用。

change buffer用的是buffer pool里的內存，不能無限增大。change buffer的大小，可以通過參數innodb_change_buffer_max_size來動態設置。這個參數設置為50的時候，表示change buffer的大小最多只能占用buffer pool 的50%。
結論：唯一索引用不了change buffer，只有普通索引可以用。

change buffer 的適用場景

change buffer 的作用是降低更新操作的頻率，緩存更新操作。這樣會有一個缺點，就是更新不及時，對於讀操作比較頻繁的表，不建議使用 change buffer。

因為更新操作剛記錄進change buffer中，就讀取了該表，數據頁被讀到了內存中，數據馬上就merge到數據頁中了。這樣不僅不會降低性能消耗，反而會增加維護change buffer的成本。