MySQL optimization-performance analysis and query optimization

MySQL optimization-performance analysis and query optimization

optimization should be carried out throughout the product development cycle, such as when writing complex SQL to view the execution plan, when installing the MySQL server as reasonable as possible (see too much to completely use the default configuration installation), Choose a reasonable hardware configuration based on the application load.

1. Performance Analysis

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

1.1 Operating System Analysis

General operating system analysis, in Linux usually contains some performance monitoring commands, such as top, Vmstat, Iostat, Strace, Iptraf and so on.

1, Memory: Memory is large, high query consumes a lot of query cache, memory must be sufficient, and to the system itself to reserve some.

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

3, Network: The current market thousands of Gigabit Gigabit network card has been very common.

4, CPU: Although in many cases the CPU is not complete, but also can not let it become a bottleneck.

The majority of MySQL in the production environment is deployed in Linux systems, and the Linux system itself can be optimized for few configurations. Hardware selection is complex, involving the principle of computer composition knowledge, need additional understanding.

1.2 MySQL Service performance analysis

The performance of a MySQL server typically looks at the system's working state through a monitoring command to determine which factors are the bottleneck.

1.2.1 SHOW GLOBAL STATUS

Shows the current MySQL working status, contains a lot of parameters, the following to explain some parameters, the rest of the reference official note:

====================================

1. aborted_clients
If the value increases over time, check whether the connection is gracefully closed and check if the Max_allowed_packet configuration variable is exceeded causing a forced break.

2. Aborted_connections
Close to 0, check the 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 a buffer, and if disk cache is large, consider increasing the memory cache.

4. Bytes_recevied and Bytes_sent
If the value is large, check whether the query exceeds the required data.

5. com_*
Try to make these uncommon variables such as com_rollback more than expected and check with Innotop.

6. Create_tmp_tables
The optimization query lowers this value.

7. Handler_read_rnd_next
Handler_read_rnd_next/handler_read_rnd shows the approximate average of the full table sweep surface and, if large, only optimizes the query.

8. Open_files
Should not be close to open_files_limit, if close should be appropriate to increase the open_files_limit.

9. qcache_*
Query cache correlation.

Ten. Select_full_join
Full join no index joins, try to avoid, optimize the query.

Select_full_range_join.
A high value indicates that a range query joins the table and that the range query is slow and can be optimized.

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

Table_locks_waited.
The table is locked causing the server lock to wait, the InnoDB row lock does not increase the variable, it is recommended to turn on the slow query log.

Threads_created.
If the value is increasing, consider increasing the thread_cache_size.

====================================

1.2.2 SHOW ENGINE INNODB STATUS

The temporary data contains too much InnoDB core information, and need a deep understanding of how the InnoDB engine works, here do not do too much, please review the specific documentation for this.

Note: Usually contains semaphores, transactions, FILE I/O, LOG, BUFFER POOL and memory and other detailed values, some parameters are the average since the last execution, so it is recommended to print at intervals to get this time of the statistics, A bit similar to the IOSTAT statistics disk average read and write.

1.2.3 Turn on slow query log configuration

It may be helpful to troubleshoot SQL, which causes MySQL to run slowly, to turn on the slow query log configuration:

Slow_query_log=1slow_query_log_file=/your_dir/mysql_slow.log

With slow query log analysis tools (such as Mysqlsla)

2, Query performance optimization

In general, when writing SQL, pay attention to whether the query can be used to the index, whether in large tables or high-frequency queries caused by full table scan, which mainly through empirical analysis with execution plan to get more ideal query consumption.

2.1 Query Basics

Understand the query process in order to know which steps may be bottlenecks, execution plan results will also be reflected in the general process of MySQL query:

1. The client sends a query instruction to the server.
2. The server query cache, if present, returns directly, otherwise the next step.
3. Server parsing, preprocessing, and optimizing 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 diagram shows the following:

650) this.width=650; "Src=" http://images2017.cnblogs.com/blog/422439/201708/422439-20170802234006178-1287445185. JPG "style=" border:0px; "/>

Parsing and preprocessing processes:

-The parser constructs the parse tree after the query is decomposed, parsing and validating the query, and checking whether the SQL is valid.

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

-Preprocessor checks permissions.

Query optimizer:

The process is complex, turning the results of the parse tree into an execution plan, the optimizer's task is to find the best way (but not always choose the best solution), and MySQL uses a cost-based optimizer to predict the overhead of different execution plans.

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

-Do not consider other queries that are running

2.2 Optimizing data access (this is important)

1. Does the application get more data than needed? (PS: You have encountered the query table all the data and then only read 10 lines of code in the program)

2. Does the MySQL server analyze more rows than needed? Is the data not coming out of the storage engine layer? (using index, using where)

The typical errors are as follows:

1. Extract more rows than needed, and then just part of the program (you should use limit to restrict the amount of data).

2. Multi-table Join extracts all columns (should read only the required columns).

3. Extracting all columns (extracting unwanted columns can result in optimized index invalidation, increased disk IO, wasted memory, etc.), but if you know the impact and use the query cache, simplifying the design can also be considered.

Type of Access:

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

The access speed is incremented by this.

For filtering data using the where statement, it is best to have the worst case scenario:

1. Find the index with where to eliminate mismatched rows of data at the storage engine layer.

2. Use the overlay index (Extra as using index) to avoid access to rows, filter rows after the index data has been obtained, and occur at the MySQL server layer, but do not need to read row data.

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

The results of the execution plan are described in more detail later.

2.3 About the Implementation plan

Execution plan results sample (also available for other visualizers, such as MySQL Workbench):

650) this.width=650; "Src=" http://images2017.cnblogs.com/blog/422439/201708/422439-20170803001739990-422717760. JPG "style=" border:0px; "/>

650) this.width=650; "Src=" http://images2017.cnblogs.com/blog/422439/201708/422439-20170803001747819-930242748. JPG "style=" border:0px; "/>

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

Some of the more important results are shown here:

The value of the Type field:

The previously mentioned access speed is incremented in order to relate to this:

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

Here is a list of some common instructions:

1. Const: Matches up to 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 connection uses only the left index or the index is not primary or unique (or not a single row of results), if the resulting number of rows of data, this is a better type.

4. Range: Use the range of the index scan, such as used =, <>,, >=, <, <=, is NULL, <=>, between, in () and other conditions.

5, Index: In addition to the index tree is scanned, the indexing connection type is the same as all. There are two ways of doing this:

**************

1. If the index is the overwrite index of the query and satisfies all the data required in the table, only the index tree is scanned. In this case, extra is listed as using index. Index-only scans are usually faster than all, because the size of the index is typically smaller than the table data.

2. Use the index to perform a full table scan to find rows of data in indexed order. In the extra column there is no using index, which differs from all in that all is scan by row.

**************

6, All: Full table scan, bad (but sometimes the data is relatively small, MySQL will directly scan the whole table to read data, more efficient).

2.4 Optimizing a specific query

One approach to query optimization is to migrate old data, freeing up memory space to rebalance the index structure, making faster queries, many applications that retain half or three months of data to meet demand, additional platform access for old data, or routing at the application level.

2.4.1 Optimization count (encountered smattering use, resulting in optimization but counterproductive)

Count has two different ways of working: the number of statistical values and the number of statistical rows.

The value is a non-empty (non-null) expression (NULL means no value), and if a column name or other expression is defined in count (), count counts the number of times that the expression has a value (non-null).

The other way to do this is to count the number of rows, and when MySQL knows that the expression in parentheses is not NULL, then the COUNT (*) is an example, and it does not expand to all columns, it is ignored and counted.

2.4.2 Optimizing limit and Offset

A large offset query is expensive, such as limit 10000, 10, which produces 10010 data, and then only 10 rows are intercepted. Workaround:

1. Limit the number of pages that can be read by paging.

2. Consider using an overlay index, such as select ID, name, description from book limit 100, 10;

There is an index improvement on the ID: Select ID, Name, description from book INNER JOIN (SELECT ID from book limit, ten) as B;

MySQL optimization-performance analysis and query optimization