MySQL database optimization (III) _ MySQL

MySQL database optimization (III) 1. estimation of query performance

In most cases, you can estimate the query performance by counting the number of disk searches. For small tables, you usually only need to search a disk once to find the corresponding record (because the index may already be cached ). For a large table, we can roughly estimate this. it uses the B-tree for indexing. to find a record, the approximate search times are: log (row_count) /log (index_block_length/3*2/(index_length + data_pointer_length) + 1.

In MySQL, an index block is usually 1024 bytes, and the data pointer is usually 4 bytes. For a table with 500,000 records and an index length of 3 bytes (medium integer), according to the above formula, log (500,000) is required) /log (1024/3*2/(3 + 4) + 1 = 4 searches.

The index of this table requires about 500,000*7*3/2 = 2/3 MB of storage space (assuming of the typical index buffer). therefore, more indexes should be stored in the memory, the corresponding record can be found only once or twice.

For writing, it takes about four (or more) searches to locate the new index location. when updating a record, it usually requires two searches.

Note that the performance of the application is not mentioned in the previous discussion because the log N value increases and decreases. As long as everything can be cached by the operating system or SQL Server, the performance will only decrease slightly because the data table is larger. When the data grows bigger, it cannot be all put into the cache, and it will become slower and slower, unless the application is restricted by disk search (it increases with the increase of the log N value ). To avoid this situation, you can increase the index cache capacity as the data volume increases. For MyISAM tables, the index cache capacity is controlled by the system variable key_buffer_size.

2. SELECT query speed

In general, the first thing to make a slow SELECT... WHERE query faster is to first check whether the index can be added. All accesses to different tables usually use indexes. You can use the EXPLAIN statement to determine which indexes are used by the SELECT statement. For details, see "7.4.5 How MySQL Uses Indexes" and "7.2.1 EXPLAIN Syntax (Get Information About a SELECT )".

The following are some common suggestions for improving the speed of MyISAM table queries:

To make MySQL Query optimization faster, you can run the "analyze table" or "myisamchk -- analyze" command after the data TABLE has been fully loaded. It updates the value of each index part, which means the average value of the same record (this value is always 1 for a unique index ). MySQL will decide which index to use based on this value when you connect two tables based on a non-constant expression. To view the result, run show index from tbl_name after the data table is analyzed to view the value of the Cardinality field. Myisamchk -- description -- verbose displays the index distribution information.

To sort data by an index, run myisamchk -- sort-index -- sort-records = 1 (if you want to sort data on index 1 ). This is a good way to increase the query speed if you have a unique index and want to read records in sequence based on the index order. However, it takes a long time to sort a large table for the first time.

3. how does MySQL optimize the WHERE clause?

This section describes how the optimizer processes the WHERE clause. The SELECT statement is used in the example, but the optimization for the WHERE clause in the DELETE and UPDATE statements is the same. Note that the MySQL optimization work continues, so this chapter is not over yet. MySQL has done a lot of optimization work, not just the one mentioned in the document.

Some MySQL optimization practices are as follows:

Remove unnecessary parentheses:

(A AND B) AND c OR (a AND B) AND (c AND d ))))

-> (A AND B AND c) OR (a AND B AND c AND d)

Expand constant:

(

-> B> 5 AND B = c AND a = 5

Remove constant conditions (required when expanding constants ):

(B> = 5 and B = 5) OR (B = 6 AND 5 = 5) OR (B = 7 AND 5 = 6)

-> B = 5 or B = 6

Constant expression is calculated only once in the index.