MySQL學習筆記-有解無憂

作者： Grey

原文地址：MySQL學習筆記

說明

注：本文中的SQL陳述句如果用到了特定方言，都是基于MySQL資料庫，

一條SQL的執行流程

關于DDL

DDL 的英文全稱是 Data Definition Language，中文是資料定義語言，它定義了資料庫的結構和資料表的結構，在 DDL 中，我們常用的功能是增刪改，分別對應的命令是 CREATE、DROP 和 ALTER，

需要注意的是：在執行 DDL 的時候，不需要 COMMIT，就可以完成執行任務，

排序規則是utf8_general_ci，代表對大小寫不敏感，如果設定為utf8_bin，代表對大小寫敏感，

DISTINCT

DISTINCT 其實是對后面所有列名的組合進行去重

SELECT DISTINCT attack_range, name FROM heros

其實是對(attack_range,name)這個組合去重，

LIMIT

另外在查詢程序中，我們可以約束回傳結果的數量，使用 LIMIT 關鍵字，

SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

在不同的 DBMS 中使用的關鍵字可能不同，在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 關鍵字，而且需要放到 SELECT 陳述句的最后面，如果是 SQL Server 和 Access，需要使用 TOP 關鍵字，比如：

SELECT TOP 5 name, hp_max FROM heros ORDER BY hp_max DESC

如果是 DB2，使用FETCH FIRST 5 ROWS ONLY這樣的關鍵字：

SELECT name, hp_max FROM heros ORDER BY hp_max DESC FETCH FIRST 5 ROWS ONLY

如果是 Oracle，你需要基于 ROWNUM 來統計行數：

SELECT name, hp_max FROM heros WHERE ROWNUM <=5 ORDER BY hp_max DESC

需要說明的是，這條陳述句是先取出來前 5 條資料行，然后再按照 hp_max 從高到低的順序進行排序, 如果這樣寫：

SELECT name, hp_max FROM (SELECT name, hp_max FROM heros ORDER BY hp_max) WHERE ROWNUM <=5

就表示先執行查詢結果，再來過濾結果中的前五條，

WHERE陳述句中 AND 和 OR優先級

WHERE 子句中同時出現 AND 和 OR 運算子的時候，你需要考慮到執行的先后順序，也就是兩個運算子執行的優先級，一般來說 () 優先級最高，其次優先級是 AND，然后是 OR，

SQL中的命名規范

MySQL 在 Linux 的環境下，資料庫名、表名、變數名是嚴格區分大小寫的，而欄位名是忽略大小寫的，
而 MySQL 在 Windows 的環境下全部不區分大小寫，

SQL撰寫的一個規范：

資料庫名、表名、表別名、欄位名、欄位別名等都小寫
SQL保留字、函式名、系結變數等都大寫
資料表的欄位名推薦采用下劃線命名
SQL陳述句必須以分號結尾

COUNT(欄位) , COUNT( * ) 和 COUNT(1)

COUNT(欄位)會忽略欄位值值為 NULL 的資料行，而 COUNT( * ) 和 COUNT(1) 只是統計資料行數，不管某個欄位是否為 NULL，

AVG、MAX、MIN 等聚集函式會自動忽略值為 NULL 的資料行，

關聯子查詢和非關聯子查詢

可以依據子查詢是否執行多次，從而將子查詢劃分為關聯子查詢和非關聯子查詢，子查詢從資料表中查詢了資料結果，如果這個資料結果只執行一次，然后這個資料結果作為主查詢的條件進行執行，那么這樣的子查詢叫做非關聯子查詢，同樣，如果子查詢需要執行多次，即采用回圈的方式，先從外部查詢開始，每次都傳入子查詢進行查詢，然后再將結果反饋給外部，這種嵌套的執行方式就稱為關聯子查詢

一個非關聯子查詢的例子：

SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)

一個關聯子查詢的例子：

SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)

(NOT) EXISTS子查詢

SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)

IN VS EXISTS

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

實際上在查詢程序中，在我們對 cc 列建立索引的情況下，我們還需要判斷表 A 和表 B 的大小，在這里例子當中，表 A 指的是 player 表，表 B 指的是 player_score 表，如果表 A 比表 B 大，那么 IN 子查詢的效率要比 EXIST 子查詢效率高，因為這時 B 表中如果對 cc 列進行了索引，那么 IN 子查詢的效率就會比較高，同樣，如果表 A 比表 B 小，那么使用 EXISTS 子查詢效率會更高，因為我們可以使用到 A 表中對 cc 列的索引，而不用從 B 中進行 cc 列的查詢，
當 A 小于 B 時，用 EXISTS，因為 EXISTS 的實作，相當于外表回圈，實作的邏輯類似于：

 for i in A
     for j in B
         if j.cc == i.cc then ...

當 B 小于 A 時用 IN，因為實作的邏輯類似于：

 for i in B
     for j in A
         if j.cc == i.cc then ...

哪個表小就用哪個表來驅動，A 表小就用 EXISTS，B 表小就用 IN，

其他一些子查詢的關鍵字：EXISTS、IN、ANY、ALL 和 SOME

函式

一個簡單的函式例子：Leetcode 177. Nth Highest Salary

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee GROUP BY Salary
      ORDER BY Salary DESC LIMIT 1 OFFSET N
  );
END

Employee表資料如下：

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

函式呼叫：

SELECT getNthHighestSalary(3);

結果：

+------------------------+
| getNthHighestSalary(3) |
+------------------------+
| 100                    |
+------------------------+

