如何选择列的优化数据类型[innodb specific]？ [英] How to choose optimized datatypes for columns [innodb specific]?

问题描述

我正在了解数据库的数据类型的用法。

例如：

• 哪个更适合电子邮件？ varchar [100]，char [100]或tinyint（joking）


• 哪个更适合用户名？应该使用int，bigint还是varchar？
  说明。我的一些朋友说，如果我们使用int，bigint或另一个数字数据类型，它会更好（Facebook的确）。像u = 123400023指用户123400023，而不是用户=用户名。因为号码获取的时间较少。


• 哪些更适合电话号码？帖子（如在博客或宣告）？或者也许日期（我使用datetime的）？


• 产品价格（我使用十进制（11,2），不知道你们）


• 或任何其他您想象的，例如我使用blablabla的序列数据类型。

除非你使用InnoDB表
类型（见第11章Advanced
MySQL ，更多信息），CHAR
列的访问速度比
VARCHAR快。

<一些差异，我不知道。
我在这里阅读。

解决方案

摘要：

em>

1. 电子邮件地址 - VARCHAR c $ c>


2. for username - VARCHAR（100）或 VARCHAR（255）


3. 对于id_username - 使用 INT （除非您计划在系统中拥有超过20亿的用户）


4. 电话号码 - INT 或 VARCHAR 或也许 CHAR （取决于您是否要存储格式）


5. posts - TEXT


6. 日期 - DATE 或 DATETIME （绝对包含发布或电子邮件等时间）


7. 钱 - DECIMAL（11,2）


8. 其他 - 见下文

至于使用InnoDB，因为 VARCHAR 应该更快，我不担心，一般来说。使用InnoDB，因为您需要执行事务和/或您想要使用外键约束（FK）来实现数据完整性。此外，InnoDB使用行级锁定，而MyISAM仅使用表级锁定。因此，InnoDB可以比MyISAM更好地处理更高级别的并发。使用MyISAM可以使用全文索引并减少一些开销。

更重要的是速度比引擎类型：在您需要快速搜索的列上放置索引。

更多详情：

这里有一系列关于MySQL数据类型和数据库设计的问题（警告，超出了你的要求）：

• 我应该选择什么DataType？




• 表格设计问题




• 枚举数据类型与数据表MySQL




• 用于电话号码和地址的mysql数据类型




• 克，毫克，微克和千焦的最佳mysql数据类型




• MySQL 5星级评级数据类型

还有一些关于何时使用InnoDB引擎的问题：

• MyISAM与InnoDB




• 您应该何时选择使用InnoDB MySQL

$

$ b> b
$ b

下面是一些具有更多细节的链接，但这里是短版本。为了存储posts，你需要一个长的文本字符串的空间。 CHAR 最大长度是255，所以这不是一个选项，当然 CHAR 会浪费未使用的字符与 VARCHAR ，它是变量长度 CHAR 。

< .3， VARCHAR 最大长度为255，因此您可以使用 TEXT 。但是，在较新版本的MySQL中，您可以使用 VARCHAR 或 TEXT 。选择取决于喜好，但有一对夫妇的差异。 VARCHAR 和 TEXT 最大长度现在都是65,535，但是你可以在 VARCHAR 。让我们假设你的帖子只需要2000的最大值，你可以设置 VARCHAR（2000）。如果你每次都达到极限，你可以稍后 ALTER 将它转到 VARCHAR（3000）。另一方面， TEXT 实际上将其数据存储在 BLOB （1）中。我听说过 VARCHAR 和 TEXT 之间可能有性能差异，但我没有看到任何证据，所以你可能想要查看更多，但你总是可以改变以后的细节。

更重要的是，使用Full-文本索引而不是 LIKE 会更快（2）。但是，您必须使用MyISAM引擎使用全文索引，因为InnoDB不支持它。在MySQL数据库中，您可以为每个表具有异构的引擎组合，因此您只需要使您的posts表使用MyISAM。但是，如果你绝对需要posts使用InnoDB（事务），然后设置一个触发器来更新你的posts表的MyISAM副本，并使用MyISAM副本进行所有的全文搜索。

MySQL数据类型图表（过期）



• MySQL数据类型（过期） p>




• 第10章数据类型（更好的细节）




• BLOB和TEXT类型（1）




• 11.9。全文搜索功能（2）




• 10.4.1。 CHAR和VARCHAR类型（3）

VARCHAR列中的值是
可变长度字符串。在MySQL 5.0.3之前，长度
可以指定为从0到
255的值，在5.0中为0到
65,535 .3和更高版本。

在MySQL 5.0.3之前，如果您需要一个数据
类型，其中尾随空格不是
，使用BLOB或TEXT
类型。

当存储CHAR值时，它们是
，用指定的

