庄小焱

# Mysql面试问题

# 超键、候选键、主键、外键是什么？

  1. 键（key）：由关系的一个或多个属性组成，任意两个键相同的元组，所有属性都相同。需要保证表示键的属性最少。一个关系可以存在好几种键，工程中一般从这些候选键中选出一个作为主键（primary key）。
  2. 候选键（candidate key）：由关系的一个或多个属性组成，候选键都具备键的特征，都有资格成为主键。
  3. 超键（super key）：包含键的属性集合，无需保证属性集的最小化。每个键也是超键。可以认为是键的超集。
  4. 外键（foreign key）：如果某一个关系A中的一个（组）属性是另一个关系B的键，则该（组）属性在A中称为外键。

# 数据的三范式是什么？

  1. 第一范式：属性不可分,用来确保每列的原子性，要求每列（或者每个属性值）都是不可再分的最小数据单元（也称为最小的原子单元）。
  2. 第二范式：在第一范式的基础上更进一层，要求表中的每列都和主键相关，即要求实体的唯一性。如果一个表满足第一范式，并且除了主键以外的其他列全部都依赖于该主键，那么该表满足第二范式。
  3. 第三范式：在第二范式的基础上更进一层，第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。

# 索引有哪几种类型？

  1. 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  2. 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  3. 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
  4. 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
  5. 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
  6. 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

# MyISAM和InnoDB区别

MySQL 5.5版本后默认的存储引擎为InnoDB。具有良好性能，其中包括了：全文索引、压缩、空间函数等，支持事务和行级锁等。MyISAM是MySQL的默认数据库引擎（5.5版之前） 但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。默认情况下mysql的底层引擎使用的都是 InnoDB 存储引擎，但是在某些情况下使用MyISAM也是合适的比如读密集的情况下。

  1. 是否支持外键：MyISAM不支持，而InnoDB支持。
  2. 是否支持行级锁: MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
  3. 是否支持事务和崩溃后的安全恢复：MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  4. 是否支持MVCC：仅InnoDB支持。应对高并发事务,MVCC比单纯的加锁更高效;MVCC只在READ—COMMITTED和REPEATABLE—READ两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

# 七种关联查询与应用场景

  1. 左连接: 返回左表中所有的行，以及右表中匹配的行。如果没有匹配的行，则右表中对应的字段值为NULL。
  2. 右连接: 返回右表中所有的行，以及左表中匹配的行。如果没有匹配的行，则左表中对应的字段值为NULL。
  3. 内连接: 它在两个表中查找匹配的行，并且只返回符合条件的记录。
  4. 全外连接：回左表和右表中的所有行，如果没有匹配的行，则对应的字段值为NULL。
  5. 交叉连接：返回两个表中所有可能的行组合，结果集的行数等于左表的行数乘以右表的行数。
  6. 自连接： 在同一表中进行连接操作的过程，它主要用于解决表中字段之间的关联问题。
  7. 自然连接：自动匹配两个表中相同的字段，并返回它们的交集。

# 事务的ACID四大原则

  1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

# Mysql事务的实现原理

Mysq|里面的事务，满足ACID特性，所以在我看来，Mysql的事务实现原理，就是InnoDB是如何保证ACID特性的。

  • A表示AtomicO原子性，也就是需要保证多个DML操作是原子的，要么都成功，要么都失败。那么，失败就意味着要对原本执行成功的数据进行回滚，所以InnoDB设计了一个UNDO_LOG表，在事务执行的过程中把修改之前的数据快照保存到UNDO_LOG里面，一旦出现错误，就直接从UNDO_LOG里面读取数据执行反向操作就行了。
  • C表示一致性，表示数据的完整性约束没有被破坏，这个更多是依赖于业务层面的保证，数据库本身也提供了一些，比如主键的唯—余数，字段长度和类型的保证等等。
  • I表示事物的隔离性，也就是多个并行事务对同一个数据进行操作的时候，如何避免多个事务的干扰导致数据混乱的问题。而InnoDB实现了SQL92的标准，提供了四种隔离级别的实现。分别是:RU（未提交读）、RC（已提交读）、RR（可重复读）、Serializable（串行化） InnoDB默认的隔离级别是RR（可重复读），然后使用了MVCC机制解决了脏读和不可重复读的问题，然后使用了行锁/表锁的方式解决了幻读的问题。
  • D表示持久性，也就是只要事务提交成功，那对于这个数据的结果的影响一定是永久性的。不能因为宕机或者其他原因导致数据变更失效。理论上来说，事务提交之后直接把数据持久化到磁盘就行了，但是因为随机磁盘IO的效率确实很低，所以InnoDB设计了Buffer Pool缓冲区来优化，也就是数据发生变更的时候先更新内存缓冲区，然后在合适的时机再持久化到磁盘。 那在持久化这个过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性了。所以InnoDB引入了Redo_LOG文件，这个文件存储了数据被修改之后的值，当我们通过事务对数据进行变更操作的时候，除了修改内存缓冲区里面的数据以外，还会把本次修改的值追加到REDO_LOG里面。 当提交事务的时候，直接把REDO_LOG日志刷到磁盘上持久化，一旦数据库出现宕机，在Mysql重启在以后可以直接用REDO_LOG里面保存的重写日志读取出来，再执行一遍从而保证持久性。

