Mysql高级

MySQL 存储引擎架构了解吗？

MySQL 存储引擎采用的是插件式架构，支持多种存储引擎，我们甚至可以为不同的数据库表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的，而不是数据库。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

  1. 所以我们用SQL语句对数据进行操作，好像是我们的SQL语句对数据进行了直接操作，但是呢，本质上是通过存储引擎来真正对数据进行操作。
  2. 存储引擎把怎么对数据操作什么的都封装好了，SQL语句更像是一个接口！你不用管存储引擎内部到底是怎样实现的，你只要会用SQL语句就好了！

Mysql的执行流程

Mysql基础架构分析

img

Server层：所有跨存储引擎的功能都在这层实现，比如存储过程，触发器，函数等，还有一个通用的binlog模块。

存储引擎：支持多个引擎（InnoDB、MyISAM、Memory）。其中InnoDB引擎有自由的redolog模块。

下面解释Server层里面的组件：

  • 连接器：主要和身份认证和权限相关，主要负责登录数据库，用户身份验证，权限等操作。如果用户名和密码通过，连接器会到权限表中查询该用户的权限（一直到断开，权限都是相同的）。

  • 查询缓存：在Mysql8.0之后就取消了这个组件，因为很少去用，它主要就是查询数据的时候，以 Key-Value 的形式缓存在内存中，会先在查询缓存中查询，如果查到则直接返回，否则执行后序的操作，并且也会存储到查询缓存中。

    • MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

  • 分析器：分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

    • 词法分析：提取关键字，提出查询的表，提出字段名，提出查询条件等。
    • 语法分析：主要是判断输入的SQL是否正确

  • 优化器：选择最优的执行方案去执行（比如有多个条件的时候，先执行哪个条件）

  • 执行器：选择执行方案后，开始执行，校验用户有没有权限，如果没有则返回错误，如果有则调用引擎接口，返回执行结果。

查询语句分析

分析下面这条查询语句：

1
select * from tb_student  A where A.age='18' and A.name=' 张三 ';

权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎

（1）先检查语句是否有权限，Mysql8.0之前先查询缓存，有直接返回，没有接着执行。

（2）通过分析器进行语法分析，提取上面的select，查询表名为tb_student，需要查询所有的列，查询条件是什么，然后判断是否有语法错误，如果没有则下一步

（3）接下来就是优化器，可能有以下两种方案，优化器选择一个效率最高的方案进行执行，

1
2
a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。
b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

（4）权限校验，如果没有权限则返回错误，如果有权限，则调用引擎接口，返回引擎结果。

更新语句分析

分析下面这条更新语句：

1
update tb_student A set A.age='19' where A.name=' 张三 ';

在更新的时候要引入日志模块，Mysql自带的日志模块是bin log（归档日志），InnoDB引擎还引入了一个自带的日志模块 redo log（重做日志）

分析器—->权限校验—->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit状态)

（1）通过分析器进行语法分析，更新操作，操作哪张表，条件是什么，检查有没有语法错误。

（2）权限校验，如果没有权限则返回错误信息，如果有权限则调用引擎接口执行。

（3）调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。

（4）执行器收到通过后记录bin log，然后调用引擎接口，提交redo log为提交状态。

（5）更新完成

为什么同时用redo log和bin log

redo log是InnoDB特有的，它有一个特殊的功能：如果数据库发生异常重启，之前提交的记录都不会丢失。

并不是说只用一个bin log日志不行，而是使用redo log来支持事务。

那如果说用两个日志，但不用这么复杂可不可以，没有prepare状态和提交状态？

  • **先写 redo log 直接提交，然后写 binlog**，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
  • **先写 binlog，然后写 redo log**，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binlog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。

假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？下面为Mysql的处理机制：

  • 判断 redo log 是否完整，如果判断是完整的，就立即提交。
  • 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。

这样就保证了数据的一致性。

MyISAM和InnoDB的区别

1.是否支持行级锁：InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度。

2.是否支持事务：MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别。

3.是否支持外键：MyISAM 不支持外键，而 InnoDB 支持

4.是否支持数据库异常崩溃后的安全恢复：MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持

