InnoDB和MyISAM存储引擎的区别

InnoDB使用的是行锁，MyISAM使用的是表锁；
InnoDB支持事务和外键，MyISAM不支持；
InnoDB采用的是聚簇索引，即索引结构的叶节点的数据域，存放的是实际的数据记录。而MyISAM采用的是非聚簇索引，存放的是数据记录的地址。
MyISAM索引和数据是分开的，而且索引是压缩的。而InnoDB索引和数据是紧密捆绑在一起的，无法压缩。所以InnoDB的体积要比MyISAM庞大。
InnoDB并不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描整个表来计算有多少行，但是MyISAM只要简单读出保存好的行数即可。注意count(*)语句包含where条件时，两个表的操作是一样的。

什么叫做意向锁

申请意向锁的动作是数据库完成的，也就是说，事务A申请行锁时，数据库会自动先开始申请意向锁。

意向锁的作用就是协调行锁和表锁之间的关系，将行锁从另一个角度提到到表锁的等级，与表锁进行判断。

举个例子：

事务A申请行锁；
事务B申请表锁，若申请生成就可以操作每一行数据，显然是与事务A冲突；
为了解决冲突，只能将事务B阻塞。

但是步骤二中，事务B如何知道自己需要阻塞？若是从头遍历整个表，效率太低。于是引入了意向锁，事务A在申请行锁的时候先自动加上意向锁，这样事务B就会直接被阻塞。

mysql的索引结构

索引是帮助mysql高效获取数据的有序数据结构。数据表中的数据看上去是连续的，但是在磁盘中是随机存储的，所以在查找表中数据时，操作系统会进行磁道旋转和磁头寻道，CPU将数据从硬盘读取大内存中，在内存中进行交互。

Mysql的索引结构是B+Tree，即横向树，减少树的高度，从而减少IO操作。

非叶节点只存储索引值，不存储data域（数据值），这样非叶节点可以包含更多的索引值；
叶节点的指针为空；
叶节点中包含顺序访问指针，可以提高区间访问性能；

聚簇索引和非聚簇索引

MyISAM是非聚簇索引，即叶节点中存储的是文件指针；
InnoDB是聚簇索引，B+Tree叶节点中存储的是实际记录；

InnoDB中的聚簇索引和二级索引（普通索引）--（引申：使用主键查询和唯一索引查询哪个速度快？）

mysql的InnoDB底层是采取B+Tree实现的，叶子节点data域中存储的是实际的数据行，因为无法将数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

如果mysql没有定义主键，InnoDB会选择一个唯一非空索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键作为聚簇索引。

二级索引（非主键索引）叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行时，需要先获取二级索引叶子节点中的主键值，在根据这个主键值去聚簇索引中寻找对应的行，这里会做重复的工作。

需要注意，二级索引存储的是主键值，若是表上索引列较多的话，主键应当尽可能的小。

虽然二级索引使用主键值当做指针会让二级索引占用更多的空间，换来的好处是InnoDB在移动行时无需更新二级索引中的这个指针。

为什么MyISAM比InnoDB查询速度快

InnoDB在做select的时候，要维护的东西比MyISAM引擎多：

MyISAM与IO交互少：InnoDB要缓存数据块，而MyISAM只缓存索引，MyISAM与IO交互的次数比较少。
MyISAM寻址快：InnoDB寻址要映射到块，在到行，MyISAM记录的是文件OFFSET（偏移量），定位比InooDB快；
InnoDB需要维护MVCC的一致。

为什么InnoDB表一定要有主键，且推荐主键使用整型的自增主键

InnoDB数据本身就是一个B+Tree的索引文件，若不显示指定主键，InnoDB也会默认生成一个主键列；

若是主键的值是顺序的，InnoDB会把每一条记录都存储在上一条记录后面。当达到页的最大填充因子时，下一条记录就会写入新的页中。

若不使用自增型的整型主键，若使用UUID

UUID比较长，浪费空间；
UUID为字符串，比较速度慢；
被写满已经被刷新到磁盘的页可能会被重新读取，会造成大量IO操作。
B+Tree是有序的，而UUID是无序的，若插入数据所在节点已经存满的情况下，会导致节点的分裂；
由于频繁的页分裂（节点分裂），页就会变得稀疏并不规则地填充，所以最终数据会有碎片。

