從淘寶MySQL數據庫經典案例來看innodb如何設計主鍵索引概述innodb 主鍵索引再看一個淘寶MySQL經典案例1、創建表2、對比分析總結

發布時間：2023-07-03 16:03:57 作者：網友整理

概述

今天主要看下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就不用BIGINT
6. 主鍵字段放在數據表的第一順序

再看一個淘寶MySQL經典案例

1、創建表

大多數互聯網業務（用戶，消息）都可以用A表或者B表滿足需求，那么兩個表有什么區別呢？

--創建表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;
--創建表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;