因此，在我看来，事务的实现原理的核心本质就是如何满足ACID的，在InnDB里面用到了MVCC、行锁表锁、UNDO_LOG、REDO_LOG等机制来保证。

# 数据库的隔离级别

  1. Read uncommitted(读未提交): 就是一个事务可以读取另一个未提交事务的数据。
  2. Read committed(读提交): 就是一个事务要等另一个事务提交后才能读取数据。
  3. Repeatable read(可重复读):就是在开始读取数据（事务开启）时，不再允许修改操作
  4. Serializable (序列化): Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

# 隔离级别导致的问题

  1. 脏读（Dirty read）: 一个事务A读取了任一个事务B未提交的数据，事务B发生回滚(commit与rollback)，导致的事务A多次读取的结果不一致。
  2. 不可重复读（Unrepeatableread）: 一个事务A读取了任一个事务B已提交的数据，事务B对数据进行的修改或者删除(update与delete)，导致的事务A多次读取的结果不一致。
  3. 幻读（Phantom read）: 一个事务A读取了任一个事务B已提交的数据，事务B对数据进行新增数据(insert)，导致的事务A多次读取的结果不一致。

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

  • 不可重复读（Unrepeatableread）: 一个事务A读取了任一个事务B已提交的数据，事务B对数据进行的修改或者删除(update与delete)，导致的事务A多次读取的结果不一致。
  • 幻读（Phantom read）: 一个事务A读取了任一个事务B已提交的数据，事务B对数据进行新增数据(insert)，导致的事务A多次读取的结果不一致。

# InnoDB-MVCC原理

多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。 在内部实现中，与Postgres在数据行上实现多版本不同，InnoDB是在undo-log(回滚日志)中实现的，通过undo-log(回滚日志)可以找回数据的历史版本。 找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本) 也可以在回滚的时候覆盖数据页上的数据。在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

MVCC是被Mysql中 事务型存储引擎InnoDB 所支持的.应对高并发事务, MVCC比单纯的加锁更高效. MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作. MVCC可以使用 乐观(optimistic)锁和悲观(pessimistic)锁来实现. 各数据库中MVCC实现并不统一.InnoDB存储引擎在数据库每行数据的后面添加了三个字段来实现

# MVCC在InnoDB存储引擎的实现

当前读与快照读

快照读：普通的 select 语句。执行方式是生成 readview，直接利用 MVCC 机制来读取，并不会对记录进行加锁。 它是基于多版本并发控制即 MVCC机制，既然是多版本，那么快照读读到的数据不一定是当前最新的数据， 有可能是之前历史版本的数据。如下的操作是快照读：不加锁的 select 操作（事务级别不是串行化，串行化的是当前读）

当前读： 它读取的记录都是数据库中当前的最新版本，会对当前读取的数据进行加锁，防止其他事务修改数据，这种锁是一种悲观锁。 当前读的规则，就是要能读到所有已经提交的记录的最新值。

如下操作都是当前读：

  1. select … lock in share mode当前读，加读锁 ，也叫共享锁
  2. select … for update 当前读，加写锁，又叫排他锁
  3. innoDB 里面 update (排他锁)、insert (排他锁)、delete (排他锁)，都会自动给涉及的语句添加写锁。
  4. 串行化事务的隔离级别

