questions

mysql分为哪些层，各用来干嘛的

  • mysql架构共分为两层：Server 层和存储引擎层
  • Server 层负责建立连接、分析和执行 SQL
  • 存储引擎层负责数据的存储和提取

    为什么mysql用b+树而不是用b树：查询性能和顺序遍历的效率高

    主要的原因可能就是b+数据存在叶子节点上，并且用指针连着，如果要遍历直接去扫一遍，而b树的话，需要遍历整棵树才能读完；
    并且查找时，b+树高度一般很稳定，b树高度一般来说会更高，因为两者的

    mysql基于什么协议传输

    TCP。
    三次握手建立后，连接器验证用户名和密码。

连接器作用

  • TCP三次握手
  • 校检用户名密码，返回用户权限

查询缓存作用

  • select语句输入，执行，先去查询缓存中找，查询缓存中存的是之前执行过的sql语句，以key-value保存的，底层数据结构为哈希表。
  • 8.0.3后移除了这一层，在一些问题，包括性能问题、锁的竞争问题以及难以扩展。

解析器作用

  • 词法分析-语法分析-语法树结构
  • 词法分析（Lexical Analysis）
  • 语法分析（Syntax Analysis）语法分析器会将其转化为一个抽象语法树（AST）
  • AST的作用：编译器或解释器用来理解代码含义的数据结构，它可以被后续的步骤用来进行语义分析、优化和生成目标代码等。
  • 【查询优化】：对AST进行查询优化，选择合适的索引和决定连接顺序

【面试题】执行一条 SQL 查询语句，期间发生了什么？

  • 连接器：建立连接，管理连接、校验用户身份；
  • 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  • 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  • 执行 SQL：执行 SQL 共有三个阶段：
  • prepare 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • optimize 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • execute 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

如何查看我的sql被几个客户端链接了

show processlist;会有ID user host等字段

mysql推荐使用长链接，但会产生的问题：

随着长连接一直不释放，内存占用大。【解决方式】定期释放，主动重置连接mysql_reset_connection()

MYSQL的数据存储方式

  1. show variable like ‘datadir’; 可以查找mysql的文件在哪
    1
    2
    3
    db.opt  用来存储当前数据库的默认字符集和字符校验规则。
    t_order.frm  存放表结构，在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
    t_order.ibd  存放表数据。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。

表空间文件的结构

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

  • 表中的数据在Page里，数据是按「页」为单位来读写的，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
  • 默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间

按区分配空间的情况

  • 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段一般分为数据段、索引段和回滚段等。

  • 索引段：存放 B + 树的非叶子节点的区的集合；
  • 数据段：存放 B + 树的叶子节点的区的集合；
  • 回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

Innodb的行格式

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

COMPACT 行格式

  • 数据类型：char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。

  • row_id：
    如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。

  • trx_id：
    事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。

  • roll_pointer：
    这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节

  • NULL值列表：用位图存储的。压缩行存储通过一种称为”Dynamic Prefix”的技术，动态地存储每一行的前缀信息和 NULL 列的位图。这使得它可以更加高效地存储具有大量 NULL 列的行。

    mysql读取时的几个情况

  • 脏读：读到其他事务未提交的数据；

  • 不可重复读：前后读取的数据不一致；

  • 幻读：前后读取的记录数量不一致。

    1
    2
    3
    4
    5
    不可重复读的重点是修改，幻读的重点在于新增或者删除。

    例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导致A再读自己的工资时工资变为 2000；这就是不可重复读。

    例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记 录就变为了5条，这样就导致了幻读。

脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.

不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.

幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成”幻觉”.

mysql四种隔离级别

  1. Serializable (串行化) :可避免脏读、不可重复读、幻读的发生。
  2. Repeatable read (可重复读) :可避免脏读、不可重复读的发生。
  3. Read committed (读已提交) :可避免脏读的发生。
  4. Read uncommitted (读未提交) :最低级别，任何情况都无法保证。

