Select an appropriate column for MySQL

Mind Map
Click an image to view the large image.

Introduction

Situation: If your table structure is poorly designed or your index design is poor, please optimize your table structure design and assign appropriate indexes so that your query performance can be improved by several orders of magnitude. -- The larger the data, the more value the index can be reflected. To improve performance, we need to consider the following factors: 1. design architecture 2. design indexes 3. Evaluate query performance

Today we will talk about the design of table columns. We will not talk about index design for the moment. I will talk about index design in the next chapter. Selecting the correct data type is crucial to improving performance. Below are several PrinciplesTo help you select the type. 1. Smaller is usually better. Use the smallest data type. -- Less disk space, memory and CPU cache. In addition, less CPU cycles are required. 2. Easy. The integer cost is less than the character. -- The character set and sorting rules make the character more complex. 1> mysql built-in types (such as timestamp and date) are better than string storage. 2> use an integer to save the IP address. 3. Avoid NULL as much as possible-if you plan to index a column, avoid setting the column to NULL as much as possible and define the field as not null. -- You can place a default value, such as '', 0, or special string. Cause: (1) it is difficult for MYSQL to optimize the NULL column. NULL columns make the index and index statistics and values more complex. (2) The NULL column requires more storage space and special processing within MYSQL. (3) add an index to the NULL column. Each record requires an additional byte, which also changes the fixed-size index in MyISAM to a variable-size index.

To determine the Data Type of a column, follow these two steps.

Step 1: roughly determine the data type. -- Determines whether it is a number, string, or time. This is usually intuitive.

Step 2: determine the specific type.

Many data types can store the same type of data, but we need to find that they areStorageRange,Precision and PhysicsSpace difference (disk or memory space ). For example, datetime and timestamp can save data of the same type: Date and time. -- Timestamp uses half the space of datetime. It can save the time zone and has special automatic update capabilities.

Taste data type Integer 1, storage type -- data range:-2 ^ (n-1) to 2 ^ (n-1)-1, where n is the number of digits of the required storage space. Data range of the number of digits occupied by the type name: TINYINT 8-2 ^ 7 ~ 2 ^ 7-1 SMALLINT 16-2 ^ 15 ~ 2 ^ 15-1 MEDIUMINT 24-2 ^ 23 ~ 2 ^ 23-1 INT 32-2 ^ 31 ~ 2 ^ 23-1 BIGINT 64-2 ^ 63 ~ 2 ^ 63-1

2. the unsigned attribute indicates that a negative number is not allowed, and the positive limit is roughly doubled. For example, tinyint unsigned stores 0 to 255 instead of-127 to 128.

3. MYSQL defines the width of the integer type. For example, int (1) and int (22) are the same for storage and calculation. Only the MYSQL interaction tool (such as the command line client) is specified to display the number of characters.

The real number has the fractional part (decimal part ). Storage Type: FLOAT, DOUBLE, DECIMAL. Size: FLOAT 4 bytes, DOUBLE 8 bytes. DECIMAL is affected by the MYSQL version. in earlier versions, DECIMAL has 254 digits and more than 5.0 has 65 digits. Differences: 1. FLOAT and DOUBLE support standard floating point operations for approximate calculation. 2. DECIMAL: the CPU does not support direct computation of DECIMAL. Floating point operations are faster, because the computation is directly performed on the CPU. 3. DECIMAL is only a storage format and will be converted to the DOUBLE Type During computation. 4. DECIMAL () uses 9 bytes, four bytes before the DECIMAL point, one DECIMAL point, and four bytes after the DECIMAL point. 5. DECIMAL is used only when the DECIMAL is accurately calculated, such as saving financial data.

String type 1. varchar (1) Stores variable-length strings. Understanding: it occupies less storage space than a fixed length because it only occupies the space you need. Exception: The MyISAM Table created using ROW_FORMAT = FIXED uses a FIXED length space for each row, which may cause waste. (2) Storage length information. If the defined column is smaller than or equal to 255, the length value is stored in 1 byte. If the latin1 character set is used, such as varchar (10), it occupies 11 bytes of storage space. Varchar (1000) occupies 1002 bytes of storage space. (3) space saving is helpful for performance. (4) MySQL 5.0 and later versions retain spaces at the end of the string, whether it is a value or a saved value.

Only allocate space that is actually needed
Use varchar (5) and varchar (200) to save the space occupied by 'Hello. -- This should refer to the space on the disk.
So what are the advantages of using shorter columns? -- Great advantages
Larger columns use more memory, because MySQL usually allocates a fixed size of memory blocks (such as varchar (200) with a memory size of 200 characters) to save the value (then trim the value, and finally put it into the disk) or the value. -- This is especially bad for sorting or using memory-based temporary tables.

2. char (1) fixed length. (2) Remove the trailing space when saving the value.

Let's take a look at varchar.

(3) char is often useful when it is a short string or a string with the same length. For example, to store the MD5 hash value of a user's password, the length is always the same. What is char better than varchar? 1> for frequently changed values, char is better than varchar, because fixed-length rows are not prone to fragmentation. -- Varchar is usually suitable when the maximum length is much larger than the average length and is rarely updated. 2> for very short columns, char is more efficient than varchar. For example, for a single-byte character set (such as latin1), char (1) only occupies 1 byte, while varchar (2) it occupies 2 bytes (one byte is used to store length information ).

3. text is used to save a large amount of data. (1) When InnoDB is large, it uses an "external" storage area for storage. -- Therefore, sufficient external storage space is required to save the actual values. (2) The sorting method is different from other character types and does not sort by full length, but only sorts by the first several bytes specified by max_sort_length.

4. Use ENUM to replace string type (1) The ENUM column can store 65 535 different strings. (2) saved in a compact manner. Compress them to 1 to 2 bytes based on the number of values in the list. (3) MySQL saves each value as an integer internally to indicate the position in the list. (4) keep a copy of "Search table" to indicate the ing relationship between integers and strings in the. frm file of the table. (5) The ENUM character column is fixed. To add or delete a string, use alter table. (6) Use Case: Use ENUM in the permission table to save the values of Y and N. Usage:

When order by is used for the enum column, it is sorted by numbers rather than strings.

Date and Time Type

DATETIME: save a large range of values. Encapsulation Format: YYYYMMDDHHMMSS. -- It is independent of the time zone and uses an 8-byte storage space.

TIMESTAMP: the number of seconds since midnight, January 1, January 1, 1970 (Greenwich Mean Time. -- Uses a 4-byte storage space.

TIMESTAMP is usually used, which saves more space than DATETIME. Sometimes people save Unix timestamps as integer values, but this is usually not of any benefit. -- This format is not easy to process. We do not recommend it.

Experience 1. When selecting data types for columns, we should consider not only the storage type size, but also how MySQL calculates and compares them. For example, MySQL saves the ENUM and SET types as integers internally, but converts them into strings during comparison. 2. We need to use the same type in the relevant table, and exact match should be made between types, including attributes such as UNSIGNED. 3. Mixing different data types will cause performance problems. Even if there is no performance problem, implicit type conversion can also cause imperceptible errors. 4. To select the smallest data type, consider the future growth space. For example, we know that there won't be thousands of provinces in China, so we don't need to use INT. TINYINT is enough. It is 3 bytes smaller than INT. 5. integers are usually the best data type because they are fast and can use AUTO_INCREMENT. 6. Avoid using strings as the data type of columns as much as possible, because they occupy a lot of space and are generally slow to use integers. MyISAM uses a compressed index for strings by default, which makes searching more slow.

SummaryIf any error occurs, please kindly advise me! References: High-Performance MYSQL