领导大怒：建表不加主键的都是耍流氓

    MySQL在创建表时，如果你没有显示的创建主键，那么innodb会自动帮你创建一个不可见的、长度是6字节的row_id，所有未定义主键的表共享该row_id，每次插入一条数据row_id加1。

    在创建表时，DBA一般都会建议一定要在这个表上显示的定义一个主键，那么如果没有创建主键会有哪些影响呢，今天我们就来说一下主键在主从复制中的影响以及如何选择主键。

环境准备

首先我们先创建一个没有主键和索引的表

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(30) NOT NULL
)

创建一个触发器插入10万数据

DELIMITER $$
drop procedure if exists `t_pro`$$
create procedure `t_pro`(num int)
begin
declare i int unsigned default 1;
set i=1;
 while i <= num do
  insert into `t` (`id`,`name`,`age`,`addr`)
   values(i,'主键测试',i,'大美山东，泉城济南');
  set i=i + 1;
 end while;
end$$
DELIMITER ;
--执行
call t_pro(100000);

模拟验证

主库进行修改操作

mysql> update t set name='主键测试，结果验证' where id <10000;

从库检查延迟情况

mysql> show slave status\G
*************************** 1. row ***************************
……………………………………..
Seconds_Behind_Master: 31
#通过查看延迟时间可以看到，从库最高延迟是31秒。

查看一下主库的binlog

 

    在ROW模式下，在主库上进行一次全表扫描，但是在从库上则是一条条的进行更新，由于没有主键和索引，就会导致在从库进行N次全表扫描，这样也就拖慢了从库APPLY的效率。

    前面说过在没有显式主键的情况下，innodb会创建一个6字节的row_id，但是row_id是引擎层的，它不会暴露给server层，binlog则是server层的东西，它需要兼顾MySAM等其他存储引擎，所以在没有显式主键的情况下，binlog并不知道row_id的存在。

如何选择主键

选择主键可以依据以下几个原则：

1. 确保主键的无意义性，保证主键本身不会被修改。

2. 采用整型主键，可以采用unsigned bigint类型。

3. 尽量使用单列主键，提升连接和筛选的效率。

4. 能保证数据的唯一性。

5. 尽量避免出现过度跳跃的主键值，尤其是对写性能要求较高的系统。

6. 应该由计算机自动生成。

    单机模式下可以采用自增ID作为主键的方案，该方案的优点是顺序存放，插入速度快，数字类型，占用空间小，易排序，但是该方案也有缺点，不支持分布式架构，在分布式数据库架构中不能保证主键的全局唯一。

    在分布式数据库中可以采用自增ID作为物理主键，UUID作为逻辑主键的方案，或者自定义实现主键值的获取，来保证分库分表后的全局唯一性，比如淘宝的分布式数据库中间件TDDL提供的Sequence解决方案等。