SQL查看隔离级别：

@@transaction_isolation;```
1
2
3

设置隔离级别：
```set session transaction isolation level read uncommitted;

生产环境数据库一般用的什么隔离级别呢？

生产环境大多使用RC(读已提交)，

1
2
缘由一：在可重复读RR隔离级别下，存在**间隙锁**，导致出现死锁的几率比RC大的多！ 
缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

InnoDB的默认隔离级别：可重复读，不能避免幻读
多版本并发控制协议MVCC(Multi- Version Concurrency Control)

索引

https://zhuanlan.zhihu.com/p/340593296

什么是索引

索引是存储引擎用于提高数据库表的访问速度的一种数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
索引一般存储在磁盘的文件中，它是占用物理空间的。

索引的分类（按字段特性）

  1. 主键索引：primary key，在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
    1
    2
    3
    4
    5
    6
    create table A(
        x int primary key, 
        //或
        x int,
        primary key(x) using BTREE
    );

建表后，创建主键索引
CREATE INDEX a ON tableA(a);
再将其添加主键约束
ALTER TABLE tableA ADD CONTRAINT id PRIMARY KEY

如果仅改一个主键ALTER TABLE tableA ADD PRIMARY KEY(a)

  1. 唯一索引：unique key建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
    1
    2
    3
    4
    create table A{
        x int unique key,
        unique key(x)
    }
  • 建表后，创建唯一索引create UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...);

  1. 普通索引/二级索引:

    1
    CREATE INDEX idx on tableA(a,b,x,y);

  2. 前缀索引: 对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引

    1
    2
    3
    4
    CREATE TABLE tableA(
        a varchar(255),
        INDEX(a(10))//字符串前10个字符匹配。
    );

【面试考点】联合索引如何使用的

指按sql里从左到右的顺序去匹配，查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成

失效的情况

如果有abc索引，可以支持查找时的 a/b/c/ac/cba(打乱)，不支持bc

  1. 联合索引范围查询

联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

【例子】

* from tableA where a> 10 and b
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
```select * from  tableA where a>= 10 and b=2;```的区别：
a都用了索引，一个没用，一个用了。因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序
   
## 索引的优缺点？

优点：
- 加快数据查找的速度
- 为用来排序或者是分组的字段添加索引，可以加快分组和排序的速度
- 加快表与表之间的连接

缺点：
- 建立索引需要占用物理空间
- 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长
## 什么情况下需要建索引？

- 经常**用于查询**的字段
- 经常**用于连接**的字段建立索引，可以加快连接的速度
- 经常**需要排序**的字段建立索引，因为索引已经排好序，可以加快排序查询速度

## 什么情况下不建索引？

- where条件中用不到的字段不适合建立索引
- 表记录较少。比如只有几百条数据，没必要加索引。
- 需要经常增删改。需要评估是否适合加索引
- 参与列计算的列不适合建索引
- 区分度不高的字段不适合建立索引，如性别，只有男/女/未知三个值。加了索引，查询效率也不会提高。

## 哈希索引
哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算得到哈希码，并且哈希算法要尽量保证不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的时间复杂度就是O(1)，一般多用于精确查找。

## 【面试题】InnoDB为什么用B+树做索引
innodb用的聚簇索引，把索引和数据一起存放，数据在叶子节点有序存放，非叶子存放索引（key和页号）；一个是有序存放适合范围查找，不需要遍历整棵树，一个是达到减少磁盘IO次数的作用。

![](/images/innodbb%2Btree.webp)
因为B+树可以实现**有序存放**和**减少磁盘IO**
知识点：
1. 适合范围查找：普通二分查找树
   由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。
2.  在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。

## 为什么 B+ 和 B树
- 从存储情况来说，B+只有叶子有数据，B树所有节点都有数据，这样就增加了磁盘IO次数
- B+ 的非叶子结点仅有关键字，适合搜索

