与或非's Blog

MySQL

基础

什么是内连接、外连接、交叉连接、笛卡尔积呢？

  • 内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。

  • 外连接（outer join）：不只取得两张表中满足存在连接匹配关系的记录，还包括某张表（或两张表）中不满足匹配关系的记录。

  • 交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，它是笛卡尔积在SQL中的实现，如果A表有m行，B表有n行，那么A和B交叉连接的结果就有m*n行。

笛卡尔积：是数学中的一个概念，例如集合A={a,b}，集合B={1,2,3}，那么

A✖ B={<a,o>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}。

MySQL 的内连接、左连接、右连接有有什么区别？

MySQL的连接主要分为内连接和外连接，外连接常用的有左连接、右连接。

  • inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。

  • left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

  • right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

说一下数据库的三大范式？

第一范式：数据表中的每一列（每个字段）都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。

第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。

varchar与char的区别？

char：

  • char表示定长字符串，长度是固定的；

  • 如果插入数据的长度小于char的固定长度时，则用空格填充；

  • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

  • 对于char来说，最多能存放的字符个数为255，和编码无关

varchar：

  • varchar表示可变长字符串，长度是可变的；

  • 插入的数据是多长，就按照多长来存储；

  • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

  • 对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用

varchar更合适一些。

一条SQL查询语句的执行顺序？

1. FROM ：对FROM子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表VT1

2. ON ：对虚拟表VT1应用ON筛选，只有那些符合的行才被插入虚拟表VT2中

3. JOIN ：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止

4. WHERE ：对虚拟表VT3应用WHERE过滤条件，只有符合的记录才被插入虚拟表VT4中

5. GROUP BY ：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5

6. CUBE|ROLLUP ：对表VT5进行CUBE或ROLLUP操作，产生表VT6

7. HAVING ：对虚拟表VT6应用HAVING过滤器，只有符合的记录才被插入虚拟表VT7中。

8. SELECT ：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中

9. DISTINCT ：去除重复数据，产生虚拟表VT9

10. ORDER BY ：将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10。11）

11. LIMIT ：取出指定行的记录，产生虚拟表VT11，并返回给查询用户

数据库架构

说说 MySQL 的基础架构

MySQL逻辑架构图主要分三层：

  • 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。

  • Server层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

  • 存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

一条 SQL 查询语句在 MySQL 中如何执行的？

  • 先检查该语句 是否有权限 ，如果没有权限，直接返回错误信息，如果有权限会先查询缓存 (MySQL8.0 版本以前)。

  • 如果没有缓存，分析器进行 语法分析 ，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。

  • 语法解析之后，MySQL的服务器会对查询的语句进行优化，确定执行的方案。完成查询优化后，按照生成的执行计划 调用数据库引擎接口 ，返回执行结果。

存储引擎

MySQL有哪些常见存储引擎？

常用引擎包括：MYISAM、Innodb、Memory、MERGE

  • MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎。
  • Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些。
  • Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表。
  • MERGE：是一组MYISAM表的组合

日志

MySQL日志文件有哪些？分别介绍下作用？

MySQL日志文件有很多，包括 ：

  • 错误日志 （error log）：错误日志文件对MySQL的启动、运行、关闭过程进行了记录，能帮助定位MySQL问题。

  • 慢查询日志 （slow query log）：慢查询日志是用来记录执行时间超过long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

  • 一般查询日志 （general log）：一般查询日志记录了所有对MySQL数据库请求的信息，无论请求是否正确执行。

  • 二进制日志 （bin log）：关于二进制日志，它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。

还有两个InnoDB存储引擎特有的日志文件：

  • 重做日志 （redo log）：重做日志至关重要，因为它们记录了对于InnoDB存储引擎的事务日志。

  • 回滚日志 （undo log）：回滚日志同样也是InnoDB引擎提供的日志，顾名思义，回滚日志的作用就是对数据进行回滚。当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志；如果事务执行失败或调用了rollback，导致事务需要回滚，就可以利用undo log中的信息将数据回滚到修改之前的样子。

