MySQL进阶篇

MySQL Advance
40

存储引擎（Engine）

Mysql体系结构


Mysql默认的存储引擎为InnoDB。

  • 连接层
    最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个用户端验证它所具有的操作权限。
  • 服务层
    第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
  • 引擎层
    存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
  • 存储层
    主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎简介

什么是存储引擎？
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎时基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
创建表时指定存储引擎

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)ENGINE = InnoDB [COMMENT 表注释];

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

  • SHOW ENGINES;

存储引擎特点

InnoDB

介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MYSQL存储引擎。

特点

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

文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
参数：innodb_file_per_table，如果该参数为on，则每张表都对应一个文件，可以通过命令SHOW VARIABLES LIKE 'innodb_file_per_table';查看该变量的状态。

InnoDB的逻辑存储结构

其中 区 的大小是固定的，为1M。页的大小也是固定的，为16K。
段
InnoDB存储引擎中，表空间通常包含如下几个段：

  • 数据段(Data Segment)：用于存储表中的数据
  • 索引段(Index Segment)：存储表的索引
  • 回滚段(Rollback Segment)：用于事务的回滚操作
  • 未提交数据段(Uncommitted Data Segment)：存储未提交的数据，即事务尚未提交的数据。
  • 插入缓冲(Insert Segment)：用于提高插入操作的性能，延迟将数据写入到主数据页中。
  • 二进制日志缓冲(Binary Log Buffer)：用于事务的持久化，记录事务的操作日志，保证事务的持久性。
MyISAM

介绍
MyISAM是MySQL早期的默认存储引擎

特点

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

文件

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

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

特点

  • 内存存放，访问速度快
  • hash索引

文件

  • xxx.sdi：存储表结构信息
三大引擎区别

存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

  • InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
  • MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。它适用于日志存储、用户足迹、浏览历史等场景。不过目前业界对于日志这类数据都选择使用NoSQL数据库，如MongoDB。
  • Memory：将所有数据保存到内存中，访问速度快，通常用于临时表及缓存。Memory的缺陷是对表的大小有限制，无法保证数据的安全性。

索引（Index）

概述

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

优缺点
优点：

  • 提高数据检索效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

  • 索引列也要占用空间
  • 索引大大提高了查询效率，但同时也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎支持不同的索引结构。

三大存储引擎支持的索引结构

默认情况下，一般讲MySQL的存储引擎索引结构，都指的是B+Tree索引。通过上图也可以看出，三大存储引擎也都支持B+Tree索引。

B-Tree的数据可视化网站B-Tree Visualization (usfca.edu)

B-Tree（多路平衡查找树）

详见【数据结构】。

B+Tree

引入目的，基本原理详见【数据结构】。

MySQL索引数据结构对经典的B+Tree进行了优化。在原有的B+Tree的基础上，增加了一个指向右侧相邻叶子节点的链表指针，就形成了带有双向指针的B+Tree，提高了区间访问的性能。

MySQL中的B+Tree

Hash

哈希索引就是采用一定的Hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决，也称为链地址法。

hash索引特点

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

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

面试题——为什么InnoDB存储引擎选择使用B+Tree索引结构？
  • 相对应二叉树、红黑树，层级更少，搜索效率高；
  • 对应B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页（Table Space, Segment, Extent, Page, Row）中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；同时B-Tree非叶子节点也存储数据，这样页分裂的情况会比较多。
  • 相对于hash索引，B+Tree支持范围匹配以及排序操作

索引分类

根据索引的类型，有如下分类：

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

  • 聚集索引（Clustered Index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据，==聚集索引必须要，而且只有一个==
  • 二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键

根据索引中列的数量，可分为：
单列索引 ：添加在单个列上的索引，索引在构建时只关心该列的值。
联合索引 ：创建索引时，按序指定多个列的索引被称为联合索引，联合索引在使用时必须符合最左前缀法则。在构建联合索引时，从左往右依次比较。

聚集索引选取规则

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，则使用第一个唯一（Unique）索引作为聚集索引。
  • 如果表没有主键也没有唯一索引，则InnoDB会使用隐藏字段db_row_id作为聚集索引。

回表查询

回表查询：指的是先从二级索引中拿到ID值，然后再从聚集索引中拿到具体的数据。

索引语法

  • 创建索引
    CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
  • 查看索引
    SHOW INDEX FROM table_name;
  • 删除索引
    DROP INDEX index_name ON table_name;