5.是否支持 MVCC：MyISAM 不支持 MVCC，而 InnoDB 支持

6.索引实现不一样。

数据库的三大范式

  • 第一范式

    第一范式的目标是确保每列的原子性:如果每列都是不可再分的最小数据单元（也称为最小的原子单元），则满足第一范式（1NF）

    image-20220908124314451

  • 第二范式

    满足第一范式，表中非主键列不存在对主键的部分依赖 （也就是第二范式要求每个表只描述一件事情 ）

    什么叫“部分依赖”：

    如果确定一个表中的某个数据组合（A，B），则就可以确定该表中的其他另一个数据（C），则我们说：C依赖于（A，B）（此时A，B通常就是做出主键）。

    但：如果某个数据D，它只依赖于数据A，或者说，A一确定，则D也可以确定，此时我们就称为“数据D部分依赖于数据A——可见部分依赖是指某个非主键字段，依赖于联合主键字段的其中部分字段。

    img

    学生和课程作为联合主键，第二范式的要求非主键字段必须完全依赖主键，所以上表中，学分是依赖课程的，成绩和依赖学生的。要进行修改：

    img

    img

    img

    image-20220908124511218

  • 第三范式

    满足第一和第二范式，不存在对非主键列的传递依赖 ，非主键的属性对主键都是直接依赖，而不是间接依赖

    image-20220908124619358

什么是元组, 码, 候选码, 主码, 外码, 主属性, 非主属性？

元组 ： 元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。

码 ：码就是能唯一标识实体的属性，对应表中的列。

候选码 ： 若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。

主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。

外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。

主属性 ： 候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。

非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

什么是 ER 图？

我们做一个项目的时候一定要试着画 ER 图来捋清数据库设计，这个也是面试官问你项目的时候经常会被问到的。

ER 图 全称是 Entity Relationship Diagram（实体联系图），提供了表示实体类型、属性和联系的方法。

ER 图由下面 3 个要素组成：

  • 实体 ：通常是现实世界的业务对象，当然使用一些逻辑对象也可以。比如对于一个校园管理系统，会涉及学生、教师、课程、班级等等实体。在 ER 图中，实体使用矩形框表示。
  • 属性 ：即某个实体拥有的属性，属性用来描述组成实体的要素，对于产品设计来说可以理解为字段。在 ER 图中，属性使用椭圆形表示。
  • 联系 ：即实体与实体之间的关系，这个关系不仅有业务关联关系，还能通过数字表示实体之间的数量对照关系。例如，一个班级会有多个学生就是一种实体间的联系。

下图是一个学生选课的 ER 图，每个学生可以选若干门课程，同一门课程也可以被若干人选择，所以它们之间的关系是多对多（M: N）。另外，还有其他两种实体之间的关系是：1 对 1（1:1）、1 对多（1: N）。

image-20230416182424886

drop、delete 与 truncate 区别

  • drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。-DDL
  • truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。-DDL
  • delete（删除数据） : delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。-DML

truncate 和不带 where``子句的 delete、以及 drop 都会删除表内的数据，但是 **truncate 和 delete 只删除数据不删除表的结构(定义)，执行 drop 语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

DML 语句和 DDL 语句区别：

  • DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
  • DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

数据库设计通常分为哪几步?

需求分析 : 分析用户的需求，包括数据、功能和性能需求。

概念结构设计 : 主要采用 E-R 模型进行设计，包括画 E-R 图。

逻辑结构设计 : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。

物理结构设计 : 主要是为所设计的数据库选择合适的存储结构和存取路径。

数据库实施 : 包括编程、测试和试运行

数据库的运行和维护 : 系统的运行与数据库的日常维护。

索引

https://www.jb51.net/article/257835.htm

什么是索引

索引是对数据库表中的一列或多列的值进行排序的一种数据结构，使用索引可以快速访问数据表中的特定信息。

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

优点 ：

  • 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
  • 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

索引的数据结构

hash表

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

为何能够通过 key 快速取出 value呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

image-20221005221522286

但是hash算法存在hash冲突问题,也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。

为什么MySQL 没有使用hash表这个数据结构呢？

