InnoDB默认创建的主键索引是聚簇索引(Clustered Index),其它索引都属于辅助索引(Secondary Index),也被称为二级索引或非聚簇索引。
我们使用一个 表来详细说明 商品表 CREATE TABLE `merchandise`
(
`id` int(11) NOT NULL,
`serial_no` varchar(20) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`unit_price` decimal(10, 2) DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE
)
CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
插入数据:
id serial_no name unit_price
1 21002 鼠标 100
5 21003 键盘 300
8 20021 显示器 1200
11 12172 手柄 350
18 22391 主机箱 3798
InnoDB 索引默认使用的是B数索引,(关于B+树的数据结构,有兴趣的可以去翻翻相关资料。)mysql 聚簇索引(主键)的叶子节点则记录了主键值、事务id、用于事务和MVCC的回流指针以及所有的剩余列,其结构大概如下图所示:
这意味着, 主键是和 行数据存储在一起。 还有,非叶子节点存储着主键的ID,这样单独扫描主键的时候,是扫描非叶子 节点,而oracle 的主键,其实 是和 行数据分开的. 这是和oracle 最大的不同。
辅助索引 (或者叫二级索引或者一般索引)的优势在于。 (唯一索引、普通索引、前缀索引等都是二级索引)InnoDB在移动行时,无需维护二级索引,因为叶子节点中存储的是主键值,而不是指针。但是查找的过程多了一层, 查找的是时候时候先到的主键,再到数据(但是数据和主键存一起,所以速度也不慢)
因为主键是和 行数据一起存放的,若表的主键不是顺序的id,而是无规律数据,比如字符串,InnoDB无法加单的把一行记录插入到索引的最后,而是需要找一个合适的位置(已有数据的中间位置),甚至产生大量的页分裂并且移动大量数据,在寻找合适位置进行插入时,目标页可能不在内存中,这就导致了大量的随机IO操作,影响插入效率。除此之外,大量的页分裂会导致大量的内存碎片。这也是为什么 mysql 推荐使用 逻辑主键,而不适用业务主键的原因,业务主键不能保证数据的插入是连续的。
主键定位问题叶节点data域保存了完整的数据记录(非叶子节点)聚集索引非叶子节点中,Pointer(索引键值)指向的是ibd文件的Page Offset(File Header中的FIL_PAGE_OFFSET),这样就定位到数据块在ibd文件中的偏移量了。通过关联该ibd文件在操作系统的inode,就能找到磁盘中的具体数据块了。找到数据块,将它读入Innodb buffer pool,然后通过Page Directory(页目录)进行二分查找,来定位到行记录,这个过程中需要使用Record Header中的next_record。