存盤程序

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGIN
       DECLARE i INT;
       DECLARE sum INT;

       SET i = 1;
       SET sum = 0;
       WHILE i <= n DO
              SET sum = sum + i;
              SET i = i +1;
       END WHILE;
       SELECT sum;
END //
DELIMITER ;

呼叫

CALL add_num(10);

另一個例子


CREATE PROCEDURE `get_hero_scores`(
       OUT max_max_hp FLOAT,
       OUT min_max_mp FLOAT,
       OUT avg_max_attack FLOAT,
       s VARCHAR(255)
       )
BEGIN
       SELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END

呼叫

CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '戰士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

如何使用游標

CREATE PROCEDURE `calc_hp_max`()
BEGIN
       -- 創建接收游標的變數
       DECLARE hp INT;
       -- 創建總數變數 
       DECLARE hp_sum INT DEFAULT 0;
       -- 創建結束標志變數
     DECLARE done INT DEFAULT false;
       -- 定義游標
       DECLARE cur_hero CURSOR FOR SELECT hp_max FROM heros;
       -- 指定游標回圈結束時的回傳值
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

       OPEN cur_hero;
       read_loop:LOOP
       FETCH cur_hero INTO hp;
       -- 判斷游標的回圈是否結束
       IF done THEN
                     LEAVE read_loop;
       END IF;
       SET hp_sum = hp_sum + hp;
       END LOOP;
       CLOSE cur_hero;
       SELECT hp_sum;
END

更復雜的一個例子

CREATE PROCEDURE `alter_attack_growth`()
BEGIN
       -- 創建接收游標的變數
       DECLARE temp_id INT;
       DECLARE temp_growth, temp_max, temp_start, temp_diff FLOAT;
       -- 創建結束標志變數
       DECLARE done INT DEFAULT false;
       -- 定義游標
       DECLARE cur_hero CURSOR FOR SELECT id, attack_growth, attack_max, attack_start FROM heros;
       -- 指定游標回圈結束時的回傳值
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

       OPEN cur_hero;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       REPEAT
                     IF NOT done THEN
                            SET temp_diff = temp_max - temp_start;
                            IF temp_growth < 5 THEN
                                   IF temp_diff > 200 THEN
                                          SET temp_growth = temp_growth * 1.1;
                                   ELSEIF temp_diff >= 150 AND temp_diff <=200 THEN
                                          SET temp_growth = temp_growth * 1.08;
                                   ELSEIF temp_diff < 150 THEN
                                          SET temp_growth = temp_growth * 1.07;
                                   END IF;
                            ELSEIF temp_growth >=5 AND temp_growth <=10 THEN
                                   SET temp_growth = temp_growth * 1.05;
                            END IF;
                            UPDATE heros SET attack_growth = ROUND(temp_growth,3) WHERE id = temp_id;
                     END IF;
       FETCH cur_hero INTO temp_id, temp_growth, temp_max, temp_start;
       UNTIL done = true END REPEAT;

       CLOSE cur_hero;
END

自動提交（autocommit）

set autocommit =0;  //關閉自動提交

set autocommit =1;  //開啟自動提交

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
BEGIN;
INSERT INTO test SELECT '關羽';
COMMIT;
BEGIN;
INSERT INTO test SELECT '張飛';
INSERT INTO test SELECT '張飛';
ROLLBACK;
SELECT * FROM test;

"張飛"這條記錄，如果資料庫未開啟自動提交，則不會入庫，如果開啟了自動提交，則第二個”張飛“輸入會回滾不插入，但是第一條”張飛“資料依然會插入，

completion_type

CREATE TABLE test(name varchar(255), PRIMARY KEY (name)) ENGINE=InnoDB;
SET @@completion_type = 1;
BEGIN;
INSERT INTO test SELECT '關羽';
COMMIT;
INSERT INTO test SELECT '張飛';
INSERT INTO test SELECT '張飛';
ROLLBACK;
SELECT * FROM test;

MySQL 中 completion_type 這個引數有 3 種可能：

completion=0，這是默認情況，也就是說當我們執行 COMMIT 的時候會提交事務，在執行下一個事務時，還需要我們使用 START TRANSACTION 或者 BEGIN 來開啟，
completion=1，這種情況下，當我們提交事務后，相當于執行了 COMMIT AND CHAIN，也就是開啟一個鏈式事務，即當我們提交事務之后會開啟一個相同隔離級別的事務），
completion=2，這種情況下 COMMIT=COMMIT AND RELEASE，也就是當我們提交后，會自動與服務器斷開連接，

關于事務

臟讀（Dirty Read）

讀到了其他事務還沒有提交的資料，

不可重復讀（Nnrepeatable Read）

對某資料進行讀取，發現兩次讀取的結果不同，也就是說沒有讀到相同的內容，這是因為有其他事務對這個資料同時進行了修改或洗掉，

幻讀（Phantom Read）

事務 A 根據條件查詢得到了 N 條資料，但此時事務 B 更改或者增加了 M 條符合事務 A 查詢條件的資料，這樣當事務 A 再次進行查詢的時候發現會有 N+M 條資料，產生了幻讀，

SQL-92 標準還定義了 4 種隔離級別來解決這些例外情況，

這些隔離級別能解決的例外情況如下表所示：

	臟讀	不可重復讀	幻讀
讀未提交（READ UNCOMMITTED）	允許	允許	允許
讀已提交（READ COMMITTED）	禁止	允許	允許
可重復讀（REPEATABLE READ）	禁止	禁止	允許
可串行化（SERIALIZABLE	禁止	禁止	禁止

