plsql 存储过程插入语句慢_《MySql体系结构与存储引擎》面试腾讯前总结

Mysql数据库能够在几乎所有操作系统上运行，包括linux和windows。Mysql实例表现为单进程多进程，与sql server类似，但oracle是多进程。

Mysql启动如果发现参数对会使用默认参数启动；而且会依次从多个配置文件读参数，以最后一个配置文件为准。

Mysql架构。数据库是文件集合，而要存储数据不能直接操作文件，因为你不懂，只能通过实例来代理操作。可以通过c、java、python、perl、php等接口连接到实例，这是mysql的连接器，属于第一层；第二层分为三个模块，一个是配置、备份、安全，二是连接池、三是sql解析器、sql优化、缓存管理等；第三层是存储引擎：MyISAM、InnoDB、NDB等；第四层是文件系统、文件、日志等。存储引擎是基于表的，而不是数据库。

InnoDB实现了4中隔离级别，使用next-key来避免幻读；提供插入缓冲、二次写、自适应哈希、预读等高性能高可用功能。

Hash索引与B+树索引数据结构都不一样，hash索引主要用于内存数据库引擎。

各种存储引擎的功能比较：

Mysql支持tcp/ip、域套接字、管道、命名管道、共享内存的方式进行通信。

二、InnoDB存储引擎

InnoDB是OLTP的首选引擎，MyISAM是OLAP的首选存储引擎。

Innodb引擎在后台管理多个线程，多个内存，多个文件，架构如图：

线程最主要就是master线程和IO线程，为了减轻master线程的负担，弄除了多个purge线程和一个cleaner线程回收脏页。

内存池里面有索引页，数据页、undo页、自适应哈希索引、数据字典、存储锁等，索引页和数据页占得最多；内存池中有多个缓冲池，根据hash索引将不同实例放入不同缓冲实例，以提高并发性能。如下图：

缓冲池是最大的内存块，而且可能存在多个缓冲池实例；缓冲池内部使用LCU、FREE、FLUSH三个链表分表管理不同的内存页；而且LCU不是朴素LCU，它会使用midpoint技术，以防热点数据被轻易污染；修改过的页同时也会放到FLUSH链表上。重做日志会放到重做日志缓冲，master thread每隔1s会刷新一次，或者数据使用率达到1/2时刷新一次，因此这个缓冲不是太大，默认8MB。额外的缓冲有内存堆实现，就相当于实现了标准库的堆内存管理，LCU、锁等数据结构的内存都是从这个额外的缓存申请或者归还。

刷新缓冲的时候会优先刷新undo日志缓存，然后才是其它脏页；如果数据库宕机就可以通过重做日志来恢复，这就是ACID，D(durability持久性)的实现保证。

主函数里面会做很多种刷新操作，脏页占有率达到70%后才会刷新；插入缓冲就是sql插入语句导致的脏页，这些数据可能是不连续的，需要等到几个插入语句合并在一起之后同步到磁盘，如果发生宕机，可能需要好久才能恢复。

插入缓冲存放的是一个B+树，而且针对所有表只有一个B+树，非叶节点存储的是索引键值；space是表id，marker标记是否支持老版本，offset表示叶节点的内存页偏移，如下：

叶节点保存数据，前三个属性与非页节点一样；如图：

Insert buffer是为了提高辅助索引插入性能，而两次写是为了提高DML sql的可靠性。

自适应哈希索引AHI是基于B+树对临热点数据创建的hash表，当也访问了100次，或者超过1/16page记录会自动将这条数据放到hash索引里；如果查询条件不一样、范围查找则不能命中hash索引。使用hash索引能提供聚集索引2倍效率，非聚集索引5倍效率。

启动异步IO恢复速度可以提高75%。

三、文件

文件有配置文件、日志文件、socket文件、pid文件、数据文件、表结构文件。其中日志文件最重要，包括：重做日志、错误日志、慢查询日志、二进制日志、查询日志、

表空间文件可以分布到多个磁盘上，这样当磁盘空间满了之后可以自动存储到另外的空间，同时也可以实现RAID功能。如下：

重做日志对InnoDB至关重要，这是保证数据可靠性的基础。重做日志太大可能的熬制恢复时间太长；重做日志太小有可能造成性能抖动。

四、表

InnoDB所有表都是按照索引进行组织的，而所有数据库的数据都放在一个表空间内，一个磁盘路径只有一个表空间；通过设置也可以把每个表放到一个表空间中。整个表空间都是B+树索引组织的；每个树节点指向各种段，段分为重做段、回滚段。而每个段又指向多个区，每个区大小1MB，每个区包含多个磁盘块；每个磁盘块又包含很多行，每个行就存储了行数据。

现在支持compact行格式和redundant行格式；redundant行格式是为了兼容旧版本格式。compact行数据格式如下：

Redundant行格式如下：

Compact和redundant格式成为antelope格式；为了支持溢出数据，弄了一种新格式叫barracuda格式，这种新格式又有两种，分别叫compress和dynamic。格式如下：

不同字符集下char类型可能是字节长度不同的，latin1是1字节，GBK是2字节。

每个数据页对应B+树的一个节点；如果是叶节点就存储了实际的数据，一个叶节点可能存储了多条数据,也有可能存在空闲空间。

Infimum比任何主键小，supermum比任何主键大；在创建B+树的时候生成，而且不会被删除。

各种行记录格式的关系

Innodb提供集中对字段的约束，叫做完整性约束，总感觉那么神秘的样子；索引表示数据结构，约束表示数据的完整，是逻辑的概念；支持的约束如下：primary key、unique key、foreign key、default、not null、enum、set；

在执行DML语句的时候可以触发触发器，一张表最多有6个触发器。

