MySql数据库面试整理

1、数据库的三范式是什么?

**第一范式(INF)：**强调的是列的原子性，即数据库表中的每一列都是不可分割的原子数据项

**第二范式(2NF)：**要求实体属性完全依赖于主关键字。

**第二范式(3NF)：**任何非主属性不依赖于其他非主属性

INNODB与MyISAM比较

• MyISAM是非事务安全的，而InnoDB是事务安全的
• MyISAM锁的粒度是表级的，而InnoDB支持行级锁
• MyISAM支持全文类型索引，而Innodb不支持全文索引
• MyISAM相对简单，效率上优于InnoDB,小型应用可以考虑MyISAM
• MyISAM表保存成文件形式，跨平台使用更加方便
• InnoDB用于处理事务，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作可使用
• MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select可选择
• InnoDB支持外键
• MyISAM一般是非聚集索引，InnoDB是聚集索引

2、一张自增表里面总共有7条数据，删除了最后2条数据，重启MySQL数据库，又插入了一条数据，此时id是多少?

表类型如果是MyISAM，那么id就是8

表类型如果是InnoDB，那么id就是6

InnoDB表只会把自增的主键的最大Id记录在内存中，所以重启之后会导致最大id丢失

3、如何获取数据库版本

使用select Version（）获取当前MySQL数据版本

4、说一下ACID是什么?（☆☆☆☆☆）

Atomicity(原子性)：一个事务（transcation)中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback0到事务开始前的状态，就像这个事务从来没有执行过一样。即：事务不可分割、不可约简

Consisentecy（一致性):在事务开始之前和结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有预设约束、触发器、级联回滚等

Isolation(隔离性)：数据库允许多个并发事务同时对数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于较差执行而导致数据的不一致。事务隔离级别可分为不同级别，包括：读未提交（Read Commited)，读提交（Read commited)，可重复读(repetable read)和串行化(Serializable)

Durability(持久性)：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

原子性（原理如何实现Undo日志）

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

持久性（原理:redo log）

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

（1）刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
（2）刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

隔离性(原理:锁机制，MVCC)---->数据库的隔离级别

并发的问题（脏读、不可重复读、幻读）---->如何解决

