存儲過程

存儲函數(shù)

存儲函數(shù)是有返回值的存儲過程，存儲函數(shù)的參數(shù)只能是in類型的。具體語法如下：

characteristic 特性

練習： 從1到n的累加

??????

create function fun1(n int)
returns int deterministic
begindeclare total int default 0;while n>0 do set total :=total + n;set n := n-1;end while;return total;
end;fun1(100);

觸發(fā)器

觸發(fā)器是與表有關的數(shù)據(jù)庫對象，指在insert/update/delete 之前或之后，觸發(fā)并執(zhí)行觸發(fā)器中定義的SQL語句集合，觸發(fā)器的這種特性可以協(xié)助應用在數(shù)據(jù)庫端確保數(shù)據(jù)的完整性，日志記錄， 數(shù)據(jù)校驗等操作。

使用別名old和 new 來引用觸發(fā)器中發(fā)生變化的記錄內容，這與其他的數(shù)據(jù)庫都是相似的?，F(xiàn)在觸發(fā)器還只支持行級觸發(fā)，不支持語句級觸發(fā)。

• 語法

? 創(chuàng)建

create? trigger trigger_name

before/after insert/update/delete

on tbl_name for each row? ?--行級觸發(fā)器

begin?

? ?trigger_stmt;

end;

? 查看

show triggers;

? 刪除

drop trigger [ schema_name.]trigger_name;?

-- 如果沒有指定schema_name，默認為當前數(shù)據(jù)庫。

