【你真的懂MYSQL这些问题吗？~初识MYSQL】

1.数据库的ACID？

• A（atomicity）,原子性。原子性指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功,整个事务的执行才算成功。事务中任何一个SQL语句执行失败,那么已经执行成功的SQL语句也必须撤销,数据库状态应该退回到执行事务前的状态。
• C（consistency）,一致性。一致性指事务将数据库从一种状态转变为另一种一致的状态。在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。
• I（isolation）,隔离性。事务的隔离性要求每个读写事务的对象与其他事务的操作对象能相互分离,即该事务提交前对其他事务都不可见,这通常使用锁来实现。
• D（durability） ,持久性。事务一旦提交,其结果就是永久性的,即使发生宕机等故障,数据库也能将数据恢复。持久性保证的是事务系统的高可靠性,而不是高可用性。

原子性：undo log（MVCC）

​ 一致性: 最核心和最本质的要求 是通过原子性、隔离性、持久性共同确定

​ 隔离性:锁，mvcc（多版本并发控制）

​ 持久性：redo log

2.事务可以分为哪几种类型？

• 扁平事务：是事务类型中最简单的一种,而在实际生产环境中,这可能是使用最为频繁的事务。在扁平事务中,所有操作都处于同一层次,其由BEGIN WORK开始,由COMMIT WORK或ROLLBACK WORK结束。处于之间的操作是原子的,要么都执行,要么都回滚。
• 带有保存点的扁平事务：除了支持扁平事务支持的操作外,允许在事务执行过程中回滚到同一事务中较早的一个状态,这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效,放弃整个事务不合乎要求,开销也太大。保存点（savepoint）用来通知系统应该记住事务当前的状态,以便以后发生错误时,事务能回到该状态。
• 链事务：可视为保存点模式的一个变种。链事务的思想是：在提交一个事务时,释放不需要的数据对象,将必要的处理上下文隐式地传给下一个要开始的事务。注意,提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果,就好像在一个事务中进行的。
• 嵌套事务：是一个层次结构框架。有一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction）,其控制每一个局部的变换。
• 分布式事务：通常是一个在分布式环境下运行的扁平事务,因此需要根据数据所在位置访问网络中的不同节点。对于分布式事务,同样需要满足ACID特性,要么都发生,要么都失效。
  注意:
  对于MySQL的InnoDB存储引擎来说,它支持扁平事务、带有保存点的扁平事务、链事务、分布式事务。对于嵌套事务,MySQL数据库并不是原生的,因此对于有并行事务需求的用户来说MySQL就无能为力了,但是用户可以通过带有保存点的事务来模拟串行的嵌套事务。

3.什么是MYSQL的索引？MySQL索引有哪些？它们有什么好处和坏处？

(1).索引就像指向表行的指针,是一种允许查询操作快速确定哪些行符合WHERE子句中的条件,并检索到这些行的其他列值的数据结构；
(2).索引主要有普通索引、唯一索引、主键索引、外键索引、全文索引、复合索引几种；
(3).在大数据量的查询中,合理使用索引的优点非常明显,不仅能大幅提高匹配where条件的检索效率,还能用于排序和分组操作的加速。 但是索引如果使用不当也有比较大的坏处：比如索引必定会增加存储资源的消耗；同时也增大了插入、更新和删除操作的维护成本,因为每个增删改操作后相应列的索引都必须被更新。

4.只要创建了索引,就一定会走索引吗？

不一定。 比如,在使用组合索引的时候,如果没有遵从“最左前缀”的原则进行搜索,则索引是不起作用的。 举例,假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放,索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀,那么MySQL不能使用局部索引,如（age）或者（name,age）组合则不能使用该索引查询。

5.什么是聚簇索引？什么是非聚簇索引？它们有什么区别呢？(无论是聚簇索引还是非聚簇索引，底层采用的数据结构都是B+Tree)

• 聚簇索引和非聚簇索引两者主要区别是数据和索引是否分离；
• 聚簇索引是将数据与索引存储到一起,找到索引也就找到了数据；而非聚簇索引是将数据和索引存储分离开,索引树的叶子节点存储了数据行的地址，地址中存储了真实的地址。
• 在InnoDB中,一个表有且仅有一个聚簇索引（因为原始数据只留一份,而数据和聚簇索引在一起）,并且该索引是建立在主键上的,即使没有指定主键,也会特殊处理生成一个聚簇索引；其他索引都是辅助索引,使用辅助索引访问索引外的其他字段时都需要进行二次查找。
• 在MyISAM中,所有索引都是非聚簇索引,叶子节点存储着数据的地址,对于主键索引和普通索引在存储上没有区别。

