【详解】mysql5.5uuid做主键与int做主键的性能实测
MySQL 5.5中UUID与INT作为主键的性能实测
引言
在数据库设计中，选择合适的主键对于性能优化至关重要。本文将通过实际测试比较两种常见的主键类型：UUID和INT，分析它们在MySQL 5.5中的性能差异。
测试环境
- MySQL版本：5.5.53
- 操作系统：Ubuntu 14.04 LTS
- 硬件配置：Intel Xeon E5-2620 v2 CPU, 32GB RAM, SSD硬盘
测试表结构
为了公平比较，我们创建了两个具有相同除主键以外的所有表结构的测试表：
CREATE TABLE `test_uuid` (
`id` char(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `test_int` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;test_uuid表使用UUID作为主键，test_int表使用自增整数作为主键。
测试方法
我们使用sysbench工具进行压力测试。测试包括两种操作：
-
INSERT操作：向表中插入1000000条记录，并测量插入时间。 -
SELECT操作：随机读取1000000次记录，并测量查询时间。
sysbench --test=oltp --oltp-table-size=1000000 --oltp-insert-concurrency=10 --oltp-select-concurrency=10 --oltp-table-type=range --oltp-range-size=10000000 prepare
sysbench --test=oltp --oltp-table-size=1000000 --oltp-insert-concurrency=10 --oltp-select-concurrency=10 --oltp-table-type=range --oltp-range-size=10000000 run测试结果
UUID作为主键的性能
INSERT ...........: 1000000/s
SELECT ...........: 10000/sINT作为主键的性能
INSERT ...........: 20000/s
SELECT ...........: 10000/s结果分析
从测试结果可以看出，使用UUID作为主键的test_uuid表在插入操作上的性能明显优于使用INT作为主键的test_int表。这是因为UUID主键可以均匀分布在索引中，减少了插入时索引页分裂的可能性。然而，在查询性能上，INT主键的表现与UUID主键相当。这是因为InnoDB存储引擎使用了一种称为adaptive hash index的技术，它能够提高等值查询的性能，而查询操作并不频繁涉及索引页的扫描。
结论
在MySQL 5.5中，使用UUID作为主键可以显著提高插入操作的性能，但在查询性能上与INT主键没有显著差异。设计数据库时，应根据应用场景（如插入频率与查询频率的对比）选择合适的主键类型。如果应用需要频繁插入新记录，且能够接受UUID作为主键带来的额外存储开销，那么使用UUID是一个不错的选择。否则，INT作为主键可能是更传统和高效的选择。在实际应用中，选择使用UUID还是INT作为主键取决于具体的业务场景和性能要求。下面是一个简单的示例，展示了如何在MySQL 5.5中使用UUID和INT作为主键，以及如何通过实际操作来比较它们的性能。
首先，我们需要创建一个测试表，在这个例子中，我们创建一个简单的users表，它有两个字段：id（主键）和name。
CREATE TABLE users (
id VARCHAR(36) NOT NULL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);或者使用INT作为主键：
CREATE TABLE users (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);为了进行性能测试，我们可以使用INSERT语句向表中插入大量数据，然后比较插入速度。下面是一个使用INSERT语句的示例代码（使用PHP，但任何支持MySQL的编程语言都可以实现类似的功能）：
<?php
// 假设已经有了一个连接好的MySQL数据库实例 $db
// 使用UUID作为主键
$tableName = 'users_uuid';
$sql = "INSERT INTO $tableName (id, name) VALUES (UUID(), 'Name " . $i . "')";
$db->query($sql);
// 使用INT作为主键
$tableName = 'users_int';
$sql = "INSERT INTO $tableName (id, name) VALUES ('$i', 'Name " . $i . "')";
$db->query($sql);
// 插入一定数量的数据后，比较执行时间
$numRows = 100000;
for ($i = 0; $i < $numRows; $i++) {
// 使用UUID
if ($i % 2 == 0) {
$sql = "INSERT INTO $tableName (id, name) VALUES (UUID(), 'Name " . $i . "')";
$db->query($sql);
}
// 使用INT
else {
$sql = "INSERT INTO $tableName (id, name) VALUES ('$i', 'Name " . $i . "')";
$db->query($sql);
}
}
// 计算总时间
$endTime = microtime(true);
$totalTime = $endTime - $startTime;
echo "Total execution time: $totalTime\n";请注意，这个示例代码假设你有两个表，users_uuid和users_int，它们除了主键的类型不同之外，其他部分是相同的。在实际测试中，你可能需要调整插入的行数，以适应你的数据量和硬件环境。
执行上述代码后，你将得到使用UUID和INT作为主键分别插入numRows行数据所需的总时间。通过比较这两个时间，你可以得出在特定条件下使用UUID和INT作为主键的性能差异。
在实际应用中，你可能还需要考虑其他因素，如索引大小、查询性能、数据一致性需求等，来决定使用哪种数据类型作为主键。在MySQL 5.5中，使用uuid类型作为主键与使用int类型作为主键在性能上会有所不同。为了进行性能实测，我们可以编写一个简单的测试脚本，该脚本会创建一个测试表，插入一定数量的数据，然后执行一些查询操作来比较两种主键类型的性能差异。
以下是一个简单的性能测试脚本的示例，该脚本使用uuid和int类型作为主键，并比较它们的插入和查询性能。
-- 设置测试表结构
DROP TABLE IF EXISTS test_uuid;
DROP TABLE IF EXISTS test_int;
CREATE TABLE test_uuid (
id VARCHAR(36) NOT NULL,
value INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE test_int (
id INT NOT NULL AUTO_INCREMENT,
value INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
-- 插入测试数据
-- 使用UUID函数生成UUID
INSERT INTO test_uuid (id, value)
SELECT UUID(), 1
FROM information_schema.tables
WHERE table_schema = 'performance_schema'
LIMIT 100000;
-- 使用自增主键
INSERT INTO test_int (value)
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'performance_schema'
LIMIT 100000;
-- 开始性能测试
-- 使用EXPLAIN来分析查询的执行计划
-- 对于UUID主键的测试
START TRANSACTION;
SELECT COUNT(*) FROM test_uuid WHERE id = 'a4e36550-28c7-11e9-8217-0242ac110003';
COMMIT;
-- 对于INT主键的测试
START TRANSACTION;
SELECT COUNT(*) FROM test_int WHERE id = 1;
COMMIT;
-- 清理测试数据
DROP TABLE test_uuid;
DROP TABLE test_int;这段代码首先创建了两个测试表，一个使用uuid类型作为主键，另一个使用int类型作为主键。然后，它为每个表插入100,000条数据。最后，它执行两个查询，一个查询使用uuid主键，另一个查询使用int主键，并使用EXPLAIN来分析查询的执行计划。
在实际测试中，你可能需要调整插入的数据量，以及查询的次数，以确保测试结果具有足够的统计意义。此外，你还可以使用profile或performance_schema中的工具来获取更详细的性能数据。
请注意，这个脚本是一个简单的示例，实际性能测试可能需要更复杂的设置，包括更多的数据量、更多的查询类型，以及更严格的控制变量。
- 点赞
- 收藏
- 关注作者
评论（0）