MySQL

（4）MVCC机制只在[读已提交]和[可重复读]的隔离级别下生效；在[读已提交]的隔离级别下，当前事务的每次快照都会生成一个Read View，这就保证了当前事务一定能通过可见性判断，读到其他事务提交的最新数据；而在[可重复读]的隔离级别下，Read View旨在当前事务第一次快照读的时候创建，后边再快照读时，读的都是第一个Read View，保证当前事务能够充分读取数据；

MVCC决定当前快照读都能读到的版本的步骤如下：

步骤	总结	执行
Step1	事务修改前+排他锁	当某个事务A修改某条记录时，首先数据库会给其加上排他锁
Step2	拷贝原记录到undo log日志	事务A修改之前，将原记录拷贝一份到undo log日志中，作为链表头（最新的历史记录）
Step3	更新回滚指针指向的历史版本与最后一次修改的事务id	事务A修改完毕，隐藏的事务id变为最后一次修改该记录的事务 ID，回滚指针指向undo log中的历史记录
Step4	事务提交+释放锁	事务A提交，释放排他锁
Step5	快照读时创建Read View	当某个事务B进行快照读时，会在当前时刻创建一个读视图Read View，此视图维护着当前数据库中活跃（未提交的）事务id列表
Step6	可见性判断	根据最后一次修改的事务id（DB_TRX_ID），与Read View中的属性进行[可见性判断]，决定当前快照读能够读到哪个版本的记录
Step7	判断步骤1	若DB_TRX_ID<当前活跃事务id的最小值，则当前执行快照读的事务可以看到数据库最新的记录；否则，转入下一步判断；
Step8	判断步骤2	若DB_TRX_id>当前活跃事务id的最大值，则当前执行快照读的事务不能看到数据库最新的记录；否则，转入下一步判断；
Step9	判断步骤3	若DB_TRX_ID不在当前活跃事务列表中，则当前执行快照读的事务可以看到数据库最新更新的数据；否则，看不到当前最新数据；DB_LAST_ID就更新为undo log中最新历史数据绑定的事务id值；转入Step7重新循环判断，知道找到当前事务快照读能够读到的历史版本进行展示；
Step10	展示读到的版本	根据可见性判断，找到了当前事务快照读所能读到的版本，进行读取展示

1.MVCC解决的是什么问题？

数据库并发场景有三种：

（1）读-读：不存在任何问题，不需要考虑并发控制；

（2）读-写：有线程安全问题，还要考虑事务隔离性，可能会出现脏读、幻读、不可重复读等问题；而加锁虽然能解决读-写冲突，但是性能太差。因此引入了一种MVCC机制，可以实现无锁并发，实现事务隔离性，解决脏读、幻读、不可重复读等问题；但是不能解决第二类更新丢失问题。

（3）写-写：有线程安全问题。采用加锁和乐观锁机制可以解决写-写冲突；

通过MVCC+乐观锁的机制（MVCC解决读写冲突，乐观锁解决写写冲突），能够最大程度的提高数据库的并发性，并解决读写冲突，和写写冲突导致的问题。

1）MVCC能解决幻读问题吗？

①当前读下MVCC无法解决幻读问题；需要用临建锁（nextKey）来解决；（锁定读）

②快照读下，读已提交+MVCC无法解决幻读；原因是：读已提交隔离级别下，MVCC每次快照读都会创建一个新的ReadView，用于保证本次快照读能够通过可见性判断，进而读到其他事务已经提交的结果，此时会[出现幻读问题]；

③快照读下：可重复读隔离级别+MVCC能解决幻读问题；原因是：可重复读隔离级别下，MVCC机制的ReadView，只能第一次快照读的时候创建，后续读到的都是第一次的ReadView（可重复读），保证不能通过可见性判断，进而每次都读取历史版本而不是最新版本，因此[不存在出现幻读问题]；

2.MVCC的实现原理

原理	作用
聚簇索引中的3个隐藏字段	（1）隐藏的自增主键id；（2）记录最后一次修改该记录的事务id；（3）指向该记录的上一个版本的回滚指针；
undo log日志	用于记录历史版本的快照，与事务id相关联。它能够解决数据库的一致性问题；（1）insert undo log:代表事务在insert新纪录时产生的undo log，旨在事务回滚时需要，并且在事务提交后可以被立即丢弃；（2）update undo log：事务在进行update或delete时产生undo log日志；不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
Read View读视图	当前事务进行快照读时产生，维护当前活跃（未提交）的事务id；作用是进行可见性判断，根据read view的属性来判断当前事务能够看到哪个版本的数据；（1）读已提交隔离级别下：当前事务每次快照都会创建一个新的Read View，保证当前事务能读取到其他事务提交后的新数据；（2）可重复读隔离级别下：Read View只会在当前事务的第一次快照读中创建，后续的快照读，都是读的第一次创建的Read View，保证可重复；

3.举例说明MVCC的执行流程

person表中有一条记录，name=jerry，age=24，隐式主键是1.

（1）初始没有事务修改，也没有历史版本，事务ID和回滚指针都是null

（2）事务1将表中的name字段Jerry改为Tom

①在事务1修改该行（记录）数据时，数据库会优先对该行加排他锁；

②然后把该行数据拷贝到undo log中，作为当前新纪录的历史版本；