SQL 性能分析

查看SQL的执行频率

使用下面的命令可以查看Mysql服务器执行select、insert、update、delete等语句的频次。
SHOW [GLOBAL | SESSION] STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time, 单位：秒，默认10秒）的所有SQL语句的日志。可以通过慢查询日志来记录执行效率较低的SQL语句，从而针对这些语句进行优化。

MySql的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.conf）中配置如下信息：

# 开启慢查询日志
slow_query_log=1
# 设置慢查询日志时间为2秒，如果SQL语句的执行时间超过2秒，就会被视为慢查询，从而记录在慢查询日志中
long_query_time=2

配置完成之后，需要重启mysqld服务，可以在/var/lib/mysql/localhost-slow.log中查看慢查询日志。

profile详情

SHOW PROFILES;能够在做SQL优化时帮助我们了解时间都耗费到哪里去了，也即查看SQL执行的各阶段耗时。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
SET profiling = 1;

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

# 查看每一条SQL的耗时情况
show profiles;

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

# 查看指定query_id的SQL语句的CPU的使用情况
show profile cpu for query query_id;
查看SQL的执行计划

使用EXPLAIN或DESC可以获取到SQL的执行计划（包括在SELECT语句执行过程中表如何连接和连接的顺序，执行时的索引使用情况等）。使用时只需要在待分析的SQL前面加EXPLAIN或DESC关键字即可。执行计划的各字段含义如下：

  • id：select 查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下，id不同，id大的先执行）
  • select_type：表示select的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询）等
  • table：执行查询时所用的表
  • type：表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、ref、range、index、all。
  • possible_key：显示可能应用的索引，一个或多个
  • key：实际使用的索引，如果为NULL，则表示没有使用索引。
  • key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
  • rows:MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的。
  • filtered：表示返回结果的行数占需要读取行数的百分比，filtered的值越大越好。
  • Extra：额外信息。

type字段说明

  • NULL：没有与表进行交互
  • system：查询系统表
  • const：通过主键或唯一索引查询，表最多有一个匹配行
  • eq_ref：用于连接查询，表示对于前表的每一个结果行，只有一行与之匹配。通常出现在使用主键或唯一索引作为连接条件的查询中。
  • ref：使用非唯一索引或唯一索引的非唯一前缀进行查询
  • range：范围查询
  • index：也用了索引，但是对索引进行了遍历
  • all：全表扫描
索引使用
联合索引
  • 最左前缀法则：如果使用了联合索引，要遵循最左前缀法则。最左前缀法则指的是查询条件要包含索引最左边的列，并且不跳过索引中列。如果跳过了中间列，索引将部分失效。

举个例子，如果索引为create index idx_1 on table(name, age, status)，如果查询条件中仅没有name，则索引完全失效。如果查询条件仅没有age，则索引部分失效，name字段使用索引，status不使用索引。

查询条件中的and条件是可交换的，这里最左前缀法则仅仅看查询条件中有没有包含对应列，与查询条件中的字段顺序无关。

范围查询：联合索引中，如果出现开区间的范围查询（>或<），则右侧索引失效。如果业务允许，可以使用闭区间（>=或<=）来规避。

以上面的索引为例，如果查询条件为name = 'zolmk' and age > 20 and status=1，则status没使用索引。如果查询条件为name = 'zolmk' and age >= 20 and status=1，则全部使用索引。

索引失效
  • 索引列运算 ：如果在索引列上进行运算操作（如substring等），索引将失效。
  • 字符串不加引号 ：索引将失效。因为存在隐式类型转换（其实也相当于对列进行运算）。
  • 模糊查询：如果仅仅是尾部模糊匹配，则索引不会失效，如果是头部，则索引失效。
  • or条件：用or分开的查询条件，如果or前面的有索引，or后面的没索引，那么涉及到的索引都不会被用到。只有当or前面和后面的条件所涉及的列都有索引时，才会使用涉及的索引。
  • 数据分布情况：如果Mysql评估走索引比全表扫描还慢，则MySql就不会使用索引
SQL提示

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

总共有三个关于索引优化的提示，分别是：

  1. use index：建议MySQL使用某个索引
  2. ignore index：忽略某个索引
  3. force index：强制使用某个索引

提示的用法示例如下：

-- 括号里面的内容为建议mysql使用的索引
select * from tb_user use index(idx_user_pro) where profession='软件工程';
覆盖索引

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

