Mysql

1. 快问快答

1.1. MySQL 和 PostgreSQL 的区别，如何技术选型？

1.2. MySQL的几种常用存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

1.3. MyISAM 和 InnoDB 的对比

1.4. MyISAM 和 InnoDB 如何选择？

1.5. 什么是索引？为什么要用索引？

索引的定义：帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

为了提高查询速度。

1.6. 索引的缺点

  • 需要占用物理空间，数量越大，占用空间越大；
  • 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
  • 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

1.7. 什么时候需要索引？

  • 字段有唯一限制，比如商品编码
  • 经常用于 WHERE 查询条件的字段，能够提高整个表的查询速度。
  • 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了。

1.8. 什么时候不需要索引？

  • 不需要快速定位的字段
  • 存在大量重复数据的字段，比如性别
  • 表数据太少的时候
  • 经常更新的字段，比如用户余额

1.9. MySQL的索引类型

  • 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
  • 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
  • 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
  • 按「字段个数」分类：单列索引、联合索引。

2. 基础

2.1. MySQL 字符集 和 字符序

  • 字符集（character set）：定义了字符以及字符的编码。
  • 字符序（collation）：定义了字符的比较规则。

MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。

  • 一个字符集对应至少一种字符序（一般是1对多）。
  • 两个不同的字符集不能有相同的字符序。
  • 每个字符集都有默认的字符序。
# 查看支持的字符集
# 方式一：
mysql> SHOW CHARACTER SET;
# 方式二：
mysql> use information_schema;
mysql> select * from CHARACTER_SETS;

# 查看支持的字符序
# 方式一：
mysql> SHOW COLLATION;
# 方式二：
mysql> use information_schema;
mysql> SELECT * FROM COLLATIONS;

# 查看server的字符集、字符序
mysql> SHOW VARIABLES LIKE "character_set_server";
mysql> SHOW VARIABLES LIKE "collation_server";
# 设置方式：启动服务时可以指定；配置文件也可以指定；运行可以修改；

# 创建database，并指定字符集、字符序
create database test1 CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci;