binlog和redo log有什么区别？

  • bin log会记录所有与数据库有关的日志记录，包括InnoDB、MyISAM等存储引擎的日志，而redo log只记InnoDB存储引擎的日志。

  • 记录的内容不同，bin log记录的是关于一个事务的具体操作内容，即该日志是逻辑日志。而redo log记录的是关于每个页（Page）的更改的物理情况。

  • 写入的时间不同，bin log仅在事务提交前进行提交，也就是只写磁盘一次。而在事务进行的过程中，却不断有redo ertry被写入redo log中。

  • 写入的方式也不相同，redo log是循环写入和擦除，bin log是追加写入，不会覆盖已经写的文件

一条更新语句怎么执行的了解吗？

更新语句的执行是Server层和引擎层配合完成，数据除了要写入表中，还要记录相应的日志。

1. 执行器先找引擎获取ID=2这一行。ID是主键，存储引擎检索数据，找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

4. 执行器生成这个操作的binlog，并把binlog写入磁盘。

5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

从上图可以看出，MySQL在执行更新语句的时候，在服务层进行语句的解析和执行，在引擎层进行数据的提取和存储；同时在服务层对binlog进行写入，在InnoDB内进行redo log的写入。

不仅如此，在对redo log写入时有两个阶段的提交，一是binlog写入之前 prepare 状态的写入，二是binlog写入之后 commit 状态的写入。

SQL 优化

慢SQL如何定位呢？

  • 慢查询日志 ：开启MySQL的慢查询日志，再通过一些工具比如mysqldumpslow去分析对应的慢查询日志，当然现在一般的云厂商都提供了可视化的平台。

  • 服务监控 ：可以在业务的基建中加入对慢SQL的监控，常见的方案有字节码插桩、连接池扩展、ORM框架过程，对服务运行中的慢SQL进行监控和告警。

有哪些方式优化慢SQL？

慢SQL的优化，主要从两个方面考虑，SQL语句本身的优化，以及数据库设计的优化。

  • 避免不必要的列

    这个是老生常谈，但还是经常会出的情况，SQL查询的时候，应该只查询需要的列，而不要包含额外的列，像 slect * 这种写法应该尽量避免。

  • 分页优化

  • 索引优化

    合理地设计和使用索引，是优化慢SQL的利器。

    • 利用覆盖索引

      InnoDB使用非主键索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引。

      例如对于如下查询：

      1
      select name from test where city='上海'

      我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取。

      1
      alter table test add index idx_city_name (city, name);

    • 低版本避免使用or查询

      在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题。

    • 避免使用 != 或者 <> 操作符

      SQL中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即使比较的字段上有索引。

      解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

      例如，把 column<>’aaa’，改成column>’aaa’ or column<’aaa’ ，就可以使用索引了

    • 适当使用前缀索引

      适当地使用前缀所云，可以降低索引的空间占用，提高索引的查询效率。

      比如，邮箱的后缀都是固定的“ @xxx.com ”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

      1
      alter table test add index index2(email(6));

      PS:需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引。

    • 避免列上函数运算

      要避免在列字段上进行算术运算或其他表达式运算，否则可能会导致存储引擎无法正确使用索引，从而影响了查询的效率。

    • 正确使用联合索引

      使用联合索引的时候，注意最左匹配原则。

  • JOIN优化

    • 优化子查询

      尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。

    • 小表驱动大表

      关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL内部会遍历驱动表，再去连接被驱动表。

      比如left join，左表就是驱动表，A表小于B表，建立连接的次数就少，查询速度就被加快了。

    • 适当增加冗余字段

      增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。

    • 避免使用JOIN关联太多的表

      《阿里巴巴Java开发手册》规定不要join超过三张表，第一join太多降低查询的速度，第二join的buffer会占用更多的内存。

      如果不可避免要join多张表，可以考虑使用数据异构的方式异构到ES中查询。

  • 排序优化

    • 利用索引扫描做排序

      MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。

      但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢。

      因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。

      例如：

      1
      2
      --建立索引（date,staff_id,customer_id）
      select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

      只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。

  • UNION优化

    • 条件下推

      MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

      最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化。

      此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，

      MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高。