1.Hash 冲突问题 ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点： 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

B+树索引

img

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

二者的区别

哈希索引一般用于精确等值查询但不支持排序，B+树用于精确等值查询之外的查询（范围查询and模糊查询等）。

B树和B+树的区别

区别：

  • B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  • B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  • B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

B树索引

  • B树中的内部节点和叶子节点均存放键和值，而B+树的内部节点只有键没有值，叶子节点存放所有的键和值。
  • B+树的叶子节点相连，方便顺序检索

image-20221006121109983

image-20221006121114693

索引的种类

image-20220908115444190

数据库为什么使用B+树而不是B树

image-20220908120042584

什么是聚簇索引，什么是非聚簇索引

聚簇索引：将数据和索引放到一起存储，索引结构的叶子节点保留了数据行。

非聚簇索引：将数据和索引分开存储，索引叶子节点存储的是指向数据行的地址。（叶子节点存储的是主键）

（1）聚簇索引的优缺点

优点 ：

  • 查询速度非常快 ：聚簇索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
  • 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点 ：

  • 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

（2）非聚簇索引的优缺点

优点 ：

更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的

缺点 ：

  • 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据
  • 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

img

也就是说通过非聚簇索引找到主键，再通过聚簇索引找到主键对应的数据。

当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

img

  • 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
  • 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
  • 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

image-20220908120558009

image-20220908120605893

索引的使用场景

image-20220908121149094

索引在什么情况下会失效

image-20220908123400865

创建索引的原则

  • 选择唯一性索引： 唯一性索引一般基于 Hash 算法实现， 可以快速、唯一地定位某条数据。
  • 为经常需要排序、分组和联合操作的字段建立索引。
  • 为常作为查询条件的字段建立索引。
  • 限制索引的数量：索引越多，数据更新表越慢，因为在数据更新时会不断计算和添加索引。
  • 尽量使用数据量少的索引：如果索引的值很长，则占用的磁盘变大，查询速度会受到影响。
  • 尽量使用前缀来索引：如果索引字段的值过长， 则不但影响索引的大小，而且会降低索引的执行效率， 这时需要使用字段的部分前缀来作为索引。
  • 删除不再使用或者很少使用的索引。
  • 尽量选择区分度高的列作为索引：区分度表示字段值不重复的比例。
  • 索引列不能参与计算：带函数的查询不建议参与索引。
  • 尽量扩展现有索引： 联合索引的查询效率比多个独立索引高。

