MySQL进阶知识

1 存储引擎

1.1 MySQL体系结构

1.2 存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，同一个库的多个表可以采用不同的存储引擎，所以存储引擎也经常称为表类型。创建表时可以指定存储引擎，如果不指定默认存储引擎是InnoDB。

查询建表语句：

show create table tb_brand;

CREATE TABLE `tb_brand` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(20) DEFAULT NULL,
  `company_name` varchar(20) DEFAULT NULL,
  `ordered` int DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `status` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
//主键是自增长的，下一条记录ID是8

在创建表时指定存储引擎：

ENGINE=XXX，例如ENGINE=InnoDB

查看当前数据库支持的存储引擎：

show engines;

1.2.1 InnoDB

innodb_file_per_table开关，控制着每张innodb表都创建一个表空间文件，还是所有的innodb表共用一个表空间文件。查看系统变量:

1.2.2 InnoDB的逻辑存储结构

Row（一行）中包含，最后一次操作事务的ID（Trx id）、指针、各个字段

page是磁盘操作的最小单元。一个Extent大小固定是1M，一个page大小固定是16K，一个Extent包含64个page

1.2.3 MyISAM和Memory

Memory数据存放到内存当中，访问速度很快

Memory在磁盘上只有.sdi文件

Memory支持hash索引

绝大多数场景都是用InnodDB引擎。MyISAM可使用mangodb替代，Memory可使用redis替代。

2 索引

2.1 索引概述

没有索引的话，查询数据时需要全表扫描，查询效率极低；

有索引的话，需要建立一种数据结构维护数据和数据所在的内存地址关系，查询效率可大大提高；

磁盘很便宜，索引占点磁盘空间也没事

实际应用上，查询比例远远多余插入、更新、删除比例，因此降低更新表的速度这个劣势可以不考虑。

2.2 索引结构

2.2.1 二叉树和红黑树

二叉树缺点：

顺序插入时，会形成一个链表，查询性能大大降低，如图2

每个节点只有两个叶子节点，在数据量很大时，二叉树的层级会很深，检索速度很慢

二叉树的1缺点，可通过红黑树解决，如图3；但是红黑树也是二叉树，无法解决缺点2

2.2.2 Btree

2.2.3 B+tree索引

想较于B树，B+树有如下特点：

1、所有的元素都出现在叶子节点，叶子节点用来存放数据；非叶子节点只起到索引的作用

2、叶子节点形成了一个单向链表

2.2.4 Hash索引

2.2.5 InnoDB为什么选择B+树作为索引

二叉树缺点：

1、顺序插入时，会形成一个链表，查询性能大大降低，如图2

2、每个节点只有两个叶子节点，在数据量很大时，二叉树的层级会很深，检索速度很慢

红黑树缺点：

1、二叉树的1缺点，可通过红黑树解决；但是红黑树也是二叉树，无法解决缺点2

B树缺点：

1、B树叶子节点或者非叶子节点都存储数据和指针，B+树只有叶子节点存储数据，非叶子节点存储指针。

2、每页大小只有16K，相较于B树，B+树一页当中能存储更多的Key和指针，相同数据量的话B+树层级会少一些，检索速度也会更快。

Hash索引缺点

1、Hash索引只能等值匹配，=、in；不支持范围匹配，beteen and、 >、<。B+树支持范围匹配。

2、Hash所以不支持排序操作。B+树支持排序操作；

2.3 索引分类

2.3.1 聚集索引和二级索引

在一个表中，聚集索引必须得有，并且只能有一个；聚集索引叶子节点下面挂的这一行的行数据，比如5下面挂的就是id是5的这一行数据。

如果针对name这一列建立索引，建立的索引是二级索引。二级索引叶子节点挂的是这一行数据的id值。

2.3.1 回表查询

举例select * from user where name=‘Arm’，MySQL的查询流程如下：

1、从对name列建立的二级索引中查找，Lee->Geek->Arm，找到Arm节点；

2、取出Arm节点下挂的id值10，返回聚集索引继续查找；15,30->10,20->10，找到id值为10的节点；

3、取出10这个节点下面挂的行数据返回

先走二级索引找到对应的主键值，再到聚集索引中根据主键值拿到行数据

答：1高于2。1扫描一遍聚集索引即可；2需要先扫描name字段的二级索引，然后再回表查询聚集索引，两遍扫描。

2.4 索引语法