总结：使用UUID作为主键插入数据时不仅花费的时间更长，而且索引占用的空间也更大。这一方面是由于主键字段更长，另一方面毫无疑问是由于页分裂和碎片导致的。

自增主键存在什么问题？

在高并发情况下，在InnoDB中按主键顺序插入可能会造成明显的争用。由于并发插入时存在间隙锁的竞争和AUTO_INCREMENT锁机制。可能存在性能上的瓶颈。
如果遇到这个问题，则需要重新设计表或应用，或更改innodb_autoinc_lock_mode的配置。

请简述AUTO_INCREMNET锁机制

在mysql中有三种插入模式。

简单插入：实现可以预测要插入的行数，例如常用的insert、insert ... value(),value()。
批量插入：实现不知道要插入的函数，比如insert...select。
混合模式插入：一些特殊的简单插入，用于指定部分（但不是全部）新行的自增值。

mysql在5.1.2版本之前，binlog只有statement模式，为了主从复制的安全性，所有的insert语句，都会在语句的末尾加上表锁（不是事务的末尾）。会影响并发性。

mysql5.2版本后，默认innodb_autoinc_lock_mode = 1 （“ 连续 ”锁定模式）对于批量插入（bulk inserts），因为不能确定数量，会将表锁住，simple inserts和mixed-mode inserts会分析语句，获取插入的数量，预分配多个，不会加表锁。

mysql8.0版本后，因为binlog默认使用row模式（基于行的复制），所以默认innodb_autoinc_lock_mode = 2。这种模式下，插入的行是来一个分配一个，不糊分配多个。并发性能最好

说一下MVCC

MVCC采用无锁的方式解决了读-写冲突，即实现了快照度。

MVCC依赖的是undo log与每行隐藏的行版本号+回滚指针实现的。事务开始时，会将系统版本号作为行版本号。当修改行记录时，会undo log日志，并通过回滚指针相连，形成版本链。根据事务ID和undo log中的行版本ID完成事务的回滚或返回某一时刻的快照。

说了这么多年的MVCC，到底什么是MVCC？？？

数据库的三大范式

第一范式：（保证原子性）所有域应该是原子性的，即数据库表中每一行都是不可分割的原子数据项。
第二范式：（完全依赖）在第一范式基础上，实体属性要完全依赖主关键字；
第三范式：在第二范式的基础上，消除传递依赖；

事务的ACID

原子性：原子性是指事务是一个不可分割的工作单位，要么全部成功，要么全部失败；
一致性：事务前后数据的完整性必须保持一致；
隔离性：多个用户并发访问数据库时，数据库为每一个用户开启事务，不能被其他事务的操作所干扰。
持久性：一个事务一旦被提交，那么它对数据库的改变是永久有效的。

慢SQL优化

开启慢查询日志，设置超过几秒为慢SQL，抓取慢SQL；
通过explain对慢SQL分析；
show profile查询SQL语句执行的资源消耗情况；
对数据库服务器参数调优；

explain查询执行计划

explain一般关注的参数：

id：select的执行顺序；
table：显示这一行的数据是关于哪张表的；
type：显示使用了何种索引类型，最好到最差的连接类型为const、eq_reg、ref、range、index和All
rows：显示需要扫描的行数；
key：使用的索引名；

请详细描述一下type类型：

ALL：按照行顺序对全表扫描；
index：按照索引顺序对全表进行扫描，最大优点是避免了排序，最大缺点是要承担按索引顺序读取整个表的开销；
range：有限制的索引扫描，一般对索引列使用范围查询；
ref：索引扫描和索引查找之间，一般是非唯一索引或唯一索引的非唯一性前缀；
eq_ref：多表查询时使用主键或唯一索引查询；
const/system：单表查询使用主键或唯一索引查询；

联合索引的底层存储结构

联合索引.png

图来自《高性能Mysql-第三版》；联合索引key(last_name,first_name,dob)

联合索引像字符串排序一样，先按照首字符，在首字符相同的情况下，在按照后面的字符排序；

索引优化技巧