怎么看执行计划（explain），如何理解其中各个字段的含义？

explain是sql优化的利器，除了优化慢sql，平时的sql编写，也应该先explain，查看一下执行计划，看看是否还有优化的空间。

直接在 select 语句之前增加 explain 关键字，就会返回执行计划的信息。

1. id 列：MySQL会为每个select语句分配一个唯一的id值

2. select_type 列，查询的类型，根据关联、union、子查询等等分类，常见的查询类型有SIMPLE、PRIMARY。

3. table 列：表示 explain 的一行正在访问哪个表。

4. type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。

性能从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null >index_merge > unique_subquery > index_subquery > range > index > ALL

  • system

    当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快

  • const

    表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量( const )值。这类扫描效率极高，返回数据量少，速度非常快。

  • eq_ref

    查询时命中主键 primary key 或者 unique key 索引， type就是 eq_ref 。

  • ref_or_null

    这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行。

  • index_merge

    使用了索引合并优化方法，查询使用了两个以上的索引。

  • unique_subquery

    替换下面的 IN 子查询，子查询返回不重复的集合。

  • index_subquery

    区别于 unique_subquery ，用于非唯一索引，可以返回重复值。

  • range

    使用索引选择行，仅检索给定范围内的行。简单点说就是针对一个有索引的字段，给定范围检索数据。在 where 语句中使用bettween…and 、 < 、 > 、 <= 、 in 等条件查询 type 都是 range 。

  • index

    Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。

  • ALL

    就不用多说了，全表扫描。

5. possible_keys 列：显示查询可能使用哪些索引来查找，使用索引优化sql的时候比较重要。

6. key 列：这一列显示 mysql 实际采用哪个索引来优化对该表的访问，判断索引是否失效的时候常用。

7. key_len 列：显示了 MySQL使用

8. ref 列：ref 列展示的就是与索引列作等值匹配的值，常见的有：const（常量），func，NULL，字段名。

9. rows 列：这也是一个重要的字段，MySQL查询优化器根据统计信息，估算SQL要查到结果集需要扫描读取的数据行数，这个值非常直观显示SQL的效率好坏，原则上rows越少越好。

10. Extra 列：显示不适合在其它列的额外信息，虽然叫额外，但是也有一些重要的信息：

  • Using index：表示MySQL将使用覆盖索引，以避免回表

  • Using where：表示会在存储引擎检索之后再进行过滤

  • Using temporary ：表示对查询结果排序时会使用一个临时表。

索引

能简单说一下索引的分类吗？

从三个不同维度对索引分类：

例如从基本使用使用的角度来讲：

  • 主键索引: InnoDB主键是默认的索引，数据列不允许重复，不允许为NULL，一

个表只能有一个主键。

  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索

引。

  • 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

为什么使用索引会加快查询？

使用索引来快速定位符合查询条件的记录，并只检索必要的行和列以返回结果，从而不必进行全表扫描。并且索引是树形结构，查询时间通常与所需查找的行数呈对数关系，从而使查询变得更加高效。

举例来说，如果要在一个包含10000条记录的用户表中查找名字为“Tom”的用户信息，如果没有索引，那么数据库会逐行扫描表格，直到找到名字为“Tom”的用户信息；而如果有名字的索引，则数据库只需在索引结构中查找名为“Tom”的索引键值所对应的记录位置，然后再去表中取出相关的用户信息，查询速度会大大提升。

不过需要注意的是，虽然索引可以加快查询速度，但是索引建立也需要消耗一定的时间和存储空间，在对数据频繁进行增删改等操作时，可能会导致索引效率下降，因此需要根据实际情况合理使用索引。

创建索引有哪些注意点？

1. 索引应该建在查询应用频繁的字段

在用于 where 判断、 order 排序和 join 的(on)字段上创建索引。

2. 索引的个数应该适量

索引需要占用空间；更新时候也需要维护。

3. 区分度低的字段，例如性别，不要建索引。

离散度太低的字段，扫描的行数降低的有限。

4. 频繁更新的值，不要作为主键或者索引