2.4.1 创建索引

UNIQUE：唯一索引

FULLTEXT：全文索引

什么都不加：常规索引

table_name：表名

index_col_name:字段名，一个索引可以关联多个字段，用,隔开。如果一个索引只关联一个字段，称为单列索引，关联多个字段的索引叫作联合索引。

为哪一张表的哪一/多个字段创建索引。

2.4.2 查看索引

查看指定表中的所有的索引。

2.4.3 删除索引

删除哪一张表中的哪一个索引。

2.4.4 索引操作案例

1、常规索引

create index name_index on tb_user(name);

2、唯一索引

create unique index phone_index on tb_user(phone);

3 create index union_index on tb_user(profession,age,status)

4 唯一索引

create unique index email_index on tb_user(email);

2.5 SQL性能分析

2.5.1 查看SQL执行频率

show global status like 'Com_______';

2.5.2 慢查询日志

查询慢查询日志是否开启：

show variables like 'slow_query_log';

2.5.3 profile查询SQL耗时

select @@have_profiling;查看当前数据库是否支持profile操作

select @@profiling;查看当前数据库profiling是否开启

set profiling=1/0;开启/关闭profiling

2.5.4 explain查看SQL语句执行计划

2.6 使用索引

2.6.1 验证索引效率

tb_brand库中总共50万条数据，给company_name建立索引后，查询时间从0.23s缩短到0.00s

注意创建索引需要建立B+Tree数据结构，这个过程耗费了1.04s。

2.6.2 联合索引

给brand_name、company_name、ordered建立联合索引index_union。brand_name是第一个字段，company_name是第二个字段，ordered是第三个字段。

如果要想用到index_union这个联合索引，select时brand_name这个字段必须存在（放置位置无所谓）。如果select时中间跳过了某一列（比如company_name），后面的索引(ordered)会失效。

case1:

三个字段的索引全部用上了。

可能用到的索引、实际用到的索引：index_union

索引长度：171

case2:

用到了brand_name、company_name两个字段的索引，ordered字段的索引长度=171-166=5；

可能用到的索引、实际用到的索引：index_union

索引长度：166

case3:

用到了brand_name字段的索引，company_name字段的索引长度=166-83=83；

可能用到的索引、实际用到的索引：index_union

索引长度：83

case4:

并未用到index_union索引，因为SQL语句中brand_name字段不存在

可能用到的索引、实际用到的索引：null

type:all，全表扫描

case5:

只用到了brand_name字段的索引，因为跳过了company_name，所以ordered字段的索引失效了

可能用到的索引、实际用到的索引：index_union

索引长度：83

case6:

效果完全和case1相同，三个字段的索引都用到了。注意最左前缀法则说的是，select语句中brand_name这个字段必须存在，对放置位置没有要求。

2.6.3 索引失效情况

1、不要在索引列上进行运算操作，否则索引将失效

2、字符串类型字段使用时，不加引号，索引将失效

3、like模糊匹配。如果仅仅是尾部模糊匹配，索引不会失效。只要有头部模糊匹配（不论尾部是否模糊匹配），索引就会失效，type是全表扫描；

4、or连接条件。用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么前面的列索引会失效。只有or前、or后两个字段都存在索引，两个索引才会用到。

brand_name有索引，company_name没有索引时，type为全表扫描。

Id和brand_name都有索引，因此下面的SQL用到了两个索引。

5、数据分布影响。如果MySQL评估使用索引比全表扫描更慢，则不使用索引。

2.6.4 SQL提示-选择索引

如果某个字段上存在多个索引，可以通过SQL提示告诉数据库使用哪个索引，如果不指定使用哪个索引，数据库会自己选择一个。

use index(index_name)：建议数据库用哪个索引，MySQL可以选择接受 or不接受。

Ignore index(index_name):告诉数据库不要用哪个索引

force index(index_name):强制数据库必须使用哪个索引

如下所示，brand_name存在一个单独的索引，brand_name和ordered共建了一个联合索引；

数据库自己选择了index_brand_name的这个单独索引：

建议数据库使用联合索引，并且MySQL接受了建议：

2.6.5 覆盖索引以及回表查询

case1：select * from tb_user where id=2

使用聚集索引（主键索引），只扫描一遍聚集索引的B+树。

id为5->id为2->返回叶子节点2下面挂的row行数据(*就是row行数据)

case2：select id,name from tb_user where name=‘Arm’