讀未提交，也就是允許讀到未提交的資料，這種情況下查詢是不會使用鎖的，可能會產生臟讀、不可重復讀、幻讀等情況，
讀已提交就是只能讀到已經提交的內容，可以避免臟讀的產生，屬于 RDBMS 中常見的默認隔離級別（比如說 Oracle 和 SQL Server），但如果想要避免不可重復讀或者幻讀，就需要我們在 SQL 查詢的時候撰寫帶加鎖的 SQL 陳述句
可重復讀，保證一個事務在相同查詢條件下兩次查詢得到的資料結果是一致的，可以避免不可重復讀和臟讀，但無法避免幻讀，MySQL 默認的隔離級別就是可重復讀，
可串行化，將事務進行串行化，也就是在一個佇列中按照順序執行，可串行化是最高級別的隔離等級，可以解決事務讀取中所有可能出現的例外情況，但是它犧牲了系統的并發性，

在實作上，資料庫里面會創建一個視圖，訪問的時候以視圖的邏輯結果為準，

在“可重復讀”隔離級別下，這個視圖是在事務啟動時創建的，整個事務存在期間都用這個視圖，

在“讀提交”隔離級別下，這個視圖是在每個 SQL 陳述句開始執行的時候創建的，

“讀未提交”隔離級別下直接回傳記錄上的最新值，沒有視圖概念；

“串行化”隔離級別下直接用加鎖的方式來避免并行訪問，

我們可以看到在不同的隔離級別下，資料庫行為是有所不同的，Oracle 資料庫的默認隔離級別其實就是“讀提交”，因此對于一些從 Oracle 遷移到 MySQL 的應用，為保證資料庫隔離級別的一致，你一定要記得將 MySQL 的隔離級別設定為“讀提交”，

查看隔離級別

SHOW VARIABLES LIKE 'transaction_isolation';

配置隔離級別

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

關于MVCC

多版本并發控制技術，是通過資料行的多個版本管理來實作資料庫的并發控制，簡單來說它的思想就是保存資料的歷史版本，這樣我們就可以通過比較版本號決定資料是否顯示出來，讀取資料的時候不需要加鎖也可以保證事務的隔離效果，

通過 MVCC 我們可以解決以下幾個問題：

讀寫之間阻塞的問題，通過 MVCC 可以讓讀寫互相不阻塞，即讀不阻塞寫，寫不阻塞讀，這樣就可以提升事務并發處理能力，
降低了死鎖的概率，這是因為 MVCC 采用了樂觀鎖的方式，讀取資料時并不需要加鎖，對于寫操作，也只鎖定必要的行，
解決一致性讀的問題，一致性讀也被稱為快照讀，當我們查詢資料庫在某個時間點的快照時，只能看到這個時間點之前事務提交更新的結果，而不能看到這個時間點之后事務提交的更新結果，

使用了如下內容來實作：

事務版本號
行記錄的隱藏列
Undo Log

InnoDB 中，MVCC 是通過 Undo Log + Read View 進行資料讀取，Undo Log 保存了歷史快照，而 Read View 規則幫我們判斷當前版本的資料是否可見，需要說明的是，在隔離級別為讀已提交（Read Commit）時，一個事務中的每一次 SELECT 查詢都會獲取一次 Read View，

出現幻讀的原因是在讀已提交的情況下，InnoDB 只采用記錄鎖（Record Locking），這里要介紹下 InnoDB 三種行鎖的方式：記錄鎖：針對單個行記錄添加鎖，間隙鎖（Gap Locking）：可以幫我們鎖住一個范圍（索引之間的空隙），但不包括記錄本身，采用間隙鎖的方式可以防止幻讀情況的產生，Next-Key 鎖：幫我們鎖住一個范圍，同時鎖定記錄本身，相當于間隙鎖 + 記錄鎖，可以解決幻讀的問題，在隔離級別為可重復讀時，InnoDB 會采用 Next-Key 鎖的機制，幫我們解決幻讀問題，

為什么建議盡量不要使用長事務

長事務意味著系統里面會存在很老的事務視圖，由于這些事務隨時可能訪問資料庫里面的任何資料，所以這個事務提交之前，資料庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存盤空間，在 MySQL 5.5 及以前的版本，回滾日志是跟資料字典一起放在 ibdata 檔案里的，即使長事務最終提交，回滾段被清理，檔案也不會變小，

除了對回滾段的影響，長事務還占用鎖資源，也可能拖垮整個庫，

在 information_schema 庫的 innodb_trx 這個表中查詢長事務，比如下面這個陳述句，用于查找持續時間超過 60s 的事務，


select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

一天一備 VS 一周一備

在一天一備的模式里，最壞情況下需要應用一天的 binlog，比如，你每天 0 點做一次全量備份，而要恢復出一個到昨天晚上 23 點的備份，一周一備最壞情況就要應用一周的 binlog 了，

關于資料庫調優

導圖參考自20丨當我們思考資料庫調優的時候，都有哪些維度可以選擇？

關于各種范式

1NF 指的是資料庫表中的任何屬性都是原子性的，不可再分，

2NF 指的資料表里的非主屬性都要和這個資料表的候選鍵有完全依賴關系，所謂完全依賴不同于部分依賴，也就是不能僅依賴候選鍵的一部分屬性，而必須依賴全部屬性，

一個沒有滿足 2NF 的例子，

一張球員比賽表 player_game，里面包含球員編號、姓名、年齡、比賽編號、比賽時間和比賽場地等屬性，
這里候選鍵和主鍵都為:（球員編號，比賽編號），