6.InnoDB存储引擎中的聚簇索引和非聚簇索引？

• 在InnoDB存储引擎中,可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。
• 无论是何种索引,每个页的大小都为16KB,且不能更改。
• 聚簇索引是根据主键创建的一棵B+树,聚簇索引的叶子节点存放了表中的所有记录。
• 辅助索引是根据索引键创建的一棵B+树,与聚簇索引不同的是,其叶子节点仅存放索引键值,以及该索引键值指向的主键。
• 也就是说,如果通过辅助索引来查找数据,那么当找到辅助索引的叶子节点后,很有可能还需要根据主键值查找聚簇索引来得到数据,这种查找方式又被称为书签查找。
• 因为辅助索引不包含行记录的所有数据,这就意味着每页可以存放更多的键值,因此其高度一般都要小于聚簇索引。

7.可以作为数据库索引的数据结构有哪些？数据库的索引底层用到了什么数据结构？为什么没有使用其中的一些数据结构？

(1).索引可选的底层数据结构包括：

• 二叉树
• 红黑树
• hash
• B-tree

(2).mysql索引的底层用的并不是二叉树和红黑树。因为二叉树和红黑树在某些场景下都会暴露出一些缺陷。

• 不使用二叉树的原因:首先,二叉树在某些场景下会退化成链表,而链表的查找需要从头部开始遍历,而这就失去了加索引的意义。
• 不使用红黑树的原因是：红黑树作为底层数据结构在面对某些表数据数百万数千万的场景时,会导致索引树的层数很高。索引从根节点开始查找,而如果我们需要查找的数据在底层的叶子节点上,那么树的高度是多少,就要进行多少次查找,数据存在磁盘上,访问需要进行磁盘IO,这会导致效率过低；
• 不使用Hash表的原因:哈希表的查询效率的确最高,时间复杂度O(1),但是它要求将所有数据载入内存,而数据库存储的数据量级可能会非常大,全部载入内存基本上是不可能实现的； B+树可以分段加载需要的节点数据,可以在内存资源有限的前提下,极大提高查询效率;

(3).为什么是选择B+树作为索引的数据结构？

• B+树由B树和索引顺序访问方法演化而来,它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树；
• 在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点,各叶子节点通过指针进行链接。
• B+树索引在数据库中的一个特点就是高扇出性,例如在InnoDB存储引擎中,每个页的大小为16KB。在数据库中,B+树的高度一般都在2～4层,这意味着查找某一键值最多只需要2到4次IO操作,这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作,2～4次的IO操作意味着查询时间只需0.02～0.04秒。

8.再来详细的说说索引怎么实现的B+树,为什么选这个数据结构？

• 索引本质上就是通过预排序+树型结构来加快检索的效率,而MySQL中使用InnoDB和MyISAM引擎时都使用了B+树实现索引。
• 它是一棵平衡多路查找树,是在二叉查找树基础上的改进数据结构。在二叉查找树上查找一个数据时,最坏情况的查找次数为树的深度,当数据量很大时,查询次数可能还是很大,造成大量的磁盘IO,从而影响查询效率； 为了减少磁盘IO的次数,必须降低树的深度,因此在二叉查找树基础上将树改成了多叉加上一些限制条件,就形成了B树；
• B+树中所有叶子节点值的总集就是全部关键字集合；B+树为所有叶子节点增加了链接,从而实现了快速的范围查找； 在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上,由各叶子节点指针进行连接。
• 在数据库中,B+树的高度一般都在2～4层,这也就是说查找某一键值的行记录时最多只需要2到4次 IO。这很不错,因为当前一般的机械磁盘每秒至少可以做100次IO,2～4次的IO意味着查询时间只需0.02～0.04秒。

在数据库中,B+树索引还可以分为聚集索引(聚簇索引)和辅助索引(非聚簇索引),但不管是聚集索引还是辅助索引,其内部都是B+树的,即高度平衡的,叶子节点存放着所有的数据。聚集索引与辅助索引不同的是,叶子节点存放的是否是一整行的信息。

