MySQL Query optimization series lectures on data types and efficiency

This section provides some guidance on how to select a data type to help improve the speed of your query:

Do not use long when you can use short columns of data. If you have a fixed-length char data column, do not allow it to be longer than you really need. If you store the longest value in a data column with 40 characters, do not define char (255), but you should define it as char (40). If you can replace bigint with Mediumint, your datasheet will be smaller (less disk I/O), and the value will be processed faster in the process of calculation. If the data columns are indexed, the performance improvement with shorter values is more pronounced. Not only can indexes improve query speed, but short index values are also quicker to handle 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 a good idea to use a fixed-length data column instead of a variable-length data column. For example, let all character columns replace the varchar type with the char type. Weighing the pros and cons, we'll find that the datasheet uses more disk space, but if you can provide additional 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 in those cases performance is more susceptible to disk fragmentation.

· When using variable-length rows of data, there are more fragments of the datasheet after the delete and update operations have been performed multiple times, due to the different length of the record. You must use optimize table to maintain its performance on a regular basis. Fixed-length data rows do not have this problem.

· If a data table crashes, a table with a fixed data row length is easier to reconstruct. When using fixed-length data rows, the start 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 a problem related to the performance of query processing, but it can certainly speed up the repair of the datasheet.

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

· Fixed-length data columns are faster, but occupy a larger space. Each value of a CHAR (n) column, even if it is a null value, usually takes up n characters, because when it is stored in a datasheet, a space is added after the value. The VARCHAR (n) column occupies a smaller space because it only needs to allocate the necessary number of characters to store the value, plus one or two bytes to store the length of the value. Therefore, the choice between char and varchar columns is actually a comparison of time and space. If speed is the primary consideration, use the Char data column to get the performance advantage of fixed-length columns. If space is important, use the VARCHAR data column. In short, you can assume that fixed-length data rows 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 tests to determine which situation meets the needs of your application.

· Even if you are willing to use a fixed length type, sometimes you have no way to use it. For example, a string longer than 255 characters cannot use a fixed-length type.

Memory data tables are currently stored with fixed-length data rows, so it does not matter whether you use char or varchar columns. Both are handled as char types.

For INNODB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use head pointers 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 row. Since Char occupies an average of more space than varchar, it is better to use varchar to minimize the amount of storage and disk I/O for the data rows that need to be processed.

For BDB data tables, the difference is small, regardless of whether a fixed-length or variable-length data column is used. You can use both methods to try and run some experimental tests to see if there is a significant difference.

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

Consider using the enum data column. If you own a data column with a low base (with a limited number of different values), consider converting it to an enum column. The 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:

SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

Each column of output information gives an optimization recommendation for the data type of the columns in the datasheet. 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 may be long, and enum definitions are often difficult to read.

Based on the procedure analyse () output information, you may find that you can modify your own datasheet to take advantage of those more efficient data types. If you decide to change the type of a data column, you need to use the ALTER TABLE statement.