应该避免在MySQL中使用mediumint吗? [英] Should mediumint be avoided in mysql?

问题描述

我在以下博客文章中发现了一条建议不要使用mediumint的评论:

即使在MySQL中也不要使用[24位INT].它很笨，很慢，实现它的代码令人毛骨悚然.

4294967295和MySQL INT(20)语法错误

关于stackoverflow的答案还指出，SQL Server，Postgres和DB2不支持mediumint.

这是什么MySQL中tinyint，smallint，mediumint，bigint和int之间的区别?

应该避免使用mediaint还是应该在它最能代表我存储的数据的情况下继续使用它?

解决方案

InnoDB将MEDIUMINT存储为三个字节的值. 但是，当MySQL必须执行任何计算时，会将3个字节的MEDIUMINT转换为8个字节的无符号长整数(我假设现在没有人在32位上运行MySQL).

有优点也有缺点，但您了解这很愚蠢，而且速度很慢，实现它的代码令人毛骨悚然"，这种推理不是技术性的吧?

我想说当磁盘上的数据大小很关键时，MEDIUMINT才有意义. IE.当一个表有很多记录时，即使一个字节的差异(4字节INT与3字节MEDIUMINT)也意味着很多.这是一种罕见的情况，但有可能.

mach_read_from_3和mach_read_from_4-InnoDB用于从InnoDB记录中读取数字的原语是相似的.他们俩都返回子.我敢打赌，您不会注意到任何工作量上的差异.

只需看一下代码:

ulint
mach_read_from_3(
/*=============*/
        const byte*     b)      /*!< in: pointer to 3 bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 16)
                | ((ulint)(b[1]) << 8)
                | (ulint)(b[2])
                );
}

您认为它比这慢得多吗?

ulint
mach_read_from_4(
/*=============*/
        const byte*     b)      /*!< in: pointer to four bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 24)
                | ((ulint)(b[1]) << 16)
                | ((ulint)(b[2]) << 8)
                | (ulint)(b[3])
                );
}

I came across a comment on the following blogpost that recommends against using mediumint:

Don’t use [the 24bit INT], even in MySQL. It’s dumb, and it’s slow, and the code that implements it is a crawling horror.

4294967295 and MySQL INT(20) Syntax Blows

An answer on stackoverflow also notes that SQL Server, Postgres, and DB2 don't support mediumint.

What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?

Should mediumint be avoided or should I continue to use it in the cases where it best represents the data I am storing?

解决方案

InnoDB stores MEDIUMINT as three bytes value. But when MySQL has to do any computation the three bytes MEDIUMINT is converted into eight bytes unsigned long int(I assume nobody runs MySQL on 32 bits nowadays).

There are pros and cons, but you understand that "It’s dumb, and it’s slow, and the code that implements it is a crawling horror" reasoning is not technical, right?

I would say MEDIUMINT makes sense when data size on disk is critical. I.e. when a table has so many records that even one byte difference (4 bytes INT vs 3 bytes MEDIUMINT) means a lot. It's rather a rare case, but possible.

mach_read_from_3 and mach_read_from_4 - primitives that InnoDB uses to read numbers from InnoDB records are similar. They both return ulint. I bet you won't notice a difference on any workload.

Just take a look at the code:

ulint
mach_read_from_3(
/*=============*/
        const byte*     b)      /*!< in: pointer to 3 bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 16)
                | ((ulint)(b[1]) << 8)
                | (ulint)(b[2])
                );
}

Do you think it's much slower than this?

ulint
mach_read_from_4(
/*=============*/
        const byte*     b)      /*!< in: pointer to four bytes */
{
        ut_ad(b);
        return( ((ulint)(b[0]) << 24)
                | ((ulint)(b[1]) << 16)
                | ((ulint)(b[2]) << 8)
                | (ulint)(b[3])
                );
}

这篇关于应该避免在MySQL中使用mediumint吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！