③修改数据，事务id字段存储事务1的id，回滚指针指向undo log的历史版本数据；

④事务1提交后，释放锁；

（3）又来了一个事务2修改person表的同一个记录，将age修改为30岁

①在事务2修改该行（记录）数据时，数据库会先对该行加排他锁；

②然后把该行数据拷贝到undo log中，发现undo log 中已有数据，那么最新的旧数据就作为链表的表头，插在该行记录的undo log最前面；

③修改数据，事务id字段存储事务2的id，回滚指针指向undo log的历史版本最新数据；

④事务2提交后，释放锁。

4.Read View可见性判断算法过程

Read View主要是用来做可见性判断的，即当我们某个事务执行快照读的时候，对该数据创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，可能是最新数据，也可能是undo log中的某个历史版本的数据；

Read View 维护了系统当前活跃事务（也就是未提交的事务）的id列表，它有三个属性

属性	作用
trx_list	一个数值列表，用来维护Read View生成时刻系统正活跃的事务id
up_limit_id	记录活跃事务列表中id最小（早）的事务
low_limit_id	Read View生成时刻系统尚未分配的下一个事务id，也就是目前出现过的事务id的最大值

我们现在拿着最后一次修改（提交）记录的事务id（DR_TRX_ID）去进行比较；

Step1->Step3的循环判断，知道找到当前事务快照读能读到的记录版本。

阶段	作用
Step1	当DB_TRX_ID<当前活跃事务id的最小值，则当前执行快照读的事务可以看到数据库最新的记录；否则，转入Step2；
Step2	当DB_TRX_ID>当前活跃事务id的最大值，则当前执行快照读的事务不能看到数据库最新的记录；否则，转入Step3;
Step3	若DB_TRX_ID不在当前活跃事务列表中，则当前执行快照读的事务可以看到数据库最新的记录；否则，看不到当前最新数据；DB_TRX_ID就更新为undo_log中最新历史数据绑定的事务id值，转入Step1重新循环判断，知道找到当前事务快照读可以读到的历史版本。

二、数据库的锁

1.MySQL锁机制

级别	引擎	原理
行锁	InnoDB默认	（1）记录锁：将查询范围内的行都锁上；共享锁（读锁）和排他锁（写锁）（2）间隙锁：锁定一个范围，防止其他事务操作间隙插入或删除数据。目的是为了解决幻读。只有在可重复读和串行化隔离界别才有（3）临建锁：记录锁+间隙锁；用于解决当前读下的幻读
表锁	InnoDB支持 Myisam默认	表锁是一种悲观锁，分为表共享锁与表排他锁，不会出现死锁现象
页锁	InnoDB支持	页锁，即锁住B+树的一次IO读写的磁盘块，因此锁住的是一组相邻的数据；粒度介于行锁和表锁之间；会出现死锁

2.悲观锁和乐观锁

注意：悲观锁和乐观锁只是一种思想

锁

适用场景

原理

乐观锁

读多、读写冲突少

只在提交操作的时候检查是否违反数据完整性

（1）CAS算法（失败重试）

（2）版本号机制（每次更新时，版本号Version会+1，当线程做更新时就会读版本号Version，对比读到的版本号和数据之前的版本号是否一致，是则提交更新，否则提交失败）

悲观锁

写多，读写冲突多

直接通过排他锁或共享锁锁住【行、表、页】，屏蔽一切可能违反数据完整性的操作

3.如何避免死锁

解决方式

（1）约定读取表的顺序

（2）同一事务内，一次锁定全部需要的资源

（3）升级锁的粒度，升级为页锁或者表锁

（4）乐观锁或者分布式锁（redis）：

分布式锁，顾名思义，就是分布式项目开发中用到的锁，可以用来控制分布式系统之间同步访问共享资

源。

三、数据库事务

1.事务的ACID是怎么保证的？

特性	概述	实现原理
原子性（A）	要么都做，要么都不做	undo log日志：事务未提交成功，则撤销已经执行成功的sql语句
一致性（C）	从一个一致性到另一个一致性，例如转账	原子性+隔离性+持久性+业务层面上的逻辑
隔离性（I）	事件之间不能互相干扰	MVCC机制：基于聚簇索引中的三个隐藏字段（自增id、回滚指针、事务id）+undo log日志（历史版本）+Read View读视图（可见性判断），保证读已提交与可重复读的隔离级别
持久性（D）	事务提交写入磁盘，永久性保存	redo log日志：在事务的执行过程中，将日志写入redo log日志中，即使数据没有持久化成功，只要redo log日志保存了。当服务器重启时，就会根据redo log日志重新持久化

2.事务隔离级别

隔离级别	问题	概述	原理
读未提交	脏读、幻读、不可重复读、第二类丢失问题	可以读到其他事务没有提交的数据
读已提交	幻读、不可重复读、第二类更新丢失问题	只能读到其他事务提交后的执行结果	MVCC机制：每次快照读都会创建一个新的Read View
可重复读	幻读、第二类丢失问题	支持读已提交，可以充分读取相同的数据（Mysql默认）	MVCC机制：Read View只会在第一次快照读的时候创建，后续的快照都是读的第一个Read View
可串行化	无	支持可重复读，最高隔离级别（InnoDB分布式事务下默认）	事务排序+共享锁，解决幻读