使用二级索引，name为Lee->name为Arm->返回Arm和Arm下面挂的id 2，扫描一遍二级索引即可返回Arm和2。二级索引的叶子节点下面挂的就是ID。

case3：select id,name,gender from tb_user where name=‘Arm’

使用二级索引和聚集索引，name为Lee->name为Arm->找到Arm节点下挂的id 2->回表查询->id为5->id为2->找到叶子节点2下面挂的row行数据->找到gender。扫描两遍，效率低。

思考题1：

select * from tb_user where id=2;//扫描一遍聚集索引，即可返回行数据

select * from tbz_user where name=‘Arm’//先遍历二级索引，找到id为2；再回表查询聚集索引，找到id为2的节点下面挂的行数据。效率低。

思考题2：

select id,username,password from tb_user where username=‘xiaoming’

1、给username单独建立索引不可取，查询password还需要回表查询聚集索引（主键索引），效率低；

2、可以给username和password建立联合索引，二级索引叶子节点下面挂的就是id，所以扫描一遍二级索引就能拿到三个字段的值

2.6.6 前缀索引

前缀索引用来解决长字符或者大文本在创建索引时浪费大量磁盘IO的情况。

前缀索引：提取字符串的前面n个字符构建索引

选择性：不重复的字段数据总数/数据表的记录总数，最大为1

查看brand_name字段的选择性：

brand_name字段中有重复的数据，因此选择性到不了1

选择brand_name的前n个字节，再查看选择性：

截取brand_name的前15，10，8个字符，去重后，计算相关性，没有变化，都是0.9677，因此选择截取brand_name的前8个字符创建前缀索引,降低索引占据的内存空间。

create index index_brand_name_8 on tb_brand(brand_name(8));

sub_part:8，截取前8个字符创建的索引。

前缀索引查询流程如下，以email截取前5个字符创建索引为例：

2.6.7 索引设计原则

3 SQL优化

select语句可以通过第2章中建立合适的索引提高查询效率。

3.1 插入数据优化

插入数据可以通过insert语句或者MySQL的load指令来实现。

3.1.1 insert优化

插入多条数据的优化点如下：

批量插入

一条insert语句插入多条记录，而不是每条insert语句只插入一条记录；

insert into tb_user values(1,'xiaoming'),(2,'xiaohong'),(3,'xiaoli');//适合插入几百至一千条记录

如果数据量很大，比如百万，可以执行多次批量插入，每次批量插入1000条；

手动提交事务

MySQL执行SQL语句默认是自动提交事务的，多条insert语句就意味着多次开启事务-提交事务，可优化为手动提交事务，降低多次开启/提交事务的耗时。

3 主键顺序插入

主键顺序插入效率要高于主键乱序插入

3.1.2 load指令大批量插入

如果想一次性加载500万/1000万条数据insert语句性能就很低了，使用MySQL提供的load指令

3.2 主键优化

1、尽量降低主键的长度。

二级索引的叶子节点下面挂的就是主键的值，如果二级索引过多，主键又很长，会占用大量的磁盘空间，搜索时磁盘IO也会增加。

2、掺入数据时，尽量选择顺序插入，选择使用auto_increment自增主键，逐渐乱序插入可能出现页分裂现象。

3、UUID和身份证号是无序的，并且长度过长，不适宜做主键

3.2.1 InnoDB引擎中数据的组织方式

表中的数据是根据主键顺序存放的，叶子节点/非叶子节点存放在page中。

3.2.2 页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页至少包含2行数据，这些行数据是根据主键排列的。

主键顺序插入时如下图所示，先申请第一页，第一页存满，申请第二页继续存储，页与页之间会维护一个双向指针。

主键乱序插入时，可能存在页分裂现象，页分裂流程如下：

现状，当前已经存在两个页，现在要插入主键为50这个叶子节点

2 开启新的数据页3，把数据页1在中间进行分割，把数据分成两部分1,5,9和23,47，将23,47移动到page3中

3 50叶子节点插入到数据页3中

4 重新设置页指针，1和3维护双向指针；2和3维护双向指针

3.2.3 页合并

3.3 order by优化

Using index的排序效率要高于Using filesort。排序字段没有索引时，排序是Using filesort排序，给排序字段创建索引后，排序是Using index排序。

按照brand_name升序，ordered降序创建索引：

在按照brand_name升序，ordered降序select时，也是Using index方式。

