MySQL高级

一、存储引擎

InnoDB和MyISAM区别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响表空间
表空间	小	大
关注点	性能	事务
默认安装	Y	Y
用户表默认使用	N	Y
自带系统表使用	Y	N

innodb 索引 使用 B+TREE

myisam 索引使用 b-tree

innodb 主键为聚簇索引，基于聚簇索引的增删改查效率非常高。

1、InnoDB存储引擎（重要）

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况

2、MyISAM存储引擎（重要）

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。

根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5、CSV引擎

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。

CSV引擎可以作为一种数据交换的机制，非常有用。

CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

导出后可以给不会sql语句的人用excel打开并修改。

6、Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)

7、Federated引擎

Federated引擎(联合引擎)是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

就是2张表关联查询，但是2张表位于不同的服务器。此时就要用到该引擎。该引擎会在每个服务器中生成其他服务器的关联表，能对该表进行简单的关联查询，不能进行分组查询等。

二、视图

1、什么是视图

视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。

2、用来做什么

通过视图，可以展现基表的部分数据；

视图数据来自定义视图的查询中使用的表，使用视图动态生成。

基表：用来创建视图的表叫做基表

3、为什么要使用视图

因为视图的诸多优点，如下

1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。简化复杂的sql操作，使用时不必知道sql的查询细节

2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

4、创建视图

create [or replace] 
view view_name 
As select_statement 
[with|cascaded|local|check option]

参数说明：

• or replace ：不加这个的话，执行时，视图存在会报错；加上的话，视图已存在会覆盖
• view_name：视图名
• select_statement：该视图封装的查询语句
• [with [cascaded|local|] check option]：可选项，表示更新视图时要保证在视图的select * from my_view权限范围内。

案例

CREATE VIEW my_view 
AS select cat_id,name from pms_category

5、修改视图

直接使用创建视图的语句 create or replace，或者

alter view view_name 
As select_statement 
[with|cascaded|local|check option]

6、删除视图

用户可以一次删除一个或者多个视图，前提是必须有该视 图的drop权限。

drop view [if exists]  
view_name,view_name …[restrict|cascade]

7、重命名视图

rename table my_view1 to my_view2

8、视图操作

查询视图

直接把视图看成一张表去查询就好了

select * from my_view

修改视图数据

直接用update、delete、insert，但是有限制

视图的可更新性和视图中查询的定义有关系，以下类型的 视图是不能更新的。

• 包含以下关键字的sql语句：分组函数、distinct、group by 、having、union或者union all
• 常量视图
• Select中包含子查询
• join
• from一个不能更新的视图
• where子句的子查询引用了from子句中的表

一般情况下，视图只用于查询

三、存储过程

1、什么是存储过程

• MySQL 5.0 版本开始支持存储过程。

• 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

• 存储过就是数据库 SQL 语言层面的代码封装与重用。

特性

• 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

• 函数的普遍特性：模块化，封装，代码复用；

• 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

优点

1、简化应用开发人员的很多工作

2、减少数据在数据库和应用服务器之间的传输

3、提高了数据处理的效

2、存储过程创建和调用

delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
  sql语句
end 自定义的结束符合
delimiter ;

案例

-- 创建存储过程
delimiter $$
create procedure proc01()
begin
  select empno,ename from emp; 
end  $$
delimiter ;

-- 调用存储过程
call proc01(); 

修改储存过程

修改存储过程：

alter procedure 存储过程名 [charactristic…]

3、变量定义和赋值

（1）局部变量

格式

• 局部变量，用户自定义，在 begin/end 中有效，后面可选项为默认值

declare var_name type [default var_value]; 

举例：declare nickname varchar(32);

基本操作

delimiter $$
create procedure fun2()
begin
     -- 定义局部变量
    declare var_name01 varchar(20) default 'aaa';
    -- 变量赋值
    set var_name01 = 'zhangsan';  
    select var_name01;
end $$
delimiter ;

