mysql数据库中几种主键策略性能对比

失去的东西,其实从来未曾真正地属于你,也不必惋惜,始终真心真意

Posted by yishuifengxiao on 2024-01-24

一 前提条件

四种场景下id的生成方式

  1. mysql自增主键
  2. mysql表结构主键自增但是手动设置雪花ID
  3. mysql表结构主键不自增但是手动设置雪花ID
  4. mysql表主键为varchar,使用UUID

在数据量级10, 50, 100, 200, 300, 400, 500, 1000, 2000, 3000, 4000, 5000, 10000,20000, 30000, 40000, 50000, 100000, 200000, 300000, 400000, 500000,1000000, 2000000, 3000000,4000000, 5000000, 10000000 依次递增的情况下分别测试以下几种场景下的耗时情况。

  1. 准备数据
  2. 数据插入
  3. 指定id查询数据
  4. 查询10条数据
  5. 指定id删除数据
  6. 随机ID删除数据

为了排除已有数据的干扰,在每次运行前均使用

truncate table 表名

清空数据。

为了使批处理功能生效,需要在mysql连接上增加参数 &rewriteBatchedStatements=true且此参数非常重要

rewriteBatchedStatements=true 是MySQL连接参数中的一个选项。当您在连接MySQL数据库时将该选项设置为true时,会启用批量重写语句功能。这个功能允许将一组SQL语句重写为单个大的SQL语句,以提高性能。

在默认情况下,MySQL驱动程序会将批量的SQL语句发送到服务器,但是每个语句都会单独执行,这可能会导致额外的网络开销和服务器端的开销。启用rewriteBatchedStatements=true后,驱动程序会尝试将多个SQL语句合并为一个更大的SQL语句,然后一次性发送到服务器执行,从而减少了网络开销和服务器端的开销。

需要注意的是,并非所有的MySQL驱动程序都支持rewriteBatchedStatements选项。一般来说,这个选项适用于特定的MySQL JDBC驱动程序,比如MySQL Connector/J。

在使用rewriteBatchedStatements时,需要注意以下几点:

  • 该选项可能会对一些特定的SQL语句产生影响,因此在启用该选项时需要进行充分的测试。
  • 在某些情况下,由于MySQL服务器的配置或者权限限制,可能无法启用该选项。
  • 该选项的性能提升效果会受到具体的应用场景、网络延迟等因素的影响,因此需要根据实际情况进行评估。

总之,rewriteBatchedStatements=true 是一个用于优化批量SQL操作性能的选项,但在启用之前需要进行充分的测试和评估。

二 具体实现

2.1 数据库结构

-- ----------------------------
-- Table structure for id_inc
-- ----------------------------
DROP TABLE IF EXISTS `id_inc`;
CREATE TABLE `id_inc` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table structure for id_increment
-- ----------------------------
DROP TABLE IF EXISTS `id_increment`;
CREATE TABLE `id_increment` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pwd` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table structure for id_snow
-- ----------------------------
DROP TABLE IF EXISTS `id_snow`;
CREATE TABLE `id_snow` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

-- ----------------------------
-- Table structure for id_uuid
-- ----------------------------
DROP TABLE IF EXISTS `id_uuid`;
CREATE TABLE `id_uuid` (
`id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2.2 具体运行代码

package org.example;