-- 觸發(fā)器
-- 需求：通過觸發(fā)器記錄user表的數(shù)據(jù)變更日志（user_logs)，包含增加，修改，刪除；-- 準備工作：日志表 user_logscreate table user_logs(id int(11) not null auto_increment,operation varchar(20) not null comment '操作類型，insert/update/delete',operate_time datetime not null comment '操作時間',operate_id int(11) not null comment'操作的id',opetate_params varchar(500) comment'操作參數(shù)',primary key(`id`)
)engine=innodb default charset=utf8; -- 插入數(shù)據(jù)觸發(fā)器
create trigger tb_user_insert_triggerafter insert on tb_user for each row
begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,'insert',now(),new.id,concat('插入的數(shù)據(jù)內容為：id=',new.id,'name=',new.name,',phone=',NEW.phone,',email=',NEW.email,',profession=',NEW.profession));
end;-- 查看
show triggers;-- 刪除
drop trigger tb_user_insert_trigger;-- 插入數(shù)據(jù)到tb_userinsert into tb_user(id,name,phone,email,profession,age,gender,status,createtime)values();-- 修改數(shù)據(jù)觸發(fā)器create trigger tb_user_update_triggerafter update on tb_user for each row
begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,'update',now(),new.id,concat('更新之前的數(shù)據(jù)：id=',old.id,'name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession,'|更新之后的數(shù)據(jù)：id=',new.id,'name=',new.name,',phone=',NEW.phone,',email=',NEW.email,',profession=',NEW.profession));end;-- 查看觸發(fā)器
show triggers;update tb_user set  age=20 where id =23;-- 刪除觸發(fā)器create trigger tb_user_delete_triggerafter delete on tb_user for each row
begin insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES(null,'delete',now(),old.id,concat('更新之前的數(shù)據(jù)：id=',old.id,'name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession);
end;show triggers;

鎖

• 概述
• 全局鎖
• 表級鎖
• 行級鎖

概述

? 鎖是計算機協(xié)調多個進程或線程并發(fā)訪問某一資源的機制。在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（? ? ? ?CPU、RAM、I/O ）的爭用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。從這個角度來說，鎖對數(shù)據(jù)庫而言顯得尤其重要，也更加復雜。

磁盤：I/O

MySQL中的鎖，按照鎖的粒度分，分為以下三類：

1. 全局鎖：鎖定數(shù)據(jù)庫中的所有表
2. 表級鎖：每次操作鎖住整張表
3. 行級鎖：每次操作鎖住對應的行數(shù)據(jù)

全局鎖

• 介紹

全局鎖就是對整個數(shù)據(jù)庫實例加鎖，加鎖后整個實例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務提交語句都將被阻塞。

其典型的使用場景是做全庫的邏輯備份，對所有表進行鎖定，從而獲得一致性視圖，保證數(shù)據(jù)的完整性。

mysqldump: 備份

加全局鎖： flush tables with read lock;

數(shù)據(jù)備份：mysqldump -uroot -p1234? itcast > itcast.sql（不要在mysql里執(zhí)行，要在windows里執(zhí)行，mysqldump不是sql命令）

解鎖：unlock tables;

• 特點

數(shù)據(jù)庫中加全局鎖，是一個比較重的操作，存在以下問題：

1. 如果在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務基本上就得停擺。
2. 如果在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進制日志（binlog），會導致主從延遲。

在InooDB引擎中，我們可以在備份時加上參數(shù)? --single-transaction 參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。

mysqldump? --single-transaction -uroot -p123456 itcast > itcast.sql

表級鎖

• 介紹

表級鎖，每次操作鎖住整張表。鎖定力度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。應用在MyISAM、InnoDB、BDB等存儲引擎中。

對于表級鎖，主要分為以下三類：

1. 表鎖
2. 元數(shù)據(jù)鎖（meta data lock，MDL）
3. 意向鎖

• 表鎖

對于表鎖，分為兩類

1. 表共享讀鎖（read lock）
2. 表獨占寫鎖（write lock）

語法：

1. 加鎖：lock tables 表名... read/write.
2. 釋放鎖：unlock tables / 客戶端斷開連接。

加讀鎖：本客戶端只能讀 不能寫，其他客戶端也是

加寫鎖：本客戶端讀寫都行，其他客戶端讀寫都不可以

• 元數(shù)據(jù)鎖（menta data lock, MDL)

MDL加鎖過程是系統(tǒng)自動控制，無需顯示使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務的時候，不可以對元數(shù)據(jù)進行寫入操作。為了避免DML與DDL沖突，保證讀寫的正確性。

• 意向鎖

為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。

1. 意向共享鎖（IS）：由語句select ... locak in share mode 添加。??
2. 意向排它鎖（IX）：由insert 、update、delete、select ... for update 添加。

1. 意向共享鎖（IS）：與表鎖共享鎖（read）兼容，與表鎖排它鎖（write）互斥。
2. 意向排它鎖（IX）：與表鎖共享鎖（read) 及排它鎖（write）都互斥，意向鎖之間不會互斥。

意向鎖 主要解決的問題就是在InnoDB引擎中表鎖和行鎖的沖突問題。

行級鎖

• 介紹

行級鎖，每次操作鎖住對應的行數(shù)據(jù)。鎖定粒度最小，發(fā)生鎖沖突的效率最低，并發(fā)度最高，應用在InnoDB存儲引擎中。

InnoDB的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現(xiàn)的，而不是對記錄家的鎖。對于行級鎖，主要分為以下三類：

1. 行鎖（Record Lock）：鎖定單個記錄的鎖，防止其他事務對此進行update和delete。在RC\RR隔離級別下都支持。
2. 間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行Insert，產(chǎn)生幻讀。在RR隔離級別下都支持。
3. 臨鍵鎖（Next-Key Lock): 行鎖和間隙鎖組合，同時鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap。在RR隔離級別下支持。

• 行鎖

InnoDB實現(xiàn)了以下兩種類型的行鎖：

1. 共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數(shù)據(jù)集的排它鎖。
2. 排它鎖（X）：允許獲取排它鎖的事務更新數(shù)據(jù)，阻止其他事務獲得相同數(shù)據(jù)集的共享鎖和排它鎖。

• 行鎖-演示

默認情況下，InnoDB在 REPEATABLE READ 事務隔離級別運行，InnoDB使用next-key 鎖進行搜索和索引掃描，以防止幻讀。

1. 針對唯一索引進行檢索時，對已存在的記錄進行等值匹配時，將會自動優(yōu)化為行鎖。
2. InnoDB的行鎖是針對于索引家的鎖，不通過索引條件檢索數(shù)據(jù)，那么InnoDB將對表中所有記錄加鎖，此時就會升級為表鎖。

• 間隙鎖/臨鍵鎖-演示

默認情況下，InnoDB在 REPEATABLE READ 事務隔離級別運行，InnoDB使用 next-key 鎖進行搜索和索引掃描，以防止幻讀。

1. 索引上的等值查詢（唯一索引），給不存在的記錄加鎖時，優(yōu)化為間隙鎖。
2. 索引上的等值查詢（普通索引），向右遍歷時最后一個值不滿足查詢需求時，next-key lock退化為間隙鎖。
3. 索引上的范圍查詢（唯一索引）--會訪問到不滿足條件的第一個值為止。