call fun2()

MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。

select col_name [...] into var_name[,...] 
from table_name wehre condition 

其中：

• col_name 参数表示查询的字段名称；

• var_name 参数是变量的名称；

• table_name 参数指表的名称；

• condition 参数指查询条件。

注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。

（2）用户变量

用户自定义，当前会话（连接）有效。类比java的成员变量

语法：@var_name

不需要提前声明，使用即声明

delimiter $$
create procedure proc04()
begin
    set @var_name01  = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01  ;  --可以看到结果

（3）系统变量

系统变量又分为全局变量与会话变量

• 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

• 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

• 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。

• 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

• 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。•系统变量又分为全局变量与会话变量

• 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。

• 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。

• 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。

• 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。

• 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。

【1】全局变量

由系统提供，在整个数据库有效。

语法：@@global.var_name

-- 查看全局变量 
show global variables; 
-- 查看某全局变量 
select @@global.auto_increment_increment; 
-- 修改全局变量的值 
set global sort_buffer_size = 40000; 
set @@global.sort_buffer_size = 40000;

【2】会话变量

由系统提供，当前会话（连接）有效

语法：@@session.var_name

-- 查看会话变量
show session variables;
-- 查看某会话变量 
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000; 
set @@session.sort_buffer_size = 50000 ;

4、传参

• 参数

类型	含义
IN	该类参数作为输入，也就是调用时传递的参数
OUT	输出，即返回值
INOUT	即可作为输出，也可作为输入

用法

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
    -- sql 语句
END;    

IN/OUT 案例

delimiter $$
CREATE PROCEDURE getUsername(IN user_id long, OUT result varchar(20))
BEGIN
    -- sql 语句
    select user_name into result from t_user where id = user_id;
END $$
delimiter ;

call getUsername(1, @result)

select @result

INOUT 案例

delimiter $$
CREATE PROCEDURE fun3(INOUT score double)
BEGIN
    set score = score * 0.5;
END $$
delimiter ;

set @score = 11;
call fun3(@score);
select @score;

5、流程控制

（1）if 判断

-- 语法
if 条件判断1 then 执行语句1
   [elseif 条件判断2 then 执行语句2] ...
   [else 执行语句3]
end if

案例

-- 输入学生的成绩，来判断成绩的级别：
/*
  score < 60 :不及格
  score >= 60  , score <80 :及格
    score >= 80 , score < 90 :良好
    score >= 90 , score <= 100 :优秀
    score > 100 :成绩错误
*/
delimiter  $$
create procedure proc_12_if(in score int)
begin
  if score < 60 
      then
          select '不及格';
  elseif  score < 80
      then
          select '及格' ;
  elseif score >= 80 and score < 90
       then 
           select '良好';
  elseif score >= 90 and score <= 100
       then 
           select '优秀';
  else
       select '成绩错误';
  end if;
end $$
delimiter  ;
call proc_12_if(120)

（2）case 判断

-- 语法一（类比java的switch）：
case 值1
    when 值2 then sql语句1
    [when 值3 then sql语句2] ...
    [else sql语句3]
end case
-- 语法二：
case
    when 条件判断1 then sql语句1
    [when 条件判断1 then sql语句2] ...
    [else sql语句3]
end case

案例

-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
  case pay_type
        when  1 
          then 
              select '微信支付' ;
        when  2 then select '支付宝支付' ;
        when  3 then select '银行卡支付';
      else select '其他方式支付';
    end case ;
end $$
delimiter ;
 
call proc14_case(2);
call proc14_case(4);

-- 语法二
delimiter  $$
create procedure proc_15_case(in score int)
begin
  case
  when score < 60 
      then
          select '不及格';
    when  score < 80
      then
          select '及格' ;
    when score >= 80 and score < 90
       then 
           select '良好';
  when score >= 90 and score <= 100
       then 
           select '优秀';
     else
       select '成绩错误';
  end case;
end $$
delimiter  ;
 
