在 MySQL 中使用字符串枚举 - 性能问题
我目前在 MySQL 数据库中使用枚举作为 TINYINT。我的表中有几百万行。然后我们将枚举从 TINYINT 映射到其他地方的字符串。
我正在考虑将这些枚举存储为字符串。如果字符串索引正确，有谁知道 MySQL 在查询字符串枚举而不是 TINYINT 枚举时如何执行？
感谢您的帮助。谢谢。
I currently use enums as TINYINTs in MySQL database. My tables have a few million rows in them. We then map the enums from TINYINT to Strings elsewhere.
I am considering storing these enums as Strings. If the Strings are indexed properly, does any one know how MySQL performs when querying on String enums instead of TINYINT enums?
Appreciate the help. Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
枚举的存储方式就像存在一个查找表一样，因此每行上仅存储一个数字引用，而其他地方则存储一个查找表。从技术上讲，匹配枚举的整数值与匹配整数一样快，因为两者相同，而匹配字符串值则稍微慢一些（在所有情况下除外），仅仅是因为只进行了一次“搜索”，通过查找表来查找整数值，然后使用该整数值来执行查找。
一个例子是，如果你搜索一个不存在的值，尝试匹配 enum 的数值来匹配一个不存在的值，它仍然会搜索所有数据，但尝试匹配一个不存在的字符串值不存在，并且通过查找表进行较小的搜索，在搜索任何数据之前不返回任何匹配项。
不要使用枚举来表示“是/否”或“男性/女性”答案，因为数字数据和查找表占用的空间比“位”值（0 或 1）大得多。另外，不要在枚举定义中使用数字字符串，例如 enum("1","0")，因为这会让任何查看代码和查询的人感到困惑（因为存储的查找表是 [0 => " 1”，1 =>“0”]。
Enums are stored as if there was a lookup table, so only a numerical reference is stored on each row, with a lookup table elsewhere. Technically, matching the integer value of an enum is as fast as matching an integer as the two are identical, and matching a string value is marginally slower (in all instances bar one), simply because only one more "search" is done, through the lookup table to find the integer value, which is then used to perform the lookup.
That one instance is if you search for a value that doesn't exist, try matching the numerical value of enum for a value that doesn't exist, and it will still search through all the data, but try matching a string value that doesn't exist, and a smaller search through the lookup table is done, returning no match before any data is searched.
Don't use enums for "yes/no" or "male/female" answers, as the numerical data and lookup table take up far more room than a "bit" value (0 or 1). Also, don't use numerical strings in the enum definition, e.g. enum("1","0") as this is confusing for anyone looking at the code and the query (because the lookup table stored is [0 => "1", 1 => "0"].
枚举似乎 根据这些基准测试，略有优势。
Enums seem to have a slight edge, according to these benchmarks.
（假设您使用 TINYINT 并有一个查找表）TINYINT 速度更快，但您最终可能会因为连接而看到一些性能下降。
我的建议是，如果您的枚举值不会更改（例如 - 男性、女性类型，是的，没有枚举类型），请改用 MYSQL ENUM 字段。它使用 1 个字节存储（如果枚举值小于 255），并且不需要连接。
但请注意，在尝试之前请先阅读 ENUM 数据类型的所有优点和缺点。
(Assuming you are using TINYINT and have a lookup table) TINYINT is faster, but you might end up seeing some performance hits because of joins.
My advice, if your enumeration values wont change (eg - Male, Female types, yes no types of enums), use MYSQL ENUM field instead. It gets stored using 1 byte (if enum values are less than 255), and doesnt need joins.
But be warned and read up all the pros and cons of ENUM data types before taking the plunge.
与
VARCHAR或CHAR相比，TINYINT（或用于此目的的INTEGER）索引速度更快，并且在 SORT 或 SELECT 中提供更好的性能>。所以，回答你，是的，字符串索引正确，但性能可能会下降。我无法说出这将有多么重要。
编辑 1 更多信息
似乎较小的
VARCHAR和INTEGER在索引方面的性能差异可以忽略不计。请参阅此处 http://forums.mysql.com/read.php ?115,251611,252006#msg-252006TINYINT(orINTEGERfor that purpose) is indexed faster and gives better performance in SORT or SELECTs thanVARCHARorCHAR.So, to answer you, yeah Strings are indexed properly but the performance might go down. I can't tell how significant that would be.
Edit 1 more info
Seems like smaller
VARCHARandINTEGERhas negligible performance difference in indexing. See here http://forums.mysql.com/read.php?115,251611,252006#msg-252006