对查询进行优化，尽量避免全表扫描，首先考虑在where即order by涉及的列上建立索引；
在varchar字段上建立索引时，必须制定索引长度，没必要对全字段建立索引，根据实际文本区分决定索引长度即可。
应尽量避免在where字句中使用!=；
尽量避免在where字句对字段进行null值判断(where num is null)；
模糊查询时，尽量避免以%开头；
尽量避免在where字句中对字段进行函数操作；

Mysql的隔离级别以及存在的问题

读未提交，存在脏读情况；
读已提交，存在不可重复读情况；
可重复读，存在幻读情况；
序列化，将快照读升级为当前读（使用共享锁），实现读写分离。

注意：读已提交和可重复读都使用了MVCC技术，但是读已提交时读取的最新快照；可重复读是读取的唯一快照。

如何预防间隙锁引起的死锁

以固定的的顺序访问表和行，将两个事务sql顺序调整为一致，可以避免死锁；
大事务拆小，大事务更容易死锁，如果业务允许，将大事务拆小。
降低隔离级别，如果业务允许，将隔离级别调低也是较好的选择，比如隔离级别从可重读读调整到读已提交，可以避免很多因为间隙锁造成的死锁；
为表添加合适的索引，如果修改或删除时where条件没有索引，那么会锁全表。

order by字段到底要不要加入索引？

select * from table where a=num1 and b>num2 order c;
如果说可以对abc去建索引，那么如何建立这个索引？

答案：在ab上建立索引。对于order by字段加入索引本身这个问题，如果最终结果集是以order by字段为条件筛选的，将order by字段加入索引中，是可以走索引的。

（也就是说，当使用order by对某字段排序时，where条件中也存在这个字段，便可以考虑为这个字段加索引）

结论.png

exists和in的区别

exists子查询返回的是boolean类型，会对外表进行全表扫描，内表的查询会走索引，适合于内表大，而外表小的情况；
当in的条件少时，外表会走索引，当in条件很多时，外表会全表扫描。适合于内表小而外表大的情况。

sql语句中，推荐大量使用join吗？

在阿里巴巴开发规范中，超过三个表禁止join。

mysql连接和断开都是很轻量级，返回一个小的查询结果方面很高效。所以运行多个小查询不是存在性能瓶颈，而数据库的优化器性能比较差，涉及到多个表的查询，往往得不到很好的查询计划，所以不推荐大量使用join，可以将多表连接拆分为多个单表连接。

而将join拆分为多个单表连接优点（join拆分可以利用in关键字代替）：

利于缓存：应用系统可以方便的缓存单笔查询对应的结果；
减少竞争：将查询分解后，执行单个查询可以减少锁的竞争；
易于扩展：更加容易对数据库进行拆分；
性能提升：使用in()来代替关联查询，可以让mysql按照ID顺序进行查询，这可能比随机的关联查询更加高效；
减少冗余：在应用层做关联查询，意味着某些记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。

Join和in的区别

先扫描外表，将外表的每一条记录传递给内表，内表通过索引来判断是否满足。

若in的条件值少的情况下，外表会走索引，当in条件多时，外表会进行全表扫描。

在外表全表扫描，且相同select结果下，in的效率会比join效率低，因为in子查询每次执行内部查询的时候都必须重新构造一个join结构，并行还需要完成析构过程。

RR级别为什么存在next-key锁

RR级别的next-key锁：如果where条件全部命中，则只会添加记录锁，如果部分命中或者全部没有命中，则会加Gap锁（间隙锁）。

其本身可以一定程度上解决幻读的问题，但是RR存在gap锁，我觉得跟底层的原因是为了兼容binlog的statement模式，在mysql5.0之前，binlog的模式只有statement模式。

binlog的statement模式：binlog记录的顺序是按照数据库事务的commit顺序为顺序的。

若不存在next-key的情况下：

1、事务a先delete id<6，然后在commit前；
2、事务b直接insert id=3，并且完成commit；
3、事务a进行commit；

此时binlog日志记录的顺序为：先insert id=3，然后delete id<6。同步到从库，最终的数据为0；但实际上主库的存在一条数据。

所以：为了保证主从数据的一致性，RR基本才会存在next-key锁。

21 数据库造成死锁的情况