三个隐藏字段

  1. 6字节的事务ID(DB_TRX_ID)字段:用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted, 并非真正删除。
  2. 7字节的回滚指针(DB_ROLL_PTR)字段:指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。如果一行记录被更新, 则 undo log record 包含 '重建该行记录被更新之前内容' 所必须的信息。
  3. 6字节的DB_ROW_ID字段: 包含一个随着新行插入而单调递增的行ID, 当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。结合聚簇索引的相关知识点, 我的理解是, 如果我们的表中没有主键或合适的唯一索引, 也就是无法生成聚簇索引的时候, InnoDB会帮我们自动生成聚集索引, 但聚簇索引会使用DB_ROW_ID的值来作为主键; 如果我们有自己的主键或者合适的唯一索引, 那么聚簇索引中也就不会包含 DB_ROW_ID 了

undo-log

undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志。 当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃。

当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除， 只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit， 并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true, 并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定时可以被清除的)

MVCC

# read-view

Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。

其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图， 把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据。

Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID(当前事务id)取出来，与系统当前其他活跃事务的id去对比， 如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较， 即遍历链表中的DB_TRX_ID，直到找到满足条件的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。

Read View中的三个全局属性:

  1. trx_list:一个数值列表，用来维护Read view生成时刻系统正活跃的事务ID(1,2,3)
  2. up_limitrid:记录trx_list列表中事务ID最小的ID (1)
  3. low_limit_id:Read View生成时刻系统尚未分配的下一个事务ID，(4)

Read View具体的比较规则如下：

  1. 首先长较DB_TRX_ID< up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断。
  2. 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断。
  3. 判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read view生成之前就已经开始commit，那么修改的结果是能够看见的。

# RC(读已提交)/RR(可重复读)隔离级别下InnoDB的快照读有啥不同？

  1. 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照即Read View,将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View,所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View,所以对之后的修改不可见。
  2. 在RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动和事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见。
  3. 在RC级别下，事务中，每次快照读都会新生成一个快照和Read View;这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。

总结: 在RC隔离级别下，是每个快照读都会生成并获取最新的Read view,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

# 索引什么时候失效？

InnoDB引擎里面有两种索引类型，一种是主键索引、一种是普通索引。InnoDB用了B+树的结构来存储索引数据。当使用索引列进行数据查询的时候，最终会到主键索引树中查询对应的数据行进行返回。 理论上来说，使用索引列查询，就能很好的提升查询效率，但是不规范的使用会导致索引失效，从而无法发挥索引本身的价值。

  1. 在索引列上做运算，比如使用函数，Mysql在生成执行计划的时候，它是根据统计信息来判断是否要使用索引的。 而在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了。不过从Mysql8开始，增加了函数索引可以解决这个问题。
  2. 不符合最左匹配法则：在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。 在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也需要按照这个顺序才能进行逐一匹配。 否则InnoDB无法识别索引导致索引失效。
  3. 当索引列存在隐式转化的时候，比如索引列是字符串类型，但是在sql查询中没有使用引号。那么Mysql会自动进行类型转化，从而导致索引失效。
  4. 在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
  5. 使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。
  6. 使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。

# 数据库优化的技术是什么?

  1. 数据的字段和表的优化
  2. 数据库表的索引优化
  3. 数据的查询优化
  4. 数据的引擎选择(InnDB还是MyISAM)
  5. 数据库的读写分离
  6. 数据的双主双从备份
  7. 数据库的分库分表操作
  8. 数据库的集群模式

# binlog和redolog有什么区别？

首先，binlog和redolog都是Mysql里面用来记录数据库数据变更操作的日志。其中binlog主要用来做数据备份、数据恢复和数据同步，大家初步接触这个概念 ， 应该是在Mysql的主从数据同步的场景中，master节点的数据变更，会写入到binlog中，然后再把binlog中的数据通过网络传输给slave节点，实现数据同步。 而redolog，主要是在Mysql数据库事务的ACID特性里面，用来保证数据的持久化特性。但是其实它还有很多的作用。比如数据库崩溃时， 可以通过Redo Log来恢复未完成的数据，保证数据的完整性。通过合理的配置Redo Log的大小和数量，还可以优化Mysql的性能。

