MySQL问题记录

1. mysql里面为什么用B+树？

mysql访问数据要通过页，一个页就是B+树上的一个节点，访问一个节点就相当于一次I/O，所以访问节点越少，I/O次数越少，性能也就越好。而B+树的特点就是够爱够胖，一般情况下，B+树都不会超过4层，所以如果我们通过主键去查找某条记录最多只需要4个节点，所以，使用B+树可以有效的减少访问节点，提升性能。

2. 那能不能用哈希呢？

不能
使用k-v键值对存储数据，把索引值做hash运算，并存放到hash表中，可能会存在hash冲突，拉链法（解决冲突的方法之一）。
有点：key无序，插入数据时不需要维护顺序，效率较高。
缺点：

1. 因为不是有序的，所以使用哈希做索引，无法使用区间查找。
2. hash只适用于精确查找
3. 组合hash索引无法使用部分索引，就是不能引用最左前缀原则。
4. 如果有大量hash冲突，性能较低
   

3. 讲讲数据库索引？

索引的出现其实就是为了提高数据的查询效率，就像书的目录一样。
mysql在查询方面主要就是两种方式：

1. 全表扫描
2. 根据索引检索

​

在任何数据库中，主键上都会自动创建索引。
在mysql中，一个字段上如果有unique约束的话，也会自动创建索引。

索引类型有

1. 普通索引：最基本的索引，没有任何限制
2. 唯一索引：与普通索引类似，但索引列的值必须是唯一的，是允许为null的
3. 主键索引：一种特殊的唯一索引，一个表只能有一个主键索引，不允许为null
4. 组合索引：在多个字段上创建索引，只有在查询条件中使用了创建索引的第一个字段，索引才会被调用，即最左匹配原则。
5. 全文索引：主要用来查找文本中的关键字，类似与搜索引擎。
   

对索引的优化方法

1. 尽量避免在while子句中对字段进行空值判断，这会导致引擎放弃使用索引，进行全表扫描。
2. 字段值分布很稀少的字段，不适合建立索引
3. 较频繁的作为查询条件的应该建立索引
4. 更新非常频繁的字段不适合创建索引，因为索引有维护成本
5. 索引不是越多越好
6. 字符字段只建立前缀索引
7. 不使用外键和UNIQUE
8. 使用组合索引时，注意顺序和查询条件保持一致，同时删除不必要的单列索引

​

索引失效的情况

1. 模糊匹配当中以"%"开头时，索引会失效
2. OR有一边的条件字段没有索引时，索引失效
3. 使用组合索引时，没有使用左侧的列查找，索引失效（最左前缀匹配原则）
4. 在where当中，索引列参加了运算，索引失效
5. 在where当中索引列使用了函数，索引失效
   

InnoDB索引模型

在InnoDB存储引擎中索引的实现通常是B+树。
根据叶子节点的内容，索引类型可以分为聚簇索引和二级索引。

聚簇索引 VS. 二级索引

主键查询使用聚簇索引，使用记录主键值的大小进行排序，所有的用户记录都存储在叶子节点，也就是所谓的索引即数据，数据即索引。
​

二级索引会根据非主键索引列的大小进行排序，B+树的叶子节点存储的并不是用户的完整记录，而只是索引列+主键这两个列的值。
​

非主键索引查询会使用二级索引，在二级索引所在的B+树中，查询到待查询记录的主键，然后根据这些查询到的主键，执行主键查询（回表）
​

也就是说，基于非主键索引的查询需要多扫描一颗索引树。因此，我们应该尽量使用主键查询。

回表的代价

因为通过二级索引查出来的记录，是通过查询的索引列排序的，所以查询出的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，这种连着的记录是可以很快的读出来的，这种读取方式称为顺序I/O，而这些记录的主键字段可能并不相连，而回表是通过聚簇索引查找的，这种根据并不连续的主键值到聚簇索引中访问完整的用户的记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式称为随机I/O ,一般情况下，顺序I/O比随机I/O性能高很多，所以需要回表的记录越多，使用二级索引的性能就越低。
​

覆盖索引

在查询列表里只包含索引列，可以告别回表带来的性能消耗。在普通索引树中，就可以直接查到待查结果，而不需要回表。

索引的优点

使用索引可以大大加快数据的检索速度。但是并不一定使用索引就能够提高查询性能，因为如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

索引的缺点

空间消耗，一个索引对应的就是一颗b+树，每一天个节点都是一个16KB大小的页。占用空间比较大。
创建索引和维护索引需要耗费许多时间，当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低sql的执行效率。
​

什么是事务？

事务是数据库区别于文件系统的重要特性。事务是一个最小的不可再分的工作单元。事务可以是一条非常简单的sql语句组成，也可以由一组复杂的sql语句组成。在事务中，要么都做修改，要么都不做。这就是事务的主要目的。

mysql事务的特性

ACID

A 原子性

一个事务的所有操作，要么全部完成，要么都不完成，不能在中间环节结束。如果事务在中间环节发生错误，会被回滚到事务开始之前的状态。

C 一致性

在事务开始之前以及事务结束之后，数据库的完整性不能被破坏。

I 隔离性

允许多个并发事务同时对数据进行修改和读写的能力，它可以防止多个事务并发执行时由于交叉执行而导致的数据不一致

D 持久性

事务处理结束之后，对数据的修改时永久的，即使发生了系统故障，数据也不会丢失。
​