我們可以通過候選鍵來決定如下的關系：

(球員編號, 比賽編號) → (姓名, 年齡, 比賽時間, 比賽場地，得分)

上面這個關系說明球員編號和比賽編號的組合決定了球員的姓名、年齡、比賽時間、比賽地點和該比賽的得分資料，
但是這個資料表不滿足第二范式，因為資料表中的欄位之間還存在著如下的對應關系：

(球員編號) → (姓名，年齡)
(比賽編號) → (比賽時間, 比賽場地)

也就是說候選鍵中的某個欄位決定了非主屬性，
插入例外：如果我們想要添加一場新的比賽，但是這時還沒有確定參加的球員都有誰，那么就沒法插入，
洗掉例外：如果我要洗掉某個球員編號，如果沒有單獨保存比賽表的話，就會同時把比賽資訊洗掉掉，
更新例外：如果我們調整了某個比賽的時間，那么資料表中所有這個比賽的時間都需要進行調整，否則就會出現一場比賽時間不同的情況，

3NF 在滿足 2NF 的同時，對任何非主屬性都不傳遞依賴于候選鍵，也就是說不能存在非主屬性 A 依賴于非主屬性 B，非主屬性 B 依賴于候選鍵的情況，比如：

你能看到球員編號決定了球隊名稱，同時球隊名稱決定了球隊主教練，非主屬性球隊主教練就會傳遞依賴于球員編號，因此不符合 3NF 的要求，

關于索引

索引模型

Hash索引

適用于只有等值查詢的場景，因為不是有序的，所以做范圍查詢的速度是很慢的，

有序陣列

有序陣列在等值查詢和范圍查詢場景中的性能就都非常優秀, 有序陣列索引只適用于靜態存盤引擎

二叉搜索樹

查詢復雜度是：O(log(N)) ，需要保持這棵樹是平衡二叉樹，為了做這個保證，更新的時間復雜度也是 O(log(N))，樹可以有二叉，也可以有多叉，多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右遞增，二叉樹是搜索效率最高的，但是實際上大多數的資料庫存盤卻并不使用二叉樹，其原因是，索引不止存在記憶體中，還要寫到磁盤上，你可以想象一下一棵 100 萬節點的平衡二叉樹，樹高 20，一次查詢可能需要訪問 20 個資料塊，在機械硬碟時代，從磁盤隨機讀一個資料塊需要 10 ms 左右的尋址時間，也就是說，對于一個 100 萬行的表，如果使用二叉樹來存盤，單獨訪問一個行可能需要 20 個 10 ms 的時間，這個查詢可真夠慢的，為了讓一個查詢盡量少地讀磁盤，就必須讓查詢程序訪問盡量少的資料塊，那么，我們就不應該使用二叉樹，而是要使用“N 叉”樹，這里，“N 叉”樹中的“N”取決于資料塊的大小，

B樹和B+樹

先看單次查詢，為了盡可能快的命中資料，我們希望盡可能的將更多的索引資料存盤在記憶體中，b樹有一個特點，每一層都會存盤真正的資料，這會擠壓索引可用的記憶體空間，從而在整體上增加io次數，另外，如果只看等值查詢的話，樹型索引是不如hash索引的，其次，關系資料庫中還會大量使用范圍查詢、有序查詢等，比如某時間范圍內的用戶交易資料，范圍查詢，這種查詢的特點是會大量使用排序，比較，回傳結果也往往是多條，如果使用b樹的話，需要使用中序遍歷，因為資料節點不在同一層上，會頻繁引起io，從而導致整體速度下降，而在b+樹中，所有的資料節點都在葉子節點，相近的葉子節點之間也存在著鏈接，因此會節約io時間，這樣，b+樹整體上就比b樹要快，其實，b+樹主要應用于關系型資料庫中，也有使用b樹做索引的資料庫，比如mangodb，
MySQL 的 InnoDB 存盤引擎還有個“自適應 Hash 索引”的功能，就是當某個索引值使用非常頻繁的時候，它會在 B+ 樹索引的基礎上再創建一個 Hash 索引，這樣讓 B+ 樹也具備了 Hash 索引的優點

索引的分類

功能上分：普通索引，唯一索引，主鍵索引，全文索引

主鍵索引的葉子節點存的是整行資料，在 InnoDB 里，主鍵索引也被稱為聚簇索引（clustered index），非主鍵索引的葉子節點內容是主鍵的值，在 InnoDB 里，非主鍵索引也被稱為二級索引（secondary index），

什么是回表，什么是覆寫索引？(ID是主鍵索引，k是普通索引)

如果陳述句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹；

如果陳述句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次，這個程序稱為回表，

如果執行的陳述句是 select ID from T where k between 3 and 5，這時只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表，也就是說，在這個查詢里面，索引 k 已經“覆寫了”我們的查詢需求，我們稱為覆寫索引，

身份證號是市民的唯一標識，也就是說，如果有根據身份證號查詢市民資訊的需求，我們只要在身份證號欄位上建立索引就夠了，而再建立一個（身份證號、姓名）的聯合索引，是不是浪費空間？如果現在有一個高頻請求，要根據市民的身份證號查詢他的姓名，這個聯合索引就有意義了，它可以在這個高頻請求上用到覆寫索引，不再需要回表查整行記錄，減少陳述句的執行時間，

物理結構上：聚集索引(順序)和非聚集索引(非順序)，可以類比鏈表和陣列的區別，

欄位上分：單一索引和聯合索引（最左匹配原則）

