mysql

fxz大约 163 分钟interview

mysql

一条 SQL 语句在 MySQL 内部是如何执行的?

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器： 按照 MySQL 认为最优的方案去执行。
执行器： 执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
插件式存储引擎 ：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

MySQL 支持哪些存储引擎？默认使用哪个？

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。

MySQL 存储引擎架构了解吗？

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

MyISAM 和 InnoDB 的区别是什么？

1.是否支持行级锁

	MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

2.是否支持事务

	MyISAM 不提供事务支持。

	InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别，具有提交(commit)和回滚(rollback)事务的能力。并且，InnoDB 默认使用的 REPEATABLE-READ（可重读）隔离级别是可以解决幻读问题发生的（基于 MVCC 和 Next-Key Lock）。

3.是否支持外键

	MyISAM 不支持，而 InnoDB 支持。

	外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。因此，通常情况下，我们是不建议在实际生产项目中使用外键的，在业务代码中进行约束即可！

	阿里的《Java 开发手册》也是明确规定禁止使用外键的。

4.是否支持数据库异常崩溃后的安全恢复

	MyISAM 不支持，而 InnoDB 支持。

	使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 `redo log` 。

5.是否支持 MVCC

	MyISAM 不支持，而 InnoDB 支持。

6.索引实现不一样。

	虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

	InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。

MySQL 查询缓存?

执行查询语句的时候，会先查询缓存。开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。

不过，MySQL 8.0 版本后移除，因为这个功能不太实用。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。

何谓数据库事务？

原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性（Consistency）：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durabilily）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

🌈 这里要额外补充一点：只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。也就是说 A、I、D 是手段，C 是目的！

并发事务带来了哪些问题?

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务 1 读取某表中的数据 A=20，事务 2 也读取 A=20，事务 1 修改 A=A-1，事务 2 也修改 A=A-1，最终结果 A=19，事务 1 的修改被丢失。
不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。这种情况通常出现在范围查询中。

不可重复读和幻读有什么区别呢？

不可重复读的重点是内容修改或者记录减少比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点在于记录新增比如多次执行同一条查询语句（DQL）时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

对于幻读，一般可以通过锁定整个范围或者使用一致性的快照读来解决，例如在数据库中使用Serializable隔离级别或者通过一定的乐观并发控制机制来避免范围查询时的幻读问题。
对于不可重复读，通常可以通过锁定特定行或者使用事务版本控制（如乐观锁）来解决，并确保事务在读取数据的同时能够防止其他事务对数据进行修改。

SQL 标准定义了哪些事务隔离级别?

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

解决幻读的方法？

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。解决幻读的方式主要有以下几种：

将事务隔离级别调整为 SERIALIZABLE 。
在可重复读的事务级别下，给事务操作的这张表添加表锁。
在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）。

MySQL 的隔离级别是基于锁实现的吗?

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别，是通过锁来实现的。除了 SERIALIZABLE 隔离级别，其他的隔离级别都是基于 MVCC 实现。

不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

MySQL 的默认隔离级别是什么?

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

表级锁和行级锁了解吗？有什么区别？

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

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

表级锁和行级锁对比 ：

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

共享锁和排他锁呢？

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

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

排他锁与任何的锁都不兼容，共享锁仅和共享锁兼容。

	S 锁	X 锁
S 锁	不冲突	冲突
X 锁	冲突	冲突

InnoDB 有哪几类行锁？

MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock） ：用于锁定某条记录，阻止其他事务对该记录进行修改。当事务需要修改或删除某条记录时，会对该记录加上记录锁，防止其他事务同时修改同一条记录。
间隙锁（Gap Lock） ：用于锁定一个范围，但不包括记录本身。当事务需要插入一条新记录时，会对该记录前后的间隙加上间隙锁，防止其他事务插入具有相同索引值的记录。这样可以避免幻读的问题，确保新插入的记录不会影响到其他事务的查询结果。
临键锁（Next-key Lock） ：Record Lock+Gap Lock，是记录锁和间隙锁的结合，它不仅锁定记录本身，还锁定记录之前的间隙，确保其他事务不能插入相同索引值的记录，同时也防止了幻读的问题。

InnoDB 的默认隔离级别 RR（可重读）是可以解决幻读问题发生的，主要有下面两种情况：

快照读（一致性非锁定读）：由 MVCC 机制来保证不出现幻读。
当前读 （一致性锁定读）：使用 Next-Key Lock 进行加锁来保证不出现幻读。

当前读和快照读

当前读（每次都是读到最新的数据）

Mysql实现当前读是通过共享锁+排他锁+Next-Key Lock实现的。

每次对行数据进行读取的时候，加共享锁。此时就不允许修改，但是允许其他事务读取，所以每次都可以读到最新的数据。
每次对行数据进行修改的时候，加排他锁，不允许其他事务读取和修改。这种情况下其他事务读取的数据也一定是最新的数据。
每次对范围行数据进行读取的时候，对这个范围加一个范围共享锁。
每次对范围行数据进行修改的时候，读这个范围加一个范围排它锁。
基于上述锁机制，实现当前读，确保每次读取的都是最新的数据。

快照读（根据快照生成的时间，读的不一定是最新的数据） mysql中的快照读是通过MVCC+undolog实现的。快照读，顾名思义，就是读取快照数据，也就是说当某个数据正在被修改的时候，也可以进行读取该数据，保证读写不冲突。刚刚提到undolog，当我们对记录做了变更操作时，就会产生undo记录，undo记录中存储的是老版数据，当一个旧的事务需要读取数据时，为了能够读取到老版本的数据，需要顺着undo列找到满足其可见性的记录，这个找满足可见行的记录依赖。就是说每次都是读取undolog中的数据。

在读未提交隔离级别下，快照是什么时候生成的？

没有快照，因为不需要，怎么读都读到最新的。不管是否提交

在读已提交隔离级别下，快照是什么时候生成的？

SQL语句开始执行的时候。

在可重复读隔离级别下，快照是什么时候生成的？

事务开始的时候。

怎么知道执行的语句是当前读还是快照读？

1.在默认隔离级别（可重复读）下，select 语句默认是快照读

select a from t where id = 1

2.select 语句加锁是当前读

共享锁
select a from t where id = 1 lock in share mode;

#排他锁
select a from t where id = 1 for update;

3.update 语句是当前读

何为索引？有什么作用？

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

