MySQL数据库管理系统——理论篇

一、MySQL数据库管理系统的逻辑架构

1.执行一条SQL语句，MySQL内部发生了什么？

答：
主要了解MySQL 内部的逻辑架构，知道每个步骤中各个模块的功能。

第一步：建立连接

首先，通过连接器建立客户端和服务端之间的TCP连接。其次，对客户端传来的账号密码进行身份认证和权限获取。

	还要对TCP长连接进行管理，以及为每次客户端连接从线程池中分配一个线程。

第二步：查询缓存（可选）

只针对SQL查询语句，查询语句如果命中查询缓存则直接返回结果，否则继续往下执行。

	key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

第三步：解析SQL语句

通过解析器对 SQL 语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；

	词法分析：MySQL 会根据你输入的字符串识别出关键字，知道每个字符串代表什么。
	语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

第四步：执行SQL语句

预处理阶段：通过预处理器，检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
优化阶段：通过优化器，确定SQL执行路径，如选择最佳索引方式。
执行阶段：通过执行器，根据执行计划真正执行 SQL 查询语句，从存储引擎读取记录，返回给客户端。

	执行器和存储引擎的具体交互过程（省略）

总结：

MySQL 的架构共分为两层：Server 层和存储引擎层。（以下可以不记）

1.Server 层负责建立连接、分析和执行 SQL：
MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。
另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

2.存储引擎层负责数据的存储和提取：
支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

二、事务

1.什么是事务？

答：
事务就是一个完整的业务逻辑，由多个SQL语句组成一个不可再分的逻辑操作单元，是一个最小的工作单元。
如转账业务，对数据库的所有操作是不可分割的，要么全部执行成功 ，要么全部失败。

2.事务有哪些特性？

答：

	原子性：针对某个事物的操作，要么全部成功，要么全部失败，不可再分。可以通过回滚日志保证。
	隔离性：针对多个事务并发操作同一数据，需要保证各个事务互不干扰。具有四个隔离级别，通过MCVV和锁机制来保证。
	持久性：事务一旦提交，对数据的改变是永久的。通过重做日志来保证。
	一致性：针对多个事务并发操作同一数据，所有事务完成后，数据是合法的。事务的一致性通过原子性、隔离性、持久性一块来保证。


重做日志redo log：
当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。
这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

3.并发事务会带来什么问题？

答：
MySQL 服务端是允许多个客户端连接的， MySQL 会同时处理多个事务。并发事务访问相同记录会有以下情况

1）读—读情况：并发事务，读取相同数据。

		读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

2）写—写情况：并发事务，对相同数据做出改动

在这种情况下会发生 脏写 的问题，任何一种隔离级别都不允许这种问题的发生。
所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。

	比如，事务 A 要对这条记录做改动，就需要生成一个 锁结构与之关联。如果此时事务B也想对同一条记录做出改动，也必须生成一个锁。
	但是这条记录已经被事务A加锁成功了，所以事务B只能等待。因此事务A和事务B是排队执行的，是串行的。	

获取锁成功，或者加锁成功：意思就是在内存中生成了对应的 锁结构 ，而且锁结构的 is_waiting 属性为 false ，也就是事务可以继续执行操作。
获取锁失败，或者加锁失败，或者没有获取到锁：意思就是在内存中生成了对应的 锁结构 ，不过锁结构的 is_waiting 属性为 true ，也就是事务
需要等待，不可以继续执行操作。

3）读----写情况：一个事务进行读取操作，另一个进行改动操作。

	对于并发事务A和B：
	脏读：对于两个并发事务A和B，事务A读取到了事务B还未提交的数据。
	不可重复读：对于两个并发事务A和B，事务A前后读取的数据不一致（因为事务B在此期间改变了数据并提交，事务A前后读取的数据都是对的）
	幻读：对于两个并发事务A和B，事务A前后查询的记录数量不一致（因为事务B在此期间改插入了新的记录）

	幻读是比不可重复读更难处理的一个级别错误，因此需要更高的隔离级别来处理。

不可重复读和幻读的区别：
不可重复读是因为在读的时候没有禁止写操作，两次读取同一条数据的时候结果不一致，主要针对的是update或delete。
幻读是因为在读取多条数据时，虽然此时读操作禁止了写操作，所以update和delete都是不行的，但是由于未知行的不存在，无法加锁，所以insert语句还是可以在读事务中间执行，所以可能出现幻读。
不可重复读和幻读的危害：

不可重复读和幻读的区别：

