1、几种主要的数据库引擎的区别

MyISAM读取操作速度很快，而且不占用大量的内存和存储资源，但是不支持事务和外键，也不支持行级锁，写操作会锁定整个表，效率便低了一些，最大的缺陷是崩溃后无法安全恢复，适合对事务完整性没有要求并以访问为主的应用

InnoDB是一个事务性引擎，支持行级锁和外键，提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎，而且在READ COMMITTED和REPEATABLE READ隔离级别下支持MVCC，即多版本并发控制，大大提高了事务的并发能力。

MVCC：

MVCC，即多版本并发控制，在概念上，通过在行记录上隐世的增加两个版本号字段，一个创建版本号和一个删除版本号。使用MVCC可以提高减少数据的加锁操作，大大提高了事务的并发能力。

2、事务的特征

原子性：事务是一个完整的操作，要么全部执行，要么都不执行

一致性：当事务完成时，数据必须处于一致状态

隔离性：对数据修改的所有并发事务都是彼此隔离的

持久性：事务一旦提交，对数据库数据的修改便是永久的

3、MySQL的隔离级别

串行化（Serializable）：可避免脏读、幻读和不可重复读

可重复读（Repeatable read）：可避免脏读和不可重复读

读已提交（Read committed）：可避免脏读

读未提交（read uncommitted）：最低级别，任何情况都无法保证

脏读

读取了另一个未提交事务中的数据

不可重复读和幻读

都是读取了另一个已提交事务中的数据，区别在于不可重复读重点在于更新和删除，幻读重点在于插入

在可重复读中，第一次读取数据时，就将这些数据加索，其他事务就无法修改这些数据，就实现可重复读了。但是无法锁住插入的数据，这个事务就发现莫名奇妙多了一个数据，这就是幻读。

4、锁的级别

共享锁：可以并发读取数据，但任何事务都不能获取排它锁，直到共享锁释放

排它锁：对数据A加上排它锁后，其他事务不能再对A加上任何类型的锁，获取排它锁的事务可以对数据进行读取和修改操作

5、锁的粒度

行级锁：粒度最小，并发度最高

页级锁：一次锁定一页，

表级锁：粒度大，并发度低

6、悲观锁和乐观锁

悲观锁：每次获取数据的时候都会认为别人会修改数据，所以每次拿数据时都加上锁

乐观锁：每次获取数据时都会认为别人不会修改数据，只是在提交数据更新的时候才会去检查在此期间有没有其他人去更新这个数据，如果发生冲突，则返回用户错误数据，让用户决定如何处理，一般实现乐观锁的方式就是记录数据版本号

7、explain详解

id：标识select查询的序列号，标识查询select子句的执行顺序

select_type：查询类型：simple（简单子查询）、primary（包含union或子查询，最外层的部分标记为primary）、subquery（查询中的子查询）、derived（派生表，从子查询派生出来的，位于from中的紫钗性能）

table：表示正在访问的表名，也可能是临时表或者union合并结果集

derivedN，表示使用了id为N的查询产生的衍生表

union n1,n2，n1,n2表示参与union的id

type：类型

all：全表扫描

index：全索引扫描

range：index的范围限制

index_subquery：在某些in查询使用此种类型，查询的是非唯一索引

unique_subquery：在某些in查询使用此种类型

index_merge：索引合并优化被使用了

ref_or_null：MySQL必须在初次查找的结果找到null条目，然后进行二次查找

ref：使用了非唯一性索引进行查找

eq_ref：唯一性索引

const：主键出现再where后面作为条件查询，MySQL优化器会把此次查询优化转化为一个常量

system：表中只有一行

possible_keys：显示可能用到的索引

keys：MySQL使用了哪个索引

rows：大致估算出找出所需的记录需要读取的行数

extra：包含了一些十分重要的额外信息

using filesort：说明MySQL无法利用索引进行排序，只能用排序算法进行排序，这会额外消耗资源，说明这个SQL需要优化，需要重新建立索引等

using temporay：建立了临时表保存中间结果，查询完之后又把临时表删除，出现这个说明sql需要优化

using index：表明当前查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表

using where：使用where进行条件过滤

using join buffer：使用了连接缓存

impossible where：where语句总是false

distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

重点：

type、possible_keys、key、rows、extra

8、索引原理

什么是索引

索引是一种数据结构，帮助我们快速检索数据库中的数据。

索引的优势

加快检索速度，减少I/O次数

索引的劣势

索引会占用存储空间，构建索引会降低数据表修改的效率

索引类型

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式

从应用层次来分：普通索引，唯一索引，复合索引

根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引

聚簇索引：叶子节点保存的就是数据节点

非聚簇索引：叶子节点保存的是指向对应数据块的指针

MyISAM和InnoDB存储引擎下索引有什么区别