注意：間隙鎖唯一目的是防止其他事務插入間隙，間隙鎖可以共存，一個事務采用的間隙鎖不會阻止另一個事務在同一間隙上采用間隙鎖。

InnoDB引擎

邏輯存儲結構

表空間（ibd文件），一個mysql實例可以對應多個表空間，用于存儲記錄、索引等數(shù)據(jù)。

段，分為數(shù)據(jù)段（Leaf node segment）、索引段（Non-leaf node segment)、回滾段（Rollback segment)，InnoDB是索引組織表，數(shù)據(jù)段就是B+樹的葉子節(jié)點，索引段即為B+樹的非葉子節(jié)點。段用來管理多個Extent（區(qū)）。

區(qū)，表空間的單元結構，每個區(qū)的大小為1M。默認情況下，InnoDB存儲引擎頁大小為16k，即一個區(qū)中一共有64個連續(xù)的頁。

頁，是InnoDB存儲引擎磁盤管理的最小單元，每個頁的大小默認為16KB，為了保證頁的連續(xù)性，InnoDB存儲引擎每次從磁盤申請4-5個區(qū)。

行，InnoDB存儲引擎數(shù)據(jù)是按行進行存放的。

Trx_id：每次對某條記錄進行改動時，都會把對應的事務id賦值給trx_id隱藏列。

Roll_pointer：每次對某條記錄進行改動時，都會把舊的版本寫入到undo日志中，然后這個隱藏列就相當于一個指針，可以通過它來找到該記錄修改前的信息。

架構

MySQL5.5版本開始，默認使用InnoDB存儲引擎，它擅長事務處理，具有崩潰恢復特性，在日常開發(fā)中使用非常廣泛。下面是InnoDB架構圖，左側為內存結構，右側為磁盤結構。

架構-內存架構

Buffer Pool ： 緩沖池是主內存中的一個區(qū)域，里面可以緩存磁盤上經(jīng)常操作的真是數(shù)據(jù)，在執(zhí)行增刪改查操作時，先操作緩沖池中的數(shù)據(jù)（若緩沖池沒有數(shù)據(jù)，則從磁盤加載并緩存），然后再以一定頻率刷到磁盤，從而減少磁盤IO，加快處理速度。

緩沖池以page為單位，底層采用鏈表數(shù)據(jù)結構管理Page，根據(jù)狀態(tài)，將Page分為三種類型：

• free page:? 空閑page，未被使用。
• clean page：被使用page，數(shù)據(jù)沒有被修改過。
• dirty page：臟頁，被使page，數(shù)據(jù)被修改過，頁中數(shù)據(jù)與磁盤的數(shù)據(jù)產(chǎn)生了不一致。

Change Buffer ：更改緩沖區(qū)（針對非唯一耳機索引頁），在執(zhí)行DML語句時，如果這些數(shù)據(jù)Page沒有在Buffer Pool中，不會直接操作磁盤，而會將數(shù)據(jù)變更存在更改緩沖區(qū)（change Buffer中，在未來數(shù)據(jù)被讀取時，再將數(shù)據(jù)合并恢復到Buffer Pool中，再將合并后的數(shù)據(jù)刷新到磁盤中。

Change Buffer的意義時什么？

與聚集索引不同，二級索引通常是非唯一的，并且以相對隨機的順序插入二級索引。同樣，刪除和更新可能會影響索引樹中不相鄰的二級索引頁，如果每一次都操作磁盤，會造成大量的磁盤IO。有了ChangeBuffer之后，我們可以在緩沖池中進行合并處理，減少磁盤IO。

Adaptive Hash Index：自適應hash索引，用于優(yōu)化對Buffer Pool數(shù)據(jù)的查詢，InnoDB存儲引擎會監(jiān)控對表上各索引頁的查詢，如果觀察到hash索引可以提升速度，則建立hash索引，稱之為自適應hash索引。

自適應哈希索引，無需人工干預，是系統(tǒng)根據(jù)情況自動完成。

參數(shù)：adaptive_hash_index

Log Buffer：日志緩沖區(qū)，用來保存要寫入到磁盤中的log日志數(shù)據(jù)（redo log、undo log），默認大小為16MB，日志緩沖區(qū)的日志會定期刷新到磁盤中，如果需要更新、插入或刪除許多行的事務，增加日志緩沖區(qū)的大小可以節(jié)省磁盤I/O。

