总结
没有主键怎么办?
- 如果定义了主键,那么InnoDB会使用主键作为聚簇索引
- 如果没有定义主键,那么会使用第一非空的唯一索引(NOT NULL and UNIQUE INDEX)作为聚簇索引
- 如果既没有主键也找不到合适的非空索引,那么InnoDB会自动生成一个不可见的名为ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引,该列是一个6字节的自增数值,随着插入而自增
自动生成的主键有什么问题?
- 使用不了主键索引,查询会进行全表扫描
- 影响数据插入性能,插入数据需要生成ROW_ID,而生成的ROW_ID是全局共享的,并发会导致锁竞争,影响性能
详细文章
问题
MySQL数据表使用InnoDB作为存储引擎的时候,数据结构就是使用B+树,而数据本身存储在主键索引上,也就是通常所说的聚簇索引,也就是每个表都需要有个聚簇索引树,但是,在建表的时候却发现可以不用指定主键,那么MySQL对于没有指定主键的表示如何处理的呢?
InnoDB索引
对于InnoDB,可以简单地把所有数据视为索引,每一个索引都对应一个B+数,而主键对应的索引就是聚簇索引,表的所有数据都存储在聚簇索引上,而除了聚簇索引的普通索引存储的只是主键的引用,所以,查询的时候对于普通索引需要进行回表才能取到具体数据。
缺少主键MySQL如何处理
既然InnoDB对数据的存储必须依赖于主键,那么对于没有创建主键的表,该怎么办?
InnoDB对聚簇索引处理如下: - 如果定义了主键,那么InnoDB会使用主键作为聚簇索引 - 如果没有定义主键,那么会使用第一非空的唯一索引(NOT NULL and UNIQUE INDEX)作为聚簇索引 - 如果既没有主键也找不到合适的非空索引,那么InnoDB会自动生成一个不可见的名为ROW_ID的列名为GEN_CLUST_INDEX的聚簇索引,该列是一个6字节的自增数值,随着插入而自增
很明显,缺少主键的表,InnoDB会内置一列用于聚簇索引来组织数据。而没有建立主键的话就没法通过主键来进行索引,查询的时候都是全表扫描,小数据量没问题,大数据量就会出现性能问题。
但是,问题真的只是查询影响吗?不是的,对于生成的ROW_ID,其自增的实现来源于一个全局的序列,而所以有ROW_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对数据的存储和处理都是基于聚簇索引的,那么,在建表时候要注意主键的重要性,为每个表都设置一个主键,如果没有合适的字段来作为主键,可以设置一个业务无关的的代理主键,可以是自增ID,也可以是UUID(建议使用自增ID,性能较好)。
总结
在理解InnoDB的数据结构之后自然而然就会知道主键的重要性,在建表的时候也不会忘记设置主键,无论表设计有无合适的唯一字段,都需要设置一个主键,提高性能的同时也是一种好的习惯,对于后续的拓展以及表之间关联都有一定的拓展性。
参考
https://zhuanlan.zhihu.com/p/98084061