call proc_15_case(88);

（3）while 循环

循环分类：

• while

• repeat

• loop

循环控制：

• leave 类似于 break，跳出，结束当前所在的循环

• iterate类似于 continue，继续，结束本次循环，继续下一次

格式

【标签:】while 循环条件 do
    循环体;
end while【 标签】;

while

-- -------存储过程-while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        set i=i+1;
    end while label;
end $$
delimiter ;
 
call proc16_while(10);

while + leave

-- -------存储过程-while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
        if i=5 then leave label;
        end if;
        set i=i+1;
    end while label;
end $$
delimiter ;
 
call proc16_while2(10);

while+iterate

-- -------存储过程-while+iterate
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
    declare i int default 1;
    label:while i<=insertcount do
        set i=i+1;
        if i=5 then iterate label;
        end if;
        insert into user(uid,username,`password`) values(i,concat('user-',i),'123456');
    end while label;
end $$
delimiter ;
call proc16_while3(10);

（4）repeat 循环

与 while 区别

• while 是满足条件继续循环
• repeat 是满足条件退出循环

repeat 无论如何都会先执行一次逻辑，然后判断条件是否满足，满足则退出，不满足继续循环。

格式

[标签:]repeat 
 循环体;
until 条件表达式
end repeat [标签];

-- -------存储过程-循环控制-repeat 
use mysql7_procedure;
truncate table user;
 
 
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
     declare i int default 1;
     label:repeat
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         until  i  > insertCount
     end repeat label;
     select '循环结束';
end $$
delimiter ;
 
call proc18_repeat(100);

（5）loop 循环

用于实现简单循环，如果不在sql逻辑中添加退出语句（leave，iterate），则是死循环。

[标签:] loop
  循环体;
  if 条件表达式 then 
     leave [标签]; 
  end if;
end loop;

-- -------存储过程-循环控制-loop
truncate table user;
 
delimiter $$
create procedure proc19_loop(in insertCount int) 
begin
     declare i int default 1;
     label:loop
         insert into user(uid, username, password) values(i,concat('user-',i),'123456');
         set i = i + 1;
         if i > 5 
          then 
           leave label;
         end if;
     end loop label;
     select '循环结束';
end $$
delimiter ;
 
call proc19_loop(10);

6、游标

游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

用于接收多条数据的结果集

• 游标的申明应该在普通变量之后

格式

-- 声明语法
declare 游标名 cursor for select_statement
-- 打开语法
open 游标名
-- 取值语法
fetch 游标名 into var_name [, var_name] ...
-- 关闭语法
close 游标名

案例

delimiter $$
create procedure proc_cursor(in age int)
begin
 -- 定义局部变量
 declare u_user_name varchar(32);
 declare u_user_age int;
 declare u_created_time datetime;

 -- 声明游标
 declare my_cursor cursor for
  select user_name , user_age, created_time from  t_user 
    where user_age <= age ;

 -- 申明条件处理程序，当数据未发现时，关闭游标
 declare exit handler for not found close my_cursor; 

  -- 打开游标
  open my_cursor;
  -- 通过游标获取每一行数据，赋值给局部变量
  label:loop
        fetch my_cursor into u_user_name, u_user_age, u_created_time;
        insert into t_user_detail 
            value (null,u_user_name,u_user_age,u_created_time);
        select u_user_name,u_user_age,u_created_time;
    end loop label;

    -- 关闭游标
    close my_cursor;
end
 
 -- 调用存储过程
 call proc_cursor(10);

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。

在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

    ...
    -- 定义句柄，当数据未发现时将标记位设置为0
    declare continue handler for NOT FOUND set flag = 0;   
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    label:loop
        fetch my_cursor into var_empno, var_ename,var_sal;
        -- 判断标志位
        if flag = 1 then
            select var_empno, var_ename,var_sal;
        else
            leave label;
        end if;
    end loop label;

    -- 关闭游标
    close my_cursor;
    ...

