MySQL学习笔记进阶

共计 13795 个字符，预计需要花费 35 分钟才能阅读完成。

存储引擎

其实存储引擎以前叫做表处理器，后来可能⼈们觉得太土，就改成了存储引擎的叫法。存储引擎是基于表的，所以也可被称为表类型。查询当前数据库支持的存储引擎：show engines;

存储引擎微观结构：包括数据内存区域和日志内存区域。

数据内存区域：

• 共享内存缓冲区：buffer pool 缓冲池，用来缓冲数据页 + 索引页，查看参数：select @@innodb_buffer_pool_size;
• 会话内存缓冲区：join_buffer_size、key_buffer_size、read_buffer_size、read_rnd_buffer_size、sort_buffer_size

日志内存区域，负责 Redo 日志缓冲。

InnoDB

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 版本之后，被设置为默认的 MySQL 存储引擎。

table.ibd：InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构、索引和数据。

系统变量 innodb_file_per_table：show variables like 'innodb_file_per_table';

查看 table.idb 文件：ibd2sdi sex.ibd，ibd2sdi 命令从 ibd 文件中提取 sdi 表结构数据，MySQL 数据存放目录：C:\ProgramData\MySQL\MySQL Server 8.0\Data\test。

逻辑存储结构：

• tablespace：表空间
• segment：段，一张表就是一段（分区表除外），可由 1 个或多个区构成
• extent：区（簇），默认 1M，连续的 64 个页
• page：页，默认 16K，连续的 4 个 OS block（连续的扇区），一页就是一个磁盘块，代表一次 IO
• row：行

表空间概念是引入于 Oracle 数据库，起初是为了解决存储空间扩展的问题。

MySQL 表空间类型

• 共享表空间：在 5.5 版本引入了共享表空间(ibdata1)，作为默认存储方式。用来存储：系统数据，日志，Undo，临时表，用户数据和索引
• 独立表空间：5.6 版本默认独立表空间模式，即单表单表空间
• 普通表空间：完全和 Oracle 一致的表空间管理模式
• Undo 表空间：存储回滚日志
• 临时表空间：存储临时表

表空间管理：

• 独立表空间和共享表空间是可以互相切换的
• 查看默认表空间模式：select @@innodb_file_per_table;，1 代表独立表空间，0 代表共享表空间
• 切换表空间：set global innodb_file_per_table=0;，重新登录会话查看。修改完之后，只影响新创建的表

扩展共享表空间大小和个数：

• 通常在初始化数据时，设定好参数。查看：select @@innodb_data_file_path;
• 初始化之前，在 my.cnf 添加配置，例如：innodb_data_file_path=ibdata1:1G;ibdata2:1G:autoextend
• 在已运行的数据库上扩展多个 ibdata 文件，设置 innodb_data_file_path 参数时，已有的 ibdata1 文件大小应该和磁盘上真实大小一致，而不是随便指定

表空间迁移：

• 首先创建一个一模一样的空表 t1
• 删掉空表的表空间 ibd 文件：alter table t1 discard tablespace;
• 拷贝准备好的 ibd 文件，修改权限，导入表空间到 t1 表：alter table t1 import tablespace;

跳过外键检查：set foreign_key_checks=0;

InnoDB 核心参数

innodb_flush_method：控制刷写磁盘时，是否使用 OS Cache：

• fsync：buffer pool/redo buffer 写磁盘时，需先经历 OS Cache 再写到磁盘
• O_DSYNC：buffer pool 写磁盘时，需先经历 OS Cache 再写到磁盘；redo buffer 写磁盘时，直接写到磁盘，跨过 OS Cache
• O_DIRECT：buffer pool 写磁盘时，直接写到磁盘，跨过 OS Cache；redo buffer 写磁时，需先经历 OS Cache 再写到磁盘

生产建议使用 O_DIRECT，最好是配合固态盘使用。

innodb_buffer_pool_size：数据缓冲区总大小。作用是缓冲数据页和索引页，MySQL 最大的内存区域。默认 128M，官方建议 80-90% 物理内存，生产建议 75% 以下，按需调配。

MyISAM

MyISAM 是 MySQL 早期的默认存储引擎，特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

文件：

• xxx.sdi 存储表结构信息
• xxx.MYD 存储数据
• xxx.MYI 存储索引

MyISAM 的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据，反观 InnoDB 是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。

