MySQL主键索引结构

概述

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

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.

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

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

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

AB表对比分析如下：

总结

因为主键是clustered index，采用自增id可以减少ｉｎｓｅｒｔ的时间。自增最大的问题就是分表分库。 数据整合。 如果增加序列分发器 带来的消耗也很高。 数据存储碎片也难以消除。 主键设计是个折中的取舍。

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~