MyISAM引擎采用B+tree作为索引结构，索引文件和数据文件是分离的，叶节点的data域保存的是数据记录的地址。

主键索引和辅助索引在结构上没有什么区别，只是主键索引要求key必须是唯一的，而辅助索引则可以重复

InnoDB引擎也采用B+tree作为索引结构，主键索引也就是聚簇索引中，叶节点的data域保存了完整的数据记录；而辅助索引的叶节点的data域保存了主键

9、如何优化MySQL

MySQL优化大致可以分为三部分：索引的优化、SQL语句优化和表的优化

索引优化可以遵循以下几个原则：

联合索引最左前缀匹配原则

尽量把字段长度小的列放在联合索引的最左侧（因为字段越小，一页存储的数据量越大，IO性能也就越好）

order by 有多个列排序的，应该建立联合索引

对于频繁的查询优先考虑使用覆盖索引

前导模糊查询不会使用索引，比如说Like '%aaa%'这种

负向条件不会使用索引，如!=，<>，not like，not in，not exists

索引应该建立在区分度比较高的字段上  一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*)

对于where子句中经常使用的列，最好设置索引

SQL语句优化，可以通过explain查看SQL的执行计划，优化语句原则可以有：

在where和order by涉及的列上建立合适的索引，避免全表扫描

任何查询都不要使用select * ，而是用具体的字段列表代替

多表连接时，尽量小表驱动大表，即小表join大表

用exists代替in

尽量避免在where字句中对字段进行函数操作

数据库表优化

表字段尽可能用not null

字段长度固定表查询会更快

将数据库大表按照时间或者一些标志拆分成小表

水平拆分：将记录散列到不同的表中，每次从分表查询

垂直拆分：将表中的大字段单独拆分到另一张表，形成一对一的关系

10、为什么索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？

B+tree是一种多路平衡查询树，节点是天然有序的，非叶子节点包含多个元素，不保存数据，只用来索引，叶子节点包含完整数据和带有指向下一个节点的指针，形成一个有序链表，有助于范围和顺序查找。因为非叶子节点不保存数据，所以同样大小的磁盘页可以容纳更多的元素，同样能数据量的情况下，B+tree相比B-tree高度更低，因此查询时IO会更少

B-tree不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

Hash索引底层是基于哈希表，就是以key-value存储数据的结构，多个数据在存储关系上是没有任何顺序关系的。只适合等值查询，不适合范围查询，而且也无法利用索引完成排序，不支持联合索引的最左匹配原则，如果有大量重复键值的情况下，哈希索引效率会很低，因为存在哈希碰撞。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

11、主键索引查询只会查一次，而非主键索引需要回表查询多次。是所有情况都是这样的吗？非主键索引一定会查询多次吗？

如果我们使用了覆盖索引，也就是当一条查询语句符合覆盖索引条件时，MySQL只需要通过查询索引就可以返回需要的数据，避免了查到索引后再返回表操作，减少I/O提高效率

12、联合索引最左前缀匹配原则

在创建多列的联合索引下，MySQL索引查询会遵循最左前缀匹配原则，即检索数据时从联合索引的最左边开始匹配。例如，当创建了一个联合索引（key1,key2,key3）相当于创建了（key1），（key1,key2），（key1,key2,key3）三个索引

13、MySQL5.6中对索引做了哪些优化

索引下推技术

例如有索引（key1，key2）,SQL语句中where key1 = 'XXX' and key2 like '%XXX%'

如果没有使用索引下推技术，MySQL会通过key1 = 'XXX'从存储引擎返回对应的数据至MySQL服务端，服务端再基于key2 like 判断是否符合条件。

如果使用了索引下推技术，MySQL首先返回key1='XXX'的索引，再根据key2 like 判断索引是否符合条件，如果符合则通过索引定位数据，如果不符合则直接reject掉。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

14、那什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引 

2、计算全表扫描的代价 

3、计算使用不同索引执行查询的代价 

4、对比各种执行方案的代价，找出成本最低的那一个

15、红黑树的五个性质

节点是黑色或者红色

根节点是黑色

所有的叶子节点都是黑色，叶子是nil节点

每个红色节点，必须有两个黑色的子节点

从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点

16、MySQL一条查询语句是如何执行的

MySQL的基本架构分为两个层次：server层和存储引擎层

其中server层包括：连接器、查询缓存、分析器、优化器、执行器

存储引擎层负责数据的存储和读取

连接器负责与客户端建立链接、权限验证和管理链接

当向server发送sql请求时，如果开启了查询缓存，则去查询缓存中看是否有，如果有则直接返回，否则再走分析器进行词法分析和语法分析

分析器工作完成后，将语句传递到优化器进行优化，确定优化执行方案，优化器会根据自己的优化算法选择效率最好的一个方案

之后就会交给执行器进行执行，执行之前还会进行权限验证，然后调用数据库存储引擎接口，返回引擎执行结果