doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead

转载

半夜未央好 2024-05-29 01:46:09

文章标签 mysql java 数据库缓冲池存储引擎 文章分类 MySQL 数据库

总概

每个存储引擎各有各的特点，能够根据具体的应用建立不同存储引擎表，InnoDB、MyISAM、NDB、Memory、Arvhive。
InnoDB通过多版本并发控制（MVCC），来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为可重复读，使用一种称为next-key locking的策略来避免幻读。支持事务、行锁设计、外键还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用功能。

doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead_存储引擎

InnoDB引擎使用了聚簇（clustered）的方式，因此每张表的存储都是按主键的顺序进行存放的。如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，并以此作为主键。
InnoDB引擎适合OLTP（Online Transaction Processing）应用。MyISAM不支持事务、表锁，支持全文索引，主要面向一些OLAP（Online Analytical Processing）数据库应用。MyISAM引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。

doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead_mysql_02

Memory存储引擎默认使用哈希索引，而不是B+树索引。Memory引擎速度非常快，但支持表锁，并发性能较差，不支持TEXT和BLOB列表类型，存储varchar按照定长char的方式进行的，因此会浪费内存。
对比下同一份数据，使用MyISAM引擎表大小为40MB，使用InnoDB存储引擎时表为113MB，而使用Archive引擎时表的大小为20MB。

InnoDB引擎

后台线程主要工作是负责刷新内存池中的数据，保证缓冲池中的内存缓存的最近的数据。此外将已修改的数据文件刷新到磁盘文件，保证在数据库发生异常的情况下InnoDB能恢复到正常状态。
Master Thread 是后台线程非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新、合并插入缓冲（Insert Buffer）、Undo页的回收。
IO Thread 主要负责IO请求回调处理。
Purge Thread事务提交后，所使用的undolog不再需要，使用Purge Thread回收已经使用并分配的undo页。
Page Cleaner Thread将之前版本中脏页的刷新操作都放入到单独的线程中完成，减轻Master Thread的工作及用户查询线程的阻塞。
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响，为了协调CPU速度与磁盘速度的鸿沟。在数据库中进行读取页的操作，首先将磁盘读到的页放在缓冲池中，下次读取时判断是否在缓冲池中，若在则命中。
缓冲池中缓存的数据页类型有：索引页、数据页、undo页、插入缓冲、自适应哈希索引、InnoDB存储的锁信息、字典信息。索引页和数据页占了很大一部分。

doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead_mysql_03

缓冲池通过LRU算法进行管理，即将最频繁使用的页放在LRU的前端，而最少使用的页在LRU列表的尾端，当缓冲池不能存放存放新读取到的页时，将首先释放LRU中尾端的页。InnoDB引擎对LRU算法做了优化，加入了midpoint位置，新读取到的页，虽然是最新访问的，但并不是直接放入到LRU列表的首部，而是放入到LRU的midpoint位置，默认配置下，该位置在LRU列表长度的5/8处，即新读取的页插入到LRU尾端3/8%的位置，midpoint之后的列表称为old列表，之前叫做new列表，new列表都是最为活跃的数据源
MySQL缓冲池加入了一个“老生代停留时间窗口”的机制来解决这类扫描大量数据导致的缓冲池污染问题，“老生代停留时间窗口”策略后，短时间内被大量加载的页，并不会立刻插入new列表头部。而只有在old列表呆的时间足够久，停留时间大于T，才会被插入new列表头部。
缓冲池命中率，通常情况下不应该小于95%，若发生小于95%情况，需要观察是否由于全表扫描引起的LRU列表被污染。
通常情况下8MB的redoLog足以满足绝大部分的应用，以下几种情况会将redolog日志缓冲中的内容刷新到外部磁盘：Master Thread每一秒将redolog缓冲刷新到redolog文件；每个事务提交(commit)时；当redolog缓冲池剩余空间小于1/2时；当有checkpoint时，checkpoint代表了刷到磁盘时日志所处的LSN位置。

doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead_存储引擎_04

为了避免发生数据丢失，事务数据库普遍采用Write Ahead Log策略，当事务提交时，必须先将事务的所有事务日志写入到磁盘上的redolog文件进行持久化，再修改页。
当内存数据页和磁盘数据页内容不一致的时候，称内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

doris和mysql读取复杂大量sql的性能哪个快 mysql read ahead_java_05

checkpoint技术目的是解决以下几个问题，缩短数据库恢复时间，因为checkpoint之前的页都已经刷新（flush）回磁盘了，故数据库只需对checkpoint后的redolog进行恢复；缓冲池内存不够用时，根据LRU算法会溢出最近最少使用的页，若此页为脏页，需强制执行checkpoint将脏页刷新到磁盘，空出内存给别的数据页使用；redolog日志写满了不可用时，刷新脏页。（内存不足的时候、redolog写满的时候）
InnoDB引擎中，Checkpoint发生的时间、条件及脏页的选择都非常复杂。而checkpoint所做的事情无外乎是将缓冲池中的脏页刷回磁盘。有两种Checkpoint，Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘；Fuzzy Checkpoint只刷新一部分脏页回磁盘。（关闭的时候、空闲的时候）
写缓冲是解决降低磁盘IO、提升数据库写性能的一种机制。在MySQL5.5之前，这部分机制叫做插入缓冲(insert buffer)，只针对insert做了优化；而现在对delete和update也同样有效，统一叫做写缓冲(change buffer)。
写缓冲原理是当非唯一普通索引页(non-unique secondary index page)不在缓冲池中时，对某一页进行了写操作，此时InnoDB并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更。等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。将多次IO合并成一次IO，提高IO性能。
InnoDB引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，称只为自适应哈希索引。（Adaptive Hash Index,AHI）
AHI通过缓冲池的B+数页构建而来，不需要对整张表构建哈希索引。InnoDB引擎会自动根据访问频率和模式自动为某些热点页建立哈希索引。
AIO不仅可以提高磁盘操作性能，而且可以进行IO Merge，即将多个IO合并为一个IO，可以提高IOPS的性能。
InnoDB引擎提供了Flush Neighbor Page(刷新临接页)，当刷新一个脏页时，InnoDB会检测该页所在区的所有页，如果是脏页，一起刷新。innodb_flush_neighbors参数可控制是否开启此功能，固态硬盘有着较高IOPS，不必开启，传统机械硬盘建议开启。

总结InnoDB引擎特点

脏页：缓冲池中的页面被修改后的页面，我们称之为脏页。
重做日志 redo log：用来记录用户对数据的修改过程，定时刷新到磁盘，用于断电重启时对缓存中的脏页进行恢复。（DB宕机后重启，InnoDB会首先去查看LSN，然后再去查看redolog中LSN的大小，如果数据页中LSN值大说明数据页领先于redolog，不需要进行恢复，反之需要从redolog中恢复数据）
CheckPoint：将脏页数据刷新到磁盘的技术。
LSN(Log Sequence Number): 每个缓存页/redolog日志/checkPoint都有 LSN，用来记录顺序节点位置。
Change Buffer：用于非聚集索引页的插入和更新操作，提高插入性能。
二次写：为了确保在宕机时刷新脏页不会出现问题，先将原始页做一份备份到磁盘，再写脏页，出现问题可以通过备份进行恢复。
自适应哈希索引：为了提高辅助索引的查询效率，对于频繁查询的辅助索引会自动建立 hash 索引。
异步IO：为了提高磁盘操作的性能，InnoDB 存储引擎采用异步 IO（AIO）的方式来处理磁盘操作。
半同步复制：异步复制指主库在执行完客户端提交的事务，就返回给客户端；同步复制需要等当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端；半同步复制介于同步和异步之间，至少一个从库接收并写入了redolog，才返回给客户端。有点类似于Kafka中ack策略。
刷新邻接页：在刷新脏页时，检查该页所在区的所有页是否存在脏页，存在则一起刷新。