在MySQL 5.0.3之前，删除尾随空格
从值
存储到VARCHAR列;这个
意味着空间也不存在
从检索的值。

最后，这里有一篇关于VARCHAR和TEXT的利弊的文章。它还说明性能问题：

• VARCHAR（n）被视为有害

I'm learning about the usage of datatypes for databases.

For example:

• Which is better for email? varchar[100], char[100], or tinyint (joking)
• Which is better for username? should I use int, bigint, or varchar? Explain. Some of my friends say that if we use int, bigint, or another numeric datatype it will be better (facebook does it). Like u=123400023 refers to user 123400023, rather then user=thenameoftheuser. Since numbers take less time to fetch.
• Which is better for phone numbers? Posts (like in blogs or announcments)? Or maybe dates (I use datetime for that)? maybe some have make research that would like to share.
• Product price (I use decimal(11,2), don't know about you guys)?
• Or anything else that you have in mind, like, "I use serial datatype for blablabla".

Why do I mention innodb specifically?

Unless you are using the InnoDB table types (see Chapter 11, "Advanced MySQL," for more information), CHAR columns are faster to access than VARCHAR.

Inno db has some diffrence that I don't know. I read that from here.

解决方案

Brief Summary:

(just my opinions)

1. for email address - VARCHAR(255)
2. for username - VARCHAR(100) or VARCHAR(255)
3. for id_username - use INT (unless you plan on over 2 billion users in you system)
4. phone numbers - INT or VARCHAR or maybe CHAR (depends on if you want to store formatting)
5. posts - TEXT
6. dates - DATE or DATETIME (definitely include times for things like posts or emails)
7. money - DECIMAL(11,2)
8. misc - see below

As far as using InnoDB because VARCHAR is supposed to be faster, I wouldn't worry about that, or speed in general. Use InnoDB because you need to do transactions and/or you want to use foreign key constraints (FK) for data integrity. Also, InnoDB uses row level locking whereas MyISAM only uses table level locking. Therefore, InnoDB can handle higher levels of concurrency better than MyISAM. Use MyISAM to use full-text indexes and for somewhat less overhead.

More importantly for speed than the engine type: put indexes on the columns that you need to search on quickly. Always put indexes on your ID/PK columns, such as the id_username that I mentioned.

More details:

Here's a bunch of questions about MySQL datatypes and database design (warning, more than you asked for):

• What DataType should I pick?

• Table design question

• Enum datatype versus table of data in MySQL?

• mysql datatype for telephne number and address

• Best mysql datatype for grams, milligrams, micrograms and kilojoule

• MySQL 5-star rating datatype?

And a couple questions on when to use the InnoDB engine:

• MyISAM versus InnoDB

• When should you choose to use InnoDB in MySQL?

I just use tinyint for almost everything (seriously).

Edit - How to store "posts:"

Below are some links with more details, but here's the short version. For storing "posts," you need room for a long text string. CHAR max length is 255, so that's not an option, and of course CHAR would waste unused characters versus VARCHAR, which is variable length CHAR.

Prior to MySQL 5.0.3, VARCHAR max length was 255, so you'd be left with TEXT. However, in newer versions of MySQL, you can use VARCHAR or TEXT. The choice comes down to preference, but there are a couple differences. VARCHAR and TEXT max length is now both 65,535, but you can set you own max on VARCHAR. Let's say you think your posts will only need to be 2000 max, you can set VARCHAR(2000). If you every run into the limit, you can ALTER you table later and bump it to VARCHAR(3000). On the other hand, TEXT actually stores its data in a BLOB (1). I've heard that there may be performance differences between VARCHAR and TEXT, but I haven't seen any proof, so you may want to look into that more, but you can always change that minor detail in the future.

More importantly, searching this "post" column using a Full-Text Index instead of LIKE would be much faster (2). However, you have to use the MyISAM engine to use full-text index because InnoDB doesn't support it. In a MySQL database, you can have a heterogeneous mix of engines for each table, so you would just need to make your "posts" table use MyISAM. However, if you absolutely need "posts" to use InnoDB (for transactions), then set up a trigger to update the MyISAM copy of your "posts" table and use the MyISAM copy for all your full-text searches.

See bottom for some useful quotes.

• MySQL Data Type Chart (outdated)

• MySQL Datatypes (outdated)

• Chapter 10. Data Types (better details)

• The BLOB and TEXT Types (1)

• 11.9. Full-Text Search Functions (2)

• 10.4.1. The CHAR and VARCHAR Types (3)

(3) "Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

Before MySQL 5.0.3, if you need a data type for which trailing spaces are not removed, consider using a BLOB or TEXT type.

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values."

Lastly, here's a great post about the pros and cons of VARCHAR versus TEXT. It also speaks to the performance issue:

• VARCHAR(n) Considered Harmful

这篇关于如何选择列的优化数据类型[innodb specific]？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！