Waiting for table metadata lock：执行DDL语句时，若此时数据库存在大事务或者有慢查询sql运行时，ddl语句会被阻塞，而ddl语句阻塞后，会阻塞该表的所有sql语句，造成mysql连接的大量等待。造成系统崩溃；
读写分离架构中，在事务中开启线程池去查询。这一个比较隐晦，且是代码易出现的情况，读写分离，主库一般做DML语句，从库做查询语句。【且该sql在90%情况下并不是慢sql，但是极少数情况下，查询出大量数据，send data步骤缓慢，造成慢sql】。问题是大量并发的慢sql占用了主库的数据库连接。造成死锁，引发数据库崩溃。
ON DUPLICATE KEY UPDATE(存在即更新，否则插入)在5.7.26版本中为了解决多个唯一索引场景下主从不一致的问题，引入了间隙锁，会造成死锁等待。详见：【精】MySql语法（6）— ON DUPLICATE KEY UPDATE不存在插入，存在即更新（死锁|性能杀手，慎用）

2. mysql底层

系列文章

mysql的一个update需要经历什么最终持久化到磁盘？

一条update语句持久化到磁盘要经历什么

update T set c = c+1 where id = 2;

执行器先调用存储引擎的接口获取“id=2”的数据行。如果这一行所在的数据页在内存中，则存储引擎直接返回给执行器；否则需要存储引擎先去磁盘中获取数据，读取到内存中，然后再返回。
执行器拿到存储引擎返回的这行数据，对其进行更新操作，将c的值加+1，得到新的数据，在调用存储引擎接口，写入这行数据。
存储引擎收到执行器写入的这行数据的新结果，先将这条更新记录保存在内存中，并将这条更新记录写入redo log，更新redo log的状态为prepare，随后向执行器返回结果。
执行器知道存储引擎已经将这条更新记录成功写入redo log后，开始将这条更新记录写入binlog。
redo log 调用fsync写入磁盘。
binlog调用fsync写入磁盘。
在执行器写入binlog成功后，存储引擎将redo log的状态更新为commit，更新完成。

image.png

WAL技术是什么，mysql为什么实现WAL

mysql的一个update需要经历什么最终持久化到磁盘？

在修改数据时，除了修改Buffer Pool中的数据，还会在redo log Buffer(内存)记录这次操作。当事务提交时，会调用fsync对redo log（磁盘）进行刷盘。重启时可以读取磁盘上的redo log中的数据，对数据库进行恢复。redo log采用的是WAL技术（Write-ahead logging，预写式日志）。所有修改先写入日志（redo log），在更新Buffer Pool，保证数据不会因为mysql宕机而丢失。

redo log和undo log之间的区别

redo log保证了WAL技术下，系统宕机后数据安全恢复，保证mysql的持久性。
undo log保证了一些变更执行到一半无法完成时，可以根据undo log恢复到变更之前的状态，保证事务的原子性。

WAL更新内存后刷入磁盘，若系统宕机如何保证数据不丢失

使用redo log日志系统解决，将精简的关键信息存储到磁盘的redo log文件，以便崩溃会恢复

redo log为什么采用两阶段提交

为了保证redo log和binlog数据一致性。

若不采用两阶段提交：

先写redo log在写binlog

如果引擎写完redo log后，bin log还没有写。异常重启。主库使用redo log 日志将数据恢复。但binlog没有记录这个语句，那么从库根据binlog同步数据时依旧没有这条语句，造成了主从库的数据不一致性；

先写binlog在写redo log

写完binlog后异常重启，因为redo log没有些，主库恢复后没有这条事务。但是由于binlog中有这条记录，从库根据binlog日志同步数据时，也会有这条事务。依旧导致主从不一致。

redo log和binlog的区别

redo log是InnoDB存储引擎层面，而binlog是mysql server层面，所有存储引擎均可使用；
redo log是InnoDB为了解决crash safe（系统奔溃后恢复），而binlog是定期存档，重要的作用是支持主从同步。
redo log是物理日志，记录的是“在某个数据上做了什么修改”（修改的结果是什么），binlog是逻辑日志，记录的是这个语句的原始逻辑（操作语句是什么）。
redo log是循环写，空间满时就会发生写覆盖；binlog是追加写，不会覆盖。

