mysql - 在 mysql/innodb 中拥有超过 500M varchar(255) 记录的唯一键的最佳方法？

Question

我的 url 列上有唯一键 - 但它的更新性能绝对糟糕。我怀疑这是因为索引并不完全适合内存。

所以我在想，如何添加一个包含 16 字节二进制数据的 md5(url) 列并对其进行唯一键控。

最好的数据类型是什么？我希望能够只看到 32 个字符的十六进制散列，而 mysql 会将其转换为 16 个二进制字节并对其进行索引，因为使用数据库的程序可能会遇到一些我宁愿避免的任意二进制数据的问题如果可能的话(我也有点担心 mysql 可能会得到一些关于字符集的奇怪想法，例如 3:1 的过度分配存储，因为它认为它可能需要 utf8，我如何避免它来治愈？)。

似乎某种解决方案是 binary(16) null 用于存储，unhex(md5(url)) 用于插入/比较，hex( url_hash) 用于检索(并不是说它真的需要检索，无论如何那里都会有未索引的 url 列)。这是最好的方法吗？

Answer 1

MD5 不保证唯一，因此您不能在其上创建唯一索引，除非您的业务模型允许您在发生冲突时完全拒绝插入和更新。是这样吗？我问是因为从性能的角度来看，解决碰撞(无论多么不可能)将被证明是极其复杂的。

在任何情况下，我都很难相信(并不是说它可能不是真的)一个结构正确的查询，由 MySQL 正确规划以使用正确的索引(甚至超过 500M 行)，会不得不忍受糟糕的性能——但如果不知道您的查询是什么样子以及您的数字是多少，又很难说清楚。

如果我是你，在考虑现有索引查找的变通方法(例如 MD5 方法)之前，我会绝对确定我的问题真正出在哪里:

• 使用EXPLAIN确认您的 UPDATE 语句确实使用了正确的索引
  • 您不能EXPLAIN UPDATE 语句，但您可以EXPLAIN 其等效的SELECT 语句(您本质上关心关于 WHERE 子句、JOIN 等)
  • 即使有 500M 行，btree 索引也应该只需要每个匹配行的少量页面
    • 您希望每个 UPDATE 语句更新多少行？实际更新了多少行？
    • 除了 url= 之外，您的 WHERE 子句中是否还有其他条件？计划者可能会首先选择选择性较低的索引并破坏您的缓存——从 EXPLAIN 计划中找出
  • 当您实际运行(不是EXPLAIN)它们时:UPDATE 是否系统地比其相应的SELECT 慢？您可能遇到写入瓶颈，可能是由于锁定问题。在缓慢的 UPDATE 时有多少 session 处于事件状态？您的表中定义了多少索引包含 url 列？
  • 有没有analyzed你最近的 table ？

所以无论如何，在继续之前，请让我们知道:

• 您是否在进行批量UPDATE？多少 UPDATE 秒(或每个 UPDATE 多少毫秒)可以满足您的性能要求？
• UPDATE 时有多少 session 处于事件状态？
• 你分析过你的表格吗？
• 什么是示例 UPDATE 查询？ (请为其参数提供具体值)
• 对应的SELECT的解释计划是什么？ (使用相同的特定值)
• 相应的 SELECT(使用相同的特定值)在执行时实际需要多长时间才能完成(不是 EXPLAINed)，以及执行的是哪一行实际上返回？
• 执行实际的UPDATE(使用相同的特定值)需要多长时间？ (不是 EXPLAINed)