9.B树与B+有何异同？有什么联系？

• 它们都是平衡多路查找树,是在二叉查找树基础上的改进数据结构。
• 在二叉查找树上查找一个数据时,最坏情况的查找次数为树的深度,当数据量很大时,查询次数可能还是很大,造成大量的磁盘IO,从而影响查询效率；
• 为了减少磁盘IO的次数,必须降低树的深度,因此在二叉查找树基础上将树改成了多叉加上一些限制条件,就形成了B树；
• B+树是B树的变种,区别主要是：对于k阶的B树,每个中间节点只存k-1个值k个指针,而B+树存k个值和k个指针；
• B树中所有节点中值的总集是全部关键字集合,而B+树中所有叶子节点值的总集就是全部关键字集合；
• B+树为所有叶子节点增加了链接,从而实现了快速的范围查找；
• B+树由B树和索引顺序访问方法演化而来,它是为磁盘或其他直接存取辅助设备设计的一种平衡查找树,在B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点,各叶子节点通过指针进行链接。
• B+树索引在数据库中的一个特点就是高扇出性,例如在InnoDB存储引擎中,每个页的大小为16KB。在数据库中,B+树的高度一般都在2～4层,这意味着查找某一键值最多只需要2到4次IO操作,这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作,2～4次的IO操作意味着查询时间只需0.02～0.04秒。

10.事务并发会带来哪些问题？

当很多事务并发地去操作数据库的表或者行的时候，如果没有我们刚才讲的事务的Isolation 隔离性的时候，会带来哪些问题呢？

• 脏读
  我们有两个事务，一个是 Transaction A，一个是 Transaction B，在第一个事务里面，它首先通过一个 where id=1 的条件查询一条数据，返回 name=panda，age=16 的这条数据。然后第二个事务呢，它同样地是去操作 id=1 的这行数据，它通过一个 update 的语句，把这行 id=1 的数据的 age 改成了 18，但是大家需要注意，它没有提交。
  这个时候，在第一个事务里面，它再次去执行相同的查询操作，发现数据发生了变化，获取到的数据 age 变成了 18。那么，这种在一个事务里面，由于其他的时候修改了数据并且没有提交，而导致了前后两次读取数据不一致的情况，这种事务并发的问题，我们把它定义成脏读。
• 不可重复读
  同样是两个事务，第一个事务通过 id=1 查询到了一条数据。然后在第二个事务里面执行了一个 update 操作，这里大家注意一下，执行了 update 以后它通过一个 commit提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况，就像这里，age 到底是等于 16 还是 18，那么这种事务并发带来的问题，我们把它叫做不可重复读。
• 幻读
  在第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条。在第二个事务里面，它插入了一行数据，并且提交了。重点：插入了一行数据。在第一个事务里面再去查询的时候，它发现多了一行数据。一个事务前后两次读取数据数据不一致，是由于其他事务插入数据造成的，这种情况我们把它叫做幻读。
  总结：
• 不可重复读是修改或者删除，幻读是插入。
• 无论是脏读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。

11.请你说说MySQL的事务隔离级别？

SQL 标准定义了四种隔离级别,这四种隔离级别分别是：

• 读未提交（READ UNCOMMITTED）
• 读提交 （READ COMMITTED）
• 可重复读 （REPEATABLE READ)
• 串行化 （SERIALIZABLE）

事务隔离是为了解决脏读、不可重复读、幻读问题,在 MySQL InnoDB 里面，不需要使用串行化的隔离级别去解决所有问题。那我们来看一下 MySQL InnoDB 里面对数据库事务隔离级别的支持程度是什么样的。如下图所示:

隔离级别越高，事务的并发度就越低。唯一的区别就在于，InnoDB 在 RR 的级别就解决了幻读的问题。这个也是InnoDB 默认使用 RR 作为事务隔离级别的原因，既保证了数据的一致性，又支持较高的并发度。

12.请你说说InnoDB的MVCC？什么是MVCC？MVCC的核心思想?

• Multi-Version Concurrency Control,即多版本并发控制,逻辑是维持一个数据的多个版本,使得读写操作没有冲突。MVCC主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突,做到即使有读写冲突时,也能做到不加锁,非阻塞并发读。
  它是一种用来解决读-写冲突的无锁并发控制机制。在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作,提高了数据库并发读写的性能,还可以解决脏读、幻读、不可重复读等事务隔离问题,但不能解决更新丢失问题。

