MySQL performance optimization Tips blog: www.extlight.com
The background,
Recently, the company added new functions to the project, but it took a long time to query the list of some functions after the launch. The reason is that the new function uses the interface of the old function, and the SQL query statements of the old interface are associated with 5 or 6 tables and the writing is not standardized enough, resulting in index failure and full table scan when MySQL executes SQL statements. The colleague who was originally in charge of optimization went home on leave, so the problem of optimizing query data fell in the hands of the author. The author in the online SQL optimization information after the success of solving the problem, here from the == global perspective == record and summarize MySQL query optimization related skills.
Second, optimization ideas
Slow data query does not mean there is a problem with SQL statement writing. First, we need to find the source of the problem before we can “treat it”. The author uses a flow chart to show the idea of MySQL optimization:
Without further words, it is clear from the figure that there are many reasons for slow data query, such as cache failure, MySQL server crash due to high concurrent access during this period; SQL statement writing problems; MySQL server parameter error; Hardware configuration limitations MySQL service performance issues.
Check the status of the MySQL server
If the number of concurrent requests is not high and the query speed is slow, you can skip this step and directly perform SQL statement tuning.
Execute command:
show statusCopy the codeResults are not posted here because too many results are returned. In the returned results, we focus on the values of ‘Queries’,’ Threads_connected ‘, and ‘Threads_running’, that is, the number of Queries, threads connected, and threads running.
We can monitor the running status of the MySQL server by executing the following script
#! /bin/bash
while true
do
mysqladmin -uroot -p"Password" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
doneCopy the codeRun the script for 24 hours to obtain the contents of status. TXT and use awk to calculate the number of MySQL service requests per second ==
awk '{q=$1-last; last=$1}{printf("%d %d %d\n",q,$2,$3)}' status.txtCopy the codeCopy the calculated content into Excel to generate charts and observe the periodicity of the data.
If the observed data changes periodically, as explained in the figure above, the cache invalidation policy needs to be modified.
Such as:
One of the values in the interval [3,6,9] is obtained by random number as the cache expiration time, which disperses the cache expiration time and saves part of memory consumption.
During peak access times, some requests are diverted to the unexpired cache and some access to the MySQL database, which reduces the strain on the MySQL server.
4. Obtain the SQL statement to be optimized
4.1 Method 1: View running threads
Execute command:
show processlistCopy the codeReturn 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)Copy the codeFrom the result, we can know what command /SQL statement the thread executed and when. In practice, N records are returned in the query result.
Where, the returned value of State is the key for us to judge the performance. If its value appears as follows, the SQL statement recorded in this row needs to be optimized:
Converting HEAP to MyISAM If the query result is too large, put the result to disk
Create tmp table Create a temporary table
Copying to tmp table on disk Copy temporary table to disk. Serious
locked # locked by another query, serious
loggin slow query # Record slow query
Sorting result # sortCopy the codeThe State field has many values, but for more information, see the link at the end of this article.
4.2 Method 2: Enable Slow Log Query
Add two parameters below the [mysqld] line in the my.cnf configuration file:
slow_query_log = 1 slow_query_log_file=/var/lib/mysql/slow-query.log long_query_time = 2 log_queries_not_using_indexes = 1Copy the codeSlow_query_log = 1 indicates that slow query is enabled. Slow_query_log_file specifies the location where slow query logs are stored. Long_query_time =2 Indicates that the log is generated after 2 seconds. Log_queries_not_using_indexes = 1 Records SQL statements that do not use indexes.
Note: The slow_query_log_file path cannot be written arbitrarily, otherwise the MySQL server may not have permission to write the log files to the specified directory. You are advised to copy the preceding path.
After modifying the saved file, restart the MySQL service. The slow-query.log file is created in the /var/lib/mysql.directory. Connect to the MySQL server. Run the following command to view the configuration.
show variables like 'slow_query%';
show variables like 'long_query_time';Copy the codeTesting slow query logs:
mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)Copy the codeOpen the 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-05 T04: it happened. 408964 z
# 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);Copy the codeWe can see that the SQL statement that just executed for 2 seconds is recorded.
Slow query logs record SQL information about slow query, but the logs are dense and difficult to query. Therefore, we need tools to filter out the SQL.
MySQL provides the mysqlDumpslow tool to analyze logs. You can use mysqlDumpslow –help to see how commands are used.
Common parameters are as follows:
-s: Sort method, followed by the following parameters c: number of access l: lock time r: returned record t: query time al: average lock time AR: average returned record book at: average query time -t: number of previous records to be returned -g: search and a regular expression are case-insensitiveCopy the codeCase study:
Gets the 10 SQL mysqlDumpslow that return the most recordsets-sR -t 10 /var/lib/mysql.slow-query. log Obtains the 10 SQL mysqldumpslow that are accessed the most times-sC -t 10 /var/lib/mysql/slow-query.log Obtains the first 10 queries in chronological order containing the left-join statement mysqldumpslow-s t -t 10 -g "left join" /var/lib/mysql/slow-query.logCopy the codeAnalyze SQL statements
5.1 Method 1: Explain
Filter out problematic SQL, we can use the explain provided by MySQL to check the SQL execution plan (associated tables, table query order, index usage, etc.).
Usage:
explain select * from category;Copy the codeReturn 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 | | NULL ALL | NULL | NULL | NULL | 1 | | NULL | 100.00 + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 rowin set, 1 warning (0.00 sec)Copy the code1) ID: select query serial number. The ids are the same and the execution sequence is from top to bottom. Different ids. A larger ID has a higher priority and is executed earlier
2) select_type: specifies the operation type to query data. Its value is as follows:
Simple: simple query, no subquery or Union primary: complex subquery, outermost query marked with the value subquery: in select orwhereContaining subqueries, tagged with that value: Subqueries contained in the FROM list are tagged with that value, and MySQL executes those subqueries recursively, putting the results in the temporary table Union: tagged with that value if the second SELECT appears after the union. If a union is contained in a subquery from, the outer select is marked as derived Union Result: the select that gets the result from the Union tableCopy the code3) table: show which table the row data is about
4) Partitions: match partitions
5) Type: the join type of the table, and its value, in descending order of performance, is as follows:
Eq_ref: unique index scan. For each index key, only one record in the table matches it. Often used for primary key or unique index scan REF: A non-unique index scan that returns all rows matching a single value. Range of indexed columns for the =, <, or > operators: Retrieves only rows in a given range, using an index to select the rows. Generally, between, >, and < are used. Index: only the index tree is traversed. ALL: ALL tables are scanned, which has the worst performanceCopy the codeNote: The first five cases are ideal index usage cases. It is usually optimized at least to the range level and preferably to the REF level
Possible_keys: Possible_keys specifies which index MySQL uses to find row records in this table. If the value is NULL, it indicates that no index is used. You can create an index to improve performance
7) key: displays the actual index used by MySQL. If NULL, no index query is used
8) key_len: indicates the number of bytes used in the index, which is used to calculate the length of the index used in the query. Without sacrificing accuracy, the shorter the length, the better it is to display the maximum length of the index field, not the actual length used
9) ref: displays the index field of the table associated with which field of which table
10) Rows: Based on the table statistics and selection, roughly estimate the number of rows needed to find or read, the smaller the better
11) Filtered: the percentage of rows returned as a result to the number of read rows, the greater the value, the better
12) extra: contains important additional information that is not suitable to be displayed in other columns. Common values are as follows:
Using filesort: indicates that MySQL uses an external index sort for data, instead of reading data in the order of the indexes in the table. SQL using temporary tables are used to hold intermediate results. MySQL uses temporary tables to sort query results. Common in sort order by and group by queries. SQL using index should be optimized to indicate that the corresponding select operation uses an overwrite index and avoids accessing rows of the tablewhere:whereUsing join buffer: Use join cache DISTINCT: Stops searching for more rows for the current combination of rows after the first match is foundCopy the codeNote: The SQL statement must be optimized for the first two values.
5.2 Profiling 2
You can use the profiling command to learn more about the resources consumed by SQL statements (the overhead per execution step).
5.2.1 Checking whether a Profile is enabled
select @@profiling;Copy the codeReturn result:
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)Copy the code0 indicates the disabled state, and 1 indicates the enabled state
5.2.2 enable profile
set profiling = 1;Copy the codeReturn result:
mysql> setprofiling = 1; Query OK, 0 rows affected (0.00 SEC) mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 rowin set, 1 warning (0.00 sec)Copy the codeAfter the connection is closed, the Profiling state is automatically set to closed.
5.2.3 Viewing the SQL List
show profiles;Copy the codeReturn result:
mysql> show profiles; +----------+------------+------------------------------+ | Query_ID | Duration | Query | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | the select @ @ 0.00062925 profiling | | 2 | | 0.00094150 show Tables | | 3 | | 0.00119125 show the databases | | | | 0.00029750 4 SELECT the DATABASE () | | | | 0.00025975 5 show the databases | | | | 0.00023050 6 show tables | | | | 0.00042000 7 show tables | | | | 0.00260675 8 desc role | | | | 0.00074900 9 select name,is_key from role | +----------+------------+------------------------------+ 9 rowsin set, 1 warning (0.00 sec)Copy the codeBefore running this command, you need to execute other SQL statements.
5.2.4 Querying execution information about the specified ID
show profile for query Query_ID;Copy the codeReturn result:
mysql> show profile forquery 9; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | Status | Duration | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + | starting 0.000207 | | | 0.000010 checking permissions | | | 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.000006 0.000362 | | end | | | | | 0.000006 query end | closing tables 0.000006 | | | freeing the items 0.000011 | | | Cleaning up 0.000013 | | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + 15 rowsin set, 1 warning (0.00 sec)Copy the codeEach row is a process of state changes and how long they last. The Status column is consistent with the State of the Show ProcessList column. Therefore, the points that need to be optimized are the same as those described above.
The value of the Status field can also be found in the link at the end.
5.2.5 Obtaining CPU and Block I/O 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;Copy the codeSixth, optimize means
It focuses on query optimization, index usage and table structure design.
6.1 Query Optimization
1) Avoid SELECT *, instead of SELECT *.
2) Small tables drive large tables, that is, small data sets drive large data sets. For example, take tables A and B as an example. The two tables are associated by the ID field.
When the data set of table B is smaller than that of table AinOptimize exist; useinSelect * from A select * from A select * from A select * from B select * from Awhere id in(select ID from B) When the data set of table A is smaller than that of table B, exist optimization is usedin; If exists is used, check table A first and then B select * from Awhere exists (select 1 from B where B.id = A.id)Copy the code3) In some cases, you can use joins instead of subqueries because MySQL does not create temporary tables in memory with joins.
4) Add redundant fields appropriately to reduce table association.
5) Reasonable use of indexes (described below). For example, create indexes for sorted and grouped fields and avoid filesort.
6.2 Index Usage
6.2.1 Suitable for Index scenarios
1) The primary key automatically creates a unique index
2) Fields frequently used as query conditions
3) Fields associated with other tables in the query
4) The sorted fields in the query
5) Query statistics or grouping fields
6.2.2 Indexes are Not Suitable for Scenarios
1) Frequently updated fields
2) Where condition does not use the field
3) Too few table records
4) Frequently added, deleted and modified tables
5) The value of the field has little difference or high repeatability
6.2.3 Principles for Creating and Using Indexes
1) Single table query: create an index in the column where the query criteria are used
2) Multi-table query: left JOIN, add index to right table associated field; When right JOIN, the index is added to the left table associated field
3) Do not perform any operations on the index column (calculation, function, type conversion)
4) Do not use index columns! =, <> is not equal
5) Index columns should not be null, and do not use is NULL or IS not NULL
6) The index field is a string, and the value of the query condition should be quoted to avoid automatic conversion of the underlying type
Violating the preceding rules may result in index failure. For details, run the Explain command
6.2.4 Index Failure
In addition to violating the principles of index creation and use, index invalidation can occur when:
1) For fuzzy query, start with %
2) When using OR, such as field 1 (non-index) or field 2 (index), the index will be invalid.
3) When using compound indexes, do not use the first index column.
Index (a,b,c), using fields A,b,c as composite indexes:
| statements | Whether the index is valid |
|---|---|
| where a = 1 | If yes, the field A index takes effect |
| where a = 1 and b = 2 | If yes, the indexes of fields A and B take effect |
| where a = 1 and b = 2 and c = 3 | If yes, all are valid |
| Where b = 2 or c = 3 | no |
| where a = 1 and c = 3 | Field A is valid, but field C is invalid |
| where a = 1 and b > 2 and c = 3 | Fields A and B are valid, but field C is invalid |
| where a = 1 and b like ‘xxx%’ and c = 3 | Fields A and B are valid, but field C is invalid |
6.3 Database table structure design
6.3.1 Selecting an appropriate data type
1) Use the data type that can hold the smallest amount of data
2) Use simple data types. Int is easier to handle in mysql than vARCHar
3) Try to use tinyint, smallInt, mediumInt as integer types instead of int
4) Use not NULL whenever possible, since NULL takes up 4 bytes of space
5) Use the text type as little as possible, and consider sub-tables when you must
6) Try to use timestamp instead of datetime
7) Do not have too many fields in a single table, it is recommended to be less than 20
6.3.2 Table splitting
When the data in the database is very large and the query optimization scheme cannot solve the problem of slow query speed, we can consider splitting the table to make the amount of data in each table smaller, so as to improve the query efficiency.
1) Vertical split: Separate multiple columns from a table into different tables. For example, if some fields in the user table are frequently accessed, put these fields in one table and some less commonly used fields in another table. When inserting data, use transactions to ensure data consistency between the two tables.
2) Horizontal split: split by row. For example, in the user table, the user ID is used, the remainder of the user ID is 10, and the user data is evenly distributed to the 10 user tables ranging from 0 to 9. Data is also queried according to this rule during lookups.
6.3.3 Read/write Separation
The general rule for databases is “read more than write less”. In other words, database stress is mostly caused by a large number of data reading operations. We can use the database cluster scheme, using a library as the master library, responsible for writing data; The other libraries are slave libraries that read data. This relieves database access pressure.
Seven, server parameters tuning
7.1 Memory Related
Sort_buffer_size Specifies the memory size of the sort buffer
Join_buffer_size Specifies the size of the connection buffer
Read_buffer_size Buffer size allocated during full table scan
7.2 the IO related
Innodb_log_file_size Transaction log size
Innodb_log_files_in_group Number of transaction logs
Innodb_log_buffer_size Transaction log buffer size
Innodb_flush_log_at_trx_commit Transaction log refresh policy. The value is as follows:
0: Writes logs to the cache and flush logs to the disk every second
1: Writes log to cache and flush log to disk on each transaction commit
2: For each transaction commit, log data is written to cache and flush log is flushed to disk every second
7.3 Security Related
Expire_logs_days Days for automatically clearing binlogs
Max_allowed_packet controls the size of packets that MySQL can receive
Skip_name_resolve disables DNS lookup
Read_only Disables the write permission of non-super users
Skip_slave_start you automatically restore using slave
7.4 other
Max_connections Controls the maximum number of connections allowed
Tmp_table_size Temporary table size
Max_heap_table_size Maximum memory table size
The author does not use these parameters to tune the MySQL server. For details and performance effects, please refer to the materials at the end of this article or separate baidu.
Hardware selection 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.
As software development programmers, we mainly focus on software optimization. The following hardware optimization can be used as an understanding
8.1 Memory Related
The I/O speed of the memory is much faster than that of the hard disk. Therefore, the buffer capacity of the system can be increased so that the data stays in the memory for a longer time, reducing the I/O speed of the disk
8.2 Disk I/O Related
1) Use SSD or PCle SSD devices to improve IOPS by hundreds or even tens of thousands of times
2) Array cards are equipped with CACHE and BBU modules to significantly improve IOPS
3) Use RAID-10 instead of RAID-5 whenever possible
8.3 Related to Configuring cpus
In the BIOS Settings of the server, adjust the following Settings:
1) Select the Performance Per Watt Optimized (DAPC) mode to maximize CPU Performance
2) Disable options such as C1E and C States to improve CPU efficiency
3) Memory Frequency select Maximum Performance
Ix. Reference materials
- Dev.mysql.com/doc/refman/… Show status of grammar
- Dev.mysql.com/doc/refman/… Show the processlist grammar
- Dev.mysql.com/doc/refman/… Thread state
- Dev.mysql.com/doc/refman/… Explain the grammar
- Dev.mysql.com/doc/refman/… Show the profile of grammar
- Blog.csdn.net/nightelve/a… MySQL server parameters were tuned
- Blog.csdn.net/qq_22929803… MySQL server parameters were tuned
- Blog.chinaunix.net/uid-1164064…
- Segmentfault.com/a/119000000…
- Blog.csdn.net/gzh0222/art…