维护索引文件需要成本；还会导致页分裂，IO次数增多。

5. 组合索引把散列性高(区分度高)的值放在前面

为了满足最左前缀匹配原则

6. 创建组合索引，而不是修改单列索引。

组合索引代替多个单列索引（对于单列索引，MySQL基本只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）

7. 过长的字段，使用前缀索引。

当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。

8. 不建议用无序的值(例如身份证、UUID )作为索引

当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

索引哪些情况下会失效呢？

  • 查询条件包含or，可能导致索引失效

  • 如果字段类型是字符串，where时一定用引号括起来，否则会因为隐式类型转换，索引失效

  • like通配符可能导致索引失效。

  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

  • 在索引列上使用mysql的内置函数，索引失效。

  • 对索引列运算（如，+、-、*、/），索引失效。

  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效。

  • 索引字段上使用is null， is not null，可能导致索引失效。

  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

  • MySQL优化器估计使用全表扫描要比使用索引快,则不使用索引。

索引不适合哪些场景呢？

  • 数据量比较少的表不适合加索引

  • 更新比较频繁的字段也不适合加索引

  • 离散低的字段不适合加索引（如性别）

MySQL索引用的什么数据结构了解吗？

MySQL的默认存储引擎是InnoDB，它采用的是B+树结构的索引。

B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

假设索引字段是 bigint 类型，长度为 8 字节。指针大小在 InnoDB 源码中设置为 6字节，这样一共 14 字节。非叶子节点(一页)可以存储 16384/14=1170 个这样的 单元(键值+指针)，代表有 1170 个指针。

树深度为 2 的时候，有 1170^2 个叶子节点，可以存储的数据1170*1170*16=21902400。在查找数据时一次页的查找代表一次 IO，也就是说，一张 2000 万左右的表，查询数据最多需要访问 3 次磁盘。

所以在 InnoDB 中 B+ 树深度一般为 1-3 层，它就能满足千万级的数据存储。

聚簇索引与非聚簇索引的区别？

首先理解聚簇索引不是一种新的索引，而是而是一种数据存储方式。 聚簇表示数据行和相邻的键值紧凑地存储在一起。我们熟悉的两种存储引擎——MyISAM采用的是非聚簇索引，InnoDB采用的是聚簇索引。

  • 索引的数据结构是树，聚簇索引的索引和数据存储在一棵树上，树的叶子节点就是数据，非聚簇索引索引和数据不在一棵树上。

  • 一个表中只能拥有一个聚簇索引，而非聚簇索引一个表可以存在多个。

  • 聚簇索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

  • 聚簇索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

回表了解吗？

在InnoDB存储引擎里，利用辅助索引查询，先通过辅助索引找到主键索引的键值，再通过主键值查出主键索引里面没有符合要求的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

覆盖索引了解吗？

在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用辅助索引中就能够取得，不用去查主键索引，这时候使用的索引就叫做覆盖索引，避免了回表。

锁

MySQL中有哪几种锁，列举一下？

如果按锁粒度划分，有以下3种：

  • 表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低；不会出现死锁。

  • 行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

  • 页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

如果按照兼容性，有两种：

  • 共享锁（S Lock）,也叫读锁（read lock），相互不阻塞。

  • 排他锁（X Lock），也叫写锁（write lock），排它锁是阻塞的，在一定时间内，只有一个请求能执行写入，并阻止其它锁读取正在写入的数据。

说说InnoDB里的行锁实现?

InnoDB使用行锁来实现并发控制，以保证事务的隔离性。行锁分为共享锁和排他锁。

  • 共享锁（S锁）允许多个事务同时持有同一行的共享锁，用于读操作。
  • 排他锁（X锁）只允许一个事务持有该行的锁，用于写操作。

在InnoDB中，行锁的实现是基于索引的，也就是说，如果没有命中索引，则会对整张表加锁。当事务需要获取某一行的锁时，会先判断是否已经被其他事务占用，如果没有则可以直接获取锁；如果已经被其他事务占用，则需要等待或者阻塞，直到该行的锁被释放。

