Use of data types for MySQL optimization

1, make your data as small as possible

One of the most basic optimizations of
is to make your data (and index) occupy as little space on disk (and in memory) as possible. This can be a huge improvement because disk reads faster and usually uses less primary memory. Indexes also occupy a smaller number of resources if indexed on smaller columns.


You can use the following techniques to make the table more performance and minimize storage space:


Use the most efficient (smallest) type as much as possible. MySQL has a number of specialized types that save disk space and memory.


If you can make a table smaller, use a smaller integer type. For example, Mediumint is often better than int.


If possible, the declaration is listed as not NULL. It makes any thing faster and you save one for each column. Note 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, do not use variable-length columns


This guideline is particularly important for tables that are often modified to be easily fragmented. For example, you should select the CHAR column without selecting the VARCHAR column. The tradeoff is that when you use a fixed-length column, the table takes up more space, but if you can afford it, using a fixed-length row is much faster than using a variable-length row.


3, define the column as not NULL


this process faster and requires less space. It can also sometimes simplify the query because there is no need to check for exception NULL.


4, consider 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 columns can be processed more quickly because they are internally represented numerically.


about BLOBs and text types


1, the advantages of using BLOBs and text types


use a BLOB to store data that is packaged or not packaged in an application, and it is possible that a data retrieval that originally requires several retrieval operations to complete can be completed in a single retrieval operation. It is also useful for storing data that is not easily represented in a standard table structure or for data that changes over time.


2, possible drawbacks of using blobs and text types


On the other hand, BLOB values have their own inherent problems, especially when doing a large number of DELETE or UPDATE operations. Removing a BLOB leaves a large white space in the table that will be populated later with a single record or possibly multiple records of different sizes.


avoid retrieving large blobs or TEXT values unless necessary. For example, a SELECT * query is not a good idea unless it is certain that the WHERE clause can limit the result to the desired line. Doing so may drag very large BLOB values from the network without a destination. This is another case where the BLOB identity information stored in another column is useful. You can search the column to determine which row you want, and then retrieve the BLOB value from the qualifying row.


3, the necessary criteria


use OPTIMIZE table for easily fragmented tables


a large number of modified tables, especially those containing variable-length columns, easily produce fragmentation. The fragment is not good because it produces unused space in the disk block that stores the table. Over time, more blocks must be read to fetch a valid row, thereby reducing performance. This problem exists with any table with variable long rows, but this problem is more pronounced for BLOB columns because they vary greatly in size. Using OPTIMIZE TABLE frequently helps keep performance down.


using multiple-column indexes


multiple-column indexed columns can sometimes be useful. One technique is to create a hash value based on another column and store it in a separate column, and then find the row by searching for the hash value. This is only valid for exact matching queries. (hash values are not useful for scoping searches with operators such as "<" or ">="). In MySQL version 3.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 MySQL 3.23.2, BLOB types cannot be indexed. Even in 3.23.2 or newer versions, using a hash value as an identity value to find a BLOB value is faster than searching the BLOB column itself.


isolate the BLOB value in a separate table


In some cases, it may be meaningful to move a BLOB column from a table into another table, provided that the table is converted to a fixed-length row format after the BLOB column is moved. This reduces fragmentation in the primary table and leverages the performance advantages of fixed-length rows.


use the Analyse procedure to check table columns


If you are using a MySQL 3.23 or newer version, you should perform PROCEDURE analyse () to view 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 different values per column that analyse will notice. This is used by analyse to check whether the best 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 analyse tries to find all the 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);


a column in the corresponding output is a recommendation about the best column type for each column in the table. The second example requires PROCEDURE analyse () to not recommend an ENUM type that contains more than 16 values or more than 256 bytes (you can change these values as needed). If there is no such restriction, the output can be very long; The definition of an ENUM can also be difficult to read. (www.3lian.com)


based on the output of PROCEDURE analyse (), you will find that you can make changes to the table to take advantage of more efficient types. If you want to change the value type, use the ALTER TABLE statement.