High performance Mysql--mysql data type introduction and optimal data type selection

MySQL supports a large number of data types, so choosing the right data type is critical to getting high performance. Then understand the pros and cons of various types first!

First, type introduction

1. Integral type

　　The integral type is: tinyint,smallint,mediumint,int,bigint . They account for 8,16,24,32,64 storage space respectively. The integer range that can be stored is -2^ (N-1) to 2^ (N-1)-1, where N is the number of bits of storage space.

You can also set the integer type to UNSIGNED , which can almost be one-fold larger. For example, the tinyint range is-128-127, while the tinyint unsigned range is 0-255. However, these two are only in the scope of the lack of, in the storage space and performance are the same.

2. Real type

　　For real types, MySQL supports an exact type (DECIMAL) and also supports imprecise types (float,double).

The decimal type allows you to store up to 65 digits, so it can store a number larger than bigint. And in MySQL5.0 or later, the MySQL server itself implements a high-precision calculation of decimal. However, compared to floating-point types, floating-point type calculations are faster because the CPU directly supports acoustic floating-point calculations.

In general, floating-point types use less space than decimal when they store the same range. Float occupies 4 bytes of storage, double consumes 8 bytes of storage, but has higher precision and greater range than float. When floating-point types are stored, there are various problems with precision, for example, when you set only one column to float and no precision is specified, the storage 1234567.33 becomes 1234570.

The bytes occupied by a decimal are more specific. It is used to store 9 digits of each 4 bytes before and after the decimal point. See the MySQL Manual for details:

So we use the largest number of decimal (10,2) bytes for 1+4+1+1=7 bytes (the decimal point is one byte).

Because additional computational overhead and storage space is required, you should try to use decimal--, such as storing financial data, as much as possible in order to accurately calculate decimals. When you have a large amount of data, in order to avoid the problem of inaccurate floating-point storage calculations and the high cost of decimal calculation, you can use bigint instead of decimal, just multiply the number of decimals that you need to store by the corresponding multiples (bigint range meets your needs).

3. String type

　　The varchar type is used to store a variable-length string, so it requires 1 or 2 extra bytes to record the length of the string: If the column is less than or equal to 255 bytes in length, then only 1 bytes are used, otherwise 2 bytes are represented. For example, varchar (10) requires 11 bytes, and varchar (1000) requires 1002 bytes.

VarChar saves storage space, so it is useful for performance. However, because the rows are longer, it may be that the original line is longer at update, which leads to some additional work. If a row occupies a long space and there is no more space in the page to store, this is InnoDB splitting the current page so that the row can be placed in the page.

The following conditions are appropriate for using varchar:

• The maximum length of a string column is much larger than the average length
• Few updates to columns
• Using a character set such as UTF-8, each character is stored in a different byte

The char type is fixed-length: MySQL always allocates enough space based on the length of the defined string. Because Char fills the end of the string as needed, and char removes the trailing space when you retrieve it. So there's a very interesting thing happening, when you store a "Johnson" to char (10), the result is "Johnson", because MySQL doesn't know whether the space you saved or the system autofill.

Char is ideal for storing very short strings or all values approaching the same length. For example, the MD5 value of the password.

Both blob and text are string data types designed to store large data types and are stored in binary and character mode, respectively. And when the data they store is too large, INNOSB uses a dedicated ' outside ' space to store the data, where each value's Row stores only a 1 to 4 byte pointer, and then stores the true reference in the outer area. When you need to sort blobs and text, it sorts only the top max_sort_length of each column. This value can be configured.

4. Enumeration type

　　Enumeration types can sometimes be used instead of commonly used string types. MySQL internally saves each value in the list as an integer and saves a "number-string" Mapping in the table's. frm file. For example, the sex column, you can use enum (male, female, unknown), here are some people may use tinyint instead of enumeration, actually I feel that this does not bring performance optimization, but you put the "number-string" Mapping relationship to your business logic to deal with, if your comments are not written clearly, But it will bring confusion to the newcomers.

Enumerations are not friendly to weakly typed languages. For a chestnut:select Id,name from users where id = 1; and select Id,name from users where ID = ' 1 '; The result is the same. Because enum internal storage is an integral type, you can also use integers when retrieving enum types, such as select Id,name from users where sex = 1; and Select I D,name from users where sex = ' male '; Can get the same result. But

Select Id,name from users where sex = ' 1 ';

No value is retrieved. But if you design and use the good, still can use.

5. Date and Time type

　　Date and time types are common: date, hour, year, DateTime, and timestamp. Where date, time, and year account for 4,3,1 bytes, and the stored format is yyyy-mm-dd,hh:mm:ss,yyyy. These three types of date and time are relatively small in comparison. Here we mainly introduce the difference between datetime and timestamp.

The range of datetime storage is large, from 1001-9,999, with a precision of seconds and a storage format of YYYY-MM-DD HH:MM:SS, which accounts for 8 bytes of storage space.

The timestamp has a much smaller range, from 1970 to 2038 (almost out of range), with a precision of seconds and a storage format of Yyyy-mm-dd HH:MM:SS, but only 4 bytes of storage space. Timestamp defaults to not NULL, and the current time of MySQL is inserted by default when the column value is not specified in the insert. In addition to special behavior, you should use timestamp as much as possible, because it accounts for less storage space than DateTime.

The special note here is that sometimes someone stores a Unix timestamp as an integer (that's what our company does), but it doesn't bring any benefit. Instead, the format of saving timestamps with integers is usually inconvenient, so this is not recommended.

Second, select the optimized data type

• Smaller, longer, better.

In general, you should try to use the smallest data type that correctly stores your data. For example, only the value of 0~200 is needed, obviously tinyint unsigned is sufficient. Smaller data types are faster because they consume less disk, memory, and CPU, and require less CPU cycles to process.

• It's simple.

Simple data type operations typically require less CPU cycles. For example, you should use an integer storage IP instead of a string.

• Try to avoid null

Nullable columns use more storage space, and special handling is required in MySQL. When a nullable column is indexed, an extra byte is required for each index record.

High performance Mysql--mysql data type introduction and optimal data type selection