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

This article focuses on "what is the method of MySQL performance optimization", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the method of MySQL performance optimization"?

First, optimization ideas

The slow query of data does not mean that there is a problem with the writing of SQL statements. First of all, we need to find the source of the problem before we can prescribe the right medicine to the case. The author uses a flow chart to show the idea of MySQL optimization:

Without more words, it can be clearly seen from the figure that there are many reasons for slow data query, such as cache invalidation, MySQL server crash due to high concurrency access during this period of time, SQL statement writing problems, MySQL server parameter problems, hardware configuration restrictions MySQL service performance problems, and so on.

Second, check the status value of the MySQL server

If the number of concurrent requests in the system is not high and the query speed is slow, you can ignore this step and proceed directly to the SQL statement tuning step.

Execute the command:

Show status

Because there are too many results returned, the results are not posted here. Among the results returned, we mainly focus on the values of "Queries", "Threadsconnected" and "Threadsrunning", that is, the number of queries, the number of thread connections, and the number of thread runs.

We can monitor the status value of the MySQL server running by executing the following script

#! / bin/bash while true do mysqladmin-uroot-p "password" ext | awk'/ Queries/ {qipple 4} / Threads_connected/ {censor 4} / Threads_running/ {raster 4} END {printf ("% d% d% d\ n", qQuery ccent r)}'> > status.txt sleep 1 done

Execute the script for 24 hours, get the contents of status.txt, and calculate again through awk = = the number of requests for MySQL services per second = =

Awk'{printf ("% d% d% d\ n", qmeme 2) 3)} 'status.txt

Copy the calculated content to Excel to generate a chart to observe the periodicity of the data.

If the observed data changes periodically, as explained in the figure above, you need to modify the cache invalidation policy.

For example:

One of the values is obtained as the cache expiration time by random number in the interval of [3p6 and 9], which disperses the cache failure time and saves some memory consumption.

During the peak access period, some requests are offloaded to the unexpired cache, while others access the MySQL database, which reduces the pressure on the MySQL server.

4. Get the SQL statements that need to be optimized

Way 1: view the running thread

Execute the command:

Show processlist

Return the result:

Mysql > show processlist +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-+-+ -+-+ | 9 | root | localhost | test | Query | 0 | starting | show processlist | +- -+ 1 row in set (0.00 sec)

From the return result, we can see what command / SQL statement the thread executed and when it was executed. In practical application, the returned result of the query will have N records.

The value of the returned State is the key to judging whether the performance is good or bad. If the value appears as follows, the SQL statement recorded in this row needs to be optimized:

When the query result of Converting HEAP to MyISAM # is too large, put the result to disk, serious Create tmp table # create temporary table, severe Copying to tmp table on disk # copy memory temporary table to disk, serious locked # is locked by other query, serious loggin slow query # record slow query Sorting result # sort

Method 2: open the slow query log

Add two parameters under the [mysqld] line in the configuration file my.cnf:

Slow_query_log = 1 slow_query_log_file=/var/lib/mysql/slow-query.log long_query_time = 2 log_queries_not_using_indexes = 1

Where slowquerylog = 1 indicates the slow query is enabled; slowquerylogfile indicates the location where the slow query log is stored; longquerytime = 2 indicates the query > = 2 seconds to record the log; logqueriesnotusing_indexes = 1 records the SQL statement without using index.

Note: the path to slowquerylog_file cannot be written casually, otherwise the MySQL server may not have permission to write log files to the specified directory. It is recommended that you copy the above path directly.

After modifying the saved file, restart the MySQL service. The slow-query.log log file is created under the / var/lib/mysql/ directory. Connect to the MySQL server and execute the following command to view the configuration.

Show variables like 'slow_query%'; show variables like' long_query_time'

Test slow query log:

Mysql > select sleep (2); +-+ | sleep (2) | +-+ | 0 | +-+ 1 row in set (2.00 sec)

Open slow query log file

[root@localhost mysql] # vim / var/lib/mysql/slow-query.log / usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)) Started with: Tcp port: 0 Unix socket: / var/lib/mysql/mysql.sock Time Id Command Argument # Time: 2017-10-05T04:39:11.408964Z # User@Host: root [root] @ localhost [] Id: 3 # Query_time: 2.001395 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use test; SET timestamp=1507178351; select sleep (2)

We can see that the SQL statement that has just been executed for 2 seconds is recorded.

Although the slow query SQL information is recorded in the slow query log, the log record is dense and difficult to consult. Therefore, we need to filter out the SQL through a tool.

MySQL provides mysqldumpslow tools to analyze the logs. We can use mysqldumpslow-- help to see the command-related usage.

The common parameters are as follows:

-s: sort mode, followed by the following parameter c: number of visits l: lock time r: return record t: query time al: average lock time ar: average return record book at: average query time-t: how many items of data are returned-g: search with a regular expression, case-insensitive

Case study:

Get the 10 sql mysqldumpslow-s r-t 10 / var/lib/mysql/slow-query.log that returns the most recordsets, get the 10 sql mysqldumpslow-s c-t 10 / var/lib/mysql/slow-query.log with the most visits, and get the query statement mysqldumpslow-s t-t 10-g "left join" / var/lib/mysql/slow-query.log sorted by time that contains a left join.

Third, analyze SQL statements

Method 1: explain

Filter out the problematic SQL, and we can use the explain provided by MySQL to view the SQL execution plan (associated tables, table query order, index usage, etc.).

Usage:

Explain select * from category

Return the result:

Mysql > explain select * from category +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + +-+ | 1 | SIMPLE | category | NULL | ALL | NULL | 1 | 100.00 | NULL | +- -+ 1 row in set 1 warning (0.00 sec)

Field explanation: 1) id:select queries the serial number. The id is the same, the execution order is from top to bottom; the id is different, the higher the id value, the higher the priority, the first to be executed.

2) select_type: the operation type of querying data. The values are as follows:

Simple: simple query without subquery or union

Primary: contains complex subqueries, and the outermost query is marked with this value

Subquery: contains a subquery in select or where and is marked as this value

Derived: subqueries included in the from list are marked with this value, and MySQL recursively executes these subqueries to put the results in the temporary table

Union: if the second select appears after union, it is marked as this value. If union is included in a subquery of from, the outer select is marked as derived

Union result: the select that gets the result from the union table

3) table: shows which table the data is about

4) partitions: matching partition

5) type: the connection type of the table. The values and performance of the table are arranged as follows:

System: a table has only one row of records, which is equivalent to a system table

Const: found by indexing once, matching only one row of data

Eq_ref: unique index scan, with only one record in the table matching for each index key. Often used for primary key or unique index scanning

Ref: a non-unique index scan that returns all rows that match a single value. Used for =,

Indexed column of operator

Range: only retrieve a given range of rows, using an index to select rows. Generally use between, >, select @ @ profiling; +-+ | @ @ profiling | +-+ | 0 | +-+ 1 row in set, 1 warning (0.00 sec)

0 means off, 1 means on

2 enable profile

Set profiling = 1

Return the result:

Mysql > set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > select @ @ profiling; +-+ | @ @ profiling | +-+ | 1 | +-+ 1 row in set, 1 warning (0.00 sec)

After the connection is closed, the profiling status is automatically set to closed.

3 View the list of executed SQL

Show profiles

Return the result:

Mysql > show profiles +-+ | Query_ID | Duration | Query | +- -+ | 1 | 0.00062925 | select @ @ profiling | | 2 | 0.00094150 | show tables | | 3 | 0.00119125 | show databases | | 4 | 0.00029750 | SELECT DATABASE () | | 5 | 0.00025975 | show databases | 6 | 0.00023050 | show tables | | 7 | 0.00042000 | show tables | | 8 | 0.00260675 | desc role | | 9 | 0.00074900 | select name Is_key from role | +-+ 9 rows in set, 1 warning (0.00 sec)

Before the command is executed, other SQL statements need to be executed before it can be recorded.

4 query the execution details of the specified ID

Show profile for query Query_ID

Return the result:

Mysql > show profile for query 9 +-- +-+ | Status | Duration | +-+-+ | starting | 0.000207 | | checking permissions | 0.000010 | | Opening tables | 0.000042 | | init | | 0.000050 | | System lock | 0.000012 | | optimizing | 0.000003 | | statistics | 0.000011 | preparing | 0.000011 | executing | 0.000002 | Sending data | 0.000362 | | end | 0.000006 | query end | 0.000006 | closing tables | 0.000006 | | | freeing items | 0.000011 | | cleaning up | 0.000013 | +-- +-+ 15 rows in set | 1 warning (0.00 sec)

Each line is a process of state change and how long they last. The Status column is the same as show processlist's State. Therefore, the attention points that need to be optimized are the same as those described above.

5 get CPU, Block IO and other information

Show profile block io,cpu for query Query_ID; show profile cpu,block io,memory,swaps,context switches,source for query Query_ID; show profile all for query Query_ID

IV. Means of optimization

It is mainly explained by query optimization, index usage and table structure design.

1 query optimization

1) avoid SELECT * and query the corresponding fields if you need any data.

2) small tables drive large tables, that is, small data sets drive large data sets. For example, take the two tables of A _ id B as an example, and the two tables are associated by the table field.

When the dataset of table B is smaller than that of table A, use in to optimize exist; and use in. The execution order of the two tables is to check table B first and then table A.

Select * from A where id in (select id from B)

When the dataset of table An is smaller than that of table B, use exist to optimize in; and use exists. The execution order of the two tables is to check table A first and then table B.

Select * from A where exists (select 1 from B where B.id = A.id)

3) in some cases, you can use joins instead of subqueries, because using join,MySQL does not create temporary tables in memory.

4) add redundant fields appropriately to reduce table association.