在具体实现上，InnoDB使用两种锁算法：Record Lock和Gap Lock。

  • Record Lock：指的是对某一行进行加锁，只有当其他事务尝试修改该行时，才会阻塞。Record Lock并不会对其他行造成阻塞。
  • Gap Lock：指的是对索引之间的间隙进行加锁，即使对已经不存在的行也会加锁。这是因为，在RR级别隔离下，如果不对间隙进行加锁，则可能出现幻读的情况。Gap Lock会对后续的插入造成阻塞，但不会对其他已经存在的行造成阻塞。

需要注意的是，InnoDB在实现行锁时，并不是完全采用Record Lock和Gap Lock的方式，而是根据具体情况进行选择和优化。例如，在RR级别隔离下，如果某一事务使用了索引范围查询，则会自动升级为Gap Lock，以避免幻读的发生。

意向锁是什么知道吗？

意向锁（Intention Lock）是InnoDB中一种特殊的锁，用于协调多个事务对同一索引关键字的行进行加锁时的并发控制。

在InnoDB中，当一个事务需要对某个表的某行进行加锁时，需要先获取该行对应的索引上的意向锁。意向锁可以被多个事务同时持有，而且不会对其他事务造成阻塞。意向锁分为两种：

  • 意向共享锁（IS锁）：表示事务打算在该索引上加共享锁。
  • 意向排他锁（IX锁）：表示事务打算在该索引上加排他锁。

意向锁的作用是告诉其他事务，在该索引上已经有事务加了锁，如果要在该索引上加锁，就需要根据意向锁的类型来决定是加共享锁还是排他锁。这样可以避免多个事务同时对同一索引关键字的行进行加锁时出现死锁的情况。

需要注意的是，意向锁只对索引起作用，对于全表扫描不起作用。此外，意向锁与普通锁的区别在于，意向锁本身并不会导致其他事务被阻塞，而是通过提示其他事务是否需要加锁来实现并发控制。

MySQL的乐观锁和悲观锁了解吗？

  • 悲观锁 （Pessimistic Concurrency Control）：

悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

  • 乐观锁（Optimistic Concurrency Control）

乐观锁认为数据的变动不会太频繁。

乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本v1与数据中最新的版本v2相对比，如果v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时version会加1，以此来表明数据已被变动。如果，v1不等于v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。不同于悲观锁，乐观锁通常是由开发者实现的。

事务

MySQL 事务的四大特性说一下？

  • 原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

  • 一致性：指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给B 账户转 10 块钱，不管成功与否，A 和 B 的总金额是不变的。

  • 隔离性：多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是井水不犯河水的。

  • 持久性：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

那ACID靠什么保证的呢？

  • 事务的 隔离性 是通过数据库锁的机制实现的。

  • 事务的 一致性 由undo log来保证：undo log是逻辑日志，记录了事务的insert、update、deltete操作，回滚的时候做相反的delete、update、insert操作来恢复数据。

  • 事务的 原子性 和 持久性 由redo log来保证：redolog被称作重做日志，是物理日志，事务提交的时候，必须先将事务的所有日志写入redo log持久化，到事务的提交操作才算完成。

事务的隔离级别有哪些？MySQL 的默认隔离级别是什么？

  • 读未提交（Read Uncommitted）：事务中的修改对其他事务都是可见的，即一个事务可以读取另一个事务尚未提交的数据。该隔离级别会导致脏读（Dirty Read）。

  • 读已提交（Read Committed）：事务中的修改只有在提交之后才对其他事务可见，即一个事务只能读取另一个事务已经提交的数据。该隔离级别会导致不可重复读（Non-Repeatable Read）。

  • 可重复读（Repeatable Read）：在同一事务中，多次读取同一数据得到的结果始终一致，即一个事务执行期间，禁止其他事务修改正在访问的数据。该隔离级别会导致幻读（Phantom Read）。

  • 串行化（Serializable）：最高的隔离级别，强制事务串行执行，避免了脏读、不可重复读和幻读问题，但会降低并发性能。

MySQL的默认隔离级别是可重复读（Repeatable Read），也就是说，在MySQL中，如果不显式设置隔离级别，则默认采用可重复读隔离级别。