Mysql本身支持分区，分区就是把一张表分成几个独立的对象管理，而对于用户来说看起来还是一个表；这个分区是在数据库引擎级别就可以支持的，而不需要在业务逻辑上来进行分区；这个分区也就是传说中的分库分表；mysql支持水平拆分，而不支持垂直拆分。Mysql支持分区的方式：RANGE、LIST、HASH、KEY。

索引与算法

有些程序员在创建表的时候不添加索引，而后面发现查询慢的时候才将索引加上；一般都是DBA通过对sql的监控，发现查询效率低的sql，然后添加索引；他们对业务不了解，这样的添加索引方式生成效率很低。因此在设计数据库的时候就应该根据业务设计好索引。

在B+树的某个节点查找某个记录使用的是二分查找

二叉树的原理就是在使用二分查找，知识一个是算法，一个是数据结构。B+树是由二叉平衡树发展而来，它会自动平衡，而不需要像红黑树那样旋转。

簇集索引与辅助索引不同的是：叶子节点是否存放了一整行的数据。簇集索引是逻辑上连续的，而不是物理上连续的。

非簇集索引叶节点上存储的是主键索引键值，然后根据这个键值找到对应的数据，速度相对于簇集索引慢好多倍。关系如下：

分裂方式有三种：PAGE_LAST_INSERT/PAGE_DIRECTION/PAGE_N_DIRECTION

字段值重复率非常低的时候创建索引才有意义。一般OLAP应用场景可能用不到你建立的索引。

联合索引的B+树

覆盖索引是介于辅助索引与簇集索引之间的一种索引，它存储少部分的数据，而不需要再次查询簇集索引取数据。

如果有多个索引的时候，可以在sql语句中明确提示优化器使用哪个索引。

Mutip-read优化，可以对索引进行排序，减少离散读次数。

Index-condition-pushdown（ICP）优化可以在存储引擎层过滤where后面的条件，可以减少sql语句层的过滤数据量，。

Hash表灵感来源于直接寻址；hash查找应对范围查找无能为力，所以在OLTP场景效果比较好。

类似关键字搜索引擎，B+树效果并不好；使用关键字、关键语句来进行搜索，hash索引页不能很好地解决问题；这就需要全文索引。全文索引基于倒排索引，就是把单词和对应出现的文档id给记录下来；在插入记录的时候会想把单词放在FTS index chache中，查询或者停止数据库的时候会将新单词同步到auxiliary table表。

五、锁

锁是数据库与文件系统的重要区别。MyISAM因此只支持表锁，插入性能相对差一些；InnoDB支持行锁；sql server支持悲观锁和乐观锁，也支持到了行锁，但是它的行锁是一种稀少资源，锁会占据很多内存，而InnoDB不管你多少行锁占据的内存没有变化。

原来latch与lock都可以成为锁；latch是针对访问时间比较短的锁，比如互斥量、rwlock；而lock是针对耗时较长的任务，比如事务、行、表等。

InnoDB支持两种行级锁：共享锁（S，支持读）、排它锁（X，支持更新和删除）

想要对细粒度行上锁，必须先对粗粒度上锁；表--》页--》行，粗粒度上可以加意向锁，细粒度上加非意向锁。这些锁的层次可以看成是一个树结构。

在X行数据之后，会创建一个快照；如果此时有sql需要读该行，就不需要等待X锁释放，直接读取快照的数据；这叫做非一致性锁定度。快照刚好是undo日志，因此它还不需要额外的开销。在READ级别总是读取原始快照，REAPETABLE总是读取最新数据。

Select...for update/select...lock in share mode对行明确锁。

对自增长列的并发插入性能比较低。

行锁有三种算法：record lock、gap lock、next-key lock；next-lock是record lock + gap lock，它的设计是为了解决幻读问题。

Phantom problem幻象问题：连续执行两次slq可能导致不同的结果。

脏读就是一个事务可以读到另外一个事务还未提交的数据；它与读到脏页上的数据是两码事。不可重复读是在一次事务中分别读到了另外一个事务提交前、提交后的数据；这就是幻读问题，可以使用next-key lock解决。丢失更新就是两个连续的更新，最后一个更新覆盖了前一个更新，这主要是业务逻辑上引起的。

解决死锁最简单的方法居然是超时！这就是为什么很多系统API提供超时设置的原因。更加优雅的方式是wait-for graph，它需要事务链表信息和锁链表信息，链表前面就是需要等待的资源。如图：

实际上两个链形成一个图，采用深度优先非递归方式进行搜索就能检测出来

死锁链长度为2的概率是n^2.r^2/4R^2

六、事务

事务又是数据库系统与文件系统不同的特性之一。InnoDB的事务符合ACID的特性：原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）。

事务的类型有三种：扁平事务、带保存点的扁平事务、链事务、嵌套事务、分布式事务。

Undo日志保证事务的一致性；redo日志保证事务的原子性、持久性。

Redo日志大小都是512字节，因此不需要使用二次写技术，它刚好与扇区大小相等

多个日志块是分组存放的，每个分组就是一个文件，文件存满后存到下一个；只有第一个log file保存检查点等信息

幂等就是重做n次结果都一样，redo日志是幂等的

Undo日志是逻辑日志，并不能将数据库恢复到原来的样子。而且undo日志在共享表空间中。

Purg其实是清理DML操作。

为了提高日志IO性能，使用group commit机制

InnoDB支持四种事务隔离级别，并不是所有数据库都支持这四种，比如memory就不支持D。

事务的操作语句，完成那五种事务。

数据库引擎直接支持分布式事务，让多个事务参与到全局控制中。由一个事务管理器统一管理所有事务。

在循环中提交事务不好；使用自动提交事务也不好；自动回滚不好判断返回错误。

长事务应该尽量分成小事务来完成。