Shulou(Shulou.com)06/01 Report--

MySQL Optimization-performance Analysis and query Optimization

Optimization should run throughout the product development cycle, such as viewing the execution plan when writing a complex SQL, installing the MySQL server as reasonably as possible (see too many cases where the default configuration is fully used), choosing a reasonable hardware configuration according to the application load, and so on.

1. Performance analysis

Performance analysis includes many aspects: CPU, Memory, disk / network IO, MySQL server itself, etc.

1.1 operating system analysis

Conventional operating system analysis usually includes some performance monitoring commands in Linux, such as top, vmstat, iostat, strace, iptraf and so on.

1, memory: memory is a large item, high query consumes a lot of query cache, memory must be enough, and reserve some for the system itself.

2. Disk: equipped with high-speed disk + RAID, the read and write speed will be better, and the cost of SSD will be gradually reduced, and the upgrade cost will be in an acceptable range.

3. Network: gigabit network cards are very common in the market at present.

4. CPU: although you can't run out of CPU in many cases, you can't make it a bottleneck.

Most of the MySQL in the production environment is deployed in the Linux system, and there are not many configurations that the Linux system itself can optimize. The selection of hardware is complex and involves the basic knowledge of computer composition, which requires additional understanding.

1.2 MySQL service performance analysis

The performance of the MySQL server usually checks the working status of the system through monitoring commands to determine which factors are the bottleneck.

1.2.1 SHOW GLOBAL STATUS

Shows the current working status of MySQL, including many parameters. Some parameters are described below, and the rest refer to the official instructions:

= =

1. Aborted_clients

If this value increases over time, check to see if the connection is gracefully closed and that the max_allowed_packet configuration variable is exceeded to cause a forced break.

2. Aborted_connections

Close to 0, check for network problems, if a small amount is normal, such as authentication failure.

3. Binlog_cache_disk_use and Binlog_cache_use

Most transactions should be done in buffers, and if the disk cache is large, consider increasing the in-memory cache.

4. Bytes_recevied and Bytes_sent

If the value is large, check to see if the query exceeds the required data.

5. Com_*

Try to make unusual variables such as Com_rollback exceed expectations and check with innotop.

6. Create_tmp_tables

Optimize the query to reduce this value.

7. Handler_read_rnd_next

Handler_read_rnd_next / Handler_read_rnd shows a rough average scan of the entire table, and if it is large, you can only optimize the query.

8. Open_files

It should not be close to open_files_limit, and if it is close, you should increase open_files_limit appropriately.

9. Qcache_*

Query cache related.

10. Select_full_join

Full join without index join, try to avoid and optimize the query.

11. Select_full_range_join

If the value is too high, the range query join table is used, and the range query is slow and can be optimized.

12. Sort_meger_passes

If the value is high, consider increasing the sort_buffer_size to find out which query caused the use of file sorting.

13. Table_locks_waited

The locking of the table causes the server lock to wait, and the row lock of InnoDB will not increase this variable. It is recommended to open the slow query log.

14. Threads_created

If the value is increasing, consider increasing thread_cache_size.

= =

1.2.2 SHOW ENGINE INNODB STATUS

The temporary data contains too much core information of InnoDB, and you need to have a deep understanding of how the InnoDB engine works. There is not much explanation here, please refer to the special documentation for this.

Note: it usually contains some detailed values, such as SEMAPHORES, TRANSACTIONS, FILE I Zero, LOG, BUFFER POOL AND MEMORY, etc., and some parameters are the averages since the last execution, so it is recommended to print the statistics for this period of time again, which is similar to the average read and write of iostat statistical disk.

1.2.3 enable slow log configuration

It may be helpful to troubleshoot the problem that causes the slow running of MySQL, SQL, and enable the configuration of slow log:

Slow_query_log=1slow_query_log_file=/YOUR_DIR/mysql_slow.log

Cooperate with slow log analysis tools (such as mysqlsla)

2. Query performance optimization

Generally speaking, when writing SQL, we should pay attention to whether the query can use the index and whether it causes full table scanning in large tables or high-frequency queries. These are mainly through empirical analysis and execution plan to get ideal query consumption.

2.1 query Foundation

Understand the query process, you can know which steps may have bottlenecks, execution plan results will also be reflected, the general process of MySQL query:

1. Client sends query instructions to the server.

two。 The server queries the cache and returns directly if it exists, otherwise the next step.

3. The server parses, preprocesses and optimizes queries to generate execution plans.

4. The execution engine calls the storage engine API to execute the query.

5. The server returns the results to the client.

The figure is shown as follows:

Parsing and preprocessing process:

-the parser decomposes the query and constructs a parsing tree to parse the syntax and verify the query to check whether the SQL is valid.

-preprocessor parsing semantics: such as checking whether tables and columns exist, whether there is ambiguity, etc.

-preprocessor check permissions.

Query optimizer:

The process is complicated, turning the result of the parse tree into an execution plan, the task of the optimizer is to find the best way (but not always the best option), and MySQL uses a cost-based optimizer to predict the cost of different execution plans.

-MySQL does not consider overhead that is not controlled by it, such as user stored procedures and user-defined functions

-do not consider other running queries

2.2 optimizing data access (this is important)

1. Does the application get more data than it needs? (PS: encountered many times to query all the data in the table and then read only 10 lines of code in the program)

2. Does the MySQL server analyze more rows than needed? Is the data not dropped at the storage engine layer? (Using index, Using where)

Typical errors are as follows:

1. Extract more rows than you need, and then only some of them in the program (you should use limit to limit the amount of data).

two。 The multi-table join extracts all the columns (only the required columns should be read).

3. Extract all columns (fetching unwanted columns may cause optimized indexes to fail, increase disk IO, waste memory, etc., but if you are aware of this impact and take advantage of query caching, simplifying design, etc.).

Type of access:

Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant.

The access speed is increased in this way.

For using the where statement to filter data, the best-to-worst scenario is:

1. Use where for index lookups to eliminate mismatched rows of data at the storage engine layer.

two。 Use an override index (Extra is Using Index) to avoid accessing rows. Rows are filtered after index data is obtained, which occurs at the MySQL server layer, but there is no need to read row data.

3. Query the data from the table, then filter (Using Where), occur on the server side and read the row data.

The results of the execution plan will be described in detail later.

2.3 about the implementation plan

A sample result of the execution plan is shown below (other visualization tools, such as mysql workbench, are also available):

The meaning represented can be found in the official document (https://dev.mysql.com/doc/refman/5.5/en/explain-output.html)

Here are some of the more important results:

Value of the TYPE field:

The gradual increase in access speed mentioned above has something to do with this:

Full Table Scan > Index Scan > Range Scan > Unique Index Lookup > Constant.

Here are some common instructions:

1. Const: match at most one line, such as SELECT * FROM rental where rental_id=1.

2. Eq_ref: the rows read match the previous table in turn.

3. Ref: the join uses only the left index or the index is not PRIMARY or UNIQUE (or the result is not the result of a row). If you get several rows of data, this is a better type.

4. Range: use the range scan of the index, such as =, >, > =

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.