binlog和redolog的区别

  1. 使用场景不同，binlog主要用来做数据备份、数据恢复、以及主从集群的数据同步； Redo Log主要用来实现Mysql数据库的事务恢复，保证事务的ACID特性。当数据库出现崩溃的时候，Redo Log可以把未提交的事务回滚，把已提交的事务进行持久化，从而保证数据的一致性和持久性。
  2. 记录的信息不同，binlog是记录数据库的逻辑变化，它提供了三种日志格式分别是statement，row以及mixed；redo log记录的是物理变化，也就是数据页的变化结果。
  3. 记录的时机不同， binlog是在执行SQL语句的时候，在主线程中生成逻辑变化写入到磁盘中，所以它是语句级别的记录方式； RedoLog是在InnoDB存储引擎层面的操作，它是在Mysql后台线程中生成并写入到磁盘中的，所以它是事务级别的记录方式，一个事务操作完成以后才会被写入到redo log中。

# Mysql如何解决幻读问题？

  • 幻读: 一个事务A读取了任一个事务B已提交的数据，事务B对数据进行新增数据(insert)，导致的事务A多次读取的结果不一致。
  • 在RR(也就是可重复读)的事务隔离级别下，InnoDB采用了MVCC机制来解决幻读问题。MVCC就是一种乐观锁的机制，它通过对不同事务生成不同的快照版本，通过UNDO版本链进行管理并且在MVCC里面，规定了高版本能够看到低版本的事务变更，低版本看不到高版本的事务变更从而实现了不同事务之间的数据隔离，解决了幻读的问题。
  • 在RR(也就是可重复读)的事务隔离级别下，但是在当前读的情况下，是直接读取内存的数据，跳过了快照度，所以还是会出现幻读问题。
  • InnDB中的引入了间隙锁和Next key-lock锁机制来解决幻读的。Next key-lock锁相当于间隙锁个记录锁的合集。记录锁锁定记录存在的行，间隙锁锁住的是数据行之间的间隙。

# 行锁、临键锁、间隙锁的理解

  • 行锁，也称为记录锁。当我们针对主键或者唯一索引加锁的时候，Mysql默认会对查询的这一行数据加行锁，避免其他事务对这一行数据进行修改。
  • 间隙锁，顾名思义，就是锁定一个索引区间。在普通索引或者唯一索引列上，由于索引是基于B+树的结构存储，所以默认会存在一个索引区间。而间隙锁，就是某个事物对索引列加锁的时候，默认锁定对应索引的左右开区间范围。
  • 临键锁，它相当于行锁+间隙锁的组合，也就是它的锁定范围既包含了索引记录，也包含了索引区间它会锁定一个左开右闭区间的数据范围。

所以总的来说，行锁、临键锁、间隙锁只是表示锁定数据的范围，最终目的是为了解决幻读的问题。而临键锁相当于行锁+间隙锁， 因此当我们使用非唯一索引进行精准匹配的时候，会默认加临键锁，因为它需要锁定匹配的这一行数据，还需要锁定这一行数据对应的左开右闭区间。 因此在实际应用中，尽可能使用唯一索引或者主键索引进行查询，避免大面积的锁定造成性能影响。

# bing-log、redo-log、undo-log区别

undo-log

  • undo log是一种用于撤销回退的日志，在数据库事务开始之前，MySQL会先记录更新前的数据到undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用undo log来进行回退。
  • Undo Log产生和销毁: Undo Log在事务开始前产生;事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo loag放入到删除列表中，后面会通过后台线程purge thread进行回收处理。
  • undo-log的作用: 1.提供事务的回滚操作，实现事务的原子性。2. 提供多版本控制(Mvcc)
  • 注意: undo log也会产生redo log，因为undo log也要实现持久性保护。

redo-log

  • InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。 这就是所谓的预写式技术(Write Ahead logging)。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。

  • redo log:被称作重做日志,包括两部分:一个是内存中的日志缓冲: redo log buffer，另一个是磁盘上的日志文件: redo log file 。

  • redo log的作用: mysql每执行一条DML语句，先将记录写入redo log buffer。后续某个时间点再一次性将多个操作记录写到 redo log file。 当故障发生致使内存数据丢失后，InnoDB会在重启时，经过重放redo，将Page恢复到崩溃之前的状态通过Redo log可以实现事务的持久性。