import com.yishuifengxiao.common.tool.random.IdWorker;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.StopWatch;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.UUID;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
* @author yishui
* @version 1.0.0
* @date 2024/1/22 17:20
* @since 1.0.0
*/
@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class DataTest {


@Autowired
private JdbcTemplate jdbcTemplate;


@Test
public void test() {
List<Integer> list = Arrays.asList(10, 50, 100, 200, 300, 400, 500, 1000, 2000, 3000, 4000, 5000, 10000,
20000, 30000, 40000, 50000, 100000, 200000, 300000, 400000, 500000, 1000000, 2000000, 3000000,
4000000, 5000000, 10000000);
list.forEach(length -> {
insertIdIncrement(length);
insertIdInc(length);
insertIdSnow(length);
insertIdUuid(length);
});
System.out.println("-------> 运行完成");
}


public void insertIdIncrement(int length) {
jdbcTemplate.execute("truncate table id_increment ");
StopWatch stopWatch = new StopWatch("【自增ID】插入性能测试:数量-" + length);
stopWatch.start("【自增ID】[准备数据]:数量-" + length);
List<IdIncrement> list =
IntStream.range(0, length).mapToObj(v -> new IdIncrement(System.currentTimeMillis() + "",
System.currentTimeMillis() + "", LocalDateTime.now())).collect(Collectors.toList());
stopWatch.stop();
stopWatch.start("【自增ID】[数据插入]:数量-" + length);
String sql = "INSERT INTO `id_increment` ( `name`, `create_time`, `pwd`) VALUES ( ?, " + "?, ?) ";

jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setObject(1, list.get(i).getName());
ps.setObject(2, list.get(i).getCreateTime());
ps.setObject(3, list.get(i).getPwd());


if (Objects.equals(i, list.size())) {
ps.executeLargeBatch();
ps.clearBatch();
} else {
if (i % 500 == 0) {
ps.executeLargeBatch();
ps.clearBatch();
}
}

}

@Override
public int getBatchSize() {
return list.size();
}
});
stopWatch.stop();
stopWatch.start("【自增ID】[id查询]:数量-" + length);
jdbcTemplate.queryForList("select * from id_increment where id = " + 10);
stopWatch.stop();
stopWatch.start("【自增ID】[查询10条]:数量-" + length);
jdbcTemplate.queryForList("select * from id_increment limit 10");
stopWatch.stop();
stopWatch.start("【自增ID】[id删除]:数量-" + length);
jdbcTemplate.execute("delete from id_increment where id = " + 10);
stopWatch.stop();
stopWatch.start("【自增ID】[随机删除]:数量-" + length);
jdbcTemplate.execute("delete from id_increment where id = " + System.currentTimeMillis());
stopWatch.stop();
log.warn("【自增ID】耗时统计信息为\r\n {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));

}

public void insertIdInc(int length) {
jdbcTemplate.execute("truncate table id_inc ");
StopWatch stopWatch = new StopWatch("【自增雪花ID】插入性能测试:数量-" + length);
stopWatch.start("【自增雪花ID】[准备数据]:数量-" + length);
List<IdIncrement> list = IntStream.range(0, length).mapToObj(v -> new IdIncrement(IdWorker.snowflakeId(),
System.currentTimeMillis() + "", System.currentTimeMillis() + "", LocalDateTime.now())).collect(Collectors.toList());
Long id = list.get(list.size() - 1).getId();
stopWatch.stop();
stopWatch.start("【自增雪花ID】[数据插入]:数量-" + length);
String sql = "INSERT INTO `id_inc` ( `id`,`name`, `create_time`, `pwd`) VALUES ( ?,?, " + "?, ?) ";

jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setObject(1, list.get(i).getId());
ps.setObject(2, list.get(i).getName());
ps.setObject(3, list.get(i).getCreateTime());
ps.setObject(4, list.get(i).getPwd());


if (Objects.equals(i, list.size())) {
ps.executeLargeBatch();
ps.clearBatch();
} else {
if (i % 500 == 0) {
ps.executeLargeBatch();
ps.clearBatch();
}
}

}

@Override
public int getBatchSize() {
return list.size();
}
});
stopWatch.stop();
stopWatch.start("【自增雪花ID】[id查询]:数量-" + length);
jdbcTemplate.queryForList("select * from id_inc where id = " + id);
stopWatch.stop();
stopWatch.start("【自增雪花ID】[查询10条]:数量-" + length);
jdbcTemplate.queryForList("select * from id_inc limit 10");
stopWatch.stop();
stopWatch.start("【自增雪花ID】[id删除]:数量-" + length);
jdbcTemplate.execute("delete from id_inc where id = " + id);
stopWatch.stop();
stopWatch.start("【自增雪花ID】[随机删除]:数量-" + length);
jdbcTemplate.execute("delete from id_inc where id = " + System.currentTimeMillis());
stopWatch.stop();
log.warn("【自增雪花ID】耗时统计信息为\r\n {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));

}