B 树的内部节点既包含关键字也包含指向实际数据的指针，而 B+ 树的内部节点仅包含关键字，实际数据只存储在叶子节点中。这使得 B+ 树在磁盘存储和范围查询等方面具有优势，适合作为数据库索引的数据结构

### 【再问】为什么不用B树
B树和B+的时间复杂度查找都是O(logN)，但是B 树可能会因为树的分支过多，导致需要进行多次磁盘访问。并且B+支持多级索引，很容易扩展。
- 更适合磁盘存储：

B+ 树的叶子节点形成了一个有序链表，这使得范围查询的效率非常高，因为相邻的元素会被存储在相邻的位置，可以在一个或者很少几个相邻的节点中找到所有需要的数据。
B+ 树的叶子节点包含了所有的数据记录，这意味着每次查找都可以直接定位到具体的数据行，而不需要额外的中间层节点来获取实际数据。

1
2
3
- 支持多级索引

B+ 树可以很容易地扩展为多级索引。在多级索引中，每一层都是一个独立的 B+ 树，它们之间通过指针进行连接。这样的设计使得在大量数据的情况下也可以保持高效的检索速度。

举例来说，假设我们有一个三级索引 (a, b, c)，那么：
第一级索引以 a 为键构建一颗 B+ 树，每个节点中存储 b 的值以及指向第二级索引的指针。
第二级索引以 b 为键构建一颗 B+ 树，每个节点中存储 c 的值以及指向第三级索引的指针。
第三级索引以 c 为键构建一颗 B+ 树，叶子节点中存储了对应的数据记录。
这样的设计使得在多级索引中，每一层都能帮助缩小搜索范围，从而提高查询效

其中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
## 【面试】 索引失效的情况
https://mp.weixin.qq.com/s/mwME3qukHBFul57WQLkOYg

- 左/左右模糊匹配`like %a`
- 使用函数，但是8.0之后出现了函数索引
- 表达式计算
- 隐式类型转换，比如name是varchar，查询时使用`select 8 from A where name=11111;`
- 联合索引的非**最左匹配**：
- WHERE 里面的 OR操作，导致全表扫描：OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，解决办法将其都加上索引。

## MySQL什么情况下索引会失效
1. 组合索引时，如果查询条件没有使用最左边的字段，就不会使用索引
2. like进行匹配时，如果字符串前面含有%百分号，就会全表扫描时，不使用索引，
3. 还有一种情况，是如果查询条件中类型是字符串，没有引号，发生了隐式转换就不会使用索引
4. 对索引列进行运算
5. 判断索引列是否不等于某个值时
6. 查询条件使用or连接，也会导致索引失效

## 【面试题】索引下推是什么，回表是什么
- **explain会显示Extra字段为using index condition，表示使用了索引下推**
- 查询的时候截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。
- 回表也叫二次查询，指在数据库中执行一个查询，如果查询的字段不在查询的索引中，数据库可能需要通过索引定位到相应的行，然后再去实际的数据页中获取所需的字段值，这个过程就被称为回表。
以索引举例，查了两个索引，那么就是先根据第一个索引找到符合要求的值，然后在这些行里用第二个索引进行过滤。

## 【面试】最左匹配的一个问题：当where a=1 and c=3时，符合最左匹配吗
答案：符合最左，只有a用了索引，c字段没使用
严格意义上来说是属于索引截断。

- MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。
- 从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。
## 【面试】讲一下回表和索引下推的区别
- **回表是先通过索引查询行，再访问数据行涉及到两次磁盘访问**
- **索引下推是先在索引上执行一部分或全部的查询条件，大大减少磁盘访问次数**

**回表（Lookup）：**

定义：
回表是指在使用索引定位到符合查询条件的行后，再次访问实际的数据行，从中获取所需的字段值的过程。

过程：
使用索引定位到符合查询条件的行的位置。
从定位到的位置中读取行的指针或主键。
使用指针或主键再次访问实际的数据行，从中获取所需的字段值。