bing-log

  • binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。
  • binlog在MySQL的Server层实现(引擎共用)
  • binlog为逻辑日志,记录的是一条SQL语句的原始逻辑,binlog不限制大小,追加写入,不会覆盖以前的日志.默认情况下， binlog日志是二进制格式的，不能使用查看文本工具的命令(比如,cat,vi等）查看，而使用mysqlbinlog解析查看。
  • 主从复制:在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。

# redo-log的持久化与undo-log策略

redo log持久化: 缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统缓冲区(OS Buffer)。 因此,redo logbuffer写入redo logfile实际上是先零入OS Buffer.然后再通过系统调用下sync()将其刷到redo log file. Redo Buffer持久化到redo log的策略，可通过Innodb_flush_log_at_trx_commit设置:Innodb_flush_log_at_trx__commit

  1. 0(延迟写): 事务提交时不会将redo log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到redo log file中。也就是说设置为O时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
  2. 1(实时写,实时刷):事务每次提交都会将redo log buffer中的日志写入os buffer并调用fsync()刷到redo log file中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
  3. 2(实时写,延时刷): 每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到redo log file。

img.png

undo log持久化: MySQL中的Undo Log严格的讲不是Log，而是数据,因此他的管理和落盘都跟数据是一样的:

  1. Undo的磁盘结构并不是顺序的，而是像数据一样按Page管理.
  2. Undo写入时，也像数据一样产生对应的Redo Log (因为undo也是对页面的修改，记录undo这个操作本身也会有对应的redo)。
  3. Undo的Page也像数据一样缓存在Buffer Pool中，跟数据Page一起做LRU换入换出，以及刷脏。Undo Page的刷脏也像数据一样要等到对应的Redo Log 落盘之后
  • 当事务提交的时候, innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatableread时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。
  • 但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

# Mysql索引的优点和缺点？

在我看来，Mysql里面的索引的优点有很多

  1. 通过B+树的结构来存储数据，可以大大减少数据检索时的磁盘IO次数，从而提升数据查询的性能。
  2. B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的链表结构往下读取即可，查询效率较高。
  3. 通过唯一索引约束，可以保证数据表中每一行数据的唯一性。

当然，索引的不合理使用，也会有带来很多的缺点

  1. 数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索引的维护会带来较大的性能开销。
  2. 一个表中允许存在一个聚簇索引和多个非聚簇索引，但是索引数不能创建太多，否则造成的索引维护成本过高。
  3. 创建索引的时候，需要考虑到索引字段值的分散性，如果字段的重复数据过多，创建索引反而会带来性能降低。

# 什么是聚集索引和非聚集索引？

  1. 简单来说，聚集索引就是基于主键创建的索引，除了主键索引以外的其他索引，称为非聚集索引，也叫做二级索引。
  2. 由于在InnoDB引擎里面，一张表的数据对应的物理文件本身就是按照B+树来组织的一种索引结构，而聚集索引就是按照每张表的主键来构建一颗B+树，然后叶子节点里面存储了这个表的每一行数据记录。
  3. 所以基于InnoDB这样的特性，聚集索引并不仅仅是一种索引类型，还代表着一种数据的存储方式。
  4. 同时也意味着每个表里面必须要有一个主键，如果没有主键，InnoDB会默认选择或者添加一个隐藏列作为主键索引来存储这个表的数据行。一般情况是建议使用自增id作为主键，这样的话id本身具有连续性使得对应的数据也会按照顺序存储在磁盘上，写入性能和检索性能都很高。否则，如果使用uuid这种随机id，那么在频繁插入数据的时候，就会导致随机磁盘IO，从而导致性能较低。
  5. 需要注意的是，InnoDB里面只能存在一个聚集索引，原因很简单，如果存在多个聚集索引，那么意味着这个表里面的数据存在多个副本，造成磁盘空间的浪费，以及数据维护的困难。
  6. 由于在InnoDB里面，主键索引表示的是一种数据存储结构，所以如果是基于非聚集索引来查询一条完整的记录，最终还是需要访问主键索引来检索。

img.png

# 什么是长事务

长事务在DB服务端的表现是session持续时间长，期间可能伴随cpu、内存升高，严重者可导致DB服务端整体响应缓慢，导致在线应用无法使用， 所以在线高并发业务中应该尽量避免长事务的发生。产生长事务的原因除了sql本身可能存在问题外，和应用层的事务控制逻辑也有很大的关系。

产生长事务的原因

  1. 事务方法业务复杂，执行时间长。
  2. 数据库死锁，导致事务执行太长。
  3. 操作的数据比较多。
  4. 事务中有其他非DB的耗时操作。

长事务的影响:

  1. 并发情况下，数据库连接池非常满，对数据库服务端资源的占用（连接数、内存、cpu），增加数据库服务端的压力；
  2. 锁定太多的数据，造成大量的阻塞和锁超时。
  3. 长事务期间应用层线程资源会一直被占用，堵塞式编程中会形成堵塞等待.高并发场景下线程堵塞会造成连锁反应、雪崩效应；
  4. 回滚所需要的时间比较长。
  5. undo log膨胀。

长事务的解决方法:

  1. 在一个事务里面， 避免一次处理太多数据
  2. 在一个事务里面，尽量避免不必要的查询
  3. 在一个事务里面， 避免耗时太多的操作，造成事务超时。一些非DB的操作，比如rpc调用，消息队列的操作尽量放到事务之外操作。

博文参考

  • https://segmentfault.com/a/1190000012650596

# 悲观锁和乐观锁的区别

悲观锁:总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁 （共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制， 比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁:每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号(version)机制和CAS算法实现。 乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。 在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。 但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

# 乐观锁的缺点

  1. ABA 问题: 如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？ 很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。 JDK 1.5 以后的 AtomicStampedReference 类就提供了此种能力，其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用， 并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值。
  2. 循环时间长开销大: 自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。 如果JVM能支持处理器提供的pause指令那么效率会有一定的提升，pause指令有两个作用，第一它可以延迟流水线执行指令（de-pipeline）, 使CPU不会消耗过多的执行资源，延迟的时间取决于具体实现的版本，在一些处理器上延迟时间是零。第二它可以避免在退出循环的时候因内存顺序冲突（memory order violation）而引起CPU流水线被清空（CPU pipeline flush），从而提高CPU的执行效率。
  3. 只能保证一个共享变量的原子操作: CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性， 你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

# varchar上的索引必须要指定索引长度

MySQL中就定义了16KB为一页，一页就是树的一个节点。那么，既然一页的大小是恒定的16KB， 那也就意味着索引字段值占用的空间越小，一页能保存的数量也就越多，最终就体现在减少磁盘IO的次数上。

虽然在阿里的Java开发手册中，是强制要求varchar字段上建立索引必须要指定索引长度，但通过我们的分析也能看出， 并不是索引的字段都适用的，比如区分度不高的、或者可以大量运用覆盖索引的实现的，我认为，开发手册强调的普适场景， 具体如何运用应该是我们通过了解原理之后，自行笃定。

# 细说varchar与char原理

  • 固定长度 & 可变长度

    • VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。
    • CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是这样实现的——也就是说这些版本中CHAR和VARCHAR在逻辑上是一样的，区别只是在存储格式上)。 同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

  • 存储方式

    • VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。
    • CHAR适合存储很短或长度近似的字符串。例如，CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

  • 存储容量

    • VARCHAR MySQL行默认最大65535字节，是所有列共享（相加）的，所以VARCHAR的最大值受此限制。
    • CHAR 对于char类型来说，最多只能存放的字符个数为255，和编码无关，任何编码最大容量都是255。

