Shulou(Shulou.com)05/31 Report--

How to optimize the performance of MySQL management? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

1. Indexes

The columns to which the index should be added include the columns used for joins and the columns referred to in the WHERE, GROUP BY, or ORDER BY clause.

Columns with many duplicate values cannot improve performance by indexing, while columns with unique values can fully benefit from the index.

It is not more convenient to have more indexes, which may slow down write operations such as INSERT, DELETE, and UPDATE while increasing search speed. Very small tables do not need to be indexed.

The values of some server state variables can reflect the situation of the index or provide a reference for adding an index, such as:

Handler_read_key: the number of requests that read rows through the index. This value is low, indicating that the index is not used frequently.

Handler_read_rnd_next: the number of requests to read the next line in the data file. The high value indicates that there are a large number of full table scans indicating that the table is not properly indexed or that the query does not take advantage of the index.

You can view status variables by mysqladmin extended-status or SHOW STATUS commands, for example:

Mysql > show status like 'Handler_read_%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Handler_read_first | 0 | |

| | Handler_read_key | 0 | |

| | Handler_read_next | 0 | |

| | Handler_read_prev | 0 | |

| | Handler_read_rnd | 0 | |

| | Handler_read_rnd_next | 0 | |

+-+ +

6 rows in set (0.00 sec)

[@ more@] for indexed tables, the ANALYZE TABLE command should be executed periodically. This command analyzes and stores the key distribution of a table, and MySQL uses the stored key distribution to determine the join order of the table when joined, and which indexes to use when querying.

2.query cache

Check the relevant server system variables to confirm that query cache is turned on:

Mysql > show variables like'% query_cache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_query_cache | YES |

| | query_cache_limit | 1048576 | |

| | query_cache_min_res_unit | 4096 | |

| | query_cache_size | 15728640 | |

| | query_cache_type | ON |

| | query_cache_wlock_invalidate | OFF |

+-+ +

6 rows in set (0.00 sec)

Whether the have_query_cache:MySQL server supports query cache.

Query_cache_size: the total amount of memory allocated to query cache.

The type of query_cache_type:query cache. 0 or OFF means that query cache is not used, but the system still allocates memory space for query cache; 1 or ON means to cache all query results, except those that start with SELECT SQL_NO_CACHE; and 2 or DEMAND means that only statements that start with SQL_CACHE are cached.

Once the table changes, the cached contents of the table in query cache are invalidated, so frequently changing tables do not benefit from the use of query cache, and consider adding SQL_NO_CACHE options to these queries.

3. Query and analysis

Add the EXPLAIN keyword at the beginning of the query statement, and the returned result lists all the SELECT in the statement. For example:

Mysql > explain select a.username, b.nickname, a.email from members a, memberfields b where a.uid = b.uid and a.email lik

E "3.com"

+-- +

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-- +

| | 1 | SIMPLE | b | ALL | PRIMARY | NULL | NULL | NULL | 46237 |

| | 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 3 | test2.b.uid | 1 | Using where |

+-- +

2 rows in set (0.00 sec)

Id lists the location of SELECT in the entire query; select_type lists the type of query, such as simple query, the last query in UNION, subquery, etc.; type lists the case where the join is executed, const is the best and ALL is the worst; possible_keys lists the index that may be used; and key lists the index actually used Rows lists the number of rows that need to be retrieved in the corresponding table to execute the query, and multiplying the numbers in each record is the total cost of the query; Extra lists some other relevant information.

4. Optimize multi-table query

(1) subquery rejoin

For example:

Select email from cdb_members where uid = (select uid from cdb_memberfields where nickname = 'aquxyz')

Can be converted into:

Select email from cdb_members Centrm, cdb_memberfields c_mf where c_m.uid = c_mf.uid and nickname = 'aquxyz'

(2) skillfully making use of the restrictions on the result set.

For example:

Select nickname from cdb_memberfields where uid = (select uid from cdb_members where email like'% com.cn' and regdate = (select min (regdate) from cdb_members))

Can be converted into:

Select nickname from cdb_memberfields c_mf, cdb_members Centrm where c_mf.uid = c_m.uid and c_m.email like'% com.cn' order by c_m.regdate asc limit 1

(3) use variables

Again, the above example can be translated into the following two statements:

Select @ min:=min (regdate) from cdb_members

Select nickname from cdb_memberfields c_mf, cdb_members Centrm where c_mf.uid = c_m.uid and c_m.email like'% com.cn' and c_m.regdate = @ min

5. Optimization table

For tables that are frequently modified, use fixed-length fields instead of variable-length fields (not valid for InnoDB tables); delete useless fields.

The OPTIMIZE TABLE command.

Merge tables; use join fields of the same type and length in the query.

6. Adjust server settings

You can improve the performance of the MySQL server by adjusting some server system variables.

The buffer size used by the index block of the key_buffer_size:MyISAM table. Generally, it can be set to 25% and 30% of the available memory of the server.

The total number of tables that all threads of the table_cache:MySQL can open. You should decide whether to increase the value based on the value of the Opened_tables state variable.

Sort_buffer_size: the buffer size allocated for each thread's sort operation. Increase this value to speed up ORDER BY and GROUP BY operations.

Read_buffer_size: the buffer allocated for the sequential scan of each thread. Increasing this value can improve the query speed.

Read_rnd_buffer_size: the buffer allocated for the random scan of each thread. After the index sort operation, MySQL reads the ordered rows from this buffer, and increasing this value speeds up the ORDER BY operation. Each client allocates this buffer, so don't set the global variable too large.

Binlog_cache_size: the cache that holds SQL statements for binary log during a transaction. If you often use large, multi-statement transactions, you can increase this value for better performance.

Bulk_insert_buffer_size: the size of the tree insert allocated for each thread's bulk cache operation.

Tread_cache_size: the total number of threads cached by the server for reuse. If you have many new connections, consider increasing this value.

Undefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefinedundefined

This is the answer to the question on how to optimize the performance of MySQL management. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.