幻读侧重行数量发生了变化。不可重复读侧重某一行数据发生了变化。
不可重复读，广义上讲，第一次读和第二次读数据不一样，这包含两种情况：
(1) 同一行数据发生了变化；
(2) 行数量发生了变化；
为了区分来这两种情况，就把后者单独叫“幻读”。而不可重复读通常指的就是第一种情况。

4.如何解决并发事务带来的问题？（事务的隔离级别有哪些？）

答：
SQL 标准提出了四种隔离级别来规避这些现象。


要解决脏读现象，就要升级到「读已提交」以上的隔离级别；
要解决不可重复读现象，就要升级到「可重复读」的隔离级别。

要解决幻读现象不建议将隔离级别升级到「串行化」，因为这样会导致数据库在并发事务时性能很差。

InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。因此InnoDB 引擎可重复读的隔离级别解决了幻读问题。

next-key lock 锁（行锁和间隙锁的组合）会产生死锁现象。

死锁：就是两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。
避免死锁有两种方法：
		设置事务等待锁的超时时间：当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。
		开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。

总结：

读未提交read uncommitted：该隔离级别下，事务A可以读取到事务B未提交的数据。因此存在脏读、不可重复读、幻读问题。
读已提交read committed：该隔离级别下，事务A只能读取到事务B提交之后的数据。因此不存在脏读问题，但存在不可重复读和幻读问题。
											比如事务B修改数据或者删除、插入新的行数据后，事务A可以读取到修改后的数据，增加或者减少的行数量。
可重复读repeatable read：该隔离级别下，事务A开启之后，即便事务B修改update、删除delete数据并提交，事务A读取的仍然是原来数据，没有发生改变。
											但是，如果并发事务B插入新的数据，事务A会读取到新的行记录。因此只解决了脏读和不可重复读问题，幻读仍在存在。
穿行化serializable：该隔离级别下，事务之间不能并发。事务A读操作期间，事务B不能进行写操作。因此不存在脏读、不可重复读、幻读问题。
								和“写---写”一样全部加锁处理。

“写---写”是事务A和事务B对同一记录并发做出改动，称为脏写。因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。
不同的隔离级别有不同的现象，并有不同的锁和并发机制，隔离级别越高，数据库的并发性能就越差。

5.如何实现四种隔离级别？

答：

总结：
读未提交：这种隔离级别的实现，对于并发事务A和B，读读、写写、读写三种情况一视同仁，什么都不做即可。

串行化：这种隔离级别的实现，对于并发事务A和B，读读什么都不做，读写和写写都要加锁，都是排队执行。

			读写加锁的意思是说，事务A在读的时候，如果有并发事务B想修改数据，此时我们事务A在读的时候，就给数据加锁，
			不给事务B修改数据的机会。而对于读读情况，没有并发事务要修改数据，因此不必加锁。

读已提交：这种隔离级别的实现，对于并发事务A和B，读读什么都不做，读写中的读采用MVCC多版本并发控制，
要读的那个事务每次读取数据时都生成readview。写写仍然采用加锁。

可重复读：这种隔离级别的实现，对于并发事务A和B，读读什么都不做，读写中的读采用MVCC多版本并发控制，
要读的那个事务每次事务开启时生成readview，后面一直用这个readview。
写写仍然采用加锁。

加锁：
比如，事务 A 要对这条记录做改动，就需要生成一个 锁结构与之关联。如果此时事务B也想对同一条记录做出改动，也必须生成一个锁。
	但是这条记录已经被事务A加锁成功了，所以事务B只能等待。因此事务A和事务B是排队执行的，是串行的。	

重要单独讨论：
以上的读指的都是快照读，读取的是版本数据。还有一种读是当前读，即读取最新的数据。当前读包括：
读取最新数据命令select … for update
修改数据update
删除数据delete
插入数据insert
这些命令在执行时，都会读取最新版本的数据。

如，当事务A执行上述操作进行查看数据行数量时，如果此时事务B插入数据（只会是插入数据情况），会发现事务A前后读取的记录数量发生了变化，这就是幻读。
因此这种情况和写写不完全一样，写写是两个事务修改同一个数据，两个事务必须是修改或删除已存在的数据。
而“当前读—插入”是一个事务在插入新数据，这是不存在的数据，一个可以认为在写数据，或者叫当前读，这个是已存在的数据。
这种情况和普通的读写又不一样，普通读写中的读是快照读，只是简单查询数据，不会要求读取当前最新数据，因此普通读写中的写可以修改、删除、插入数据。