索引举例

  • 创建表test_table1,并分别是在id字段上名称为uniqidx的唯一索引；在name和address字段上的组合索引；在description字段上长度为30的普通索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table test_table1    -> (

    -> id int not null primary key auto_increment,

    -> name char(100) not null,

    -> address char(100) not null,

    -> description char(100) not null,

    -> unique index uniqidx(id),

    -> index MultiColidx(name(20),address(30) ),

    -> index Comidx(description(30));
  • 创建表test_table2，存储引擎为MyISAM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table test_table2    -> (

    -> id int not null primary key auto_increment,

    -> firstname char(100) not null,

    -> middlename char(100) not null,

    -> lastname char(100) not null,

    -> birth date not null,

    -> title char(100) null

    -> )ENGINE=MyISAM;
  • 使用alter table 语句在表test_table2的birth字段上建立名称为ComDateIdx的普通索引
1
alter table test_table2 add index ComDateidx(birth);
  • 使用alter table语句在表test_table2的id字段上添加名称为Uniqidx2的唯一索引
1
alter table test_table2 add unique index Uniqidx(id);
  • 使用create index 在firstname和middlename两个字段上建立名称为 MultiColidx2的组合索引
1
create index MultiColidx2 on test_table2(firstname,middlename);
  • 使用create index在title字段上建立名称为FTidx的全文索引
1
create fulltext index ftidx on test_table2(title);
  • 使用alter table语句删除表test_table1中名称为Uniqidx的唯一索引
1
alter table test_table1 drop index uniqidx;
  • 使用drop index语句删除表test_table2中名称为MultiColidx2的组合索引
1
drop index MultiColidx2 on test_table2;

最左前缀法则

对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

注意： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

下面以一个表的例子进行展示，查询表中的索引，看是否有联合索引

1
show index from tb_user

image-20221126140105574

（1）符合最左原则，成功！红框中的是联合索引，最左边的列是profession，因此下面的查询都是有效的：

1
2
3
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31;
explain select * from tb_user where profession = '软件工程';

image-20221126140422974

（2）不符合最左原则，失败！如果profession列没有用到的时候，则索引失效

1
2
explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';

image-20221126140549906

（3）跨列查询

1
explain select * from tb_user where profession = '软件工程' and status = '0';

image-20221126140922771

根据索引使用的长度，索引的部分生效，只用到了profession列的部分

思考：如果顺序不同的话是否可以执行索引，答案是：可以！

1
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

image-20221126141104785

出现 > 或 < 时，>或<的列失效，其他的不是失效

mysql8的时候，如果条件语句中有or，并且两边都有索引，则走索引，否则失效。而mysql5无论两边是否有索引，出现or就会失效

对于is null 和 is not null，按照情况说是否走索引，会判断走索引块还是查询表块

Using where 和 Using index

Using where和USing index：查找使用到了索引，需要的数据都能在索引列中找到，不需要回表查询

1
explain select id, profession from tb_user where profession = '软件工程' and age = 31 and status = '0' ;

Using index：使用覆盖索引的时候就会生效。

using index condition：查找使用了索引，不需要回表查询，因为要过滤的字段在索引中

1
explain select id,profession,age, status, name from tb_user where profession = '软件工程' and age = 31 and status = '0' ;

Using Where：在查找使用索引的情况下，需要回表去查询所需的数据

1
explain select * from tb_user where age = 31 and name='zw'; #删除了name的索引

id是主键，是一个聚集索引。 name字段建立了普通索引，是一个二级索引（辅助索引）。

执行SQL : select * from tb_user where id = 2;

img

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

执行SQL：selet id,name from tb_user where name = 'Arm';

img

执行SQL：selet id,name,gender from tb_user where name = 'Arm';

img

思考题： 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:

1
select` `id,username,``password` `from` `tb_user ``where` `username =``'zhangsan'``;

答案: 针对于 username, password建立联合索引, sql为:
create index idx_user_name_pass on tb_user(username,password);
这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。

前缀索引

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

语法：create index idx_xxxx on table_name(column(n)) ;

实例：

create index idx_email_5 on tb_user(email(5));

image-20221126145423129

查询流程：

img

单列索引与联合索引

  • 单列索引：即一个索引只包含单个列。

  • 联合索引：即一个索引包含了多个列。

    如果查询条件存在两个单列索引的话，只能用到一个索引并进行回表查询，

    但创建联合索引后，就走了联合索引，而在联合索引中包含 phone、name的信息，在叶子节点下挂的是对应的主键id，所以查询是无需回表查询的。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

img

覆盖索引

覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。说白了就是避免回表查询

事务篇

什么是数据库事务

数据库事务执行一系列基本操作，这些基本操作组成一个逻辑工作单元一起向数据库提交，要么都执行，要么都不执行。事务是一个不可分割的工作逻辑单元。

事务的四大特性

  • 原子性：包含事务的操作要么全部执行成功，要么全部失败执行回滚
  • 一致性：事务在执行前后状态是一致的
  • 隔离性：并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
  • 持久性：⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

数据库并发一致性问题

当多个事务并发执行时，会出现以下问题：

  • 脏读：事务A更新了数据，但没有提交，事务B读取了事务A更新的数据，然后事务A进行回滚，则数据B读到的数据为脏读数据。
  • 不可重复读取：事务A对数据进行多次读取，事务B在事务A的多次读取中，更新了数据并提交，导致事务A多次读取到数据的数据不一致。
  • 幻读：事务A读取了数据库中的数据后，事务B向事务A中插入了几行数据，事务A再次读取数据时发现多了几条数据，和之间读取的数据不一致。
  • 丢失修改：事务A和事务B对同一个数据进行修改，事务A 先修改随后事务B再修改覆盖了事务A的修改。

数据库的隔离级别

数据库的隔离级别可以解决数据库的脏读、幻读、不可重复读的问题

  • 未提交读：一个事务在提交之前，它的修改对其他事务也是可见的。
  • 提交读：一个事务提交之后，才可能被其他事务看到。
  • 可重复读：同一事务中多次读取到的数据是一致的
  • 串行化：加锁实现，事务串行执行

image-20220908130942497

Mysql的默认隔离级别：可重复读是怎么实现的

InnoDB是通过维护两个隐藏列来实现mvcc，隐藏列记录了数据行创建版本号和删除版本号

mvcc在可重复读级别下的具体实现：

  • 查询：需要满足两个条件，

    • 创建版本号小于等于当前事务的版本号，这样可以保证查询到的数据是事务开始之前就已经存在的，或者由该事务创建和修改的。
    • 删除版本号要大于等于当前事务的版本号，可以保证在该事务之前是存在的。

  • 插入：插入的每条数据将创建版本号与该事务的版本号一致。

  • 删除：删除每条数据，要将删除版本号与该事务版本号一致。

  • 修改：将创建版本号修改为事务版本号，将删除版本号修改为事务版本号。

数据的锁

什么是数据库的锁

有并发事务时，保证事务的访问顺序机制是锁机制

数据库锁的类型

image-20220908131726920

数据库的乐观锁和悲观锁

image-20220908132425625

MySql锁

表级锁和行级锁

MyISAM 仅仅支持表级锁(table-level locking)，一锁就锁整张表，这在并发写的情况下性非常差。

InnoDB 不光支持表级锁(table-level locking)，还支持行级锁(row-level locking)，默认为行级锁。行级锁的粒度更小，仅对相关的记录上锁即可（对一行或者多行记录加锁），所以对于并发写入操作来说， InnoDB 的性能更高.

表级锁和行级锁对比 ：

  • 表级锁： MySQL 中锁定粒度最大的一种锁，是针对非索引字段加的锁，对当前操作的整张表加锁,其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。
  • 行级锁： MySQL 中锁定粒度最小的一种锁，是针对索引字段加的锁，只针对当前操作的行记录进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁（Share Lock，S 锁）和排他锁（Exclusive Lock，X 锁）这两类：

  • 共享锁（S 锁） ：又称读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取（锁兼容）。
  • 排他锁（X 锁） ：又称写锁/独占锁，事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条事务加任何类型的锁（锁不兼容）。

手动加锁:

1
2
3
4
# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向共享锁和意向排他锁

  • 意向共享锁（IS）：当需要对数据加行级读锁时，会像整个表加意向读锁。
  • 意向排他锁（IX）：当需要对数据加行级写锁时，会像整个表加意向写锁。

为什么要加入意向锁：为了告诉你当前表中已经有了共享锁/排他锁，就没有必要加表锁了，起到一个标识的作用，提高表锁的效率。

如果需要对表加锁的时候，可以快速遍历表是否有行锁，避免用遍历的方式检查是否上行锁。

  • 如果意向锁是行锁，则需要遍历每一行数据去确认；
  • 如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。

MySQL的三大日志

mysql比较重要的三大日志为:二进制日志 binlog（归档日志）和事务日志 redo log（重做日志）和 undo log（回滚日志）。

redo log（重做日志）

是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

bin log(归档日志)

binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

数据库执行过慢怎么办

  • SQL及索引优化

    （1）开启慢查询，查找哪条执行语句过慢。

    1
    2
    log-slow-queries=/data/mysqldata/slow-query.log
    long_query_time=3
    • log-slow-queries参数为慢查询日志存放的位置，一般这个目录要有mysql的运行账号的可写权限，一般都将这个目录设置为mysql的数据存放目录。
    • long_query_time=5中的5表示查询超过五秒才记录。

    （2）通过Explain分析，优化语句。

  • 数据库表结构优化

    • 尽量将表字段定义为NOT NULL约束，这时由于在MySQL中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得很复杂。

    • 对于只包含特定类型的字段，可以使用enum、set 等数据类型。

    • 数值型字段的比较比字符串的比较效率高得多，字段类型尽量使用最小、最简单的数据类型。例如IP地址可以使用int类型。

    • 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED。但对整数类型指定宽度，比如INT(11)，没有任何用，因为指定的类型标识范围已经确定。

    • VARCHAR的长度只分配真正需要的空间。

    • 尽量使用TIMESTAMP而非DATETIME，但TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

    • 单表不要有太多字段，建议在20以内。

    • 合理的加入冗余字段可以提高查询速度。

  • 硬件优化

四. 总结

（1）首先数据库优化的主要思路，是先找到当前数据库的瓶颈所在，然后再针对具体问题具体优化；

（2）由于优化成本限制，所以要考虑SQL语句及索引优化，通过慢查询日志发现项目中是哪条SQL语句导致了查询变慢；

（3）然后通过Explain执行计划分析这条要优化的SQL语句到底是哪里有问题，再进行优化；

（4）对于有索引的字段，要在查询条件中遵循最左匹配原则，确保能够命中索引；

（5）然后再考虑优化表结构，对于单表500万条以上数据的大表，可以进行水平分表，比如使用数据库中间件Mycat进行分表；

（6）最后再考虑是否是服务器硬件导致的瓶颈，可以通过Zabbix监控软件进行分析，优化服务器硬件配置。

数据库的优化

https://blog.csdn.net/orecle_littleboy/article/details/88534160

Sql中in包含的值不应该过多

SELECT语句务必指明字段名称

当只需要一条数据的时候，使用limit 1

如果限制条件中其他字段没有索引，尽量少用or

尽量用union all代替union

避免在where子句中对字段进行null值判断

不建议使用%前缀模糊查询

避免在where子句中对字段进行表达式操作

禁止使用 order by rand() 进行随机排序

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

对于连续的数值，能用 between 就不要用 in 了：

Mysql怎么保证事务的持久性

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。

为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

img

Mysql怎么保证事务的原子性

是利用Innodb的undo log。
undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

是当事务回滚时能够撤销所有已经成功执行的sql语句

InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

例如:
(1)当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2)当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3)当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操
undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

