MySQL事務
事務可靠性模型ACID：
- Atomicity: 原子性，一次事務中的操作要麼成功，要麼失敗
- Consistency: 一致性，跨表，跨行，跨事務，資料庫始終保持一致狀態
- Isolation: 隔離性，保護事務不會互相干擾，包含4種隔離級別
- Durability: 永續性，事務提交後，不會丟資料。如系統崩潰，電源故障。
事務隔離級別
- 讀未提交 READ UNCOMMITTED
- 讀已提交 READ COMMITTED
- 可重複讀 REPEATABLE READ
- 可序列化 SERIALIZABLE
可以設定全域性的預設隔離級別，也可以單獨設定會話的隔離級別
- 讀未提交
很少使用，不能保證一致性。
- 問題：髒讀、幻讀、不可重複讀
- 鎖：以非鎖的方式執行
- 讀已提交
每次查詢都會設定和讀取自己的新快照
- 問題：幻讀、不可重複讀
- 鎖：鎖定索引記錄，而不鎖定記錄之間的間隙
- 可重複讀（預設隔離級別）
使用事務第一次讀取時建立的快照。
- 問題：幻讀
- 鎖：使用唯一索引的唯一查詢條件時，只鎖定查詢到的索引記錄。其他查詢條件，會鎖定掃描到的索引範圍，通過間隙鎖或臨鍵鎖來阻止其他會話在這個範圍中插入值。
- 序列化
最嚴格的級別，事務序列執行，資源消耗最大
鎖
鎖是資料庫系統區別於檔案系統的一個關鍵特性，用於管理對共享資源的併發訪問。Innodb引擎是在行級別對資料庫上鎖。
MySQL擁有4種鎖
- 共享鎖 （S Lock），允許事務讀一行資料
- 排他鎖（X Lock)，允許事務刪除或更新一行資料
為了支援多粒度的鎖定，這種鎖定允許行級上的鎖和表級上的鎖同時存在。InnoDB還支援意向鎖。意向鎖就是把鎖定的物件分為多個層次，意向鎖意味著事務希望在更細粒度上加鎖。如下圖所示，如果需要對記錄r上X鎖，那麼就需要對資料庫A、表、頁上意向鎖IX，最後對r上X鎖。如果其中任何一個部分導致等待，那麼該操作需要等待粗粒度鎖的完成。
意向鎖的設計目的是為了在一個事務中揭示下一行將被請求的鎖型別
- 意向共享鎖 （IS Lock），事務想要獲得一個表中某幾行的共享鎖
- 意向排他鎖 （IX Lock），事務想要獲得一個表中某幾行的排他鎖
相容關係圖：
| 鎖型別 | IS | IX | S | X |
|---|---|---|---|---|
| IS | 相容 | 相容 | 相容 | 不相容 |
| IX | 相容 | 相容 | 不相容 | 不相容 |
| S | 相容 | 不相容 | 相容 | 不相容 |
| X | 不相容 | 不相容 | 不相容 | 不相容 |
使用者可以通過命令檢視鎖請求的資訊
間隙鎖
會鎖定整個查詢範圍內的所有鍵值，即使這個鍵值不存在。
如：
select * from emp where empid > 1 and empid<10 for update;
即使emp只有2,4,7三條記錄，也會對1-10之間的“間隙”記錄加鎖。
SHOW ENGINE INNODB STATUS
悲觀鎖與樂觀鎖
悲觀鎖和樂觀鎖是人們定義出來的概念，你可以理解為一種思想。
悲觀鎖
悲觀鎖：就是對資料處理保持悲觀態度，總認為會衝突。所以在整個資料處理過程中，需要將資料鎖定。
悲觀鎖的實現通常通過資料庫提供的鎖機制實現，比如mysql中的排他鎖，select ... for update.
舉個例子。扣減庫存的情況下，我們需要先判斷商品庫存是否充足。
- 建表
CREATE TABLE `tb_goods_stock` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`goods_id` bigint(20) unsigned DEFAULT '0' COMMENT '商品ID',
`nums` int(11) unsigned DEFAULT '0' COMMENT '商品庫存數量',
`create_time` datetime DEFAULT NULL COMMENT '建立時間',
`modify_time` datetime DEFAULT NULL COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品庫存表';
- 開啟事務，查詢商品，並對記錄加鎖
先關閉自動提交事務 set autocommit = 0;
BEGIN;
SELECT nums FROM tb_goods_stock WHERE id=1 FOR UPDATE;
- 判斷商品數量是否大於購買數量。如果不滿足，回滾事務
- 如果滿足條件，扣庫存，提交事務
UPDATE tb_goods_stock SET nums=nums-5 WHERE id=1 AND nums>5;
COMMIT;
- 事務提交時會釋放鎖。
樂觀鎖
樂觀鎖是對資料的處理保持樂觀態度，認為資料一般不會衝突，只有提交資料更新時，才會對資料釋放衝突進行檢測。
樂觀鎖的實現不依靠資料庫提供的鎖機制，而是需要我們自己實現，實現方式一般是記錄資料版本，一種是通過版本號，一種是公故時間戳。很多持久化框架已經封裝好了樂觀鎖的實現，如hibernate。
- 建表
CREATE TABLE `tb_goods_stock` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
`goods_id` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '商品ID',
`nums` INT(11) UNSIGNED DEFAULT '0' COMMENT '商品庫存數量',
`create_time` DATETIME DEFAULT NULL COMMENT '建立時間',
`modify_time` DATETIME DEFAULT NULL COMMENT '更新時間',
`version` BIGINT(20) UNSIGNED DEFAULT '0' COMMENT '版本號',
PRIMARY KEY (`id`),
UNIQUE KEY `goods_id` (`goods_id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='商品庫存表';
- 查詢要賣的商品和版本號
BEGIN;
SELECT nums,VERSION FROM tb_goods_stock WHERE id=1;
- 判斷商品數量是否大於購買數量。如果不滿足，就回滾事務。
- 如果滿足，扣減庫存
UPDATE tb_goods_stock SET nums=nums-5,VERSION=VERSION+1 WHERE id=1 AND nums>5 AND VERSION=0;
- 判斷更新是否成功，如果成功，則提交，否則就回滾。
日誌
undo log :撤銷日誌
保證事務的原子性
用處：事務回滾、一致性讀、崩潰恢復
記錄事務回滾時所需的撤銷操作。一條insert語句，對應一條delete的undo log,一條update語句，對應一條反向update的undo log
redo log:重做日誌
確保事務的永續性，防止事務提交後未重新整理到磁碟就崩潰了
事務執行過程中寫入redo log，記錄事務對資料頁做了哪些修改
可以提升效能，先寫日誌，再刷磁碟。
日誌檔案：ib_logfile0,ib_logfile1
日誌緩衝：innodb_log_buffer_size
強刷：fsync()
MVCC 多版本併發控制
可以解決的問題：
- 使InnoDB支援一致性讀:READ COMMITTED和REPEATABLE READ。當我們在某個時間點查詢資料時，只能看到這個時間點之前的事務提交更新的結果。
- 讓查詢不被阻塞、無需等待被其他事務持有的鎖，可以增加併發效能。
快照讀：讀取的是快照資料，不加鎖的簡單Select 都屬於快照讀
當前讀：讀取的是最新資料，加鎖的select，或者增刪改都是進行當前讀。
1. 行記錄的隱藏列
- row_id :隱藏的行id，用來生成預設的聚集索引，如果建立表時沒有指定聚集索引，這時InnoDB就會用這個隱藏id來建立聚集索引。
- trx_id：操作這個資料的事務id，最後一個對資料操作的事務id
- roll_pointer：回滾指標，指向這個記錄的undo log資料
資料的快照記錄通過連結串列的結構串聯起來了，要找到歷史快照，可以通過遍歷roll_ptr的方式查詢。
2. Read View
- trx_ids 系統當前正在活躍的事務id集合
- low_limit_id Read View生成時刻系統尚未分配的下一個事務ID
- up_limit_id 活躍事務中最小的事務id
Read View是事務進行快照讀操作的時候生產的讀試圖，在該事務執行的快照讀的那一刻，會生成資料庫系統當前的一個快照，記錄並維護當前活躍事務的ID(每個事務開啟都有一個ID，這個ID是遞增的)
如果一個事務要查詢行記錄，那麼應該讀哪個快照版本呢？Read View就是解決這個問題的。Read View可以幫助我們解決可見性問題，Read View儲存了當前事務開啟時所有活躍的事務列表。換個角度可以理解為Read View儲存了不應該讓這個事務看到的其他事務Id的列表。
如果事務隔離級別是
-
讀已提交，那麼一個事務每次的select都會去查一次Read View,每次查到的Read View不同，就可能導致不可重複讀或幻讀的情況
-
可重複讀，那麼事務只在第一次select的時候獲取一次Read View，然後後面的select會複用這個Read View。
舉個例子：
當事務2對使用者表某行資料執行了快照讀，資料庫為該行資料生成一個Read View讀檢視，假設當前事務ID為2，此時還有事務1和事務3在活躍中，事務4在事務2快照讀前一刻提交更新了
update user set name='小明1' where id=1
此時undo日誌中存在如下版本鏈。
事務2會拿著trx_id欄位記錄的事務id:4去跟Read View中的up_limit_id(1)和low_limit_id(5)比較
- 判斷db_trx_id < up_limit_id，如果為true就 結束，否則繼續判斷。
- db_trx_id >= low_limit_id ，如果為true就結束，否則繼續判斷
- 判斷db_trx_id是否在活躍列表中，若不存在，符合條件，結束。
優化實戰
- 建表的時候欄位儘量小，如果是固定的，可以用char.
小貼士：int、bigint等後面寫的數字沒有實際意義。如int(8)
示例建表的欄位型別選擇：
-
儲存引擎的選擇，一般用Innodb,如果是冷資料，也沒人查了，但是又不能刪，可以用TokuDB,支援高壓縮比（1：12）
-
注意sql中的隱式轉換
-
適當增加索引，注意索引的區分度，如身份證號、手機號比較好。性別、年齡就比較差。
-
主鍵id推薦單調遞增，這樣插入的時候速度更快
-
資料寫入優化
- 通過PreparedStatement減少SQL解析
- Multiple Values減少互動。
insert into persons
(id_p, lastname , firstName, city )
values
(200,'haha' , 'deng' , 'shenzhen'),
(201,'haha2' , 'deng' , 'GD'),
(202,'haha3' , 'deng' , 'Beijing');
- 資料更新，範圍更新需要謹慎，避免鎖的範圍過大
- 模糊查詢
like 如果前面不加%，還是能走到索引的。如果查詢條件非常多，並且任意組合的那種場景，不建議再用sql來做，推薦solr、ES。
9. 連線查詢 避免笛卡爾積
連線查詢時，當連線on條件是非唯一欄位時，會出現笛卡爾積(區域性笛卡爾積)；當連線on條件是唯一欄位時，則不會出現笛卡爾積。
10. 注意索引失效
- null、not、not in、函式等
- 減少使用or，可以用union或union all(不去重，比union效能好)代替
- 必要時可以用force index強制指定索引
- 查詢sql的設計
- 考慮查詢量和查詢次數的平衡
- 避免大量資料的傳輸
- 避免使用臨時檔案排序或臨時表
- 分析類需求，可以用匯總表