加for updata 不走MVCC机制，走当前读，会出现幻读；不加for updata直接走MVCC机制

3.快照读和当前读

类型

形式

出现问题

如何解决

快照读

普通select语句

脏读、幻读、不可重复读、第二类更新丢失

（1）脏读、幻读、不可重复读：MVCC机制（Read View可见性判断）

（2）第二类更新丢失问题：乐观锁思想使用版本号机制解决（版本号相同才能提交修改，否则不能提交）

当前读

（1）排他锁：select..for update

（2）共享锁：select...lock in share mode

（3）insert、update、delete：自动加了排他锁fro upadte

幻读、第二类更新丢失问题

（1）幻读：临建锁（当前读自身的排他锁+间隙锁）

（2）第二类更新丢失：执行更新操作前，先用排他锁锁住，然后更新；

4.脏读、幻读、不可重复读、更新丢失问题

问题	描述	解决方式
脏读	读到了其他事务未提交的数据，然后其他事务回滚	（1）当前读下（即排他锁/共享锁）：其他事务无法获取锁进行修改，也就不会有脏读问题（2）快照读下：利用MVCC机制的Read View读视图的创建时机与可见性判断，可以实现读已提交和可重复读的隔离基本，进行解决
不可重复读	同一事务中，两次读到的数据不一样	（1）当前读下（即排他锁/共享锁）：其他事务无法获取锁进行修改，也就不存在不可重复读问题（2）快照读下：利用MVCC机制的Read view读视图的创建时机与可见性判断，来实现可重复读的隔离级别进行解决；
幻读	由于其他事务的插入、删除是操作行的间隙，因此只加行锁是组织不了其他事务操作间隙的。就会导致前后两次本事务读到的结果变多或者变少	（1）当前读下（即排他锁/共享锁）：可以阻止其他事务更新操作，但是不能组织其他事务的插入、删除操作；因此要引入间隙锁把行的间隙也所著；而当前读自己本身的排他锁与引入的间隙锁，就组成了临建锁；（2）快照读下：只在可重复读隔离级别+MVC机制，可以解决幻读问题；原因就是利用了MVCC第一次读到的Read View（可重复读），保证了每次读取都是历史数据，因此不会出现幻读；而读已提交隔离级别+MVCC无法解决幻读问题；
更新丢失问题	（1）第一类更新丢失（所有隔离级别都能够避免，可以忽略）：事务A与事务B同时操作一个数据行，事务A提交完毕，但是事务B回滚啦，导致A 的更新丢失（2）第二类更新丢失问题：事务A和事务B同时操作一个数据行（都没有加记录锁），事务A先提交完毕。然后事务B进行了修改然后提交，覆盖掉了事务A提交的结果	针对第二类更新丢失问题：（1）更新前，先用当前读锁住：先select+（排他锁/共享锁）锁住当前行之后，再进行更新操作；（2）快照读下：乐观锁机制，通过version字段，事务提交之前都判断数据version是否发生变化，变化则提交失败。

数据库第二类更新丢失问题：

第二类数据丢失的问题是关于多个事务同时更新一行数据导致的问题，如下表所示，事务A和事务B都更新一行数据，他们事务开始的时候都查询到账户有1000元，然后都往账户添加了100元，最后大家都提交了各自的事务，结果却是错误的。

四、数据库的日志系统

1.四种日志总结

日志	作用	原理
undo log日志	保证[原子性]	记录数据的历史版本，可以回滚到事务未提交之前的版本，MVCC机制也需要undo log日志
redo log日志	保证[持久性]	redo log日志是InnoDB数据库引擎层面上的重做物理日志，基于WAL技术（先写入日志，再写入磁盘）与二阶段式提交（主从同步下），保证事务提交后的数据能够写入到磁盘
bin log日志	数据的复制与恢复，实现[主从复制]	bin log日志是MySql中Service层面上的逻辑日志，作用是记录所有数据库的变更（create、Alter等）与表数据的修改（insert、update、delete）等；主服务器的binlog日志与从服务器的relaylog共同实现主从复制；
relay log日志	从服务器的中继日志，从服务器中存储着从bin log中读到的数据；从服务器用于解析成SQL进行重放；[主从复制]	relay log日志有两个info结构：（1）master.info：记录上一次从master节点同步的bin log位置，以及连接master和启动复制所需的全部信息；（2）slave.info：记录了slave复制master文件的进度，以及下一次服务从哪个位置开始。

2.bin log日志和redo log日志？

（1）逻辑日志 bin log

作用：逻辑日志bin log的作用是记录数据库表和表数据库的修改操作，它有三种记录模式：基于SQL语句模式的statement、基于行的模式row以及混合模式；

工作层：MySql中的Service层上，可以结合任意存储引擎使用

空间大小：无限

记录模式：基于SQL语句的模式、基于行的模式、混合模式

模式	原理
statement	基于sql语句的模式。缺点：有些sql语句中有UUID等随机函数会导致记录的数据不一致；
row	基于数据行的模式。直接记录行的变化，但是开销大，可能会造成库延迟，效率相对一些
mixed	前两种的混合模式，根据语句来是自适应的选择哪一种（推荐）

（2）物理日志redo log