覆盖索引不会造成回表查询，因为所有的数据在索引都都可以找到。

Tips： Extra中的值
具体优化效果：using index > using index condition > using where。

using index condition：使用了MySQL的内部优化技术——索引条件下推（ICP, Index Condition Pushdown），可以减少回表查询的次数。
using where：使用了索引，但是进行了回表查询。
using index：查找使用了索引，但是需要的数据在索引中都可以找到，不需要回表查询

前缀索引

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

-- 这里的column_name后的n表示使用该字段的多少个前缀字符作为索引
CREATE INDEX idx_xxx on table_name(column_name(n));

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

-- 计算取前缀长度为5时的选择性
SELECT COUNT(DISTINCT substring(email, 1, 5)) / count(1) from tb_user;

查询前缀索引流程

前缀索引中的key保存的仅是目标字段的前几个字符，在前缀索引（图中辅助索引）中通过b+tree查找到叶子节点后，根据叶子节点保存的主键值，查询聚集索引，然后再验证目标字段的条件，这样就完成了一个记录的匹配，然后又从前缀索引中取下一个匹配的叶子节点，直至不符合前缀索引的条件。

单列索引和联合索引

单列索引：即一个索引只包含单个列
联合索引：即一个索引包含多个列（==创建联合索引时要考虑字段的顺序==）
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

如果某个查询条件，既是联合索引的第一个字段，又有单列索引，那么MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

联合索引的存储情况

从上图可以看出，联合索引的节点中保存了与联合索引相关的全部字段。

索引的设计原则

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

SQL优化

插入优化

  • 批量插入
INSERT INTO tb_test VALUES(1,'Tom'), (2, 'Cat'), ...;
  • 手动提交事务
start transaction; -- or begin;
insert into ...;
insert into ...;
...
insert into ...;
commit;
  • ==主键顺序插入==，主键顺序插入的性能高于乱序插入
  • 大批量数据插入
    如果一次性需要插入大批量数据（万以上），使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下：
  1. 使用参数local-infile连接客户端，表明我们需要从文件中加载数据。
mysql --local-infile -u root -p
  1. 查看全局参数local-infile并设置为1，开启从本地加载文件导入数据的开关
# 查看是否开启
select @@local_infile;
# 设置为1
set global local_infile = 1;
  1. 执行load指令将准备好的数据，加载到表结构中。
load data local infile '/path/to/file' into table tb_user fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的。这种存储方式的表称为索引组织表（index-origanized table，IOT）。

页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。

如果所有数据都顺序插入，则不会发生页分裂。如果乱序插入，则会产生页分裂。

  1. 当要插入数据的页不足以存放时
  2. InnoDB会开辟一个新页，将之前页的50%的数据放入到新页中
  3. 然后将要插入的数据存放在新页中，接着修改双向链表的指针。

页合并
当删除一行记录时，实际记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的物理空间变得允许被其他记录声明使用。（逻辑删除）

当页中删除掉的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

Tips：MERGE_THRESHOLD合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键的设计原则

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

排序优化

当对排序进行优化时，执行计划的Extra字段可能出现的值为Using filesort和Using index，它们的含义如下：

  • Using filesort：通过表的索引或全表扫描，读取到满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
  • Using Index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

order by优化

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则（即排序字段顺序和索引字段顺序要一致）。
  • 尽量使用覆盖索引。
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASE/DESC）。
  • 如果不可避免的出现了filesort，大数据量排序时，可以适当增大排序缓存区大小sort_buffer_size（默认256k）。

Group By优化

  • 在分组操作时，可以通过索引来提高效率。
  • 分组操作时，索引的使用也是满足最左前缀法则的。

Tips：group by 需要满足的最左前缀法则比较宽松，它的范围不限于group by，而是整个where。举个例子，假设tb_user表有个联合索引index(age, dep)，那么select age, dep,count(1) from tb_user where age = 35 group by dep;是符合最左前缀法则的，会直接使用索引分组。

limit优化

一个常见又非常头疼的问题就是 limit 2000000, 10 ，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。

在MySQL中，LIMIT子句用于限制查询返回的行数。LIMIT条件在查询执行完成后、结果集返回给客户端之前生效。具体来说，LIMIT条件会在查询的结果集被组装完成后，按照指定的行数进行截取，并将截取后的结果返回给客户端

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

Count 优化

