如果将INT列更改为MEDIUMINT，我将节省多少大小？ [英] How much size I will save if changed INT column to MEDIUMINT?

问题描述

我正在学习如何通过为列重新选择正确的数据类型来优化数据库，并且我想知道如果选择 MEDIUMINT 可以节省多少大小（3个字节）而不是 INT （4个字节）

I'm learning how to optimize my database by re choosing the correct data types for the columns and I want to know how much size I will save if I choose MEDIUMINT (3 Bytes) instead of INT(4 Bytes)

AFAIK-如果我错了，请纠正我-我需要数据库大小尽可能小以适合RAM，以减少硬桌面请求。数据库的大小由表大小+索引大小组成

AFAIK -and correct me if I'm wrong- I need the database size to be as small as possible to fit in RAM to reduce the hard-desk requests. The size of the database consists of the tables sizes + index sizes

假设我有一个 INT 列， 10'000'000行和一个B-Tree索引，如果将列的数据类型从 INT 更改为<$ c，我将节省多少MB $ c> MEDIUMINT at

giving that I have an INT column that has 10'000'000 rows and a B-Tree index on it, how much size In MBs I will save if I changed the datatype of the column from INT to MEDIUMINT at

• 表数据大小？


• 索引大小？

注意：我知道MySQL不会减小磁盘上的实际大小，除非我OPTIMIZE表格

编辑：我的情况是我将很快完成我人生中的第一个 serious 系统-这是一个ERP我计划在阿拉伯地区市场上销售的系统-。计划1、2、3、4的数据库应该分别约为2GB，4GB，10GB和40GB，所以如果我可以在不牺牲性能/功能的情况下减小每个数据库的大小，那为什么不呢？如果我可以使一台32GB的RAM机器服务于4个客户端而不是2个客户端，为什么不呢？

My situation is that I will finish my first serious system in my life shortly -it's an ERP system that I plan to sell in the Arab region market - . Plans 1, 2, 3, 4 databases are supposed to be about 2GB, 4GB, 10GB, 40GB respectively, so If I could reduce the size of each database without sacrificing performance/features, why not ? If I could make a 32GB RAM machine serve 4 clients instead of 2, why not ?

推荐答案

只需使用 INT ，除非您遇到特定的，可测量问题。在这个时代，即使是最节俭的智能手机也仅拥有十亿个用于存储的内存，如果在每个字节上烦恼，都会使事情变得一团糟。

Just use INT unless you have a specific, measurable problem. You're only going to make a mess of things if you fret over every single byte in an era where even the most thrifty of smart phones has a billion of them for memory alone.

我需要数据库的大小尽可能小以适合RAM以减少硬桌面请求。

I need the database size to be as small as possible to fit in RAM to reduce the hard-desk requests.

不，你不知道。您需要该数据库易于使用并充分执行 。在以SSD为后盾的数据库时代，I / O直到您进行大规模操作时才成为问题，并且当这一天到来时，您可以进行测量并了解您遇到的具体问题。

No you don't. You need the database to be easy to work with and perform adequately. In an era of SSD-backed databases, I/O will not be a problem until you're operating at large scale, and when and if that day comes then you can take measurements and understand the specific problems you're having.

从 INT 字段中删除一个字节不太可能使任何事情变得更好，因为三字节整数值不是您的CPU可以直接处理。这些将被转换为四个字节并正确对齐，以便可以理解，与读取普通的旧32位整数相比，此过程很麻烦。

Shaving a single byte off your INT field is unlikely to make anything better since three byte integer values are not something your CPU can directly deal with. These will be converted to four bytes and aligned properly so they can be understood, a process that's messy compared to reading a plain old 32-bit integer.

记住，MySQL来了从一个高端服务器具有64个兆字节内存和9个千兆字节硬盘的时代开始，这种时代被认为是巨大的。那时您确实必须舍弃字节，因为只有少数几个字节。

Remember, MySQL comes from an era where a high-end server had 64 megabytes of memory and a 9 gigabyte hard disk was considered huge. Back then you did have to shave bytes off because you only had a handful of them.

现在我们还有其他担忧，例如您会不小心耗尽24位整数空间像 Slashdot做了他们的网站因您打算在此处进行优化而关闭。

Now we have other concerns, like will you accidentally exhaust your 24-bit integer space like Slashdot did where their site went down because of exactly the sort of "optimizing" you're intending to do here.

请注意。在有具体原因的时候进行优化，而不仅仅是因为您认为自己需要这样做。避免过早的优化是开发过程中经常遇到的难题，但是如果您受纪律约束，则可以避免。

Be careful. Optimize when you have a concrete reason to, not just because you think you need to. Avoiding premature optimization is a constant struggle in development, but if you're disciplined you can avoid it.

这篇关于如果将INT列更改为MEDIUMINT，我将节省多少大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！