public void insertIdSnow(int length) {
jdbcTemplate.execute("truncate table id_snow ");
StopWatch stopWatch = new StopWatch("【雪花ID】插入性能测试:数量-" + length);
stopWatch.start("【雪花ID】[准备数据]:数量-" + length);
List<IdSnow> list = IntStream.range(0, length).mapToObj(v -> new IdSnow(IdWorker.snowflakeId(),
System.currentTimeMillis() + "", System.currentTimeMillis() + "", LocalDateTime.now())).collect(Collectors.toList());
Long id = list.get(list.size() - 1).getId();
stopWatch.stop();
stopWatch.start("【雪花ID】[数据插入]:数量-" + length);
String sql = "INSERT INTO `id_snow` (`id`, `name`, `create_time`, `pwd`) VALUES (?, ?, " + "?, ?) ";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setObject(1, list.get(i).getId());
ps.setObject(2, list.get(i).getName());
ps.setObject(3, list.get(i).getCreateTime());
ps.setObject(4, list.get(i).getPwd());

if (Objects.equals(i, list.size())) {
ps.executeLargeBatch();
ps.clearBatch();
} else {
if (i % 500 == 0) {
ps.executeLargeBatch();
ps.clearBatch();
}
}
}

@Override
public int getBatchSize() {
return list.size();
}
});
stopWatch.stop();
stopWatch.start("【雪花ID】[id查询]:数量-" + length);
jdbcTemplate.queryForList("select * from id_snow where id = " + id);
stopWatch.stop();
stopWatch.start("【雪花ID】[查询10条]:数量-" + length);
jdbcTemplate.queryForList("select * from id_snow limit 10");
stopWatch.stop();
stopWatch.start("【雪花ID】[id删除]:数量-" + length);
jdbcTemplate.execute("delete from id_snow where id = " + id);
stopWatch.stop();
stopWatch.start("【雪花ID】[随机删除]:数量-" + length);
jdbcTemplate.execute("delete from id_snow where id = " + System.currentTimeMillis());
stopWatch.stop();
log.warn("【雪花ID】耗时统计信息为\r\n {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));
}


public void insertIdUuid(int length) {
jdbcTemplate.execute("truncate table id_uuid ");
StopWatch stopWatch = new StopWatch("【UUID】插入性能测试:数量-" + length);
stopWatch.start("【UUID】[准备数据]:数量-" + length);
List<IdUuid> list = IntStream.range(0, length).mapToObj(v -> new IdUuid(UUID.randomUUID().toString(),
System.currentTimeMillis() + "", System.currentTimeMillis() + "", LocalDateTime.now())).collect(Collectors.toList());
String id = list.get(list.size() - 1).getId();
stopWatch.stop();
stopWatch.start("【UUID】[数据插入]");
String sql = "INSERT INTO `id_uuid` (`id`, `name`, `create_time`, `pwd`) VALUES (?, ?, " + "?, ?) ";
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
ps.setObject(1, list.get(i).getId());
ps.setObject(2, list.get(i).getName());
ps.setObject(3, list.get(i).getCreateTime());
ps.setObject(4, list.get(i).getPwd());

if (Objects.equals(i, list.size())) {
ps.executeLargeBatch();
ps.clearBatch();
} else {
if (i % 500 == 0) {
ps.executeLargeBatch();
ps.clearBatch();
}
}
}

@Override
public int getBatchSize() {
return list.size();
}
});
stopWatch.stop();
stopWatch.start("【UUID】[id查询]:数量-" + length);
jdbcTemplate.queryForList("select * from id_uuid where id = '" + id + "'");
stopWatch.stop();
stopWatch.start("【UUID】[查询10条]:数量-" + length);
jdbcTemplate.queryForList("select * from id_uuid limit 10");
stopWatch.stop();
stopWatch.start("【UUID】[id删除]:数量-" + length);
jdbcTemplate.execute("delete from id_uuid where id = '" + id + "'");
stopWatch.stop();
stopWatch.start("【UUID】[随机删除]:数量-" + length);
jdbcTemplate.execute("delete from id_uuid where id = '" + UUID.randomUUID().toString() + "'");
stopWatch.stop();
log.warn("【UUID】耗时统计信息为\r\n {}", stopWatch.prettyPrint(TimeUnit.MILLISECONDS));
}

}

