database table field design performance and efficiency __ Database

Original address: http://www.cnblogs.com/qiusnay/archive/2010/03/15/1686500.html

3. Performance and efficiency

5.3. Fixed length and variable length table

A data table containing any varchar, text, and other variable-length fields, which is a variable-length table, whereas a fixed-length table.

L for variable-length tables, many deletions and changes on them will cause more fragmentation in the table because of the different size of the record. You need to run OPTIMIZE table regularly to maintain performance. and the fixed-length table does not have this problem;

L If there are variable-length fields in the table, converting them to fixed-length fields can improve performance because fixed-length records are easy to handle. However, before attempting to do so, the following questions should be considered:

l Use fixed-length columns to deal with some kind of compromise. They are faster, but occupy more space. Each value of a char (n) Type column always occupies n bytes (even if the empty string does), because when stored in a table, the length of the value is not sufficient to fill the right space;

L and varchar (n)-type columns take up less space because they are allocated only the space needed to store each value, and each value plus one byte is used to record its length. Therefore, if you choose between char and varchar type, you need to compromise time and space;

L variable-length tables to fixed-length tables, you cannot convert only one variable-length field, you must convert all of them. Also, you must use an ALTER table statement to convert all at once, otherwise the conversion will not work;

L Sometimes can't use a fixed-length type, even if I want to. For example, for strings longer than 255 characters, there is no fixed-length type;

l When designing a table structure, if we can use fixed-length data type as long as possible, because the fixed-length table query, retrieve, update speed is very fast. Some key and frequently accessed tables can be split if necessary, such as a table with fixed-length data, and a table with no fixed-length data. For example, Phpcms's Phpcms_member table and so on. Therefore, the planning data structure needs to be considered in a global situation;

When the table structure is designed, it should be done just right and hashed to realize the optimal data storage system.

5.3.2. Operation and Retrieval

Numeric operations are generally faster than string operations. For example, comparison operations can be compared by logarithm in a single operation. The string operation involves a few byte-per comparisons, and if the string is longer, the comparison is more.

If the number of values in a string column is limited, the superiority of the numerical operation should be obtained by using the generic integer or Emum type.

Smaller field types are always much quicker to handle than larger field types. For strings, the processing time is directly related to the string length. In general, smaller table processing is faster. For a fixed-length table, you should select the smallest type, as long as you can store the desired range of values. For example, if Mediumint is sufficient, do not choose bigint. For variable-length types, you can still save space. The value of one text type records the length of the value in 2 bytes, while a Longtext records the length of its value in 4 bytes. If the stored value is never longer than 64KB, using text will save each value by 2 bytes.

5.3.3. Structural Optimization and index optimization

Indexing accelerates query speed, while index optimization and query optimization complement each other by optimizing indexes based on queries or by optimizing queries based on existing indexes, depending on how the query or index is modified, and which has the least impact on the existing product architecture and efficiency.

Index optimization and query optimization are the crystallization of years of experience, but we can not elaborate on this, but still give a few basic principles.

First, find out which SQL statements are most commonly executed, based on the actual running and access of the product. The most commonly executed and most often present in a program is a completely different concept. The most frequently executed SQL statement can be divided into large tables (many data entries) and operations on small tables (less data entries). Whether a large table or a small table, there can be divided into read (SELECT) more, write (Update/insert) more or read and write operations.

For SQL statements that are often executed, you need to pay particular attention to large table operations:

L Write a lot of, usually can use write caching method, first need to write or need to update data cache to file or other table, regularly to large table for bulk write operation. At the same time, we should try to make the large table which is often read and written as a fixed-length type, even if the original structure of the large table is not fixed length. Large table length, can change the data storage structure and data reading mode, a large table to be broken into a read and write more fixed-length table, and a read more write less variable long table to achieve;

L Read more, you need to set the SQL query frequency for high-frequency SQL statements of the index and Federated Index.

The small table is relatively simple, adding a specific index that meets the requirements of the query, usually the effect is more obvious. At the same time, the fixed-length small table also benefits the efficiency and the load ability enhancement. Small fixed-length tables with fewer fields can even be indexed.

Second, look at the conditions of the SQL statement and the sort field is very dynamic (that is, according to the different function switches or attributes, SQL query conditions and the change of the sort field is very large), the dynamic of the SQL statement is too high to be optimized through the index. The only way to do this is to cache the data, update it regularly, and apply it to situations where the results are not demanding.

