aboss.top
MySQL学习笔记进阶
编辑
2026-04-01
undefined
00

目录

存储引擎
InnoDB
InnoDB核心参数
MyISAM
Memory
索引
索引分类
索引结构
索引语法
使用规则
最左前缀法则
索引失效
SQL提示
覆盖索引
前缀索引
索引设计原则
SQL性能分析
慢日志
profile详情
explain执行计划
SQL优化
插入数据
主键优化
order by优化
group by优化
limit优化
count优化
update优化
视图
information_schema
基本语法
检查选项
更新及作用
存储过程
基本语法
变量
系统变量
用户变量
局部变量
参数
if / case
while
repeat
loop
游标
条件处理程序
存储函数
触发器
锁
全局锁
表级锁
表锁
元数据锁
意向锁
行级锁
锁监控

存储引擎

其实存储引擎以前叫做表处理器，后来可能⼈们觉得太土，就改成了存储引擎的叫法。存储引擎是基于表的，所以也可被称为表类型。查询当前数据库支持的存储引擎：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
  • 在已运行的数据库上扩展多个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

索引InnoDBMyISAMMemoryB+Tree✅✅✅Hash❌❌✅R-Tree❌✅❌Full-text5.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>createprocedurep1(inscoreint,outresultvarchar(10))−>begin−>ifscore>=85then−>setresult=′A′;−>elseifscore>=60then−>setresult=′B′;−>else−>setresult=′C′;−>endif;−>end−> 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。

本文作者:a

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！