V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
badboy17
V2EX  ›  数据库

mysql 为什么一定要生成聚簇索引

  •  
  •   badboy17 · 2022-09-01 09:49:51 +08:00 · 3144 次点击
    这是一个创建于 574 天前的主题,其中的信息可能已经有所发展或是发生改变。

    前端时间面试被问到,Mysql 会根据主键生成聚簇索引,如果没有主键或者唯一键,也会尝试隐式生成聚簇索引,mysql 为什么要这样做

    第 1 条附言  ·  2022-09-01 14:40:35 +08:00
    几处错误,修正一下,
    1:前段时间,不是前端
    2:是 mysql 的 innodb 引擎使用了聚簇索引,myisam 使用的是非聚簇索引

    感谢指正
    23 条回复    2022-09-02 09:22:23 +08:00
    fiypig
        1
    fiypig  
       2022-09-01 09:51:01 +08:00
    ??? 卷到前端问 mysql ? 你是写 nodejs 吗
    php01
        2
    php01  
       2022-09-01 09:53:32 +08:00
    不是 mysql 。是引擎。
    Mitt
        3
    Mitt  
       2022-09-01 10:05:14 +08:00
    @fiypig #1 "前段时间"
    fiypig
        4
    fiypig  
       2022-09-01 10:15:15 +08:00
    @Mitt 哈哈哈我的错。。。
    mitu9527
        5
    mitu9527  
       2022-09-01 10:19:58 +08:00
    高性能 MySQL 5.3.5
    codewld
        6
    codewld  
       2022-09-01 10:21:20 +08:00
    可以把聚簇索引理解为数据存放点如果设置了主键,那么就根据你设置的主键放置,否则就
    codewld
        7
    codewld  
       2022-09-01 10:22:20 +08:00
    可以把聚簇索引理解为数据存放点 如果设置了主键,那么就根据你设置的主键放置数据;否则它就自己搞个 key ,自己安排怎么放
    sadfQED2
        8
    sadfQED2  
       2022-09-01 10:27:22 +08:00
    因为 mysql innodb 是索引组织表,所有数据都是挂在主键下面的,如果没有主键数据结构都没法生成了。

    另外普通索引里面存的数据也是主键的值,因此如果没有主键,普通索引也没法创建了
    mitu9527
        9
    mitu9527  
       2022-09-01 10:27:42 +08:00
    聚簇索引叶子节点是按照主键或者唯一键有序存放的,很多查询就会形成顺序 IO ,非聚簇索引是乱序存放的,查询很可能会形成随机 IO ;然后聚簇索引可以利用索引覆盖。总之聚簇索引比较有利于查询。
    不过聚簇索引要求有序,明显不利于插入;所以使用 InnoDB 存储引擎时,尽可能让所有插入语句顺序插入,可以提升插入性能。
    aladdinding
        10
    aladdinding  
       2022-09-01 10:37:25 +08:00
    innodb 会生成 不是 mysql
    anonymousar
        11
    anonymousar  
       2022-09-01 10:40:00 +08:00
    因为他就是存数据的那个 b+ tree, 有序当然可以索引。至于说什么查询快啊 什么的, 那肯定啊 直接查数据当然快了。这类问题建议直接看数据结构 /代码 原理都在那了。
    jtwor
        12
    jtwor  
       2022-09-01 10:50:48 +08:00
    innodb 底层设计是 b+树,工作原理差不多理解为表的数据都是绑在这个主键的,所以条件过滤主键是最快的,如果建表 [没有设置主键] ,引擎会 [自动生成] 类似 oralce 的 row_id 作为主键,因为工作原理就是需要主键。
    wxf666
        13
    wxf666  
       2022-09-01 11:09:28 +08:00
    不生成聚簇索引。。是打算变成 csv 么。。
    simonlu9
        14
    simonlu9  
       2022-09-01 11:16:58 +08:00
    大多数的应用场景是范围查找,局部查找,产生的随机 io 不能过多,b+树比较适合这种场景
    az467
        15
    az467  
       2022-09-01 11:43:44 +08:00
    因为聚簇索引不但是索引,还是表的结构和储存形式,而 innodb 引擎只支持索引组织表 /聚簇索引。
    相对于普通堆表,其整个表都可以看成是一个索引,不”生成”聚簇索引,就无法 blablablabla……

    啥?你问为啥这么设计,你这是另外的问题。
    ediron
        16
    ediron  
       2022-09-01 11:44:51 +08:00   ❤️ 7
    首先 MyISAM 和 InnoDB 两个引擎都是使用 B+Tree 作为索引结构,B+Tree 的特点就是数据都存放在叶子节点并且有序,但两者存储数据的形式不一样,MyISAM 的数据是以正常文件结构存储的,建立索引后叶子节点 data 域存放的是实际数据的物理地址,是属于非聚簇索引; InnoDB 的数据文件本身就是以 B+Tree 结构存储的,也就是同一个结构既保存了索引也保存了数据,这是 InnoDB 必须存在一个主键的原因(显示或隐式)。而由于 InnoDB 数据节点存储的是完整的数据,因此更新操作的改动代价就更大,为了尽量降低更新操作对索引结构的修改,同时保证叶子有序的,新增数据直接在叶子节点右侧追加即可,这是 InnoDB 使用自增连续主键的原因。
    以上是我个人的理解,如有误还请大佬指正。
    fourthLALA
        17
    fourthLALA  
       2022-09-01 12:08:35 +08:00 via iPhone
    mark ,等一波专业回答
    DonaldY
        18
    DonaldY  
       2022-09-01 13:47:02 +08:00
    回答:使用 innodb 引擎。
    zmal
        19
    zmal  
       2022-09-01 14:14:04 +08:00
    为了查找方便,就算是 kafka 这种 MQ 的 offset 也有索引,不然 seed 一个 offset 得从头遍历到尾,多傻啊。
    nothingistrue
        20
    nothingistrue  
       2022-09-01 14:15:28 +08:00
    主键跟索引是两码事。

    主键是键,在关系数据模型中是用来当做不同行之间的区分的。根据自然意义的需要,你应当从列当中选择最具代表性,且最少数量的列,当作主键。但这不是必须的,当选不出来的时候,你可以不要主键,这时候会有一个默认的候选键起作用,即所有列的组合。

    索引是在主数据之外,用来辅助查询的额外数据。这其中,如果是树索引,且将主数据直接放到树的叶子节点上,这就是聚集索引。其他情况下是非聚集索引,这时候索引数据跟主数据是分开放的,索引上放的是主数据的地址或引用。

    关系数据模型的键,不管是选择出来的主键,还是所有列组合的默认键,是一种天然的索引。这二者之间就这么点联系,其他时刻二者都是独立概念。聚集索引必然要用到这个天然索引,不然是无法将主数据跟索引放在一起的,非聚集索引就无所谓了。是否有主键,不影响是否能用聚集索引,但是是否有主键、主键是否单列、主键的值是否顺序,会极大的影响聚集索引的性能表现。

    至于聚集索引有什么好处吗,我也不知道,大概是省了一份索引的空间占用吧。缺点倒是大得要命,它让性能参与主键选择策略。
    opengps
        21
    opengps  
       2022-09-01 14:20:44 +08:00
    总是需要一个物理落盘根据的,你不生成,当然就是他自己管理了(这里需要特别强调下举例不合适的地方:磁盘地址里随处丢也算一种管理)
    sardina
        22
    sardina  
       2022-09-01 18:21:39 +08:00 via iPhone
    innodb 技术内幕
    yurman
        23
    yurman  
       2022-09-02 09:22:23 +08:00
    搜索聚集索引,先查到主键 id (没有主键的表默认也是有隐式的主键),再根据主键 id 回表查询到对应的数据。这么做就是为了减少查询次数吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3205 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 11:58 · PVG 19:58 · LAX 04:58 · JFK 07:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.