讲一下死锁

死锁是指两个或多个进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法继续执行下去。简单来说，就是两个或多个进程互相持有对方需要的资源，同时又等待对方释放资源，导致所有进程都无法继续执行下去。

为了避免死锁的发生，我们可以采取以下措施：

  1. 避免多个线程同时持有多个资源，并且让每个线程按照同样的顺序请求资源，这样可以避免死锁的发生。
  2. 设置超时时间，当线程等待时间超过一定阈值时，就放弃等待并释放已经持有的资源。
  3. 使用资源分配图来检测死锁的发生，并采取相应的措施解除死锁。
  4. 使用锁粒度更细的锁，这样可以减少锁的竞争，从而减少死锁的可能性。

什么是幻读，脏读，不可重复读呢？

  • 脏读：一个事务修改了某个数据，但还没有提交，另一个事务读取了这个数据，此时就会出现脏读。脏读问题主要发生在读未提交隔离级别下。

  • 不可重复读：通常是由于其他事务修改了满足查询条件的数据行导致的。例如，一个事务读取了某一条数据，另一个事务修改了该数据，然后第一个事务再次读取同样的数据时，会发现数据已经发生改变。

  • 幻读：通常是由于其他事务插入或删除了满足查询条件的数据行导致的。例如，一个事务在读取某个范围内的数据时，另一个事务插入了一条符合条件的数据，第一个事务再次读取同样的范围时，会发现多了一条记录。

不同的隔离级别，在并发事务下可能会发生的问题：

隔离级别 脏读 不可重复读 幻读
Read Uncommited 读取未提交 是 是 是
Read Commited 读取已提交 否 是 否
Repeatable Read 可重复读 否 否 是
Serialzable 可串行化 否 否 否

事务的各个隔离级别都是如何实现的？

  • 读未提交

    读未提交，就不用多说了，采取的是读不加锁原理。

    • 事务读不加锁，不阻塞其他事务的读和写
    • 事务写阻塞其他事务写，但不阻塞其他事务读；

  • 读取已提交&可重复读

    读取已提交和可重复读级别利用了 ReadView 和 MVCC ，也就是每个事务只能读取它能看到的版本（ReadView）。

    • READ COMMITTED：每次读取数据前都生成一个ReadView
    • REPEATABLE READ ： 在第一次读取数据时生成一个ReadView

  • 串行化

    串行化的实现采用的是读写都加锁的原理。

    串行化的情况下，对于同一行事务， 写 会加 写锁 ， 读 会加 读锁 。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MVCC了解吗？怎么实现的？

MVCC（Multi-Version Concurrency Control，多版本并发控制）是一种用于数据库管理系统中实现并发控制的技术。该技术支持在一个事务正在进行修改时，其他事务仍然可以读取数据，并且保证了读取的数据不会被正在执行的事务修改，从而提高了数据库的并发性能。

MVCC的实现需要对每个数据行记录都维护多个版本，并为每个事务分配一个唯一的事务ID（Transaction ID，即XID）。在每个数据行记录上，都会有一个存储该行的创建版本号和删除版本号的字段，以及一个指向最新版本的指针。在MVCC中，只有创建版本号早于当前事务ID的版本才是可见的，也就是说，一个事务只能读取它开始之前已经存在的数据行或者是自己创建的数据行，而不能读取其他事务正在修改的数据行。

当一个事务需要读取某个数据行时，会首先检查这个数据行是否被其他事务锁定，如果没有被锁定，则会根据该数据行的版本信息和当前事务的ID来选择合适的版本。如果该数据行被其他事务锁定，则会等待锁释放或超时，避免产生死锁问题。

当一个事务修改某个数据行时，会创建一个新版本，并将新版本的创建版本号设置为当前事务ID，并将原数据行的删除版本号设置为当前事务ID。这样就可以确保其他事务读取到的是旧版本，同时也不会修改其他事务正在使用的版本。

需要注意的是，在MVCC中，每个版本的数据行都会占用一定的存储空间，因此在大量并发访问和更新数据时，需要考虑存储空间的使用情况。

0%