5) rational use of indexes (described below). Such as: establish an index for sorting and grouping fields to avoid the emergence of filesort. More: a list of MySQL index data structures and optimizations

2 Index usage

2.1 scenarios suitable for using indexes

1) the primary key automatically creates a unique index

2) Fields frequently used as query criteria

3) Fields associated with other tables in the query

4) sorted fields in the query

5) Statistics or grouping fields in the query

2.2 scenarios that are not suitable for using indexes

1) frequently updated fields

2) unused fields in where condition

3) too few table records

4) tables that are frequently added and deleted

5) the value of the field has little difference or high repeatability.

2.3 principles for index creation and use

1) single table query: create an index in which column is the query condition

2) Multi-table query: when left join, the index is added to the right table associated field; when right join, the index is added to the left table associated field

3) do not perform any operations on index columns (calculations, functions, type conversions)

4) do not use! = in index columns, which is not equal to

5) the index column should not be empty, and do not use is null or is not null judgment

6) the index field is a string type, and the value of the query condition should be enclosed in''single quotation marks to avoid automatic conversion of underlying types.

Violation of the above principles may lead to index invalidation, which needs to be checked using the explain command.

2.4 Index failure

In addition to violating the principles of index creation and use, the following conditions can also cause the index to fail:

1) when fuzzy query, start with%

2) when using or, such as: field 1 (non-index) or field 2 (index) will cause the index to fail.

3) when using a composite index, the first index column is not used.

Index, taking the field as a composite index as an example:

3.1 Select the appropriate data type 6.3 Database table structure design

1) use the data type that can save the smallest amount of data

2) use simple data types. Int is easier to handle in mysql than varchar types.

3) try to use tinyint, smallint, mediumint as integer types instead of int

4) use not null to define fields whenever possible, because null takes up 4 bytes of space

5) use text types as little as possible, and it is best to consider sub-tables when you have to.

6) try to use timestamp instead of datetime

7) do not have too many fields in a single table. It is recommended to be within 20.

3.2 split of the table

When the data in the database is very large and the query optimization scheme can not solve the problem of slow query speed, we can consider splitting the table to reduce the amount of data in each table, so as to improve the query efficiency.

1) Vertical split: open multiple columns in a table to different tables. For example, some fields in the user table are accessed frequently, putting these fields in one table and other less commonly used fields in another table. When inserting data, use transactions to ensure data consistency between the two tables.

2) split horizontally: split by row. For example, in the user table, using the user ID, the remainder of 10 is taken from the user ID, and the user data is evenly distributed among the 10 user tables in 099. When searching, the data is also queried according to this rule.

3.3 Separation of read and write

Generally speaking, it is "read more and write less" for the database. In other words, the pressure on the database is mostly caused by a large number of operations to read data. We can adopt the scheme of database cluster, using one library as the master library, which is responsible for writing data; the other libraries are slave libraries, which are responsible for reading data. This relieves the pressure of accessing the database.

Fifth, server parameter tuning

1 memory dependent

Sortbuffersize sort buffer memory size

Joinbuffersize uses connection buffer size

Buffer size allocated when readbuffersize full table scan

2 IO correlation

Innodblogfile_size transaction log size

Number of Innodblogfilesingroup transaction logs

Innodblogbuffer_size transaction log buffer size

Innodbflushlogattrx_commit transaction log refresh strategy, whose values are as follows:

0: write log to cache once per second and flush log to disk

1: log writes cache and flush log to disk at each transaction commit

2: every time the transaction commits, execute log data to write to cache, and execute flush log to disk once per second

3 related to safety

Expirelogsdays specifies the number of days to automatically clean up binlog

Maxallowedpacket controls the size of packets that can be received by MySQL

Skipnameresolve disables DNS lookup

Read_only forbids write permissions for users with non-super permissions

At skipslavestart level, you can use slave to recover automatically.

4 other

Max_connections controls the maximum number of connections allowed

Tmptablesize temporary table size

Maxheaptable_size maximum memory table size

VI. Hardware purchase and parameter optimization

The performance of hardware directly determines the performance of MySQL database. The performance bottleneck of hardware directly determines the running data and efficiency of MySQL database.

1 memory dependent

The IO of memory is much faster than that of hard disk, which can increase the buffer capacity of the system and make the data stay in memory longer, so as to reduce the IO of disk.

2 disk Icano correlation

1) use SSD or PCle SSD devices to achieve at least hundreds or even tens of thousands of times IOPS improvement

2) the purchase of array cards equipped with both CACHE and BBU modules can significantly improve IOPS.

3) choose RAID-10 instead of RAID-5 whenever possible

3 configure CUP correlation

"in the server's BIOS settings, adjust the following configuration:"

1) choose Performance Per Watt Optimized (DAPC) mode to maximize the performance of CPU

2) turn off options such as C1E and C States to improve CPU efficiency

3) Memory Frequency (memory frequency) Select Maximum Performance

At this point, I believe you have a deeper understanding of "what is the method of MySQL performance optimization". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.