聚集索引的葉子節點存盤的就是我們的資料記錄，非聚集索引的葉子節點存盤的是資料位置，非聚集索引不會影響資料表的物理存盤順序，
一個表只能有一個聚集索引，因為只能有一種排序存盤的方式，但可以有多個非聚集索引，也就是多個索引目錄提供資料檢索，
使用聚集索引的時候，資料的查詢效率高，但如果對資料進行插入，洗掉，更新等操作，效率會比非聚集索引低，

什么時候創建索引，什么時候不應該創建索引？

創建索引

欄位唯一
WHERE頻繁查詢
經常GROUP BY或者ORDER BY的列
DISTINCT欄位

不適合的情況

頻繁更新的欄位
重復資料比較多的欄位
WHERE用不到的欄位

InnoDB 表 T，如果你要重建索引 k，你的兩個 SQL 陳述句可以這么寫：

alter table T drop index k;

alter table T add index(k);

如果你要重建主鍵索引，也可以這么寫：

alter table T drop primary key;

alter table T add primary key(id);

其中，重建索引 k 的做法是合理的，可以達到省空間的目的，但是，重建主鍵的程序不合理，不論是洗掉主鍵還是創建主鍵，都會將整個表重建，所以連著執行這兩個陳述句的話，第一個陳述句就白做了，這兩個陳述句，你可以用這個陳述句代替： alter table T engine=InnoDB，

分析一下哪些場景下應該使用自增主鍵，而哪些場景下不應該？

自增主鍵每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂，

而有業務邏輯的欄位做主鍵，則往往不容易保證有序插入，這樣寫資料成本相對較高，

除了考慮性能外，還可以從存盤空間的角度來看，

假設你的表中確實有一個唯一欄位，比如字串型別的身份證號，那應該用身份證號做主鍵，還是用自增欄位做主鍵呢？

由于每個非主鍵索引的葉子節點上都是主鍵的值，如果用身份證號做主鍵，那么每個二級索引的葉子節點占用約 20 個位元組，而如果用整型做主鍵，則只要 4 個位元組，如果是長整型（bigint）則是 8 個位元組，顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引占用的空間也就越小，

所以，從性能和存盤空間方面考量，自增主鍵往往是更合理的選擇，

有沒有什么場景適合用業務欄位直接做主鍵的呢？

比如典型的 KV 場景，由于沒有其他索引，所以也就不用考慮其他索引的葉子節點大小的問題，這時候我們就要優先考慮上一段提到的“盡量使用主鍵查詢”原則，直接將這個索引設定為主鍵，可以避免每次查詢需要搜索兩棵樹，

最左前綴原則，

基于以上原則，在建立聯合索引的時候，如何安排索引內的欄位順序？

因為可以支持最左前綴，所以當已經有了 (a,b) 這個聯合索引后，一般就不需要單獨在 a 上建立索引了，因此，第一原則是，如果通過調整順序，可以少維護一個索引，那么這個順序往往就是需要優先考慮采用的，

那么，如果既有聯合查詢，又有基于 a、b 各自的查詢呢？查詢條件里面只有 b 的陳述句，是無法使用 (a,b) 這個聯合索引的，這時候你不得不維護另外一個索引，也就是說你需要同時維護 (a,b)、(b) 這兩個索引，這時候，我們要考慮的原則就是空間了，比如上面這個市民表的情況，name 欄位是比 age 欄位大的，那我就建議你創建一個（name,age) 的聯合索引和一個 (age) 的單欄位索引，

索引下推原則

以市民表的聯合索引（name, age）為例，如果現在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”，那么，SQL 陳述句是這么寫的：

select * from tuser where name like '張%' and age=10 and ismale=1;

在 MySQL 5.6 之前，只能從 ID3 開始一個個回表，到主鍵索引上找出資料行，再對比欄位值，

而 MySQL 5.6 引入的索引下推優化（index condition pushdown)，可以在索引遍歷程序中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數，

什么時候索引失效

索引欄位使用了運算式
使用函式
在 WHERE 子句中，如果在 OR 前的條件列進行了索引，而在 OR 后的條件列沒有進行索引，那么索引會失效，
當我們使用 LIKE 進行模糊查詢的時候，前面不能是 %
最左原則

資料庫中的存盤結構

資料庫管理存盤空間的基本單位是頁（Page），一頁中可以存盤多行記錄, InnoDB中頁大小查看

show variables like '%innodb_page_size%';

我們在分配空間的時候會按照頁為單位來進行分配，同一棵樹上同一層的頁與頁之間采用雙向鏈表，而在頁里面，記錄之間采用的單向鏈表的方式，

區（Extent）是比頁大一級的存盤結構，InnoDB 中，頁大小默認是 16KB, 一個區會分配 64 個連續的頁，所以一個區的大小是:

64*16KB=1MB，

段（Segment）由一個或多個區組成，不過在段中不要求區與區之間是相鄰的，段是資料庫中的分配單位，不同型別的資料庫物件以不同的段形式存在，當我們創建資料表、索引的時候，就會相應創建對應的段，比如創建一張表時會創建一個表段，創建一個索引時會創建一個索引段，

表空間（Tablespace）是一個邏輯容器，表空間存盤的物件是段，在一個表空間中可以有一個或多個段，但是一個段只能屬于一個表空間，資料庫由一個或多個表空間組成，表空間從管理上可以劃分為系統表空間、用戶表空間、撤銷表空間、臨時表空間等，在 InnoDB 中存在兩種表空間的型別：

共享表空間和獨立表空間，