索引的原理：就是把⽆序的数据变成有序的查询。

索引的优缺点?

优点：

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

缺点：

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

为什么MySQL 没有使用Hash作为索引的数据结构呢？

1.Hash 冲突问题。

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

B 树& B+树?

	B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 `Balanced` （平衡）的意思。

目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点(双向链表)。

B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

  在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是，两者的实现方式不太一样。
  
  MyISAM 引擎中，B+Tree 叶节点的 data 域存放的是数据记录的地址。在索引检索的时候，首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
  
  InnoDB 引擎中，其数据文件本身就是索引文件。相比 MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按 B+Tree 组织的一个索引结构，树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的 data 域存储相应记录主键的值而不是地址，这也是和 MyISAM 不同的地方。在根据主索引搜索时，直接找到 key 所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

索引类型?

主键索引(Primary Key)

  数据表的主键列使用的就是主键索引。
  
  一张数据表有只能有一个主键，并且主键不能为 null，不能重复。
  
  在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

二级索引(辅助索引)

  二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
  
  唯一索引，普通索引，前缀索引等索引属于二级索引。

唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。 建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。
普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小，因为只取前几个字符。
全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，是目前搜索引擎数据库使用的一种技术。Mysql5.6 之前只有 MYISAM 引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。

二级索引:

聚集索引与非聚集索引以及优缺点?

聚集索引

  聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。
  
  在 MySQL 中，InnoDB 引擎的表的 `.ibd`文件就包含了该表的索引和数据，对于 InnoDB 引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
  
  聚集索引的优点:
  		聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
  
  聚集索引的缺点：
  1. 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  2. 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

非聚集索引

  非聚集索引即索引结构和数据分开存放的索引。
  
  二级索引属于非聚集索引。
  
  非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。
  
  非聚集索引的优点：
  		更新代价比聚集索引要小 。非聚集索引的更新代价就没有聚集索引那么大了，非聚集索引的叶子节点是不存放数据的
  
  非聚集索引的缺点：
  1. 跟聚集索引一样，非聚集索引也依赖于有序的数据
  2. 可能会二次查询(回表) :这应该是非聚集索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

聚集索引和非聚集索引:

非聚集索引一定回表查询吗(覆盖索引)?

非聚集索引不一定回表查询。

覆盖索引？

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。
再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引，那么直接根据这个索引就可以查到数据，也无需回表。

联合索引？

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。

所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

创建索引的注意事项？

1.选择合适的字段创建索引：

不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。
被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

2.被频繁更新的字段应该慎重建立索引。

	虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

3.尽可能的考虑建立联合索引而不是单列索引。

	因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

4.注意避免冗余索引 。

	冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name,city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引。

	前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引

使用索引的一些建议？

对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
避免 where 子句中对字段施加函数，这会造成无法命中索引。
在使用 InnoDB 时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用
在使用 limit offset 查询缓慢时，可以借助索引来提高性能

redo log是什么？Mysql为什么不丢数据

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。

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

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

redo log刷盘时机？Mysql为什么不丢数据

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。

innodb_flush_log_at_trx_commit=0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

innodb_flush_log_at_trx_commit=1时，只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失。

innodb_flush_log_at_trx_commit=2时，只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

为什么不直接刷盘修改后的数据，而是刷盘redo log?Mysql为什么不丢数据

数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，没有必要把完整的数据页刷盘。

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

什么是binlog？Mysql为什么不丢数据

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

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

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

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

binlog记录格式？Mysql为什么不丢数据

binlog 日志有三种格式，可以通过binlog_format参数指定。

statement
row
mixed

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，记录的内容如下。

同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

为了解决这种问题，我们需要指定为row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据，记录内容如下。

row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。

update_time=now()变成了具体的时间update_time=1627112756247，条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值（假设这张表只有 3 个字段）。

这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。

但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

所以就有了一种折中的方案，指定为mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式

binlog的写入机制？Mysql为什么不丢数据

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

binlog日志刷盘流程如下

undo log？Mysql为什么不丢数据

我们知道如果想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在 MySQL 中，恢复机制是通过 回滚日志（undo log） 实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再执行相关的操作。

如果执行过程中遇到异常的话，我们直接利用 回滚日志 中的信息将数据回滚到修改之前的样子即可！并且，回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

sql注入问题？

原因:用户传入的参数中注入符合sql的语法，从而破坏原有sql结构语意，达到攻击效果。

数据库的三范式是什么？

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖与其他表的非主属性外键约束

NULL和空串判断?

NULL值是没有值,，它不是空串。如果指定''(两个单引号，其间没有字符)，这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。

判断NULL需要用 IS NULL 或者 IS NOT NULL。

like走索引吗?

Xxx% 走索引， %xxx不走索引。

主键与索引有什么区别？

主键一定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

主键不允许为空值，唯一索引列允许空值；

一个表只能有一个主键，但是可以有多个唯一索引；

主键可以被其他表引用为外键，唯一索引列不可以；

主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构，两者有本

索引不生效的情况？

使用不等于查询 NULL值

列参与了数学运算或者函数

在字符串like时左边是通配符.比如 %xxx

当mysql分析全表扫描比使用索引快的时候不使用索引.

当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引

MVVC？

MVCC 全称是多版本并发控制系统，是一种用来解决读 - 写冲突的无锁并发控制。

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动新增，事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。

也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

varchar(10)和int(10)代表什么含义？

varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度。

int的10只是代表了展示的长度, 不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示。

count(*)在不同引擎的实现方式?

MyISAM :把一个表的总行数存在了磁盘上，执行 count() 的时候会直接返回这个数，效率很高。

InnoDB : 比较麻烦，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

锁的类型有哪些呢？

	mysql 锁分为共享锁和排他锁，也叫做读锁和写锁。

	读锁是共享的，可以通过 lock in share mode 实现，这时候只能读不能写。

	写锁是排他的，它会阻塞其他的写锁和读锁。

	从颗粒度来区分，可以分为表锁和行锁两种。 表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如 alter 修改表结构的时候会锁表。 行锁又可以分为乐观锁和悲观锁，悲观锁可以通过 for update 实现，乐观锁则通过版本号实现。

那 ACID 靠什么保证的呢？

A 原子性由 undo log 日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的 sql

C 一致性是由其他三大特性保证，程序代码要保证业务上的一致性

I 隔离性由 MVCC 来保证

