Detailed description of MySQL database Optimization Methods

Use analyze for processing and regular Processing

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

Analyze table table_name

Check table tb1_name [, tbl_name]... [option]... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Optimize tables on a regular basis

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

The optimize table command should be used for TABLE optimization. This command can merge the space fragments in the TABLE and eliminate space waste caused by deletion or update. However, the optimize table command only applies to MyISAM, BDB, and InnoDB tables.

Example: optimize table table_name

The following is the mysql tutorial server Optimization Configuration method.

(1) back_log:
The number of connections that MySQL can have. When the main MySQL thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.
When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | NULL | Connect | NULL | login | when a NULL process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.

(2) interactive_timeout:
The number of seconds that the server waits for action on an interactive connection before closing it. An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect. The default value is 28800. I will change it to 7200.

(3), key_buffer_size:
The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 M). My MySQL host has 2 GB of memory, so I changed it to 402649088 (400 MB ).

(4) max_connections:
Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the Too connector connections error. The default value is 100. I will change it to 1024.

(5), record_buffer:
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K). I changed it to 16773120 (16 M)

(6) sort_buffer:
Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 M). I changed it to 16777208 (16 M ).

(7), table_cache:
Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. MySQL requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.

(, Thread_cache_size:
The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in Connections and Threads_created states, you can see the role of this variable. I set it to 80.

(10), wait_timeout:
The number of seconds that the server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200.

Note: you can modify the parameters by modifying the/etc/my. cnf file and restarting MySQL. This is a relatively cautious job. The above results are just some of my views. You can further modify it based on the hardware situation of your host (especially the memory size.

In the architecture of Apache, PHP, and MySQL, MySQL has the greatest impact on performance and is also a key core component. For Discuz! The same is true for Forum programs. Whether MySQL settings are reasonably optimized directly affects the speed and carrying capacity of the Forum! At the same time, MySQL is also the most difficult part of optimization. It not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters. Next, let's take a look at some of the basics of MySQL optimization. MySQL optimization is divided into two parts: one is the optimization of the physical hardware of the server, and the other is the optimization of MySQL itself (my. cnf.

(1) Impact of server hardware on MySQL Performance
A) disk tracing capability (disk I/O). Taking the current high-speed SCSI hard disk (7200 RPM) as an example, this hard disk is theoretically found 7200 times per second, which is determined by the physical characteristics, there is no way to change. MySQL performs a large number of complex query operations every second. You can imagine the disk read/write volume. Therefore, we usually think that disk I/O is one of the biggest factors restricting MySQL performance. For Discuz with an average daily access volume of more than 1 million PVS! Forum, due to disk I/O constraints, MySQL performance will be very low! To solve this problem, consider the following solutions: Use a RAID-0 + 1 disk array. Do not try RAID-5, mySQL's efficiency on RAID-5 disk arrays will not be as fast as you expected; instead, it will discard traditional hard disks and use faster flash storage devices. After Discuz! The company's technical engineering tests show that the use of flash storage devices is about 6-10 times faster than traditional hard disks.
B) For MySQL applications, we recommend that you use a multi-channel symmetric CPU in the S. M. P. architecture. For example, you can use two Intel Xeon GHz CPUs.
C) For a Database Server using MySQL, we recommend that the Server memory be no less than 2 GB. We recommend that you use more than 4 GB physical memory.

(2) MySQL's own factors when the above server hardware constraints are solved, let's see how MySQL's own optimization works. The optimization of MySQL is mainly to optimize and adjust the parameters in its configuration file my. cnf. The following describes some parameters that have a great impact on performance. Since the optimization settings of the my. cnf file are closely related to the server hardware configuration, we specify a hypothetical server hardware environment:
CPU: 2 Intel Xeon 2.4 GHz memory: 4 gb ddr hard drive: SCSI 73 GB
Next, we will describe the optimized my. cnf based on the above hardware configuration:
# Vi/etc/my. cnf only lists the content in the [mysqld] section in the my. cnf file. The content in other sections has little impact on MySQL running performance, so ignore it.
[Mysqld]
Port = 3306
Serverid = 1
Socket =/tmp/mysql. sock
Skip-locking
# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.
Skip-name-resolve prohibits MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!

 

Back_log = 384 specify the number of possible MySQL connections. When the MySQL main thread receives many connection requests within a short period of time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

Key_buffer_size = 256 M
# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 4 M
Thread_stack = 256 K
Table_cache = 128 K
Sort_buffer_size = the buffer size that can be used for sorting by 6 m. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

Read_buffer_size = buffer size available for 4 m read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Join_buffer_size = the buffer size that can be used by 8 m joint query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection!

Myisam_sort_buffer_size = 64 M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64 m specifies the size of the MySQL Query Buffer. You can run the following command on the MySQL console:
#> Show variables like '% query_cache % ';
#> Show status like 'qcache % ';
# If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is very large, it indicates that the query buffer is frequently used, if this value is small, it will affect the efficiency, you can consider not to query the buffer; Qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.

SQL Optimization Method

