MySQL InnoDB数据表缺少主键会怎样

InnoDB索引

MySQL数据表使用InnoDB作为存储引擎的时候,数据结构就是使用B+树,而表的所有数据存储在主键索引上,也就是通常所说的聚簇索引,也就是每个表都需要有个聚簇索引树,对于InnoDB,主键对应的索引就是聚簇索引,表的所有数据都存储在聚簇索引上,而除了聚簇索引的普通索引存储的只是主键的引用,所以,查询的时候对于普通索引需要进行回表才能取到具体数据。

缺少主键MySQL如何处理

既然InnoDB对数据的存储必须依赖于主键,那么对于没有创建主键的表,该怎么办?

InnoDB对聚簇索引处理如下: - 如果定义了主键,那么InnoDB会使用主键作为聚簇索引 - 如果没有定义主键,那么会使用第一非空的唯一索引(NOT NULL and UNIQUE INDEX)作为聚簇索引 - 如果既没有主键也找不到合适的非空索引,那么InnoDB会自动生成一个包含了ROW_ID值的列作为聚簇索引,行都会根据这个ROW_ID排序。ROW_ID是一个6个字节,即48位的单调递增字段。有新数据插入时,就会生成一个新的递增的ROW_ID。所以根据ROW_ID排序的行,本质上是按照插入顺序排序。很明显,缺少主键的表,InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引,查询的时候都是全表扫描,小数据量没问题,大数据量就会出现性能问题。

隐式ROW_ID实现

这个在既没有主键,也没有一个非空唯一键的InnoDB表中自动添加的被称为ROW_ID的列,既不能被任何查询访问,也不能被内部(例如基于行的复制)使用。更坑爹的是,所有用ROW_ID列的表,共享同一个被保存在数据字典中的全局序列数,且下一个将要使用的值被保存在系统表空间的page 7(type SYS),数据字段头里(字段名为DICT_HDR_ROW_ID)。

这个全局序列计数器dict_sys->mutex保护它的线程安全问题,在include/dict0boot.ic中的核心实现源码如下:

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

你可能注意到了,这段代码对row_id只是一味的递增,没有任何48位溢出的保护。事实上也没有必要,因为即使只有48位,假设每秒插入10万次,需要90年才会耗尽ROW_ID的48位空间。所以,基本上够用!

  • 确保生成不冲突的ID

另外我们从这段代码可知,每生成256次ROW_ID,计数器就会被刷到磁盘持久化(dict_hdr_flush_row_id()),这个频率通过字段DICT_HDR_ROW_ID_WRITE_MARGIN定义,并且被保存在事务日志中。在启动的时候,InnoDB将保存在磁盘上的DICT_HDR_ROW_ID增加256,这样就能确保已经生成的ID,不管是否被持久化到磁盘上,都会小于新生成的值,所以生成的ID不会有任何冲突。

  • 性能问题

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

 

Returns a new row id.
 @return the new id */
 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);
 }

缺少主键或者非空索引存在问题

  • 使用不了主键索引,查询会进行全表扫描
  • 影响数据插入性能,插入数据需要生成ROW_ID,而生成的ROW_ID是全局共享的,并发会导致锁竞争,影响性能

总结

在理解InnoDB的数据结构之后自然而然就会知道主键的重要性,在建表的时候也不会忘记设置主键,无论表设计有无合适的唯一字段,都需要设置一个主键,提高性能的同时也是一种好的习惯,对于后续的拓展以及表之间关联都有一定的拓展性。

https://zhuanlan.zhihu.com/p/98084061

©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值