D 持久性由内存 + redo log 来保证，mysql 修改数据同时在内存和 redo log 记录这次操作，事务提交的时候通过 redo log 刷盘，宕机的时候可以从 redo log 恢复

说说 mysql 主从同步怎么做的吧？

首先先了解 mysql 主从同步的原理

master 提交完事务后，写入 binlog
slave 连接到 master，获取 binlog
master 创建 dump 线程，推送 binglog 到 slave
slave 启动一个 IO 线程读取同步过来的 master 的 binlog，记录到 relay log 中继日志中
slave 再开启一个 sql 线程读取 relay log 事件并在 slave 执行，完成同步
slave 记录自己的 binglog

由于 mysql 默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入 binlog 后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作完成。

解释MySQL外连接、内连接与自连接的区别？

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。

右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。

SQL语言包括哪几部分？每部分都有哪些操作关键字？

SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询（DQL）四个部分。

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等

数据操纵：Select ,insert,update,delete,

数据控制：grant,revoke

数据查询：select

SQL优化手段有哪些？

1、查询语句中不要使用select * (无法覆盖索引)

2、尽量减少子查询，使用关联查询（left join,right join,inner join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时， union all会更好)

5、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有 null值，然后这样查询： select id from t where num=0

简单说一说drop、delete与truncate的区别？

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

delete和truncate只删除表的数据不删除表的结构速度,

一般来说: drop> truncate >delete

delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;

truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚.

TRUNCATE 操作会重置自增长计数器等，相比 DELETE 更为高效。

大表如何优化？

限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读；

垂直分区

根据数据库里面数据表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

垂直拆分的优点：可以使得列数据变小，简化表的结构，易于维护。

垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨节点Join性能较差，逻辑复杂。

下面补充一下数据库分片的两种常见方案：

客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

可以使用多少列创建索引？

任何标准表最多可以创建 16 个索引列。

mysql锁的类型有哪些？

基于锁的属性分类：共享锁、排他锁。

基于锁的粒度分类：行级锁（innodb ）、表级锁（ innodb 、myisam）、页级锁（ innodb引擎）、记录锁、间隙锁、临键锁。

基于锁的状态分类：意向共享锁、意向排它锁。

共享锁（share lock）：共享锁又称读锁，简称 S 锁；当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避免出现重复读的问题。

排他锁（exclusive lock）：排他锁又称写锁，简称 X 锁；当一个事务为数据加上写锁时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取，避免了出现脏数据和脏读的问题。

表锁（table lock）：表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问；特点：粒度大，加锁简单，容易冲突；

行锁：行锁是指上锁的时候锁住的是表的某一行或多行记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问，特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高

记录锁（Record lock）:记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录，加了记录锁之后数据可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题

页锁：页级锁是 MysQL 中锁定粒度介于行级锁和表级锁中间的一种锁．表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。特点：开销和加锁时间界于表锁和行锁之间，会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

间隙锁：是属于行锁的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读）的事务级别中。

临键锁（Next-Key lock)：也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

怎么处理MySQL的慢查询？

1、开启慢查询日志，准确定位到哪个sql语句出现了问题。

2、分析sql语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

3、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

4、如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

mysql为什么需要主从同步？

1、在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

2、做数据的热备

3、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

Innodb是如何实现事务的

Innodb通过Buffer Pool，LogBuffer，Redo Log，Undo Log来实现事务，以⼀个update语句为例：

Innodb在收到⼀个update语句后，会先根据条件找到数据所在的⻚，并将该⻚缓存在Buffer Pool中
执⾏update语句，修改Buffer Pool中的数据，也就是内存中的数据
针对update语句⽣成⼀个RedoLog对象，并存⼊LogBuffer中
针对update语句⽣成undolog⽇志，⽤于事务回滚
如果事务提交，那么则把RedoLog对象进⾏持久化，后续还有其他机制将Buffer Pool中所修改的数据⻚持久化到磁盘中
如果事务回滚，则利⽤undolog⽇志进⾏回滚

b 树和 b+树的理解

二叉树，每个节点支持两个分支的树结构，相比于单向链表，多了一个分支。

二叉查找树，在二叉树的基础上增加了一个规则，左子树的所有节点的值都小于它的根节点，右子树的所有子节点都大于它的根节点。

二叉查找树会出现斜树问题，导致时间复杂度增加，因此又引入了一种平衡二叉树，它具有二叉查找树的所有特点，同时增加了一个规则：”它的左右两个子树的高度差的绝对值不超过 1“。平衡二叉树会采用左旋、右旋的方式来实现平衡。

而 B 树是一种多路平衡查找树，它满足平衡二叉树的规则，但是它可以有多个子树，子树的数量取决于关键字的数量，比如这个图中根节点有两个关键字 3 和 5，那么它能够拥有的子路数量=关键字数+1。

因此从这个特征来看，在存储同样数据量的情况下，平衡二叉树的高度要大于 B 树。

B+树，其实是在 B 树的基础上做的增强，最大的区别有两个：

B 树的数据存储在每个节点上，而 B+树中的数据是存储在叶子节点，并且通过链表的方式把叶子节点中的数据进行连接。

B+树的子路数量等于关键字数

（如图所示）这个是 B 树的存储结构，从 B 树上可以看到每个节点会存储数据。

（如图所示）这个是 B+树，B+树的所有数据是存储在叶子节点，并且叶子节点的数据是用双向链表关联的。

B 树和 B+树，一般都是应用在文件系统和数据库系统中，用来减少磁盘 IO 带来的性能损耗。

以 Mysql 中的 InnoDB 为例，当我们通过select语句去查询一条数据时，InnoDB需要从磁盘上去读取数据，这个过程会涉及到磁盘 IO 以及磁盘的随机 IO（如图所示）

我们知道磁盘 IO 的性能是特别低的，特别是随机磁盘 IO。

很明显，磁盘 IO 这个过程的性能开销是非常大的，特别是查询的数据量比较多的情况下。

所以在 InnoDB 中，干脆对存储在磁盘块上的数据建立一个索引，然后把索引数据以及索引列对应的磁盘地址，以 B+树的方式来存储。

如图所示，当我们需要查询目标数据的时候，根据索引从 B+树中查找目标数据即可，由于 B+树分路较多，所以只需要较少次数的磁盘 IO 就能查找到。

