When I'm setting up a MySQL table, it asks me to define the name of the column, type of input, and length. My assumption, without having read anything about it, is that it's for minimization. Specify the smallest possible int/smallint/tinyint for your needs, and it will reduce overhead of some sort. If it's all positives, make it unsigned to double your space, etc.
当我设置MySQL表时,它要求我定义列的名称,输入类型和长度。我没有读过任何关于它的内容的假设是,它是为了最小化。根据需要指定最小的int / smallint / tinyint,它将减少某种开销。如果它都是正面的,那就让它无符号以使你的空间加倍,等等。
What happens if I just make every field a varchar-200 characters? When/why is this bad, what will I miss out on, and when will any inefficiencies manifest themselves? 100k records?
如果我只是让每个字段变成一个varchar-200字符会发生什么?什么时候/为什么这么糟糕,我会错过什么,何时会出现效率低下的情况? 100k记录?
I think about this every time I set up a DB, but I haven't built anything to scale enough where I've ever had my scheme setup inappropriately, either too "strict/small" or "loose/big". Can someone confirm that I'm making good assumptions about speed and efficiency?
每次我设置一个数据库时都会考虑这个问题,但是我还没有构建任何足够扩展的东西,而且我的方案设置不合适,要么“严格/小”或“宽松/大”。有人可以证实我对速度和效率做出了很好的假设吗?
Thanks!
3 个解决方案
#1
3
Data types not only optimize storage, but how data is indexed. As your databases get bigger, it will become apparent that it's quicker to search for all the records that have a 1 in an integer field than those that have a "1" in a varchar field. This becomes especially important when you're joining data from more than one table and your database engine is having to do this sort of thing repeatedly. (Daren also rightly points out below that it's important that the types of the fields you're matching on are identical as well.)
数据类型不仅可以优化存储,还可以优化数据的索引方式。随着数据库变得越来越大,很明显,搜索整数字段中的1的所有记录比在varchar字段中具有“1”的记录更快。当您从多个表连接数据并且您的数据库引擎不得不重复执行此类操作时,这一点变得尤为重要。 (达人还正确地指出,你所匹配的领域类型也是相同的,这一点非常重要。)
The level at which these inefficiencies become an issue depends greatly on your hardware and your application design. We have big enough iron these days that if you're building moderate-scale apps, you may not see an appreciable difference. (Aside from feeling a little bit guilty about your database design!) But establishing good habits on small projects makes the bigger ones easier when they come along.
这些低效率成为问题的程度在很大程度上取决于您的硬件和应用程序设计。这些天我们有足够大的铁,如果你正在构建中等规模的应用程序,你可能看不到明显的差异。 (除了对你的数据库设计感到有点内疚!)但是在小项目上建立良好的习惯会使更大的项目更容易出现。
#2
0
If you have two columns as varchar and put in the values 10 and 20 and add them, you'll get 1020 instead of 30 which you'd likely expect.
如果您有两列作为varchar并放入值10和20并添加它们,那么您将获得1020而不是30,这是您可能期望的。
#3
0
Sure, you could save everything as VARCHAR strings. But you'd be giving up a lot of functionality provided by the database engine.
当然,您可以将所有内容保存为VARCHAR字符串。但是你会放弃数据库引擎提供的很多功能。
You should choose the database type that most closely matches the intended use of the column. For example, using DATE or DATETIME to store dates provides you with all sorts of date/time functions that you don't get with basic VARCHAR types.
您应该选择与列的预期用途最匹配的数据库类型。例如,使用DATE或DATETIME存储日期可以为您提供基本VARCHAR类型无法获得的各种日期/时间函数。
Likewise, fields used to count things or provide simple unique IDs should be INT or one of its related types. Also bear in mind that an INT occupies only 4 bytes, whereas a 9-digit string uses at least 9 bytes.
同样,用于计算内容或提供简单唯一ID的字段应为INT或其相关类型之一。还要记住,INT只占用4个字节,而9位数字符串至少使用9个字节。
For character data, it's wise to use NVARCHAR for internationalized values that users in any locale are going to enter (esp. names and locations). If you know the text is limited to US or internal use only, VARCHAR is safe.
对于字符数据,最好将NVARCHAR用于任何语言环境中的用户要输入的国际化值(尤其是名称和位置)。如果您知道文本仅限于美国或仅供内部使用,则VARCHAR是安全的。
#1
3
Data types not only optimize storage, but how data is indexed. As your databases get bigger, it will become apparent that it's quicker to search for all the records that have a 1 in an integer field than those that have a "1" in a varchar field. This becomes especially important when you're joining data from more than one table and your database engine is having to do this sort of thing repeatedly. (Daren also rightly points out below that it's important that the types of the fields you're matching on are identical as well.)
数据类型不仅可以优化存储,还可以优化数据的索引方式。随着数据库变得越来越大,很明显,搜索整数字段中的1的所有记录比在varchar字段中具有“1”的记录更快。当您从多个表连接数据并且您的数据库引擎不得不重复执行此类操作时,这一点变得尤为重要。 (达人还正确地指出,你所匹配的领域类型也是相同的,这一点非常重要。)
The level at which these inefficiencies become an issue depends greatly on your hardware and your application design. We have big enough iron these days that if you're building moderate-scale apps, you may not see an appreciable difference. (Aside from feeling a little bit guilty about your database design!) But establishing good habits on small projects makes the bigger ones easier when they come along.
这些低效率成为问题的程度在很大程度上取决于您的硬件和应用程序设计。这些天我们有足够大的铁,如果你正在构建中等规模的应用程序,你可能看不到明显的差异。 (除了对你的数据库设计感到有点内疚!)但是在小项目上建立良好的习惯会使更大的项目更容易出现。
#2
0
If you have two columns as varchar and put in the values 10 and 20 and add them, you'll get 1020 instead of 30 which you'd likely expect.
如果您有两列作为varchar并放入值10和20并添加它们,那么您将获得1020而不是30,这是您可能期望的。
#3
0
Sure, you could save everything as VARCHAR strings. But you'd be giving up a lot of functionality provided by the database engine.
当然,您可以将所有内容保存为VARCHAR字符串。但是你会放弃数据库引擎提供的很多功能。
You should choose the database type that most closely matches the intended use of the column. For example, using DATE or DATETIME to store dates provides you with all sorts of date/time functions that you don't get with basic VARCHAR types.
您应该选择与列的预期用途最匹配的数据库类型。例如,使用DATE或DATETIME存储日期可以为您提供基本VARCHAR类型无法获得的各种日期/时间函数。
Likewise, fields used to count things or provide simple unique IDs should be INT or one of its related types. Also bear in mind that an INT occupies only 4 bytes, whereas a 9-digit string uses at least 9 bytes.
同样,用于计算内容或提供简单唯一ID的字段应为INT或其相关类型之一。还要记住,INT只占用4个字节,而9位数字符串至少使用9个字节。
For character data, it's wise to use NVARCHAR for internationalized values that users in any locale are going to enter (esp. names and locations). If you know the text is limited to US or internal use only, VARCHAR is safe.
对于字符数据,最好将NVARCHAR用于任何语言环境中的用户要输入的国际化值(尤其是名称和位置)。如果您知道文本仅限于美国或仅供内部使用,则VARCHAR是安全的。