四、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。

存储函数与存储过程的区别

1.存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。

2.存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；

4.存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

5.存储过程可以调用存储函数。但函数不能调用存储过程。

6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.

• 除了创建和调用的语句有所不同，其他的书写方式一样。

1、创建语句格式

在MySQL中，创建存储函数使用create function关键字，其基本形式如下：

create function func_name ([param_name type[,...]])
returns type
[characteristic ...] 
begin
    routine_body
end;

参数说明：

（1）func_name ：存储函数的名称。

（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

（3）RETURNS type：指定返回值的类型。

（4）characteristic：可选项，指定存储函数的特性。

• DETERMINISTIC：相同的输入参数总是产生相同的结果
• NO SQL：不包含 SQL 语句
• READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。

（5）routine_body：SQL代码内容。

案例

-- 导入测试数据
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
 
-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_emp;
 
delimiter $$
create function myfunc1_emp() returns int
begin
  declare cnt int default 0;
    select count(*) into  cnt from emp;
  return cnt;
end $$
delimiter ;
-- 调用存储函数
select myfunc1_emp();

有输入参数案例

-- 创建存储过程-有输入参数
drop function if exists myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
    declare out_name varchar(50);
    select ename into out_name from emp where  empno = in_empno;
    return out_name;
end $$
delimiter ;
 
select myfunc2_emp(1008);

五、触发器

1、简介

• 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。

• 在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行

• 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

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

特性

• 1、什么条件会触发：I、D、U

• 2、什么时候触发：在增删改前或者后

• 3、触发频率：针对每一行执行

• 4、触发器定义在表上，附着在表上

注意事项

• 1.MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作，以免递归循环触发

• 2.尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。

• 3. 触发器是针对每一行的；对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。

2、OLD 和 NEW

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

触发器类型	OLD 和 NEW 的使用
INSERT	NEW 表示新增的数据，没有 OLD
UPDATE	OLD 表示修改前的数据，NEW表示修改后的数据
DELETE	OLD 表示将要删除的数据

使用方法：NEW.columnName （columnName为相应数据表某一列名）

create trigger trigger_test3 after insert
on user for each row
insert into user_logs values(NULL,now(),concat('有新用户添加，信息为:',NEW.uid,NEW.username,NEW.password));
 
-- 测试
insert into user values(4,'赵六','123456');

3、创建触发器

1、创建只有一个执行语句的触发器

create trigger 触发器名 触发时机 触发事件
on 表名 for each row 
执行语句;

2、创建有多个执行语句的触发器

create trigger 触发器名 触发时机  触发事件 
on 表名 for each row
begin
     执行语句列表
end;

• 触发时机：
  • BEFORE：在语句执行之前那触发
  • AFTER：在语句执行之后触发
• 触发事件：INSERT、UPDATE、DELETE

案例

-- 如果触发器trigger_test2存在，则先删除
drop trigger if exists trigger_test2;

-- 创建触发器trigger_test2
delimiter $$
create trigger trigger_test2
after update on user  -- 触发时机：当修改user表数据时触发
for each row -- 每一行
begin
insert into user_logs values(NULL,now(), '用户修改发生了修改',new.id);
end $$
 
delimiter ;
 
-- 添加数据，触发器自动执行并添加日志代码
update user set password = '888888' where uid = 1;

4、查看、删除触发器

1、查看触发器

SHOW TRIGGERS;

2、删除触发器

-- 如果没有指定schema_name（数据库），默认为当前数据库
DROP TRIGGER [schema_name.]trigger_name;

六、锁

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

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

1、全局锁

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

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

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

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

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

数据库备份案例

-- 添加全局锁
flush tables with read lock;

-- 执行数据备份,window命令行中执行，而不是mysql
mysqldump -h主机地址 -u用户名 -p密码 数据库名 > 存储的文件名.sql 

-- 释放全局锁
unlock tables;

不加全局锁的备份方式