The format of EXPLAIN output results has changed, making it more suitable for the structure of UNION statements, subqueries, and derived tables. It adds two new fields: id and select_type. If you use MySQL 4.1 earlier than MySQL, you will not be able to see these fields.

The relevant information of each table is displayed in each line of the EXPLAIN result. Each line of record contains the following fields:

Id

The identifier of this SELECT statement. Each SELECT statement in a query has an ordered value.

Select_type

The SELECT type may include the following types:

SIMPLE

Simple SELECT (UNION or subquery is not used)

PRIMARY

SELECT of the outermost layer.

UNION

The second layer uses UNION after SELECT.

DEPENDENT UNION

The second SELECT in the UNION statement depends on external subqueries.

SUBQUERY

The first select in the subquery

DEPENDENT SUBQUERY

The first subquery in the subquery depends on the external SUBQUERY.

DERIVED

Derived table SELECT (subquery in the from clause)

Table

Record the referenced table.

Type

Table connection type. The following lists various types of table connections, from the best to the worst:

System

The table has only one record (equal to the system table ). This is a special case of the const table connection type.

Const

A table can contain at most one matching record, which is read from the beginning of the query. Since there is only one record, the field values recorded by this row in the remaining optimization programs can be treated as a constant value. The const Table query is very fast, because only one read is required! Const is used to compare a fixed value with a primary key or UNIQUE index. In the following queries, tbl_name is the const table:

　　
SELECT * FROMtbl_nameWHEREprimary_key = 1;
SELECT * FROMtbl_name
WHEREprimary_key_part1 = 1ANDprimary_key_part2 = 2;
 

Eq_ref

A row of records is read from the table and associated with the records read from the previous table. Different from the const type, this is the best connection type. It is used for connecting all parts of the index and the index is of the primary key or UNIQUE type. Eq_ref can be used to retrieve fields when "=" is compared. The comparative values can be fixed values or expressions, and the fields in the table can be used in the expressions. They are ready before reading the table. In the following examples, MySQL uses the eq_ref connection to process ref_table:

　　
SELECT * FROMref_table, other_table
WHEREref_table.key_column = other_table.column;
SELECT * FROMref_table, other_table
WHEREref_table.key_column_part1 = other_table.column
ANDref_table.key_column_part2 = 1;
 

Ref

All records that match the search value in this table will be taken out and used together with the records obtained from the previous table. Ref is the leftmost prefix used by the Connection Program to use the KEY, or the KEY is not a primary key or a UNIQUE index (in other words, the connection program cannot retrieve only one record based on the KEY value. When only a few matching records are queried based on the key value, this is a good connection type. Ref can also be used to compare fields by using the = Operator. In the following examples, MySQL uses ref to process ref_table:

　　
SELECT * FROMref_tableWHEREkey_column = expr;
SELECT * FROMref_table, other_table
WHEREref_table.key_column = other_table.column;
SELECT * FROMref_table, other_table
WHEREref_table.key_column_part1 = other_table.column
ANDref_table.key_column_part2 = 1;

Ref_or_null

This connection type is similar to ref. The difference is that MySQL will search for records containing NULL values during retrieval. This connection type is optimized from MySQL 4.1.1 and is often used for subqueries. In the following example, MySQL uses the ref_or_null type to process ref_table:

　　
SELECT * FROMref_table
WHEREkey_column = exprORkey_columnISNULL;

Index_merge

This connection type means that the Index Merge optimization method is used. In this case, the key field includes all the indexes used, and the key_len includes the longest part of the key used. For details, see "7.2.5 How MySQL Optimizes OR clses ".

Unique_subquery

This type replaces ref with IN subqueries IN the following form:

Value IN (SELECT primary_key FROM single_table WHERE some_expr)

Unique_subquery is only used to replace the index lookup function of a subquery, which is more efficient.

Index_subquery

The connection type is similar to unique_subquery. It uses a subquery to replace IN, but it is used when there is no unique index IN the subquery, for example, the following form:

Value IN (SELECT key_column FROM single_table WHERE some_expr)

Range

Only records within a given range are retrieved and an index is used to obtain a record. The key field indicates which index is used. The key_len field contains the longest part of the key used. In this type, the ref field value is NULL. Range IS used to compare a field with a field by any of the following operators: =, <>,>, >=, <, <=, is null, <=>,, or IN:
Tmp_table_size = 256 M
Max_connections = 768 specifies the maximum number of connection processes allowed by MySQL. If the Too connector Connections error is frequently reported during Forum access, you need to increase the value of this parameter.

Max_connect_errorrs = 10000000
Wait_timeout = 10 specifies the maximum connection time of a request. For servers with around 4 GB of memory, it can be set to 5-10.

Thread_concurrency = 8 this parameter is set to the number of logical CPUs of the server × 2. In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4x2 = 8.

Skip-networking enabling this option can completely disable the MySQL TCP/IP connection mode. If the WEB server accesses the MySQL database tutorial server remotely, do not enable this option! Otherwise, the connection will fail!