代价：
回表的代价相对较高，因为它涉及了两次磁盘/内存访问。

**索引下推（Index Pushdown）：**

定义：
索引下推是指在查询执行过程中，数据库管理系统会尝试在索引上执行部分或全部的查询条件，从而减少需要访问实际数据行的次数。

过程：
当查询中的条件可以在索引中找到匹配项时，数据库会尝试在索引上执行这部分查询条件，以过滤掉不符合条件的行。 只有符合索引条件的行才会被返回给用户。

优势：
减少了回表的次数，降低了查询的代价，提高了查询的性能。
减少了磁盘/内存访问次数，尤其在大型数据集中，效果显著。

适用情况：
索引下推通常在涉及到范围查询、排序、聚合等操作时可以发挥较大的优化作用。

## Hash索引和B+树索引的区别？

哈希索引**不支持排序**，因为哈希表是无序的。
哈希索引**不支持范围查找**。
哈希索引**不支持模糊查询**及组合索引的最左前缀匹配。
因为哈希表中会存在哈希冲突，所以哈希索引的性能是**不稳定**的，
而B+树索引的性能是**相对稳定的**，每次查询都是从根节点到叶子节点。

## 为什么B+树比B树更适合实现数据库索引？【】

- 由于B+树的数据都存储在叶子结点中，**叶子结点均为数据，方便扫库**，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，而在数据库中基于范围的查询是非常频繁的，所以通常B+树用于数据库索引。

B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。

B+树的查询效率更加稳定，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

## 什么是覆盖索引？

select的数据列只用从索引中就能够取得，不需要回表进行二次查询，也就是说查询列要被所使用的索引覆盖。对于innodb表的二级索引，如果索引能覆盖到查询的列，那么就可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL使用b+树索引做覆盖索引。

对于使用了覆盖索引的查询，在查询前面使用explain，输出的extra列会显示为using index。

比如user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