作用：物理日志redo log的作用是实现数据库的持久化；Mysql中有一个问题，如果每一次的更新操作都写入磁盘，然后磁盘也要找到对应的那条记录，然后更新，整个过程IO成本、查找成本太高了。因此，Mysql引入了WAL技术（write-Ahead Logging），关键点就在于先写入redo log日志然后同步更新内存，InnoDB引擎在系统空闲的时候再写入到磁盘中进行持久化，提高效率；

工作层：InnoDB存储引擎特有的

空间大小：空间大小固定，循环写入，当空间不足时，需要持久到磁盘，然后空出来的部分再写入

（3）bin log日志和redo log日志的区别？

	bin log日志	redo log 日志
工作层	Service	InnoDB存储引擎特有的
内存空间	逻辑日志：存储sql语句修改的逻辑，用于主从复制与数据恢复	物理日志：存储的是“在某个数据也上做了什么修改”，更新内存并写入磁盘进行持久化保存
内存空间	bin log是可以追加写入的，没用空间概念，一直写就行	redo log是循环写入的，有固定的大小，所以它的空间会用完，如果用完的话，一定要进行一些写入磁盘的操作才能继续；
crash-safe能力	不具备	能够恢复到数据库宕机之前的最后一次事务提交版本

（4）bin log日志与redo log日志如何保证一致性：二阶段提交（2pc）

“主从同步靠bin log日志，bin log 和 redo log一致二阶段提交”

阶段一：二阶段提交

redolog和binlog有一个共同的数据字段（redo log给bin log），叫XID；

提交阶段	执行内容
Step1	事务开始执行；
Step2	开始写入redo log日志，并同步持久化到redo log的磁盘空间；redo log事务中记录二阶段提交的XID，并且redo log事务标识变为prepare；
Step3	开始写入bin log日志，并同步持久化到bin log的磁盘空间；写完之后，bin log事务记录二阶段提交的XID；
Step4	bin log写入成功，redo log事务标识变成commit；
Step5	事务提交；

阶段二：数据恢复：

若在上述阶段提交的过程中，数据库宕机了；那么在MySql重启时，会根据redo log的标识与bin log的二阶段XID字段进行数据恢复，判断是否应该提交[宕机时未能提交的事务]以保证redo log与undo log中数据的一致性；

★恢复关键点：redo log与bin log是否全部写入成功；

原因： redo log与bin log的执行过程是在事务的执行过程中进行的，实际上当redo log与bin log 都写入成功时，事务就已经真正意义上的提交了，因此数据恢复时，是否提交[由于宕机未能提交的事务]关键在于： redo log 与 bin log是否都写入成功；

恢复阶段	执行内容
Step1	按顺序扫描redo log，如果redo log中的事务既有prepare标识，又有commit标识，就直接提交 [宕机时未成功提交成功的] 事务，保证一致性；
Step2	若redo log中的事务只有prepare标识，则证明bin log的写入过程宕机啦；此时要根据XID字段来判断bin log日志是否写入成功，以决定是否应该提交[宕机时未提交成功]的事务；
Step3	（1）若bin log中没有二阶段提交的XID字段，那么证明bin log日志也没有写入成功；则使用undo log来回滚redo log中的事务（prepare标识表示已经写入redo log），保证两个日志中的数据一致；（2）若bin log中存在二阶段提交的XID字段，那么证明bin log日志写入成功；此时可以提交[宕机时未成功提交]的事务，保证一致性。

“[宕机时未成功提交]”指的两个都写好了只是没有最后提交。commit之后有一个提交。

(5)MySQL的执行查询过程？

客户端通过连接器访问 Server 层。连接器主要负责身份认证和权限鉴别的工作。即校验账户密码，权限。分析器是用来分辨SQL语句的执行目的。优化器的作用是找到其中最好的执行计划。执行器执行语句。

执行计划?

执行一条sql语句，但是server并没有告诉服务器如何去做。服务i去在收到sql语句后会先检查语法错误，没有问题才会解析执行。在这个过程中，他会考虑是执行整张表，还是根据索引，服务器会比较所有可能的方法所消耗的资源，最终sql语句被物理性执行的计划叫做执行计划。

五、Mysql主从复制

1.什么是主从复制？

Mysql主从复制指的是，数据可以Mysql主服务复制到其他从服务器。mysql默认采用异步复制的方式，因此从服务器可以不用一致连接着主服务器，甚至可以通过拨号的方式断断续续的连接主服务器；根据配置文件，从服务器可以指定复制主服务器中全部或者部分的数据。作用：通过主从复制，可以实现读写分离（主写从读）以及数据备份，提高服务器的访问性能以及安全按性。

2.主从复制的作用？

（1）读写分离，提高数据库的访问性能；主写从读，减少数据库的压力；

（2）数据备份，提高数据安全性；从服务器可以多地备份主服务器的数据，且随时终止复制流程，不破坏主服务器的数据。

3.主从复制原理？

主从复制过程的关键点：

文件	位置	作用
bin log文件	主	用于记录主服务器中表结构与行数据的更新操作（基于sql语句模式、基于行变化模式、混合模式）
dump线程	主	响应从服务器的I/O线程请求，给从服务器发送bin log日志数据
I/O线程	从	当从服务器探测到了主服务器bin log日志的变化，则开启I/O线程向主服务器请求读取bin log日志
SQL线程	从	从服务器开始SQL线程读取relay log日志，解析成SQL语句进行本地重放
relay log	从	从服务器的I/O线程读取bin log日志后，写入到本地的中继日志中
master.info	relay log日志	记录了上一次I/O线程读取到bin log的位置；目的是：本次I/O线程从bin log的哪个位置开始请求读取
relay.info	relay log日志	记录了上一次SQL线程读取relay log 的位置；目的是：本次SQL线程从relay log的哪个位置开始SQL重放

