Use of data types optimized by MySQL

1. Make your data as small as possible
One of the most basic optimizations is to make your data (and indexes) on the disk (and in Memory) occupy as little space as possible. This can give a huge improvement, because the disk reads quickly and usually uses a small number of primary storage. If you index a smaller column, the index also occupies less resources.
You can use the following technology to make the table better and minimize the storage space:
· Use the most effective (minimum) type as much as possible. MySQL has many specialized types that save disk space and memory.
· Use a smaller Integer type if the table may be smaller. For example, MEDIUMINT is often better than INT.
· If possible, the declared column is not null. It makes everything faster and saves one bit for each column. Note: If you really need NULL in your application, you should use it without a doubt, just avoid having it on all columns by default.
2. Use a fixed-length column instead of a variable-length Column
This criterion is especially important for tables that are frequently modified and prone to fragmentation. For example, select the CHAR column instead of the VARCHAR column. The trade-off is that when a fixed-length column is used, the table occupies more space. However, if you can afford this space, using a fixed-length line is much faster than using a variable-length line.
3. Define a column as NOT NULL
This process is faster and requires less space. In addition, it can also simplify the query because it does not need to check whether there is a special case NULL.
4. Consider using the ENUM Column
If a column contains only a limited number of specific values, you should consider converting it into an ENUM column. The values of the ENUM column can be processed more quickly because they are represented by numerical values internally.
BLOB and TEXT types
1. Advantages of BLOB and TEXT
Storing packaged or unpackaged data in an application using BLOB may allow data retrieval that requires several retrieval operations to be completed in a single retrieval operation. It also helps to store data that is not easily represented by the standard table structure or data that changes over time.
2. Possible drawbacks 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 BLOB will leave a large blank in the table. In the future, you will need to fill in one record or multiple records of different sizes.
Avoid retrieving large BLOB or TEXT values unless necessary. For example, the SELECT * query is not a good method unless it is certain that the WHERE clause can limit the result to the desired row. This may drag a very large BLOB value from the network without a destination. This is another scenario where BLOB identifiers stored in another column are useful. You can search for this column to determine the row you want and then retrieve the BLOB value from the specified row.
3. Necessary criteria
Use optimize table for tables that are prone to fragmentation
A large number of modified tables, especially those with variable length columns, are prone to fragmentation. Fragment is not good because it creates unused space in the disk block of the storage table. As time increases, more blocks must be read to obtain valid rows, reducing performance. This problem exists in any table with variable long rows, but this problem is more prominent for blob columns because their sizes change greatly. Optimize table is often used to keep performance intact.
Use multi-column Indexes
Multi-column index columns are sometimes useful. One technique is to create a hash value based on other columns and store it in an independent column. Then, you can search for the hash value to find the row. This is only valid for exact match queries. (Hash value pairs are useless for range searches with operators such as "<" or "> = ). In MySQL 3.23 and later versions, hash values can be generated using the MD5 () function. Hash indexes are particularly useful for BLOB columns. Note that the BLOB type cannot be indexed in MySQL versions earlier than 3.23.2. Even in version 3.23.2 or later, it is faster to search for BLOB values by using hash values as the identity values.
Isolate BLOB values in an independent table
In some cases, removing a BLOB column from a table and placing it in another sub-table may be of some significance, provided that the table can be converted to a fixed-length row format after the BLOB column is removed. This will reduce the fragmentation in the primary table and take advantage of the performance advantage of fixed-length rows.
Use ANALYSE process checklist Columns
If you are using MySQL 3.23 or an updated version, execute procedure analyse () to view the information it provides about the columns in the table.
ANALYSE ([max elements, [max memory])
It checks the results from your query and returns the results for analysis.
Max elements (default 256) is the maximum number of different values in each column that analyze will pay attention. This is used by ANALYSE to check whether the best column type is ENUM.
Max memory (8192 by default) is the maximum memory size allocated to each column when analyze tries to find all different 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 contains a column which is recommended for the best column type of each column in the table. In the second example, procedure analyse () is not recommended to contain more than 16 values or to retrieve ENUM types larger than 256 bytes (you can change these values as needed ). Without such restrictions, the output may be very long, and the definition of ENUM may be hard to read. (Www.bkjia.com)
According to the output of procedure analyse (), you can change the table to take advantage of more effective types. If you want to change the value type, use the alter table statement.