V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
movq
V2EX  ›  程序员

mysql 里面会自动为没有主键的表创建聚簇索引吗?

  •  
  •   movq · 2022-12-01 09:54:03 +08:00 · 1091 次点击
    这是一个创建于 722 天前的主题,其中的信息可能已经有所发展或是发生改变。

    在某个视频里面看到说

    如果有主键,会为主键生成聚簇索引;

    如果没有主键但有 unique index ,会使用 unique index 作为聚簇索引

    如果以上都没有,就会自动用 row id 作为聚簇索引


    但是也有完全没有任何索引的表吧?

    比如我自己试了下,一个表如果没有主键也没有 unique index ,不会自动用 row id 创建聚簇索引

    create table table_without_primary_key(
        name varchar(30) not null ,
        date datetime not null
    );
    
    insert into table_without_primary_key
    values ('jack',now());
    insert into table_without_primary_key
    values ('alice',now());
    insert into table_without_primary_key
    values ('ribbon',now());
    show index from table_without_primary_key;
    
    

    结果显示没有任何 index ,并不是自动用 row id 创建了聚簇索引

    如果此时为 date 创建一个索引,然后查询所有索引,也没看到有聚簇索引(一个问题是这个索引的叶子结点是什么内容呢?如果没有聚簇索引,那也没法回表查询了)

    create index time_index
    on table_without_primary_key (date);
    show index from table_without_primary_key;
    
    
    +---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | Table                     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
    +---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    | table_without_primary_key |          1 | time_index |            1 | date        | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
    +---------------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
    1 row in set (0.02 sec)
    
    4 条回复    2022-12-01 14:24:23 +08:00
    hex2en
        1
    hex2en  
       2022-12-01 10:05:45 +08:00
    本人纯菜鸡,说一下我的理解:以下场景仅限 innodb 。
    首先「如果没有主键但有 unique index ,会使用 unique index 作为聚簇索引」这一段可能不完全正确,应该是非 null 的 unique index 。其次如果没有指定主键和非 null 的 unique index 就会自动使用 row_id 作为聚簇索引。
    对于你的这种场景,row_id 是隐式列,是无法查询出来的,这个索引也是 show index 看不到的。
    最后你修改了表结构,在 date 列上创建了一个索引,但是 time_index 不满足 not null unique index 的条件。即使满足,按照我的理解,聚簇索引已经是 row_id 了,并不会将整颗 b+树重构到 time_index 上去。

    有错误请各位大佬指出
    wps353
        2
    wps353  
       2022-12-01 10:07:23 +08:00   ❤️ 1
    在 8.0.30 以前,如果一张表没有主键和 unique index ,系统会自动加一个主键(6 个字节?),但是是隐藏的,你是看不到的。在 8.0.30 以后,开启 sql_generate_invisible_primary_key 这个参数,你就可以看见了。https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html
    movq
        3
    movq  
    OP
       2022-12-01 10:22:40 +08:00
    刚查了下 mysql 文档,有一个自动生成的主键

    > If you do not define a PRIMARY KEY for a table, InnoDB uses the first UNIQUE index with all key columns defined as NOT NULL as the clustered index. If a table has no PRIMARY KEY or suitable UNIQUE index, InnoDB generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column that contains row ID values.

    https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
    xuanbg
        4
    xuanbg  
       2022-12-01 14:24:23 +08:00
    mysql:听说你想查询的时候自动创建索引?
    你:是的,我太想要这个功能了,这样用起来才智能。
    mysql:不,你不想要的。我当然可以做到,这种又不需要什么智能才能实现。不过……你能接受查询的时候先花上几分钟,让我想把你想要的索引创建出来吗?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5286 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 50ms · UTC 08:45 · PVG 16:45 · LAX 00:45 · JFK 03:45
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.