一、事务

1.1 简介

事务是一组操作的集合，是不可分割的工作单位，要么同时成功，要么同时失败。

默认MySQL的事务是自动提交的，当执行一条DML语句，MySQL会立即隐式提交事务。

1.2 事务操作

查看/设置事务提交方式

SELECT @@autocommit ;

SET @@autocommit = 0 ;

0：不自动提交 1：自动提交

提交事务

COMMIT;

回滚事务

ROLLBACK;

开启事务

START TRANSACTION; 或 BEGIN;

1.3 ACID

image-20260405170220484

1.4 并发事务问题

image-20260406150034555

1.5 事务隔离级别

image-20260406150430028

查看事务隔离级别：SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别：SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注：事务隔离级别越高，数据越安全，但是性能越低

二、存储引擎

image-20260406152908495

2.1 介绍

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。建表时可以指定存储引擎，如果没有指定将自动选择默认存储引擎。

image-20260406153844609

2.2 存储引擎特点

2.2.1 InnoDB

介绍

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

特点

  • DML操作遵循ACID，支持 事务
  • 行级锁，提高并发访问性能
  • 支持外键 FOREIGN KEY约束，保证数据的完整性和正确性

文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

参数：innodb_file_per_table

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。

image-20260406154346049

逻辑存储结构

image-20260406155130738

2.2.2 MyISAM

介绍

MySQL早期的默认存储引擎。

特点

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

文件

xxx.sdi：存储表结构信息

xxx.MYD: 存储数据

xxx.MYI: 存储索引

2.2.3 Memory

介绍

表数据存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

特点

  • 内存存放
  • hash索引（默认）

文件

xxx.sdi：存储表结构信息

image-20260406155820955

注：InnoDB支持行锁和表锁

2.3 存储引擎的选择

根据业务的需求，选择合适的存储引擎。

  • InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

  • MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。【被MongoDB替代】

  • MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。【被Redis替代】

三、索引

3.1 索引概述

索引（index）是帮助MySQL高效获取数据的**数据结构(有序)**。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

image-20260407100649498

3.2 索引结构

image-20260407100912695

image-20260407101030140

默认是B+树索引

（二叉树顺序插入数据时会退化成链表；红黑树是一颗自平衡二叉树，但是大数据量情况下，层级较深，检索速度慢）

3.3 B-Tree

最大度数为5（5阶），则每个节点最多存储4个数据，有5个指针。度数指的是一个节点的子节点的个数。

image-20260407102544043

3.4 B+Tree

和B树的区别：

  • 所有数据都会出现在叶子节点
  • 叶子节点形成一个单向链表
  • 非叶子节点仅起到索引的作用，具体数据都存放在叶子节点中

image-20260407103418414

MySQL对B+树进行了优化，将叶子节点的链表改成了双向循环链表。

image-20260407103526743

3.5 Hash

特点：

  • 只支持等于（=, in）的比较，不支持范围查询、模糊查询
  • 无法利用索引完成排序操作
  • 查询效率高，通常（没有hash冲突的情况下）只需要一次检索，效率通常高于B+Tree索引

image-20260407104050732

在MySQL中，支持hash索引的是Memory存储引擎。

而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

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

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；

C. 相对Hash索引，B+tree支持范围匹配及排序操作；

3.6 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：

image-20260407111227195

而在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

image-20260407112040589

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。

  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

举例：

image-20260407112441051

检索过程：

image-20260407112537342

3.7 索引语法

  • 创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name,... ) ;

如果关联了表中的多列，那么是联合索引。

  • 查看索引

SHOW INDEX FROM table_name ;

  • 删除索引

DROP INDEX index_name ON table_name ;

3.8 性能分析

3.8.1 SQL执行频率

通常在查询时的优化占比比较大，如果一个表经常使用增删改，查询比较少，那么就不需要优化。所以需要查看SQL语句的执行频率。

show [session|global] status LIKE 'Com_______'

（7个下划线去匹配7个字符）

– session 是查看当前会话 ; global 是查询全局数据 ;

3.8.2 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

image-20260408102817304

查看慢查询日志：

image-20260408103524334

3.8.3 profile详情（耗时详情）

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

1
2
3
4
5
6
7
8
-- 查看每一条SQL的耗时基本情况
show profiles;

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

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

3.8.4 explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

EXPLAIN/DESC SELECT 字段列表 FROM 表名 WHERE 条件 ;

image-20260408110232893

3.9 索引失效情况

最左前缀法则

如果条件筛选中有字段使用了联合索引，要遵守最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，**索引将会部分失效(后面的字段索引失效)**。

注：mysql8以后允许跳跃了。

范围查询

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

举例：

image-20260408113916467

image-20260408114005650

索引列运算

1、在索引列上使用函数运算，会导致索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

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

模糊查询

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

or连接的条件

用or分割开的条件，左右两侧都需要有索引，否则索引会失效。

数据分布影响

如果MySQL评估使用索引比全表扫描更慢，那么就不用索引。

3.10 SQL提示

1、use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2、 ignore index ： 忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3、force index ： 强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

3.11 覆盖索引

覆盖索引：要查询的所有字段已经完全包含在了索引之中。

