登录
转载

为什么InnoDB表要建议用自增列做主键而MyISAM却不需要

发布于 2021-04-07 阅读 24
  • 数据库
  • SQL
转载

在创建数据库的时候我们总是会创建一个自增的主键ID,可是你想过没有?这个自增的ID为什么要设置?可不可以不设置,不设置的后果是什么?带着这来两个疑问我们来验证一下。

CREATE TABLE `student_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuname` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB ;

CREATE TABLE `student_table_2` (
  `stuname` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL
) ENGINE=InnoDB ;

创建两个数据库,如下添加一部分数据,发现增删改查好像也没有什么太大的区别。

CREATE DEFINER=`root`@`localhost` PROCEDURE `student_insert_procedure`(count INT)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE days INT;#当前日期加上的随机天数
    WHILE n < count DO
        SET days = FLOOR(RAND() * 6);
        INSERT INTO student_table (`stuname`, birthday,create_time,update_time) VALUES
        (FLOOR(RAND() * 15),NOW(),NOW(),NOW());
        SET n = n + 1;
    END WHILE;
COMMIT;
END

call student_insert_procedure(1000000);

在innodb引擎中,那是不是我们的结论就是可以不用创建自增的ID???

原因是:在InnoDB存储引擎中,使用聚簇索引。14.6.2.1 Clustered and Secondary Indexes,mysql的官方文档可以知道:

1.在PRIMARY KEY表上定义a时,InnoDB将其用作聚簇索引。为您创建的每个表定义一个主键。如果没有逻辑唯一且非空的列或列集,请添加一个新的 自动递增 列,其值将自动填充。

2.如果您没有PRIMARY KEY为表定义,MySQL会UNIQUE在所有键列所在的位置找到第一个索引,NOT NULL并将 InnoDB其用作聚集索引。

3.如果表没有索引PRIMARY KEY或没有合适的 UNIQUE索引,则在InnoDB 内部生成一个隐藏的聚集索引GEN_CLUST_INDEX,该索引在包含行ID值的合成列上命名 。
这些行由InnoDB分配给该表中各行的ID排序 。行ID是一个6字节的字段,随着插入新行而单调增加。因此,按行ID排序的行实际上在插入顺序上。

innodb中索引的这种设置,导致上面的两张表在本质上没有什么区别。

innodb如果有2、3这种两种策略,我们为什么要自己设置一个?这个隐藏的row_id有什么缺点呢?

1.这个row_id是一个隐藏的列,既不能被任何查询访问,也不能被内部使用。可以说对我们的任何操作都没有什么用途。

2.所有用ROW_ID列的表,共享同一个被保存在数据字典中的全局序列数,且下一个将要使用的值被保存在系统表空间的page 7(type SYS),数据字段头里(字段名为DICT_HDR_ROW_ID),自增主键源码:

#define DICT_HDR_ROW_ID_WRITE_MARGIN 256

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);
}

1.从mysql的源码中我们可以看出来:dict_sys->row_id++;值递增的。

2.当增加到256的倍数的时会刷新到硬盘中,(dict_hdr_flush_row_id()),这个频率通过字段DICT_HDR_ROW_ID_WRITE_MARGIN定义。在启动的时候,InnoDB将保存在磁盘上的DICT_HDR_ROW_ID增加256,这样就能确保已经生成的ID,不管是否被持久化到磁盘上,都会小于新生成的值,所以生成的ID不会有任何冲突。

缺点:

ROW_ID的生成都是通过dict_sys->mutex保证线程安全问题,因此,我可以说任何用ROW_ID作为隐式聚簇索引键的表,都可能随机性的碰到插入停顿问题。多张这种表并行插入就会遇到性能限制,因为共享计数器的共享互斥锁和缓存争用是串行的。此外,每生成256个ID就会需要日志写入和刷新,这些都会引起性能毛刺问题。

因此系统的自定义主键就有一点鸡肋了,那么随机的主键以及自增的主键有什么区别呢

1.在mysql中存储的基本单元是data page,当没有空闲页的时候刷入到磁盘中,当批量的数据进行操作的过程中就会涉及到页的分裂以及合并的过程,页的分裂并不是把页一分为二,而是创建空白页达到裂变的可能。

假设主键是随机设置的,就会出现如下的可能:page10是20、21、22、23、24、25、26。而page11索引是28、29、30、31、32、33、34,此时如果27需要插入的话,page10、page11页同时都满了,就需要新创建一个page12来存储27这个数据,同时原来的page10需要判断在哪拆分,移动数据定义各个页数之间的关系:

page10

 

 

page11

 

 

page12

页面之间的关系,原来page 10-->page11

修改之后 page 10 -->page 12 -->page11

如果是随机的主键就会操作大量的页分裂导致的数据迁移,造成大量的时间浪费

2.自增主键:一旦一个页被写满,它就不会被重新访问(意思是接下来的写入再也不会被写入之前已经写满的页)。这样就能充分的利用buffer pool来缓存索引页,从而提高效率。

总结:

如果没有主键,并且也没有一个趋势递增的唯一键,那么所有这些表都会依赖一个全局序列计数器生成的ROW_ID来构造一个隐式聚簇索引,这就会导致竞争从而引起性能问题。

如果随机主键,那么所有页都会被频繁写入,从而导致无法高效的缓存页。并且频繁的裂变还会导致页填充率不理想,从而额外占用很多的磁盘空间。  

至于innodb为什么非要设置而myisam可以不用设置:

原因:

两个概念

  • 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据
  • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因

1.innodb是聚簇索引,所用的二级索引都是直接关联的自增ID

2.MyISAM按照插入的顺序把值保存在磁盘上,由于行的尺寸是固定的,MyISAM能从表开头跳过所需的字节找到需要的行

回复  8888可以领取面试资料,感谢各位小伙伴的关注

评论区

admin
9粉丝

打江山易,守江山难,负重前行,持续创新。

0

0

0