# 执行数据备份,window命令行中执行，而不是mysql
mysqldump --single-transaction -h主机地址 -u用户名 -p密码 数据库名 > 存储的文件名.sql 

2、表级锁

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

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

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

（1）表级锁

表锁分为两类

• 表共享读锁（read lock）（阻塞其他客户端的写，还会阻塞当前客户端的写）
• 表独占写锁（write lock）（阻塞其他客户端的读写，不会阻塞当前客户端的读写）

语法

• 加锁：

lock tables 表名 read/write

• 释放锁（或是客户端断开连接）

unlock tables

读锁不会阻塞其他客户端的读，但是会阻塞写；当前客户端的写也会阻塞。

写锁会阻塞其他客户端的读写；但不会阻塞当前客户端的读写。

可以这么理解：

• 读锁：就是当前客户端要读取安全的数据，所以禁止所有客户端写入数据，但是可以读
• 写锁：就是当前客户端要写入数据，且保证并发安全，所以同时禁止其他客户端的读写，禁止读是为了防止其他客户端读到错误数据。

（2）元数据锁

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

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。

对应SQL	锁类型	说明
lock tables xxx read / write	SHARED_READ_ONLY / SHARED_NO_READ_WRITE
select 、select … lock in share mode	SHARED_READ	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
insert 、update、delete、select … for update	SHARED_WRITE	与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥
alter table …	EXCLUSIVE	与其他的MDL都互斥

锁的兼容就是锁可以共存，而互斥则必须等其他锁释放才能加上锁。

查看元数据锁

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

（3）意向锁

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

解决的问题

当表中的某一行有行锁时，在对该表添加表锁，因为存在冲突不能直接添加，需要逐行判断是否存在行锁，效率低。

解决的方式（意向锁）

当对表中的某一行添加行锁时，还需对该表添加意向锁，此时在对该表添加表锁时，只需判断该表是否存在意向锁即可

意向锁分类

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

与其他表锁兼容情况

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

查看意向锁和行锁（mysql8.0新增）

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

3、行级锁

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

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

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

（1）行锁

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

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

	S（共享锁）	X（排他锁）
S（共享锁）	兼容	冲突
X（排他锁）	冲突	冲突

各种sql语句所加的锁

SQL	行锁类型	说明
INSERT …	排他锁	自动加锁
UPDATE …	排他锁	自动加锁
DELETE …	排他锁	自动加锁
SELECT（正常）	不加锁
SELECT … LOCK IN SHARE MODE	共享锁	在SELECT之后加LOCK IN SHARE MODE
SELECT … FOR UPDATE	排他锁	在SELECT之后加FOR UPDATE

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

1. 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

2. InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁

查看意向锁和行锁（mysql8.0新增）

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

（2）间隙锁/临键锁

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

添加间隙锁的情况（下面有案例）

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

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

三种情况的案例

id	name	age
1	zhangsan	1
5	lisi	3
19	wangwu	7
25	zhaoliu	9

1、一个连接开启事务，查询唯一索引字段（id）1和5之间的数据，但并不存在时，会在1和5之间的间隙中加上间隙锁，此时，另一个事务无法通过 insert 将数据插入到 1 和 5 之间。

3、当执行范围查询 select * from table where id > 19 lock in share mode; 时，会添加以下锁

• id为19这条记录的行锁
• id=25 的临键锁，锁 id = 25 和 19和25之间的间隙
• 25 到 正无穷大的临键锁

七、事务

1、四个特性 ACID

• 原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况

• 一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。

• 隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

• 持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

2、开启事务

事务分为隐式事务和显式事务两种。我们的DML语句（insert、update、delete）就是隐式事务。

• 隐式事务：该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；不妨思考一下，update语句修改数据时，是不是对表中数据进行改变了，它的本质其实就相当于一个事务。
• 显式事务：该事务具有明显的开启和结束标记；也是本文重点要讲的东西。使用显式事务的前提是你得先把自动提交事务的功能给禁用。禁用自动提交功能就是设置autocommit变量值为0（0:禁用 1:开启）