原子性，一致性和持久性是通过数据库的redo log和undo log来完成的。redo log称为重做日志，用来保证事务的原子性和持久性。undo log用来保证事务的一致性。而隔离性是通过锁实现的。
​

三种日志

redo log

redo log为物理日志，记录的是页的物理修改操作。
因为我们只是想让已经提交了的事务对数据库中数据所作的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。所以其实没有必要在每次事务提交时就把该事务在内存中修改的全部页面刷新到磁盘（因为InnoDB是以页为单位来进行磁盘I/O的，如果只是修改了某个页面的一个字节，也会刷新完整的页），只需要把修改了哪些东西记录一下，这种记录就是redo log。

1. redo log占用的空间非常小（存储表空间ID，页号，偏移量，修改数据长度以及需要修改的值）
2. redo log是顺序写入磁盘的

​

redo log的刷盘时机

1. log buffer空间不足时（如果当前写入log buffer的redo日志量已经占满了log buffer总容量的大约一半左右，就会把日志刷新到磁盘上）
2. 后台线程不停的刷刷刷（大约每秒都会刷新一次log buffer到磁盘）
3. 正常关闭服务器

​

bin log

bin log是顺序写的，通常是逻辑修改。
用来进行Point-In-Time的恢复及主从复制环境的建立，保证数据的一致性，bin log的写入时机也非常简单，事务执行过程中，先把日志写到bin log cache，事务提交的时候，再把bin log cache写到bin log文件中。
​

bin log VS. redo log

1. redo log是在InnoDB存储引擎层产生的，而bin log是在mysql数据库上层产生的，bin log日志不仅仅针对InnoDB存储引擎，任何存储引擎都会产生bin log。
2. 两种日志的记录内容形式不同。bin log是一种逻辑日志，记录的是sql语句；而redo log是物理格式日志，记录是对于每个页的修改。
3. 写入磁盘的时机不同，bin log是在事务提交完成后进行一次写入，而redo log在事务进行中不断写入。

​

undo log

回滚记录到某个特定的版本，通常是逻辑日志。
要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，在mysql中，回滚机制就是通过undo log实现的。回滚日志会先于数据持久化到磁盘上。这样就保证了即使遇到数据库突然宕机等情况，当用户再次启动数据库的时候，数据库还能够通过查询回滚日志来回滚将之前未完成的事务。MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改。

既然你说了MVCC,那简单讲讲MVCC？

MVCC（多版本并发控制）指的就是在使用READ COMMITTED、REPEATABLE READ这两种隔离级别的事务在执行普通的select操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
READ COMMITTED、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTED在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后查询操作都重复使用这个ReadView。
​

既然用了MVCC版本查看，为什么还会出现幻读？

MVCC生成ReadView的那个时刻就好像做了一个快照，查询语句只能读到在生成ReadView之前已经提交的事务所做的更改，在生成ReadView之前未提交的事务或者之后才开启的事务所作的更改是看不到的。而写操作肯定是针对最新版本的记录，读历史版本的记录和改动最新版本的记录并不冲突，也就是说，采用MVCC时，读-写操作不冲突。
在READ COMMITTED隔离级别下，一个事务在执行过程中每次执行select操作都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所作的更改，也就避免了脏读。
在REPEATABLE READ隔离级别下， 只会在事务开启后的第一次查询生成 ReadView ，并使用至事务提交。所以在生成 ReadView 之后其它事务所做的更新、插入记录版本对当前事务并不可见，实现了可重复读和防止快照读下的 “幻读”
​

另外：

串行化，间隙锁、Next-Key锁也能解决幻读

讲讲数据库的隔离级别？分别怎么解决？可能出现的问题？

并发事务带来的问题

多个事务并发运行时，经常会操作相同的数据来完成各自的任务，所以可能会导致一些问题。

脏写

一个事务修改了另一个未提交事务修改过的数据，就发生了脏写。

脏读

一个事务读到了另一个未提交事务修改过的数据，就发生了脏读。

不可重复读

一个事务能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就发生了不可重复读。

幻读

一个事务根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，就发生了幻读。
​

不可重复读和幻读的区别：
不可重复读的重点是修改，幻读的重点是新增或删除。
​

事务的4种隔离级别

未提交读（Read Uncommitted）

在事务A读取数据时，事务B读取和修改数据加了共享锁。
会导致脏读、不可重复读、幻读。

已提交读（Read Committed）

在事务A读取数据时增加了共享锁，一旦读取，立即释放锁，事务B读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务A在读取数据时，事务B只能读取数据，不能修改。当事务A读取数据结束后，事务B才能修改。
可以避免脏读，但依然存在不可重复读，幻读。

可重复读（Repeatable Read）

在事务A读取数据时增加了共享锁，事务结束，才释放锁，数据B读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务A在没有结束事务前，事务B只能读取数据，不能修改。当事务A结束后，事务B才能修改。
可以避免脏读，不可重复读，但依然存在幻读。

可串行化（Serializable）

在事务 A 读取数据时增加了共享锁，事务结束，才释放锁， 事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏 读、不可重复读、幻读等问题，
​

但隔离级别越来越高的同时，并发性会越来越低。


InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ，使用可重复读可以解决脏读，不可重复读，而幻读有可能发生，但是mysql在可重复读隔离级别下，可以避免幻读。（通过加Next-Key Lock）。