mysql中业务主键和非业务主键浅析

凯先生boy

已于 2022-06-01 10:54:12 修改

阅读量1.3k

点赞数

分类专栏： mysql 文章标签： mysql b树数据库

于 2022-05-30 20:45:56 首次发布

本文链接：https://blog.csdn.net/weixin_44457062/article/details/125053406

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql中业务主键和非业务主键浅析

之前就听别人说要mysql表中要有非业务主键，这样会提升mysql的效率。当时只是那么一听，没有详细的了解。最近，接触两个项目的数据库结构发现一个使用业务主键，另一个则没有使用。再加上也在阅读《Java开发手册》，也阅读到了类似的规范，所以决定进一步了解一下。

java开发手册（黄山版）：（官方地址：https://github.com/alibaba/p3c）

【强制】:表必备三字段:id，create_time，update_time。
说明:其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time，update_time 的类型均为 datetime 类型，如果要记录时区信息，那么类型设置为 timestamp。

概念

业务主键：

与业务相关，一般是varchar类型，包含各种信息，方便区分。比如商品的类别，初始化的日期等，都会出现在业务主键，方便快速识别，查找相关信息。

非业务主键：

与业务无关，一般在mysql中设置为bigint、无符号和自增。主要是用来提升mysql插入的效率，让b+树的每个非叶子结点存储更多的信息。

主要区别

业务主键和非业务主键对于mysql数据库来说，最大的区别是否自增。为什么是自增呐？mysql的底层使用数据结构是B+树，并对B+在一定程度上进行了优化。大家先大致了解B+树，后面会进行解释。

B+树

简单介绍：（大家注意区分叶子节点、非叶子节点、节点，其中节点包括叶子节点和非叶子节点）

除了根节点（1到M，M是阶数），其他节点是M/2到M-1
类似二叉排序树，左节点的数据比父节点小，父节点的数据比右节点的数据小。
所有的数据都会出现在叶子节点。
叶子节点的是连接的、有序的，方便顺序查找。
最终都会查找到叶子节点，在叶子节点才是真正的存储数据的，所以每次查找的深度相同。

三阶B+树如下（每个节点最多是2个）：

在这里插入图片描述
mysql使用b+树，并且做了一定的优化，让每一个节点都可以一次IO读入。从整体上看，每一层都会进行一次磁盘IO，而磁盘IO是比较慢的，所以我们要尽可能减少B+树的层数，让每个非叶节点存储更多的更多的数据，让B+树胖起来，就能进一步提升查询的效率。

为什么对于mysql来说，自增是业务主键和非业务主键的主要区别？

从上图中和B+树的特性可以得知，并不是每个节点都是达到节点的最大容量（M-1），而对于mysql来说，每个节点都对应一个磁盘盘，也就是对应一次IO，所以对于未达到最大容量的节点会对磁盘空间造成浪费，并且可能会导致B+树的高增增加，增加磁盘IO的次数，造成查询的效率降低。

为了解决这个问题，mysql对B+树进行了优化，会对相邻的未达到最大容量的节点，进行整合，但是如果大部分都是未达到最大容量的节点，整合节点的过程过多也会造成插入的效率的降低。而且，不是自增的数据，会有很多的未到达最大容量的节点，自增的数据，基本上节点都是达到最大容量的。

对于非自增的业务主键来说，会出现很多未达到最大容量的节点，所以会有过多的节点分裂和整合的过程，这样就会造成插入的效率的下降，特别是当数据量多的时候。

对于自增的业务主键来说，每次都会在最后的节点（最下面和最右边）达到最大容量的时候，进行分裂，不会有整合的过程，这样就能增加插入的效率。

总结：

InnoDB的最小储存单位是页（也就是B+树的节点）。

如果使用自增的非业务主键，只有当一页插入满后才会插入下一页。
如果使用的是无序的业务主键，会造成频繁的也分裂（即B+树的节点分裂），同时为了防止浪费，也会对分裂的未达到最大容量的也进行整合。这样就会降低插入的效率。

