MySQL Enum 性能优势？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/766299/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Enum performance advantage?
提问by gsueagle2008
Is there a performance advantage to using enum in situations where there are only 5-10 different possible values for a field? if not what is the advantage?
在一个字段只有 5-10 个不同可能值的情况下，使用 enum 是否有性能优势？如果不是，优势是什么？
采纳答案by Bill Karwin
There is a huge performance penaltyto using ENUMfor operations such as:
使用以下操作会带来巨大的性能损失ENUM：
Query the list of permitted values in the
ENUM, for instance to populate a drop-down menu. You have to query the data type fromINFORMATION_SCHEMA, and parse the list out of a BLOB field returned.Alter the set of permitted values. It requires an
ALTER TABLEstatement, which locks the table and may do a restructure.
查询 中允许的值列表
ENUM，例如填充下拉菜单。您必须从 查询数据类型INFORMATION_SCHEMA，并从返回的 BLOB 字段中解析列表。更改允许值集。它需要一个
ALTER TABLE语句，该语句锁定表并可以进行重组。
I'm not a fan of MySQL's ENUM. I prefer to use lookup tables. See also my answer to "How to handle enumerations without enum fields in a database?"
我不喜欢 MySQL 的ENUM. 我更喜欢使用查找表。另请参阅我对“如何处理数据库中没有枚举字段的枚举？”的回答。
回答by John Douthat
ENUMs are represented internally by 1 or 2 bytes, depending on the number of values. If the strings you're storing are larger than 2 bytes and rarely change, then an ENUM is the way to go. Comparison will be faster with an enum and they take up less space on disk, which in turn can lead to faster seek times.
ENUM 在内部由 1 或 2 个字节表示，具体取决于值的数量。如果您存储的字符串大于 2 个字节并且很少更改，那么 ENUM 是可行的方法。使用枚举进行比较会更快，并且它们在磁盘上占用的空间更少，这反过来又会导致更快的寻道时间。
The downside is that enums are less flexible when it comes to adding/removing values.
缺点是枚举在添加/删除值时不太灵活。
回答by Guillaume Gendre
In this article http://fernandoipar.com/2009/03/09/using-the-enum-data-type-to-increase-performance/Fernando looks into performances of Enum type for queries.
在这篇文章http://fernandoipar.com/2009/03/09/using-the-enum-data-type-to-increase-performance/ 中，Fernando 研究了 Enum 类型的查询性能。
The result is that while using ENUM might seem a little less elegant from a design point of view (if your ENUM value are changing sometimes), the performance gain is obvious for large datasets. See his article for details. Do you agree?
结果是，虽然从设计的角度来看，使用 ENUM 似乎不太优雅（如果您的 ENUM 值有时会发生变化），但对于大型数据集，性能提升是显而易见的。详见他的文章。你同意？
