• 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html
  • 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html

InnoDB如果没有主键或者随机主键真的很可怕吗?

技术杂谈 勤劳的小蚂蚁 3个月前 (01-08) 118次浏览 已收录 0个评论 扫描二维码

没有主键真的可怕吗

讨论这个问题之前,我们先大概说明一下InnoDB的一些行为。我们都知道每个InnoDB存储引擎表都有一个聚簇索引,在有主键的情况下,主键索引就是这个聚簇索引。MySQL官方文档(14.6.2.1 Clustered and Secondary Indexes)有说明:
如果表没有主键,甚至都没有唯一键索引的话,InnoDB内部会基于一个包含了ROW_ID值的列生成一个隐式的聚簇索引,行都会根据这个ROW_ID排序。ROW_ID是一个6个字节,即48位的单调递增字段。有新数据插入时,就会生成一个新的递增的ROW_ID。所以,根据ROW_ID排序的行,本质上是按照插入顺序排序。
需要说明的是,这个单调递增的ROW_ID,和我们平时申明主键时指定AUTO_INREMENT的实现逻辑是完全不一样的。
  • 隐式ROW_ID实现
这个在既没有主键,也没有一个非空唯一键的InnoDB表中自动添加的被称为ROW_ID的列,既不能被任何查询访问,也不能被内部(例如基于行的复制)使用。
更坑爹的是,所有用ROW_ID列的表,共享同一个被保存在数据字典中的全局序列数,且下一个将要使用的值被保存在系统表空间的page 7(type SYS),数据字段头里(字段名为DICT_HDR_ROW_ID)。
这个全局序列计数器被dict_sys->mutex保护它的线程安全问题,在include/dict0boot.ic中的核心实现源码如下:
UNIV_INLINE
row_id_t
dict_sys_get_new_row_id(void)
/*=========================*/
{
    row_id_t        id;
    mutex_enter(&(dict_sys->mutex));
    id = dict_sys->row_id;
    if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {
            dict_hdr_flush_row_id();
    }
    dict_sys->row_id++;
    mutex_exit(&(dict_sys->mutex));
    return(id);
}
你可能注意到了,这段代码对row_id只是一味的递增,没有任何48位溢出的保护。事实上也没有必要,因为即使只有48位,假设每秒插入10万次,需要90年才会耗尽ROW_ID的48位空间。所以,基本上够用!
  • 如何保证不冲突
另外我们从这段代码可知,每生成256次ROW_ID,计数器就会被刷到磁盘持久化(dict_hdr_flush_row_id()),这个频率通过字段DICT_HDR_ROW_ID_WRITE_MARGIN定义,并且被保存在事务日志中。在启动的时候,InnoDB将保存在磁盘上的DICT_HDR_ROW_ID增加256,这样就能确保已经生成的ID,不管是否被持久化到磁盘上,都会小于新生成的值,所以生成的ID不会有任何冲突。
  • 性能问题
InnoDB中很多地方的代码,包括刚才提到的ROW_ID的生成都是通过dict_sys->mutex保证线程安全问题,因此,我可以说任何用ROW_ID作为隐式聚簇索引键的表,都可能随机性的碰到插入停顿问题。多张这种表并行插入就会遇到性能限制,因为共享计数器的共享互斥锁和缓存争用是串行的。此外,每生成256个ID就会需要日志写入和刷新,这些都会引起性能毛刺问题。

随机主键真的可怕吗

