MySQL data type optimization principles

MySQL supports many data types, and choosing the right data type is crucial for high performance. Here are a few simple principles to help you make better choices.

• Smaller is usually better
  

You should try to use the smallest data type that can correctly store the data. Smaller data types are generally faster because they take up less disk, memory, and CPU cache, and require fewer CPU cycles to process. If you are unsure which data type is best, choose the smallest type that you think will not exceed the range.

• Keep it simple

Operations on simple data types generally require fewer CPU cycles. For example, integer operations are cheaper than character operations because character sets and collations make character comparisons more complicated than integer comparisons. For example, use MySQL's built-in types instead of strings to store dates and times, and use integers to store IP addresses.

• Try to avoid null

Queries involving nullable columns are more difficult for MySQL to optimize because nullable columns make indexes, index statistics, and value comparisons more complicated. Nullable columns use more storage space and require special handling in MySQL. When a nullable column is indexed, each index record requires an extra byte, which in MyISAM can even cause a fixed-size index to become a variable-size index.

Generally, the performance improvement of changing a nullable column to not null is small, so there is no need to find and fix this situation in an existing schema unless you are sure it will cause problems.

The exception is that InnoDB uses a separate bit to store null values, so it has good space efficiency for sparse data (many values ​​are null and only a few rows have non-null values ​​in the column), but this does not apply to MyISAM.

When choosing a data type for a column.

The first step is to determine the appropriate large type: number, string, time, etc. The second step is to choose the specific type. Many MySQL data types can store the same type of data, but the storage length and range are different, the allowed precision is different, or the required physical space is different.

Integer Types

These types can be used to store integers

They can store values ​​ranging from -2^(N-1)^ to 2^(N-1)^-1, where N is the number of bits of storage space.
Integer types also have the optional signed property, which means that negative values ​​are not allowed, which can double the upper limit of positive numbers. For example, the range that can be stored in unsigned tinyint is 0 to 255, but it allows negative values ​​but the storage range is -128 to 127.

MySQL chooses different integer types to determine how to store data in memory and on disk. However, in integer calculations, 64-bit bigint integers are generally used, even in 32-bit environments. (except aggregate functions)

MySQL also allows you to specify widths for integer types. Such as int(11), but it does not limit the legal range of values, but only specifies the number of characters used by some MySQL interactive tools (such as SQLyog, navicat) to display. For storage and computation purposes, int(1) and int(11) are identical.

Real number types

Real numbers are numbers with a fractional part. In MySQL, decimal, float, and double can be used to store real number types.

The float and double types support approximate calculations using standard floating-point arithmetic.
The decimal type is used to store precise decimal numbers. In MySQL 5.0 and higher versions, the MySQL server itself implements high-precision decimal calculations.

Floating-point types generally use less space than decimal types to store the same range of values. Float uses 4 bytes for storage and double uses 8 bytes for storage, which has higher precision and larger range than float. In floating-point calculations, MySQL uses double as the internal floating-point calculation type.

When considering accurate calculations on decimals, use decimal (such as financial data). However, when the amount of data is large, you can consider using bigint instead of decimal, and store the currency unit by multiplying the number of decimal places by the same multiple. This can avoid the high cost of decimal calculation.

Float and double use native floating-point calculations supported by the CPU, so they are fast.
Decimal is a high-precision calculation implemented by the MySQL server itself.

String Type

VARCHAR and CHAR are the two main string types.

varchar

The varchar type is used to store variable-length strings and is the most common string data type. It is more space-efficient than fixed-length types because it uses only the necessary space.

VARCHAR requires one or two extra bytes to record the string length: if the maximum length of the column is less than or equal to 255 bytes, only one byte is used, otherwise two bytes are used.

VARCHAR saves storage space, so it also helps performance. However, because rows are variable length, an update may make the row longer than before, which requires extra work. Because the space occupied by the row grows, and there is no more room to store it on the page. In this case, MyISAM will split the row into different fragments for storage, and InnoDB needs to split the page so that the row can fit into the page. Some other storage engines may not update data in-place.

The most appropriate scenarios for using varchar are: when the maximum length of the string column is much larger than the average length; when the column is rarely updated (fragmentation is not a problem); and when using a complex character set such as utf-8, each character is stored using a different number of bytes.

The space cost of storing 'hello' using varchar(5) and varchar(200) is the same, but using a longer column consumes more memory. MySQL usually allocates a fixed size memory block to store internal values. This is especially bad when using in-memory temporary tables for sorting or manipulation, and it is equally bad when using disk temporary tables for sorting. So the best strategy is to allocate only the space you really need.

char

The char type is fixed-length. MySQL always allocates enough space for the defined string length. When storing char values, MySQL removes all trailing spaces. Char values ​​are padded with spaces as necessary to facilitate comparison.

char is suitable for storing very short strings, or when all values ​​are close to the same length. For example, the MD5 value of a password. For data that changes frequently, char is also better than varchar because fixed-length char is less likely to be fragmented. For very short columns, char is also more efficient in storage space than varchar, because varchar requires an extra byte for the record length.

The above is the details of MySQL data type optimization principles. For more information on MySQL data type optimization, please pay attention to other related articles on 123WORDPRESS.COM!