InnoDB 和 MyISAM 三大区别：InnoDB 支持事务安全、支持行锁、支持外键。MyISAM 支持表锁。

Memory

该引擎的表数据是存储在内存中的，只有一个文件 xxx.sdi 用来存储表结构信息。

索引

索引分类

按照数据结构分类：

• B+ 树 索引（MySQL 的 InnoDB 存储引擎采用的就是这种索引）采用 B+ 树 的数据结构
• Hash 索引（仅 memory 存储引擎支持）采用 哈希表 的数据结构

按照物理存储分类：

• 聚集索引(Clustered Index)：索引和表中数据在一起，数据存储的时候就是按照索引顺序存储的，索引结构的叶子节点保存了行数据。一张表必须有一个聚集索引，而且只有一个。
• 非聚集索引(Secondary Index)：索引和表中数据是分开的，索引是独立于表空间的，索引结构的叶子节点关联的是对应的主键，一张表可以有多个非聚集索引。

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

按照字段特性分类：

• 主键索引（primary key）
• 唯一索引（unique）
• 普通索引（index）
• 全文索引（fulltext：仅 InnoDB 和 MyISAM 存储引擎支持）：要求字段类型都是文本内容才可以使用全文索引

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	primary
唯一索引	避免同一表中某数据列中的值重复	可以有多个	unique
常规索引	快速定位特定数据	可以有多个
全文索引	查找的是文本中的关键词，而不是比较索引中的值	可以有多个	fulltext

按照字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）

索引是在引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

• B+Tree 索引：最常见的索引类型，大部分引擎都支持 B + 树索引
• Hash 索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
• R-Tree 空间索引：是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text 全文索引：是一种通过建立倒排索引，快速匹配文档的方式。类似于 Lucene, Solr, ES

索引	InnoDB	MyISAM	Memory
B+Tree	✅	✅	✅
Hash	❌	❌	✅
R-Tree	❌	✅	❌
Full-text	5.6 版本之后支持	✅	❌

索引结构

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

红黑树缺点：大数据量情况下，层级较深，检索速度慢。

B Trees（多路平衡查找树）：以一颗最大度数为 5 的 B Trees 为例，每个节点最多存储 4 个 key，5 个指针。具体动态变化过程可参考：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+ Trees：MySQL 索引数据结构对经典的 B Trees 进行了优化。在原 B Trees 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B + Trees，提高区间访问的性能，这样很适合范围查询。B+ 树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样让 B + 树更矮更胖，提高检索效率。

Hash：

• Hash 索引只能用于对等比较(=，in)，不支持范围查询 between，>，<
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B +Tree 索引

🤔为什么 InnoDB 存储引擎选择使用 B + Trees 索引结构?

• 相对于二叉树，层级更少，搜索效率高
• 相对于 B Trees，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对于 Hash 索引，B+ Trees 支持范围匹配及排序操作

回表查询：是指先走二级索引，找到对应的主键值，再根据主键值再到聚集索引当中，拿到这一行的数据。

🤔InnoDB 主键索引的 B + Trees 高度为多高呢？
假设：一行数据大小为 1k，一页中可以存储 16 行这样的数据。InnoDB 指针占用 6 个字节的空间，主键即使为 bigint，占用字节数为 8。

高度为 2：

• n 8 + (n + 1) 6 = 16 * 1024，算出 n 约为 1170
• 1171 * 16 = 18736

高度为 3：1171 1171 16 = 21939856

会发现，即使存储 2000+ 万的记录，树的结构也才只有 3 层。如果说存储了 4000+ 万的记录，那就有可能超过 3 层，这个时候就得考虑分库分表了。

MySQL 中建议索引树高度为 3 - 4 层。

🤔如果一张表没有主键索引，那还会创建 B + 树吗？
当一张表没有主键索引时，默认会使用一个隐藏的内置聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建，通常是使用 B + 树来实现的。

索引语法

查看索引：show index from 表;

删除索引：drop index 索引名 on 表;

创建索引：

• 常规索引：create index idx_name on user(name);
• 唯一索引：create unique index idx_name on user(name);
• 联合索引：create index idx_name on user(name, id);

压力测试：mysqlslap --concurrency=100 --number-of-queries=200 --auto-generate-sql;

• concurrency 指定同时有 100 个客户端连接
• number-of-queries 指定总测试查询次数（并发客户端数 * 每个客户端查询次数）