# 数据库中char和varchar的区别？

长度不同

  1. char类型：char类型的长度是固定的。
  2. varchar类型：varchar类型的长度是可变的。

效率不同

  1. char类型：char类型每次修改的数据长度相同，效率更高。
  2. varchar类型：varchar类型每次修改的数据长度不同，效率更低。

存储不同

  1. char类型：char类型存储的时候是初始预计字符串再加上一个记录字符串长度的字节，占用空间较大。
  2. varchar类型：varchar类型存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。

# 既然VARCHAR长度可变，那我要不要定到最大?

就像使用VARCHAR(5)和VARCHAR(200)存储 '陈哈哈’的磁盘空间开销是一样的。那么使用更短的列有什么优势呢？ 事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。

当然，在没拿到存储引擎存储的数据之前，并不会知道我这一行拿出来的数据到底有多长，可能长度只有1，可能长度是500，那怎么办呢？那就只能先把最大空间分配好了， 避免放不下的问题发生，这样实际上对于真实数据较短的varchar确实会造成空间的浪费。

举例：我向数据类型为：varchar（1000）的列插入了1024行数据，但是每个只存一个字符，那么这1024行真实数据量其实只有1K， 但是我却需要约1M的内存去适应他。所以最好的策略是只分配真正需要的空间。

# 深度分页问题

# join带来性能问题与反范式设计

← 数据库与缓存一致性 →