天气预报网站怎么做,wordpress当下载站,郑州手机网站建设价格明细表,网站开发课程的心得MYSQL 存储引擎 查看MySQL提供所有的存储引擎 mysql show engines; mysql常用引擎包括#xff1a;MYISAM、Innodb、Memory、MERGE 1、MYISAM#xff1a;全表锁#xff0c;拥有较高的执行速度#xff0c;不支持事务#xff0c;不支持外键#xff0c;并发性能差#x…MYSQL 存储引擎 查看MySQL提供所有的存储引擎 mysql show engines; mysql常用引擎包括MYISAM、Innodb、Memory、MERGE 1、MYISAM全表锁拥有较高的执行速度不支持事务不支持外键并发性能差占用空间相对较小对事务完整性没有要求以select、insert为主的应用基本上可以使用这引擎 2、Innodb行级锁提供了具有提交、回滚和崩溃回复能力的事务安全支持自动增长列支持外键约束并发能力强占用空间是MYISAM的2.5倍处理效率相对会差一些 3、Memory全表锁存储在内容中速度快但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失默认使用HASH索引检索效率非常高但不适用于精确查找主要用于那些内容变化不频繁的代码表 4、MERGE是一组MYISAM表的组合
MyISAM
MyISAM引擎特点
MyISAM引擎是MySQL5.5版本之前的数据库所默认的数据表引擎。每一个采用MyISAM引擎的数据表在实际存储中都是由三个文件组成分别是frm文件MYD文件和MYI文件文件后缀为上述三个文件名与数据表名相同。 其中frm文件保存表的结构MYD保存表的数据MYI保存表的索引文件MYD和MYI与MyISAM引擎有很深的关联。
MyISAM引擎的特点
1、不支持事务。 2、表级锁定。 即发生数据更新时会锁定整个表以防止其他会话对该表中数据的同时修改所导致的混乱。这样做可以使得操作简单但是会减少并发量。 3、读写互相堵塞。 在MyISM类型的表中既不可以在向数据表中写入数据的同时另一个会话也向该表中写入数据也不允许其他的会话读取该表中的数据。只允许多个会话同时读取该数据表中的数据。 4、只会缓存索引不会缓存数据。 所谓缓存就是指数据库在访问磁盘数据时将更多的数据读取进入内存这样可以使得当访问这些数据时直接从内存中读取而不是再次访问硬盘。MyISAM可以通过key_buffer_size缓存索引以减少磁盘I/O提升访问性能。但是MyISAM数据表并不会缓存数据。 5、读取速度较快占用资源较少。 6、不支持外键约束。 7、支持全文索引。
说明从MySQL8.0开始它就可以被称为过时了 – MySQL已经确保在使用InnoDB时MyISAM可以完成的所有工作都可以完成因此目前只有当您希望简单的COUNT*查询更快时MyISAM才非常有用。这样的查询会更快因为MyISAM将表记录数数字存储在表元数据中–其他 MySQL存储引擎则没有
InnoDB
从Mysql5.5版本开始InnoDB是默认的表存储引擎。其特点是行锁设计、支持MVCC、支持外键、提供一致性非锁定读、同时被设计用来最有效的利用以及使用内存和CPU。
InnoDB体系架构 下图简单描述了InnoDB存储引擎的体系结构 InnoDB存储引擎有多个内存块这些内存块组成了一个大的内存池。后台线程主要负责刷新内存池中的数据、将已修改的数据刷新到磁盘等等。接下来我们分别介绍后台线程和内存池。
后台线程
InnoDB后台有多个不同的线程用来负责不同的任务。主要有如下
1、Master Thread 这是最核心的一个线程,主要负责将缓冲池中的数据异步刷新到磁盘,保证数据的一致性,包括赃页的刷新、合并插入缓冲、UNDO 页的回收等. 2、IO Thread 在 InnoDB 存储引擎中大量使用了异步 IO 来处理写 IO 请求, IO Thread 的工作主要是负责这些 IO 请求的回调处理。 3、Purge Thread 事务被提交之后, undo log 可能不再需要,因此需要 Purge Thread 来回收已经使用并分配的 undo页. InnoDB 支持多个 Purge Thread, 这样做可以加快 undo 页的回收。 4、Page Cleaner Thread Page Cleaner Thread 是在InnoDB 1.2.x版本新引入的,其作用是将之前版本中脏页的刷新操作都放入单独的线程中来完成,这样减轻了 Master Thread 的工作及对于用户查询线程的阻塞。
内存
InnoDB 存储引擎是基于磁盘存储的,也就是说数据都是存储在磁盘上的,由于 CPU 速度和磁盘速度之间的鸿沟,
InnoDB 引擎使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域.在数据库中进行读取页的操作,首先将从磁盘读到的页存放在缓冲池中,下一次读取相同的页时,首先判断该页是不是在缓冲池中,若在,称该页在缓冲池中被命中,直接读取该页。否则,读取磁盘上的页。对于数据库中页的修改操作,首先修改在缓冲池中页,然后再以一定的频率刷新到磁盘,并不是每次页发生改变就刷新回磁盘。
缓冲池中缓存的数据页类型有:索引页、数据页、 undo 页、插入缓冲、自适应哈希索引、 InnoDB 的锁信息、数据字典信息等。索引页和数据页占缓冲池的很大一部分知道有这些页把这些页当做名词即可不用感到迷惑。在InnoDB中缓冲池中的页大小默认为16KB。 我们已经知道这个Buffer Pool其实是一片连续的内存空间那现在就面临这个问题了怎么将磁盘上的页缓存到内存中的Buffer Pool中呢直接把需要缓存的页向Buffer Pool里一个一个往里怼么不不不为了更好的管理这些被缓存的页InnoDB为每一个缓存页都创建了一些所谓的控制信息这些控制信息包括该页所属的表空间编号、页号、页在Buffer Pool中的地址一些锁信息以及LSN信息锁和LSN这里可以先忽略当然还有一些别的控制信息。
每个缓存页对应的控制信息占用的内存大小是相同的我们就把每个页对应的控制信息占用的一块内存称为一个控制块吧控制块和缓存页是一一对应的它们都被存放到 Buffer Pool 中其中控制块被存放到 Buffer Pool 的前边缓存页被存放到 Buffer Pool 后边所以整个Buffer Pool对应的内存空间看起来就是这样的
控制块和缓存页之间的那个碎片是个什么呢你想想啊每一个控制块都对应一个缓存页那在分配足够多的控制块和缓存页后可能剩余的那点儿空间不够一对控制块和缓存页的大小自然就用不到喽这个用不到的那点儿内存空间就被称为碎片了。当然如果你把Buffer Pool的大小设置的刚刚好的话也可能不会产生碎片
前面我们知道了缓冲池的结构。接下来说InnoDB存储引擎是怎么对缓冲池进行管理的。讲下我当时的困惑吧这部分可以不看因为有些名词还没涉及到看《Mysql技术内幕》这本书讲LRU List比较多Free List只是顺带一过没有将free list初始时是怎么分配的或者是什么样的结构导致我对缓冲池的管理总是想象不出来后来经过在网上寻找资料算是弄明白了。来看一下吧
当我们最初启动MySQL服务器的时候需要完成对Buffer Pool的初始化过程就是分配Buffer Pool的内存空间把它划分成若干对控制块和缓存页。但是此时并没有真实的磁盘页被缓存到Buffer Pool中因为还没有用到之后随着程序的运行会不断的有磁盘上的页被缓存到Buffer Pool中那么问题来了从磁盘上读取一个页到Buffer Pool中的时候该放到哪个缓存页的位置呢或者说怎么区分Buffer Pool中哪些缓存页是空闲的哪些已经被使用了呢我们最好在某个地方记录一下哪些页是可用的我们可以把所有空闲的页包装成一个节点组成一个链表这个链表也可以被称作Free链表或者说空闲链表。因为刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的所以每一个缓存页都会被加入到Free链表中假设该Buffer Pool中可容纳的缓存页数量为n那增加了Free链表的效果图就是这样的
从图中可以看出我们为了管理好这个Free链表特意为这个链表定义了一个控制信息里边儿包含着链表的头节点地址尾节点地址以及当前链表中节点的数量等信息。我们在每个Free链表的节点中都记录了某个缓存页控制块的地址而每个缓存页控制块都记录着对应的缓存页地址所以相当于每个Free链表节点都对应一个空闲的缓存页。
有了这个Free链表事儿就好办了每当需要从磁盘中加载一个页到Buffer Pool中时就从Free链表中取一个空闲的缓存页并且把该缓存页对应的控制块的信息填上然后把该缓存页对应的Free链表节点从链表中移除表示该缓存页已经被使用了
简单回顾一下为什么讲free list是为了讲怎么管理buffer pool对吧。那free list就相当于是数据库服务刚刚启动没有数据页时维护buffer pool的空闲缓存页的数据结构。
下面再来简单地回顾Buffer Pool的工作机制。Buffer Pool两个最主要的功能一个是加速读一个是加速写。加速读呢 就是当需要访问一个数据页面的时候如果这个页面已经在缓存池中那么就不再需要访问磁盘直接从缓冲池中就能获取这个页面的内容。加速写呢就是当需要修改一个页面的时候先将这个页面在缓冲池中进行修改记下相关的重做日志这个页面的修改就算已经完成了。至于这个被修改的页面什么时候真正刷新到磁盘这个是后台刷新线程来完成的。
在实现上面两个功能的同时需要考虑客观条件的限制因为机器的内存大小是有限的所以MySQL的InnoDB Buffer Pool的大小同样是有限的如果需要缓存的页占用的内存大小超过了Buffer Pool大小也就是Free链表中已经没有多余的空闲缓存页的时候岂不是很尴尬发生了这样的事儿该咋办当然是把某些旧的缓存页从Buffer Pool中移除然后再把新的页放进来喽 那么问题来了移除哪些缓存页呢
为了回答这个问题我们还需要回到我们设立Buffer Pool的初衷我们就是想减少和磁盘的I/O交互最好每次在访问某个页的时候它都已经被缓存到Buffer Pool中了。假设我们一共访问了n次页那么被访问的页已经在缓存中的次数除以n就是所谓的缓存命中率我们的期望就是让缓存命中率越高越好
怎么提高缓存命中率呢
InnoDB Buffer Pool采用经典的LRU算法来进行页面淘汰以提高缓存命中率。当Buffer Pool中不再有空闲的缓存页时就需要淘汰掉部分最近很少使用的缓存页。不过我们怎么知道哪些缓存页最近频繁使用哪些最近很少使用呢呵呵神奇的链表再一次派上了用场我们可以再创建一个链表由于这个链表是为了按照最近最少使用的原则去淘汰缓存页的所以这个链表可以被称为LRU链表Least Recently Used。当我们需要访问某个页时可以这样处理LRU链表
1、如果该页不在Buffer Pool中在把该页从磁盘加载到Buffer Pool中的缓存页时就把该缓存页包装成节点塞到链表的头部。 2、如果该页在Buffer Pool中则直接把该页对应的LRU链表节点移动到链表的头部。 但是这样做会有一些性能上的问题比如你的一次全表扫描或一次逻辑备份就把热数据给冲完了就会导致导致缓冲池污染问题Buffer Pool中的所有数据页都被换了一次血其他查询语句在执行时又得执行一次从磁盘加载到Buffer Pool的操作而这种全表扫描的语句执行的频率也不高每次执行都要把Buffer Pool中的缓存页换一次血这严重的影响到其他查询对 Buffer Pool 的使用严重的降低了缓存命中率
所以InnoDB存储引擎对传统的LRU算法做了一些优化在InnoDB中加入了midpoint。新读到的页虽然是最新访问的页但并不是直接插入到LRU列表的首部而是插入LRU列表的midpoint位置。这个算法称之为midpoint insertion stategy。默认配置插入到列表长度的5/8处。midpoint由参数innodb_old_blocks_pct控制。
midpoint之前的列表称之为new列表之后的列表称之为old列表。可以简单的将new列表中的页理解为最为活跃的热点数据。
同时InnoDB存储引擎还引入了innodb_old_blocks_time来表示页读取到mid位置之后需要等待多久才会被加入到LRU列表的热端。可以通过设置该参数保证热点数据不轻易被刷出。
好了基本拿下了LRU list后我们继续。前面我们讲到页面更新是在缓存池中先进行的那它就和磁盘上的页不一致了这样的缓存页也被称为脏页英文名dirty page。
所以需要考虑这些被修改的页面什么时候刷新到磁盘以什么样的顺序刷新到磁盘
当然最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上但是频繁的往磁盘中写数据会严重的影响程序的性能毕竟磁盘慢的像乌龟一样。所以每次修改缓存页后我们并不着急立即把修改同步到磁盘上而是在未来的某个时间点进行同步由后台刷新线程依次刷新到磁盘实现修改落地到磁盘。
但是如果不立即同步到磁盘的话那之后再同步的时候我们怎么知道Buffer Pool中哪些页是脏页哪些页从来没被修改过呢
总不能把所有的缓存页都同步到磁盘上吧假如Buffer Pool被设置的很大比方说300G那一次性同步这么多数据岂不是要慢死所以我们不得不再创建一个存储脏页的链表凡是在LRU链表中被修改过的页都需要加入这个链表中因为这个链表中的页都是需要被刷新到磁盘上的所以也叫FLUSH链表有时候也会被简写为FLU链表。链表的构造和Free链表差不多这就不赘述了。
这里的脏页修改指的此页被加载进Buffer Pool后第一次被修改只有第一次被修改时才需要加入FLUSH链表代码中是根据Page头部的oldest_modification 0来判断是否是第一次修改如果这个页被再次修改就不会再放到FLUSH链表了因为已经存在。需要注意的是脏页数据实际还在LRU链表中而FLUSH链表中的脏页记录只是通过指针指向LRU链表中的脏页。并且在FLUSH链表中的脏页是根据oldest_lsn这个值表示这个页第一次被更改时的lsn号对应值oldest_modification每个页头部记录进行排序刷新到磁盘的值越小表示要最先被刷新避免数据不一致。
注意脏页既存在于LRU列表中也存在与Flush列表中。LRU列表用来管理缓冲池中页的可用性Flush列表用来管理将页刷新回磁盘二者互不影响。 这三个重要列表LRU list free listflush list的关系可以用下图表示
Free链表跟LRU链表的关系是相互流通的页在这两个链表间来回置换。而FLUSH链表记录了脏页数据也是通过指针指向了LRU链表所以图中FLUSH链表被LRU链表包裹。
CheckPoint技术
说完缓冲池下面说CheckPoint技术。 CheckPoint技术是用来解决如下几个问题
缩短数据库恢复时间 缓冲池不够用时将脏页刷新到磁盘 重做日志不可用时刷新脏页 缩短数据库恢复时间重做日志中记录了的checkpoint的位置这个点之前的页已经刷新回磁盘只需要对checkpoint之后的重做日志进行恢复。这样就大大缩短了恢复时间。
缓冲池不够用时根据LRU算法溢出最近最少使用的页如果页为脏页强制执行checkpoint将脏页刷新回磁盘。
重做日志不可用是指重做日志的这部分不可以被覆盖为什么因为由于重做日志的设计是循环使用的。这部分对应的数据还未刷新到磁盘上。数据库恢复时如果不需要这部分日志即可被覆盖如果需要必须强制执行checkpoint将缓冲池中的页至少刷新到当前重做日志的位置。
checkpoint每次刷新多少页到磁盘每次从哪里取脏页什么时间触发checkpoint InnoDB存储引擎内部两种checkpoint分别为:
1、Sharp Checkpoint 2、Fuzzy Checkpoint Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘这是默认的工作方式即参数innodb_fast_shutdown1。
不适用于数据库运行时的刷新。
在数据库运行时InnoDB存储引擎内部采用Fuzzy Checkpoint只刷新一部分脏页。
几种发生Fuzzy Checkpoint的情况
①MasterThread Checkpoint 异步刷新每秒或每10秒从缓冲池脏页列表刷新一定比例的页回磁盘。异步刷新即此时InnoDB存储引擎可以进行其他操作用户查询线程不会受阻。
②FLUSH_LRU_LIST Checkpoint
InnoDB存储引擎需要保证LRU列表中差不多有100个空闲页可供使用。在InnoDB 1.1.x版本之前用户查询线程会检查LRU列表是否有足够的空间操作。如果没有根据LRU算法溢出LRU列表尾端的页如果这些页有脏页需要进行checkpoint。因此叫flush_lru_list checkpoint。
InnoDB 1.2.x开始这个检查放在了单独的进程Page Cleaner中进行。好处 1.减少master Thread的压力 2.减轻用户线程阻塞。 设置参数innodb_lru_scan_dept控制LRU列表中可用页的数量该值默认1024
③Async/Sync Flush Checkpoint
指重做日志不可用的情况需要强制刷新页回磁盘此时的页时脏页列表选取的。
这种情况是保证重做日志的可用性说白了就是重做日志中可以循环覆盖的部分空间太少了换种说法就是极短时间内产生了大量的redo log。
接下来会有几个变量图解也不难仔细看看。
InnoDB存储引擎通过LSNLog Sequence Number来标记版本LSN是8字节的数字。每个页有LSN重做日志有LSNcheckpoint有LSN。写入日志的LSN:redo_lsn刷新回磁盘的最新页LSN:checkpoint_lsn
有如下定义: checkpoint_age redo_lsn - checkpoint_lsn async_water_mark 75% * total_redo_file_size sync_water_mark 90% * total_redo_file_size 刷新过程如下图所示
④Dirty Page too much Checkpoint 即脏页太多强制checkpoint.保证缓冲池有足够可用的页。 参数设置innodb_max_dirty_pages_pct 75 表示当缓冲池中脏页的数量占75%时强制checkpoint。1.0.x之后默认75
InnoDB关键特性
插入缓冲
Insert Buffer是InnoDB存储引擎关键特性中最令人激动与兴奋的一个功能。不过这个名字可能会让人认为插入缓冲是缓冲池中的一个组成部分。其实不然InnoDB缓冲池中有Insert Buffer信息固然不错但是Insert Buffer和数据页一样也是物理页的一个组成部分。
一般情况下主键是行唯一的标识符。通常应用程序中行记录的插入顺序是按照主键递增的顺序进行插入的。因此插入聚集索引一般是顺序的不需要磁盘的随机读取。因为对于此类情况下的插入速度还是非常快的。如果主键类是UUID这样的类那么插入和辅助索引一样也是随机的。
如果索引是非聚集的且不唯一。在进行插入操作时数据的存放对于非聚集索引叶子节点的插入不是顺序的这时需要离散地访问非聚集索引页由于随机读取的存在而导致了插入操作性能下降。这是因为B树的特性决定了非聚集索引插入的离散性。
Insert Buffer的设计对于非聚集索引的插入和更新操作不是每一次直接插入到索引页中而是先判断插入非聚集索引页是否在缓冲池中若存在则直接插入不存在则先放入一个Insert Buffer对象中。数据库这个非聚集的索引已经插到叶子节点而实际并没有只是存放在另一个位置。然后再以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge合并操作这时通常能将多个插入合并到一个操作中因为在一个索引页中这就大大提高了对于非聚集索引插入的性能。 需要满足的两个条件
1、索引是辅助索引 2、索引不是唯一的。 辅助索引不能是唯一的因为在插入缓冲时数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生从而导致Insert Buffer失去了意义。
两次写
如果说插入缓冲是为了提高写性能的话那么两次写是为了提高可靠性。
介绍两次写之前说一下部分写失效 想象这么一个场景当数据库正在从内存向磁盘写一个数据页时数据库宕机从而导致这个页只写了部分数据这就是部分写失效它会导致数据丢失。这时是无法通过重做日志恢复的因为重做日志记录的是对页的物理修改如果页本身已经损坏重做日志也无能为力。
从上面分析我们知道在部分写失效的情况下我们在应用重做日志之前需要原始页的一个副本两次写就是为了解决这个问题下面是它的原理图
两次写需要额外添加两个部分 1内存中的两次写缓冲doublewrite buffer大小为2MB 2磁盘上共享表空间中连续的128页大小也为2MB
其原理是这样的 1当刷新缓冲池脏页时并不直接写到数据文件中而是先拷贝至内存中的两次写缓冲区。 2接着从两次写缓冲区分两次写入磁盘共享表空间中每次写入1MB 3待第2步完成后再将两次写缓冲区写入数据文件
这样就可以解决上文提到的部分写失效的问题因为在磁盘共享表空间中已有数据页副本拷贝如果数据库在页写入数据文件的过程中宕机在实例恢复时可以从共享表空间中找到该页副本将其拷贝覆盖原有的数据页再应用重做日志即可。
其中第2步是额外的性能开销但由于磁盘共享表空间是连续的因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能默认是开启的强烈建议开启该功能。
自适应哈希索引
哈希是一种非常快的查找方法在一般情况时间复杂度为O(1)。而B树的查找次数取决于B树的高度在生成环境中B树的高度一般为3-4层不需要查询3-4次。
InnoDB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以提升速度这简历哈希索引称之为自适应哈希索引(Adaptive Hash Index, AHI)。AHI是通过缓冲池的B树页构造而来的。因此建立的速度非常快且不要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。
AHI有一个要求对这个页的连续访问模式(查询条件)必须一样的。例如联合索引(a,b)其访问模式可以有以下情况:
WHERE aXXX; WHERE axxx AND bxxx。 若交替进行上述两张查询InnoDB存储引擎不会对该页构造AHI。此外AHI还有如下要求 以该模式访问了100次 页通过该模式访问了N次其中N页中记录/16。 根据官方文档显示启用AHI后读取和写入的速度可以提高2倍负责索引的链接操作性能可以提高5倍。其设计思想是数据库自由化的无需DBA对数据库进行人为调整。 异步IO(AIO)
为了提高磁盘操作性能当前的数据库系统都采用异步IO的方式来处理磁盘操作。InnoDB也是如此。
与AIO对应的是Sync IO即每进行一次IO操作需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询那么这条SQL语句可能需要扫描多个索引页也就是需要进行多次IO操作。在每扫描一个页并等待其完成再进行下一次扫描这是没有必要的。用户可以在发出一个IO请求后立即再发出另外一个IO请求当全部IO请求发送完毕后等待所有IO操作完成这就是AIO。
AIO的另外一个优势是进行IO Merge操作也就是将多个IO合并为一个IO操作这样可以提高IOPS的性能。
在InnoDB 1.1.x之前AIO的实现是通过InnoDB存储引擎中的代码来模拟的。但是从这之后提供了内核级别的AIO的支持称为Native AIO。Native AIO需要操作系统提供支持。Windows和Linux都支持而Mac则未提供。在选择MySQL数据库服务器的操作系统时需要考虑这方面的因素。
MySQL可以通过参数innodb_use_native_aio来决定是否启用Native AIO。在InnoDB存储引擎中read ahead方式的读取都是通过AIO完成脏页的刷新也是通过AIO完成。
刷新邻接页
InnoDB存储引擎在刷新一个脏页时会检测该页所在区(extent)的所有页如果是脏页那么一起刷新。这样做的好处是通过AIO可以将多个IO写操作合并为一个IO操作。该工作机制在传统机械磁盘下有显著优势。但是需要考虑下吧两个问题:
是不是将不怎么脏的页进行写入而该页之后又会很快变成脏页
固态硬盘有很高IOPS是否还需要这个特性
为此InnoDB存储引擎1.2.x版本开始提供参数innodb_flush_neighbors来决定是否启用。对于传统机械硬盘建议使用而对于固态硬盘可以关闭。