如果是共享表空間就意味著多張表共用一個表空間，
如果是獨立表空間，就意味著每張表有一個獨立的表空間，也就是資料和索引資訊都會保存在自己的表空間中，獨立的表空間可以在不同的資料庫之間進行遷移，

show variables like 'innodb_file_per_table';

關于資料庫緩沖池

-- 每個緩沖池多大
show variables like 'innodb_buffer_pool_size'

-- 可以開啟多個緩沖池
show variables like 'innodb_buffer_pool_instances'

-- 獲取查詢頁的數量
SHOW STATUS LIKE 'last_query_cost';

三星索引

在 WHERE 條件陳述句中，找到所有等值謂詞中的條件列，將它們作為索引片中的開始列；
將 GROUP BY 和 ORDER BY 中的列加入到索引中；
將 SELECT 欄位中剩余的列加入到索引片中，

關于資料庫中的鎖

按鎖粒度劃分

行鎖
表鎖（表鎖，MySQL 5.5引入了元資料鎖）
全域鎖

不同的資料庫和存盤引擎支持的鎖粒度不同，InnoDB 和 Oracle 支持行鎖和表鎖，而 MyISAM 只支持表鎖，MySQL 中的 BDB 存盤引擎支持頁鎖和表鎖，SQL Server 可以同時支持行鎖、頁鎖和表鎖，

全域鎖的典型使用場景是，做全庫的邏輯備份，官方自帶的邏輯備份工具是 mysqldump，當 mysqldump 使用引數–single-transaction 的時候，導資料之前就會啟動一個事務，來確保拿到一致性視圖，而由于 MVCC 的支持，這個程序中資料是可以正常更新的，single-transaction 方法只適用于所有的表使用事務引擎的庫，如果有的表使用了不支持事務的引擎，那么備份就只能通過 FTWRL 方法，這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一，

為什么不使用 set global readonly=true 的方式呢？主要有兩個原因：

一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫，因此，修改 global 變數的方式影響面更大，不建議使用，

二是，在例外處理機制上有差異，如果執行 FTWRL 命令之后由于客戶端發生例外斷開，那么 MySQL 會自動釋放這個全域鎖，整個庫回到可以正常更新的狀態，而將整個庫設定為 readonly 之后，如果客戶端發生例外，則資料庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處于不可寫狀態，風險較高，

事務中的 MDL 鎖，在陳述句執行開始時申請，但是陳述句結束后并不會馬上釋放，而會等到整個事務提交后再釋放，

那么如何安全地給小表加欄位？

首先我們要解決長事務，事務不提交，就會一直占著 MDL 鎖，在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務，如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務，但考慮一下這個場景，如果你要變更的表是一個熱點表，雖然資料量不大，但是上面的請求很頻繁，而你不得不加個欄位，你該怎么做呢？這時候 kill 可能未必管用，因為新的請求馬上就來了，比較理想的機制是，在 alter table 陳述句里面設定等待時間，如果在這個指定的等待時間里面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞后面的業務陳述句，先放棄，之后開發人員或者 DBA 再通過重試命令重復這個程序，MariaDB 已經合并了 AliSQL 的這個功能，所以這兩個開源分支目前都支持 DDL NOWAIT/WAIT n 這個語法，

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

我們還可以從資料庫管理的角度對鎖進行劃分，共享鎖和排它鎖

加共享鎖：

LOCK TABLE product_comment READ;

解鎖：

UNLOCK TABLE;

加排他鎖

LOCK TABLE product_comment WRITE;

解鎖

UNLOCK TABLE;

意向鎖（Intent Lock），簡單來說就是給更大一級別的空間示意里面是否已經上過鎖，
如果事務想要獲得資料表中某些記錄的共享鎖，就需要在資料表上添加意向共享鎖，同理，事務想要獲得資料表中某些記錄的排他鎖，就需要在資料表上添加意向排他鎖，這時，意向鎖會告訴其他事務已經有人鎖定了表中的某些記錄，不能對整個表進行全表掃描，

從程式員角度劃分

樂觀鎖：通過版本號或者時間戳來控制
悲觀鎖：對資料被其他事務的修改持保守態度，會通過資料庫自身的鎖機制來實作，從而保證資料操作的排它性，

如果事務涉及多個表，操作比較復雜，那么可以盡量一次鎖定所有的資源，而不是逐步來獲取，這樣可以減少死鎖發生的概率；
如果事務需要更新資料表中的大部分資料，資料表又比較大，這時可以采用鎖升級的方式，比如將行級鎖升級為表級鎖，從而減少死鎖產生的概率；
不同事務并發讀寫多張資料表，可以約定訪問表的順序，采用相同的順序降低死鎖發生的概率，

當然在資料庫中，也有一些情況是不會發生死鎖的，比如采用樂觀鎖的方式，另外在 MySQL MyISAM 存盤引擎中也不會出現死鎖，這是因為 MyISAM 總是一次性獲得全部的鎖，這樣的話要么全部滿足可以執行，要么就需要全部等待，

使用 MySQL InnoDB 存盤引擎時，為什么對某行資料添加排它鎖之前，會在資料表上添加意向排他鎖呢？

因為要告訴其他人這個資料頁或資料表已經有人上過排它鎖了，這樣當其他人想要獲取資料表排它鎖的時候，只需要了解是否有人已經獲取了這個資料表的意向排他鎖即可，而不需要進行全表的掃描，節省時間，提高效率！

死鎖和死鎖檢測

當并發系統中不同執行緒出現回圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖，

這里我用資料庫中的行鎖舉個例子，這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖，