• 脏读:当前事务读到其他事务未提交的数据（脏数据）(READVIEW(
• 不可重复度:：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。(对READVIEW生成的时机进行控制)
• 幻读:在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了(加间隙锁-)

定义

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：
(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

实现隔离级别方式1：锁机制(具体参考锁）

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。
锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

实现隔离级别方式2：MVCC（具体参考MVCC）

MVCC：MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)

MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等；当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC；隐藏列的详细格式不再展开。

数据库的隔离级别

一致性(实现原理：终极目的所有的一切都是为了这点服务）

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

实现一致性的措施包括：

保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

小结(ACID）

下面总结一下ACID特性及其实现原理：

• 原子性：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的；实现主要基于undo log

• 持久性：保证事务提交后不会因为宕机等原因导致数据丢失；实现主要基于redo log

• 隔离性：保证事务执行尽可能不受其他事务影响；InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制、数据的隐藏列、undo log和类next-key lock机制

• 一致性：事务追求的最终目标，一致性的实现既需要数据库层面的保障，也需要应用层面的保障

说一下MySql的数据类型

MySQL支持多种类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串(字符)类型。

数值类型

浮点型

日期和时间类型

字符串类型

5、char和varcahr的区别是什么?

char(n)：固定长度类型，比如订阅char(10)，当你输入"abc"三个字符时候，他们展的空间是10个字节，其他为7个孔子杰

优点：**效率高；**缺点:占用空间;

varchar(n):可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度

所以，从空间上考虑varchar比较合适；从效率上考虑char比较合适

6、float和douvble的区别是什么

float最多可以存储8位的十进制数，并在内存中站4个字节

double最多可以存储16位的十进制数，并在内存中占8字节

7、MySQL的内连接、左连接、右连接有什么区别?

内连接是吧匹配的关联数据显示出来；

左连接是吧左边的表全部显示出来，右面的表显示出符合条件的数据

8、MySQL索引是怎么实现的?

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据

具体来说MySQL中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引是B+树实现的，B+树的搜索效率，可达到二分法的性能，找到数据区域后就找到了完整的数据结构

9、怎么验证MYSQL的索引是否满足需求

使用explain查看sql是如何执行查询语句的，从而分析你的索引是否满足需求

10、说一下事务的隔离界别

11、说一下MySql常用的引擎?

InnoDB引擎:

• 提供了对数据库acid事务的支持；**
• 提供了行级锁和外键的约束**
• 它的设计目的就是处理大数据容量的数据库系统；mysql运行的时候，InnoDB会在内存中建立缓冲池，用于缓冲数据和索引；但该索引不支持全文搜索，同时启动也比较慢，它是不会保存表行数的，所以当进行select count(*) from table指令的时候，需要扫描全表**
• 由于锁的粒度小，写操作是不会锁定全表的，所以在并发度较高的场景下使用会提升效率**

MyIASM引擎:

• 不会提供事务的支持，也不支持行级锁和外键
• 当执行插入和更新语句时，即使执行写操作的时候需要锁定这个表，所以会导致效率较低；
  • 不过和InnoDB不同的是，MyISAM保存了表的行数，于是select count(*) from table语句时，可以直接读取已经保存的值而不需要进行扫描全表

13、说一下乐观锁和悲观锁?

14、数据库中的锁

锁的粒度（行锁/表锁）

行级锁以及表级锁

在选择封锁粒度时，需要在锁开销和并发程度之间做一个权衡。（应该尽量只锁定需要修改的奶粉数据，而不是所有资源。锁定的数据量越少，发生锁挣用的可能就越小，系统的并发程度就越高。但是加锁需要消耗资源，锁的各种操作（包括获取锁，释放锁，以及检查锁状态）都会增加系统开销。因此封锁粒度越小，系统开销就越大）

普通的增删改是表锁，加入索引的增删改是行锁，执行查询是不假任何锁的

InnoDB行锁是通过给索引上的索引项加锁来实现的，
InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB讲使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

数据操作类型来分锁

读写锁和意向锁

• 读写锁

  排他锁:简称为X锁，又称为写锁

  ​ 共享锁:简写为S锁，又称读锁

  在存在行级锁和表级锁的情况下，事务T想要对表A加X锁，就需要先检查是否有其他事务对表A或者表A中的任意一行加了锁，那么就需要对表A中的每一行都加测一次，这是非常耗时的

• 意向锁

  ​ 在原来x/s锁之上引入IX/IS锁，IX/IS锁都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁或S锁。有以下两个规定

  • 一个事务在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁
  • 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁
    通过引入意向锁，事务T想要对表A加X锁，只需要先检测是否有其他事务对表A加了X锁/IX/S/IS锁。如果加了就表示有其他事务正在使用这个表或者表中某一行的锁，因此事务T加X锁失败。

悲观锁和乐观锁

悲观锁，它指的是对数据被外界（包括本系统当前的事务，以及来自外部系统的事务处理）修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制

乐观锁假设认为数据一般情况下不会造成冲突，所以只会在数据进行提交更新的时候，才会正式对数据地冲突与否进行检测，如果发现冲突了，则返回用户错误的信息，让用户决定如何去做

乐观锁的实现：使用版本号；当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是国企数据，实际就是并发控``制中的CAS理论

加锁语句

• 意向锁是Innodb自动加的，不需要加
• 对于UPDATE,DELETE和INSERT语句，Innodb会自动给设计数据集加排他锁（X)
• 对于普通SELECT语句，Innodb不会加任何锁
• 事务可以通过有以下语句显示给记录集加共享锁和排他锁（对于select加上锁,默认是不加任何锁的）

//共享锁
SELECT * FROM table_name where......LOCK IN SHARE MODE

//排他锁
SELECT * FROM table_name where .....for update

• 加共享锁，主要用于需要数据库一寸关系时来确认某行记录是否存在，并确保没有人对这个记录进行updarte或者DELETE操作
• 但如果当前十五需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，页应该使用for update方式来获得排他锁

数据库中锁的算法

• Record Lock：单个行记录上的锁
• Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
• Next-Lock key:Gap Lock+Record Lock ，锁定一个范围，并且锁定记录本身（这个主要是解决幻读的问题，在事务中 select * from for update,in share mode在REPEATBLE READ的情况下会加上间隙锁）

事务中的读（select）

快照读（不加锁）：MVCC版本控制协议（普通的select)

和锁没有关系，回去快照中读取
不加锁
多版本并发控制是MySQL的InnoDB的存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交度隔离级别总是读取最新的数据行，无需使用MVCC.可串行化隔离级别需要对所有读取的行都加锁，单纯使用MVCC无法实现

实现了对读的非阻塞，读不加锁，读写不冲突。缺点是每行记录都需要额外的存储空间，需要左更多行的维护和检查工作

MVCC并发版本控制的原理

在每条记录含有的隐藏列如下

• DB_TRX_ID:创建这条记录/最后一次更新这条记录的事务
• DB_ROLL_PTR:回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
• DB_ROLL_ID:隐含的自增ID,如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚集索引
• 另外每条记录的头信息(record header)里都有一个专门的bit来表示当前记录是否已经被删除

记录的历史版本是放在专门的rollback segement里(undo log)

• UPDATE非主键语句的效果是；老记录被复制到rollback segment中形成undo log,DB_TRX_ID和DB_ROLL_PTR不动
• 新记录的DB_TRX_ID=当前事务ID,DB_ROLL_PTR指向捞记录形成的undo log
• 这样就能通过DB_ROLL_PTR找到这条记录的历史版本；如果对同一行记录执行连续的update操作，新记录与undo log会组成一个链表，遍历这个链表可以看到这条记录的变迁。

MySQL的一致性读，是通过一个叫做read view的结构来实现的

ReadView中的保存的是当前不应该被本事无看到的其他事务Id列表

用MVCC这一手段可以同时实现RR与RC隔离级别

它们的不同之处在于:

• RR：read view是在first touch read时创建的，也就是执行事务中的第一条SELECT语句的瞬间，后续所有的SELECT都是复用这个read view，所以能保证每次读取的一致性（可重复读的语义）

• RC：每次读取，都会创建一个新的read view。这样就能读取到其他事务已经COMMIT的内容。

所以对于InnoDB来说，RR虽然比RC隔离级别高，但是开销反而相对少。

只采用MVCC的话，无论在哪种隔离级别下，都不能解决 幻读的问题

事务中的当前读（加锁读）—如何解决幻读的问题

有些情况下，用户需要显示的对数据库读取操作进行进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT 的只读操作。InnoDB存储引擎对于SELECT 语句支持两种一致性的锁定读:

• SELECT…FOR UPDATE(对读取的行记录加X锁，其他事务不能对已经锁定的行加任何锁你）
• SELECT…LOCK IN SHARE MODE*（对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是不能加X）

• 在REPETABLE READ中，若采用锁定读，则会采用间隙锁的算法，这样就可以避免幻读的问题(禁止对当前数据或者查询范围内的数据的修改)
• • 在Read Commited中，若采用锁定读，则会采用行锁的算法，这样就可以不能解决幻读的问题

数据库死锁

死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。

产生死锁原因

• 系统资源不足
• 进程运行推进顺序不合适
• 资源分配不当

产生死锁的必要条件

• 互斥条件：一个资源每次只能被一个进程使用
• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
• 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
• 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系

如何最大限度的降低死锁

• 按同一顺序访问对象。
• 避免事务中的用户交互
• 保持事务简短并在一个批处理中
• 使用低隔离级别

常见死锁情况与解决方法

1、事务之间对资源访问顺序的交替

出现原因

一个用户A 访问表A（锁住了表A），然后又访问表B；另一个用户B 访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

2、并发修改同一记录

出现原因

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁由于比较隐蔽，但在稍大点的项目中经常发生。

解决方法

使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。

3、索引不当导致全表扫描

出现原因

如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

15、索引底层数据结构

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘IO消耗，相对于内存存取，IO存取的消耗要搞几个数量级，所以评价一个数据结构作为索引的优劣 最重要的指标就是在查找过程中磁盘IO操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘IO的存取次数

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引等等

详细介绍索引等数据结构

b树与b+树的区别

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数，对于树来说，IO次数就是树的高度，而矮胖就收b树的特征之一，它的每个节点最多包含m个孩子，m称为b树的阶，m的代销取决于磁盘页的大小

b树

• 关键字集合分布在整颗树中。
• 任何一个关键字出现且只出现在一个节点中。
• 搜索有可能在非叶子节点结束。
• 其搜索性能等价于在关键字集合内做一次二分查找。
• B树在插入删除新的数据记录会破坏B-Tree的性质，因为在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质。

B+树

关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）

所有的叶子节点中包含了全部关键字的信息，机制向含这些关键字记录的指针，且叶子节点本身依赖关键字的代销自小到大的顺序连接

通常在b+树上有两个头指针，一个是指向跟节点，一个指向关键字最小的页子节点

• 非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层。
• 更适合文件索引系统。
  

B+树与红黑树比较

• 更少的查找次数

  平衡树的操作时间复杂度和树高h相比，o(h)=O(LOGdn)，其中d为每个节点的出度，红黑树的出度为2，而B+树的初度一般都非常大，所以红黑树的树高h很明显比B+树大非常多，查找次数也就更多

• 利用磁盘预读特性

为了减少磁盘I/O操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻到，并且只需要很短的旋转时间，速度会非常快

操作系统一般将内存和磁盘分隔成固定大写哦的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次I/O操作就能完全载入一个节点。并且可以利用预读特性，相邻的节点也能够被预先载入

B+树相比于B树的查询优势

• B+树空间利用率更高，可减少IO次数

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储子啊磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。e而因为B+树的内部节点只是作为索引使用，而不是像B树那样每个节点都需要存储硬盘指针。也就是说，B+树中每个非叶子节点中没有指向某个关键字具体信息的指针，所以每一个节点可以存放更多的关键字数量，即一次性读入内存中所需要查找的关键字也就越多，减少了IO操作，

eg假设磁盘中的么偶一个盘快容纳16字节，而一个关键字2字节，一个关键字具体信息指针2字节，一颗9阶B树（一个节点最多8个关节子)的内部节点需要2个盘快。而B+树内部节点只需要一个盘快，当需要把内部节点读入内存的时候，B树就比B+树多一次盘块查号时间

• 增删文件（节点）时，效率更高

  因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率，基于范围查询更好

• B+树的查询效率更稳定

  因为B+树的每次查找过程中，都需要遍历从根节点到叶子节点的某条路径。所有关键字的查询路径长度相同。导致每一次查询的效率相当

B+树索引与hash索引的比较

B+树索引与哈希索引的明显区别是

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法就能找到对应的键值；当然了，这个前提是：键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后根据链表往后扫描，直到找到相应的数据

如果是范围查询检索，原先是有序的键值，经过哈希算法，有可能编程不连续的了，就没办法再利用哈希完成范围查询检索；

同理，哈希索引页没办法利用索引完成排序，以及like 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询（

哈希索引也不支持多了联合索引的最左匹配原则

B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

MySql索引的实现(不同存储引擎的实现机制）

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引的实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

同样也是一棵B+树，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，上图为定义在Col3上的一个辅助索引：

17、MySQL索引有哪些？

为什么要有索引?

https://www.cnblogs.com/aspwebchh/p/6652855.html

主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

每次给字段建一个新索引， 字段中的数据就会被复制一份出来， 用于生成索引。 因此， 给表添加索引，会增加表的体积， 占用磁盘存储空间。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据， 而通过非聚集索引可以查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，如下图

MySQL有哪些索引

 普通索引： MySQL 中基本索引类型， 没有什么限制， 允许在定义索引的列中插入重复值和空值， 纯粹为了查询数据更快一 点。
唯一索引： 索引列中的值必须是唯一的， 但是允许为空值。
 主键索引： 是一种特殊的唯一索引， 不允许有空值。 （主键约束， 就是一个主键索引）
 组合索引： 在表中的多个字段组合上创建的索引， 只有在查询条件中使用了这些字段的左边字段时， 索引才会被使用， 使用组合索引时遵循最左前缀集合。 例如， 这里由 id、name 和 age3 个字段构成的索引， 索引行中就按 id/name/age 的顺序存放， 索引可以索引下面字段组合(id， name， age)、 (id， name)或者(id)。 如果要查询的字段不构成索引最左面的前缀， 那么就不会是用索引， 比如， age 或者（name， age） 组合就不会使用索引查询。
 全文索引： 全文索引， 只有在 MyISAM 引擎上才能使用， 只能在 CHAR,VARCHAR,TEXT类型字段上使用全文索引， 介绍了要求， 说说什么是全文索引， 就是在一堆文字中， 通过其中的某个关键字等， 就能找到该字段所属的记录行。 一般开发中， 不贵用到全文索引， 因为其占用很大的物理空间和降低了记录修改性， 故较为少用。

为什么有联合索引?

• 减 少 开 销 。 建 一 个 联 合 索 引 (col1,col2,col3) ， 实 际 相 当 于 建 了
  (col1),(col1,col2),(col1,col2,col3)三个索引。 每多一个索引， 都会增加写操作的开销和磁
  盘空间的开销。 对于大量数据的表， 使用联合索引会大大的减少开销！

• 覆盖索引。 对联合索引(col1,col2,col3)， 如果有如下的 sql: select col1,col2,col3 from test
  where col1=1 and col2=2。 那么 MySQL 可以直接通过遍历索引取得数据， 而无需回表，
  这减少了很多的随机 io 操作。 减少 io 操作， 特别的随机 io 其实是 dba 主要的优化策略。
  所以， 在真正的实际应用中， 覆盖索引是主要的提升性能的优化手段之一。

• 效率高。 索引列越多， 通过索引筛选出的数据越少。 有 1000W 条数据的表， 有如下
  sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出
  10%的数据， 如果只有单值索引， 那么通过该索引能筛选出 1000W10%=100w 条数据，
  然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据， 然后再排序， 再

索引有呢些优点

• 大大减少了服务器需要扫描的数据行数
• 帮助服务器避免排序和分组，以及避免创建临时表（B+树索引是有序的，可以用于Prder bY和GROP BY操作。临时表主要是子排序和分组过程中创建，因为不需要排序和分组，也就不需要创建临时表）
• 将随机IO变为顺序IO(B+索引是有序的，会将相邻的数据都存储在一起)

索引的缺点

 创建索引和维护索引要耗费时间,而且时间随着数据量的增加而增大
 索引需要占用物理空间,如果要建立聚簇索引,所需要的空间会更大
 在对表中的数据进行增加删除和修改时需要耗费较多的时间,因为索引也要动态地维护

18、聚集索引和非聚集索引

innodb中索引的组织形式是B+树，非叶子节点存放key，叶子节点存key+data，叶子节点之间用指针连通

聚集索引则在data存放数据页

非聚集索引：data中存放的是主键的值，得到主键后还要在聚集索引上再查询一次。
`在效率方面最好使用聚集索引，并给表设定唯一主键。在数据索引的存储有序的情况下，可以大大提高效率。

聚集索引

辅助索引

19、MySQL优化

**集合视频 MySQL技术内幕 深入浅出MySQK**可以从

索引 查询 建表 数据库服务器 应用优化

1、索引优化

• 建立聚集索引

  首先聚合索引是提高查询速度最有效的手段。基于聚合索引的性质，我们可以了解到，数据库的物理存储顺序是按照聚合索引顺序排列的，通过聚合索引的B+树，我们可以想迅速的查找到任何一行的全部信息

• 常查询数据建立索引或者组合索引

• 最左前缀原则

  建立组合索引优化查询语句时，一定要考虑到最左前缀原则，否则你的索引建立的可以说毫无意义。

• 不要建立无意义的索引

​ 对于查询次数很少的语句中的字段的索引、备注描述和大字段的索引等不需要建立无意义的索引

2、查询优化

使用Explain进行分析

Explain用来分析SELECT查询语句，开发人员可以通过分析EXPLAUIN结果来优化查询语句

select_type 查询类型，有简单查询，联合查询，子查询等

key使用的索引

rows 扫描的行数

优化数据访问

1. 减少请求的数据量

   • 只返回必要的列：最好不要使用SELECT *语句
   • 只返回必要的行：使用LIMIT语句来限制返回的数据
   • 缓存重要的查询数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会非常明显。

2. 减少服务器端扫描的行数

   • 最有效的方式是使用索引来覆盖查询

3. 重构查询方式

   • 切分大查询

     ​ 一个大查询如果一次性执行的话，可能锁住很多数据，沾满整个事务日志，耗尽系统资源，阻塞很多小的但很重要的查询

• 分解大连接查询

  ​ 将一个大连接查询分解成对每一个表进行一次单表查询，然后在用用程序中进行关联，这样做的好处有：让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其他表的查询缓存依然可以使用，分解成多个单表查询，这些单表查询的缓存结果更可能被其他查询使用到，从而减少冗余记录的查询

3、SQL语句的优化

insert

若从同一客户插入很多行，应尽量使用多个值表的Insert语句，这种方式大大缩减客户端与数据库之间的连接

order by

• 尽量减少额外排序，通过索引直接返回有序数据
• where条件和order by使用相同的索引，并且Order byy顺序和索引顺序相同
• 保证全部排序字段排序一致性（都是升序或降序）

OR语句

如果要利用索引，则OR之间的每个条件列都必须要用到索引，如果没有索引，则应该增加索引

4、优化MySQLServer

对常见参数的优化

5、应用优化

• 使用连接池

• 减少对MySQL的访问

  • 避免对同一数据做重复检索
  • 使用查询缓存
  • 增加Cache层

• 负载均衡

  ​ 负载均衡是实际应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布在不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。

6、建表时优化

• 优化表的数据类型
• 通过拆分提高表的访问效率
• 使用中间表提高统计查询速度

20、水平切分和垂直切分

• 水平切分又称为Sharidung,它是将桶一个表中的记录拆分到多个结构相同的表中
• 垂直切分是将一张表按列切分为多个表，通常是按照列的关系密集程度进行切分，也可以利用垂直切分将经常被使用的列和不经常使用的列切分到不同的表中

切分存在问题

• 事务问题

  使用分布是事务来解决，比如XA接口

• 连接

  可以将原来的连接问题分解成多表查询，然后再用户程序中进行连接

• ID唯一性

  使用全局唯一ID

  为每个分片指定一个ID范围

  分布式ID生成器

21、主从复制原理、作用、实现

原理

• 1、主服务器master将改变的数记录在本地而二进制日志中，该过程称之为：二进制日志文件
• 2、从服务器将主服务器的binary log拷贝到自己的Relay log(中继日志问文件）中
• 3、从服务器重做中继日志中的值日，把更改应用到自己的数据库上，以达到数据的最终一致性

22、数据的热备份和冷备份

冷备份

冷备份（off,慢，时间点上恢复)

冷备份发生在数据库正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言，冷备份是最快和最安全的方法

冷备份的优点

• 是非常快速的反方（只需要拷贝文件）
• 容易归档（简单拷贝即可）
• 容易恢复到某个时间带你（只需要将文件再拷贝回去OP）
• 能与归档方法相结合，作数据库最新状态的恢复
• 低维护读，高度安全

冷备份的缺点

• 单独使用时，只能提供到“某一时间点”上的恢复
• 在实施备份的全过程中，数据库必须要备份而不能做其他工作，也就是说，在冷备份的过程中，数据库必须是关闭的状态’
• 若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢
• 不能按表或按用户回复

热备份

热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的Sql语句

热备份的优点

• 可在空表或数据文件级的备份，备份时间短
• 备份时数据库仍可使用
• 可以达到秒级的恢复（恢复到某一时间点）
• 可对几乎所有数据库实体作恢复
• 恢复是快速的，在大多数情况下载数据库仍工作时恢复

热备份的缺点

• 不能出错，否则后果严重
• 若热备份不成功，所得结果不可用于时间点的恢复
• 因难于维护，所以要特别小心，不允许以失败而告终

23、数据库视图和存储过程

• 视图是虚拟的表，它只包含使用时动态检索数据的查询
• 存储是为方便以后使用而保存的一条或多条MySQL语句的集合，可以将其视为批文件

存储过程

24、数据库操作（加锁，加索引）

创建索引

• 创建表时指定索引

  CREATE TABLE student(
  
     id int(6) not null，
     
     primary key(id)
    
     
     
  )；
  

• 添加索引

  • 添加主键索引

    ALTER TABLE `table_name` ADD PRIMARY KEY(`column`)
    

  • 添加唯一索引

    ALTER TABLE `table_name` ADD UNIQUE('COLUMN')
    

  • 添加普通索引

    ALTER TABLE `table_name` ADD INDEX index_name (`column`)
    

  • 添加全文索引

    ALTER TABLE `table_name` ADD FULLTEXT(`column`)
    

  • 添加多列索引

          ```sql
    

  ALTER TABLE table_name ADD INDEX index name(column1,column2,column3)
  ```

加锁

• 意向锁是Innodb自动加的，不需要加
• 对于UPDATE,DELETE和INSERT语句，Innodb会自动给设计数据集加排他锁（X)
• 对于普通SELECT语句，Innodb不会加任何锁
• 事务可以通过有以下语句显示给记录集加共享锁和排他锁

//共享锁
SELECT * FROM table_name where......LOCK IN SHARE MODE

//排他锁
SELECT * FROM table_name where .....for update

• 加共享锁，主要用于需要数据库一寸关系时来确认某行记录是否存在，并确保没有人对这个记录进行updarte或者DELETE操作
• 但如果当前十五需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，页应该使用for update方式来获得排他锁

Innodb解决死锁

实际上咋Innodb发现死锁之后，会计算出两个事物各自插入，更新或者删除的数据来判定两个事物的大小。也就是说那个事物锁改变的记录条数越多，在死锁中就越不会被回滚掉

Cont(1)、count(*)与count(字段）的区别

• count（1）和count（）之间没有区别，因为count（）count（1）都不会去过滤空值，

• 但count（列名）就有区别了，因为count（列名）会去过滤空值。

列名为主键，count(列名)会比count(1)快
列名不为主键，count(1)会比count(列名)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（）
如果有主键，则 select count（主键）的执行效率是最优的
如果表只有一个字段，则 select count（）最优