存储引擎

  • MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候直接返回这个数，效率很高；
  • InnoDB引擎，它执行count(*)的时候，需要把数据一行一行从引擎里面读出来，然后累积计数。

count的几种用法

  • count()是一个聚合函数，对应返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值加一，否则不加，最后返回累计值。

  • 用法：count(*)、count(主键)、count(字段)、count(1)

  • count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。

  • count(字段)：InnoDB会将字段取出来，如果字段为null，则不累加，如果字段不是null，则进行累加。

  • count(1)：InnoDB会遍历整张表，但不取值。服务层对于返回的每一行，放一个数字1进去，直接按行进行累加。

  • count(*)：InnoDB并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

==按照效率排序的话，count(字段) < count(主键) < count(1) 约等于 count(*)，所以尽量使用count(*)或count(1)==

Update优化

where中的条件列最好时存在索引，否则会升级为表锁。
在使用update对记录进行更新时，需要注意的是：InnoDB的行锁是针对索引加的锁，而不是记录，并且该索引不能失效，否则会从行锁升级为表锁。

视图、存储过程、触发器

视图

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗地讲，视图只是保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图操作命令
  • 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

tips: 中括号内的为可选项

  • 查询
-- 查看创建视图的语句
SHOW CREATE VIEW 视图名称;
-- 查看视图数据，和表操作相同
SELECT * FROM 视图名称...;
  • 修改
-- 方式一
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
-- 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
  • 删除
DROP VIEW [IF EXISTS] 视图名称 [, 视图名称...];

视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入、更新、删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，在特定条件下，它还会检查依赖视图中的规则以保证一致性。为了确定检查的范围，MySQL提供了两个选项：CASCADED和LOCAL，默认值CASCADED。

  • CASCADED：级联检查，会一直向下检查
  • LOCAL：只检查当前的视图
视图的更新

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

  1. 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
  2. DISTINCT
  3. GROUP BY
  4. HAVING
  5. UNION 或者 UNION ALL
作用

  • 简单：视图不仅可以简化用户对数据的理解，也可以简化它们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 安全：数据库可以授权，但不能授权到数据库特定行和特定的列上，通过视图用户只能查询和修改他们所能见到的数据。

  • 数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

特点

  • 封装、重用
  • 可以接受参数，也可以返回数据
  • 减少网络交互，提升效率
基本操作命令

创建

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
 -- SQL 语句
END;

调用

CALL 存储过程名称([参数]);

查看

-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFOMATION_SCHEMA_ROUTINES WHERE ROUTINE_SCHEMA='数据库名称';
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;

删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

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

-- 将语句结束符修改为$$，在mysql命令行中执行下面的语句
delimiter $$
MySQL中的变量
系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（global）、会话变量（session）。

查看系统变量

-- 查看所有系统变量，支持模糊匹配
SHOW [SESSION | GLOBAL] VARIABLES [LIKE ...];
-- 查看特定名称的系统变量
SELECT @@[SESSION. | GLOBAL.]var_name;

设置系统变量

SET [SESSION | GLOBAL] var_name = value;
SET @@[SESSION. | GLOBAL.]var_name = value;

==注意：==如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。mysql服务器重新启动之后，所设置的全局参数会失效，想要不失效，可以在/etc/my.cnf中配置。

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

赋值

SET @var_name = expr [, @var_name = expr, ...];

SET @var_name := expr [, @var_name := expr, ...];

SELECT @var_name := expr [, @var_name := expr, ...];

SELECT 字段名 INTO @var_name FROM table_name;

使用

SELECT @var_name;

==注意==：用户定义的变量无需对其进行声明或初始化。

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要用DECLARE关键字声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN...END块。

声明

DECLARE 变量名 变量类型 [DEFAULT value];

变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

赋值

SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名 ...;
参数

参数类型

用法

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

示例：

CREATE PROCEDURE transfer(INOUT score double)
BEGIN
	set score := score * 0.5;
END;
MySQL中的语句

IF
语法如下：

IF 条件1 THEN
	...
ELSEIF 条件2 THEN -- 可选
...
ELSE -- 可选
...
END IF;

CASE

  • 语法一
CASE case_value
	WHEN when_val THEN statement
	[WHEN when_val_1 THEN statement_1 ...]
	[ELSE statement_n]
END CASE;
  • 语法二
CASE
	WHEN when_condition THEN statement
	[WHEN when_condition_1 THEN statement_1 ...]
	[ELSE statement_n]