因此我们需要区别“快照读和当前读”，区别“读—读、读—写、写—写、写–插入（当前读—插入）”。

区别“当前读—插入”加组合锁，和“写—写”加锁：
写写加锁是两个并发事务A和B操作同一个数据，数据是已经存在的。比如事务A要操作数据时，会对其加锁，那么事务B就不能操作了。
而“当前读—插入”是事务A读的时候没办法对不存在的数据进行加锁，因此只能加行锁和间隙锁的组合，来锁住某个范围和记录本身，这样事务B就不能在其中插入数据了。因此，这两种锁有区别。
个人认为，RR隔离级别下，加入next-key lock锁解决幻读问题，和“读–写”加锁、“写—写”加锁方案是相同的。

6.详细谈谈多版本并发控制MVCC？

多版本并发控制MVCC实现两种事务隔离级别，读提交和可重复读。

1）MVCC解决的问题

答：
多版本并发控制MVCC是实现读提交和可重复读两种隔离级别的一种方案，在解决读—写冲突时，可以对读不用加锁，从而提高并发性。
具体是通过read view + undo log + **聚簇索引两个隐藏列（trx_id和roll_pointer）**来实现的。

什么是 “事务id 、undo日志、read view、trx_id、roll_pointer”？

答：

数据库中有很多数据，每个事务都有自己的ID，每个事务操作一个数据后，数据库会记录当前数据是哪个事务操作的，用trx_id记录。
当前事务对数据修改后，会把旧版本的记录写入到undo日志中，每个版本通过roll_pointer指针连接起来，因此我们可以找到不同事务修改同一个数据的记录。

trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

read view就是当前事务对该数据的一个快照，里面包含这条数据各个版本的记录。不同时刻生成的readview是不同的。

详细解释一下readview？

Read View 有四个重要的字段：

m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；
creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

2）MVCC到底是如何实现读提交和可重复读两种隔离级别？

答：
MVCC多版本控制 = read view + undo log + 聚簇索引两个隐藏列（trx_id和roll_pointer）

并发事务A和B，事务A读，事务B写：
事务A对同一数据进行查询操作时，会对当前数据的所有记录生成一个read view。
当然生成read view有两种情况，一种是事务A启动时生成，后面就不改了。一种是事务A每次读的时候会再重新生成一个。
显然第一个情况，数据对于当前事务来说，一直未变，实现了可重复读隔离级别。
第二个情况，数据对于当前事务来说，是改变了，实现了读提交隔离级别。

读提交隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是启动事务后，在执行第一个查询语句后，会创建一个 Read View，然后后续的查询语句利用这个 Read View，通过 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以每次查询的数据都是一样的。

3）当前读产生的幻读问题如何解决？

答：
当前读，必须查到最新的数据。因此采用MVCC生成视图的方法肯定不行。

Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了 next-key 锁，防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。next-key 锁是记录锁和间隙锁的组合。记录锁，锁的是记录本身；间隙锁，锁的就是两个值之间的空隙。

可重复读隔离级别的幻读现象，是要建立在当前读的情况下。
而对于可重复读隔离级别的快照读的情况下，MVCC是解决了幻读问题。
读提交隔离级别也会有幻读现象，因为每次读取是就是当前查询的最新数据，而且读已提交隔离级别没有采用next-key 组合锁，因此还是存在幻读问题。

4）为什么要使用MVCC？

答：
本来可以通过串行化隔离级别，对“读–写”也加锁处理，像“写—写”一样处理。但是呢，我们本着提高并发性能的目的，使用MVCC来代替“读–写”加锁方案，虽然提高了并发性能，解决了脏读和不可重复读问题，但是没有解决幻读问题。幻读问题不属于“写—写”中的问题，他从本质上说属于“读—写”未加锁带来的问题，但是如果“写—写”未加锁也会有幻读问题，幻读仅专指“新插入的行”。因此“当前读–插入”中当前读是select … for update时，这就是“读—写”未加锁中的问题，如果“当前读–插入”中当前读是update、delete、insert就是“写—写”未加锁中的问题。但是“写—写”一定是会加锁。因此，我们讨论的幻读问题，一般指的是不采用“读—写”加锁，而采用MCVV带来的问题。即一个事务要读取最新的行数量，一个事务要插入新的记录。

为了解决这个问题，我们引入组合锁来解决。