所以一定要有一个自增的非业务主键，保证插入和查询都能更加高效，避免使用UUID当主键。这也是《Java开发规范》里的要求。

mysql演示

在mysql数据库新建查询：

SHOW VARIABLES LIKE 'version%';

版本信息：

Variable_name	Value
version	8.0.27
version_comment	MySQL Community Server - GPL
version_compile_machine	arm64
version_compile_os	macos11
version_compile_zlib	1.2.11

自增主键建表语句：

CREATE TABLE `t_id` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name1` varchar(255) DEFAULT NULL,
  `name2` varchar(255) DEFAULT NULL,
  `name3` varchar(255) DEFAULT NULL,
  `name4` varchar(255) DEFAULT NULL,
  `name5` varchar(255) DEFAULT NULL,
  `name6` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查询当前B+树的情况，sql语句如下:

CREATE TABLE `t_id` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name1` varchar(255) DEFAULT NULL,
  `name2` varchar(255) DEFAULT NULL,
  `name3` varchar(255) DEFAULT NULL,
  `name4` varchar(255) DEFAULT NULL,
  `name5` varchar(255) DEFAULT NULL,
  `name6` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PAGE_NUMBER：页码
PAGE_TYPE：页面类型
NUMBER_RECORDS：页面中的记录数
DATA_SIZE：记录数的大小
PAGE_STATE：页面的状态

查询结果：
在这里插入图片描述

大家可以查看information_schema库中的innodb_buffer_page表，里面有很多属性列，大家可以查看更多属性的含义，查询自己感兴趣的属性。
在这里插入图片描述
也可以通过sql语句查询

SELECT *
FROM information_schema.innodb_buffer_page

innodb默认页大小

sql语句：

show variables like '%innodb_page_size%';

查询结果：

Variable_name	Value
innodb_page_size	16384

可以看到默认页大小为16k

插入数据