MySQL index, commonly used with primary KEY, index, unique several, please refer to the MySQL documentation. Typically, the PRIMARY key and the unique index are faster than index when a single table data value is not duplicated, please use it as appropriate.

In fact, the index is the conditional query, sorting the read operation resource consumption, distributed to the write operation, the more indexes, the more disk space consumption, the more slow write operation. Therefore, the index must not be blindly added. The most fundamental starting point for the field index is still the probability of executing the SQL statement, the size of the table, and the frequency of the write operation.

5.3.4. Query optimization

MySQL does not provide optimization features for query conditions, so developers need to optimize the order of the query in the program. For example, the following SQL statement:

SELECT * FROM table WHERE a> ' 0 ' and b< ' 1 ' ORDER by C LIMIT 10;

In fact, no matter a> ' 0 ' or b< ' 1 ', the result is the same, but the query speed is very different, especially when you are working on a large table.

Developers need to keep this principle in mind: the first condition must be the condition of filtering and excluding more results, the second occurrence, and so forth. Therefore, the distribution of the values of the different fields in the table has a great effect on the query speed. The conditions in the order by are related only to the index, regardless of the conditional sequence.

In addition to conditional order optimization, for fixed or relatively fixed SQL query statements, you can also optimize the index structure to achieve a very high query speed. The principle is that, in most cases, a federated index, based on the order of the Where condition and the order of the sorted field of the ordering by, is the optimal index structure that matches this SQL statement. Although, the fact of the product can not only consider a SQL statement, but also take into account the space footprint to establish too many indexes.

Also take the above SQL statement as an example, the best when table records to millions or even tens, you can obviously see the speed of index optimization brought about by the upgrade.

According to the above two principles of condition optimization and index optimization, the optimal conditional order scheme can be obtained when the value of table table is the following scheme:

Field A	Field B	Field C
1	7	11
2	8	10
3	9	13
-1	0	12
Optimal conditions:b< ' 1 ' and a> ' 0 ' Optimal index: Index ABC (b, A, c) The reason:b< ' 1 ' as the first condition can first filter out the results of 75%. If you use a> ' 0 ' as the first condition, you can filter out 25% of the results first. Note 1: Field C because it does not appear in the condition, the conditional order optimization is irrelevant Note 2: The optimal index is derived from the optimal conditional order, not from the SQL statement in the example Note 3: The index is not a physical order to modify the data store, but rather a virtual pointer that is implemented by the physical data corresponding to a specific offset

Explain statements are an easy way to detect whether indexes and queries can match well. Run explain+ query statements in phpMyAdmin or other MySQL clients, such as EXPLAIN select * FROM table WHERE a> ' 0 ' and b< ' 1 ' order by C; This form, even if the developer Do not need to simulate the millions data, you can verify that the index is reasonable, relevant details please refer to the MySQL description.

It is worth noting that the Using Filesort is the least desirable case, and if explain the result, the database specifically creates a temporary table file for the query to cache the results and deletes it after the query has ended. As we all know, hard disk I/O speed is always the bottleneck of computer storage, therefore, the query should do its utmost to avoid the high execution frequency of SQL statements using Filesort. Although it is never possible for a developer to guarantee that all SQL statements in a product will not use Filesort.

Limited to space, this document is far from covering all aspects of database optimization, such as the reusability of federated indexes and common indexes, the index design of join joins, memory/heap tables, and so on. Database optimization is in fact a lot of factors and pros and cons of constantly weighing, modifying, only in the success and failure experience hashed can be drawn from the experience, this experience is often the most valuable and valuable.

5.3.5. Compatibility issues

Since MySQL 3.23 to 5.0 varies greatly, it is possible to avoid the use of special SQL statements in the program so as not to create compatibility problems and to make database porting difficult.

Typically in MySQL version 4.1, phpcms should be stored using a comparable character set, such as Gbk/big5/utf-8. Although the traditional latin1 coding has some compatibility, it is still not the recommended choice. When using the corresponding Non-default character set, the program needs to use set NAMES ' Character_set ' each time it is run to specify the character set of the connection, transmission, and result.

MySQL  5.0 more than a few new Sql_mode, the default Sql_mode depending on the server installation settings, so the program every time you need to use set sql_mode= '; To specify the current SQL mode.