• [高清组图]环广西赛:参赛车队赛前适应训练 2018-08-31
  • [高清组图]特谢拉复出吴曦失单刀 苏宁0-0平斯威 2018-08-31
  • [高清组图]潜水偶遇座头鲸 亲密互动玩起“水中击掌” 2018-08-31
  • [高清组图]法拉利拍定妆照 维特尔KIMI准备好了 2018-08-31
  • [高清组图]毛剑卿伤退莫雷诺捅射 申花1-0一方 2018-08-31
  • [高清组图]比埃拉双响巴坎布建功 国安5-1富力 2018-08-31
  • [高清组图]武磊世界波胡尔克点射 上港2-0胜申花 2018-08-31
  • [高清组图]武磊2球吕文君建功 上港3-1富力 2018-08-31
  • [高清组图]欧超杯-科斯塔2球 皇马加时赛2-4马竞 2018-08-31
  • [高清组图]格里芬赤膊骑行 休赛期享受二人世界 2018-08-31
  • [视频]【深化改革 重在实效】精准扶贫 四川彝区要拔掉“穷根” 2018-08-31
  • [视频]【深化改革 重在实效】破藩篱促合力 体制创新粘合“两张皮” 2018-08-31
  • [视频]【深化改革 重在实效】激发活力 实现市场准入全程便利化 2018-08-31
  • [视频]【深化改革 重在实效】打通简政放权的“最后一公里” 2018-08-31
  • [视频]【深化改革 重在实效】广东:户籍改革为外来工打开一扇门 2018-08-31
  • 手机版
    你好,游客 登录 注册 搜索
    背景:
    阅读新闻

    MySQL表为什么必须有主键 - 关于聚集索引的简介

    [日期:2018-08-13] 来源:CSDN  作者:jhgdike [字体: ]

    奥门新萄京官方正版 www.arianalance.com 注意:下面讨论的都是MySQL5.6版本中的innodb引擎。

    比较规范的数据库表设计(包括我们公司)都会有一条不成文的规定,那就是给每张表一个自增主键。那么自增主键除了有数据的唯一性外,还有什么所用呢?为什么要有自增主键?

    解释:

    • 主键递增,数据行写入可以提高插入性能,可以避免page分裂,减少表碎片提升空间和内存的使用
    • 主键要选择较短的数据类型, Innodb引擎普通索引都会保存主键的值,较短的数据类型可以有效的减少索引的磁盘空间,提高索引的缓存效率
    • 无主键的表删除,在row模式的主从架构,会导致备库夯住

    第三条先不必关注,我们来看看前两条。
    为什么能提高插入性能呢,避免page分页又是怎么回事?

    这里就不得不说一下聚集索引了。


    聚集索引(Clustered Index)

    一个聚集索引定义了表中数据的物理存储顺序。如何理解聚集索引呢,好比一个电话本,比如一个电话本是按照姓氏排序,并且电话号码紧跟着后面。因为聚集索引决定了表中数据的物理存储顺序,那么一个表则有且只有一个聚集索引。一个聚集索引可以包含多个列。好比一个电话本是基于名字,姓氏同时排序。

    Innodb的聚集索引

    Innodb的存储索引是基于B+tree,理所当然,聚集索引也是基于B+tree。与非聚集索引的区别则是,聚集索引既存储了索引,也存储了行值。当一个表有一个聚集索引,它的数据是存储在索引的叶子页(leaf pages)。因此innodb也能理解为基于索引的表。

    * 那么Innodb如何决定那个索引作为聚集索引呢?*


    Innodb如何选择一个聚集索引

    对于Innodb,主键毫无疑问是一个聚集索引。但是当一个表没有主键,或者没有一个索引,Innodb会如何处理呢。请看如下规则

    如果一个主键被定义了,那么这个主键就是作为聚集索引

    如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引

    如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。

    还有一个需要注意的是:

    次级索引的叶子节点并不存储行数据的物理地址。而是存储的该行的主键值。

    所以:一次级索引包含了两次查找。一次是查找次级索引自身。然后查找主键(聚集索引)


    现在应该明白了吧,建立自增主键的原因是:

    Innodb中的每张表都会有一个聚集索引,而聚集索引又是以物理磁盘顺序来存储的,自增主键会把数据自动向后插入,避免了插入过程中的聚集索引排序问题。聚集索引的排序,必然会带来大范围的数据的物理移动,这里面带来的磁盘IO性能损耗是非常大的。
    而如果聚集索引上的值可以改动的话,那么也会触发物理磁盘上的移动,于是就可能出现page分裂,表碎片横生。

    解读中的第二点相信看了上面关于聚集索引的解释后就很清楚了。


    虽然遵循上面的原则也没错,但某些特殊的情况也是可以自己指定一些非自增主键为聚集索引的。如:

    • 当数据量大,但长时间不会被更新的;
    • 新生成的数据的索引本来就是按照自增的顺序增加的等等。

    举个栗子,(只是栗子啊,现实中不太可能):
    有一家公司里的员工上百万,有关员工的个人信息几年都不会发生变化,那么以员工的user_id号来作为各个表的索引就很适合。因为一个员工的信息都按照物理顺序呢排列在一起了,避免了磁盘移动查找数据的IO时间。比如说员工属于几个部门,一下子就查到了,不同分不同的地址去挨个进行磁盘扫描。

    栗子从简,哈哈哈哈,就这样子吧,我也不太会写,简单的记录下来了~

    Linux公社的RSS地址:http://www.arianalance.com/rssFeed.aspx

    本文永久更新链接地址http://www.arianalance.com/Linux/2018-08/153490.htm

    linux
    本文评论   查看全部评论 (0)
    表情: 表情 姓名: 字数

           

    评论声明
    • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
    • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
    • 本站管理人员有权保留或删除其管辖留言中的任意内容
    • 本站有权在网站内转载或引用您的评论
    • 参与本评论即表明您已经阅读并接受上述条款