MySQL 中 varchar 字段与 bigint 字段的性能比较

在本文中，我们将介绍 MySQL 中唯一约束下，使用 varchar 字段与使用 bigint 字段的性能比较。这个问题在实际开发中常常遇到，如何选择最合适的类型来保证性能和数据正确性就成了重要的决策。

阅读更多：MySQL 教程

背景

在 MySQL 中，我们经常会用到唯一约束来保证某个字段的值不重复。比如说，我们有一个用户表，其中用户名是唯一的。我们可以这样定义表：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

这段 SQL 定义了一个包含 id、username、password 三个字段的表。其中 id 是 bigint 类型的自增主键，而 username 是 varchar 类型的唯一键，用来区分不同用户。

这样的定义可以避免用户重复注册，确保数据的正确性。但是，如果表中的记录量大了起来，性能问题就会显现出来。

比如这样一个场景：我们有一个博客网站，有数百万用户。现在我们需要按照用户 ID 查询某个具体的用户信息，那么表现如何呢？我们可以使用下面的 SQL 语句：

SELECT id, username, password FROM `user` WHERE id = 123456;

对于这条查询语句，如果 id 字段是 varchar 类型的，在用户数据量大的情况下，查询速度就会相对较慢。而如果 id 字段是 bigint 类型的，则查询速度会更快一些。

实验

为了验证这个问题，我们可以编写一些测试用例。这里我们使用 Go 语言编写了一个测试脚本，用于在 MySQL 中生成千万级别的数据，并测试查询速度。脚本的主要代码如下：

func genData(count int64) {
    for i := int64(0); i < count; i++ {
        username := fmt.Sprintf("user%d", i)
        password := fmt.Sprintf("%d", util.RandInt(100000000, 999999999))

        var id mysql.NullInt64
        if enableVarchar {
            id.String = fmt.Sprintf("%d", i)
            id.Valid = true
        } else {
            id.Int64 = i
            id.Valid = true
        }

        _, err := db.Exec("INSERT INTO `user` (`id`, `username`, `password`) VALUES (?, ?, ?)",
            id, username, password)
        if err != nil {
            log.Fatal(err)
        }
    }
}

func doQuery(id int64) {
    var username string
    var password string
    var t1 time.Time
    var t2 time.Time

    t1 = time.Now()
    err := db.QueryRow("SELECT username, password FROM `user` WHERE id = ?", id).Scan(&username, &password)
    if err != nil {
        log.Fatal(err)
    }
    t2 = time.Now()

    log.Printf("Query result: %s, %s (elapsed: %s)", username, password, t2.Sub(t1).String())
}

测试分两步进行，第一步是生成测试数据，第二步是对数据表中的某个指定 ID 的记录进行查询，计算出查询时间。

我们分别测试了 id 字段为 bigint 类型和 varchar 类型的情况，并记录测试结果。下面是一组测试结果：

从上面的表格中可以看出：

• 随着数据量的增加，bigint 类型的查询速度越来越慢，但是相对来说变化不是非常明显。
• varchar 类型的查询时间在小数据量的情况下略快，但是随着数据量的增加，查询时间急剧增加，使得查询成为瓶颈。

结论

综合上面实验的结果，我们可以得出一些结论：

• 在 MySQL 中，使用 varchar 类型的唯一键查询性能比 bigint 类型差。
• 小型数据量下，varchar 类型执行查询的性能可能会优于 bigint 类型。但当数据量逐步变大时，varchar 类型的查询速度逐渐变慢。

总结

MySQL 中，选择合适的数据类型非常重要。根据具体的业务需求，选择性能最优的数据类型可以显著提高系统的性能。

在使用唯一键进行查询时，如果记录值是字符串类型，使用 varchar 类型会导致查询性能下降；如果记录值是数字类型，使用 bigint 类型则性能更优。因此，在设计数据库表结构时需要根据实际情况选择合适的数据类型。