END CASE;

REPEAT
repeat 是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：

-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
	SQL逻辑
	UNTIL 条件
END REPEAT;

WHILE
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：

WHILE condition_0 DO
	 SQL逻辑
END WHILE;

LOOP
LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合下面两个语句使用：

  • LEAVE：配合循环使用，退出循环。
  • ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[lable:] LOOP
	SQL 逻辑
END LOOP [label];

LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环

游标
游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法如下：

  • 声明游标
DECLARE cursor_name CURSOR FOR 查询语句;
  • 打开游标
OPEN cursor_name;
  • 获取游标记录
FETCH cursor_name INTO 变量[, 变量...];
  • 关闭游标
CLOSE cursor_name;

当使用游标读数据的时候，并不能通过游标判断游标中是否还有数据。在MySQL可以通过条件处理程序来退出游标循环。

DECLARE cname varchar(100) DEFAULT '';
DECLARE cage int DEFAULT 0;
DECLARE c1 CURSOR FOR SELECT name, age FROM tb_user;

OPEN c1;

-- 当游标无数据时，会报错
WHILE True DO
	FETCH c1 INTO cname, cage;
	-- do something
END WHILE;
-- unexecute
CLOSE c1;

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

DECLARE handler_action HANDLER FOR condition_value[, condition_value]... statement;

handler_action：

  • CONTINUE：继续执行当前程序
  • EXIT：终止执行程序
    condition_value：
  • SQLSTATE sqlstate_value：状态码，如02000
  • SQLWARNING：所有以01开头的SQL STATE代码的简写
  • NOT FOUND：所有以02开头的SQL STATE代码的简写
  • SQLEXCEPTION：所有没有被SQL WARNING或NOT FOUND捕获的SQLSTATE代码的简写

可以将上面的例子改为如下：


DECLARE cname varchar(100) DEFAULT '';
DECLARE cage int DEFAULT 0;
DECLARE c1 CURSOR FOR SELECT name, age FROM tb_user;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE c1;

OPEN c1;

-- 当游标无数据时，会报错
WHILE True DO
	FETCH c1 INTO cname, cage;
	-- do something
END WHILE;
-- unexecute
CLOSE c1;

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
	-- SQL 语句
	RETURN ...;
END;

characteristic说明：该字段是用来对存储函数的特性进行说明，有些版本的MySQL必须提供该项，它的取值如下。

  • deterministic：相同的输入参数总是产生相同的结果
  • no sql：不包含sql语句
  • reads sql data：包含读取数据的语句，但不包含写入数据的语句

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

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

触发器类型

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

语法

  • 创建
create trigger trigger_name
 after | before insert | update | delete
 on tbl_name for each row -- 行级触发器
BEGIN
	stmt_set;
END;
  • 查看触发器
show triggers;
  • 删除触发器
drop trigger [schema_name.]trigger_name;

锁

介绍

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供多个用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

分类

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

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

全局锁

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

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

  • 全局锁 锁定命令
flush tables with read lock;
  • 备份数据库命令，需要在终端中执行
mysqldump -uroot -p database_name > filename.sql
  • 全局锁 解锁命令
unlock tables;

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

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

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

mysqldump --single-transaction -uroot -p database_name > filename.sql

表级锁

介绍

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

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

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

对于表锁，分为两类：

  1. 表共享读锁（read lock）：如果有读锁，所有的连接只能读取，不能写入
  2. 表独占写锁（write lock）：只有持有写锁的连接可读写表，其余不可读写

语法：

  1. 加锁
lock tables 表名.. read / write;
  1. 释放锁
unlock tables; -- / 客户端断开连接

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

元数据锁（meta data lock，MDL）

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

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

查看元数据锁：

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

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

总的来说，==引入意向锁是为了解决行锁与表锁的冲突问题==。

类型

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

特点

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

可以通过下面的SQL，查看意向锁及行锁的加锁情况：

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

行级锁

介绍

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

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

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

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

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

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

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

行锁的规则
![[行锁的类型.png]]

间隙锁/临键锁

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

  1. 索引上索引上等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。这里的间隙锁锁定的是不存在的记录应该插入位置的两边。
  2. 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁。
  3. 索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止。

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

InnoDB引擎

逻辑存储结构

表空间（Table Space）
ibd文件，一个MySQL实例可以对应多个表空间，用于存储记录、索引等数据。

段（Segment）
分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

区（Extent）
表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