注意，若如果我们直接采用“读–写”加锁方案，是不会出现幻读问题的。比如我们要读id>2的记录数量，我们会对这个区间所有数据进行加锁，相当于锁住了间隙，因此不会让事务B进行插入。而采用MVCC，读操作时并未给记录加锁，因此会出现幻读问题。对于当前读，加了组合锁后，就避免了幻读现象，同时提高了快照读情况下的并发性能。这就是MVCC的优点。

**“读—写”加锁方案和“写—写”加锁方案，要解决幻读问题，是不是也必须使用行锁和间隙锁的组合来锁住记录和间隙，防止插入？如果不是，如何解决呢。**应该也是同样方式解决，对行记录加锁的基本单位就是next-key lock。
（如果对行记录使用update加锁时，没有带上索引，或者使用的是普通字段来索引，会给全表加锁）

三、Buffer Pool缓冲池？

1.什么是Buffer Pool缓冲池？为什么要有Buffer Pool缓冲池？

答：
Buffer Pool缓冲池就是缓存，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。

2.Buffer Pool 有多大？缓存了什么？

答：
Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB 。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。

按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。
Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页，插入缓存、自适应哈希索引、锁信息等等。

3.如何管理Buffer Pool ？

1）如何管理空闲页？

答：
建立Free List （空闲页链表）

2）如何管理脏页？

答：
建立Flush List （脏页链表）

3）如何管理干净页和脏页？（如何提高缓存命中率？）

答：
建立LRU List（Least recently used），管理脏页+干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。

针对出现的预读失效和Buffer Pool 污染问题，InnoDB 对 LRU 做了一些优化：

预读失效是被提前加载进来的数据页，并没有被访问，却占据了LRU链表头部。

	将 LRU 链表 分为young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域

Buffer Pool 污染是当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了。

