MySQL Query optimization: data type and efficiency

This section provides some guidance on how to choose a data type to help improve the speed of queries:

Do not use long when you can use short data columns.If you have a fixed-length char data column, don't let it be longer than you actually need. If the longest value that you store in the data column is 40 characters, do not define char (255), and it should be defined as char (40). If you can use Mediumint instead of bigint, your data tables will be smaller (less disk I/O), and the value will be processed faster during the calculation process. If the data columns are indexed, the performance improvements that are caused by using shorter values are more significant. Not only can indexes improve query speed, but short index values are also faster than long index values.

If you can choose the storage format for the data rows, you should use the one that best fits the storage engine. For MyISAM data tables, it is best to use fixed-length data columns instead of variable-length data columns. For example, let all character columns replace the varchar type with a char type. The tradeoff is that the data table uses more disk space, but if you can provide extra space, the fixed-length data rows are processed faster than the variable-length data rows. This is especially true for tables that are frequently modified because performance is more susceptible to disk fragmentation in those cases.

　　 when using variable-length data rows, the data table is fragmented more often after the delete and update operations have been performed multiple times due to different record lengths. You must use optimize table to maintain its performance on a regular basis. Fixed-length data rows without this problem。

If a data table crashes, a table with fixed data row lengths is easier to reconstruct. With fixed-length data rows, the starting position of each record can be detected because these locations are multiples of the fixed record length, but not necessarily when using variable-length data rows. This is not an issue related to the performance of query processing, but it can certainly speed up the repair of data tables.

Although converting a MYISAM data table to a fixed-length data column can improve performance, you first need to consider some of the following:

fixed-length data columns are faster, but occupy more space。 Each value of a CHAR (n) column, even a null value, usually occupies n characters, because when you store it in a data table, a space is added after the value. The VARCHAR (n) column occupies less space because only the required number of characters is allocated for storing the value, plus one or two bytes to store the length of the value. Sowhen choosing between char and varchar columns, it is actually the contrast between time and space. If speed is the primary consideration, use a char data column to get the performance benefits of a fixed-length column. If space is important, then the varchar data column is used. All in all, you can assume that a fixed-length row of data can improve performance, although it takes up more space. But for some special applications, you might want to implement a data table in two ways, and then run the tests to determine which one fits your application's needs.

Even if you are willing to use fixed-length types, sometimes you can't use them. For example, a string longer than 255 characters cannot use a fixed-length type.

　 The Memory data table is currently stored using fixed-length data rows,Therefore, there is no relationship between using char or varchar columns. Both are handled as char types.

For INNODB data tables, the internal row storage format does not differentiate between fixed-length and variable-length columns (all data rows use the head pointer pointing to data column values), so in essence, using fixed-length char columns is not necessarily simpler than using variable-length varchar columns. Thus, the main performance factor is the total amount of storage used by the data rows. Since Char takes up more space on average than varchar, it is better to use varchar to minimize the amount of storage and disk I/O for the rows of data that need to be processed.

For BDB data tables, there is little difference whether you use fixed-length or variable-length data columns. There are two ways you can try it out and run some experimental tests to see if there are any obvious differences.

　　 defines a data column as not nullable (not NULL)。 This makes processing faster and requires less storage. It also sometimes simplifies the query, because in some cases you do not need to check the Null property of the value.

Consider using enum data columns. If you have a data column that has a low cardinality (with a limited number of different values), you might consider converting it to an enum column. Enum values can be processed faster because they are internally represented as numeric values.

Use procedure analyse (). Run procedure analyse () to see the columns in the datasheet:

PROCEDURE analyse ();  PROCEDURE Analyse (N.);   

Each column of the output proposes optimization recommendations for the data type of the columns in the data table. The second example tells procedure analyse () not to make recommendations for enum types that contain more than 16 or 256 bytes of value. Without such a restriction, the output information can be long, and the enum definition is often difficult to read.

Reprint: http://dev.yesky.com/103/2114603.shtml?412

MySQL Query optimization: data type and efficiency