Mysql怎么保证事务的隔离性

有两种方法MVCC和LBCC保证事务的隔离性，至于到底使用的哪种，就看程序员的sql语句是怎样写的。

MVCC (Multi-Version Concurrency Control) 多版本并发控制.

建立一个快照，同一个事务无论查多少次都是一个结果。某一个事务第一次查询确认了快照之后，无论后面的事务插入数据，删除数据还是更新数据，都不会影响该事务的查询结果，只能看到最初建立快照时饿情况。

(1)MVCC的底层支持，InnoDB为每个表提供了三个隐藏的字段以及事务id和删除版本号的使用

在这里插入图片描述

(2)MVCC的底层是read view（一致性视图）。

不同的表类型生成的时机不同：

  • RR repeat read 可重复读: read view 是在事务第一次查询的时候建立（之后 read view 就不会变了）；

  • RC read commit 已提交读: read view 是在事务每次查询的时候建立（每次查询都重新生成一个 read view ）；

(3)readview一致性视图的结构

在这里插入图片描述

readview一致性视图的应用

  • 对于某个事务trx_id，如果这个trx_id=creator_trx_id，表示这个readview就是这个trx_id创建的，自然可以访问这个trx_id

  • 对于某个事务trx_id，如果这个trx_id<min_trx_id，表示这个trx_id 比最小的未提交的事务id还小，表示这个事务id是已提交的，自然可以访问这个trx_id

  • 对于某个事务trx_id，如果这个trx_id>max_trx_id，表示这个trx_id 比最大的未提交的事务id还大，表示这个事务id是未提交的，自然不可以访问这个trx_id

  • 对于某个事务trx_id，如果这个max_trx_id > trx_id>min_trx_id，表示这个 trx_id 比在这个范围内，如果在 m_ids ,表示未提交，不可访问，如果不在 m_ids ，表示已提交，可以访问

