深圳建设厅网站,建设银行员工学习网站,四川建设网站项目招标,设计公司官网梁志天索引
Mysql由索引组织#xff0c;所以索引是mysql多重要概念之一。
聚簇索引
InnoDB和MyISAm一样都是采用B树结构#xff0c;但不同点在于InnoDB是聚簇索引#xff08;或聚集索引#xff09;#xff0c;将数据行直接放在叶子节点后面。 这里可能存在一个误区#xff1…索引
Mysql由索引组织所以索引是mysql多重要概念之一。
聚簇索引
InnoDB和MyISAm一样都是采用B树结构但不同点在于InnoDB是聚簇索引或聚集索引将数据行直接放在叶子节点后面。 这里可能存在一个误区觉得MyISAm是B树。innoDB采用了B树将数据存在了叶子节点上。 这个逻辑是不对的。这两个存储引擎都采用的B树只是叶子节点上存的数据不一样。MyISAm存的是一个地址而innoDB存的一整行数据。 聚簇索引是innoDB的主键索引的实现方式。如果没有设置主键呢难道就没有聚簇索引了当然不会了你不创建innoDB会帮你找一个 首先看看是不是有唯一索引如果有的话那么第一个唯一索引就是聚簇索引 如果唯一索引也没有表结构中还藏了一个隐藏字段DB_ROW_ID作为聚簇索引字段。
如图所示
辅助索引二级索引
非聚簇索引都是辅助索引包括唯一索引普通索引组合索引。
之所以叫辅助索引是因为一般情况下都需要“回表”最终还是要靠聚簇索引查出数据。
select * from tab where name a;
如图
索引分裂
当新增/修改索引数据时索引块空间不够分裂出一个新的索引块过程成为索引分裂。分裂方式方式包括
1-9分裂传统分裂方式。一块索引写完加一块新的上一个索引块数据尽量不动。适合索引自增长事务并发比较低的情况。但可能引起索引块层级突然升高多层的问题。
5-5分裂新增索引块时把上一个索引块一半的数据分到新索引块中。保证了增加了B树层级的稳定性避免增加一个索引块增加两层以上的情况。适合随机索引数据事务并发高的情况。但会引起索引的空间利用率不高的问题。
buffer poor缓冲池
数据库的读操作并不是直接从磁盘读取数据。而是首先在buffer poor里找如果有就直接返回没有再去磁盘获取放在buffer poor里然后返回。听起来像缓存但其实不是。 mysql缓存是[sql语句-查询结果]的key-value数据由于命中效率不高在mysql8就去掉了这块功能属于mysql的server层。 buffer poor里单纯是数据是存储引擎的一部分。 数据库的写操作并不是直接改的磁盘文件而是拿到缓冲池操作。从这个角度缓冲池起到一个工作空间的作用。修改过的数据页称为脏数据页。等待被刷到磁盘在刷到磁盘之前先写到redo log中。
可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小。
表空间
表空间(tablespace)是mysql数据在磁盘上的存储形式。或者说是.ibd文件的数据格式。表空间的逻辑从大到小依次可拆分为段(segment)区(extent)页(page)行(Row)。如下图所示 一般把页作为基本单位。
事务
innoDB为实现事务引入了一系列技术同时也带来了很多问题为解决这些问题又引入更多新技术。
所以mysql没有一开始就给自己默认存储引擎加入事务也是可以理解的。
事务四大特性
如图所示
锁
Mysql是一个多线程系统要实现事务保证数据库的一致性锁是必要的技术。
日志
redo log
redo log称为重做日志用于记录事务操作。以便在服务重启后通过前滚操作恢复数据。保证事务在服务正常/异常重启的情况下依然能保障原子性。
有些文章会这么做比喻 redo log可以理解为饭店里的小黑板。饭店允许客人记账赊账。当就餐人太多时就先把钱记到小黑板上。这样就不用在忙的焦头烂额时还去翻账本去记账算账了。 或者 redo log 就像快递代收点往一家一家送快递太慢先统一摆到代收点。有时间再往用户家里送。 从某种程度上说这么比喻也没问题。但可能会让人产生误解redo log在起“缓冲”作用那么下一步是不是把数据从redo log再往数据文件挪
如果你这么想那就可能就被误导了。
redo log和数据写入数据库表文件。正常情况下两者并没有很强的关联性。你写你的日志我写我的数据。只有当服务异常重启断电/服务宕机redo log才开始起作用。为数据库恢复缺失的数据。如图所示
上图中左边是redo log buffer写到磁盘日志文件的过程。右边是buffer poor写入磁盘数据库文件的过程。左右两边在正常情况下并没有什么交集。恢复过程是服务重启时才会起作用。
当然但也不是完全没关联。redo log在正常情况下并不完全只会无脑记日志。它还起到一个很重要的作用纪录完后作为事务完成的标志。有时候甚至会说日志比数据更重要。
顺序io由于日志的顺序io的特性redo log可以实现快速的落盘操作相比右边的数据落盘要快的多因为右边要处理复杂的数据结构写磁盘相当于一种随机io。 前面说的两个比喻其实想表达的就是这层意思。
上图中直接从log buffer 刷到了磁盘。实际操作中其实中间还有一层OS Buffer操作系统的内核空间如图所示
注意看这个图虽然也是两条路但是redo log和 undo log。其实看到这里就会明白一件事无论是redo logundo log还是buffer pool里的数据都需要经过 buffer用户空间 -- os buffer操作系统内核空间 -- file磁盘文件
这里涉及到一个配置参数innodb_flush_log_at_trx_commit 这个值表示三种从内核刷到磁盘的刷盘策略
1【默认】事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据但是因为每次提交都写入磁盘IO的性能较差。
0事务提交时不会将log buffer中日志写入到os buffer而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的当系统崩溃会丢失1秒钟的数据。
2每次提交都仅写入到os buffer然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。
在《Mysql技术内幕》里作者做了一个实验50万数据的插入测试配置从1改为0时间从113秒 缩短到了 13.9秒近10倍的速度差距还是很可观的。如果业务以写数据为主如状态监控系统并发量不是很大不在乎可能丢失1秒到数据我个人认为调为0就行。
前面我们看到有两个redo log日志ib_logfile0, ib_logfile1其实以后也不会再增加了。因为redo log是循环写数据。新数据会把最老的数据逐渐覆盖。这个日志本来就是为了拯救系统宕机前最后一批事务。而是不是用来恢复长期数据的。
与之对应的时binlog有人叫它二进制日志这纯粹是直译。其实redo logbinlogundo log 都不是纯文本日志。有人称之为”归档日志“这个在意思上就很贴切。
redo log和binlog的同步
redo log是innoDB用来保障数据完整性的日志。binlog是Mysql自带的日志除了可以用来恢复数据还用于集群之间的数据同步。所以这两者必须保证一致性。否则数据库集群之间的数据就不一致了。innoDB采用了2阶段提交机制 先把redo log写完但不改状态。再去写binlog等binlog写完再改状态。
实际生产中在16处都可能出现中断。前四处中断都无所谓二者都可以保持”未提交“状态。但问题是如果在5或者6处中断就有问题了。此时binlog完全可能已经把数据同步给集群的其他数据库了。但redo log却还是prepare状态。这就会导致本机和其他服务器数据不一致。因为2pc在理论上就无法实现一致性。
但好在日志是可以自我校验完整性的无论是redo log还是binlog都可以。
所以当服务再次重启redo log着手恢复数据时如果发现最后一个事物的状态是”prepare“状态。它并不会马上把该事务数据删掉而是要再去binlog那里看看。如果binglog的这个事务是完整的那么就可以判断应该就是刚好在5或6的位置中断的。则把事务数据恢复后状态改为commit这样本机和其他服务器数据就一致了。实现了弥补2pc逻辑漏洞的问题。
当然由于56时间极短。6这一步只是改一改状态几乎不费什么时间。前面大家都把事情干完了最后只是为了一起确认个状态。这也是为什么2pc机制即便存在逻辑漏洞但依然被使用的原因。
redo log还有一个特性需要强调的是它是物理日志。经常会以此和undo log一起说或者跟binlog做对比。因为undo log和binlog都是逻辑日志。具体见博客mysql详解之redolog的关键——幂等性
redo log所在位置mac系统的目录
undo log
被称为回滚日志。可以在事务出现异常非服务或服务器异常时进行回滚操作。也就是程序正常的回滚操作。
和redo log的物理日志不同undo log是逻辑日志。可以简单理解为sql语句。
当用户执行了insert语句那么就会生成delete的undo log日志。当用户执行了a -- b的updte语句那么就会生成一个b -- a的undo log日志。
undo log和redo log 共同实现了无论是日常事务回滚还是服务重启都可以自动保障事务的原子性。
undo log的第二大作用在是MVCC机制中实现多版本控制。MVCC中多个版本的数据其实只有一个版本。修改就是在原数据上改的都是buffer pool里的操作。之所以看起来还能查到“历史版本”。不是保留了历史版本而是通过undo log回退计算获得的历史版本。
另外undo log也会产生redo log因为undo log也要实现持久性保护。
undo log默认和redo log存放在一起也可以通过配置改为和表文件存放在一起。
参考
深入理解MySQL重做日志 redo log MySQL事务日志(redo log和undo log)的详细分析【非常全面】 B树索引是怎么分裂的 Mysql buffer pool详解 答应我这次要搞懂 Buffer Pool MySQL的表空间是什么 为什么MySQL 删除表数据 磁盘空间还一直被占用 mysql深入浅出之日志(redolog, binlog, undolog)