參數(shù)：

innodb_log_buffer_size: 緩沖區(qū)大小

innodb_flush_log_at_trx_commit: 日志刷新到磁盤時機。

1：日志在每次事務提交時寫入并刷新到磁盤。

0：每秒將日志寫入并刷新到磁盤一次。

2：日志在每次事務提交后寫入，并每秒刷新到磁盤一次。

架構-磁盤結構

架構-后臺線程

1.Master Thread

核心后臺線程，負責調度其他線程，還負責將緩沖池中的數(shù)據(jù)異步刷新到磁盤中，保持數(shù)據(jù)的一致性；還包括臟頁的刷新、合并插入緩存、undo頁的回收。

2.IO Thread

在InnoDB存儲引擎中大量使用了AIO來處理IO請求，這樣可以極大地提高數(shù)據(jù)庫的性能，而IOThread主要負責這些IO請求的回調。

3.Purge Thread

主要用于回收事務已經(jīng)提交了的undo log，在事務提交之后，undo log可能不用，就用它來回收。

4.Page Cleaner Thread

協(xié)助 Master Thread 刷新臟頁到磁盤的線程，它可以減輕Master Thread的工作壓力，減少阻塞。

事務原理

事務 是一組操作的集合，它是一個不可分割的工作單位，事務會把所有的操作作為一個整體一起向系統(tǒng)提交或撤銷操作請求，即這些操作要么同時成功，要么同時失效。

特性

• 原子性（Atomicity）：事務是不可分割的最小操作單元，要么全部成功，要么全部失敗。
• 一致性（Consistency）：事務完成時，必須使所有的數(shù)據(jù)都保持一致狀態(tài)。
• 隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供的隔離機制，保證事務不受外部并發(fā)操作影響的獨立環(huán)境下運行。
• 持久性（Durability）：事務一旦提交或者回滾，它對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久的 。

• redo log? ?持久性

重做日志，記錄的是事務提交時數(shù)據(jù)頁的物理修改，是用來實現(xiàn)事務的持久性。

該日志文件由兩部分組成：重做日志緩沖（redo log buffer）以及重做日志文件（redo log file)，前者時在內存中，后者在磁盤中。當事務提交之后會把所有修改信息都存到該日志文件中，用于在刷新臟頁到磁盤，發(fā)生錯誤時，進行數(shù)據(jù)恢復使用。

---

• undo log? 原子性

回滾日志，用于記錄數(shù)據(jù)被修改前的信息，作用包含兩個：提供回滾和MVCC（多版本并發(fā)控制）。

undo log和redo log記錄物理日志不一樣，它是邏輯日志?？梢哉J為當delete一條記錄時，undo log中會記錄一條對應的insert記錄，反之亦然，當update一條記錄時，它記錄一條對應相反的update記錄。當執(zhí)行rollback時，就可以從undo log中的邏輯記錄讀取到相應的內容并進行回滾。

Undo log銷毀：undo log在事務執(zhí)行時產(chǎn)生，事務提交時，并不會立即刪除undo log，因為這些日志可能還用于MVCC。

Undo log存儲：undo log采用段的方式進行管理和記錄，存放在前面介紹的rollback segment回滾段中，內部包含1024個undo log segment。

MVCC

MVCC-基本概念

• 當前讀

讀取的是記錄的最新版本，讀取時還要保證其他并發(fā)事務不能修改當前記錄，會對讀取的記錄進行加鎖。對于我們日常的操作，如：select ... lock in share mode(共享鎖)，select ... for update、update、insert、delete(排它鎖）都是一種當前讀。

• 快照讀

簡單的select（不加鎖）就是快照讀，快照讀，讀取的是記錄數(shù)據(jù)的可見版本，有可能是歷史數(shù)據(jù)，不加鎖，是非阻塞讀。

· Read Committed：每次select，都生成一個快照讀。

· Repeatable Read；開啟事務后第一個select語句才是快照讀的地方。

· Serializable：快照讀會退化為當前讀。

• MVCC

全稱 Multi-Version Concurrency Control，多版本并發(fā)控制。指維護一個數(shù)據(jù)的多個版本，使得讀寫操作沒有沖突，快照讀為MySQL實現(xiàn)MVCC提供了一個非阻塞讀功能。MVCC的具體實現(xiàn)，還需要依賴于數(shù)據(jù)庫記錄中三個隱式字段、undo log日志、readView。

MVCC-實現(xiàn)原理