（4）MVCC的局限

只作用于RC和RR隔离级别。

repeat read特点：只保存第一次查询的视图；
repeat read优点：解决了幻读问题（第一次生成视图之后，不受 update/insert/delete 影响）；
repeat read缺点：不能查询到最新的实时数据。

MVCC的局限：对于RR repeat read 可重复读，只能保存第一次查询的视图，不能查询到最新的实时数据，要想实时查询到最新的实时数据，只能 LBCC ，就是加锁。

LBCC(Lock-Base Concurrency Control)基于锁的并发控制.

使用加锁的方式来保证并发下数据的一致性，实现了事务隔离

（1）表锁到行锁

表锁和行锁的区别：表锁粒度大，行锁效率高，行锁冲突概率小，行锁并发性能强，所以行锁优于表锁。

（2）四种基本锁

共享锁，独占锁，意向共享锁，意向独占锁。

对于程序员给表加上行锁（读共享锁 写独占锁），
如果存在索引，锁住的索引，如果where条件中有索引列，仅锁住where条件命中的这一行或多行数据；如果where条件中没有索引列 或者 没有根本就没有where子句，会锁住整个表（连插入都插入不进去了 解决幻读问题）。
如果不存在索引，锁住的隐藏的rowid，此时会锁住整个表（连插入都插入不进去了 解决幻读问题）。

