MYSQL data column type and query efficiency
Selecting an appropriate data column type helps improve the execution speed of query commands. The following are some suggestions on how to select an appropriate data column type:
Use data columns with smaller sizes as much as possible. This saves disk space and speeds up query. If an index is built on a short data column, the processing speed of the index will be further improved.
Select the most suitable data table type for the data column type. For example, if a fixed-length data column is in the MyISAM or ISAM data table, the speed is the fastest, so in this case, use the char type instead of the varchar type in the data table to save the string data. For InnoDB data table types, the varchar type can effectively reduce the occupied space and disk I/O. Therefore, it is advantageous to use the varchar type. For a data table of the BDB type, the difference between the fixed length and the variable length column type is not big. you can choose one of them.
Try to declare the data column as not null to save storage space and speed up processing.
For data columns with limited values, consider using the ENUM data column type. The processing speed of the ENUM data column type in MySQL is very fast.
Use the procedure analyse () statement to analyze the data table. It provides suggestions on the declaration of the data column. we can modify the statements according to the suggestions.
Select * from table_name procedure analyse ();
Select * from table_name procedure analyse (16,256); # (16,256) indicates that if a column has more than 16 values or has more than 256 bytes in length, we do not recommend using ENUM.
Use the optimize table statement to sort data tables that are prone to fragmentation. Data tables that contain variable-length data columns are fragmented, occupying extra disk space and affecting query speed. Therefore, you must regularly run the optimize table statement to prevent data TABLE query performance degradation. However, this statement is only valid for MyISAM data tables. The common method for data table fragmentation is as follows: first use the tool mysqldump to export the data table, then delete the data table, and then recreate it, for example:
$ Mysqldump -- opt db_name table_name> dump. SQL
$ Mysql db_name <dump. SQL
Place unstructured and changed data in the BLOB data column and regularly OPTIMIZE it using the optimize table command.
Artificially add a data column to the data table to act as an index. In this case, a hash value is calculated based on other data columns in the data table and saved in a data column. then, the hash value is searched to retrieve data rows. Note that this technique is only applicable to exact match queries. The hash value does not work in operations such as greater than or less. Hash values can be MD5 () (applicable to versions 3.23 and later), SHA1 () (applicable to versions 4.0.1 and later), CRC32 () (applicable to versions 4.1 and later), and other functions. Retrieving BLOB and TEXT values using hash values is faster than directly retrieving BLOB and TEXT.
Try to avoid retrieving large BLOB values. If you want to search, you should first use the hash value mentioned above to filter. Instead of blindly transmitting a large number of BLOB values in the network.
If the BLOB value is removed from another data table, other data columns in the data table can be converted into fixed-length data columns. In this way, the data table fragments can be reduced, and select * queries in the original table do not transmit large BLOB values over the network without any need.