概述

今天主要看下innodb是怎么去設(shè)計(jì)主鍵索引的，這里引用了一個(gè)淘寶MySQL數(shù)據(jù)庫(kù)經(jīng)典案例。

innodb 主鍵索引

在Innodb中，聚簇索引默認(rèn)就是主鍵索引。如果沒有主鍵，則按照下列規(guī)則來(lái)建聚簇索引:

沒有主鍵時(shí)，會(huì)用一個(gè)非空并且唯一的索引列做為主鍵，成為此表的聚簇索引;

如果沒有這樣的索引，InnoDB會(huì)隱式定義一個(gè)主鍵來(lái)作為聚簇索引。

由于主鍵使用了聚簇索引，如果主鍵是自增id，那么對(duì)應(yīng)的數(shù)據(jù)也會(huì)相鄰地存放在磁盤上，寫入性能較高。如果是uuid等字符串形式，頻繁的插入會(huì)使innodb頻繁地移動(dòng)

磁盤塊，寫入性能就比較低了。

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，因此對(duì)于InnoDB而言，主鍵具有特殊意義。可以通過(guò)主鍵直接定位到對(duì)應(yīng)的某一數(shù)據(jù)行記錄的物理位置，主鍵索引指向?qū)?yīng)行記錄，其他索引則都指向主鍵索引；因此，可以這么說(shuō)，InnoDB其實(shí)就是一個(gè) B+樹索引，這棵B+樹的索引就是主鍵，它的值則是對(duì)應(yīng)的行記錄。

在InnoDB數(shù)據(jù)表設(shè)計(jì)中，我們需要注意幾點(diǎn)：

• 1. 顯式的定義一個(gè) INT 類型自增字段的主鍵，這個(gè)字段可以僅用于做主鍵，不做其他用途
• 2. 如果不顯式定義主鍵的話，可能會(huì)導(dǎo)致InnoDB每次都需要對(duì)新數(shù)據(jù)行進(jìn)行排序，嚴(yán)重?fù)p害性能
• 3. 盡量保證不對(duì)主鍵字段進(jìn)行更新修改，防止主鍵字段發(fā)生變化，引發(fā)數(shù)據(jù)存儲(chǔ)碎片，降低IO性能
• 4. 如果需要對(duì)主鍵字段進(jìn)行更新，請(qǐng)將該字段轉(zhuǎn)變成一個(gè)唯一索引約束字段，另外創(chuàng)建一個(gè)沒有其他業(yè)務(wù)意義的自增字段做主鍵
• 5. 主鍵字段類型盡可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT
• 6. 主鍵字段放在數(shù)據(jù)表的第一順序

再看一個(gè)淘寶MySQL經(jīng)典案例

1、創(chuàng)建表

大多數(shù)互聯(lián)網(wǎng)業(yè)務(wù)（用戶，消息）都可以用A表或者B表滿足需求，那么兩個(gè)表有什么區(qū)別呢？

--創(chuàng)建表A
CREATE 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;
--創(chuàng)建表B
CREATE 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、對(duì)比分析

AB表對(duì)比分析如下：

總結(jié)

因?yàn)橹麈I是clustered index，采用自增id可以減少insert的時(shí)間。自增最大的問題就是分表分庫(kù)。 數(shù)據(jù)整合。 如果增加序列分發(fā)器 帶來(lái)的消耗也很高。 數(shù)據(jù)存儲(chǔ)碎片也難以消除。 主鍵設(shè)計(jì)是個(gè)折中的取舍。