[MySQL优化案例]系列 -- InnoDB主键设计

众所周知,InnoDB是clustered-index table,因此对于InnoDB而言,主键具有特殊意义。可以通过主键直接定位到对应的某一数据行记录的物理位置,主键索引指向对应行记录,其他索引则都指向主键索引;因此,可以这么说,InnoDB其实就是一个 B-树索引,这棵B-树的索引就是主键,它的值则是对应的行记录。
在InnoDB数据表设计中,我们需要注意几点:

  • 1. 显式的定义一个 INT 类型自增字段的主键,这个字段可以仅用于做主键,不做其他用途
  • 2. 如果不显式定义主键的话,可能会导致InnoDB每次都需要对新数据行进行排序,严重损害性能
  • 3. 尽量保证不对主键字段进行更新修改,防止主键字段发生变化,引发数据存储碎片,降低IO性能
  • 4. 如果需要对主键字段进行更新,请将该字段转变成一个唯一索引约束字段,另外创建一个没有其他业务意义的自增字段做主键
  • 5. 主键字段类型尽可能小,能用SMALLINT就不用INT,能用INT就不用BIGINT
  • 6. 主键字段放在数据表的第一顺序

评论

有道理,上次我们还讨论到底是使用auto_increment id还是用逻辑上的主键,后来考虑到传值、连接、以及表的逻辑结果发生变化时这个字段不需要发生变化,就用了自增id。今天你说的这个又增加了一条理由,采用自增id可以减少insert的时间,因为主键是clustered index,调整数据调整的比较多。

自增最大的问题就是分表分库。 数据整合。 如果增加序列分发器 带来的消耗也很高。 数据存储碎片也难以消除。 主键设计是个折中的取舍。

恩.只做主键,不更新的好.
所以也不要经常用optimize table来消除innodb 主键的数据存储碎片,因为整理后,split page速率会起初更高,降低性能.

请问下,如果主键用int型但非递增性能会怎样?
情况是这样,有两张表
member:主键是user_id, int型自增
member_profile:主键是user_id, int型非递增(关联member中的user_id)
为了把text型字段拆分出来或者是不使一张表字段数太多会经常这么做,请问这样有木问题?

这种方式可以,因为member已经是符合规范设计的了,member_profile与之匹配,没有问题。
P.S,垃圾评论太多,因此加了图片验证以及审核,即使这样,还是有不少,没办法哈 :-)

这验证码太复杂了吧?刚才发的问题没显示,要通过审核吗?

不知到金大师是否注意到.innodb对主键的选择并不一定是表中的primary key.任何not null的唯一值的unique key也可以被选为innodb的主键。

例如:
mysql> CREATE TABLE `t5` ( `id` int(11) NOT NULL, `name` varchar(10) DEFAULT NULL, `b` char(10) NOT NULL DEFAULT 'kk', PRIMARY KEY (`id`), UNIQUE KEY `uk_name_b` (`name`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.29 sec)

mysql> INSERT INTO `t5` VALUES (9,'0o','ll'),(1,'kk','yy');
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from t5;
+----+------+----+
| id | name | b |
+----+------+----+
| 9 | 0o | ll |
| 1 | kk | yy |
+----+------+----+
2 rows in set (0.00 sec)

这里的unique key被innodb标为“主键”?

已经显式指定`id`为PRIMARY KEY了,就不会再选择其他了。在未指定PRIMARY KEY的时候,是有一个选择的过程,见手册:

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.