为什么用 B 树或者 B+树来做索引结构？

原因是 AVL（平衡二叉树）树的高度要比 B 树的高度要高，而高度就意味着磁盘 IO 的数量。所以为了减少磁盘 IO 的次数，文件系统或者数据库才会采用 B 树或者 B+树。

https://mp.weixin.qq.com/s/d7Zfat2fP6IX5DMKKtEIjQopen in new window

for update的作用和用法？

一、for update定义

for update是一种行级锁，又叫排它锁，一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

只有当出现如下之一的条件，才会释放共享更新锁： 1、执行提交（COMMIT）语句 2、退出数据库（LOG　OFF） 3、程序停止运行

2、概念和用法

通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。

而select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子: 假设有张表user ，里面有 id 和 name 两列，id是主键。

例1: (明确指定主键，并且数据真实存在，row lock)

SELECT * FROM user WHERE id=3 FOR UPDATE;

SELECT * FROM user WHERE id=3 and name='Tom' FOR UPDATE;

例2: (明确指定主键，但数据不存在，无lock)

SELECT * FROM user WHERE id=0 FOR UPDATE;

例3: (主键不明确，table lock)

SELECT * FROM user WHERE id<>3 FOR UPDATE;

SELECT * FROM user WHERE id LIKE '%3%' FOR UPDATE;

例4: (无主键，table lock)

SELECT * FROM user WHERE name='Tom' FOR UPDATE;

注意： 1、FOR UPDATE仅适用于InnoDB，且必须在事务处理模块(BEGIN/COMMIT)中才能生效。

2、要测试锁定的状况，可以利用MySQL的Command Mode(命令模式) ，开两个视窗来做测试。

3、Myisam 只支持表级锁，InnerDB支持行级锁添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改。是表级锁时，不管是否查询到记录，都会锁定表。

3、什么时候需要使用for update？

借助for update语句，我们可以在应用程序的层面手工实现数据加锁保护操作。就是那些需要业务层面数据独占时，可以考虑使用for update。

场景上，比如火车票订票，在屏幕上显示有票，而真正进行出票时，需要重新确定一下这个数据没有被其他客户端修改。所以，在这个确认过程中，可以使用for update。

4、for update悲观锁

悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。

乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。

如何保证 REPEATABLE READ 级别绝对不产⽣幻读？

在SQL中加⼊ for update (排他锁) 或 lock in share mode (共享锁)语句实现。就是锁住了可能造成幻读的数据，阻⽌数据的写⼊操作。

mysql的update的加锁情况

在 InnoDB 事务中，对记录加锁带基本单位是 next-key 锁，但是会因为一些条件会退化成间隙锁，或者记录锁。加锁的位置准确的说，锁是加在索引上的而非行上。 1.使用了唯一索引 在 update 语句的 where 条件使用了唯一索引，那么 next-key 锁会退化成记录锁，也就是只会给一行记录加锁。 2.没有使用索引 在 update 语句的 where 条件没有使用索引，就会全表扫描，于是就会对所有记录加上 next-key 锁（记录锁 + 间隙锁），相当于把整个表锁住了。

MySQL的数据存在磁盘上到底长什么样

MyISAM

每个 MyISAM 表都以3个文件存储在磁盘上。这些文件的名称以表名开头，以扩展名指示文件类型。

.frm 文件（frame）存储表结构；

.MYD 文件（MY Data）存储表数据；

.MYI 文件（MY Index）存储表索引。

MySQL 里的数据默认是存放在安装目录下的 data 文件夹中，也可以自己修改。

.MYI 文件组织索引的方式就是 B+tree。叶子节点的 value 处存放的就是索引所在行的磁盘文件地址。

底层查找过程：

首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .MYI 文件里通过 B+tree 快速定位，从根节点开始定位查找；

找到后再把这个索引关键字（就是我们的条件）存放的磁盘文件地址拿到 .MYD 文件里面找，从而定位到索引所在行的记录。

表逻辑上相邻的记录行数据在磁盘上并不一定是物理相邻的。

InnoDB

一张 InnoDB 表底层会对应2个文件在文件夹中进行数据存储。

.frm 文件（frame）存储表结构；

.ibd 文件（InnoDB Data）存储表索引+数据。

很显然，InnoDB 把索引和数据都放在一个文件里存着了。毫无疑问，InnoDB 表里面的数据也是用 B+tree 数据结构组织起来的。

下面我们来看看它具体是怎么存储的。

.ibd 存储数据的特点就是 B+tree 的叶子节点上包括了我们要的索引和该索引所在行的其它列数据。

底层查找过程：

首先会判断查找条件 where 中的字段是否是索引字段，如果是就会先拿着这字段去 .ibd 文件里通过 B+tree 快速定位，从根节点开始定位查找；

找到后直接把这个索引关键字及其记录所在行的其它列数据返回。

为什么用了索引，SQL查询还是慢？

慢查询归纳起来大概有这么几种情况：

全表扫描
全索引扫描
索引过滤性不好
频繁回表的开销

什么是三星索引？

第一颗星 - 使用合适的索引：确保在WHERE子句中的查询列构成了合适的单列索引或联合索引，以加速数据检索。这是为了减小数据查找的开销，提高查询性能。
第二颗星 - 避免排序：当查询中涉及ORDER BY子句时，如果查询结果已经按所需列排序，就可以避免生成临时表或执行排序操作，从而提高查询效率。
第三颗星 - 覆盖索引：选择在SELECT中包含尽可能多的索引列，以避免回表查询。回表查询是指在索引中找到匹配行后，还需要进一步检索主表中的数据。通过选择索引列，可以减少回表查询的需求，从而提高查询性能。

count(1)和count(*) 哪个效率高？

count(1)和count(列名)对比

两者的主要区别是：

count(1) 会统计表中的所有的记录数，包含字段为 null 的记录。
count(字段) 会统计该字段在表中出现的次数，忽略字段为 null 的情况。即不统计字段为 null 的记录。

count(*)、count(1)和count(列名)区别

执行效果上：

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

执行效率上：

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

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么？这样的话那sql要怎么写？

做这个限制有两个原因

：一是优化器很弱，涉及多个表的查询，往往得不到很好的查询计划；

二是执行器open in new window很弱，只有nested loop join，block nested loop join和index nested loop join。