Collation：代表升序（A），降序（D）方式

order by优化点如下：

3.4 group by优化

对分组字段建立适当的索引，提高分组效率。

未建立索引时分组：

type:ALL，全表扫描

建立联合索引后的分组：

type:index

根据age分组，未满足最左前缀法则，使用了临时表，效率低：

根据profession、age分组，满足最足前缀法则，只使用了索引，未使用临时表，效率高：

3.5 limit优化

优化方法：覆盖索引+子查询

3.6 count优化

3.7 update优化

执行update时更新的条件字段一定要有索引，否则行锁会升级成表锁，一旦锁表，并发性能会降低；

1、id是主键索引，所以执行SQL 1语句时锁定的是id为1的这一行记录；此时，其他客户端可以同时更新id不是1的其他记录。

2、name没有建立索引，所以执行SQL 2语句时锁定的是整张表，此时其他客户端不能同时更新表，并发性能严重下降；

4 视图/存储过程/触发器

4.1 视图

4.1.1 视图介绍及基本用法

视图是一张虚拟表，最终的数据来源于select语句，select语句从哪张表中查询数据，这张表称为视图所关联的基表。

4.1.2 检查选项-级联-with cascaded check option

视图是一张虚拟表，在视图中插入数据，数据实际是插入到了基表中。

视图检查选项：在视图中插入、更新、删除数据时，MySQL会检查对视图的上述操作是否符合视图定义，如果违背了视图定义时的条件，上述操作会失败。

案例：

stu_v_1插入5成功

stu_v_1插入25成功，因为定义stu_v_1时没有添加with check option选项

stu_v_2掺入7失败，不满足stdu_v_2定义时的视图要求（id>=10）

stu_v_2掺入26失败，cascaded（级联）会检查被依赖的视图定义，stu_v_2基于stu_v_1创建而来，所以with cascaded check option要求对stu_v_2插入数据时，要同时满足stu_v_2和stu_v_1的视图定义

stu_v_2掺入15成功，同时满足stu_v_1和stu_v_2的视图定义

stu_v_3插入11成功，满足stu_v_2和stdu_v_1的视图定义要求,

stu_v_3插入17成功，满足stu_v_2和stdu_v_1的视图定义要求，定义stu_v_3视图时没有加检查选项，因此插入17时不会检查v_3的视图定义（id<=15）

stu_v_3插入28失败，不满足stdu_v_1的视图定义要求

4.1.3 检查选项-with local check option

案例：

给V4插入数据，不会检查视图定义，因为没有声明with check option

给V5插入数据时，会检查V5的视图定义，因为声明了with local check option；同时也会查看V4视图，因为V4没有声明with check option，因此不会检查V4的视图定义；如果V4声明了with check option，给V5插入数据时还会同时检查V4的视图定义；

stu_v_4插入5成功，V4没有声明with check option，插入数据时不会检查视图定义

stu_v_4插入16成功，V4没有声明with check option，插入数据时不会检查视图定义

stu_v_5掺入13成功，满足stdu_v_5定义时的视图要求（id>=10），不检查V4的视图定义

stu_v_5掺入17成功，满足stdu_v_5定义时的视图要求（id>=10），不检查V4的视图定义

stu_v_6掺入14成功，因为V6没有声明with check option，所以不对V6视图定义做检查；检查V5的视图定义，满足V5的视图定义（id>=10）；同时也会查看V4视图，因为V4没有声明with check option，因此不会检查V4的视图定义；

4.1.4 视图更新条件以及作用

4.2 存储过程

4.2.1 存储过程基本语法

存储过程特点：

封装、复用SQL依据

可以接收参数，也可以返回数据

减少网络交互，从而提高数据处理效率

-- 创建存储过程
create procedure p2()
begin
    select count(*) from tb_brand;
end;

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


-- 查看存储过程的SQL定义
show create procedure p2;

-- 查看指定数据库的存储过程以及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='db1';

-- 删除存储过程
drop procedure if exists p1;

-- sql命令行中定义存储过程分号问题，可通过delimiter指定SQL语句结束符号，如delimiter $$
delimiter $$
create procedure p3()
begin
    select count(*) from tb_brand;
end$$

4.2.2 系统变量