下面是显式事务的开启方式

#步骤一：开启事务（可选）
start transaction;
#步骤二：编写事务中的sql语句（insert、update、delete）
#这里实现一下"李二给王五转账"的事务过程
update t_account set balance = 50 where vname = "李二";
update t_account set balance = 130 where vname = "王五";
#步骤三：结束事务
commit; #提交事务
# rollback; #回滚事务:就是事务不执行，回滚到事务执行前的状态

3、并发问题

因为某一刻不可能总只有一个事务在运行，可能出现A在操作t_account表中的数据，B也同样在操t_account表，那么就会出现并发问题，对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采用必要的隔离机制，就会发生以下各种并发问题。

• 脏读：即读取到其他事务未提交的数据。事务T1将某一值修改但是还未提交，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改并提交，这就导致了T2所读取到的数据是无效的，值得注意的是，脏读一般是针对于update操作的。
• 不可重复读 ：即一个事务范围内两个相同的查询返回相同的数据行，但数据内容不一致（两次之间有其他事务修改了数据）。在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据并修改数据。那么，在第一个事务的两次读数据之间。由于另一个事务的修改，那么第一个事务两次读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
• 幻读：即一个事务范围内两个相同的查询返回的数据条数不一致（两次之间有其他事务新增或删除了数据）。事务1在A表中读取了一个字段，然后事务2又在A表中插入了一些新的数据时，事务1再读取该表时，就会发现神不知鬼不觉的多出几行了…

不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

4、隔离级别

（1）概念

四种隔离级别以及相关问题是否会出现

隔离级别	脏读	不可重复读	幻读
读未提交（read uncommitted）	是	是	是
读提交（read committed）	否	是	是
可重读（repeatable read）	否	否	是
可串行化（serializable）	否	否	否

mysql中的四种事务隔离级别如下：

• read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。

• read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更。（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）

• repeatable read（可重复读）（行锁）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）

• serializable（串行化）（表锁）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下

数据库支持

oracle 支持两种事务隔离级别：读已提交、串行化。

oracle 默认的事务隔离级别是：读已提交

mysql 的默认事务隔离级别是：可重复读

（2）查看、设置隔离级别

查看当前的事务隔离级别通过 tx_isolation 变量或者 transaction_isolation(版本8.0以上使用)；

语法：

select @@tx_isolation;

注意：在mysql8.0之后，就已经抛弃了tx_isolation变量了，而是用 transaction_isolation变量代替了。

select @@transaction_isolation;

设置隔离级别

#设置当前mysql连接的隔离级别:
set session transaction isolation level read uncommitted;
#设置数据库系统的全局的隔离级别:
set global transaction isolation level read uncommitted;

注意：当前mysql连接的隔离级别和mysql全局的隔离级别的区别是什么？

如果只设置当前的隔离级别，也就是session，那么另外一个并发的“mysqy程序”的隔离级别不会受到当前连接的影响，而是保持默认的repeatable read。

但是如果是设置全局的事务隔离级别，则整个mysql数据库（包括所有打开的mysql程序连接）的隔离级别都会随之改变，除非服务器重启，不然就不会恢复默认了。

5、保存点（回滚点）

回滚点表示的就是使事务回滚到指定回滚点

语法:

savepoint 节点名称;

注意：保存点只允许搭配rollback回滚来使用，不能和commit一起使用

#禁用自动提交事务
set autocommit = 0;
#开启事务
start transaction;
#删除id为2的记录
delete from t_stu where id = 2;
#设置保存点名为AA
savepoint AA;
#删除id为3的记录
delete from t_stu where id = 3;
#回滚到AA保存点处
rollback to AA;

可以看到id为2的数据被删除了，而id为3却还在，就是因为事务回滚到了AA处，所以id为3的那条记录被回滚掉了。