1. MySQL引擎
mysql> show engines;
複製程式碼| Engine | Support | Comment | Transactions | XA | Savepoints |
|---|---|---|---|---|---|
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
其他引擎詳情請移步：dev.mysql.com/doc/refman/…
InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine.
- 翻譯：
innodb: mysql 5.7 中的預設儲存引擎。innodb 是 mysql 的事務安全 (符合 acid) 儲存引擎, 具有提交、回滾和崩潰恢復功能, 可保護使用者資料。innodb 行級鎖定 (不升級到更粗粒度鎖) 和 oracle 型別一致使用非鎖定讀取，可提高多使用者併發性和效能。innodb 將使用者資料儲存在聚集索引中, 以減少基於主鍵的常見查詢的 I/O。為了保持資料完整性, innodb 還支援外來鍵。有關 innodb 的詳細資訊, 請參閱第14章, inodb 儲存引擎。
InnoDB儲存資料結構 - B+Tree
- 為什麼使用B+Tree?
索引的常見模型常見的有，雜湊表、有序陣列、搜尋樹。
有序陣列，優點是等值查詢，範圍查詢都非常快，缺點也很明顯，就是插入效率太低，因為如果從中間插入，要移動後面所有的元素。
Hash Index Characteristics(雜湊索引特性)
Hash indexes have somewhat different characteristics from those just discussed:
They are used only for equality comparisons that use the = or <=> operators (but are very fast). They are not used for comparison operators such as < that find a range of values. Systems that rely on this type of single-value lookup are known as “key-value stores”; to use MySQL for such applications, use hash indexes wherever possible.
雜湊結構只適用於等值查詢（但這樣速度非常快）。雜湊結構不支援順序檢索例如'<'、'>'、"between and"等，這種儲存結構屬於“鍵值”查詢，符合這種需求可以考慮使用雜湊索引。
The optimizer cannot use a hash index to speed up ORDER BY operations. (This type of index cannot be used to search for the next entry in order.)
優化器不能使用雜湊索引來加快 order by 操作。(此型別的索引不能用於按順序搜尋下一個條目。
MySQL cannot determine approximately how many rows there are between two values (this is used by the range optimizer to decide which index to use). This may affect some queries if you change a MyISAM or InnoDB table to a hash-indexed MEMORY table.
mysql 不能大致確定兩個值之間有多少行 (範圍優化器使用它來決定要使用哪個索引)。如果將 MyISAM 或 InnoDB 表更改為雜湊索引的記憶體表, 這可能會影響某些查詢。
- Only whole keys can be used to search for a row. (With a B-tree index, any leftmost prefix of the key can be used to find rows.)
只有等值匹配才能適用雜湊結構查詢某一行。（而適用B-tree索引，最左字首就可以用於查詢。） 雜湊表，優點就是查詢快，缺點是範圍查詢效率很低（因為無序）。適用於等值查詢。
B-Tree Index Characteristics(B樹索引特性)
A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.
一個B樹索引可以適用於=、>、>=、<、<=、BETWEEN 等操作符。B樹索引也可以用於LIKE比較，只有當LIKE的引數是一個字串常量並且不以萬用字元開始才可以適用索引。 樹結構，優點有序，並且多叉樹可以減少磁碟I/O次數。
- B-Tree和B+Tree。
衛星資料：指索引元素所指向的資料記錄。例如資料庫中的某一行資料。
InnoDB索引模型
CREATE TABLE `r` (
`id` int NOT NULL primary key auto_increment,
`k` int not null,
`name` varchar(16),
index(k)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
複製程式碼在表中插入(1,10,"張三"),(2,20,"李四"),(3,30,"王五")。則索引如下圖
在InnoDB裡，主鍵索引被稱為聚簇索引或聚集索引（clustered index）,非主鍵索引被稱為二級索引或輔助索引(secondary index)。 在InnoDB中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表成為索引組織表。每一個索引在InnoDB裡對應一棵B+樹，資料是有序排列的。 聚簇索引生成規則：
- When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.
定義主鍵用主鍵作為聚簇索引。
- If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.
沒定義主鍵使用第一個唯一非空索引作為聚簇索引。
- If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
沒定義主鍵，也沒定義唯一索引，生成一個隱藏的列作為聚簇索引。
更多詳情
基於主鍵索引和普通索引查詢有什麼區別？
- 如果sql是 select * from r where id = 1; 即通過主鍵方式查詢，只需要搜尋主鍵這棵B+樹。
- 如果sql是 select * from r where k = 10; 即通過普通索引查詢，需要先搜尋普通索引k這棵B+樹，拿到主鍵id=1,在用id=1再去搜尋主鍵索引的B+樹。這個過程叫做回表。
在分析一個sql語句：select * from r where k between 8 and 22;
- 在k索引樹上找到k=10的記錄，取得id=1;
- 在id索引樹上找到id=1的對應的行記錄data(回表);
- 在k索引樹上找到k=20的記錄，取得id=2;
- 在id索引樹上找到id=2的對應的行記錄data(回表);
- 在k索引樹取下一個值k=30,不滿足，迴圈結束。
這個例子由於要查詢的結果只有主鍵索引上面才有，所以不得不回表。那麼如何避免回表？
覆蓋索引
如果sql語句是：select id from r where k between 8 and 22，由於這時只需要查詢id值，而id值已經在k索引樹上了，所以不需要回表查詢，索引k已經覆蓋了我們的查詢需求，稱之為覆蓋索引。
由於覆蓋索引可以減少數的搜尋次數，顯著提高查詢效能，所以使用覆蓋索引是一個常用的優化手段。
場景：假設有一個市民表：
CREATE TABLE `citizen` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
複製程式碼是否有必要建立身份證號和姓名的聯合索引？
根據業務來看，如果有根據身份證號查詢姓名的高頻需求，可以考慮建立身份證號和姓名的聯合索引，避免回表提高查詢的效率。
最左字首原則
select * from citizen where name = "張三" ;
複製程式碼這個肯定是可以用name索引的，如果要查詢姓張的人，語句是
select * from citizen where name like '張%';
複製程式碼這時也可以用上name的索引，查詢到第一個以張開頭的人，向後遍歷直到不滿足條件為止。
而如果要檢索姓張，年齡10歲的男孩。
select * from tuser where name like '張%' and age=10 and ismale=1;
複製程式碼這個在MySQL5.6以前是要根據查詢到姓張的人開始一個一個回表去查詢age是否滿足10的，而5.6引入了索引下推優化(index condition pushdown)，可以在遍歷中，對索引中包含的欄位先判斷，過濾掉不滿足的記錄，減少回表次數。 以下兩句可以使用到索引。
1. SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
2. SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
複製程式碼第一句只有'Patrick' <= key_col < 'Patricl'的行才會被篩選出來，而第二句只有'Pat' <= key_col < 'Pau' 會被篩選出來。
以下兩句不會使用索引：
3. SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
4. SELECT * FROM tbl_name WHERE key_col LIKE other_col;
複製程式碼第三句由於以萬用字元開始，不符合最左字首原則，所以不能適用索引。第四句，由於LIKE的引數不是一個字串常量，所以也不使用索引。
如果用 LIKE '%string%' 字串長度超過3，會使用串匹配的BM演算法提高查詢效率。
另外，如果某一列有索引，如果值為空，使用where col_name IS NULL也是可以走索引的。
如果業務滿足某欄位唯一，是否可以考慮用該欄位作為主鍵？
例如居民身份證號可以保證唯一，那麼是否用身份證號當做主鍵建表？這裡並太建議，根據上面介紹的聚簇索引和二級索引的結構之後，可以看出主鍵索引越長對於輔助索引建立需要更多的空間，另外對於聚簇索引，如果索引過長會導致主鍵索引樹的高度變高，因為一個資料頁預設是16k，主鍵索引越長則一個資料頁能容納的索引則越少。身份證號是18位，用字串來存需要18個位元組，而如果使用自增的long來做主鍵，則只有8個位元組。另一個好處就是自增主鍵可以保證插入只需要插入到資料頁的隊尾，不需要插入中間，而身份證號按照順序排序有可能會插入中間位置，這樣會導致資料頁存滿，資料頁分裂等消耗。
字串應該如何建立索引？
場景一，根據郵箱登入是一個普遍場景，如果郵箱不加索引則需要權標掃描，而如果加入全量索引則需要佔用很大的空間。由於字串索引支援最左字首原則，則我們可以這樣建立索引：
alter table user add index index(email(5));
複製程式碼這裡設定email的最左前5個字元作為索引可以縮小範圍，但是如果前5個字元可能重複的資料很多，比如zhangsan@XX.com、zhangsi@XX.com、zhangwu@XX.com、zhangliu@XX.com、zhangqi@XX.com都會搜尋出來在遍歷，區別度太小，在某欄位簡歷索引的一個原則就是這個欄位的區別度，如此建立索引區別度太小。所以應該取得區別度可接受的最左字首。
select count(distinct email) as L from user;（查詢總數）
複製程式碼然後執行下列語句，來看每個字首長度索引的區別度，找一個能夠接受的長度，比如你的要求是區別度大於95%，那麼可以算一下多長的字首符合你的要求，區別度=L(n)/L。
select
count(distinct left(email,4) as L4,
count(distinct left(email,5) as L5,
count(distinct left(email,6) as L6,
count(distinct left(email,7) as L7,
from user;
複製程式碼- 場景二，還是身份證的場景，根據身份證進行等值查詢，應該如何建立索引？ 提供兩種方案：
- 因為身份證前面都是省市生日等重複較多的資訊，所以這裡可以考慮倒序儲存，並選擇一個長度，比如倒數8位作為字首索引。
select field_list from t where id_card = reverse('input_id_card_string');
複製程式碼- 第二種是用hash，在建立一個身份證hash欄位，用這個欄位作為索引。
alter table t add id_card_crc int unsigned, add index(id_card_crc);
複製程式碼查詢時候用以下語句：
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string');
複製程式碼這樣可以先快速縮小結果集的範圍，在根據結果集遍歷來查詢精確的身份證號，提高效率。
缺點：以上幾種方式都不支援範圍查詢，可以自己根據業務場景自己選擇合適的方式。
隔離級別
未提交讀(READ UNCOMMITTED)
讀未提交是指，一個事務還沒提交時，它做的變更就能被別的事務看到。
提交讀(READ COMMITTED)
讀提交是指，一個事務提交之後，它做的變更才會被其他事務看到。
可重複讀(REPEATABLE READ)
可重複讀是指，一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一致的。
序列化(SERIALIZABLE)
序列化，顧名思義是對於同一行記錄，“寫”會加“寫鎖”,"讀”會加“讀鎖”。當出現讀寫鎖衝突的時候，後訪問的事務必須等前一個事務執行完成，才能繼續執行。
- 髒讀（dirty read）
讀取到其他事物未提交的結果。
- 虛讀（non-repeatable read）
在事物中無讀到其他事物提交的UPDATE更新結果。
- 幻讀（phantom read）
在事物中無讀到其他事物提交的INSERT更新結果。
MySQL預設級別是可重複讀，Oracel預設級別是提交讀。
快照讀和當前讀
- 先看以下場景。在MySQL5.7版本下，預設隔離級別RR下，下面語句的執行結果是什麼？
CREATE TABLE `r` (
`id` int NOT NULL primary key auto_increment,
`v` int not null
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into r values (1,1);
複製程式碼- 場景一：
| 事物A | 事物B |
|---|---|
| begin; | |
| 1, select * from r; | begin; |
| update r set v=v+1 where id =1; | |
| commit; | |
| 2, select * from r; | |
| 3, select * from r for update; | |
| commit; |
其中1,2,3句id等於3的列 v的值都等於多少？
- 場景二：
先恢復數值,
update r set v = 1 where id =1;
複製程式碼| 事物A | 事物B |
|---|---|
| begin; | |
| 1, select * from r; | begin; |
| update r set v=v+1 where id = 1; | |
| commit; | |
| 2, select * from r; | |
| update r set v=v+1 where id = 1; | |
| 3, select * from r; | |
| commit; |
此時語句1、2、3查詢結果中v的值是多少？
- 快照讀
簡單的select操作，屬於快照讀，不加鎖。
select * from table where ?;
複製程式碼- 當前讀
特殊的讀操作，插入/更新/刪除操作，屬於當前讀，需要加鎖。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
複製程式碼所有以上的語句，都屬於當前讀，讀取記錄的最新版本。並且，讀取之後，還需要保證其他併發事務不能修改當前記錄，對讀取記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共享鎖)外，其他的操作，都加的是X鎖 (排它鎖)。
為什麼併發讀不需要加鎖？
MVCC(參考高效能MySQL)
這裡簡單介紹一下MVCC(多版本併發控制)，MVCC的實現是通過儲存資料在某個時間點的快照來實現的。不同儲存引擎實現的方式也不同。這裡簡單介紹一下InnoDB簡化版行為來說明MVCC是如何工作的。
InnoDB的MVCC是通過在每行記錄後面儲存的兩個隱藏列來實現的。兩個列一個儲存了行的建立時間，另個一儲存了行的過期時間。這裡其實儲存的並不是具體時間，而是系統版本號(system version number)。每新開啟一個事物，系統版本號都會自動遞增，事物開始時刻的系統版本號會作為事物的版本號，用來和查詢到的每行記錄版本作為比較。
下面看下InnoDB, REPEATABLE READ隔離級別下MVCC是如何操作的。
- SELECT
a.查詢版本號早於或等於當前事物版本的行資料。這樣可以保證讀取到的行，要麼早於該事物已經存在，要麼是本事物自己提交的。
b.行的刪除版本要麼未定義，要麼大於當前事物的版本號。這樣可以保證，當前事物讀取到的行在事物開始之前是未被刪除的。
- INSERT
為新插入的每一行資料儲存當前系統版本號作為行版本號。
- DELETE
為刪除的每一行儲存當前系統版本號作為行刪除標誌。
- UPDATE
a. 插入一行資料，儲存當前系統版本號作為行版本號。 b. 同時新增當前的系統版本號作為原資料的刪除標記。
InnoDB-鎖
共享鎖和排他鎖
- 共享鎖、讀鎖（Shared (S) Lock）
持有行記錄讀鎖的事物允許讀取該行記錄。
- 排他鎖、寫鎖（ Exclusive (X) Lock）
持有行記錄寫鎖的事物允許更新活刪除該行記錄。
表鎖
行鎖（Record Locks）
A record lock is a lock on an index record. For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.
行鎖是作用在索引記錄上的。例如， SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;會防止其他事物對於t.c1=10的增、刪、改。
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
行鎖總是鎖索引記錄的，儘管沒有建立索引，對於沒有建立索引的情況，InnoDB建立一個隱藏的聚簇索引並用該索引來實現行鎖。
間隙鎖（Gap Locks）
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
間隙鎖是作用於索引記錄之間或第一條索引記錄之前或最後一條索引記錄之後的鎖。
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.
間隙鎖不會作用於使用唯一索引去檢索唯一行記錄的情況。
為什麼會有間隙鎖？
防止幻讀。（當前讀）
- 下面這個語句加什麼鎖？已知條件MySQL5.7版本，InnoDB引擎，隔離級別RR。
delete from t where id = 10;
複製程式碼對id = 10的記錄加寫鎖 (走主鍵索引)。這個答案對嗎？
可能是對的也有可能是錯的，因為已知條件不足。缺少下列條件：
1，id是否是主鍵？
2，id列如果不是主鍵，那麼id上是否有索引？
3，id列上如果有二級索引，那麼這個索引是否是唯一索引？
4，id=10的記錄是否存在？
根據以下組合來判斷加的鎖：
- id列是主鍵，只對id = 10 的記錄加X鎖。
- id是唯一索引，並且id = 10 記錄存在，對唯一索引id的B+Tree id = 10 的記錄加行鎖，同時對聚簇索引（主鍵索引）上對應id = 10 的主鍵 b 加X鎖。
- id是唯一索引，但是id = 10 的記錄不存在，對唯一索引id的B+Tree id = 10 的空隙加就間隙鎖，防止新的符合條件的記錄插入，產生幻讀，即鎖住id索引樹(8,b)至(15,c)之間的空間。
看下面幾個例子：
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`k` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
複製程式碼insert into t values(2,2),(6,6);
複製程式碼MySQL5.7版本，InnoDB引擎，隔離級別是RR情況下，建立表t，其中id是主鍵，k是普通索引。
問題1：其中語句 1、2、3可以插入嗎？
| 事物A | 事物B | 事物C | 事物D |
|---|---|---|---|
| begin; | |||
| select * from t where k=4 for update; | |||
| 1, insert into t values (3,3); | |||
| 2, insert into t values (4,4); | |||
| 3, insert into t values (5,5); | |||
| commit; |
是不能插入的，因為k是普通索引，所以會在索引 k 的B+樹上 k=2 至 k=6 之間加上間隙鎖，防止幻讀。
問題2：其中語句 4、5、6、 7可以插入嗎？
| 事物A | 事物B | 事物C | 事物D | 事物E |
|---|---|---|---|---|
| begin; | ||||
| select * from t where k=4 for update; | ||||
| 4, insert into t values (1,2); | ||||
| 5, insert into t values (3,2); | ||||
| 6, insert into t values (5,6); | ||||
| 7, insert into t values (7,6); | ||||
| commit; |
其中語句 4, 7 是可以插入的，而語句 5, 6 是不能插入的。
根據前面的加鎖機制分析一下原因：
首先，select * from t where k=4 for update，由於 k 是普通索引，所以在InnoDB引擎下，預設隔離級別RR情況下會加間隙鎖防止新資料插入，防止幻讀。加鎖方式如下圖：
請看下圖：
-
間隙鎖到底鎖的是什麼？ 通過上面這個例子可以看出，其實間隙鎖並沒有完全鎖住k=2和k=6記錄的插入，也就是鎖住的不是具體的值，而是所有可能插入新值k=4的位置，在本例中也就是(2,2)至(6,6)之間的位置，因為索引是有序排列的，所以k=4只能插入(2,2)至(6,6)之間。
-
什麼情況下產生間隙鎖？
- where條件後用非索引列約束的時候。
- where後用非唯一索引列約束(等值搜尋、字串的最左字首 LIKE '張%' )的時候。
- where條件後用唯一索引、普通索引做範圍查詢時候( <、<=、>、>=、between and )。
- 對於多列聯合唯一索引，如果查詢沒有包含所有列的等值查詢也會產生間隙鎖。例如，聯合唯一索引column_1、cloumn_2、column_3的聯合唯一索引，如果當前讀語句是 ··· where column_1 = value_1 and cloumn_2 = value_2，則會產生間隙鎖，因為沒有包含column_3。
- 根據唯一索引delete、update 、select ··· lock in share mode 或 select ··· for update等值條件約束不存在的列的時候。