-- 全局变量针对所有的会话都有效，会话变量只对当前会话有效。show后面如果不加级别，默认是会话级别的系统变量
-- 查看所有的系统变量
show variables ;
-- 模糊匹配，查看自动提交相关的系统变量
show variables  like 'auto%';
show global variables  like 'auto%';
-- 准确查看某一个系统变量
select @@autocommit;
select @@global.autocommit;
-- 设置系统变量
set @@autocommit=0;
set @@global.autocommit=0;
set session  autocommit  = 0;
set global  autocommit  = 0-- 数据库重启后，全局的autocommit变量会恢复到初始值

4.2.3 用户自定义变量

-- 定义用户自定义变量，session内有效
set @myname='zxb';
set @myage:=20;
set @mygender='男',@mybobby:='running';-- 一次性定义多个
select @mycolor:='red';
select count(*) into @mycount from tb_brand;-- 将select的查询结果赋值给自定义变量 @mycount
-- 使用用户自定义变量
select @myage,@myname,@mygender;
select @abc -- 该变量没有声明，查询出来是null，查询时并不会报错

4.2.4 局部变量


create procedure p4()
begin
    declare brand_count int default 0;-- 声明局部变量，p4()内有效
    select count(*) into brand_count from tb_brand;-- 局部变量赋值
    select brand_count;-- 查询局部变量
end;

call p4();

4.2.5 if语法

create procedure p5()
begin
    declare score int default 80;-- 声明局部变量，p5()内有效
    declare result varchar(10);
    if score >=85 then
        set result := '优秀';
    elseif score>=60 then
        set result:='及格';
    else
        set result:='不及格';
    end if;
    select result;
end;

call p5();

4.2.6 参数

create procedure p6(in score int,out result varchar(10))
begin
    if score >=85 then
        set result := '优秀';
    elseif score>=60 then
        set result:='及格';
    else
        set result:='不及格';
    end if;
end;

call p6(98,@result);
select @result;

create procedure p7(inout score double)

begin
    set  score:=score*0.5;
end;
set @score = 80;
call p7(@score);
select @score;

4.2.7 case

create procedure p8(in month int)
begin
    declare result varchar(10);
    case
        when month>=1 and month<=3 then
        set result:='一季度';
        when month>=4 and month<=6 then
            set result:='二季度';
        when month>=7 and month<=9 then
            set result:='三季度';
        when month>=10 and month<=12 then
            set result:='四季度';
        else
        set result:='非法参数';
    end case;
    select concat('你输入的月份为',month,',所属的季度为',result);-- 字符串拼接
end;

call p8(1);

4.2.8 while循环

计算1-n的累加值：

create procedure p10(in n int)
begin
    declare sum int default 0;
    while n>0 do
        set sum := sum+n;
        set  n := n-1;
    end while;
    select sum;
end;

call p10(10);

4.2.9 repeat循环

计算1-n的累加值：

create procedure p11(in n int)
begin
    declare sum int default 0;
    repeat
        set sum:=sum+n;
        set  n := n-1;
    until
        n<=0
    end repeat;
    select sum;

end;


call p11(10);

4.2.10 loop循环

计算1-n的累加值：

create procedure p12(in n int)
begin
    declare sum int default 0;
    sumLabel:loop
        if n<=0 then
            leave sumLabel;
        end if;
        set sum:=sum+n;
        set  n := n-1;

    end loop sumLabel;
    select sum;
end;

call p12(10);

计算1-n之间偶数累加值：

create procedure p13(in n int)
begin
    declare sum int default 0;
    sumLabel:loop
        if n<=0 then
            leave sumLabel;
        end if;
        if n%2=1 then
            set  n := n-1;
            iterate sumLabel;-- 如果是奇数，跳过本次循环
        end if;
        set sum:=sum+n;
        set  n := n-1;

    end loop sumLabel;
    select sum;
end;

call p13(10);

4.2.11 cursor游标

create procedure p15(in n int)
begin
    -- 声明三个变量
    declare brand_name varchar(20);
    declare company_name varchar(20);
    declare ordered int default 0;
    -- 声明游标，存储查询结果集
    declare cur cursor for select brand_name,company_name,ordered from tb_brand where id>=n;
    -- 创建一张新表
    drop table if exists tb_brand_2;
    create table if not exists tb_brand_2(id int primary key  auto_increment,brand_name varchar(20),company_name varchar(20),ordered int);
    -- 开启游标
    open cur;

    while true do
        -- 获取游标中的记录，并赋值给变量
        fetch cur into brand_name,company_name,ordered;
        -- 插入表结构
        insert into tb_brand_2 values(null,brand_name,company_name,ordered);

    end while;
    -- 关闭游标
    close cur;