许多人可能知道基于随机顺序构建索引效率一般般。然而,很少有人知道到底为什么。前面介绍的innodb_ruby在一些“-illustrate”模式下,能轻而易举的可视化索引结构。比如page-illustrate可以可视化索引页里中的内容。而space-lsn-age-illustrate可以根据“LSN age”可视化索引的所有页,并基于最近每页被修改的信息生成一些类似heatmap(Heat map wiki: https://en.wikipedia.org/wiki/Heat_map)图。
我们要证明随机主键不好,只需要借助illustrate模式,对比InnoDB中顺序和随机写入即可。
  • 顺序主键
以主键顺序插入行数据效率更高的两个主要原因:
  1. 页能被更充分的填满。数据库能感知”批量加载”行为,并且能更高效的通过创建一个新的空白页来达到裂变的目的,而不是把页分裂为两半。
  2. 只有索引的边(edge)会被继续写入。一旦一个页被写满,它就不会被重新访问(意思是接下来的写入再也不会被写入之前已经写满的页)。这样就能充分的利用buffer pool来缓存索引页,从而提高效率。
下面是一个只有一个主键聚簇索引的表,以主键顺序插入行数据(主键自增即可),然后执行命令innodb_space -s ibdata1 -T yyfax_afei/t_afei space-lsn-age-illustrate得到如下图所示:
insert in key order
如上图所示,首先看图的最下方Min LSN->Max LSN的颜色变化,几乎就是page 64->1728的颜色变化,说明整个写入过程以先写编号较小的页,再写编号较大的页这种几乎完美顺序的方式写入。
  • 随机主键
索引KEY以顺序方式构建索引几乎是完美的,那么随机方式呢?我们接下来再看看索引KEY以随机方式构建索引时怎么样的,先创建一张表:
— 创建表
DROP TABLE IF EXISTS rand_afei;
CREATE TABLE rand_afei (
  id int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  num int not null
ENGINE=InnoDB;
然后通过如下代码进行随机插入一一代码比较简单,将0~100000这一百万个id随机插入(开始笔者本打算定义主键id为varchar类型,然后循环插入uuid()为id的值,后面发现uuid()的值是趋势递增的,所以这种方案不可行):
@Rollback(false)
@Test
public void saveBatch(){
    int total = 1000000;
    int batch = 1000;

    // 准备需要插入的所有数据
    List<Integer> randList = new ArrayList<>(total);
    for (int i = 0; i < total; i++) {
        randList.add(i);
    }

    for (int i = 0; i < total/batch; i++) {
        List<TestAfei> afeiList = new ArrayList<>();
        for (int j = 0; j < batch; j++) {
            TestAfei afei = new TestAfei();
            int index = new Random().nextInt(randList.size());
            afei.setId(randList.get(index));
            afei.setNum(i*j);
            afeiList.add(afei);
            randList.remove(index);
        }
        // 每次插入1000条数据
        afeiMapper.saveBatch(afeiList);
    }
}
然后再次执行执行命令innodb_space -s ibdata1 -T yyfax_afei/rand_afei space-lsn-age-illustrate,得到如下图所示:
insert in key random
因为数据行完全以随机顺序插入,所以每个索引页都有均等的机会被插入。这就意味着,实际上每个索引页最近都被修改过(即都有新的数据插入),通过上图也能很清晰的看出来,每个page颜色都非常接近。
这也意味着整个表所有的页必须连续出现在缓冲池子(buffer pool)中,如果缓冲池容纳不下,就意味着一些页无法被缓存,性能将受到很大影响,事实上一般大表的B+Tree索引树体积也很大,不大可能所有页都被缓存,这才是随机插入性能糟糕的原因。
  • 页填充率
通过对比顺序插入和随机插入我们发现,同样是写入100w数据量,随机写入完成后,总page数有2688,远远大于顺序写入总page数1728。因为随机写入导致很多页频繁的裂变,很多页并没有写满从而导致很低的填充率,这些不仅会影响插入性能,还会额外占用很多磁盘空间。我们可以通过innodb_ruby命令的space-extents-illustrate模式查看索引页填充率从而验证这一点:
insert by key order
insert by key random

总结

所有,通过上面的分析可知:
  • 如果没有主键,并且也没有一个趋势递增的唯一键,那么所有这些表都会依赖一个全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引,这就会导致竞争从而引起性能问题。
  • 如果随机主键,那么所有页都会被频繁写入,从而导致无法高效的缓存页。并且频繁的裂变还会导致页填充率不理想,从而额外占用很多的磁盘空间。
参考:
  • 14.6.2.1 Clustered and Secondary Indexes
  • How does InnoDB behave without a Primary Key?
  • Visualizing the impact of ordered vs. random index insertion in InnoDB


    丨极客文库, 版权所有丨如未注明 , 均为原创丨
    本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
    转载请注明原文链接:InnoDB如果没有主键或者随机主键真的很可怕吗?
    喜欢 (0)
    [247507792@qq.com]
    分享 (0)
    勤劳的小蚂蚁
    关于作者:
    温馨提示:本文来源于网络,转载文章皆标明了出处,如果您发现侵权文章,请及时向站长反馈删除。

    您必须 登录 才能发表评论!

    • 精品技术教程
    • 编程资源分享
    • 问答交流社区
    • 极客文库知识库

    客服QQ


    QQ:2248886839


    工作时间:09:00-23:00