事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態，

當出現死鎖以后，有兩種策略：

一種策略是，直接進入等待，直到超時，這個超時時間可以通過引數 innodb_lock_wait_timeout 來設定，
另一種策略是，發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行，將引數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯，在 InnoDB 中，innodb_lock_wait_timeout 的默認值是 50s，意味著如果采用第一個策略，當出現死鎖以后，第一個被鎖住的執行緒要過 50s 才會超時退出，然后其他執行緒才有可能繼續執行，

對于在線服務來說，這個等待時間往往是無法接受的，但是，我們又不可能直接把這個時間設定成一個很小的值，比如 1s，這樣當出現死鎖的時候，確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設定太短的話，會出現很多誤傷，

所以，正常情況下我們還是要采用第二種策略，即：主動死鎖檢測，而且 innodb_deadlock_detect 的默認值本身就是 on，主動死鎖檢測在發生死鎖的時候，是能夠快速發現并進行處理的，但是它也是有額外負擔的，你可以想象一下這個程序：每當一個事務被鎖的時候，就要看看它所依賴的執行緒有沒有被別人鎖住，如此回圈，最后判斷是否出現了回圈等待，也就是死鎖，那如果是我們上面說到的所有事務都要更新同一行的場景呢？每個新來的被堵住的執行緒，都要判斷會不會由于自己的加入導致了死鎖，這是一個時間復雜度是 O(n) 的操作，假設有 1000 個并發執行緒要同時更新同一行，那么死鎖檢測操作就是 100 萬這個量級的，雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的 CPU 資源，因此，你就會看到 CPU 利用率很高，但是每秒卻執行不了幾個事務，根據上面的分析，我們來討論一下，怎么解決由這種熱點行更新導致的性能問題呢？問題的癥結在于，死鎖檢測要耗費大量的 CPU 資源，

一種方案是：
如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉，但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就回滾，然后通過業務重試一般就沒問題了，這是業務無損的，而關掉死鎖檢測意味著可能會出現大量的超時，這是業務有損的，另一個思路是控制并發度，根據上面的分析，你會發現如果并發能夠控制住，比如同一行同時最多只有 10 個執行緒在更新，那么死鎖檢測的成本很低，就不會出現這個問題，一個直接的想法就是，在客戶端做并發控制，但是，你會很快發現這個方法不太可行，因為客戶端很多，我見過一個應用，有 600 個客戶端，這樣即使每個客戶端控制到只有 5 個并發執行緒，匯總到資料庫服務端以后，峰值并發數也可能要達到 3000，因此，這個并發控制要做在資料庫服務端，如果你有中間件，可以考慮在中間件實作；如果你的團隊有能修改 MySQL 原始碼的人，也可以做在 MySQL 里面，基本思路就是，對于相同行的更新，在進入引擎之前排隊，這樣在 InnoDB 內部就不會有大量的死鎖檢測作業了，

第一種方案是：
你可以考慮通過將一行改成邏輯上的多行來減少鎖沖突，還是以影院賬戶為例，可以考慮放在多條記錄上，比如 10 個記錄，影院的賬戶總額等于這 10 個記錄的值的總和，這樣每次要給影院賬戶加金額的時候，隨機選其中一條記錄來加，這樣每次沖突概率變成原來的 1/10，可以減少鎖等待個數，也就減少了死鎖檢測的 CPU 消耗，這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計，如果賬戶余額可能會減少，比如退票邏輯，那么這時候就需要考慮當一部分行記錄變成 0 的時候，代碼要有特殊處理，

安全洗掉前10000行資料

如果你要洗掉一個表里面的前 10000 行資料，有以下三種方法可以做到：

第一種，直接執行 delete from T limit 10000;

事務相對較長，則占用鎖的時間較長，會導致其他客戶端等待資源時間較長，

第二種，在一個連接中回圈執行 20 次 delete from T limit 500;

串行化執行，將相對長的事務分成多次相對短的事務，則每次事務占用鎖的時間相對較短，其他客戶端在等待相應資源的時間也較短，這樣的操作，同時也意味著將資源分片使用（每次執行使用不同片段的資源），可以提高并發性，

第三種，在 20 個連接中同時執行 delete from T limit 500，

人為自己制造鎖競爭，加劇并發量，

關于事務的隔離

參考這個文章：https://time.geekbang.org/column/article/70562

CBO VS RBO

第一種是基于規則的優化器（RBO，Rule-Based Optimizer），規則就是人們以往的經驗，或者是采用已經被證明是有效的方式，通過在優化器里面嵌入規則，來判斷 SQL 查詢符合哪種規則，就按照相應的規則來制定執行計劃，同時采用啟發式規則去掉明顯不好的存取路徑，

第二種是基于代價的優化器（CBO，Cost-Based Optimizer），這里會根據代價評估模型，計算每條可能的執行計劃的代價，也就是 COST，從中選擇代價最小的作為執行計劃，相比于 RBO 來說，CBO 對資料更敏感，因為它會利用資料表中的統計資訊來做判斷，針對不同的資料表，查詢得到的執行計劃可能是不同的，因此制定出來的執行計劃也更符合資料表的實際情況，

但我們需要記住，SQL 是面向集合的語言，并沒有指定執行的方式，因此在優化器中會存在各種組合的可能，我們需要通過優化器來制定資料表的掃描方式、連接方式以及連接順序，從而得到最佳的 SQL 執行計劃，

你能看出來，RBO 的方式更像是一個出租車老司機，憑借自己的經驗來選擇從 A 到 B 的路徑，而 CBO 更像是手機導航，通過資料驅動，來選擇最佳的執行路徑，