三 结果对比

3.1 准备数据

3.2 数据插入

为了清晰可见,排除UUID之外的方式的性能对比数据为

柱状图为

在30w数据的数量级情况下

3.3 指定id查询数据

3.4 查询10条数据

3.5 指定id删除数据

3.6 随机ID删除数据

四 空间占用情况

查看指定表的空间占用情况

SELECT
table_name AS `Table`,
round(
(
(data_length + index_length) / 1024
),
2
) `Size in KB`
FROM
information_schema. TABLES
WHERE
table_schema = 'personkit'
AND table_name = 'id_inc';

使用下面的脚本新建一个数据表

DROP TABLE IF EXISTS `id_inc`;
CREATE TABLE `id_inc` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`pwd` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

4.1 初始化占用空间大小

此时数据表的初始空间占用情况为

1706103512856

4.2 1000条数据占用空间大小

然后使用上面的程序插入1000条数据后查看空间占用大小

1706103997918

4.3 使用delete删除后占用大小

delete from id_inc limit 500

再次查看空间的占用大小为

1706104517214

接下来删除剩余的数据

delete from id_inc

此时占用空间大小为

1706104623032

4.4 数据长度

需要的注意的地方是,在使用上面的sql创建数据表后,此时表的对象信息如下

1706105142301

在插入1000条数据后

1706105359147

使用delete删除所有的数据后,数据长度不变。

使用truncate table id_inc删除所有的数据后,数据长度依然不变。

这是因为当您在MySQL中删除数据后,表的数据长度(data_length)通常不会立即减少。这并不意味着磁盘上的实际空间没有发生变化,而是因为MySQL使用了一种称为MVCC(多版本并发控制)的机制来管理数据。

在MVCC中,当您删除数据时,MySQL并不会立即释放磁盘空间。相反,它会将被删除的数据标记为"已删除",并在后续的操作中进行清理。这样做的原因是为了保持事务的一致性,并且可以更好地支持并发操作。

当您删除数据后,MySQL会在后台的清理任务中逐渐回收已删除数据所占用的磁盘空间。因此,即使您删除了数据,表的数据长度在某些情况下可能不会立即减少。

如果您希望立即释放磁盘空间,可以考虑执行以下操作:

  • 对表进行优化(OPTIMIZE TABLE)操作,这将重新组织表并释放未使用的空间。
  • 对InnoDB存储引擎的表执行重建表(REBUILD TABLE)操作,这将重新创建表并释放未使用的空间。

需要注意的是,执行这些操作可能会导致表被锁定,因此在生产环境中需要谨慎操作。

五 结论

根据上面的实验过程,我们可以得知,使用作为UUID作为主键的性能非常大随着数据数量的增大,UUID方案在准备数据、插入数据、指定id删除数据时的耗时情况与其他方案相比也越来越大,性能相差十分悬殊,几乎达到不可忍受的地步,在实际开发过程中几乎可以完成抛弃不用。

UUID作为MySQL数据库主键性能较差的原因主要有以下几点:

  1. 数据存储空间:UUID是一个128位的数字,相比于自增的整型主键,它占用的存储空间更大,导致每个索引节点能存储的记录数量减少,从而增加了索引的深度,降低了查询效率。
  2. 索引效率:UUID是随机生成的,插入数据的时候会导致数据的插入位置也是随机的,这样会造成索引的频繁分裂和重新构建,影响了查询性能。
  3. 索引碎片:由于UUID是随机生成的,插入数据的时候会导致数据的插入位置也是随机的,这样会导致索引碎片化,进而影响了查询性能。
  4. 查询性能:由于UUID是随机生成的,会导致数据在磁盘上的存储也是随机的,这样会增加磁盘的随机读写,影响了查询性能。
  5. 内存占用:由于UUID占用的存储空间更大,会导致更多的数据需要存储在内存中,增加了内存的占用,进而影响了数据库的整体性能。

