MySQL 主键性能解析

目录

InnoDB基本知识

B+树介绍

性能解析

占用空间

主键长度 

查询性能

磁盘随机IO

写入性能

磁盘随机IO

页分裂

页合并

总结

注：

---

默认存储引擎InnoDB

InnoDB基本知识

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

InnoDB 使用了 B+ 树索引模型，整张表的数据是存储在主键索引对应的B+树中的

InnoDB 可操作的最小粒度是页（16k），页加载进内存后才会通过扫描页来获取行/记录。

B+树介绍

在InnoDB中，主键为聚簇索引：B+ 树的叶子节点中直接存储数据行

除了主键，其余都是辅助索引：B+ 树的叶子节点存储主键值。如果不是覆盖索引，需要回表

性能解析

测试版本：MySQL5.7.27

测试表

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名称',
  `age` tinyint(4) NOT NULL DEFAULT '0' COMMENT '年龄',
  `address` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COMMENT='用户表'

占用空间

整张表的大小 = 所有数据行（存储在主键索引中） + 非主键索引的大小，即整张表占用的总空间等于主键索引大小+普通索引大小

-- 先分析表数据
ANALYZE TABLE user

-- 切换到本地数据库的information_schema库
select table_name, data_length, index_length from tables where table_schema='my_test';

测试数据 100000 行，查询结果 

主键长度 

主键索引大小（data_length) =  叶子节点（十万行数据）+  每个非叶子节点（主键值 + 指向下一层的指针）

普通索引大小（index_length) = 叶子节点（十万主键值）+ 每个非叶子节点（普通索引字段 + 指向下一层的指针）

可以知道，主键设置的长度是对所有索引大小都有影响的

查询性能

可以看到本例中，

主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，N就是16*1024 / (8+6) = 1170。

假设一行记录大小是1k，2层 B+ 树可以存放，1170 * 16， 大概18000条数据

3层B+树可以存放1170 * 1170 * 16，大概两千万数据

100000行数据测试结果：0.033s

 20100000行数据测试结果：0.049s

磁盘随机IO

B+树查询过程：select * from user where id = 8;

我们通过这棵 B+ 树来查找，首先找到根页，每张表的根页位置在表空间文件中是固定的，找到根页后通过二分查找法，定位到 id = 8 的数据应该在指针P1指向的页中，那么去拿 B+ 树下一层的 page number = 2 的页中查找，同样通过二分查询法即可找到 id = 8 的记录。

索引存储了整张表的数据，所以不可能全部放在内存，前面提到，InnoDB可操作的最小单位为页，所以查询的时候 每次根据指针找 B+ 树下一层的页时，本质就是一次磁盘随机IO的过程。磁盘的速度是远远小于内存的，所以影响查询性能的最大原因就是 磁盘随机IO的次数 = B+树层数 - 1（因为根页是一直放在内存中的）

写入性能

user表，自增ID：数据行顺序写入，主键写入的值是顺序的，所以InnoDB将每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认为页的15/16），下一条记录就会写入新的页中

反例：如果主键不是递增的，也就是随机写入，每条记录写入时，为了保证索引的顺序，需要为新写入的行寻找合适的位置

随机写入的性能影响 

磁盘随机IO

写入的目标页可能已经刷新到磁盘上并从缓存中删除，所以在写入之前需要先从磁盘上读取目标页到内存

页分裂

频繁页分裂操作，创建新页，改变前后页的指针，移动数据

页合并

当页中删除的记录达到MERGE_THRESHOLD（默认页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

总结

1、表的主键ID在范围大小合适的情况下，越短越好

例如，阿里开发规范要求强制使用bigint作为主键，bigint长度为8字节；

例如，如果没有显式定义主键，InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索（ROWID随着行记录的写入而主键递增）

2、表的主键最好是递增的

注：

本例中造数据使用的存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_user`()
BEGIN
  #Routine body goes here...
	DECLARE i INT DEFAULT 0;
	DECLARE name VARCHAR ( 255 ) DEFAULT '张三';
	DECLARE age TINYINT DEFAULT 25;
	DECLARE address VARCHAR ( 255 ) DEFAULT '北京市海淀区';

	while i < 100000 DO
		
		SET name =  CONCAT("张三", FLOOR( 9 + RAND() * 1000000 ));
		SET age = FLOOR( 20 + RAND() * 10);
		SET address = CONCAT( "北京市海淀区", FLOOR( 31 + RAND() * 1000000 ), "号" );
		
		INSERT INTO user ( name, age, address ) VALUES( name, age, address );
		
		SET i = i + 1;
	
	END WHILE;
END

在十万行数据基础上再写入两千万数据（没有普通索引，只有主键）耗时：36.7h

 

 20100000行数据（没有普通索引，只有主键）占用空间：1.58G