5.7.10 版本之后，MySQL 會引入兩張資料表，里面規定了各種步驟預估的代價（Cost Value），我們可以從mysql.server_cost和mysql.engine_cost這兩張表中獲得這些步驟的代價

定位慢SQL方法

mysqldumpslow
EXPLAIN 查看執行計劃

SHOW PROFILE 查看開銷

如何查看執行計劃

主從同步原理

提到主從同步的原理，我們就需要了解在資料庫中的一個重要日志檔案，那就是 Binlog 二進制日志，它記錄了對資料庫進行更新的事件，實際上主從同步的原理就是基于 Binlog 進行資料同步的，

在主從復制程序中，會基于 3 個執行緒來操作，一個主庫執行緒，兩個從庫執行緒，二進制日志轉儲執行緒（Binlog dump thread）是一個主庫執行緒，當從庫執行緒連接的時候，主庫可以將二進制日志發送給從庫，當主庫讀取事件的時候，會在 Binlog 上加鎖，讀取完成之后，再將鎖釋放掉，從庫 I/O 執行緒會連接到主庫，向主庫發送請求更新 Binlog，這時從庫的 I/O 執行緒就可以讀取到主庫的二進制日志轉儲執行緒發送的 Binlog 更新部分，并且拷貝到本地形成中繼日志（Relay log），從庫 SQL 執行緒會讀取從庫中的中繼日志，并且執行日志中的事件，從而將從庫中的資料與主庫保持同步，

如何解決主從不一致問題

異步復制
半同步復制

MySQL5.5 版本之后開始支持半同步復制的方式，原理是在客戶端提交 COMMIT 之后不直接將結果回傳給客戶端，而是等待至少有一個從庫接收到了 Binlog，并且寫入到中繼日志中，再回傳給客戶端，這樣做的好處就是提高了資料的一致性，當然相比于異步復制來說，至少多增加了一個網路連接的延遲，降低了主庫寫的效率，在 MySQL5.7 版本中還增加了一個rpl_semi_sync_master_wait_for_slave_count引數，我們可以對應答的從庫數量進行設定，默認為 1，也就是說只要有 1 個從庫進行了回應，就可以回傳給客戶端，如果將這個引數調大，可以提升資料一致性的強度，但也會增加主庫等待從庫回應的時間，

組復制（MySQL 5.7.17 以后，基于Paxos）

MySQL的備份與恢復

方式1：

innodb_force_recovery

方式2：

Linux下MySQL資料庫的備份與恢復

Redo Log VS BinLog

Redo Log是引擎層日志

使用了WAL技術，先寫日志，再寫磁盤，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log（粉板）里面，并更新記憶體，這個時候更新就算完成了，同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統比較空閑的時候做

redo log是固定大小的，可以配置一組4個檔案，每個檔案大小1GB，那么可以記錄4G記錄，

有了 redo log，InnoDB 就可以保證即使資料庫發生例外重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe，

binlog是Server層日志

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 層實作的，所有引擎都可以使用，
redo log 是物理日志，記錄的是“在某個資料頁上做了什么修改”；binlog 是邏輯日志，記錄的是這個陳述句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”，redo log 是回圈寫的，空間固定會用完；
binlog 是可以追加寫入的，“追加寫”是指 binlog 檔案寫到一定大小后會切換到下一個，并不會覆寫以前的日志，

兩階段提交

MySQL在執行這段更新操作的時候：

update T set c=c+1 where ID=2;

圖片參考自：MySQL實戰45講

操作程序如上圖: 圖中淺色框表示是在 InnoDB 內部執行的，深色框表示是在執行器中執行的，最后三步將 redo log 的寫入拆成了兩個步驟：prepare 和 commit，這就是"兩階段提交"，

為什么要使用兩階段提交？如果不使用，就會出現如下情況：

先寫 redo log 后寫 binlog，假設在 redo log 寫完，binlog 還沒有寫完的時候，MySQL 行程例外重啟，由于我們前面說過的，redo log 寫完之后，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復后這一行 c 的值是 1，但是由于 binlog 沒寫完就 crash 了，這時候 binlog 里面就沒有記錄這個陳述句，因此，之后備份日志的時候，存起來的 binlog 里面就沒有這條陳述句，然后你會發現，如果需要用這個 binlog 來恢復臨時庫的話，由于這個陳述句的 binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是 0，與原庫的值不同，
先寫 binlog 后寫 redo log，如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復以后這個事務無效，所以這一行 c 的值是 0，但是 binlog 里面已經記錄了“把 c 從 0 改成 1”這個日志，所以，在之后用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是 1，與原庫的值不同，

可以看到，如果不使用“兩階段提交”，那么資料庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致，

redo log 用于保證 crash-safe 能力，innodb_flush_log_at_trx_commit 這個引數設定成 1 的時候，表示每次事務的 redo log 都直接持久化到磁盤，這個引數我建議你設定成 1，這樣可以保證 MySQL 例外重啟之后資料不丟失，

sync_binlog 這個引數設定成 1 的時候，表示每次事務的 binlog 都持久化到磁盤，這個引數我也建議你設定成 1，這樣可以保證 MySQL 例外重啟之后 binlog 不丟失，

參考檔案

MySQL實戰45講

SQL必知必會

MySQL 面試題閱讀指南

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/296442.html

標籤：其他

上一篇：SQL 入門教程：創建表（CREATE TABLE）

下一篇：MSSQL·查詢DB實體版本&DB兼容性查改