当[某页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为1秒）」**时，才会将页插入到 young 区域，否则还是插入到 old 区域。

4）脏页什么时候被刷入磁盘？

答：
脏页不是每次修改数据都刷入磁盘，这样性能会很差，因此一般都会在一定时机进行批量刷盘。
InnoDB 的更新操作采用的是 先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

四、索引的一系列问题？

1.什么是索引？

答：
索引就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是，索引就是数据的目录。

	存储引擎，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。
	索引和数据就是位于存储引擎。
	索引是以空间换时间的设计思想。

2.索引的分类有哪些？

1）按数据结构分类

答：
从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

B+Tree 索引的存储和查询过程？

1. B+Tree 索引的存储：

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。
每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。

2. B+Tree 索引的查询过程：

注：创建的主键索引和二级索引默认使用的都是 B+Tree 索引，但二者有区别

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

为什么 MySQL InnoDB 选择 B+tree 作为索引的数据结构？

具体谈一谈

2）按物理存储分类

答：

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。
如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。

3）按字段特性分类

答：
从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

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

4）按字段个数分类

答：
从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

建立在单列上的索引称为单列索引，比如主键索引；

通过将多个字段组合成一个索引称为联合索引。=> 联合索引 的 B+Tree 示意图

3.什么时候需要或不需要索引？

答：

4.有什么优化索引的方法？

答：

前缀索引优化：减少索引所占内存
覆盖索引优化：索引包含查询的数据
主键索引最好是自增的：插入新数据时，主键值是最大，避免像B+树中间结点插入
防止索引失效：索引不要NULL

5.什么时候索引会失效？

具体谈一谈索引失效的情况有哪些？原因是什么？见Q2

Q1：为什么 MySQL 采用 B+ 树作为索引？

1.怎样的索引的数据结构是好的？

答：
MySQL 的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的。

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。
所以，我们希望索引的数据结构能在尽可能少的磁盘的 I/O 操作中完成查询工作，因为磁盘 I/O 操作越少，所消耗的时间也就越小。

另外，MySQL 是支持范围查找的，所以索引的数据结构不仅要能高效地查询某一个记录，而且也要能高效地执行范围查找。

一个好的 MySQL 索引的数据结构，至少满足以下要求：

能在尽可能少的磁盘的 I/O 操作中完成查询工作；
要能高效地查询某一个记录，也要能高效地执行范围查找；

2.什么是二分查找？

答：

索引数据能按顺序排列，二分查找法每次都把查询的范围减半，这样时间复杂度就降到了 O(logn)。
但是每次查找都需要不断计算中间位置。

3.什么是二分查找树？

答：
用数组来实现线性排序的数据虽然简单好用，但是插入新元素的时候性能太低。
其次，有序的数组在使用二分查找的时候，每次查找都要不断计算中间的位置。
为了解决上面两个问题，提出二叉查找树：

二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点。

查询数据时，不需要计算中间节点的位置了，只需将查找的数据与节点的数据进行比较。
插入新节点时，因为二叉查找树是一个跳跃结构，不必连续排列。这样在插入的时候，新节点可以放在任何位置，不会像线性结构那样插入一个元素，所有元素都需要向后排列。

二叉查找树的缺点：
当每次插入的元素都是二叉查找树中最大的元素，二叉查找树就会退化成了一条链表，查找数据的时间复杂度变成了 O(n)

由于树是存储在磁盘中的，访问每个节点，都对应一次磁盘 I/O 操作（假设一个节点的大小「小于」操作系统的最小读写单位块的大小），也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会影响查询性能。

而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

4.什么是自平衡二叉树？

答：
为了解决二叉查找树会在极端情况下退化成链表的问题，后面就有人提出平衡二叉查找树（AVL 树）。

主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度差不能超过 1。
也就是说节点的左子树和右子树仍然为平衡二叉树，这样查询操作的时间复杂度就会一直维持在 O(logn) 。

自平衡二叉树缺点：
不管平衡二叉查找树还是红黑树，都会随着插入的元素增多，而导致树的高度变高，这就意味着磁盘 I/O 操作次数多，会影响整体数据查询的效率。

5.什么是 B 树？

答：
自平衡二叉树虽然能保持查询操作的时间复杂度在O(logn)，但是由于节点个数增多的时候，会导致树高非常高。而树高每增加一层，就会增加一次磁盘IO次数，从而影响数据查询的效率。
为了解决降低树的高度的问题，后面就出来了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。

B 树的每一个节点最多可以包括 M 个子节点，M 称为 B 树的阶，所以 B 树就是一个多叉树。
假设 M = 3，那么就是一棵 3 阶的 B 树，特点就是每个节点最多有 2 个（M-1个）数据和最多有 3 个（M个）子节点

如果同样的节点数量在平衡二叉树的场景下，树的高度就会很高，意味着磁盘 I/O 操作会更多。
所以，B 树在数据查询中比平衡二叉树效率要高。

B树的缺点：

B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。

（因为每次能从磁盘加载到内存的数据是定量的，如果其中数据很多，索引很少，那么就需要额外访问磁盘，进行索引加载）

而且在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，我们只是想读取这些节点的索引数据来做比较查询，而**「非 A 记录节点」里的记录数据对我们是没用的**，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

6. 什么是 B+ 树？

答：
为了解决B树存在的问题，提出B+ 树，就是对 B 树做了一个升级，MySQL 中索引的数据结构就是采用了 B+ 树。

B+ 树与 B 树差异的点，主要是以下这几点：

叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
非叶子节点中有多少个子节点，就有多少个索引；

单点查询：

插入和删除：
B+ 树的插入和删除效率更高

范围查询：

Q2：什么时候索引会失效？

索引存储结构长什么样？

答：
InnoDB 和 MyISAM 都支持 B+ 树索引，但是它们数据的存储结构实现方式不同。不同之处在于：

	InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
	MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

MyISAM 的B+ 树的主键索引结构：

InnoDB 的B+ 树的主键索引结构：

InnoDB 的B+ 树的二级索引结构：

使用「二级索引」字段作为条件查询的时候，如果要查询的数据都在「聚簇索引」的叶子节点里，那么需要检索两颗B+树：

先在「二级索引」的 B+ 树找到对应的叶子节点，获取主键值；
然后用上一步获取的主键值，在「聚簇索引」中的 B+ 树检索到对应的叶子节点，然后获取要查询的数据。

上面这个过程叫做回表

使用「二级索引」字段作为条件查询的时候，如果要查询的数据在「二级索引」的叶子节点，那么只需要在「二级索引」的 B+ 树找到对应的叶子节点，然后读取要查询的数据，这个过程叫做覆盖索引。

情况1：对索引使用左或者左右模糊匹配

答：
当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。

	查询 name 后缀为「林」的用户，执行计划中的 type=ALL 就代表了全表扫描，而没有走索引。
	// name 字段为二级索引
	select * from t_user where name like '%林';
	

	如果是查询 name 前缀为林的用户，那么就会走索引扫描，执行计划中的 type=range 表示走索引扫描，key=index_name 看到实际走了 index_name 索引：
	// name 字段为二级索引
	select * from t_user where name like '林%';

like 关键字左或者左右模糊匹配无法走索引的原因是，索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

情况2：对索引使用函数

答：
如果查询条件中对索引字段使用函数，就会导致索引失效。

对索引使用函数，就无法走索引的原因是，索引保存的是索引字段的原始值，而不是经过函数计算后的值。

情况3：对索引进行表达式计算

答：
在查询条件中对索引进行表达式计算，也是无法走索引的。

	select * from t_user where id + 1 = 10;

对索引进行表达式计算，就无法走索引的原因是，索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值。
只能通过把索引字段的取值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

情况4：对索引隐式类型转换

答：
如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描。

情况5：联合索引非最左匹配

答：

联合索引不遵循最左匹配原则就会失效原因是：

在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。
也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。
如果我们仅仅按照第二列搜索，肯定无法走索引。

情况6：where 子句中的 OR

答：
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

这是因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。
要解决办法很简单，将 age 字段设置为索引即可。

总结

五、锁的几个问题？

MySQL并发事务访问相同数据

答：
读----读情况：多个并发事务，读取相同数据。

读取操作本身不会对记录有任何影响，并不会引起什么问题，所以允许这种情况的发生。

写----写情况：多个并发事务，对相同数据做出改动

在这种情况下会发生 脏写 的问题，任何一种隔离级别都不允许这种问题的发生。
所以在多个未提交事务相继对一条记录做改动时，需要让它们 排队执行 ，这个排队的过程其实是通过 锁 来实现的。

读----写情况：一个事务进行读取操作，另一个进行改动操作。

可能发生 脏读 、 不可重复读 、 幻读 的问题。
解决：
		第一种方法是对读和写都加锁，读-写 操作彼此需要 排队执行 ，影响性能。
		第二种方法是对读进行多版本控制（MVCC），对写操作进行加锁，并发性更好。

1.什么是锁？为什么MySQL要有锁？

答：
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。
锁是一种为保证数据的一致性，对 并发操作进行控制 的一种机制 。
MySQL可以同时执行多个并发事务，因此需要锁机制来实现，事务的隔离性就是通过锁来实现的。

2.全局锁？

答：
全局锁执行后，整个数据库就处于只读状态。
其他线程对数据的增删改操作，对表结构的更改操作都会阻塞。

全局锁应用场景：全库逻辑备份
全局锁缺点：整个数据库都是只读状态，客户端除查询之外的所有操作停滞，影响性能。
改进：对于支持可重复读的事务隔离级别的存储引擎，在备份数据库之前先开启事务即可。

3.表级别锁？

答：
表级别锁就对数据库中某个表格加的锁。

1）表级别的共享锁和独占锁

表锁就是对表中的数据加上了共享锁或独占锁。

	共享锁：即读锁，s锁，加上该锁后，多个并发事务都执行进行读操作，本线程也无法进行写操作。
	独占锁：即写锁，x锁，加上该锁后，只允许本线程进行读写操作，其他线程不允许进行读和写。

2）元数据锁

元数据锁是对表的结构加上了读锁或写锁。
MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。
当用户对表结构进行变更时，防止其他用户对表执行 CRUD 操作。

对一张表进行 CRUD 操作时，加的是 MDL 读锁；
对一张表做结构变更操作的时候，加的是 MDL 写锁；

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

3）意向锁

意向锁是一种指示灯作用，当我们给某一行记录加入共享锁或独占锁时，存储引擎会自动在表级别加上对应的共享锁或独占锁，告知其他线程，这个表被上锁了，对于表级别的操作是有限制的。

因此，意向锁的目的是为了快速判断表里是否有记录被加锁。

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。
那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

因此意向锁对于行级锁没有影响，对于表级锁有影响。我们加锁时，一定要明白共享锁和独占锁是针对表级的，还是针对行级的。

4）AUTO-INC 锁

AUTO-INC 锁是在插入数据时，对某个字段的值进行自动递增，是一种表级别锁。

为某个字段声明 AUTO_INCREMENT 属性时，就是会加一个表级别的 AUTO-INC 锁，之后在插入数据时，可以不指定该字段的值，数据库会自动给该字段赋值递增的值。
一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

缺点：AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。
改进：
轻量级的锁，一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

总结：以上四种都是针对表级别的锁。

4.行级别锁？

答：

1）行级别的共享锁和独占锁（记录锁）

记录锁，Record Lock，也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。
比如我们把id值为8的那条记录加一个记录锁。仅仅是锁住了id值为8的记录，对周围的数据没有影响。

记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

2）间隙锁

间隙锁是锁定一个范围，但是不包含记录本身，Gap Lock。

3）临键锁（Next-Key Locks）

Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，称为Next-Key Locks 的锁， LOCK_ORDINARY