nested loop join就是分别从两个表读一行数据进行两两对比，复杂度open in new window是n^2
block nested loop join是分别从两个表读很多行数据，然后进行两两对比，复杂度也是n^2，只是少了些函数调用open in new window等overhead
index nested loop join是从第一个表读一行，然后在第二个表的索引中查找这个数据，索引是B+树索引，复杂度可以近似认为是nlognopen in new window，比上面两个好很多，这就是要保证关联字段有索引的原因
如果有hash join，就不用做这种限制了，用第一个表（小表）建hash table，第二个表在[hash table](https://www.zhihu.com/search?q=hashopen in new window table&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"answer"%2C"sourceId"%3A153450286})中查找匹配的项，复杂度是n。缺点是hash table占的内存可能会比较大，不过也有基于磁盘的[hash join](https://www.zhihu.com/search?q=hashopen in new window join&search_source=Entity&hybrid_search_source=Entity&hybrid_search_extra={"sourceType"%3A"answer"%2C"sourceId"%3A153450286})，实现起来比较复杂

二：在这种限制下SQL怎么写？

可是我确实需要两个表里的数据链接在一起啊，我们可以做个冗余open in new window，建表的时候，就把这些列放在一个表里，比如一开始有student(id, name)，class(id, description)，student_class(student_id, class_id)三张表，这样是符合数据库范式的(第一范式，第二范式，第三范式open in new window，BC范式等)，没有任何冗余，但是马上就不符合“编程规范“了，那我们可以用一张大表代替它，student_class_fullopen in new window(student_id, class_id, name, description)，这样name和descriptionopen in new window可能要被存储多份，但是由于不需要join了，查询的性能就可以提高很多了。

任何的规范open in new window都是在特定情况下的某种妥协，脱离了这个环境，就不一定成立了。

什么是mysql的索引下推

MySQL的索引下推（Index Condition Pushdown）是一种查询优化技术，它允许数据库在执行查询时使用索引来过滤不满足条件的行，而不必读取整个数据行，从而提高查询性能。

具体来说，索引下推的工作方式如下：

当执行一个查询时，MySQL会首先使用索引来查找满足查询条件的行。
然后，MySQL会对使用索引定位到的行应用查询中的额外过滤条件。这些额外的条件可以是与索引列无关的其他列的条件。
如果行满足额外的过滤条件，它将被包括在查询结果中，否则将被丢弃。

索引下推的优势在于它减少了磁盘和内存的读取操作，因为不需要读取整个数据行，而只需读取索引和满足额外条件的行。这降低了查询的I/O开销，提高了查询性能。

mysql为什么不丢数据(mysql七种日志)

Mysql逻辑架构

MySQL的逻辑架构大致可以分为三层：

第一层：处理客户端连接、授权认证，安全校验等。
第二层：服务器server层，负责对SQL解释、分析、优化、执行操作引擎等。
第三层：存储引擎，负责MySQL中数据的存储和提取。

redo log（重做日志）什么是redo log以及redo log刷盘策略

redo log属于MySQL存储引擎InnoDB的事务日志。

MySQL的数据是存放在磁盘中的，每次读写数据都需做磁盘IO操作，如果并发场景下性能就会很差。为此MySQL提供了一个优化手段，引入缓存Buffer Pool。这个缓存中包含了磁盘中部分数据页（page）的映射，以此来缓解数据库的磁盘压力。

当从数据库读数据时，首先从缓存中读取，如果缓存中没有，则从磁盘读取后放入缓存；当向数据库写入数据时，先向缓存写入，此时缓存中的数据页数据变更，这个数据页称为脏页，Buffer Pool中修改完数据后会按照设定的更新策略，定期刷到磁盘中，这个过程称为刷脏页。

MySQL宕机

如果刷脏页还未完成，可MySQL由于某些原因宕机重启，此时Buffer Pool中修改的数据还没有及时的刷到磁盘中，就会导致数据丢失，无法保证事务的持久性。

为了解决这个问题引入了redo log，redo Log如其名侧重于重做！它记录的是数据库中每个页的修改，而不是某一行或某几行修改成怎样，可以用来恢复提交后的物理数据页，且只能恢复到最后一次提交的位置。

redo log用到了WAL（Write-Ahead Logging）技术，这个技术的核心就在于修改记录前，一定要先写日志，并保证日志先落盘，才能算事务提交完成。

有了redo log再修改数据时，InnoDB引擎会把更新记录先写在redo log中，在修改Buffer Pool中的数据，当提交事务时，调用fsync把redo log刷入磁盘。至于缓存中更新的数据文件何时刷入磁盘，则由后台线程异步处理。

注意：此时redo log的事务状态是prepare，还未真正提交成功，要等bin log日志写入磁盘完成才会变更为commit，事务才算真正提交完成。

这样一来即使刷脏页之前MySQL意外宕机也没关系，只要在重启时解析redo log中的更改记录进行重放，重新刷盘即可。

大小固定

redo log采用固定大小，循环写入的格式，当redo log写满之后，重新从头开始如此循环写，形成一个环状。

那为什么要如此设计呢？

因为redo log记录的是数据页上的修改，如果Buffer Pool中数据页已经刷磁盘后，那这些记录就失效了，新日志会将这些失效的记录进行覆盖擦除。

上图中的write pos表示redo log当前记录的日志序列号LSN(log sequence number)，写入还未刷盘，循环往后递增；check point表示redo log中的修改记录已刷入磁盘后的LSN，循环往后递增，这个LSN之前的数据已经全落盘。

write pos到check point之间的部分是redo log空余的部分（绿色），用来记录新的日志；check point到write pos之间是redo log已经记录的数据页修改数据，此时数据页还未刷回磁盘的部分。当write pos追上check point时，会先推动check point向前移动，空出位置（刷盘）再记录新的日志。

注意：redo log日志满了，在擦除之前，需要确保这些要被擦除记录对应在内存中的数据页都已经刷到磁盘中了。擦除旧记录腾出新空间这段期间，是不能再接收新的更新请求的，此刻MySQL的性能会下降。所以在并发量大的情况下，合理调整redo log的文件大小非常重要。

crash-safe

因为redo log的存在使得Innodb引擎具有了crash-safe的能力，即MySQL宕机重启，系统会自动去检查redo log，将修改还未写入磁盘的数据从redo log恢复到MySQL中。

MySQL启动时，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。会先检查数据页中的LSN，如果这个 LSN 小于 redo log 中的LSN，即write pos位置，说明在redo log上记录着数据页上尚未完成的操作，接着就会从最近的一个check point出发，开始同步数据。

简单理解，比如：redo log的LSN是500，数据页的LSN是300，表明重启前有部分数据未完全刷入到磁盘中，那么系统则将redo log中LSN序号300到500的记录进行重放刷盘。

undo log（回滚日志）

undo log也是属于MySQL存储引擎InnoDB的事务日志。

undo log属于逻辑日志，如其名主要起到回滚的作用，它是保证事务原子性的关键。记录的是数据修改前的状态，在数据修改的流程中，同时会记录一条与当前操作相反的逻辑日志到undo log中。

我们举个栗子：假如更新ID=1记录的name字段，name原始数据为小富，现改name为程序员内点事

事务执行update X set name = 程序员内点事 where id =1语句时，先会在undo log中记录一条相反逻辑的update X set name = 小富 where id =1记录，这样当某些原因导致服务异常事务失败，就可以借助undo log将数据回滚到事务执行前的状态，保证事务的完整性。

那可能有人会问：同一个事物内的一条记录被多次修改，那是不是每次都要把数据修改前的状态都写入undo log呢？

答案是不会的！

undo log只负责记录事务开始前要修改数据的原始版本，当我们再次对这行数据进行修改，所产生的修改记录会写入到redo log，undo log负责完成回滚，redo log负责完成前滚。

回滚

未提交的事务，即事务未执行commit。但该事务内修改的脏页中，可能有一部分脏块已经刷盘。如果此时数据库实例宕机重启，就需要用回滚来将先前那部分已经刷盘的脏块从磁盘上撤销。

前滚

未完全提交的事务，即事务已经执行commit，但该事务内修改的脏页中只有一部分数据被刷盘，另外一部分还在buffer pool缓存上，如果此时数据库实例宕机重启，就需要用前滚来完成未完全提交的事务。将先前那部分由于宕机在内存上的未来得及刷盘数据，从redo log中恢复出来并刷入磁盘。

数据库实例恢复时，先做前滚，后做回滚。

如果你仔细看过了上边的 MySQL数据更新流程图 就会发现，undo log、redo log、bin log三种日志都是在刷脏页之前就已经刷到磁盘了的，相互协作最大限度保证了用户提交的数据不丢失。

bin log（归档日志）

bin log是一种数据库Server层（和什么引擎无关），以二进制形式存储在磁盘中的逻辑日志。bin log记录了数据库所有DDL和DML操作（不包含 SELECT 和 SHOW等命令，因为这类操作对数据本身并没有修改）。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启：

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

bin log也被叫做归档日志，因为它不会像redo log那样循环写擦除之前的记录，而是会一直记录日志。一个bin log日志文件默认最大容量1G（也可以通过max_binlog_size参数修改），单个日志超过最大值，则会新创建一个文件继续写。

mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mysq-bin.000001 |      8687 |
| mysq-bin.000002 |      1445 |
| mysq-bin.000003 |      3966 |
| mysq-bin.000004 |       177 |
| mysq-bin.000005 |      6405 |
| mysq-bin.000006 |       177 |
| mysq-bin.000007 |       154 |
| mysq-bin.000008 |       154 |

bin log日志的内容格式其实就是执行SQL命令的反向逻辑，这点和undo log有点类似。一般来说开启bin log都会给日志文件设置过期时间（expire_logs_days参数，默认永久保存），要不然日志的体量会非常庞大。

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set

mysql> SET GLOBAL expire_logs_days=30;
Query OK, 0 rows affected

bin log主要应用于MySQL主从模式（master-slave）中，主从节点间的数据同步；以及基于时间点的数据还原。

主从同步

通过下图MySQL的主从复制过程，来了解下bin log在主从模式下的应用。

用户在主库master执行DDL和DML操作，修改记录顺序写入bin log;
从库slave的I/O线程连接上Master，并请求读取指定位置position的日志内容;
Master收到从库slave请求后，将指定位置position之后的日志内容，和主库bin log文件的名称以及在日志中的位置推送给从库;
slave的I/O线程接收到数据后，将接收到的日志内容依次写入到relay log文件最末端，并将读取到的主库bin log文件名和位置position记录到master-info文件中，以便在下一次读取用;
slave的SQL线程检测到relay log中内容更新后，读取日志并解析成可执行的SQL语句，这样就实现了主从库的数据一致;

基于时间点还原

我们看到bin log也可以做数据的恢复，而redo log也可以，那它们有什么区别？

层次不同：redo log 是InnoDB存储引擎实现的，bin log 是MySQL的服务器层实现的，但MySQL数据库中的任何存储引擎对于数据库的更改都会产生bin log。
作用不同：redo log 用于碰撞恢复（crash recovery），保证MySQL宕机也不会影响持久性；bin log 用于时间点恢复（point-in-time recovery），保证服务器可以基于时间点恢复数据和主从复制。
内容不同：redo log 是物理日志，内容基于磁盘的页Page；bin log的内容是二进制，可以根据binlog_format参数自行设置。
写入方式不同：redo log 采用循环写的方式记录；binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上。
刷盘时机不同：bin log在事务提交时写入；redo log 在事务开始时即开始写入。

bin log 与 redo log 功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

relay log（中继日志）

relay log日志文件具有与bin log日志文件相同的格式，从上边MySQL主从复制的流程可以看出，relay log起到一个中转的作用，slave先从主库master读取二进制日志数据，写入从库本地，后续再异步由SQL线程读取解析relay log为对应的SQL命令执行。

slow query log

慢查询日志（slow query log）: 用来记录在 MySQL 中执行时间超过指定时间的查询语句，在 SQL 优化过程中会经常使用到。通过慢查询日志，我们可以查找出哪些查询语句的执行效率低，耗时严重。

出于性能方面的考虑，一般只有在排查慢SQL、调试参数时才会开启，默认情况下，慢查询日志功能是关闭的。可以通过以下命令查看是否开启慢查询日志：

mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_query_log      | OFF                                                    |
| slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log |
+---------------------+--------------------------------------------------------+

通过如下命令开启慢查询日志后，我发现 iZ2zebfzaequ90bdlz820sZ-slow.log 日志文件里并没有内容啊，可能因为我执行的 SQL 都比较简单没有超过指定时间。

mysql>  SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected

上边提到超过 指定时间 的查询语句才算是慢查询，那么这个时间阈值又是多少嘞？我们通过 long_query_time 参数来查看一下，发现默认是 10 秒。

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

这里我们将 long_query_time 参数改小为 0.001秒再次执行查询SQL，看看慢查询日志里是否有变化。

mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected

果然再执行 SQL 的时，执行时间大于 0.001秒，发现慢查询日志开始记录了。

general query log

一般查询日志（general query log）：用来记录用户的所有操作，包括客户端何时连接了服务器、客户端发送的所有SQL以及其他事件，比如 MySQL 服务启动和关闭等等。MySQL服务器会按照它接收到语句的先后顺序写入日志文件。

由于一般查询日志记录的内容过于详细，开启后 Log 文件的体量会非常庞大，所以出于对性能的考虑，默认情况下，该日志功能是关闭的，通常会在排查故障需获得详细日志的时候才会临时开启。

我们可以通过以下命令查看一般查询日志是否开启，命令如下：

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

下边开启一般查询日志并查看日志存放的位置。

mysql> SET GLOBAL general_log=on;
Query OK, 0 rows affected
mysql> show variables like 'general_log_file';
+------------------+---------------------------------------------------+
| Variable_name    | Value                                             |
+------------------+---------------------------------------------------+
| general_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ.log |
+------------------+---------------------------------------------------+

执行一条查询 SQL 看看日志内容的变化。

mysql> select * from t_config;
+---------------------+------------+---------------------+---------------------+
| id                  | remark     | create_time         | last_modify_time    |
+---------------------+------------+---------------------+---------------------+
| 1325741604307734530 | 我是广播表 | 2020-11-09 18:06:44 | 2020-11-09 18:06:44 |
+---------------------+------------+---------------------+---------------------+

我们看到日志内容详细的记录了所有执行的命令、SQL、SQL的解析过程、数据库设置等等

error log

错误日志（error log）: 应该是 MySQL 中最好理解的一种日志，主要记录 MySQL 服务器每次启动和停止的时间以及诊断和出错信息。

默认情况下，该日志功能是开启的，通过如下命令查找错误日志文件的存放路径。

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+----------------------------------------------------------------+
| Variable_name | Value                                                          |
+---------------+----------------------------------------------------------------+
| log_error     | /usr/local/mysql/data/LAPTOP-UHQ6V8KP.err |
+---------------+----------------------------------------------------------------+

注意：错误日志中记录的可并非全是错误信息，像 MySQL 如何启动 InnoDB 的表空间文件、如何初始化自己的存储引擎，初始化 buffer pool 等等，这些也记录在错误日志文件中。

mysql中的锁

行级锁和表级锁及页级锁

在MySQL数据库体系中，可以按照锁的粒度把数据库锁分为行级锁(Innodb引擎)、表级锁(MyISam引擎)和页级锁(BDB引擎 )。

行级锁

行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。
- 行级锁能大大减少数据库操作的冲突。
- 其加锁粒度最小，但加锁的开销也最大。

行级锁分为共享锁和排他锁。具体针对于这两种锁会在后续介绍。

特点
- 开销大，加锁慢；
- 会出现死锁；
- 锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁

表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。

最常使用的MYISAM与INNODB都支持表级锁定。

表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。

特点
- 开销小，加锁快；
- 不会出现死锁；
- 锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁

页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。

表级锁速度快，但冲突多，行级冲突少，但速度慢。

所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

特点
- 开销和加锁时间界于表锁和行锁之间；
- 会出现死锁；
- 锁定粒度界于表锁和行锁之间，并发度一般

MySQL常用存储引擎的锁机制

MyISAM和MEMORY采用表级锁(table-level locking)
BDB采用页面锁(page-level locking)或表级锁，默认为页面锁
InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

Innodb中的行锁与表锁

Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
- InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
- InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
- 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。。
- 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL认为全表扫效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁,而不是行锁。

因此，在分析锁冲突时, 别忘了检查 SQL 的执行计划,以确认是否真正使用了索引。

行级锁与死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。

在InnoDB中，锁是逐步获得的，就造成了死锁的可能。行级锁并不是直接锁记录，而是锁索引。
- 索引分为主键索引和非主键索引两种：
  - 如果一条语句操作了主键索引，MySQL就会锁定这条主键索引；
  - 如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

当两个事务同时执行：
- 一个锁住了主键索引，在等待其他相关索引。
- 另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

有多种方法可以避免死锁，这里只介绍常见的三种
1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

共享锁与排他锁

上面介绍过，行级锁是MySQL中锁定粒度最细的一种锁，行级锁能大大减少数据库操作的冲突。

行级锁分为共享锁和排他锁两种，本文将详细介绍共享锁及排他锁的概念、使用方式及注意事项等。

共享锁(Share Lock)

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁。
当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（exclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

意向锁

意向锁是一种不与行级锁冲突表级锁，这一点非常重要。意向锁分为两种： 1. 意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁） 2. 意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）即：意向锁是有数据引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InnoDB会先获取该数据行所在在数据表的对应意向锁。

InnoDB支持多粒度锁，特定场景下，行级锁可以与表级锁共存。

意向锁之间互不排斥，但除了IS与S兼容外，意向锁会与共享锁 / 排他锁互斥。
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
意向锁在保证并发性的前提下，实现了行锁和表锁共存且满足事务隔离性的要求。

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

对于一般的Select语句，InnoDB不会加任何锁(快照读)。

mysql日期类型选择

在 MySQL 数据库中，选择合适的日期类型对于正确存储和操作日期数据非常重要。以下是一些建议：

DATE：如果只需要存储日期信息（年、月、日），而不需要精确到具体的时分秒，可以使用 DATE 类型。DATE 类型占用 3 字节的存储空间，范围从 '1000-01-01' 到 '9999-12-31'。
DATETIME：如果需要同时存储日期和时间信息，并且需要精确到秒级别，可以使用 DATETIME 类型。DATETIME 类型占用 8 字节的存储空间，范围从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'。
TIMESTAMP：TIMESTAMP 类型也用于存储日期和时间信息，精确度为秒级，但它在存储和显示时会受到时区的影响。TIMESTAMP 类型占用 4 字节的存储空间，范围从 '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC。
TIME：如果只需要存储时间信息（时、分、秒），可以使用 TIME 类型。TIME 类型占用 3 字节的存储空间，范围从 '-838:59:59' 到 '838:59:59'。
YEAR：如果只需要存储年份信息，可以使用 YEAR 类型。YEAR 类型占用 1 字节的存储空间，范围从 1901 到 2155。

根据实际需求选择合适的日期类型，可以有效地节省存储空间并确保数据存储的准确性和完整性。同时，还需考虑到对日期数据的查询和操作需求，以便选择最适合的日期类型。

mysql

# mysql

# 一条 SQL 语句在 MySQL 内部是如何执行的?

# MySQL 支持哪些存储引擎？默认使用哪个？

# MySQL 存储引擎架构了解吗？

# MyISAM 和 InnoDB 的区别是什么？

# MySQL 查询缓存?

# 何谓数据库事务？

# 并发事务带来了哪些问题?

# 不可重复读和幻读有什么区别呢？

# SQL 标准定义了哪些事务隔离级别?

# 解决幻读的方法？

# MySQL 的隔离级别是基于锁实现的吗?

# MySQL 的默认隔离级别是什么?

# 表级锁和行级锁了解吗？有什么区别？

# 共享锁和排他锁呢？

# InnoDB 有哪几类行锁？

# 当前读和快照读

# 何为索引？有什么作用？

# 索引的优缺点?

# 为什么MySQL 没有使用Hash作为索引的数据结构呢？

# B 树& B+树?

# B 树& B+树两者有何异同呢？

# 索引类型?

# 聚集索引与非聚集索引以及优缺点?

# 非聚集索引一定回表查询吗(覆盖索引)?

# 覆盖索引？

# 联合索引？

# 最左前缀匹配原则

# 创建索引的注意事项？

# 使用索引的一些建议？

# redo log是什么？Mysql为什么不丢数据

# redo log刷盘时机？Mysql为什么不丢数据

# 为什么不直接刷盘修改后的数据，而是刷盘redo log?Mysql为什么不丢数据

# 什么是binlog？Mysql为什么不丢数据

# binlog记录格式？Mysql为什么不丢数据

# binlog的写入机制？Mysql为什么不丢数据

# undo log？Mysql为什么不丢数据

# sql注入问题？

# 数据库的三范式是什么？

# NULL和空串判断?

# like走索引吗?

# 主键与索引有什么区别？

# 索引不生效的情况？

# MVVC？

# varchar(10)和int(10)代表什么含义？

# count(*)在不同引擎的实现方式?

# 锁的类型有哪些呢？

# 那 ACID 靠什么保证的呢？

# 说说 mysql 主从同步怎么做的吧？

# 解释MySQL外连接、内连接与自连接的区别？

# SQL语言包括哪几部分？每部分都有哪些操作关键字？

# SQL优化手段有哪些？

# 简单说一说drop、delete与truncate的区别？

# 大表如何优化？

# 可以使用多少列创建索引？

# mysql锁的类型有哪些？

# 怎么处理MySQL的慢查询？

# mysql为什么需要主从同步？

# Innodb是如何实现事务的

# b 树和 b+树的理解

# for update的作用和用法？

# 如何保证 REPEATABLE READ 级别绝对不产⽣幻读？

# mysql的update的加锁情况

# MySQL的数据存在磁盘上到底长什么样

# 为什么用了索引，SQL查询还是慢？

# 什么是三星索引？

# count(1)和count(*) 哪个效率高？

# 《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么？这样的话那sql要怎么写？

# 什么是mysql的索引下推

# mysql为什么不丢数据(mysql七种日志)

# mysql中的锁

# mysql日期类型选择

mysql

一条 SQL 语句在 MySQL 内部是如何执行的?

MySQL 支持哪些存储引擎？默认使用哪个？

MySQL 存储引擎架构了解吗？

MyISAM 和 InnoDB 的区别是什么？

MySQL 查询缓存?

何谓数据库事务？

并发事务带来了哪些问题?

不可重复读和幻读有什么区别呢？

SQL 标准定义了哪些事务隔离级别?

解决幻读的方法？

MySQL 的隔离级别是基于锁实现的吗?

MySQL 的默认隔离级别是什么?

表级锁和行级锁了解吗？有什么区别？

共享锁和排他锁呢？

InnoDB 有哪几类行锁？

当前读和快照读

何为索引？有什么作用？

索引的优缺点?

为什么MySQL 没有使用Hash作为索引的数据结构呢？

B 树& B+树?

B 树& B+树两者有何异同呢？

索引类型?

聚集索引与非聚集索引以及优缺点?

非聚集索引一定回表查询吗(覆盖索引)?

覆盖索引？

联合索引？

最左前缀匹配原则

创建索引的注意事项？

使用索引的一些建议？

redo log是什么？Mysql为什么不丢数据

redo log刷盘时机？Mysql为什么不丢数据

为什么不直接刷盘修改后的数据，而是刷盘redo log?Mysql为什么不丢数据

什么是binlog？Mysql为什么不丢数据

binlog记录格式？Mysql为什么不丢数据

binlog的写入机制？Mysql为什么不丢数据

undo log？Mysql为什么不丢数据

sql注入问题？

数据库的三范式是什么？

NULL和空串判断?

like走索引吗?

主键与索引有什么区别？

索引不生效的情况？

MVVC？

varchar(10)和int(10)代表什么含义？

count(*)在不同引擎的实现方式?

锁的类型有哪些呢？

那 ACID 靠什么保证的呢？

说说 mysql 主从同步怎么做的吧？

解释MySQL外连接、内连接与自连接的区别？

SQL语言包括哪几部分？每部分都有哪些操作关键字？

SQL优化手段有哪些？

简单说一说drop、delete与truncate的区别？

大表如何优化？

可以使用多少列创建索引？

mysql锁的类型有哪些？

怎么处理MySQL的慢查询？

mysql为什么需要主从同步？

Innodb是如何实现事务的

b 树和 b+树的理解

for update的作用和用法？

如何保证 REPEATABLE READ 级别绝对不产⽣幻读？

mysql的update的加锁情况

MySQL的数据存在磁盘上到底长什么样

为什么用了索引，SQL查询还是慢？

什么是三星索引？

count(1)和count(*) 哪个效率高？

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join 这是为什么？这样的话那sql要怎么写？

什么是mysql的索引下推

mysql为什么不丢数据(mysql七种日志)

mysql中的锁

mysql日期类型选择