注：虽然redo log是物理日志，但是它并不是直接存储修改后的行数据本身（如果这样实现就更直接将数据写入磁盘没有区别了，也提高不了写数据效率的目的），实际上redo log中存储的内容格式：“xx表空间中yy数据页zz偏移量做了ww更新”。即只记录更新语句的简要信息，减少了写磁盘的数据量。

redo log为什么可以解决crash-safe而binlog不可以

redo log：是一个固定大小，“循环写”的日志文件，记录物理日志；
binlog：是一个无限大小，“追加写”的日志文件，记录的是逻辑日志；

redo log只会记录未刷盘的日志，已经刷入磁盘的数据都会从redo log这个固定大小的日志文件里删除；binlog是追加日志，保存的是全量的日志。

当数据库crash崩溃后，想要恢复：未刷盘但已经写入redo log和binlog的数据到内存时，binlog是无法实现的。虽然binlog拥有全量日志，但是没有标志让InnoDB判断哪些数据已经刷盘。

但redo log不一样，只要写入磁盘的数据，都会从redo log中抹除，数据库重启后，直接将redo log的数据恢复到内存。

写磁盘的flush操作如何实现

InnoDB执行update更新操作是采用的“先写日志，在写磁盘”的策略。更新后的行数据本身先缓存在内存中，直将缩略的关键信息写入到redo log磁盘。但缓存在内存中的数据最终总是要写入到磁盘，这个操作叫做flush。

当InnoDB的redo log写满时；
当系统的内存不足时；
当mysql认为系统“空闲”时；
当mysql正常关闭时；

一个select查询语句的执行流程？

select执行流程.png

连接器：建立连接，验证用户名密码，获取权限列表；
缓存：命中缓存直接返回，否则继续执行；
分析器：词法分析（识别sql字符串分别是什么，代表什么）语法分析（分析sql语句是否合法）语义分析（检查表、列是否存在）
优化器：选择合适索引，优化sql执行，最终生成执行计划。
执行器：校验sql权限，调用存储引擎接口，返回结果集。

总体来说：分析器：知道做什么；优化器：知道怎么做；执行器：最终去执行。

执行器的执行流程

判断用户“对表t”有没有查询权限（刚开始建立连接已经查出所有权限），没有权限报错，有则进入下一步；
有权限就会打开表继续执行，执行器会根据表的引擎定义，去使用这个引擎提供的接口。
调用InnoDB引擎接口，判断这一行id是不是10，如果不是跳过，如果是则将这行存在结果集中。
调用引擎接口取“下一行”，重复相同的判断逻辑，直至取到最后一行。
执行器将上述遍历过程中所有满足条件的行组成结果集，并且返回给客户端。

对于表的操作权限验证是在哪里进行的？

执行器进行验证，为什么不在优化器进行验证：因为查询的表不一定是sql语句，比如触发器。此时优化器阶段并不知道，只能在执行阶段知道。

serer层和存储引擎层的作用

存储引擎层完成的是数据的存储和提取；
server完成的是：跨存储引擎的功能，例如存储过程。触发器，视图。

sql常见的错误：you have an error in your SQL syntax和列不存在分别是那一步抛出的。

you have an error in your SQL syntax是分析器的语法分析；
列不存在是分析器的语义分析；

3. tidb分布式数据库

官方文档——TiDB 乐观事务模型

1 tidb如何实现事务

在v3.0.8版本前，tidb为了支持分布式事务，采用乐观事务使用两阶段提交协议，流程如下简单如下：

TiDB 从当前要写入的数据中选择一个 Key 作为当前事务的 Primary Key。
TiDB 从 PD 获取所有数据的写入路由信息，并将所有的 Key 按照所有的路由进行分类。
tidb并发向所有涉及到的TiKV发起prewrite请求，TiKV收到prewrite数据后，检查数据版本是否存在或已过期，复合条件的数据会被加锁；
tiDB收到所有的prewrite响应且所有prewrite都成功。
TiDB 向 PD 获取第二个全局唯一递增版本号，定义为本次事务的 commit_ts。
TiDB 向 Primary Key 所在 TiKV 发起第二阶段提交。TiKV 收到 commit 操作后，检查数据合法性，清理 prewrite 阶段留下的锁。
TiDB 收到两阶段提交成功的信息。