10.22 MySQL

存储过程

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是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);

触发器

触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合，触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录， 数据校验等操作。

使用别名old和 new 来引用触发器中发生变化的记录内容，这与其他的数据库都是相似的。现在触发器还只支持行级触发，不支持语句级触发。

• 语法

  创建

create  trigger trigger_name

before/after insert/update/delete

on tbl_name for each row   --行级触发器

begin 

   trigger_stmt;

end;

  查看

show triggers;

  删除

drop trigger [ schema_name.]trigger_name; 

-- 如果没有指定schema_name，默认为当前数据库。

-- 触发器
-- 需求：通过触发器记录user表的数据变更日志（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'操作参数',primary key(`id`)
)engine=innodb default charset=utf8; -- 插入数据触发器
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('插入的数据内容为：id=',new.id,'name=',new.name,',phone=',NEW.phone,',email=',NEW.email,',profession=',NEW.profession));
end;-- 查看
show triggers;-- 删除
drop trigger tb_user_insert_trigger;-- 插入数据到tb_userinsert into tb_user(id,name,phone,email,profession,age,gender,status,createtime)values();-- 修改数据触发器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('更新之前的数据：id=',old.id,'name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession,'|更新之后的数据：id=',new.id,'name=',new.name,',phone=',NEW.phone,',email=',NEW.email,',profession=',NEW.profession));end;-- 查看触发器
show triggers;update tb_user set  age=20 where id =23;-- 删除触发器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('更新之前的数据：id=',old.id,'name=',old.name,',phone=',old.phone,',email=',old.email,',profession=',old.profession);
end;show triggers;

锁

• 概述
• 全局锁
• 表级锁
• 行级锁

概述

  锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（       CPU、RAM、I/O ）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

磁盘：I/O

MySQL中的锁，按照锁的粒度分，分为以下三类：

1. 全局锁：锁定数据库中的所有表
2. 表级锁：每次操作锁住整张表
3. 行级锁：每次操作锁住对应的行数据

全局锁

• 介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有表进行锁定，从而获得一致性视图，保证数据的完整性。

mysqldump: 备份

加全局锁： flush tables with read lock;

数据备份：mysqldump -uroot -p1234  itcast > itcast.sql（不要在mysql里执行，要在windows里执行，mysqldump不是sql命令）

解锁：unlock tables;

• 特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

1. 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
2. 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InooDB引擎中，我们可以在备份时加上参数  --single-transaction 参数来完成不加锁的一致性数据备份。

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

表级锁

• 介绍

表级锁，每次操作锁住整张表。锁定力度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

1. 表锁
2. 元数据锁（meta data lock，MDL）
3. 意向锁

• 表锁

对于表锁，分为两类

1. 表共享读锁（read lock）
2. 表独占写锁（write lock）

语法：

1. 加锁：lock tables 表名... read/write.
2. 释放锁：unlock tables / 客户端断开连接。

加读锁：本客户端只能读 不能写，其他客户端也是

加写锁：本客户端读写都行，其他客户端读写都不可以

• 元数据锁（menta data lock, MDL)

MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

• 意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

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

1. 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
2. 意向排它锁（IX）：与表锁共享锁（read) 及排它锁（write）都互斥，意向锁之间不会互斥。

意向锁 主要解决的问题就是在InnoDB引擎中表锁和行锁的冲突问题。

行级锁

• 介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的效率最低，并发度最高，应用在InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录家的锁。对于行级锁，主要分为以下三类：

1. 行锁（Record Lock）：锁定单个记录的锁，防止其他事务对此进行update和delete。在RC\RR隔离级别下都支持。
2. 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行Insert，产生幻读。在RR隔离级别下都支持。
3. 临键锁（Next-Key Lock): 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

• 行锁

InnoDB实现了以下两种类型的行锁：

1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
2. 排它锁（X）：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁。

• 行锁-演示

默认情况下，InnoDB在 REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key 锁进行搜索和索引扫描，以防止幻读。

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
2. InnoDB的行锁是针对于索引家的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时就会升级为表锁。

• 间隙锁/临键锁-演示

默认情况下，InnoDB在 REPEATABLE READ 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

1. 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。
2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
3. 索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止。

注意：间隙锁唯一目的是防止其他事务插入间隙，间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

InnoDB引擎

逻辑存储结构

表空间（ibd文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment)、回滚段（Rollback segment)，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

区，表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16k，即一个区中一共有64个连续的页。

页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB，为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

行，InnoDB存储引擎数据是按行进行存放的。

Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。

Roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

架构-内存架构

Buffer Pool ： 缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真是数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以page为单位，底层采用链表数据结构管理Page，根据状态，将Page分为三种类型：

• free page:  空闲page，未被使用。
• clean page：被使用page，数据没有被修改过。
• dirty page：脏页，被使page，数据被修改过，页中数据与磁盘的数据产生了不一致。

Change Buffer ：更改缓冲区（针对非唯一耳机索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区（change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中。

Change Buffer的意义时什么？

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以在缓冲池中进行合并处理，减少磁盘IO。

Adaptive Hash Index：自适应hash索引，用于优化对Buffer Pool数据的查询，InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

自适应哈希索引，无需人工干预，是系统根据情况自动完成。

参数：adaptive_hash_index

Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中，如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。

参数：

innodb_log_buffer_size: 缓冲区大小

innodb_flush_log_at_trx_commit: 日志刷新到磁盘时机。

1：日志在每次事务提交时写入并刷新到磁盘。

0：每秒将日志写入并刷新到磁盘一次。

2：日志在每次事务提交后写入，并每秒刷新到磁盘一次。

架构-磁盘结构

架构-后台线程

1.Master Thread

核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性；还包括脏页的刷新、合并插入缓存、undo页的回收。

2.IO Thread

在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大地提高数据库的性能，而IOThread主要负责这些IO请求的回调。

3.Purge Thread

主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用，就用它来回收。

4.Page Cleaner Thread

协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞。

事务原理

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失效。

特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或者回滚，它对数据库中的数据的改变就是永久的 。

• redo log   持久性

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file)，前者时在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

---

• undo log  原子性

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

MVCC

MVCC-基本概念

• 当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select ... lock in share mode(共享锁)，select ... for update、update、insert、delete(排它锁）都是一种当前读。

• 快照读

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

· Read Committed：每次select，都生成一个快照读。

· Repeatable Read；开启事务后第一个select语句才是快照读的地方。

· Serializable：快照读会退化为当前读。

• MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中三个隐式字段、undo log日志、readView。

MVCC-实现原理