★主从复制过程：

阶段	服务器	执行内容
Step1	主	master服务器将更新的sql语句，记录在master的bin log日志中；然后授予slave服务器远程连接的权限
Step2	从	（1）slave服务器在一定事件间隔中探测bin log日志是否发生改变；（2）若发生改变，则slave开启I/O线程，向master服务器请求读取bin log日志；
Step3	主	master服务器收到请求，开启dump线程给slave服务器传输bin log日志的二进制数据
Step4	从	（1）slave服务器收到bin log数据，存储到TCP/IP缓存，立即返回ACK给主库（解决主从同步延时问题），并更新master.info（记录了读到了bin log的哪个位置）（2）将TCP/IP缓存，写入自己的中继日志relay log；（3）然后开启sql线程，sql线程读取relay中的relay.info，获取到sql线程上次读取relay log的位置，然后将relay log的新数据解析成sql语句，进行本地重放（保证slave和master的数据一致性）；重放结束后，更新relay.info；（4）I/O线程与sql线程进入睡眠，等待下一次唤醒。

六、Mysql索引

1.Mysql中的两种存储引擎

二者区别如下：

（1）InnoDB支持事务，MyISAM不支持事务，对于InnoDB每一条SQL语言都默认封装成事务；

（2）InnoDB支持外键，而MyISAM不支持外键。对于包含外键的InnoDB表转换为MyISAM会失败；

（3）InnoDB是聚簇索引，使用B+Tree结构，数据与主键都存储在B+Tree的叶子节点中，非叶子节点存储的都是叶子节点的key值；而MyISAM是非聚簇索引，也是适用B+Tree结构，但叶子节点存储的是数据的物理地址，需要额外的进行一次IO去真正的地址中读取数据；

（4） InnoDB支持表锁、行锁（默认），而MyISAM支持表级锁。

（5）InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生成一个隐藏列Row_id来充当默认主键），而MyISAM可以没有。

（6）InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM更高。

2.为什么索引数据使用B+Tree？

首先从可能的各种数据结构上分析。二叉树的数据量越多，树越高，且树的高度不能自动平衡，查找效率和树的高度直接相关；红黑树的话本质上是也是一棵二叉树，因此随着数据量的增多，查找效率也会变差； Hash的话优点是可以快速的定位数据，更适用于等值查询。但是它不适用于范围查询，不支持模糊查询，不支持排序，不支持只查部分字段，并且数据量大的时候，容易发送哈希碰撞，占用的内存也非常大；

B-Tree就是一棵多叉搜索树，优点是能够自平衡，数据分布在各个节点中（每个节点都存有key-value），每个节点都存储了多个关键字，IO效率相对于二叉树、红黑树、Hash更高。B+Tree的话是在B-Tree的基础上改进的，叶子节点具有指向上一个和下一个叶子节点的指针。并且所有数据都存储在叶子节点中，非叶子节点只存储key值。B+Tree通过只在叶子节点中存储数据的方式，大大的提高了磁盘页中存储key的数量，因此在相同的数据量的情况下，B+Tree的高度要比B-Tree低得多；在查询数据时，B-Tree由于每个叶子节点都存放了数据，因此总的IO次数是不一定的，距离根节点越近查询次数越少，反之越多。而B+Tree的只在叶子节点中存放数据，这就保证了每次IO的次数都是固定的，IO次数等于树的层数，而且B+Tree的高度又低通常3~4层就已经能够应对千万级别的数据了，所以总的IO效率B+Tree更好。此外B+Tree叶子节点的前后指针还解决了B-Tree不利于范围查询的问题。因此B+Tree实用性更好，效率更高。

由于B+Tree只在叶子节点存储数据，其他层不存数据，所以假设非叶子节点key+指针的大小是10字节，那么一页可以存放1600个key，三层就是1600*1600*16个数据，达到了千万级，所以B+Tree是B-Tree存储树的一万倍。

3.聚簇索引与非聚簇索引

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

存储引擎

存储内容

是否需要回表

聚簇索引

InnoDB特有的，主键就是聚簇索引

索引B+Tree的子节点存储的是【索引+数据】

不需要回表，找到聚簇索引就找到了数据

非聚簇索引（二级索引）

（1）InnoDB引擎下，除了主键索引都是非聚簇索引（包括自建的）

（2）Myisam引擎下，主键索引和二级索引都是非聚簇索引

索引B+树的叶子节点，存储着【聚簇索引的key】

根据是否二级索引覆盖来判断是否需要回表；（1）若查询的内容就是二级索引本身能够查询到的（索引覆盖），则无需回表；

（2）没有覆盖索引，则需要回表；

（1）为什么聚簇索引的key值推荐使用自增主键？

自增主键可以保证每次插入时B+Tree索引是从右边扩展，可以避免B+Tree树的频繁合并和分裂（对比使用UUID）造成的性能消耗。如果使用字符串和随机主键，会使得数据随机插入，效率会非常的低。