（3）三种高级锁

在这里插入图片描述

  • record记录锁

    在这里插入图片描述

  • 间隙锁

    那么幻读能仅通过行锁解决么？答案是否定的，如上面示例，首先说明一下，select xx for update（当前读）是将所有条件涉及到的（符合where条件）行加上行锁。但是，就算我在select xx for update 事务开启时将所有的行都加上行锁。那么也锁不住Session C新增的行，因为在我给数据加锁的时刻，压根就还没有新增的那行，自然也不会给新增行加上锁。

    所以要解决幻读，就必须得解决新增行的问题。

    现在你应该明白了，产生幻读的原因是：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 LOL，初始化插入了 7 个记录，这就产生了 8 个间隙。

    在这里插入图片描述

  • 临键锁

    在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。MySQL将行锁 + 间隙锁组合统称为 next-key lock，通过 next-key lock 解决了幻读问题。

    在这里插入图片描述

（4）四种隔离级别的锁，哪种mvcc,哪种加锁

mvcc只有在RC RR中有，

  • 在read uncommited(读未提交)啥都没有，没有锁，没有mvcc，
  • 在rc（读已提交）里面，有mvcc，有锁，只有记录锁，没有间隙锁，所以无法解决幻读问题
  • 在rr（可重复读）里面，有mvcc，有锁，有记录锁，有间隙锁，可以解决幻读问题
  • 在serializable（串行化），只有锁，没有mvcc

Mysql中explain

返回执行计划的信息

select语句前面加explain关键字，执行mysql就会返回执行计划的信息，而不是执行mysql。

exception中的列：

img

（1）id：id列的编号是select的序列号，有几个select就有几个id

（2）select_type：对应行的查询是简单查询还是复杂查询。

simple：不包含子查询和union的简单查询

primary：复杂查询中的最外层的查询

subquery：包含在select中的子查询（不在from中的子句中，可以是where后的子查询）

derived：包含在from中的子查询

（3）table：表示当前行访问的是哪张表，

（4）partitions：查询将匹配记录的分区，如果是非分区表，则为null。

（5）type：表示访问类型，就是mysql如何查找表中的行，最优到最差：system > const > eq_ref > ref > range > index > all。

system：主键索引或者唯一索引，表中只有一条元组匹配时。

const：主键索引或者唯一索引上的等值查询。

eq_ref：命中主键索引或唯一索引join查询，等值连接，前表的每一行，后表只有一行命中。

ref：使用了普通索引，没有使用主键索引和唯一索引，可能有多行命中。

range：索引上的范围扫描，相比index的全文索引扫描有范围限制，因此要优于index。

index：扫描全部索引

all：全表扫描

（6）possible_keys：显示查询中可能用到的索引，如果为null，表示没有用到相关索引。

（7）key：实际查询用到的索引，。

（8）key_len：具体使用了索引的哪些列，索引中使用的字节数

（9）ref：索引中使用到的列或者常量

（10）row：mysql查询中估计要读取的行数

（11）extra：额外的信息

如何保证缓存与数据库的双写一致性？