使用规则

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始（即最左边的列必须存在），并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。

索引失效

范围查询：联合索引中，出现范围查询(>, <)，范围查询右侧的列索引失效。

索引列运算：不要在索引列上进行运算操作，索引将失效。

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

模糊匹配：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or 连接的条件：用 or 分割开的条件，如果 or 前条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

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

SQL 提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

use index：select * from user use index(idx_name) where name=’ 张三 ’;

ignore index：select * from user ignore index(idx_name) where name=’ 张三 ’;

force index：select * from user force index(idx_name) where name=’ 张三 ’;

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列在该索引中已经全部能够找到），减少 select *。

前缀索引

当字段类型为字符串 (varchar，text 等) 时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘 IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：create index idx_name on user(name(n));

前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值 (基数) 和数据表记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

select count(distinct name) / count(*) from user;

select count(distinct substring(name,1,3)) / count(*) from user;

索引设计原则

1. 针对数据量较大，且查询比较频繁的表建立索引。建表一定要有主键，一般是个无关列。大表加索引，要在业务不繁忙期间操作
2. 针对常作为查询条件(where)、排序(order by)、分组(group by)、join 等操作的字段建立索引，若重复值特别多，可以建立联合索引。少在经常更新值的列上建索引
3. 选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果索引字段太长，最好使用前缀索引
5. 尽量使用联合索引，减少单列索引。查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 限制索引条目，删除不再使用或很少使用的索引，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引列不存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询

SQL 性能分析

查看服务器状态信息：show global status like ‘Com_______’;

慢日志

可参考MySQL 日志管理

profile 详情

查看是否支持 profile 操作：select @@have_profiling;

查看 profiling 开关状态：select @@profiling;

默认 profiling 是关闭的，开启语句为：set profiling=1;

查看每条 SQL 语句的耗时基本情况：show profiles;

查看指定 query_id 的 SQL 语句各个阶段耗时情况：show profile for query query_id;

查看指定 query_id 的 SQL 语句 CPU 使用情况：show profile cpu for query query_id;

explain 执行计划

直接在 select 语句之前加上关键字 explain：explain select * from user;

各字段含义：
id：SELECT 查询的序列号，表示查询中执行 select 子句或者是操作表的顺序。id 相同，执行顺序从上到下；id 不同，值越大，越先执行。

select_type：表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE 之后包含了子查询)等。

type：表示连接类型，性能由好到差的连接类型为 NULL、system、const（聚簇索引等值查询）、eq_ref（多表连接中，非驱动表连接条件是主键或唯一键）、ref（辅助索引等值查询）、range（索引范围查询）、index（全索引扫描）、all（全表扫描）。

possible_key：表示可能应用在这张表上的索引，一个或多个。

key：实际使用的索引，如果为 NULL，则没有使用索引。

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。

rows：表示根据表统计信息及索引选用情况，大致估算出找到所需记录需要读取的行数。

Extra：表示不适合在其他字段中显示，但是十分重要的额外信息。

SQL 优化

插入数据

insert 优化

• 批量插入
• 手动提交事务
• 主键顺序插入

大批量插入数据，使用 insert 性能较低，此时可以使用 load 指令进行插入。

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -uroot -p
# 设置全局参数为 1，开启从本地加载文件导入数据的开关
set global local_infile=1;
# 执行 load 指令将数据加载到表结构中
load data local infile '文件路径' into table '表名' fields terminated by ',' lines terminated by '\n';

主键优化

主键设计原则：

• 满足业务需求的情况下，尽量降低主键长度
• 插入数据时，尽量选择顺序插入，选择使用 AUTOINCREMENT 自增主键
• 尽量不要使用 UUID 做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改

order by 优化

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。

尽量使用覆盖索引，避免使用 select *。

多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。

如果不可避免的出现 filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认 256K)。

创建索引指定升降顺序：create index idx_id_name_ad on user(id asc, name desc);

group by 优化

类似 order by 优化。

limit 优化

优化思路：一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

count 优化

按照效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)。

update 优化

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

视图

information_schema

每次数据库启动，会自动在内存中生成 information_schema 视图，用于查询 MySQL 部分元数据信息。

数据库资产统计，统计每个库，所有表个数、表名：select table_schema, count(table_name), group_concat(table_name) from information_schema.tables group by table_schema \G