• InnoDB默认的隔离级别是RR（REPEATABLE READ）,RR解决脏读、不可重复读、幻读等问题,使用的是MVCC。MVCC全称Multi-Version Concurrency Control,即多版本的并发控制协议。它最大的优点是读不加锁,因此读写不冲突,并发性能好。

• InnoDB实现MVCC,多个版本的数据可以共存,主要基于以下技术及数据结构：

  1. 隐藏列：InnoDB中每行数据都有隐藏列,隐藏列中包含了本行数据的事务id、指向undo log的指针等。
  2. 基于undo log的版本链：每行数据的隐藏列中包含了指向undo log的指针,而每条undo log也会指向更早版本的undo log,从而形成一条版本链。
  3. ReadView：通过隐藏列和版本链,MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本,则需要根据ReadView来确定。所谓ReadView,是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照,之后再进行读操作时,会将读取到的数据中的事务id与trx_sys快照比较,从而判断数据对该ReadView是否可见,即对事务A是否可见。

• MVCC 的核心思想是：我可以查到在我这个事务开始之前已经存在的数据，即使它在后面被修改或者删除了。在我这个事务之后新增的数据，我是查不到的。

13.请你说说数据库引擎innodb和myisam的区别？如何选择合适的数据库引擎？

区别	Innodb	MyISAM
事务	支持	不支持
外键	支持	不支持
索引	即支持聚簇索引又支持非聚簇索引	只支持非聚簇索引
行锁	支持	不支持
表锁	支持	支持
存储文件	frm，ibd	frm,myi,myd
具体行数	每次必须要全表扫描统计行数	通过变量保存行数（查询不能带条件）

如何选择？

​ 1、是否需要支持事务，如果需要选择innodb，如果不需要选择myisam

​ 2、如果表的大部分请求都是读请求，可以考虑myisam，如果既有读也有写，使用innodb

​ 现在mysql的默认存储引擎已经变成了Innodb,推荐使用innodb

14.什么是乐观锁？什么是悲观锁？它们的应用场景？

乐观锁和悲观锁的基本概念:

• 乐观锁：乐观锁总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量**,像数据库提供的类似于write_condition机制,其实都是提供的乐观锁。
• 悲观锁：悲观锁总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程）。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。

两种锁的使用场景:

• 乐观锁：
  GIT,SVN,CVS等代码版本控制管理器,就是一个乐观锁使用很好的场景,例如：A、B程序员,同时从SVN服务器上下载了xxx.java文件,当A完成提交后,此时B再提交,那么会报版本冲突,此时需要B进行版本处理合并后,再提交到服务器。这其实就是乐观锁的实现全过程。如果此时使用的是悲观锁,那么意味者所有程序员都必须一个一个等待操作提交完,才能访问文件,这是难以接受的。
• 悲观锁：
  悲观锁的好处在于可以减少并发,但是当并发量非常大的时候,由于锁消耗资源、锁定时间过长等原因,很容易导致系统性能下降,资源消耗严重。因此一般我们可以在并发量不是很大,并且出现并发情况导致的异常用户和系统都很难以接受的情况下,会选择悲观锁进行。

​ 乐观锁并不是数据库自带的，如果需要使用乐观锁，那么需要自己去实现，一般情况下，我们会在表中新增一个version字段，每次更新数据version+1,在进行提交之前会判断version是否一致。

15.Mysql中锁的种类？根据锁的粒度怎么对它们进行区分?

​ mysql中的绝大部分锁都是悲观锁，按照粒度可以分为行锁和表锁：

​ 行锁：

​ 共享锁：当读取一行记录的时候，为了防止别人修改，则需要添加S锁

​ 排它锁：当修改一行记录的时候，为了防止别人同时进行修改，则需要添加X锁

	X	S
X	不兼容	不兼容
S	不兼容	兼容

​ 记录锁：添加在行索引上的锁

​ 间隙锁：锁定范围是索引记录之间的间隙，针对可重复读以上隔离级别

​ 临键锁：记录锁+间隙锁

​ 表锁：

​ 意向锁：在获取某行的锁之前，必须要获取表的锁，分为意向共享锁，意向排它锁

​ 自增锁：对自增字段所采用的特殊表级锁

​ 锁模式的含义：

​ IX：意向排它锁