（2）分布式情况下还能使用自增主键充当B+Tree的叶子节点key吗？

不能，分布式数据库，B+Tree的key需要使用雪花片算法或者分布式id生成器。

（3）索引主键key的存储类型是int还是varchar？

在一个16KB的磁盘中，主键key占的字节越少，那么能存储的key范围就越大；因此用int还是varchar就看哪个占的字节小；int（4个字节），当varchar占的字节小于4个时，推荐用varchar ，反之，则用int类型；

4.回表查询

（1）什么是回表？

所谓的回表查询，执行一条sql语句，需要从两个b+Tree索引中去取数据，先定位主键值，再定位行记录。

我们使用非聚簇索引查询时，首先要查询非聚簇索引的B+Tree，然后判断是否非聚簇索引自己本身的字段已经能够命中想要查询的全部内容（索引覆盖）；a.如果能够覆盖，则不需要去聚簇索引的B+Tree树中去回表；b.如果不覆盖，则需要去拿着非聚簇索引中查到的主键id值，再去聚簇索引的B+树种查询。

（2）如何避免回表？

使用覆盖索引，所谓覆盖索引就是指索引中包含了查询中的所有字段，这种情况不需要再进行回表查询了。

（3）回表的几种情况分析

三种查询的执行流程：

<1>根据主键索引（聚簇索引）查询：不需要回表

①sql语句：select*from table where id=5；

②分析：直接查询聚簇索引的B+树结构，索引和数据放在一起，直接命中要查询的内容，无需回表；

<2>根据二级索引（非聚簇索引）查询

1>不需要回表的情况:二级索引覆盖

①sql语句：select name，id from table where name=李四；

②分析：name是二级索引（非聚簇索引），先查询非聚簇索引B+Tree，查到了数据（name=李四，id=5），此时发现要查询的内容全部命中，可以不用去聚簇索引种回表；

2>需要回表的情况:二级索引覆盖

①sql语句：select * from table where name=李四；

②分析：name是二级索引（非聚簇索引），先查询非聚簇索引B+Tree，查到了数据（name=李四，id=5），此时发现要查询的内容没有全部命中，那么根据id=5去聚簇索引B+Tree查询，最终得到数据（id=5，name=李四，age=25，sex=男）；

（3）索引下推

作用：提高非聚簇索引的查询效率，减少回表次数：

①在Mysql5.6之前，当使用非主键联合索引进行查询时，存储引擎在非主键索引B+树中查询数据之后，然后将结果返回给MySQL的Server层，在Server层判断是否符合条件（即回表）。

②MySQL5.6之后，推出了索引下推技术，判断是否符合条件的这一步是在存储引擎层中实现的；存储引擎会直接根据索引条件过滤掉不符合的结果，然后回表查询得到结果，将结果返回给MySQL server。

案例：（name，level）是联合索引，查询下表

SELECT address FROM user_info WHERE name LIKE "大%" AND level = 1;

<1>在Mysql5.6之前，没有索引下推

步骤	内容
Step1	根据条件【name like“大%”】在在【存储引擎层】的联合索引（name，level）的B+Tree树种查到了两条记录（id=1，name=大彬，level=1）与（id=4，name=大铁锤，level=4）
Step2	将这两条记录返回给Mysql【Service层】，然后拿着（id=1）与（id=4）去主键索引B+Tree种回表，根据【level=1】进行查询
Step3	经过两次回表，最终得到（weapon=铁锤）

<2>在Mysql5.6之后，开始索引下推技术

步骤	内容
Step1	根据条件【name like ’陈%‘】在【存储引擎层】的联合索引（name，level）的B+Tree种查询到了两条记录（id=1,name=大彬，level=1）与（id=4,name=大铁锤，level=4）
Step2	然后再根据索引条件【level】过滤得到（id=1，name=大彬，level=1）
Step3	将一条记录返回给Mysql的【Service】层，然后拿着（id=1）去主键索引B+树种回表，根据条件【level=1】进行查询
Step4	经过一次回表，得到（weapon=铁锤）

5.最左前缀原则

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。mysql会一直向右匹配直到遇到范围查询(>、<、between、 like)就停止匹配。

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

= 和 in 可以乱序，比如 a = 1 and b = 2 and c = 3 建立 (a,b,c) 索引可以任意顺序， mysql 的查询优化器会帮你优化成索引可以识别的形式。

案例：表table建立了联合索引（a,b,c）；

（1）Mysql的索引顺序必须从左到右排序；

1、不会走索引的情况：跳过索引a
	select * from table where b = "1";
	select * from table where c = "1"; 
	select * from table where  b = "1" and c = "2"; 

2、走索引的情况

  1)全部索引都存在，但是乱序。优化器可以优化
	select * from table where c = "1" and b = "2" and a = "3"; 
	
  2) 部分索引顺序是相对从左至右的，也可以走索引
	select * from table where a = "1" and  c = "3"; 
	select * from table where a = "1" and  b = "3";

（2）Mysql会一直向右匹配知道遇到范围查询就停止匹配索引；

1、不会走索引的情况

	1) 遇到范围查询（in、between、>、<）
	select * from table where a = "1" and b > "2" and c = "3";// c不走索引
	select * from table where a in ("1","2") and b = "2" and c = "3";// b、c都不走索引