统计每个库占用空间总大小：select table_schema, sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH) from information_schema.tables group by table_schema;

查询业务数据库（系统库除外），所有非 InnoDB 表：select table_schema, table_name from information_schema.tables where engine!='InnoDB' and table_schema not in ('sys','performance_schema','information_schema','mysql');

查询业务数据库（系统库除外），所有非 InnoDB 表，并将非 InnoDB 表转换为 InnoDB 表：

• 修改配置文件（/etc/my.cnf），在 [mysqld] 下添加secure_file_priv=/tmp
• 重启数据库，执行select concat('alter table',table_schema,'.',table_name,'engine=innodb;') from information_schema.tables where engine!='InnoDB' and table_schema not in ('sys','performance_schema','information_schema','mysql') into outfile '/tmp/alter.sql';;
• 导入 sql 脚本：mysql </tmp/alter.sql

基本语法

创建视图：create or replace view user_v as select * from user;

查询视图：show create view user_v;

修改视图：

• create or replace view user_v as select * from user;
• alter view user_v as select id from user;

删除视图：drop view if exists user_v;

检查选项

当使用 with check option 子句创建视图时，MySOL 会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL 允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，MySOL 提供了两个选项：cascaded 和 local，默认值为 cascaded。

create or replace view user_v as select * from user where id>1 with cascaded check option;
create or replace view user_v as select * from user where id>1 with local check option;

更新及作用

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：

1. 聚合函数或窗口函数 (sum()、min()、max()、count() 等)
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL

作用：

1. 简单。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
2. 安全。数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
3. 数据独立。视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

基本语法

在命令行中，执行创建存储过程的 SQL 时，需要通过关键字 delimiter 指定 SQL 语句的结束符。

创建：

mysql> delimiter $$
mysql> create procedure p()
    -> begin
    -> select count(*) from user;
    -> end
    -> $$
mysql> delimiter ;

调用：call p();

查看指定数据库的存储过程及状态信息：select * from information_schema.routines where routine_schema=’test’;

查询某个存储过程的定义：show create procedure p;

删除：drop procedure if exists p;

变量

系统变量

系统变量是 MySQL 服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。

查看所有系统变量（默认是 session）：show variables;

模糊匹配查找变量：show global variables like ‘auto%’;

查看指定变量的值：

• select @@autocommit;
• select @@global.autocommit;

设置系统变量：

• set autocommit=0;
• set @@autocommit=0;
• @@是 MySQL 系统变量前缀，用于访问服务器全局变量

如果没有指定 session/global，默认是 session。MySQL 服务重新启动之后，所设的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

用户变量

用户定义的变量无需对其进行声明或初始化，只不过获取到的值为 NULL。用户自定义的变量，只在当前会话有效，以 @开头用于存储用户定义的变量。

赋值：

• set @myname=’apple’;
• set @myname:=’apple’;
• set @name1=’apple’, @name2=’pear’;

select count(*) into @mycount from user;

在 MySQL 中，赋值推荐使用 :=。

使用：select @myname, @name1, @name2;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要 declare 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 begin…end 块。

mysql> delimiter $$
mysql> create procedure p()
    -> begin
    -> declare my_count int default 0;
    -> select count(*) into my_count from user;
    -> select my_count;
    -> end
    -> $$
mysql> delimiter ;

参数

• in：输入参数
• out：输出参数

mysql> delimiter $$
mysql> create procedure p1(in score int, out result varchar(10))
    -> begin
    -> if score>=85 then
    -> set result='A';
    -> elseif score>=60 then
    -> set result='B';
    -> else
    -> set result='C';
    -> end if;
    -> end
    -> $$
mysql> delimiter ;
mysql> call p1(82, @result);
mysql> select @result;

inout：输入输出参数

mysql> delimiter $$
mysql> create procedure p2(inout score double)
    -> begin
    -> set score=score*0.5;
    -> end
    -> $$
mysql> delimiter ;
mysql> set @score=80;
mysql> call p2(@score);
mysql> select @score;

if / case

if 条件 1 then
    ...;
elseif 条件 2 then
    ...;
else
    ...;
end if;

case 值
  when 值 1 then
    ...
  when 值 2 then
    ...;
  else
    ...;
end case;

case
  when 条件 1 then
    ...;
  when 条件 2 then
    ...;
  else
    ...;
