MySQL 哪个更快： char(1) 或 tinyint(1) ？为什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2023476/
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
Which is faster: char(1) or tinyint(1) ? Why?
提问by Devner
MY PLATFORM:
我的平台：
PHP & mySQL
PHP & MySQL
MY SITUATION:
我的情况：
I came across a situation where I need to store a value for user selection in one of my columns of a table. Now my options would be to:
我遇到了一种情况，我需要在表格的一列中存储用户选择的值。现在我的选择是：
- Either declare the Column as char(1) and store the value as 'y' or 'n'
- Or declare the Column as tinyint(1) and store the value as 1 or 0
- This column so declared, may also be indexed for use within the application.
- 将 Column 声明为 char(1) 并将值存储为 'y' 或 'n'
- 或者将 Column 声明为 tinyint(1) 并将值存储为 1 或 0
- 如此声明的这一列也可以被索引以供在应用程序中使用。
MY QUESTIONS:
我的问题：
So I wanted to know, which of the above two types:
所以我想知道，以上两种类型中的哪一种：
Leads to faster query speed when that column is accessed (for the sake of simplicity, let's leave out mixing other queries or accessing other columns, please).
Is the most efficient way of storing and accessing data and why?
How does the access speed vary if the columns are indexed and when they are not?
访问该列时会导致更快的查询速度（为了简单起见，请不要混合其他查询或访问其他列）。
是存储和访问数据的最有效方式吗？为什么？
如果列被索引，当它们没有被索引时，访问速度如何变化？
My understanding is that since char(1) and tinyint(1) take up only 1 byte space, storage space will not be an issue in this case. Then what would remain is the access speed. As far as I know, numeric indexing is faster and more efficient than anything else. But the case here is tough one to decide, I think. Would definitely like to hear your experience on this one.
我的理解是，由于 char(1) 和 tinyint(1) 仅占用 1 个字节的空间，因此在这种情况下存储空间不会成为问题。那么剩下的就是访问速度了。据我所知，数字索引比其他任何东西都更快、更有效。但我认为，这里的案件很难决定。绝对想听听您在这方面的经验。
Thank you in advance.
先感谢您。
回答by Glen Solsberry
Rate insert tinyint(1) insert char(1) insert enum('y', 'n')
insert tinyint(1) 207/s -- -1% -20%
insert char(1) 210/s 1% -- -19%
insert enum('y', 'n') 259/s 25% 23% --
Rate insert char(1) insert tinyint(1) insert enum('y', 'n')
insert char(1) 221/s -- -1% -13%
insert tinyint(1) 222/s 1% -- -13%
insert enum('y', 'n') 254/s 15% 14% --
Rate insert tinyint(1) insert char(1) insert enum('y', 'n')
insert tinyint(1) 234/s -- -3% -5%
insert char(1) 242/s 3% -- -2%
insert enum('y', 'n') 248/s 6% 2% --
Rate insert enum('y', 'n') insert tinyint(1) insert char(1)
insert enum('y', 'n') 189/s -- -6% -19%
insert tinyint(1) 201/s 7% -- -14%
insert char(1) 234/s 24% 16% --
Rate insert char(1) insert enum('y', 'n') insert tinyint(1)
insert char(1) 204/s -- -4% -8%
insert enum('y', 'n') 213/s 4% -- -4%
insert tinyint(1) 222/s 9% 4% --
it seems that, for the most part, enum('y', 'n')is faster to insert into.
在大多数情况下，似乎enum('y', 'n')插入速度更快。
Rate select char(1) select tinyint(1) select enum('y', 'n')
select char(1) 188/s -- -7% -8%
select tinyint(1) 203/s 8% -- -1%
select enum('y', 'n') 204/s 9% 1% --
Rate select char(1) select tinyint(1) select enum('y', 'n')
select char(1) 178/s -- -25% -27%
select tinyint(1) 236/s 33% -- -3%
select enum('y', 'n') 244/s 37% 3% --
Rate select char(1) select tinyint(1) select enum('y', 'n')
select char(1) 183/s -- -16% -21%
select tinyint(1) 219/s 20% -- -6%
select enum('y', 'n') 233/s 27% 6% --
Rate select tinyint(1) select char(1) select enum('y', 'n')
select tinyint(1) 217/s -- -1% -4%
select char(1) 221/s 1% -- -2%
select enum('y', 'n') 226/s 4% 2% --
Rate select char(1) select tinyint(1) select enum('y', 'n')
select char(1) 179/s -- -14% -20%
select tinyint(1) 208/s 17% -- -7%
select enum('y', 'n') 224/s 25% 7% --
Selecting also seems to be the enum. Code can be found here
选择似乎也是enum。代码可以在这里找到
回答by Ivan Nevostruev
I think you should create column with ENUM('n','y'). Mysql stores this type in optimal way. It also will help you to store only allowed values in the field.
我认为您应该使用ENUM('n','y'). Mysql 以最佳方式存储这种类型。它还将帮助您仅在字段中存储允许的值。
You can also make it more human friendly ENUM('no','yes')without affect to performance. Because strings 'no'and 'yes'are stored only once per ENUMdefinition. Mysql stores only index of the value per row.
您还可以在ENUM('no','yes')不影响性能的情况下使其更加人性化。因为字符串'no'和'yes'每个ENUM定义只存储一次。Mysql 只存储每行值的索引。
Also note about sorting by ENUMcolumn:
另请注意按ENUM列排序：
ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a').
ENUM 值根据枚举成员在列规范中列出的顺序进行排序。（换句话说，ENUM 值根据它们的索引号排序。）例如，对于 ENUM('a', 'b')，'a' 在 'b' 之前排序，但对于 ENUM，'b' 在 'a' 之前排序（'b'，'a'）。
回答by Matchu
Using tinyint is more standard practice, and will allow you to more easily check the value of the field.
使用 tinyint 是更标准的做法，可以让您更轻松地检查字段的值。
// Using tinyint 0 and 1, you can do this:
if($row['admin']) {
// user is admin
}
// Using char y and n, you will have to do this:
if($row['admin'] == 'y') {
// user is admin
}
I'm not an expert in the inner workings of MySQL, but it intuitively feels that retrieving and sorting integer fields is faster than character fields (I just get a feeling that 'a' > 'z' is more work that 0 > 1), and seems to feel much more familiar from a computing perspective in which 0s and 1s are the standard on/off flags. So the storage for integers seems to be better, it feels nicer, and is easier to use in code logic. 0/1 is the clear winner for me.
我不是 MySQL 内部工作的专家，但直觉上觉得检索和排序整数字段比字符字段更快（我只是觉得 'a' > 'z' 比 0 > 1 更有效） ，并且从计算的角度来看似乎更熟悉，其中 0 和 1 是标准的开/关标志。所以整数的存储似乎更好，感觉更好，并且更容易在代码逻辑中使用。0/1 对我来说是明显的赢家。
You may also note that, to an extent, this is MySQL's official position, as well, from their documentation:
您可能还注意到，在某种程度上，这也是 MySQL 的官方立场，来自他们的文档：
BOOL, BOOLEAN: These types are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.
BOOL、BOOLEAN：这些类型是 TINYINT(1) 的同义词。零值被认为是错误的。非零值被认为是真的。
If MySQL goes so far as to equate TINYINT(1) with BOOLEAN, it seems like the way to go.
如果 MySQL 将 TINYINT(1) 与 BOOLEAN 等同起来，这似乎是要走的路。
回答by Jan Fabry
To know it for sure, you should benchmark it. Or know that it probably will not matter that much in the grander view of the whole project.
要确定地了解它，您应该对其进行基准测试。或者知道从整个项目的宏观角度来看，这可能没有那么重要。
Char columns have encodings and collations, and comparing them could involve unnecessary switches between encodings, so my guess is that an int will be faster. For the same reason, I think that updating an index on an int column is also faster. But again, it won't matter much.
Char 列有编码和排序规则，比较它们可能涉及编码之间不必要的切换，所以我的猜测是 int 会更快。出于同样的原因，我认为更新 int 列上的索引也更快。但同样，这也没什么大不了的。
CHARcan take up more than one byte, depending on the character set and table options you choose. Some characters can take three bytes to encode, so MySQL sometimes reserves that space, even if you only use yand n.
CHAR可以占用多个字节，具体取决于您选择的字符集和表选项。有些字符可能需要三个字节来编码，因此 MySQL 有时会保留该空间，即使您只使用y和n。
回答by Dave Markle
They're both going to be so close that it doesn't matter. If you feel have to ask this question on SO, you're over-optimizing. Use whichever one makes the most logical sense.
他们都将如此接近以至于无关紧要。如果您觉得必须在 SO 上问这个问题，那么您就过度优化了。使用最合乎逻辑的那个。
回答by Powerlord
If you specify the types BOOLor BOOLEANas a column type when creating a table in MySQL, it creates the column type as TINYINT(1). Presumably this is the faster of the two.
如果在 MySQL 中创建表时指定类型BOOL或BOOLEAN列类型，则会将列类型创建为TINYINT(1). 据推测，这是两者中更快的一个。
Also:
还：
We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.
我们打算在未来的 MySQL 版本中根据标准 SQL 实现完整的布尔类型处理。
回答by zombat
While my hunch is that an index on a TINYINT would be faster than an index on a CHAR(1) due to the fact that there is no string-handling overhead (collation, whitespace, etc), I don't have any facts to back this up. My guess is that there isn't a significant performance difference that is worth worrying about.
虽然我的预感是 TINYINT 上的索引会比 CHAR(1) 上的索引快，因为没有字符串处理开销（排序规则、空格等），但我没有任何事实支持这一点。我的猜测是没有值得担心的显着性能差异。
However, because you're using PHP, storing as a TINYINT makes much more sense. Using the 1/0 values is equivalent to using trueand false, even when they are returned as strings to PHP, and can be handled as such. You can simply do a if ($record['field'])with your results as a boolean check, instead of converting between 'y' and 'n' all the time.
但是，因为您使用的是 PHP，所以存储为 TINYINT 更有意义。使用 1/0 值等同于使用trueand false，即使它们作为字符串返回给 PHP，并且可以这样处理。您可以简单地if ($record['field'])将结果作为布尔值检查，而不是一直在 'y' 和 'n' 之间进行转换。
回答by streetparade
TINYINT 1 Byte
CHAR(M) M Bytes, 0 <= M <= 255
is there any different?
有什么不同吗？
