High-performance MySql evolution (I): Data Type optimization

The database performance tuning process involves a lot of knowledge, including whether the attribute settings of fields are appropriate, whether the index is created properly, and whether the table structure is reasonable, are the database operating system settings correct ..... Each topic may be a field. In my opinion, it is relatively difficult to optimize fields in key technologies for improving database performance.

The database performance tuning process involves a lot of knowledge, including whether the attribute settings of fields are appropriate, whether the index is created properly, and whether the table structure is reasonable, are database/operating system settings correct ..... Each topic may be a field. In my opinion, it is relatively difficult to optimize fields in key technologies for improving database performance.

The database performance tuning process involves a lot of knowledge, including whether the attribute settings of fields are appropriate, whether the index is created properly, and whether the table structure is reasonable, are database/operating system settings correct ..... Each topic may be a field.

In my opinion, in key technologies for improving database performance, it is relatively difficult to optimize fields and has a great impact on performance. Mysql supports many data types and each type has its unique features. However, when you select a specific data type, you can select a usable data type at will, it does not consider whether this type is optimal. Before describing specific types, let's look at some main principles for data type selection:

A) try to select a type that occupies a small amount of space.
Because small types occupy a small amount of space in both disk and memory, the space required for querying or sorting temporary tables is also relatively small. It may not be felt when the data size is small, but when the data size is large, the importance of this principle may be apparent.

For example, there is a "item information" table with 20 million records. This table has a "remaining item quantity" (COUNT) field. Generally, SMALLINT (len: 16 range: 0-65535) is enough to express this field, but if you use BIGINT (len: 64 range: 0-18446744073709551615) in the design process, although the program may run correctly, this field will increase the disk storage space (64-16)/8*20,000,000 Bytes) by about 95mb ), in addition, when selecting and sorting data, this field will increase your memory usage by MB. Based on the influence of the above behaviors, the Performance of the database will inevitably be affected.

The premise to be as small as possible here is to ensure that the type you want to select can meet the needs of future business development, it is very slow and troublesome to update the table structure when the data volume is large.

B) Try to select a simple/appropriate type

When selecting and sorting tables, for simple types, only a small CPU clock cycle is required. For example, for MySql server, Compare of the integer type is easier and faster than Compare of the string type, so when you need to sort specific tables, try to select the integer type as the basis for sorting.

C) set the field to NOTNULL whenever possible.
Generally, if you do not specify a NULL field, the field will be considered as NULLABLE by the database system. The default behavior of the system will cause the following three problems:
(1) The query optimization function of the Mysql server will be affected.
(2) Mysql requires additional storage space and processing for null Fields
(3) If a null value is part of the index, the index effect will also be affected.

This principle does not greatly improve database performance. Therefore, for existing DB schema, The NULLABLE field or index is NULLABLE, and you do not need to modify it specially, however, we recommend that you adhere to this principle when designing new databases or indexes.

After introducing the principles of data type selection, we will introduce the Common Data Types in Mysql and the notes for performance optimization.

· Integer
Mysql's integer family includes TINYINT (8bit), SMALLINT (16bit), MEDIUMINT (24bit), INT (32bit), or BIGINT (64bit ).

For signed integers, the storage range of these types is (-2 (n-1), 2 (n-1)-1), and the value range of the unsigned number is (0, 2n-1). For databases, the number of symbols and the number of unsigned symbols occupy the same storage space. Therefore, when selecting a type, you can only consider the number range, instead of signed or unsigned.

Mysql allows you to specify the integer width when defining the integer type, for example, INT (10 ). INT (10) is different for the output of Client/CMD Line, but in Mysql Server's opinion, the actual storage space/computing consumption/number range: INT (10) and INT (32) there is no difference.

· Decimal
In Mysql, the Data Types of Small and Medium-sized data families mainly include FLOAT (4 Bytes), DOUBLE (8 Bytes ), from the two types of buckets, we can see that the access of decimal places consumes more space than the integer, so unless necessary, we should try to avoid using the decimal type.

When creating a decimal field, you can use FLOAT (5.0) to specify the decimal precision.> = Mysql supports the maximum precision of 65 decimal places.

Because the database uses the Binary Array String method to store digits after the decimal point, the higher the precision you require, the higher the CPU clock consumption of the storage space/computing.

Although decimals may consume more storage space and CPU resources, early Mysql versions may also lose precision when two decimals are involved in computing, however, it is necessary in many cases, such as the storage of amounts in the financial sector. In many cases, in order to reduce storage overhead and ensure accuracy, decimal places are often extended to Integers stored in the database, and decimal places are converted and computed in the Application, for example, if a user's account balance is still 999.35 yuan, the amount stored in the data is 99935 points. After the bank's processing program receives 99935 points, it will first convert to 999.35 yuan, and then proceed with the corresponding processing

· String

No matter which language, strings are a very important and complex type. This rule is also applicable to MYSQL.
MYSQL mainly includes two string types: VARCHAR and CHAR. The Storage method of these two string types in disk and memory is determined by the Storage engine, different storage engines may have different storage methods. Generally, for a storage engine, the storage methods in disks and memory are also different. when data is transferred between disks and memory, the storage engine is responsible for data conversion.
VARCHAR
The first thing to note is that Mysql uses the variable length Method to store VARCHAR. Compared with the fixed length method, this method adopts the "How much is used, how much is required" policy for the bucket ", is a space-saving storage solution, which can be used as the default type without special requirements.

VARCHAR can be fixed because each VARCHAR value is appended with a length indicator with a length of 1-2 bytes. For example, when you need to store "I Love Java, the underlying storage content is "11I Love Java", and 11 (1 Byte) indicates the length. When the length of the content to be stored is 1000, the length indicator requires two bytes. Because the maximum value of 2 bytes is 216, when the length of the stored string exceeds this length, an unexpected exception occurs. In this case, you need to use CLOB to store this ultra-long string.

In different versions of MYSQL, the trailing space processing for the varchar field is also different.
Version> = 5.0 reserved trailing space
Version <= 4.1 intercept Space

Take MYSQL 5.6 as an example:

?The storage overhead of 'hello' using VARCHAR (5) and VARCHAR (200) is the same. So what are the advantages of using shorter columns?

Facts have proved to have great advantages. Larger columns consume more memory, because MySQL usually allocates a fixed size of memory blocks to save internal values. Especially when using memory temporary tables for sorting or operations, it will be particularly bad. The sorting of temporary disk tables is also bad.

Therefore, the best strategy is to allocate only the space actually needed.

CHAR
The biggest difference between the CHAR type and the VARCHAR type is that it is fixed length. Compared with VARCHAR, VARCHAR has the following features:
1) In all MYSQL versions, spaces at the end will be intercepted.

2) It is a good choice for some short fields with the same length, such as MD5 and ID Number.
3) the CHAR type is more efficient for fields that often need to be changed.
4) some ultra-short fields also save space. For example, if you save "Y" or "N", only one byte is required for CHAR, and two bytes (1 byte length + 1 byte value) for VARCHAR)

For a CHAR with a fixed length, Mysql server uses spaces to allocate enough storage space according to its defined length. One thing to note is that VARCHAR/CHAR is implemented by Mysql server to perform "fill space" and "Remove trailing space" operations, and has nothing to do with Storage engine.

DATE/TIMESTAMP, BLOB/CLOB/TEXT, ENUM, BIT types will be explained in the next blog

High-performance MySql evolution (II): optimization of Data Types