end case;

while

while 条件 do
    ...;
end while;

repeat

repeat
    ...;
until 条件
end repeat;

loop

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

mysql> delimiter $$
mysql> create procedure p3(in n int)
    -> begin
    -> declare total int default 0;
    -> sum:loop
    -> if n<=0 then leave sum;
    -> end if;
    -> if n%2=1 then
    -> set n:=n-1;
    -> iterate sum;
    -> end if;
    -> set total:=total+n;
    -> set n:=n-1;
    -> end loop sum;
    -> select total;
    -> end
    -> $$
mysql> delimiter ;
mysql> call p3(10);

游标

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

声明游标：declare u_cursor for select * from user;

打开游标：open u_cursor;

获取游标：fetch u_cursor into 变量;

关闭游标：close u_cursor;

条件处理程序

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

• declare exit handler for sqlstate ‘02000’ close u_cursor;
• declare exit handler for not found close u_cursor;

handler_action：continue: 继续执行当前程序；exit: 终止执行当前程序。

condition_value

• sqlstate: 状态码，如 02000
• sqlwarning: 所有以 01 开头的 sqlstate 代码的简写
• not found: 所有以 02 开头的 sqlstate 代码的简写
• sqlexception: 所有没有被 sqlwarning 或 not found 捕获的 sqlstate 代码的简写

存储函数

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

mysql> delimiter $$
mysql> create function f1(n int)
    -> returns int deterministic
    -> begin
    -> declare total int default 0;
    -> while n>0 do
    -> set total=total+n;
    -> set n=n-1;
    -> end while;
    -> return total;
    -> end
    -> $$
mysql> delimiter ;
mysql> select f1(100);

触发器

• insert 型触发器：new 表示将要或者已经新增的数据
• update 型触发器：old 表示修改之前的数据，new 表示将要或已经修改后的数据
• delete 型触发器：old 表示将要或者已经删除的数据

创建：create trigger insert_trigger after insert on 表 for each row;
begin
…
end

查看：show triggers;

删除：drop trigger if exists 触发器名;

锁

锁是属于资源的，不是某个事务的特性。每次事务需要资源的时候，需要申请持有资源的锁。

锁类型：

• 内存锁：Latch 是轻量级的锁，因为其要求锁定的时间必须非常短。若持续时间长，则应用性能会非常差，在 InnoDB 引擎中，Latch 又可以分为 mutex 互斥量和 rwlock 读写锁

• 对象锁：

  • MDL 元数据锁：修改元数据时、备份时
  • Table Lock 表锁
  • Record Lock 行锁
  • Gap Lock 间隙锁
  • Next-Key Lock 临键锁

功能分类：

• IS：意向共享锁，表级别
• S：共享锁读锁，行级别
• IX：意向排它锁，表级别
• X：排它锁写锁，行级别

全局锁

加上一个全局锁：flush tables with read lock;

数据备份：mysqldump -uroot -p123456 test > D:/test.sql

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

mysqldump –single-transaction -uroot -p123456 test > D:/test.sql

释放锁：unlock tables;

备份指定数据库表结构：mysqldump -uroot -B test –no-data > test.sql

表级锁

表锁

加锁：lock tables 表名 read/write;

释放锁：unlock tables;

读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

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

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

意向锁

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

行级锁

每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。

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

行锁(Record Lock)：锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。

间隙锁(Gap Lock)：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其事务在这个间隙进行 inset，产生幻读。

临键锁(Next-Key Lock)：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙 Gap。

锁监控

看有没有锁等待：SHOW STATUS LIKE ‘innodb_row_lock%’;

查看哪个事务在等待（被阻塞）：SELECT * FROM information_schema.INNODB_TRX WHERE trx_state=’LOCK WAIT’;

查看锁源：SELECT * FROM sys.innodb_lock_waits;

找到锁源 thread_id：SELECT * FROM performance_schema.threads WHERE processlist_id=15;

找到锁源 SQL 语句：

• 当前执行语句：SELECT * FROM performance_schema.events_statements_current WHERE thread_id=41;
• 执行语句历史：SELECT * FROM performance_schema.events_statements_history WHERE thread_id=41;

死锁监控：

show engine innodb status \G
show variables like '%deadlock%';

把死锁信息打印进错误日志：vi /etc/my.cnf，添加innodb_print_all_deadlocks = 1。