（3）Mysql遇到不符合最左前缀原则的模糊查询，也不会走索引

1、不会走索引的情况

	1) %在前 或 %%
	select * from table where a like %张 and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描
	select * from table where a like %张% and b = "1" and c = "2";// a,b,c都不会走索引，全表扫描  

1、走索引的情况

	1) %在后
	select * from table where a like 张% and b = "1" and c = "2";// a,b,c都走索引；
	//a的索引类型为index或者range（由优化器决定）

6.前缀索引

因为可能我们索引的字段非常长，这既占内存空间，也不利于维护。所以我们就想，如果只把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果。但是，我们需要注意，order by不支持前缀索引。

7.索引失效的几种情况

失效情况	举例
① 索引类型不一致	phone字段类型是varchar，查询用的是int：`select * from table where phone = 123；`
② where中使用了函数	select * from table where DATE(create_time) = '2022-01-01' ；
③ 模糊查询%不在后边	select * from table where name like "%张%" select * from table where name like "%张"
④ or引起索引失效	`select * from table where name = "张三" or age = 10 ；` name和age只要有一个没有建索引，那么就都不走索引
⑤ 不符合最左前缀原则	具体见上
⑥ 索引列参与计算	select * from table where age - 1 = 10

8.怎么查看Mysql语句是否用到了索引？

通过explain命令。可以查看语句的执行计划。如type，possible_key，key等。type可以判断查询是全表扫描还是索引扫描，all是全表扫描。possible_key表示可能用到的索引。key是此字段是mysql在当前查询真正用到的索引。

8.怎么创建索引？索引创建注意什么？

（1）在执行CREATE TABLE的时候创建索引；还可以使用ALTER TABLE命令去增加索引。

（2）注意：<1>非空字段，应该指定列为NOT NULL；<2>取值离散大的字段，变量之间的差异程度要大，可以通过count()函数查看字段的差异值；<3>索引的字段越小越好。

七、Mysql调优

问到如何调优的万能回答

解决层面	方式
索引层面	① 联合索引：提高检索效率； ② 索引下堆：减少回表次数； ③ 索引覆盖：减少回表次数； ④ 索引类型：当varchar字节小于4时，比int类型效率高；
数据库层面	① 主从同步：读写分离（主写从读）； ② 分库分表：垂直拆分与水平拆分；提高并发量与磁盘使用率；
缓存层面	① Redis缓存：但需要考虑数据一致性（延迟双删、redis用消息队列订阅MySQL的binlog日志、读取与更新操作的异步串行化） ② MySQL缓存：现在已经没有了，因为缓存的命中率太低，而且一旦数据更新就要缓存无效化；
SQL语法层面	① 避免索引失效的语法； ②分析语句，看看是否加载了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。 ③分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。 ② 适当使用子查询，来覆盖索引，减小回表次数；

八、Mysql分库分表

1.什么是分表？

单表数据量太大，会极大影响你的sql执行的性能，到了后面sql可能跑到很慢了。一般来说，表单到几百万的时候，性能就会相对差一些了，就需要分表了。分表就是把一个表的数据放到多个表中，然后查询的时候就查一个表。比如按照用户id来分表，将一个用户的数据放到一个表中。然后操作的时候你对一个用户就操作那个表就好了。这样可以扩展每个表的数据量在可控范围内，比如单个表就固定在200万以内。

2.什么是分库？

分库就是你一个库一般我们经验而言，最多支撑到并发 2000 ，一定要扩容了，而且一个健康的单库并发值你最好保持在每秒1000 左右，不要太大。那么你可以将一个库的数据拆分到多个库中，访问的时候就访问一个库好了。

总结：分表能够解决单表数据量过大带来的查询效率下降的问题；分库可以解决面对高并发的读写访问压力的问题，当数据库master服务器无法承载写操作压力时，不管如何扩展slave服务器，都于事无补。此时，则需要通过数据分库策略，提高数据库并发访问能力。分库分表技术优化了数据库存储方式，有效减小数据库服务器的负担、缩短查询响应时间。

3.分库分表中间件？

常见的有cobar、TDDL、atlas、sharding-jdbc、mycat。

中间件	介绍
sharding-jdbc	当当开源的，属于 client 层方案。确实之前用的还比较多一些，因为 SQL 语法支持也比较多，没有太多限制，而且目前推出到了 2.0 版本，支持分库分表、读写分离、分布式 id 生成、柔性事务（最大努力送达型事务、TCC 事务）。而且确实之前使用的公司会比较多一些（这个在官网有登记使用的公司，可以看到从 2017 年一直到现在，是有不少公司在用的），目前社区也还一直在开发和维护，还算是比较活跃，个人认为算是一个现在也可以选择的方案。
mycat	基于 cobar 改造的，属于 proxy 层方案，支持的功能非常完善，而且目前应该是非常火的而且不断流行的数据库中间件，社区很活跃，也有一些公司开始在用了。但是确实相比于 sharding jdbc 来说，年轻一些，经历的锤炼少一些。
cobar	阿里 b2b 团队开发和开源的，属于 proxy 层方案。早些年还可以用，但是最近几年都没更新了，基本没啥人用，差不多算是被抛弃的状态吧。而且不支持读写分离、存储过程、跨库 join 和分页等操作。
TDDL	淘宝团队开发的，属于 client 层方案。支持基本的 crud 语法和读写分离，但不支持 join 、多表查询等语法。目前使用的也不多，因为还依赖淘宝的 diamond 配置管理系统。

