Detailed explanation of data type optimization methods in mysql

This article mainly introduces the optimization methods related to data types in MySQL, including the use of multi-column indexes and other related optimization methods. Friends in need can refer to the following

Type selections that contribute to efficiency

1. Keep your data as small as possible

One of the most basic optimizations is to keep your data (and indexes) on disk ( and occupy as little space as possible in memory). This can give a huge improvement because disk reads are faster and typically use less main memory. If you index on smaller columns, the index also takes up fewer resources.

You can use the following techniques to make the table perform better and minimize storage space:

·Use the most efficient (smallest) type possible. MySQL has many specializations that save disk space and memory.

·If possible to make the table smaller, use a smaller integer type. For example, MEDIUMINT is often better than INT.

·If possible, declare the column NOT NULL. It makes everything faster and you save one bit per column. Note that if you really need NULL in your application, you should use it without question, just avoid having it on all columns by default.

2. Use fixed-length columns instead of variable-length columns.

This rule is particularly important for tables that are frequently modified and are prone to fragmentation. For example, CHAR columns should be selected instead of VARCHAR columns. The trade-off is that the table takes up more space when using fixed-length columns, but if you can afford the cost of space, using fixed-length rows will be much faster than using variable-length rows.

3. Define the column as NOT NULL

This will process faster and require less space. It also sometimes simplifies queries because there is no need to check for the presence of the special case NULL.

4. Consider using an ENUM column

If you have a column that only contains a limited number of specific values, you should consider converting it to an ENUM column. ENUM column values ​​can be processed faster because they are represented internally as numeric values.

About BLOB and TEXT types
1. Advantages of using BLOB and TEXT types

It is possible to use BLOB to store packaged or unpackaged data in an application Data retrieval that originally required several retrieval operations can be completed in a single retrieval operation. It is also helpful for storing data that is not easily represented by standard table structures or data that changes over time.

2. Possible disadvantages of using BLOB and TEXT types

On the other hand, BLOB values ​​also have their own inherent problems, especially when performing a large number of DELETE or UPDATE operations. Deleting A BLOB leaves a large gap in the table that will later need to be filled with one record or possibly multiple records of different sizes.

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 will limit the results to exactly the rows you want. Doing so may drag very large BLOB values ​​across the network without destination. This is another situation 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.

3. Necessary guidelines

Use OPTIMIZE TABLE for tables that are prone to fragmentation
Tables that are heavily modified, especially those with variable-length columns, are prone to fragmentation. Fragmentation is bad because it creates unused space in the disk blocks where the table is stored. Over time, more blocks must be read to get valid rows, which reduces performance. This problem exists for any table with variable-length rows, but it's more problematic for BLOB columns because their sizes vary so much. Regular use of OPTIMIZE TABLE will help keep performance from degrading.

Using multi-column indexes

Multi-column index columns are sometimes useful. One technique is to build a hash value based on other columns and store it in a separate column, then the row can be found by searching for the hash value. This only works for exact match queries. (Hash values ​​are not useful for range searches with operators such as "0f2b9e4ada5fafd77945e69dc6bec0d7="). In MySQL version 3.23 and above, hash values ​​can be generated using the MD5() function. Hash indexes are particularly useful on BLOB columns. One thing to note is that in versions prior to MySQL 3.23.2, the BLOB type could not be indexed. Even in version 3.23.2 or newer, finding a BLOB value using a hash value as an identifying value is faster than searching the BLOB column itself.

Isolate BLOB values ​​in a separate table

In some cases, moving BLOB columns out of the table and into another side table may have certain Meaning, provided that the table can be converted to fixed-length row format after moving out the BLOB columns. This reduces fragmentation in the main table and takes advantage of the performance benefits of fixed-length rows.

Use the ANALYSE procedure to check table columns
If you are using MySQL 3.23 or newer, you should execute PROCEDURE ANALYSE() to see the information it provides about the columns in the table

ANALYSE([max elements,[max memory]])

It examines the results from your query and returns an analysis of the results.

max elements (default 256) is the maximum number of distinct values ​​per column that analyze will notice. This is used by ANALYSE to check whether the optimal column type should be an ENUM type.

max memory (default 8192) is the maximum amount of memory that should be allocated to each column when analyze attempts to find all distinct values.

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max elements,[max memory]])

For example:

mysql>SELECT * FROM student PROCEDURE ANALYSE();

mysql>SELECT * FROM student PROCEDURE ANALYSE(16,256);

The corresponding output has a column with recommendations for the best column type for each column in the table. The second example asks PROCEDURE ANALYSE( ) not to suggest ENUM types that have more than 16 values ​​or take more than 256 bytes (these values ​​can be changed as needed). Without such a restriction, the output might be very long; the definition of ENUM would also be difficult to read.

Based on the output of PROCEDURE ANALYSE(), you will find that changes can be made to the table to take advantage of more efficient types. If you wish to change the value type, use the ALTER TABLE statement.

The above is the detailed content of Detailed explanation of data type optimization methods in mysql. For more information, please follow other related articles on the PHP Chinese website!