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

High-performance MySql evolution (1): 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 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 the specific types, let's take a look at some of the main principles for Data Type Selection: a) try to select a type that occupies a small amount of space, because a small type is either on a disk, or the space occupied in the memory is small, and the space required for querying or sorting is 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 alone will increase your memory consumption by MB. Based on the influence of the above behavior, the Performance of the database must be affected. The premise here is to make sure 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 a table, for a simple type, you usually only need to consume less CPU clock cycles. 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, you should try to select the integer type as the sort basis c) try to set the field to NOTNULL, if you do not specify a field as NULL, 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 for null fields and processing (3) if a null value is part of the index, the index effect will also be affected because this principle does not greatly improve database performance. Therefore, for existing DB schema, The NULLABLE field exists or the index is NULLABLE, you do not need to modify it. However, you need to follow this principle whenever possible for newly designed 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. · Integers in the Mysql family mainly include 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 symbols occupy the same storage space. Therefore, when selecting a type, you can only consider the number range, regardless of whether it is signed or unsigned Mysql, you can specify its width when defining the integer type, such as 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. · The Data Types of decimals in the Mysql family mainly include FLOAT (4 Bytes), DOUBLE (8 Bytes ), from the two types of buckets, it can be seen that the access of decimal places consumes more space than the integer, so unless necessary, otherwise, try to avoid using the decimal type to create a decimal field. You can use FLOAT () to specify the decimal precision.> = Mysql 5.0 supports the maximum precision of 65 digits after the decimal point. 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, then perform corresponding processing. The string is a very important and complex type no matter which language it is used, this rule is also applicable to MYSQL and mainly includes VARCHAR and CHAR string types. The Storage engine determines the Storage methods of these two string types on disk and memory, 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, storage engine will be responsible for converting data to VARCHAR. First of all, it should be pointed out that Mysql uses the variable length Method to store VARCHAR, relative to fixed length, this method adopts the "How much is used, how much is required" Strategy for storage space. It is a storage solution that saves space, in the absence of special requirements, the default type of VARCHAR can be set to a fixed length, because each VARCHAR value will be appended with a length indicator with a length of 1-2 bytes, for example, 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 MYSQL versions, the trailing space processing for the varchar field is also different Version> = 5.0 reserved trailing space Version <= 4.1 truncated 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. The biggest difference between the CHARCHAR type and the VARCHAR type is that it is fixed length. At the same time, compared with VARCHAR, it has the following features: 1) In all MYSQL versions, spaces at the end will be truncated. 2) it is a good choice for some short fields with the same length, such as MD5 and ID Number3) for fields that often need to be changed, the CHAR type will be more efficient. 4) for some very short fields, it also saves a lot of space. For example, if you save "Y" or "N", you only need one byte for CHAR. If you use VARCHAR, you need two bytes (1 byte length + 1 byte value) for a CHAR with a fixed length, mysql server uses spaces to allocate enough storage space according to the 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.