```explain select blog_id from user_like where user_id = 13;

explain结果的Extra列为Using index，查询的列被索引覆盖，并且where筛选条件符合最左前缀原则，通过索引查找就能直接找到符合条件的数据，不需要回表查询数据。

select user_id from user_like where blog_id 
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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

explain结果的Extra列为Using where; Using index， 查询的列被索引覆盖，where筛选条件不符合最左前缀原则，无法通过索引查找找到符合条件的数据，但可以通过索引扫描找到符合条件的数据，也不需要回表查询数据。

# 数据库引擎
## InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，使用最广泛，基于**聚簇索引**建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。

**优点**：支持事务和崩溃修复能力；引入了行级锁和外键约束。

**缺点**：占用的数据空间相对较大。

**适用场景**：需要事务支持，并且有较高的并发读写频率。

### 什么是聚簇索引？【索引结构和数据一起存放的索引，根据主键创建的索引，用B+树创建】

InnoDB使用**表的主键构造主键索引树**，同时叶子节点中存放的即为整张表的记录数据。聚集索引叶子节点的存储是逻辑上连续的，使用双向链表连接，叶子节点按照主键的顺序排序，因此对于主键的排序查找和范围查找速度比较快。

聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。

对于InnoDB来说，聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引。如果没有主键也没有合适的唯一索引，那么InnoDB内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键长度为6个字节，它的值会随着数据的插入自增。
### 聚簇和非聚簇索引
- 聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引，一般情况还需要回表查询，如果查询的字段命中索引就不需要回表了，比如select age where age = 10.
- or：聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引
      非聚簇索引的叶子节点存放的是主键值或数据记录的地址（InnoDB辅助索引的data域存储相应记录主键的值，MyISAM辅助索引的data域保存数据记录的地址）

## MyISAM存储引擎

数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

**优点**：访问速度快

**缺点**：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。

**适用场景**：对事务完整性没有要求；表的数据都会只读的。

## InnoDB和MyISAM的区别
1. myisam支持到表锁，Innodb支持表锁，进一步支持到行锁，粒度更细，解决脏读和不可重复度。使用场景myisam适合读场景多，crud场景少的场景，比如博客这些；innodb适合事务支持，高并发等情况
2. myisam不支持事务，innodb有binlog可以恢复数据库
3. myisam数据存储是直接查到内存地址，innodb是有数据缓存
4. myisam索引是非聚簇索引，索引里只有innodb是聚簇索引
## 两者索引的区别【非聚簇索引myisam，看3.】
1. myisam使用非聚簇索引，也是b+树，innodb使用聚簇索引，B+树
2. myisam的索引和数据存储是分开的，聚簇索引是将数据和索引存储在一起
3. 将数据存储于索引分开结构，索引结构的叶子节点指向了数 据的对应行，myisam 通 过 key_buffer 把索引先缓存到内存中，当需要访问 数据时(通过索引访问数据)，在内存中直接 搜索索引，然后通过索引找 到磁盘相应数据，这也就是为什么索引不在 key buffer 命中时，速度慢 的 原因。

## 【重点】为什么innodb比myisam慢了点
- myisam是非聚簇，索引和数据是分开的，在查询时，可以直接访问到索引文件，而不需要额外的查找操作。
- 而在 InnoDB 中，由于使用了聚簇索引，查询时可能需要在索引中定位到主键，再根据主键访问数据行
# 事务Transaction
## InnoDB 引擎通过什么技术来保证事务的这ACID特性的？

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

*MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生，因为使用「串行化」隔离级别会影响性能*
https://xiaolincoding.com/mysql/transaction/phantom.html#什么是幻读
## 可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

## 【面试题】为什么事务要有隔离性，我们就要知道并发事务时会引发什么问题。
数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

## 多事务的并发进行一般会造成以下几个问题: 

锁：共享锁和排他锁（读写锁）

# 【面试重点】锁
锁分为全局锁，表锁，行锁，下面介绍各锁的使用和场景

## 全局锁
**应用场景**：全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。
* 如果在主库备份，在备份期间不能更新，业务停止，所以更新业务会处于等待状态
* 如果在从库备份，在备份期间不能执行主库同步的binlog，导致主从延迟
**缺点**：意味着整个数据库都是只读状态，备份花时间长，无法执行其他操作。
但是MYSQL解决了这个问题，通过**可重复度**，使用ReadView，事务操作时用ReadView，MVCC支持备份与事务同时进行。

- 上锁1`flush tables with READ lock;`锁定所有的表，防止其他会话对这些表进行写操作，但允许读操作
- 上锁2`lock tables [tablename] READ/WRITE`
- `unlock tables;`

## 表级锁
### 表锁
**应用场景**:表级别的共享锁=读锁,独占锁=写锁
- 尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能
- `lock tables [tablename] read/write;` 
### 元数据锁
**应用场景**不需要显式使用，在CRUD/alter中自动创建，select执行完才可以执行其他操作，
- MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。
  
【引申问题】
那如果数据库有一个长事务（所谓的长事务，就是开启了事务，但是一直还没提交），
首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；

然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；

接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

**那么为什么线程 C 因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？**

因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

### 意向锁
**用于快速判断表是否加了锁**。因为：

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

### 自增锁
```java
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY) // 指定自动生成主键的策略
@Column(name = "id")
private int id;

在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

应用场景

行锁

记录锁 Record Lock，仅仅把一条记录锁上；

间隙锁 Gap Lock，锁定一个范围，但是不包含记录本身；间隙锁的意义只在于阻止区间被插入

临键锁 Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

  • 【普通的select没有行锁】普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

    记录锁 Record Lock

    记录锁分为S锁和X锁。
  • 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
  • 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

    间隙锁 Gap Lock

    只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。
  • 对间隙加锁是为了防止插入/删除的时候出现幻读
  • 间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

    临键锁 Next-key Lock

    理解为一个范围的间隙锁