4.如何对数据库进行垂直拆分或水平拆分？

（1）水平拆分，就是把一个表的数据弄到多个库的多个表里去去，但是每个库的表结构都一样，只不过每个库表方的数据是不同的，所有库表的数据加起来就是全部数据。水平拆分的意义，就是将数据均有的放到更多的库里面，然后用多个库来抗更高的并发，还有就是用多个库的存储容量来进行扩容。

（2）垂直拆分的意思，就是把一个有很多字段的表拆分成多个表，或者多个库上去。每个库表的结构都不一样，每个库都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表中，然后将较多的访问频率很低的字段放到另外一个表里。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。

5.怎么去分库分表？

一种是按照range来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，但是这种一般较少用，因为很容易产生热点问题，大量的流量都打在最新的数据上了。好处是扩容的时间很简单，只要预备好，给每个月都准备一个库就可以了，到了一个新的月份时候，自然而然，就会写新的库了；缺点就是大部分请请求，都是访问最新的数据。实际生产用range，要看场景。

一种是按照某个字段hash一下均匀分散，这个比较常用。好处在于，可以平均分配每个库的数据量和请求压力；坏处就是扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算hash值重新分配到不同的库或者表。

6.什么是分区？

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的。

7.项目中的数据库怎么设计的？

数据库设计主要分为六步，首先（1）是分析用户需求，包括数据、功能和性能需求；（2）概念结构设计：主要采用E-R模型进行设计，实体-联系模型；（3）逻辑结构设计：通过把E-R模型将转换成表，实现E-R模型到关系模型的转换

九、Mysql零碎的知识

1.数据库的三范式是什么？

（1）第一范式：强调的是列的原子性，即数据库的每一列都是不可分割的原子数据项；

（2）第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性；

（3）第三范式：任何非主属性不依赖于其它非主属性。

2. SQL约束有哪几种？

（1）NTO NULL:用于控制字段的内容一定不能为空（NULL）。

（2）UNIQUE:控件字段内容不能重复，一个表允许有多个Unique约束。

（3）PRIMARY KEY：也是用于控件字段内容不能重复，但它在一个表只允许出现一个。

（4）FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

（5）CHECK: 用于控制字段的值范围。

3.Mysql中varchar和char有什么区别？

char 是一个定长字段 ，假如申请了 char(10) 的空间 , 那么无论实际存储多少内容。该字段都占用 10 个字符 , 而 varchar 是变长的 , 也就是说申请的只是最大长度 , 占用的空间为实际字符长度 +1, 最后一个字符存储使用了多长的空间。

在检索效率上来讲,char > varchar, 因此在使用中 , 如果确定某个字段的值的长度 , 可以使用 char, 否则应该尽量使用 varchar. 例如存储用户 MD5 加密后的密码 , 则应该使用 char 。

一些细节问题

1.B树和B+树的查询过程

（1）B树的查询过程：

注意：a.B-Tree的每个节点都存储着数据data；

b.一次IO就是读取一个16KB大小的磁盘块（数据页）；

16KB的数据可以改，调整参数innodb_page_size；

①以查询key值为【29】的数据data为例：

步骤	执行
Step1	查磁盘块1（数据页），16 ≤ 【29】 ≤ 34，则选中该层的指针p2；
Step2	查磁盘块3（数据页），25 ≤ 【29】 ≤ 31，则选中该层的指针p2；
Step3	查磁盘块8（数据页），找到目标key值【29】，得到数据data；

步骤	执行
Step1	查磁盘块1（数据页），34 ≤ 【64】，则选中该层的指针p3；
Step2	查磁盘块4（数据页），找到目标key值【64】，得到数据data；

（2）B+树的查询过程：

在B+Tree上有两个头指针，一个指向根节点，另一个指向key最小的叶子节点。且所有叶子节点（数据节点）之间是一种链式环形结构。因此B+Tree有两种查询结构。

a.从【根节点】开始进行【随机查找】；

b.从【主键最小的叶子节点】开始进行【范围查找和分页查找】

（3）为什么B+Tree只在叶子节点中存储数据，非叶子节点只存储key？

①通常情况下，数据库的B+树高度在3-4层左右，已经能够存储千万级别的数据量了；

②能够当作key值的类型的优先级：主键索引>唯一索引>6个字节的row_id；

★原因：

总的来说， B树与B+树1次读取就是与磁盘的1次IO交互，而磁盘(数据页)占用16KB的空间；也就是说，如果16KB的空间能够存储的key值（主键值）范围越大，那么我们1次IO交互就能读到的数据量越多；换句话说，在数据量固定的情况下，磁盘能存储的key值范围越大，我们就能减小IO交互次数，提高IO读写效率；

其次：B树的每个16KB的磁盘节点，存储着三个类型的数据：【key值（主键）、value值（查询的数据）、指针（指向下一个磁盘节点的地址信息）】，而这些数据中（查询的数据）占用空间最大，导致剩余存储key值范围的空间越小。而B+Tree只在叶子节点存储数据，非叶子节点值存储key和指针，所以存储更多的key值和指针，从而大大减少IO次数，提高数据库的IO读写效率。