专栏首页国产程序员MySql中InnoDB表为什么要建议用自增列做主键

MySql中InnoDB表为什么要建议用自增列做主键

InnoDB引擎表的特点

1、InnoDB引擎表是基于B+树的索引组织表(IOT)

关于B+树

B+ 树的特点:

  • 所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
  • 不可能在非叶子结点命中;
  • 非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;

2、主键(PRIMARY KEY)

如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

3、主索引

数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)

4、自增主键

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

5、非自增主键

如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

总结

如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的,也就是下面这几种情况的存取效率最高: 1、使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;

2、该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致; 除此以外,如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。

《高性能MySQL》中的原话

本文分享自微信公众号 - 国产程序员(Monday_lida),作者:Monday

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 数据库主键和外键

    聚集索引,在索引页里直接存放数据,而非聚集索引在索引页里存放的是索引,这些索引指向专门的数据页的数据。

    一觉睡到小时候
  • GSON搞定任何JSON数据

    一觉睡到小时候
  • 幸存者偏差

    幸存者偏差(英语:survivorship bias),另译为“生存者偏差”,是一种认知偏差。其逻辑谬误表现为过分关注于目前人或物“幸存了某些经历”然而往往忽略...

    一觉睡到小时候
  • [zz学习]MySQL索引背后的数据结构及算法原理MySQL索引背后的数据结构及算法原理MyISAM索引实现InnoDB索引实现

    虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,...

    一个会写诗的程序员
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    remcarpediem
  • MySQL探秘(七):InnoDB行锁算法

     在上一篇《InnoDB一致性非锁定读》中,我们了解到InnoDB使用一致性非锁定读来避免在一般的查询操作(SELECT FOR UPDATE等除外)时使用锁。...

    aoho求索
  • 浅析MySQL的锁机制

    数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访问变得有序所设计的一种规则;对于任何一种数据库来说都需要有相应的锁定机制,My...

    动力节点Java学院
  • 『教程』来自小程序开发者的实例教程

    最近由马伊琍、靳东男神主演的《我的前半生》火到不行,剧中人物的爱恨纠葛,简直让大批剧迷深陷其中,不能自拔!而女主人公罗子君的前半生,更让剧迷们操碎了心。 就没人...

    极乐君
  • 新的人工智能在模拟战斗中击败人类专家

    大数据文摘
  • 并发容器和框架之ConcurrentHashMap

    了解HashMap的人都知道HashMap是线程不安全的(多线程下的put方法达到一定大小,引发rehash,导致闭链,最终占满CPU),同时线程安全的Hash...

    MindMrWang

扫码关注云+社区

领取腾讯云代金券