页（Page）
页是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区。

行（Row）
InnoDB存储引擎数据是按行存放的。
行中保存的数据包括Trx_id、Roll_pointer以及列。

  • Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
  • Roll_pointer：每次对某条记录进行改动时，都会把旧的版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

InnoDB存储引擎架构

MySQL5.5版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。

内存架构

InnoDB的内存架构是上图的左边部分，包括四个结构，分别是Adaptive Hash Index、Buffer Pool、Change Buffer、Log Buffer。

Buffer Pool
缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型：

  • free page：空闲page，未被使用过
  • clean page：被使用page，但是数据没被修改过
  • dirty page：脏页，被使用过的page，而且数据被修改过，页中数据与磁盘的数据产生了不一致

Change Buffer
更改缓冲区（针对非唯一二级索引页），在执行DML语句时，如果这些数据页没有再Buffer Pool中，不会直接操作磁盘，而会将数据变更保存在Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，然后再将合并后的数据刷新到磁盘中。

==Change Buffer的意义是什么？==
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了==Change Buffer后，我们可以在缓冲池中进行合并处理，减少了磁盘的IO==。

Adaptive Hash Index
自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。

相关系统变量：innodb_adaptive_hash_index

Log Buffer
日志缓冲区，用来保存要写入到磁盘中的日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。

相关系统变量：innodb_log_buffer_size 缓冲区大小、innodb_flush_log_at_trx_commit 日志刷新到磁盘时间

  • innodb_flush_log_at_trx_commit：取值有
    • 1 日志在每次事务提交时写入并刷新到磁盘
    • 0 每秒将日志写入并刷新到磁盘一次
    • 2 日志在每次事务提交后写入，并每秒刷新到磁盘一次
磁盘架构

System Tablespace： 系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。（在MySQL5.x版本中还包含InnoDB数据字典、undolog等）

相关系统变量：innodb_data_file_path

File-Per-Table Tablespace： 每个表的文件表空间包含单个innoDB表的数据和索引，并存储在文件系统上的单个数据文件中。

相关系统变量：innodb_file_per_table

General Tablespace：通用表空间，需要通过create tablespace语法创建通用表空间，在创建表时，可以指定该表空间。

  • 创建通用表空间 create tablespace xxxx add datafile 'ts_name' engine=engine_name;
  • 使用通用表空间 create table xxx(...) tablespace ts_name;

Undo Tablespace： 撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小为16M），用于存储undo log日志。

Temporary Tablespace：InnoDB使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据。

Doublewrite Buffer Files 双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件（两个文件）中，便于系统异常时恢复数据。

Redo Log：重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用。

以循环方式写入重做日志文件，涉及到两个文件：ib_logfile0、ib_logfile1

后台线程架构

Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收。

IO Thread
在InnoDB存储引擎中大量使用来AIO（异步非阻塞IO，效率高于NIO）来处理IO请求，这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调。

Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，Purge Thread是用来专门回收这些无用的undo log。

Page Cleaner Thread
协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻塞。

事务原理

![[事务ACID特性是如何保证的.png]]

  • 原子性：通过undo log来保证
  • 一致性：通过redo log和undo log来保证
  • 持久性：通过redo log来保证
  • 隔离性：通过锁+MVCC来保证
Redo Log

InnoDB存储引擎通过redo log来保证事务的持久性。重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘（有专门的线程负责以一定的频率异步刷新脏页到磁盘），发生错误时，进行数据恢复使用。

当与事务相关的脏页成功刷新到磁盘后，事务相关的redo log也就无用了。

Undo Log

回滚日志，用于记录数据被修改前的信息，作用有两个：提供回滚和MVCC（多版本并发控制）。
undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log 在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024个undo log segment。

Undo Log可以保证事务的原子性，当事务执行失败时，通过执行undo log中保存的记录进行回滚。

MVCC（Multi-Version Concurrency Control）

当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select .. lock in share mode（共享锁）,select .. for update、update、insert、delete（排它锁）都是一种当前读。

==事务开启时，进行过快照读，后续执行了修改操作（update、insert等），则快照读会降级为当前读。==

快照读
简单的select（不加锁）就是快照读，快照读 读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
不同隔离级别下，生成的快照读是不同的：

  • read committed：每次select，都生成一个快照读。
  • repeatable read：开启事务后第一个select语句才是快照读的地方，之后的select语句都将在该快照上进行读取。
  • serializable：快照读会退化为当前读。