end;

call p15(1063565);

4.2.12 handler条件处理

create procedure p15(in n int)
begin
    -- 声明三个变量
    declare brand_name varchar(20);
    declare company_name varchar(20);
    declare ordered int default 0;
    -- 声明游标，存储查询结果集
    declare cur cursor for select brand_name,company_name,ordered from tb_brand where id>=n;
    -- 声明条件处理程序,当SQL报错02000时退出程序并关闭游标，[02000][1329] No data - zero rows fetched, selected, or processed
    declare exit handler for sqlstate  '02000' close cur;
    -- declare exit handler for not found close cur;
    -- 创建一张新表
    drop table if exists tb_brand_2;
    create table if not exists tb_brand_2(id int primary key  auto_increment,brand_name varchar(20),company_name varchar(20),ordered int);
    -- 开启游标
    open cur;

    while true do
        -- 获取游标中的记录，并赋值给变量
        fetch cur into brand_name,company_name,ordered;
        -- 插入表结构
        insert into tb_brand_2 values(null,brand_name,company_name,ordered);

    end while;
    -- 关闭游标
    -- close cur;
end;

call p15(1063565);

4.3 存储函数

计算1-n的累加值：

create function fun1(n int)
returns int deterministic

begin
    declare sum int default 0;
    while n>0 do
            set sum := sum+n;
            set  n := n-1;
    end while;
    return sum;

end;

select fun1(100);

存储函数用的比较少，存储函数都可以用存储过程替代。

4.4 触发器

行级触发器：比如当执行update语句时影响了5行，如果触发器被触发5次，该触发器是行级触发器

语句级触发器：比如当执行update语句，不管这条update语句影响了多少行，该触发器只被触发一次

案例：

create table brand_logs(
    id int(11) primary key 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',
    operate_params varchar(500) comment '操作的参数'
);

4.4.1 insert触发器

create trigger insert_trigger
after insert
on tb_brand for each row
begin
insert into brand_logs(id, operation, operate_time, operate_id, operate_params) values
(null,'insert',now(),NEW.id,concat(new.id,new.brand_name,new.company_name,new.ordered));

end;

show triggers;-- 查看当前数据库有哪些触发器
-- 测试，tb_brand表插入数据后，brand_logs会插入日志记录
insert into tb_brand(id, brand_name, company_name, ordered, description, status) values (null,'小米','xiaomi',1,'ddd',20);
select * from brand_logs;

4.4.2 update触发器

create trigger update_trigger
    after update
    on tb_brand for each row
begin
    insert into brand_logs(id, operation, operate_time, operate_id, operate_params) values
        (null,'update',now(),NEW.id,concat('更新之前的数据',old.id,old.brand_name,old.company_name,old.ordered,'更新之后的数据',new.id,new.brand_name,new.company_name,new.ordered));

end;
-- 测试,tb_brand影响了几行，brand_logs中就会新增多少条记录，因为是行级触发器
update tb_brand set brand_name='xiaomi' where company_name='xiaomi';
select * from brand_logs;

4.4.3 delete触发器

create trigger delete_trigger
    after delete
    on tb_brand for each row
begin
    insert into brand_logs(id, operation, operate_time, operate_id, operate_params) values
        (null,'delete',now(),old.id,concat('更新之前的数据',old.id,old.brand_name,old.company_name,old.ordered));

end;
-- 测试
delete from tb_brand where company_name='xiaomi';
select * from brand_logs;

5 锁

5.1 全局锁

一、未加全局锁场景，出现数据不一致问题；

二、加全局锁

1、加全局锁

flush tables with read lock;其他客户端可以读取数据，不能写入数据。

2、业务操作

数据备份，mysqldump -uroot -p1234 dbname->D:/backup.sql

3、解锁

unlock tables;

加上--single-transaction参数后，不用在手动加全局锁，InnoDB内部会保证数据一致。

5.2 表级锁

5.2.1 表锁

-- 读锁：加锁过程中，其他客户端和当前客户端可以读，不能写;
-- 写锁：加锁过程中，其他客户端不可以读、不可以写；当前客户端可以读，可以写；
lock tables tb_brand read;
unlock tables;

lock tables tb_brand write;
unlock tables;