# 创建table，并指定字符集、字符序
CREATE TABLE IF NOT EXISTS `test1_tb` ( `name` VARCHAR(65532) NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

# 类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序
ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

2.2. MySQL 的数据存放在哪个文件？

# 目录/var/lib/mysql/下，每个database有一个目录，目录下有table相关文件
[root@fedora test1]# ls
test1_tb.ibd
# 将存储的表数据、索引等信息单独存储在一个独占表文件「 表名字.ibd 」。

2.3. 表空间文件的结构是怎么样的？

表空间由段（segment）、区（extent）、页（page）、行（row）组成。

tablefile

  • 行（row）：表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
  • 页（page）：InnoDB 的数据是按「页」为单位来读写的。当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。默认每个页的大小为 16KB。页的类型有很多，常见的有数据页、undo 日志页、溢出页等等
  • 区（extent）：B+ 树中每一层都是通过双向链表连接起来的。如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
  • 段（segment）：段一般分为数据段（B+树叶子节点的区集合）、索引段（B+树非叶子节点的区集合）和回滚段等。

2.4. InnoDB 行格式有哪些？

行格式（row_format），就是一条记录的存储结构。

  • Redundant：非紧凑的行格式，已废弃；
  • Compact：紧凑的行格式；
  • Dynamic：默认的行格式，基于 Compact 改进；
  • Compressed：基于 Compact 改进；

2.5. Compact 行格式

Compact 行格式：

Compact

  • 变长字段长度列表：这些变长字段的 真实数据占用的字节数 会按照列的顺序逆序存放；
    • 「 为什么逆序 」？使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。
    • 「 表的行格式一定有这个列表吗？ 」当数据表没有变长字段的时候，行格式就不会有 变长字段长度列表 了。
    • 变长字段长度列表 里不需要保存值为 NULL 的变长字段的长度。
    • 「 比如字段varchar(n)，变长字段长度如何计算？ 」n 小于等于255，就用 1 字节表示变长字段长度；n 大于 255，就用 2 字节表示变长字段长度；
  • NULL 值列表：如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的 NULL 值列表。
    • 「 表的行格式一定有这个列表吗？ 」当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
  • 记录头信息：占用 5 个字节。
    • delete_mask：标识此条数据是否被删除。
    • next_record：下一条记录的位置。指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
    • record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。

三个隐藏字段：

  • row_id：row_id不是必需的，占用 6 个字节。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。
  • trx_id：事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
  • roll_pointer：这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

2.6. varchar(n) 中 n 最大取值为多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度「 加起来 」不能超过 65535 个字节。

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。

假设条件：

  • 字符集选择ascii （一个字符占用一个字节）
  • 表字段只有一个name，类型为varchar，允许为NULL

结果：65535 - 变长长度列表（占用2字节）- NULL值列表（占用1字节）= 65532，所以 n 的最大值为 65532。（总之，需要根据表结构定义具体分析）

2.8. 行溢出后，MySQL 是怎么处理的？

在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。

Compact 行格式在发生行溢出后的处理：（当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。）

compact-overflow

Compressed 和 Dynamic 行格式在发生行溢出后的处理：

dynamic-overflow

2.9. 执行一条 select 语句的过程

select

  • 客户端和server建立TCP连接，连接器验证用户名和密码；
  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  • 解析器 做 词法分析 和 语法分析，构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行SQL阶段：
    • 预处理器：检查 SQL 查询语句中的表或者字段是否存在；将 select 中的 符号，扩展为表上的所有列；
    • 优化器：负责将 SQL 查询语句的执行方案确定下来；选择查询成本最小的执行计划；（比如使用哪个索引）
    • 执行器：在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。从存储引擎读取记录，返回给客户端。
      • 主键索引查询：比如：where id = 1，使用主键索引，访问类型为 const；
      • 全表扫描：比如：where name = 'jack'，没有用到索引，访问类型为 ALL；
      • 索引下推：比如：where age > 20 and reward = 10，age和reward为联合索引，使用索引下推；

3. 索引

3.1. 创建表时，InnoDB 存储引擎如何选择主键索引的？

  • 如果有主键，默认会使用 主键 作为聚簇索引的索引键（key）；
  • 如果没有主键，就选择第一个 不包含 NULL 值的唯一列 作为聚簇索引的索引键（key）；
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个 隐式自增 id 列 作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

3.2 主键索引的 B+Tree 和二级索引的 B+Tree 区别

  • 主键索引的 B+Tree：叶子节点才存放数据（完整的用户记录），非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。
  • 二级索引的 B+Tree：叶子节点存放的是主键值，而不是实际数据。

3.3 什么是回表？

根据二级索引查找记录时，只能查到 主键值，还需要根据主键值，通过主键索引查询到对应叶子节点，才能得到完整记录。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。

另外，如果在二级索引的 B+Tree 就能查询到结果（比如只是查主键字段），这时就不需要回表了，这个过程叫「覆盖索引」。

3.4 为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

  • B+Tree vs B Tree：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据；B+Tree 叶子节点采用的是双链表连接。所以，B+Tree在相同的磁盘 I/O 次数下，能查询更多的节点，且可以做到基于范围的顺序查找。
  • B+Tree vs 二叉树：对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。（实际中 d 大于 100的，千万级别数据，树高只有3~4层，磁盘I/O次数也只有3~4次。）。二叉树搜索复杂度为 O(logN)，检索到目标数据所需的磁盘I/O次数要多的多。
  • B+Tree vs Hash：Hash的搜索复杂度为 O(1)，但不适合做范围查询。

3.5 主键索引、唯一索引、普通索引、前缀索引

  • 主键索引：一张表最多只有一个主键索引，索引列的值不允许有空值。（PRIMARY KEY (index_column_1) USING BTREE）
  • 唯一索引：一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。（CREATE UNIQUE INDEX index_name ：ON table_name(index_column_1,index_column_2,...);）
  • 普通索引：既不要求字段为主键，也不要求字段为 UNIQUE。（CREATE INDEX index_name ON table_name(index_column_1,index_column_2,...);）
  • 前缀索引：指对字符类型（char、varchar、binary、varbinary）字段的前几个字符建立的索引。（CREATE INDEX index_name ON table_name(column_name(length));）

3.6 联合索引（复合索引）

通过将多个字段组合成一个索引，该索引就被称为联合索引。使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

举例：创建了一个 (a, b, c) 联合索引，查询时使用 where b=2 and c=3 无法匹配上联合索引。

这很好理解，这个联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。

单独只看 b，其实是无序的。

另外，有一个特殊情况，联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，因为在 = 的情况下，还可以继续使用联合索引。

3.6.1 索引下推

看例子，对于联合索引（a, b），执行 select * from table where a > 1 and b = 2; ，可以知道，a 字段可以用联合索引，b 字段无法使用联合索引。

那么，对于 b 字段的判断，是在联合索引里判断？还是回主键索引去判断呢？

  • 在 MySQL 5.6 之前，需要回表，在主键索引中找到行，再对于 b 字段的值。
  • MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

3.6.2 索引区分度

建立联合索引时的字段顺序，对索引效率也有很大影响。建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数。

3.7 索引优化

  • 前缀索引优化：减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。
    • order by 就无法使用前缀索引；
    • 无法把前缀索引用作覆盖索引；
  • 覆盖索引优化：避免回表的操作，不需要查询出包含整行记录的所有信息，也就减少了大量的 I/O 操作。
  • 主键索引最好是自增的：每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
    • 如果是非自增主键，当页满时，插入新的数据，会导致页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。 page-break
  • 主键字段的长度不要太大：主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。
  • 索引最好设置为 NOT NULL：有两个原因，
    • 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。比如进行索引统计时，count 会省略值为NULL 的行。
    • NULL 值是一个没意义的值，但是它会占用物理空间。行格式中的NULL 值列表。
  • 防止索引失效：索引失效的查询效率是很低的。发生索引失效的情况，
    • 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
    • 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
    • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
    • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

3.8 使用 explain 看执行计划

对于执行计划，参数有：

  • possible_keys 字段表示可能用到的索引；
  • key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  • key_len 表示索引的长度；
  • rows 表示扫描的数据行数。
  • type 表示数据扫描类型，我们需要重点看这个。

常见扫描类型的执行效率从低到高的顺序为：

  • All（全表扫描）；
  • index（全索引扫描）；
  • range（索引范围扫描）；
  • ref（非唯一索引扫描）；
  • eq_ref（唯一索引扫描）；
  • const（结果只有一条的主键或唯一索引扫描）。

index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。

const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

另外，需要关注 extra 显示的结果：

  • Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
  • Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
  • Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

3.9 count(*) 和 count(1) 性能比较

# 按性能排序
count(*) = count(1) > count(主键字段) > count(字段)
  • count(1)：优先查二级索引 > 主键索引，因为参数是1，所以不需要读取记录中的字段值。
  • count(*)：count(*) 执行过程跟 count(1) 执行过程基本一样的，没有性能差异。
  • count(主键字段)：优先查二级索引 > 主键索引，因为参数是1，所以需要读取主键字段，判断是否为 NULL，如果不为 NULL，就将 count 变量加 1。
  • count(字段)：采用全表扫描的方式来统计。

另外，MySQL 会对count(*)和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。

3.10 索引练习题

# 看SQL语言，说出索引使用计划

select * from product where id= 5;
# 根据主键索引，查到记录。

select * from product where product_no = '0002';  # product_no 为二级索引
# 先根据二级索引查询到主键，再根据主键索引查询到记录。（回表）

select id from product where product_no = '0002';  # product_no 为二级索引
# 根据二级索引查询到主键。（索引覆盖）

select * from t_table where a > 1 and b = 2; # 联合索引（a, b）
# a 字段用到了联合索引，b 字段没有用到联合索引。（a查完，b是无序的）
# 匹配 b = 2 条件时，使用了索引下推优化，减少回表次数。

select * from t_table where a >= 1 and b = 2; # 联合索引（a, b）
# a 和 b 字段都用到了联合索引。

SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2; # 联合索引（a, b）
# a 和 b 字段都用到了联合索引。（BETWEEN属于闭区间的情况）

SELECT * FROM t_user WHERE name like 'j%' and age = 22; # 联合索引（name, age）
# name 和 age 字段都用到了联合索引。

SELECT * FROM t_user WHERE name like '%xxx'; # name是二级索引，表中有很多字段
# 二级索引失效，会全表扫描

SELECT * FROM t_user WHERE name like '%xxx'; # name是二级索引，表中只有id,name两个字段
# 使用了二级索引，但也是全扫描。（因为只需要扫描整个二级索引表就行）

4. 事务（InnoDB 引擎）

4.1. 事务特性

  • 原子性（Atomicity）：一个事务中的所有操作，要么全部完成，要么全部不完成，而且事务在执行过程中发生错误，会被回滚到事务开始前的状态。
  • 一致性（Consistency）：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
  • 隔离性（Isolation）：每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
  • 持久性（Durability）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

4.2. 如何保证事务的这四个特性？

  • 持久性：是通过 redo log （重做日志） 来保证的；
  • 原子性：是通过 undo log（回滚日志） 来保证的；
  • 隔离性：是通过 MVCC（多版本并发控制） 或 锁机制 来保证的；
  • 一致性：是通过持久性+原子性+隔离性来保证；

4.3. 事务并发有什么问题？

  • 脏读（dirty read）：读到其他事务未提交的数据；
  • 不可重复读（non-repeatable read）：前后读取的数据不一致；
  • 幻读（phantom read）：前后读取的记录数量不一致；

严重性：脏读 > 不可重复读 > 幻读

4.4. 事务的隔离级别有哪些？

  • 读未提交（read uncommitted）：指一个事务还没提交时，它做的变更就能被其他事务看到；
  • 读提交（read committed）：指一个事务提交之后，它做的变更才能被其他事务看到；
  • 可重复读（repeatable read）：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
  • 串行化（serializable ）：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

隔离水平高低：串行化 > 可重复读 > 读提交 > 读未提交

  • 在「读未提交」隔离级别下，可能发生 脏读、不可重复读和幻读 现象；
  • 在「读提交」隔离级别下，可能发生 不可重复读和幻读 现象，但是不可能发生脏读现象；
  • 在「可重复读」隔离级别下，可能发生 幻读 现象，但是不可能脏读和不可重复读现象；
  • 在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能会发生。

4.5. 四种隔离级别如何实现的呢？

  • 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以 直接读取最新的数据 就好了；
  • 对于「串行化」隔离级别的事务来说，通过加 读写锁 的方式来避免并行访问；
  • 对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。
    • 「读提交」隔离级别是在 「每个语句执行前」都会重新生成一个 Read View；
      • 「可重复读」隔离级别是 「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

4.6. 如何开启事务？

  • 执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了增删查改操作的 SQL 语句，才是事务真正启动的时机；
  • 执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

4.7. MySQL 可重复读 隔离级别，完全解决幻读了吗？

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是 它很大程度上避免幻读现象（并不是完全解决了） ，解决的方案有两种：

  • 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
  • 针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

但是还是有个别的情况造成的幻读现象是无法解决的。

  • 第一个例子：对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
  • 第二个例子：对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select ... for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

4.8. 普通的“增删改”，有开启事务吗？

当然有。

在执行执行一条“增删改”语句的时候，虽然没有输入 begin 开启事务和 commit 提交事务，但是 MySQL 会隐式开启事务来执行“增删改”语句的，执行完就自动提交事务的。

5. 锁

5.1. MySQL 有哪些锁？

  • 全局锁：用做全库逻辑备份；
    • 用法：flush tables with read lock，unlock tables；
    • 备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？
      • 如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。
  • 表级锁：表锁、元数据锁（MDL）、意向锁、AUTO-INC 锁；
  • 行级锁：记录锁（Record Lock）、间隙锁（Gap Lock）、Next-Key Lock

5.1.1. 表级锁

  • 表锁：共享锁（读锁）lock tables t_student read;，独占锁（写锁）lock tables t_student write;，解锁unlock tables；
  • 元数据锁（MDL）：不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL；
    • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
    • 对一张表做结构变更操作的时候，加的是 MDL 写锁；
    • MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
    • 申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
  • 意向锁：目的是为了快速判断表里是否有记录被加锁；
    • 对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
    • 对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；
  • AUTO-INC 锁：表里的主键通常都会设置成自增的，在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。
    • （mysql 5.1.22以后）InnoDB 存储引擎提供了一种 轻量级的锁 来实现自增。（给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。）
    • innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁。
    • 当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

5.1.2. 行级锁

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
  • 插入意向锁：一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。
    • 如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。
    • 插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁。

间隙锁的目的是防止幻读而提出的，只存在于可重复读隔离级别。

5.2. MySQL 记录锁+间隙锁可以防止删除操作而导致的幻读吗？

可以防止。

由于先加了 记录锁+间隙锁，另外一个事务的(增、删、改)操作会被阻塞。

5.3. 如何查看加了哪些锁？

select * from performance_schema.data_locks\G

LOCK_TYPE：

  • TABLE：表级锁
  • RECORD：行级锁

表级锁，LOCK_MODE：

  • IX：X 型意向锁

行级锁，LOCK_MODE：

  • X：Next-Key 锁；
  • X, REC_NOT_GAP：记录锁；
  • X, GAP：间隙锁；
  • X, GAP, INSERT_INTENTION：插入意向锁

行级锁，LOCK_DATA：

  • 主键索引值：如果是记录锁，表示锁定这条记录；如果是间隙锁，表示区间右边界。
  • 二级索引值，主键索引值：
  • supremum pseudo-record：正无穷
  • infimum pseudo-record：负无穷

5.4. MySQL 如何加 行级锁 的？

先确定场景讨论， InnoDB 引擎的 可重复读 隔离级别。

普通的 select 语句是不会对记录加锁的，因为它属于快照读，是通过 MVCC（多版本并发控制）实现的。

# 对读取的记录加共享锁(S型锁)
select ... lock in share mode;

# 对读取的记录加独占锁(X型锁)
select ... for update;

# 对操作的记录加独占锁(X型锁)
update table .... where id = 1;

# 对操作的记录加独占锁(X型锁)
delete from table where id = 1;

5.4.1. 唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

  • 当查询的记录是 「存在」 的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会 退化成「记录锁」。
  • 当查询的记录是 「不存在」 的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的 next-key lock 会 退化成「间隙锁」。
# 比如现在表中数据的主键是：1, 5, 6
begin;

# 事务会为 id = 1 记录的主键索引上加 X 型的记录锁。
select * from user where id = 1 for update;

# 事务会在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。
# 在另外一个事务中，能添加 id = 2 的新记录吗？ 不行
# 在另外一个事务中，能删除 id = 5 的记录吗？ 可以
select * from user where id = 2 for update;

# 事务在特殊记录（supremum pseudo-record）的主键索引上加间隙锁，锁住的范围是 (6, 无穷大)。
# 在另外一个事务中，能添加 id = 7 的新记录吗？ 不行
# 在另外一个事务中，能删除 id = 6 的记录吗？ 可以
select * from user where id = 7 for update;

【为什么不可以针对不存在的记录加记录锁？】

锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

5.4.2. 唯一索引范围查询

当唯一索引进行 范围 查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

  • 情况一：针对「大于等于」的范围查询，对于 等值查询的记录，那么该记录的索引中的 next-key 锁会退化成记录锁。
  • 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
    • 当条件值的记录不在表中，对于 扫描到终止范围查询的记录 时，该记录的索引的 next-key 锁会退化成间隙锁。
    • 当条件值的记录在表中，如果是 「小于」 条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁。
# 比如现在表中数据的主键是：1, 5, 10
begin;

# 【大于】
# 1. 事务在 id = 10 这条记录的主键索引上，加了范围为 (5, 10] 的 X 型 next-key 锁。
# 2. 事务在特殊记录（supremum pseudo-record）的主键索引上，加了范围为 (10, +∞] 的 X 型 next-key 锁
select * from user where id > 5 for update;

# 【大于等于】
# 1. 事务在 id = 5 这条记录的主键索引上，加 X 型 记录锁。
# 2. 事务在 id = 10 这条记录的主键索引上，加了范围为 (5, 10] 的 X 型 next-key 锁。
# 3. 事务在特殊记录（supremum pseudo-record）的主键索引上，加了范围为 (10, +∞] 的 X 型 next-key 锁
select * from user where id >= 5 for update;

# 【小于】
# 【小于等于】
# 条件值不存在的情况
# 1. 事务在 id = 1 这条记录的主键索引上，加范围为 (-∞, 1] X 型 next-key 锁。
# 2. 事务在 id = 5 这条记录的主键索引上，加范围为 (1, 5)  X 型 间隙锁。
# 这时候另外一个事务能插入 id = 4 的新记录吗？ 不能。
select * from user where id < 3 for update;
select * from user where id <= 3 for update;

# 【小于】
# 【小于等于】
# 条件值存在的情况
# 1. 事务在 id = 1 这条记录的主键索引上，加范围为 (-∞, 1] X 型 next-key 锁。
# 2. 事务在 id = 5 这条记录的主键索引上，加范围为 (1, 5)  X 型 间隙锁。
select * from user where id < 5 for update;
# 1. 事务在 id = 1 这条记录的主键索引上，加范围为 (-∞, 1] X 型 next-key 锁。
# 2. 事务在 id = 5 这条记录的主键索引上，加范围为 (1, 5)  X 型 next-key 锁。
select * from user where id <= 5 for update;

5.4.3. 非唯一索引等值查询

  • 当查询的记录「存在」时，对于第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  • 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。
# 比如现在表中数据是：
# 主键索引： 5, 10, 20
# 二级索引：11, 22, 39
begin;

# 【条件值不存在】
# 1. 事务在 age = 39 这条记录的二级索引上，加范围为 (22, 39)  X 型 间隙 锁。
select * from user where age = 25 for update;
# 这里可以知道，其他事务操作时，如果新数据的age处于(22, 39)，是无法插入的。
# 如果新数据的 age = 22 或 39，能否插入呢？ （这个要看 id 值）
# 原则是：**插入语句在插入一条记录之前，需要先定位到该记录在 B+树 的位置，如果插入的位置的下一条记录的索引上有间隙锁，才会发生阻塞。**

# 【条件值存在】
# 1. 事务在 age = 22 这条记录的二级索引上，加范围为 (11, 22]  X 型 next-key 锁。
# 2. 事务在 id = 10 这条记录的主键索引上，加  X 型 记录 锁。
# 3. 事务在 age = 39 这条记录的二级索引上，加 范围为 (22, 39)  X 型 间隙 锁。
select * from user where age = 22 for update;
# 为什么需要在 22 前或后 加锁？ （由于不是唯一索引，所以肯定存在值相同的记录，为了避免幻读）

5.4.4. 非唯一索引范围查询

非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

# 比如现在表中数据是：
# 主键索引  id： 5, 10, 20
# 二级索引 age：11, 22, 39
begin;

# 1. 事务在 age = 22 这条记录的二级索引上，加范围为 (11, 22]  X 型 next-key 锁。
# 2. 事务在 id = 10 这条记录的主键索引上，加  X 型 记录 锁。
# 3. 事务在 age = 39 这条记录的二级索引上，加 范围为 (22, 39]  X 型 next-key 锁。
# 4. 事务在 id = 20 这条记录的主键索引上，加  X 型 记录 锁。
# 5. 事务在特殊记录supremum pseudo-record的二级索引上，加 范围为 (39, +∞]  X 型 next-key 锁。
select * from user where age >= 22 for update;

5.4.5. 没有加索引的查询

在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

本质上，每一条记录的索引上都会加 next-key 锁。

5.4.6. mysql 参数 sql_safe_updates

当 sql_safe_updates 设置为 1 时。

update 语句必须满足如下条件之一才能执行成功：

  • 使用 where，并且 where 条件中必须有索引列；
  • 使用 limit；
  • 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

  • 同时使用 where 和 limit，此时 where 条件中可以没有索引列；

5.5. MySQL 死锁

示例，age是二级索引，目前age最大记录是39。

# 事务A
begin;
select * from user where age=40 for update;
insert user values (30, "CCC", 40, 100000);

# 事务B
begin;
select * from user where age=41 for update;
insert user values (31, "CCC", 41, 100000);

分析：

  • select ... for update 会对二级索引加 X 型 next-key 锁，范围是 (39, +∞]
  • insert 会对二级索引加 X 型 插入意向 锁

插入意向锁与间隙锁是冲突的，insert会阻塞。

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。

【mysql 解除死锁：】

  • 设置事务等待锁的超时时间。innodb_lock_wait_timeout
  • 开启主动死锁检测。innodb_deadlock_detect。（主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。）

【业务角度预防死锁】：

上面示例是为了保证 age 唯一，可以将字段设置为唯一索引列。但插入已存在时，会抛异常。

5.6. 唯一键冲突时，会加什么锁？

# 比如现在表中数据是：
# 主键索引  id： 5, 10, 20
# 二级索引 age：11, 22, 39
begin;

# 主键索引冲突
# 事务在 id = 20 这条记录的主键索引上，加  S 型 记录 锁。
insert user values (20, "CCC", 40);

# 唯一二级索引冲突
# 事务在 age = 39 这条记录的二级索引上，加  S 型 next-key 锁。
# 事务在 id = 20 这条记录的主键索引上，加  X 型 间隙 锁
insert user values (19, "CCC", 39);

5.7. 字节分析题

# 比如现在表中数据是：
# 主键索引  id： 5, 10, 20

# 事务A
begin;
update user set reward=5000 where id=15;      # 时间 1 运行
insert user values (15, "CCC", 40, 100000);   # 时间 3 运行

# 事务B
begin;
update user set reward=5000 where id=16;      # 时间 2 运行
insert user values (16, "CCC", 41, 100000);   # 时间 4 运行

分析：

  • update 会加 X 型 间隙 锁
  • insert 会加 X 型 插入意向 锁

冲突导致死锁

6. 日志

  • undo log（回滚日志）：Innodb 存储引擎提供；
  • redo log（重做日志）：Innodb 存储引擎提供；
  • binlog：MySQL 的 Server 层提供；

6.1. undo log

undo log 是一种用于撤销回退的日志。在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。

undo log（回滚日志），两个作用：

  • 它保证了事务的 ACID 特性 (opens new window)中的原子性（Atomicity）。
  • 通过 ReadView + undo log 实现 MVCC（多版本并发控制）。

6.1.1. undo log 是怎么持久化到磁盘的？

undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。

6.2. 每次更新记录，MySQL都直接写磁盘吗？还是先缓存起来？

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。

6.3. redo log

Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo-log

redo log（重做日志），两个作用：

  • 实现事务的持久性，用于掉电恢复。
  • 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

6.4. binlog

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，

binlog 有 3 种格式类型：

  • STATEMENT（默认格式）：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现。（有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果。）
  • ROW：记录行数据最终被修改成什么样了。（ 缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大）
  • MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

binlog 用于：备份恢复、主从复制；

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。

results matching ""

    No results matching ""