Shulou(Shulou.com)05/31 Report--

What are the column types in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

What is the relationship between MySQL column type selection and query efficiency

■ uses fixed-length columns instead of variable-length columns. This rule is particularly important for tables that are often modified so that they are prone to fragmentation. For example, you should select the CHAR column instead of the VARCHAR column. The tradeoff is that the table takes up more space when using fixed-length columns, but if you can afford the cost of this space, using fixed-length rows will be much faster than using variable-length rows.

■ do not use longer columns when shorter columns meet the requirements. If you are using fixed-length CHAR columns, you should keep them as short as possible. If the maximum value stored in a column is 40 characters, don't define it as CHAR (255); just define it as CHAR (40). If you could use MEDIUMINT instead of BIGINT, the table would be smaller (and the disk IPUBO would be less), and its value could be processed faster in the calculation.

■ defines the column as NOTNULL. This process is faster and requires less space. And sometimes the query can be simplified because there is no need to check for the existence of a special case NULL.

■ considers using the ENUM column. If you have a column that contains only a limited number of specific values, you should consider converting it to an ENUM column. The values of the ENUM column can be processed more quickly because they are represented as numeric values internally.

■ uses PROCEDUREANALYSE (). If you are using MySQL3.23 or later, you should execute PROCEDUREANALYSE () to see the information it provides about the columns in the table:

One column in the corresponding output is a recommendation about the best column type for each column in the table. The second example asks PROCEDUREANALYSE () not to recommend ENUM types that contain more than 16 values or take more than 256bytes (these values can be changed as needed). Without such restrictions, the output can be long; the definition of ENUM can be difficult to read. Based on the output of PROCEDUREANALYSE (), you will find that the table can be changed to take advantage of more efficient types. If you want to change the value type, use the alter table statement.

What is the relationship between MySQL column type selection and query efficiency

■ loads data into BLOB. Using BLOB to store packaged or unwrapped data in an application makes it possible to complete data retrieval that originally required several retrieval operations to be completed in a single retrieval operation. It is also helpful to store data that is not easily represented by the standard table structure or data that changes over time. In the introduction to the ALTERTABLE statement in Chapter 3, there is an example of dealing with a table that stores the results from the Web questionnaire. This example discusses how to use ALTERTABLE to add columns to the table when adding questions to the questionnaire.

Another way to solve this problem is to have the application processing Web wrap the data into some kind of data structure and insert it into a single BLOB column. This increases the overhead of the application decoding the data (and encodes the records after they are retrieved from the table), but simplifies the structure of the table and eliminates the need to change the table when changing the questionnaire. Blob values, on the other hand, have their own inherent problems, especially when performing a large number of DELETE or UPDATE operations. Deleting BLOB leaves a large blank space in the table, which will need to be filled in later with one record or possibly multiple records of different sizes.

■ uses OPTIMIZETABLE for tables that are prone to fragmentation. A large number of modified tables, especially those with variable length columns, are prone to fragmentation. Fragmentation is not good because it creates unused space in the disk block where the table is stored. Over time, more blocks must be read to fetch valid rows, which degrades performance. This problem exists for any table with variable length rows, but it is more prominent for BLOB columns because they vary greatly in size. Regular use of OPTIMIZETABLE helps keep performance intact.

■ uses a composite index. Composite index columns are sometimes useful. One technique is to create a hash value based on other columns, store it in a separate column, and then search for the hash value to find the row. This is only valid for queries that match exactly. (hash values are not useful for range searches with operators such as "="). In MySQL3.23 and above, hash values can be generated using the MD5 () function. Hash indexes are particularly useful for BLOB columns. One thing to note is that in previous versions of MySQL3.23.2, BLOB types could not be indexed. Even in version 3.23.2 or later, using hash values as identity values to find blob values is faster than searching for BLOB columns themselves.

■ should avoid retrieving large BLOB or text values unless necessary. For example, a SELECT* query is not a good idea unless you are sure that the WHERE clause can restrict the result to exactly the desired row. Doing so may drag very large BLOB values aimlessly from the network. This is another case where BLOB identification information stored in another column is useful. You can search the column to determine the desired row, and then retrieve the blob value from the qualified row.

■ isolates BLOB values in a separate table. In some cases, it may make sense to move the BLOB column out of the table into another secondary table, provided that the table can be converted to a fixed-length row format after moving out of the BLOB column. This reduces fragmentation in the main table and takes advantage of the performance of fixed-length rows.

After reading the above, have you mastered the methods of column types in MySQL? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.