​ X：锁定记录本身和记录之前的间隙

​ S：锁定记录本身和记录之前的间隙

​ X,REC_NOT_GAP：只锁定记录本身

​ S，REC_NOT_GAP：只锁定记录本身

​ X，GAP：间隙锁，不锁定记录本身

​ S，GAP：间隙锁，不锁定记录本身

​ X，GAP,INSERT_INTENTION：插入意向锁

16.MySQL主从同步是如何实现的？

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案,一般用来建立大型的应用。
总体来说,replication的工作原理分为以下3个步骤：

1. 主服务器（master）把数据更改记录到二进制日志（binlog）中。
2. 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3. 从服务器重做中继日志中的日志,把更改应用到自己的数据库上,以达到数据的最终一致性。 复制的工作原理并不复杂,其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是,复制不是完全实时地进行同步,而是异步实时。这中间存在主从服务器之间的执行延时,如果主服务器的压力很大,则可能导致主从服务器延时较大。
   

17.数据库中的这几个日志文件你知道吗？redo log？bin log?undo log?

redo log
InnoDB 引入了一个日志文件，叫做 redo log（重做日志），我们把所有对内存数据的修改操作写
入日志文件，如果服务器出问题了，我们就从这个日志文件里面读取数据，恢复数据——用它来实现事
务的持久性。
redo log 有什么特点？
1.记录修改后的值，属于物理日志
2.redo log 的大小是固定的，前面的内容会被覆盖，所以不能用于数据回滚/数据恢复。
3.redo log 是 InnoDB 存储引擎实现的，并不是所有存储引擎都有。

bin log
MySQL Server 层也有一个日志文件，叫做 binlog，它可以被所有的存储引擎使用。
binlog 以事件的形式记录了所有的 DDL 和 DML 语句（因为它记录的是操作而不是数据值，属于逻
辑日志），可以用来做主从复制和数据恢复。
事务执行过程中会遇到一些突然状况，导致事务无法正常结束：

undo log
服务器错误、操作系统错误、突然断点等不可抗因素
事务执行过程中，通过rollback指令回滚
这种执行一半的事务，可能已经修改了很多数据，根据事务的原子性要求，没有完成的事务必须回滚到执行前的状态，看起来像什么事都没发生过的样子，因此回滚比较重要，回滚的实现方式大致如下：
insert: 插入一条记录时，将这条记录的主键记录下来，回滚时根据这个主键删除即可。
delete: 删除一条记录时，将这条记录的内容记录下来，回滚时重新插入到表中即可。
update：修改一条记录时，将被更新的列的旧值记录下来，回滚时将这些值更新回去即可。
MySQL将这种用于回滚的记录称为 undo log，查询操作并不会对记录造成影响，因此没有对应的undo log。

18.什么是联合索引的最左匹配原则？

前面我们说的都是针对单列创建的索引，但有的时候我们的多条件查询的时候，也会建立联合索引，举例：查询成绩的时候必须同时输入身份证和考号。单列索引可以看成是特殊的联合索引。比如我们在 user 表上面，给 name 和 phone 建立了一个联合索引。联合索引在 B+Tree 中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的（name 在左边，phone 在右边）。
name 是有序的，phone 是无序的。当 name 相等的时候，phone 才是有序的。这个时候我们使用 where name= ‘jim’ and phone = '136xx '去查询数据的时候，B+Tree 会优先比较 name 来确定下一步应该搜索的方向，往左还是往右。如果 name相同的时候再比较 phone。但是如果查询条件没有 name，就不知道第一步应该查哪个节点，因为建立搜索树的时候 name 是第一个比较因子，所以用不到索引。

CREATE INDEX idx_name on user_innodb(name);
CREATE INDEX idx_name_phone on user_innodb(name,phone);

当我们创建一个联合索引的时候，按照最左匹配原则，用左边的字段 name 去查询的时候，也能用到索引，所以第一个索引完全没必要。相当于建立了两个联合索引(name),(name,phone)。如果我们创建三个字段的索引 index(a,b,c)，相当于创建三个索引：
index(a)
index(a,b)
index(a,b,c)
用 where b=? 和 where b=? and c=? 是不能使用到索引的。
这里就是 MySQL 里面联合索引的最左匹配原则。

                      后续内容持续更新中，创做不易，您的点赞和关注就是我前进的动力 ^~^ 一起加油！！！