比如有一个联合索引（name, password）这是键，索引对应的值是id。我要查询id,name,password，那么就是完全包含在这个索引之中的。如果我还要查询phone，这个是不包含在这个二级索引中的，所以需要回表查询，速度会慢一点。

尽量使用覆盖索引，减少select *。

image-20260408203044798

3.12 前缀索引

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

create index idx_email_5 on tb_user（email（5））;其中这个（5）代表email的前5个字符。

确定前缀长度

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

流程

现在二级索引中使用前缀进行查询，查询到相等的后，根据id到聚集索引中查询整行内容，然后对索引的那个字段进行判断是否一致，如果不一致就回到二级索引中寻找下一个相等的。如果相等就返回结果。

image-20260408202325755

3.13 单列索引 与 联合索引

如果存在多个查询条件，建议建立联合索引，避免回表查询。

联合索引的结构：

image-20260408203303699

（先按第一个字段进行排序，如果第一个字段完全相等，再按第二个排序）

3.14 索引设计原则

  1. 数据量较大，且查询比较频繁的表建立索引
  2. 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
  4. 如果是字符串字段，字段的长度较长，可以建立前缀索引
  5. 尽量使用联合索引（创建覆盖索引的条件，避免回表查询），减少单列索引。
  6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价也就越大，会影响增删改的效率
  7. 如果索引列不能存储NULL值，需要在创建表时用NOT NULL约束。当优化器知道每列是否包含NULL时，它可以更好地确定哪个索引用于查询更加有效。

四、锁

按锁的粒度分，分为：

  • 全局锁：锁定数据库的所有表
  • 表级锁：每次操作锁定整张表
  • 行级锁：每次操作锁住对应的行数据

4.1 全局锁

典型使用场景是做全库的逻辑备份。

image-20260410143329148

存在的问题：

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

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

4.2 表级锁

锁定粒度大，发生锁冲突的概率最高，并发度最低。

分类：

  1. 表锁
  2. 元数据锁
  3. 意向锁

4.2.1 表锁

分类：

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

语法：

加锁：lock tables 表名 read/write

释放锁：unlock tables / 客户端断开连接

读锁

允许自己/别的客户端读，但是不允许写（如果其他客户端要执行写操作，则其他客户端进入阻塞状态）

image-20260410144807677

写锁

允许自己读/写，其他客户端的读/写进去阻塞状态

image-20260410145044691

4.2.2 元数据锁 meta data lock，MDL

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

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

image-20260410145508969

兼容：两个事务可以分别持有这两种锁，不会导致阻塞。

4.2.3 意向锁

用于解决行锁和表锁的冲突问题。

在DML执行时，加了行锁。另一个客户端想加表锁，就需要先检查每行数据是否加锁了，效率很低。于是引入意向锁来减少表锁的检查。

image-20260410151334906

加入意向锁后，如果别的客户端要加表锁，检测到有意向锁后根据条件看要不要阻塞，等到意向锁解锁后再加锁。

意向锁

  1. 意向共享锁(IS): select ... lock in share mode（加上行锁，并自动添加意向共享锁）。与 表锁共享锁(read)兼容，与 表锁排他锁(write)互斥。
  2. 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

4.3 行锁

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

行锁是通过对索引上的索引项加锁来实现的，而不是对记录加锁。

分类：

1.

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

image-20260410153429237

4.3.1 行锁

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

image-20260410153622821

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

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

4.3.2 间隙锁/临键锁

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

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。

  • 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。

  • 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止。

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

五、日志

5.1 错误日志

记录了当mysqld（服务端程序）启动和停止时，以及服务器再运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

该日志是默认开启的，默认存放目录 /var/log/，默认的日志文件名为 mysqld.log

查看日志位置：

show variables like '%log_error%';

linux命令：tail -50 /var/log/mysqld.log查看尾部的50行

5.2 二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。

作用：①. 灾难时的数据恢复；②. MySQL的主从复制。在MySQL8版本中，默认二进制日志是开启着的，涉及到的参数如下：

show variables like '%log_bin%';

参数说明

  • log_bin_basename：当前数据库服务器的binlog日志的基础名称(前缀)，具体的binlog文件名需要再该basename的基础上加上编号(编号从000001开始)。

  • log_bin_index：binlog的索引文件，里面记录了当前服务器关联的binlog文件有哪些。

日志格式

image-20260410200545216

在 /etc/my.cnf 文件中添加binlog_format=STATEMENT就可以修改其日志格式。

日志查看

image-20260410200744477

日志删除

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志：

image-20260410201529349

配置日志的过期时间，过期会自动删除。如需修改在配置文件中添加这个参数就可以了。（my.cnf)

show variables like ‘%binlog_expire_logs_seconds%’;

5.3 查询日志

查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。查询日志是默认关闭的，需要手动在/etc/my.cnf文件中设置

image-20260410202318808

5.4 慢查询日志

image-20260410202845872

默认情况下，不会记录管理语句（修改、管理、维护数据库的命令），也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和 更改此行为 log_queries_not_using_indexes，如下所述。

image-20260410202909758

补充

Linux启动Mysql服务

1
systemctl start mysqld
1
systemctl restart mysqld
1
systemctl stop mysqld