INSERT INTO t_id (id, name1, name2, name3, name4, name5, name6) 
VALUES (1, REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

插入完成之后，查看一条数据的大小，使用上面的语句。

SELECT PAGE_NUMBER, PAGE_TYPE, NUMBER_RECORDS, DATA_SIZE, COMPRESSED_SIZE,PAGE_STATE
FROM information_schema.innodb_buffer_page
WHERE table_name like "%t_id%" AND index_name = "PRIMARY";

查询结果：
在这里插入图片描述
可以看到data_size的大小为1566，大致计算一下。默认的页大小/data_size的大小。

16384/1566 = 10.46

所以大致在10条左右数据的时候会进行页分裂。

接着，再插入8条数据，观察是否分裂。

INSERT INTO t_id (name1, name2, name3, name4, name5, name6) 
VALUES 
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85)),
(REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

使用sql查看

SELECT PAGE_NUMBER, PAGE_TYPE, NUMBER_RECORDS, DATA_SIZE, COMPRESSED_SIZE,PAGE_STATE
FROM information_schema.innodb_buffer_page
WHERE table_name like "%t_id%" AND index_name = "PRIMARY";

得到结果：
在这里插入图片描述
可以看到data_size已经快到16384了。接着在插入一条数据查看，此时已经插入10条数据了，发现开始页分裂，结果如下图所示。

可以看到page_number=4的页分裂之后，还保留两条数据，page_number为5，6的两个页的记录数加起来刚好是10。

接着，继续一条一条的插入数据。大家可以观察变化过程。
在这里插入图片描述
增加的是page_number=6的页的记录数。

当插入第14条数据的时候，page_number=6的开始页分裂。
在这里插入图片描述
page_number=6的记录数还是9，新出来了一个page_number=7，记录数为1，这就是刚刚我们新增的一条数据。

接着，再一条一条的添加，结果如下。
在这里插入图片描述
可以看出来，page_number=7的记录数不断增加，最终会增加到9条记录数。

当page_number=7的记录数到9之后，在插入一条数据，就会再次页分裂。

page_number=7的记录数还是9，新出来了一个page_number=8，记录数为1，这就是刚刚我们新增的一条数据，根节点page_number=4,也增加了1。

由此可以看出，自增的业务主键，除了第一次会对半拆分，进行页分裂，之后的每一次添加数据都将新的数据放在page_number最大的页中，直到达到最大容量，然后分裂。分裂的原则是原来的页的记录数保持不变，新增一个页来装新的记录数。所以自增的业务主键，不会改动原来的页的内容，这样就可以更快的插入数据，提升效率。

非自增的业务主键的建表语句

本次测试使用UUID测试，在代码中生成UUID，然后作为主键。

sql语句：

CREATE TABLE `t_uuid` (
  `id` varchar(32) NOT NULL,
  `name1` varchar(255) DEFAULT NULL,
  `name2` varchar(255) DEFAULT NULL,
  `name3` varchar(255) DEFAULT NULL,
  `name4` varchar(255) DEFAULT NULL,
  `name5` varchar(255) DEFAULT NULL,
  `name6` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

添加一条数据，sql如下：

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("deaa596f0fa44c008af93623c67c68b1", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

结果如下图。
在这里插入图片描述
可以看到data_size的大小为1566，大致计算一下。默认的页大小/data_size的大小。

16384/1594 = 10.28

如果是自增id的话，根据上面的验证，会在记录数为10机型页分裂，但这次不是自增的，所以我一条一条数据添加，观察变化。

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("12d5f1a2bae9499e825193e32036cfc7", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("584df7df3a134970a8243b2970272cb7", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("5d6012aa343b4cce8bb92520eafdd15b", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("465adac7b92c49bbb9c82aa4d4576c52", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("ac5e99c2a4544866b389ed5e95268e41", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("2aedd7c198ae411b9dafcea8a38ab5af", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("e6588349681a42dca0ceb4c09c5182bc", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("c46552a0acb247319d2a9b0cc36cd4f5", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("57a7ec8891c84069a3cd239cbe3916de", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果如下：
在这里插入图片描述
可以看出使用UUID当主键，根节点的记录数正常增加到10，接着继续添加数据，达到11条时开始分裂。

sql语句：

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("2356de02680c41f7b6bf977d28cdedc3", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

SELECT PAGE_NUMBER, PAGE_TYPE, NUMBER_RECORDS, DATA_SIZE, COMPRESSED_SIZE,PAGE_STATE
FROM information_schema.innodb_buffer_page
WHERE table_name like "%t_uuid%" AND index_name = "PRIMARY";

查询结果
在这里插入图片描述
接着，添加数据。

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("182bf3ffb7cf49efbc5085eeaf7bf5ab", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
可以发现增加的是page_number=5的记录数，如果是自增主键，则应该增加page_number=6的记录数，因为6是当前最大的数。

接着添加数据。

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("1c76b8e9a9f341ce8d7e753933930577", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
添加数据：

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("331be685f4cc4db0a3e785c692369864", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
添加数据：

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("0c19cb385d704300ada7ea00b0af1ad3", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
添加数据：

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("ba4749b0e9504b82b163fbfa24c265f6", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
可以发现增加的是page_number=5的记录数到10之后，开始增加page_number=6的记录数。

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("38accbe9b1c44b499ea760c8dac56305", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
观察上面的结果，我以为是要把page_number=6的记录增加到7，结果是增加在page_number=5的页上，数据终于开始随机了（这里的随机也是遵循着B+的规则的，左节点 < 父节点 < 根节点，只是这里人眼难以比较），相对于自增主键的数据每次都在page_number最大的页增加的来说的。之前的数据一直在page_number=5增加，我还以为和理论不同呐，看来是多虑了，刚好碰着都增在page_number=5的情况。

接着增加数据。

INSERT INTO t_uuid (id, name1, name2, name3, name4, name5, name6) 
VALUES ("c4e8771ddafe4782aafb422a2b5c351f", REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85), REPEAT("cay", 85));

查询结果：
在这里插入图片描述
可以看出增加在page_number=6上。接下来，大家可以在一条一条的添加数据，现象应该也是随机的。其实这还是优化之后的结果，之前没有优化之前，非自增的业务主键会频繁的页分裂，浪费磁盘空间。

由此，非自增的业务主键的每个页都可能被添加数据，添加的时候每个页都有可能发生分裂，需要维护，需要的代价也就更多，所以一定要有自增的非业务主键。

插入数据测试

往之前创建的表里，批量插入数据，对比自增的主键和使用UUID的主键的插入效率。

foreach 标签批量插入

自增主键的相关代码

自增主键的测试类代码：

@Autowired
private BatchMapper batchMapper;
private final static String CAY_STR = "cay";

/**
* 测试foreach，自增主键插入
*/
@Test
public void testInsertByForEachAndId(){
    int j = 10;
    for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至百万数据
         log.info("第  {}  次循环，条数： {}", i, j);
         List<BatchIdEntity> batchList = getListById(j);//获取实体类
         long startTime = System.currentTimeMillis();
         batchMapper.insertByForEachAndId(batchList);
         long endTime = System.currentTimeMillis();
         log.info("执行时间：{}", endTime - startTime);
    }
}

/**
* 构造实体类
* @param n 实体类的数量
* @return
*/
private List<BatchIdEntity> getListById(int n){
    List<BatchIdEntity> batchList = new ArrayList<>();
    for (int i = 0; i < n; i++){
    BatchIdEntity batchIdEntity = new BatchIdEntity();
    batchIdEntity.setName1(CAY_STR);
    batchIdEntity.setName2(CAY_STR);
    batchIdEntity.setName3(CAY_STR);
    batchIdEntity.setName4(CAY_STR);
    batchIdEntity.setName5(CAY_STR);
    batchIdEntity.setName6(CAY_STR);
    batchList.add(batchIdEntity);
    }
    return batchList;
}

mapper文件:

<insert id="insertByForEachAndId" parameterType="BatchIdEntity">
        insert into t_id(name1, name2, name3, name4, name5, name6)
        values
        <foreach collection="entityList" open="(" close=")" item="entity" separator="),(">
            #{entity.name1},#{entity.name2},#{entity.name3},#{entity.name4},#{entity.name5},#{entity.name6}
        </foreach>
</insert>

UUID测试类的相关代码

UUID测试类的代码

/**
 * 测试foreach，UUID主键插入
 */
@Test
public void testInsertByForEachAndUuid(){
    int j = 10;
    for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至百万数据
        log.info("第  {}  次循环，条数： {}", i, j);
        List<BatchUuidEntity> batchList = getListByUuid(j);//获取实体类
        long startTime = System.currentTimeMillis();
        batchMapper.insertByForEachAndUuid(batchList);
        long endTime = System.currentTimeMillis();
        log.info("执行时间：{}", endTime - startTime);
    }
}

/**
 * 构造实体类
 * @param n 实体类的数量
 * @return
 */
 private List<BatchUuidEntity> getListByUuid(int n){
     List<BatchUuidEntity> batchList = new ArrayList<>();
     for (int i = 0; i < n; i++){
     BatchUuidEntity batchUuidEntity = new BatchUuidEntity();
        String uuid = UUID.randomUUID().toString().replace("-", "");
        batchUuidEntity.setId(uuid);
        batchUuidEntity.setName1(CAY_STR);
        batchUuidEntity.setName2(CAY_STR);
        batchUuidEntity.setName3(CAY_STR);
        batchUuidEntity.setName4(CAY_STR);
        batchUuidEntity.setName5(CAY_STR);
        batchUuidEntity.setName6(CAY_STR);
        batchList.add(batchUuidEntity);
    }
    return batchList;
}

mapper文件:

<insert id="insertByForEachAndUuid" parameterType="BatchUuidEntity">
        insert into t_uuid(id, name1, name2, name3, name4, name5, name6)
        values
        <foreach collection="entityList" open="(" close=")" item="entity" separator="),(">
            #{entity.id}, #{entity.name1}, #{entity.name2}, #{entity.name3}, #{entity.name4}, #{entity.name5},
            #{entity.name6}
        </foreach>
</insert>

插入耗时对比

自增主键：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：698
第  2  次循环，条数： 100
执行时间：50
第  3  次循环，条数： 1000
执行时间：273
第  4  次循环，条数： 10000
执行时间：605
第  5  次循环，条数： 100000
执行时间：3859

UUID主键：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：739
第  2  次循环，条数： 100
执行时间：94
第  3  次循环，条数： 1000
执行时间：212
第  4  次循环，条数： 10000
执行时间：647
第  5  次循环，条数： 100000
执行时间：4429

可以看出6个列的时候，插入在10万条的数据有一定的影响，但是影响不大，大概是0.6秒。

下面开始测试20列的情况。由于代码差不多，就不展示了。

耗时对比：

自增主键：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：739
第  2  次循环，条数： 100
执行时间：130
第  3  次循环，条数： 1000
执行时间：386
第  4  次循环，条数： 10000
执行时间：1117
第  5  次循环，条数： 100000
执行时间：11565

UUID主键：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：648
第  2  次循环，条数： 100
执行时间：123
第  3  次循环，条数： 1000
执行时间：378
第  4  次循环，条数： 10000
执行时间：1170
第  5  次循环，条数： 100000
执行时间：12450

可以看出20个列的时候，插入在10万条的数据有一定的影响，但是影响不大，大概是0.9秒。

使用其他方式批量插入方式的比较，可以查看我之前写的内容：点这里

使用命令观察mysql的底层。

自增主键sql语句：

SELECT PAGE_NUMBER, PAGE_TYPE, NUMBER_RECORDS, DATA_SIZE, COMPRESSED_SIZE,PAGE_STATE
FROM information_schema.innodb_buffer_page
WHERE table_name like "%t_id%" AND index_name = "PRIMARY";

查询结果：
在这里插入图片描述
大部分都是页的记录数都是320，和上面我们一条一条插入观察的结果相同。

UUID主键sql语句：

SELECT PAGE_NUMBER, PAGE_TYPE, NUMBER_RECORDS, DATA_SIZE, COMPRESSED_SIZE,PAGE_STATE
FROM information_schema.innodb_buffer_page
WHERE table_name like "%t_uuid%" AND index_name = "PRIMARY";

查询结果：
在这里插入图片描述
可以看到页的记录数都是不一样。

结论：

从上面的测试看出，在10万条之内数据的情况，自增主键和UUID主键插入效率基本差不多。大家注意版本的区别，我使用的是mysql8.0.27，可能不同的版本也不一样，毕竟mysql也在不断的优化。还是数据量的问题，我这里是10万以内的数据，大家感兴趣可以测试更多的数据。就目前的测试数据来说，自增和非自增影响不大。

本人也在不断探索中，如有错误，请指出，如果你的测试结果和我不一样，麻烦发一下mysql版本，咱们可以一起讨论一下，共同进步。

凯先生boy

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
mysql中业务主键和非业务主键浅析

mysql中业务主键和非业务主键浅析之前就听别人说要mysql表中要有非业务主键，这样会提升mysql的效率。当时只是那么一听，没有详细的了解。最近，接触两个项目的数据库结构发现一个使用业务主键，另一个则没有使用。再加上也在阅读《Java开发手册》，也阅读到了类似的规范，所以决定进一步了解一下。java开发手册（黄山版）：（官方地址：https://github.com/alibaba/p3c）【强制】:表必备三字段:id，create_time，update_time。说明:其中 id 必为主键
复制链接

扫一扫

专栏目录