Mysql: 如何将多个整数字段合并为一个字符串字段？

Question

假设我有一个包含数百万行的表，其中有三个整数变量：x、y和z，根据这些变量来进行SELECT查询，例如WHERE x=a and y=b and z=c。哪种方法更快/更有效？

• 将这3个字段组合成单独的字符串列“x_y_z”（例如1231_3242_6864）并对其进行索引

• 使用三个整数列创建索引？

- paullb

---

每个整数的最大值是多少？ - Sebas

为了论证的目的，假设每个有50000个。 - paullb

就存储大小而言，三个“UNSIGNED SMALLINT”字段总共需要6个字节，而像“12345_67890_24680”这样的字符串可能需要17个字节。同样的原则也适用于索引的RAM使用。一些方面可以进行调整，但在我的快速测试中，一个字符串表是130 MB数据/ 170 MB索引，而三个Int表是97 MB数据/ 76 MB索引。 - Wiseguy

这很简单：3列和复合索引。 - Imre L

Answer 1

不，那样会更糟，字符串比较要慢得多。如果确实需要的话，你可以将这3个整数组合成一个整数，但前提是它们适合。

然而，为了解决你的索引问题，最简单的方法是在x、y和z上创建一个复合索引。

- mb14

---

你不能将整数合并成一个大列。想象一下情况(x=1，y=3，z=5)和(x=5，y=1，z=3)，它们都加起来相同的值，但意义却非常不同。 - EkoostikMartin

不，你不需要将它们相加。但是例如对于 x、y、z = 3、5、7，结果将会是 3000005000007。这就是我问整数大小的原因。 - Sebas

@Ekoo 当然你不会添加它们，但是你可以像Sebas所说的那样（或者在二进制中移位），但这取决于每个大小的最大值。这就是我所说的“如果它们适合”的意思。 - mb14

我明白了，如果您能保证连接适合“bigint”，那么为什么会建议不要使用它呢？毫无疑问，一个列索引的性能要比三个列组合索引好得多。 - EkoostikMartin

@Ekoo 一个列索引可能比三个更好，但是由三个整数组成的复合索引可能更好且更方便。 - mb14

非常正确，我猜在95%以上的情况下，3个整数组合已经足够了。 - EkoostikMartin

Answer 2

如果您可以使用覆盖索引，并且始终提供所有三个数字，因此不必担心索引顺序（请注意，这也是字符串版本中的问题），我将使用三个整数的复合索引。

这三个整数将占用更少的空间，使更多的行适合每个页面，通常在读取索引时使索引更有效。与五字节字符串99999（+/- ~2^31的四个字节）相比，您还有更多的整数头部空间。

效率的大小在数据库查询中很难判断，但请记住，您还需要组装和填充它们。不确定您计划在MySQL中如何或何处执行此操作- SQL Server具有持久化计算列，如果您要提交到字符串版本，则可能是一个很好的设计选择。

当然，我们不希望在执行连接之前将整数转换为字符串并动态连接它们。

- Cade Roux

Answer 3

我认为你需要自己进行基准测试（肯定还有其他因素会影响特定数据上的特定查询性能），但除了使用@mb14提出的复合三列索引的想法之外，你还可以尝试这个：

在你的表上创建一个新列，像这样：

xyzcomposite BINARY(16)

接下来，在这个列上创建你的索引。

在插入时，你需要执行一个额外的步骤，将你的字符串“x_y_z”连接起来，然后像这样插入：

INSERT INTO yourtable (...,xyzcomposite) VALUES (...,UNHEX(MD5('the_xyz_concat')));

当然，在执行选择语句之前，您还需要运行哈希算法。

set @xyz = UNHEX(MD5('x_y_z'));
select * from yourtable where xyzcomposite = @xyz

我不确定这种开销的总和是否值得你为了只索引一个列而获得的性能提升，相比于三个列的索引。就像我说的，你需要根据你的表格和数据进行基准测试。

编辑：这种方法的优点是它适用于任何大小的 x、y 和 z 数字。

- EkoostikMartin

Answer 4

讨论中还有一些其他相关的问题：

索引性能 BigInt vs VarChar

INT 和 VARCHAR 主键之间是否存在真正的性能差异？

何时应该使用复合索引？

从关于复合索引的问题中，其中一个答案似乎表明，如果您的 x、y 和 z 在它们自己之间趋于唯一（即大多数 x 值彼此不同，依此类推），那么您也不会得到太多的性能提升。但如果它们确实如此，那么复合索引似乎是正确的选择。

- Andrew C

---

当查询为WHERE x=a AND y=b AND z=c时，不存在值重叠或不重叠的问题，索引将非常高效。该答案是关于具有空间搜索的查询（两个范围条件），而不是3个等式条件的查询。 - ypercubeᵀᴹ

重叠的值可能不是我正在寻找的确切短语，也许x、y和z的唯一性更准确。基本上，这个想法是只有当多个x = a时索引才有帮助，依此类推。 - Andrew C

当然，如果表中的所有行都有x=a，y=b和z=c，索引就没有太大帮助:) 这是索引的“选择性”（关于查询参数）。它与索引是否有帮助以及是否被使用有关。但这与您链接的答案无关。 - ypercubeᵀᴹ