除此以外,在其他的三种方案的对比中,三种方案的性能相差不多,几乎相同。

在数据准备阶段,自增id性能最好,但是在数据插入阶段, 发现表结构设置为自增但是使用雪花算法生成主键数据插入这种方案效率相对较高 。

在实际测试中发现表结构设置为自增但实际使用雪花算法生成主键数据插入效率相对较高的情况可能存在以下原因:

  1. 索引性能:在使用自增ID作为主键时,由于ID是递增的,新插入的数据会追加到已有数据的末尾,这有利于维护索引的顺序性,提高了索引的性能。而使用雪花算法生成主键时,由于ID是随机的,新插入的数据可能会分散存储,导致索引的性能下降。

  2. 数据分布:在使用雪花算法生成主键时,由于ID是随机的,数据的分布更加均匀,可以更好地利用多核处理器和磁盘带宽,提高插入性能。而使用自增ID作为主键时,由于ID是递增的,可能会造成数据分布不均匀,影响了插入性能。

  3. 索引碎片:在高并发插入的情况下,使用自增ID作为主键可能会导致索引碎片化,影响性能。而使用雪花算法生成主键可以避免这种情况。

因此,实际中表结构设置为自增,但实际使用雪花算法生成主键数据插入效率相对较高的情况可能是由于数据分布更均匀、索引性能更好、避免了索引碎片等原因导致的。在具体情况下,需要根据实际的数据量、并发情况、查询模式等因素进行综合考虑和测试,选择合适的主键生成方式。

在高并发插入的情况下,使用自增ID作为主键可能会导致索引碎片化的情况如下:

  1. 插入位置:使用自增ID作为主键时,新插入的数据会追加到已有数据的末尾。在高并发插入的情况下,多个事务同时插入数据,由于每个事务插入的数据是按照递增的ID顺序进行的,可能会导致多个事务同时试图插入相邻的位置,这可能会导致页分裂或者页合并的情况。
  2. 页分裂和页合并:当多个事务同时插入数据时,数据库存储引擎可能需要为了容纳新的数据而进行页分裂,即将一个数据页分裂成两个,这可能会导致索引树的高度增加,影响查询性能。另外,当删除数据时,可能会出现页合并的情况,也会导致索引的重新组织和碎片化。
  3. 索引维护成本:在高并发插入的情况下,使用自增ID作为主键,数据库存储引擎需要频繁地进行索引的维护和重组,这会增加系统的开销和成本。

在删除数据后,占用的磁盘空间会减少,但是磁盘空间不会立即回收,而是由数据库决定回收时机。

在 MySQL 中使用 DELETE 命令删除数据后,磁盘空间不会立即回收的原因主要有以下几点:

  1. 数据并未真正从磁盘上删除:当使用 DELETE 命令删除数据时,MySQL 通常会将数据标记为已删除,而不是立即从磁盘上删除。这是因为立即释放磁盘空间可能会导致频繁的磁盘写入,影响数据库性能。因此,MySQL 通常会在后续的时刻进行磁盘空间的回收。
  2. 事务回滚:如果删除操作发生在一个事务中,并且该事务后来被回滚,那么被删除的数据可能会被恢复,因此 MySQL 不会立即释放相关的磁盘空间。
  3. 索引维护:当删除数据时,可能会导致相关的索引结构发生变化,需要进行索引的维护和重组。这可能需要一定的时间和资源,因此 MySQL 不会立即回收磁盘空间。
  4. 数据文件碎片:即使数据被删除,磁盘上的数据文件可能会出现碎片化,这些碎片在磁盘空间回收之前会占用一定的空间。

总的来说,MySQL 中使用 DELETE 命令删除数据后,磁盘空间不会立即回收是因为数据库引擎的设计考虑到了性能、事务一致性和数据文件的维护等因素。虽然磁盘空间不会立即回收,但是在后续的时刻,MySQL 会自动或者通过手动操作进行磁盘空间的回收和整理。

综上所述,在实际开发过程中,使用mysql数据库时可以将表结构设置为自增id,但是在高并发或大数据批量插入时使用雪花算法生成id进行数据插入操作。