【面试题】MYSQL怎么加锁

  1. 【加行锁】在查询时对记录加行级锁，这两种查询会加锁的语句称为锁定读。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    //对读取的记录加共享锁(S型锁)
    select ... lock in share mode;

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

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

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

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。

普通查询，没有使用索引的话，会导致什么情况？

没有使用索引字段作查询条件的话，导致扫描是全表扫描。那么，每一条记录的索引上都会加 临键（NK）锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

  • 如果在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。
    这条 update 语句产生了 4 个记录锁（有几条记录就有几个Record锁）和 5 个间隙锁（范围+1），相当于锁住了全表。

那 update 语句的 where 带上索引就能避免全表记录加锁了吗？

关键还得看这条语句在执行过程种，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

update 没加索引，加的是表锁还是行锁

对每一行都加了NK锁，就锁了整张表。

避免全表锁定

将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

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

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

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

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

    另外：如果 where 条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用 FORCE INDEX([index_name]) 可以告诉优化器使用哪个索引，以此避免有几率锁全表带来的隐患。

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

插入意向锁

插入意向锁的生成时机：

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。

三大日志

binlog，归档日志，逻辑日志，属于Server层，与引擎无关

保证一致性C；用于数据库的数据备份、主备、主主、主从复制

  1. 事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中，事务一次性写入，后台给一个线程去写

三种模式：statement、row和mixed.

  • statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
  • row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
  • mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

show master status

在my.ini文件中 查看bin_log文件位置

redolog：宕机恢复数据，物理日志

Redo Log记录的是新数据的备份。

保证持久性D

  1. 查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中
  2. 后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能
  3. 更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新，然后会把修改内容记录到redologbuffer里，接着刷盘到 redo log 文件里

undolog：回滚，缺点-写操作较多

在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

保证原子性A

  • 所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作
  • MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

刷盘时机

  • 每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

https://gitee.com/mydb/interview

如何做增量备份

  1. mysqldump 做一个全备
  2. mysqlbinlog 做一个增量备份

innodb使用自增ID当主键【顺序添加，uuid插入值会造成页面的碎片和不紧凑的索引结构】

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面

MyISAM和InnoDB使用B+树做索引，区别是什么【innodb使用聚簇，myisam使用非聚簇】

  1. Innodb的聚簇：数据在叶子节点有序存放，非叶子存放key和页号，也就是一棵B+树的索引文件本身就可以是数据文件，找到了索引就找到了对应的行数据。
  2. 叶节点存key和data。data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录，这被称为“非聚簇索引”

    mysql自增主键不连续的情况原因有三个:

    1) 唯一键冲突
    2) 事务回滚
    3) insert…select的时候会出现主键id每次双倍分配导致主键id跳过的问题

高并发场景下，如何安全修改同一行数据

  1. 悲观锁，innodb的事务支持加行锁
  2. 乐观锁，每次修改时先判断版本号是否一直，如果更改了就返回失败/重试，使用版本号机制，或者CAS算法实现
  3. CAS算法

select …for update语句是我们经常使用手工加锁语句。

用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止

超大数据

如果mysql limit加载超多10000，如何解决

  1. 如果id连续，分离出来几个范围查找
  2. 利用子查询优化超多分页场景。（先快速定位需要获取的id段，然后再关联）
  3. order by id 10000，10
  4. 更改业务，一般不需要那么多数量

千万数据，可以用分库分表优化表结构

  1. 分表方案（水平分表，垂直分表，切分规则hash等）
  2. 分库分表一些问题（事务问题？跨节点Join的问题）
  3. 分库分表中间件（Mycat，sharding-jdbc等）
  4. 解决方案（分布式事务等如何做）

【思格】简述count(1)、count(*)与count(列名)的执行区别 ?

count(*)：包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL
count(1)：包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL
count(列名)：只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。
执行效率上：

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（）最优。