MVCC
全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

记录中的隐藏字段

一张表除了我们创建时指定的字段外，还包括以下的隐藏字段：

  • DB_TRX_ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
  • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
  • DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

我们可以通过mysql提供的一个小工具来查看表结构（包含隐藏字段）：ibd2sdi ibd文件，该命令将输出json格式的表结构字符串。

Undo Log版本链

回滚日志，在insert、update、delete的时候产生的便是数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即删除。

Undo Log版本链

不同事务或相同事务在对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧纪录，链表尾部是最早的旧纪录。

ReadView

ReadView（读视图）是快照读SQL执行时MVCC提取数据的依据，主要用来做可见性判断，记录并维护系统当前活跃的事务（未提交的）id。

ReadView中包含了四个核心字段：

  • m_ids：当前活跃的事务ID集合（当前未提交的事务集合）
  • min_trx_id：最小活跃事务ID
  • max_trx_id：预分配事务ID，创建ReadView时的当前最大事务ID+1（事务ID是自增的）
  • creator_trx_id：ReadView创建者的事务ID

ReadView属于创建它的事务，相当于一个标记，我们可以利用ReadView来遍历Undo Log版本链，利用ReadView访问版本链中的节点，如果符合访问规则，则停止遍历，该节点的数据能被当前事务所看到。

Undo Log版本链数据访问规则：（trx_id：版本链节点的事务ID，数据行中的DB_TRX_ID字段）

  1. trx_id == creator_trx_id：可以访问该版本。说明该版本的数据是当前事务更改的。
  2. trx_id < min_trx_id：可以访问该版本。说明当前节点早已提交（创建ReadView时，创建该undo log节点的事务已完成）
  3. trx_id > max_trx_id：不可以访问该版本。说明该undo log节点是在ReadView生成后才产生。
  4. min_trx_id <= trx_id <= max_trx_id and trx_id not in m_ids：可以访问该版本。说明事务在ReadView生成时已提交。

==不同的隔离级别，生成ReadView的时机不同：==

  • read committed(RC)：在事务中每一次读取数据时，使用当前读生成ReadView。这样就保证了每次读的时候都可以读到最新的数据（可能造成不可重复读问题）。
  • repeatable read(RR)：仅在事务中第一次快照读时生成ReadView，后续复用该ReadView。复用第一次快照读生成的ReadView造成的结果就是，可以保证在一个事务中前后读取数据保持一致。
RC隔离级别

RR隔离级别

在RR隔离级别下，仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

一致性、隔离性

InnoDB存储引擎通过由隐藏字段、Undo Log版本链、ReadView支持的MVCC（多版本并发控制）和锁来实现事务的隔离，通过redo log 和 undo log来保证数据的一致性。

MySql管理

系统数据库

常用工具

mysql

这里的mysql不是指mysql服务，而是mysql客户端工具。

mysql [options] [database]

可选的参数：

  • -u：指定用户名
  • -p：使用密码登录并指定密码
  • -h：指定MySQL主机IP
  • -P：指定MySQL主机端口
  • -e：不进入mysql控制台，直接执行mysql命令并返回结果，如mysql -uroot -p123456 -h127.0.0.1 -P3306 database_name -e 'select * from t1'

对于一些脚本文件连接数据库获取数据时，可以直接使用-e参数获取数据。

mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

mysqlbinlog

由于服务器生成的二进制日志文件以二进制格式保存，所以如果想要检查这些文本的格式，就要使用到mysqlbinlog日志管理工具。

mysqlshow

mysqlshow客户端对象查找工具，用来快速查找存在哪些数据库、数据库中的表、表中的列或者索引。

mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

示例命令：

# 将数据库dbname备份到dbname.sql文件
mysqldump -uroot -p123456 -hremote_ip dbname > dbname.sql

mysqldump中的-T参数后面可以跟要保存文件的目录，这个目录最好指定mysql信任的路径，不然会出现错误mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'，mysql信任的目录存放在mysql全局变量secure_file_priv中。

mysqlimport / source

mysqlimport是客户端数据导入工具，用来导入mysqldump加 -T 参数后导出的==文本文件==。

语法：

mysqlimport [options] db_name textfile1 [textfile2...]
# 示例
mysqlimport -uroot -p123456 test /tmp/city.txt

如果需要导入sql文件，可以使用mysql中的source指令：

source /root/xxx.sql

完结撒花🎉