二. Mysql中各种日志详细介绍
2.1 redoLog 物理日志,innodb层产生,记录数据页的物理修改,而不是某一行或几行的修改怎样,它用来恢复提交后的物理数据页(checkpoint开始恢复)。
Redo log file 大小对innodb性能影响非常大,设置太大,恢复时就会时间较长,设置太小,会导致写redo log时循环切换redo log file。
2.2 undoLog: 用来回滚行记录到某个版本,undo log一般为逻辑日志,根据每行记录进行记录。
undoLog有2个作用:提供回滚和多个行版本控制(MVCC)。
当delete一条记录时,undo log中会记录一条对应的insert记录。 反之亦然,当update一条记录时,它记录一条对应相反的update记录。
进行rollback时,可以从undo log中的逻辑记录读取到相对应的内容并进行回滚。应用到多版本控制时,也是通过undo log来实现的:当读取的某一行被其他事务锁定时,它可以从undo log中分析出该行记录以前的数据是什么,从而提供该行版本信息,让用户实现非锁定一致性读取。
Undo log采用segment的方式来记录。每个undo操作在记录时占用一个undo log segment。 另外undo log也会产生redo log,因为undo log也要实现持久化保护。
事务提交时,innodb 不会立即删除undo log,而是将delete对象打个delete flag。因为后续还可能会用到。如隔离级别为repeatable read时,事务读取的都是开启事务时的最新提交行版本,只要该事务不结束,该行版本就不能删除,即undo log不能删除。
但是在事务提交时,会将该事务对应的undo log放入待删除列表,未来通过purge删除。
对于InnoDB来说,无论是更新,删除,都只是设置行记录上的deleted version标记位,而不是真正的删除记录,后续这些记录的清理,是通过Purge后台进程实现的。
二进制日志是mysql上层的日志,先于存储引擎的事务日志被写入。
Gap Locks不适用于唯一索引(unique index),在唯一索引记录上,只会使用index-record lock
Crash Recovery(no binlog)
由于未提交的事务和已回滚的事务也会记录到redo log中,因此在进行恢复的时候,这些事务要进行特殊的处理
innodb的处理策略是:进行恢复时,从checkpoint开始,重做所有事务(包括未提交的事务和已回滚的事务),然后通过undo log回滚那些未提交的事务
1、扫描最后一个 binlog,提取xid(标识binlog中的第几个event)2、xid也会写到redo中,将redo中prepare状态的xid,去跟最后一个binlog中的xid比较 ,如果binlog中存在,则提交(即前滚),否则回滚
为什么只扫描最后一个binlog?因为binlog rotate的时候会把前面的binlog都刷盘,而且事务是不会跨binlog的
MySQL为了兼容其它非事物引擎的复制,在server层面引入了 binlog, 它可以记录所有引擎中的修改操作,因而可以对所有的引擎使用复制功能。Mysql在4.x后引入了binlog来取代redolog的复制策略。但引入了新的bilog和redolog的一致性问题(一个事务的提交必须写redolog和binlog,mysql就是通过XA来解决这一问题的。然后又通过binlog的组提交来解决了write/sync binlog带来的性能问题)。
2.3 行锁的实现方式
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。
由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的
当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。如果不同的索引碰巧都落到了同一个行上,那么同样会阻塞。
即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。
2.4 B+树存放数据计算
B+树作为INNODB的存储结构能存放多少数据?怎么计算的?
先计算单个叶子节点的大小为16k,假如一条记录为1K大小,那么记录数为 16条。然后计算非叶子节点能存放多少指针,假如ID为bigint类型,长度为8字节,那么指针大小在innoDB中为6个字节,加起来为14字节。那么通过页大小,即16384/(8+6)=1170个指针,所以一颗高度为2的B+树可以存放1170×16= 18720条记录。 高度为3的B+树可以存放1170×1170×16=21902400条记录, 所以主键设置不大,记录也不大很大的情况下,一般高度为3的B+树,就能满足千万级的数据存储。
具体计算可为16K/(主键大小+6(innodb的指针)) ×二次方 ×(16K/每条记录大小(innodb的B+和myISAM的B不一样的,它的聚簇索引的数据和主键索引是一起存储的))
2.5 为什么说表的数据量很大了以后会变慢很多?
索引原理,为什么使用索引后会变得很快?
索引做了些什么可以让我们查询加快速度呢?其实就是将无序的数据变成有序(相对)
分析了MySQL的索引结构的实现原理,然后我们来看看具体的存储引擎怎么实现索引结构的,MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB,分别实现了非聚簇索引和聚簇索引。
聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序
非聚簇索引的解释是:索引顺序与数据物理排列顺序无关
(这样说起来并不好理解,让人摸不着头脑,清继续看下文,并在插图下方对上述两句话有解释)
首先要介绍几个概念,在索引的分类中,我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”,使用主键键值建立的索引称为“主索引”,其它的称为“辅助索引”。因此主索引只能有一个,辅助索引可以有很多个。
MyISAM存储引擎采用的是非聚簇索引,非聚簇索引的主索引和辅助索引几乎是一样的,只是主索引不允许重复,不允许空值,他们的叶子结点的key都存储指向键值对应的数据的物理地址。
非聚簇索引的数据表和索引表是分开存储的。
非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。
只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身,辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好,类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找,可以有更少的磁盘I/O,加快查询速度。但是也是因为这个原因,聚簇索引的插入顺序最好按照主键单调的顺序插入,否则会频繁的引起页分裂,严重影响性能。
在InnoDB中,如果只需要查找索引的列,就尽量不要加入其它的列,这样会提高查询效率(即覆盖索引)。
下图可以形象的说明聚簇索引和非聚簇索引的区别
从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值,主索引的叶子节点的data存储的是数据本身,也就是说数据和索引存储在一起,并且索引查询到的地方就是数据(data)本身,那么索引的顺序和数据本身的顺序就是相同的;
而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址,也就是说索引和数据并不是存储在一起的,数据的顺序和索引的顺序并没有任何关系,也就是索引顺序与数据物理排列顺序无关。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞4
添加新评论0 条评论