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