MySQL 技术内幕:InnoDB 存储引擎

首先推荐下Draveness's Blog,他的文章深入浅出,很有学习价值,每篇文章我都会很认真地去学习。前面他写了好几篇关于InnoDB的文章,看完后意犹未尽,不过还是有些地方不是很明白。所以决定自己动手,把MySQL 技术内幕:InnoDB 存储引擎看完,解开了不少疑惑。

首先先提几个小问题:

  1. InnoDB的逻辑存储结构和磁盘的物理存储结构的关系?
  2. InnoDB存储引擎为什么在绝大多数情况下选择B+树建立索引?
  3. 插入根据自增顺序进行,B+树分裂会导致什么问题?
  4. 行锁和表锁的区别,有了S,X锁后为什么还需要IS,IX锁,可以解决什么问题?
  5. 为什么说InnoDB锁资源开销小,不存在锁升级的问题?
  6. 为什么有些人认为在循环中提交事务不好,有些人认为好?对于InnoDB,哪种操作好呢?

InnoDB的逻辑存储结构

  • 首先要确定,页是InnoDB磁盘管理的最小单元,默认大小为16KB,也可以设置为4KB或者8KB。像B+树索引里的一个个节点就是一张张数据页。
  • 页里的数据是按行存放的,最多允许存放7992行记录,这一行行数据已链表的形式存放。
  • InnoDB一次会从磁盘申请4-5个区,在任何情况下每个区的大小都是1MB。所以默认情况下,一个区里有64个连续的页。

以上是概念,所以InnoDB的逻辑存储结构和磁盘的物理存储结构的关系就是:每次InnoDB申请空间时,都是从磁盘载入连续的64页,这些页都是物理地址连续的。但随着不停地增删改,比如B+索引里每个逻辑相邻的页在物理上是不相邻的。

一直说索引,select一定范围的数据,是顺序从磁盘读取的,读取速度比随机访问快。这样就有了疑问,既然是不相邻的,那不是每次select一定范围的数据,如果横跨几个页,就不是理论上磁盘的顺序读取了。不过一般根据索引计算理论读取时间并不会算这部分开销。

这边要多说一句,其实索引占用磁盘的空间非常小,一般索引除了叶子节点外其余都在内存里,1亿条数据占的内存也就400MB左右。所以一般主键访问或者辅助访问,也就最后在叶子节点那一步要去磁盘读。

B+树索引

B+树索引的概念就不讲了,网上太多了。为什么选择B+树而不选择B树?主要就是B+树的非叶子节点不用保存Data,而每个节点就是一页的大小16KB。所以B+树每个节点存放的索引值要比B树多很多。也就是出度大,通俗点讲就是Son多。这样整个树的深度就小。
而B+树的索引次数就是树的深度,所以InnoDB存储引擎选择B+树建立索引。

以上是概念,但其实真正在使用中索引次数并不是树的深度。因为除了叶子节点外,别的索引所在页量很小,基本上都一直内存呆着,所以一般也就1次需要磁盘访问。
而B树因为每个节点都存了数据,所以有些数据在内存里就读了,有些可能要频繁地去磁盘读,所以性能很不均衡,这才是选取B+树的原因。

InnoDB的索引分裂策略,在特定的情况下,索引页面的分裂存在问题,导致每个分裂出来的页面,仅仅存储一条记录,页面的空间利用率极低。
这篇博客讲的非常清楚,主要就是每次分裂出来的页面之后不会再存储数据,解决这个问题的关键就是节点分裂点的寻找,这个InnoDB已经有相应的算法优化了。

行锁和表锁

对于为什么有了S,X锁之后还要有IS,IX着实想了好久。主要原因还是看书不仔细,还有网上的冲突兼容表格的误导。

有的人可能会对意向锁的目的并不是完全的理解,我们在这里可以举一个例子:如果没有意向锁,当已经有人使用行锁对表中的某一行进行修改时,如果另外一个请求要对全表进行修改,那么就需要对所有的行是否被锁定进行扫描,在这种情况下,效率是非常低的;不过,在引入意向锁之后,当有人使用行锁对表中的某一行进行修改之前,会先为表添加意向互斥锁(IX),再为行记录添加互斥锁(X),在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了,只需要通过等待意向互斥锁被释放就可以了。

Lock-Type-Compatibility-Matrix

上面的引用和图是从『浅入浅出』MySQL 和 InnoDB拷过来的。
例子和图都没错,但是有很强的误导性。最早我的疑惑在这里,既然加了IX,那我想改或者读没被X锁的行怎么办?InnoDB怎么知道我要读或者写的行会和X锁的行一样,凭什么加了IX我就不能进了。那还要X锁干嘛,全部用IX把表锁住就行啊。

说到底还是理解错了。

首先书上的定义:

  • 共享锁(S),允许事务读一行数据
  • 排他锁(X),允许事务删除或者更新一行数据
    这两种锁都是标准的行级锁。

就是这个行级锁误导了我,让我觉得S,X只能行级别锁定。导致我后面什么都想不通。
其实S,X也能锁表的,当要对全表进行读取或者修改时就对整张表加S或者X锁。
所以上面的例子其实是这样,当对一行进行修改时,会给这行数据加一个X锁,给表加一个IX锁。之后还有修改的,也会给表加一个IX,IX和IX本身是兼容的,所以可以进去修改。而像全表扫这种是直接给表加X锁的,这个和IX是不兼容的,所以要等待。

所有的疑惑就是对全表查或者修改,是对表加的S或者X锁,S或者X锁不是只能给行加锁!!!

锁信息存放

SQL Server数据库中,每个行记录都维护锁,每个所占用10B,那么数据很多的情况下,锁占用了太多的内存,所以就会锁升级为页锁,这样锁的个数会大幅度减少。不过锁的粒度变大,并发性能会降低。

而InnoDB根据页进行加锁,采用的是位图方式,每个页维护这个页的所有锁信息,占用空间小,不需要锁升级

mysql_page_lock

循环提交事务

  1. 每次循环都commit一次,这样每一次提交都要写一次重做日志,执行时间太久。
  2. 整个循环结束后commit一次,如果while循环次数很多,就成了长事务。这样如果发生宕机等情况,需要回滚所有已经发生的变化。所以一般长事务可以通过转化为小批量的事务来进行处理。

InnoDB书上给出的结论是程序员不论从何种角度出发,都不应该在一个循环中反复进行提交操作。
不过我觉得真的循环次数过多,可以把整个循环分成n个组,转化为一个个循环次数不多的组做为小事务来执行,做个折中。

感想

其实看完InnoDB存储引擎,会发现它其实也是在不停地取舍和折中。感叹一下,程序世界真的没有银弹,不同条件下会有不同的解法,所以需要不停地取舍,用来达到最满意的效果。

作者:levi
comments powered by Disqus