详解MySQL数据库设计之innodb如何设计主键索引

xxcc140 2019-10-22

展开全文

概述

今天主要看下innodb是怎么去设计主键索引的，这里引用了一个淘宝MySQL数据库经典案例。

详解MySQL数据库设计之innodb如何设计主键索引

innodb 主键索引

在Innodb中，聚簇索引默认就是主键索引。如果没有主键，则按照下列规则来建聚簇索引:

没有主键时，会用一个非空并且唯一的索引列做为主键，成为此表的聚簇索引;

如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据也会相邻地存放在磁盘上，写入性能较高。如果是uuid等字符串形式，频繁的插入会使innodb频繁地移动

磁盘块，写入性能就比较低了。

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.

详解MySQL数据库设计之innodb如何设计主键索引

InnoDB是clustered-index table，因此对于InnoDB而言，主键具有特殊意义。可以通过主键直接定位到对应的某一数据行记录的物理位置，主键索引指向对应行记录，其他索引则都指向主键索引；因此，可以这么说，InnoDB其实就是一个 B+树索引，这棵B+树的索引就是主键，它的值则是对应的行记录。

在InnoDB数据表设计中，我们需要注意几点：

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

再看一个淘宝MySQL经典案例

1、创建表

大多数互联网业务（用户，消息）都可以用A表或者B表满足需求，那么两个表有什么区别呢？

--创建表ACREATE TABLE `A` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`message_id` int(11) NOT NULL,`user_id` int(11) NOT NULL,`msg` varchar(1024) DEFAULT NULL,`gmt_create` datetime NOT NULL,PRIMARY KEY (`id`),KEY `user_id` (`user_id`,`message_id`),KEY `idx_gmt_create` (`gmt_create`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;--创建表BCREATE TABLE `B` (`user_id` int(11) NOT NULL,`message_id` int(11) NOT NULL,`msg` varchar(1024) DEFAULT NULL,`gmt_create` datetime NOT NULL,PRIMARY KEY (`user_id`,`message_id`),KEY `idx_gmt_create` (`gmt_create`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;