最经典的缓存+数据库读写的模式，就是 Cache Aside Pattern。- 读的时候，先读缓存，缓存没有的话，就读数据库，然后取出数据后放入缓存，同时返回响应。- 更新的时候，先更新数据库，然后再删除缓存。

为什么是删除缓存，而不是更新缓存？

举个栗子，一个缓存涉及的表的字段，在 1 分钟内就修改了 20 次，或者是 100 次，那么缓存更新 20 次、100 次；但是这个缓存在 1 分钟内只被读取了 1 次，有大量的冷数据。实际上，如果你只是删除缓存的话，那么在 1 分钟内，这个缓存不过就重新计算一次而已，开销大幅度降低。用到缓存才去算缓存

明确了是删除缓存后，目前存在两种选择:

  • 先更新数据库，再删除缓存
  • 先删除缓存，再更新数据库

（1）先更新数据库，再删除缓存

问题：更新数据库成功，线程出现问题，缓存删除失败，缓存中的是旧数据，数据不一致，有两种解决方式；失败重试和异步更新

失败重试：把删除的key放入到消息队中，从消息队列中进行删除，（有个缺点，首先会对业务代码造成入侵，其次引入了消息队列，增加了系统的不确定性。）

MySQL和Redis的数据一致性问题_一致性问题_04

异步更新：因为更新数据库时会往 binlog 中写入日志，所以我们可以启动一个监听 binlog变化的服务（比如使用阿里的 canal开源组件），然后在客户端完成删除 key 的操作。如果删除失败的话，再发送到消息队列。

（2）先删除缓存，再更新数据库

问题：如果先删除Redis缓存数据，然而还没有来得及写入MySQL，另一个线程就来读取。这个时候发现缓存为空，则去Mysql数据库中读取旧数据写入缓存，此时缓存中为脏数据。出现了数据不一致的问题。可以采用延时双删的策略解决。

MySQL和Redis的数据一致性问题_缓存_05

延时双删：就是更新数据库之后，再删除一次缓存。

MySQL和Redis的数据一致性问题_缓存_06

数据库的连接

又称多表查询，当查询的字段分别来自于不同表时，就会用到连接查询。

连接的分类

1
2
3
4
5
6
内连接：inner
外连接：
	左外：left outer
	右外：right outer
	全外（用的较少）
交叉连接：cross（用的较少）相当于做了一个笛卡尔乘积

内连接：

image-20230412190611640

右外连接：

image-20230412190743985

左外连接：

image-20230412190723458

全连接：

image-20230412190747912

内连接

基本语法：

1
2
3
4
select column
from table 1
inner join table 2
on 连接条件

等值连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 1 查询员工名、部门名
# 其中员工名在employees表，部门名在departments表
SELECT last_name,department_name
FROM departments d
JOIN  employees e
ON e.`department_id` = d.`department_id`;

#2.查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';

#3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT city, COUNT(*) 部门个数 
FROM departments d
INNER	JOIN locations l
ON d.`location_id` = l.`location_id`
GROUP BY city
HAVING 部门个数 > 3;

#4.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

非等值连接

1
2
3
4
5
6
7
8
#1. 查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT COUNT(*),grade_level
FROM employees e
 JOIN job_grades g
 ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;

自连接

1
2
3
4
5
6
7
8
9
10
11
12
#1.查询员工的名字、上级的名字
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`;

#2.查询姓名中包含字符k的员工的名字、上级的名字 
 SELECT e.last_name,m.last_name
 FROM employees e
 JOIN employees m
 ON e.`manager_id`= m.`employee_id`
 WHERE e.`last_name` LIKE '%k%';

外连接

用于查询一个表中有，另一个表没有的记录

左外连接

1
2
3
4
5
6
# 查询没有员工的部门的信息
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;

右外连接

1
2
3
4
5
SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;

上述两种方法得到结果一致，部门信息在departments表中，departments表没有employee_id，这时我们想要的结果是部门信息＋员工id，因此，如果使用左外连接，则departments为主表，右外连接则employees为主表。

Mysql
Mysql高级
http://example.com/2022/09/05/Mysql高级/
作者
zlw
发布于
2022年9月5日
许可协议