Today’s sharing started, please give us more advice ~

Slow query log is a type of log provided by MySQL. It is used to record statements in MySQL that exceed the response time threshold. In this article, I hope you got some tips on how to analyze slow queries in MySQL. Slow query, in MySQL, is always a topic that cannot be avoided. Slow query can be done in a variety of ways. What if you want to avoid slow query altogether? Young man, I suggest you rattail juice ~~ without further ado, the text begins!

1. Slowly query the configuration

1-1. Enable slow Query

MySQL support

1. Run the following command to enable slow query (temporary), which will be automatically stopped after the MySQL service restarts.

2, configure my.cnf (Windows is my.ini) system file open, modify the configuration file is persistent open slow query.

Method 1: Enable slow query by running a command

Step 1 Run the slow_query_log command to check whether slow query logs are enabled.

show variables like '%slow_query_log%';

mysql> show variables like '%slow_query_log%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log | OFF | | slow_query_log_file | The/var/lib/mysql/localhost - missile. The log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set (0.01) SEC) Step 2 Enable the slow query command:Copy the code

Step 2 Run the slow query command.

set global slow_query_log='ON';

Step 3 Set the threshold for logging the SQL execution time of slow query logs (long_query_time unit: second, default is 10 seconds).

I set it to 1 second below. SQL that takes longer than 1 second will be logged in the slow query

set global long_query_time=1;

Step 4 Query slow Query Log File Location.

show variables like '%slow_query_log_file%'; mysql> show variables like '%slow_query_log_file%'; +---------------------+-----------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------+ | slow_query_log_file | /var/lib/mysql/localhost-slow.log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.01 SEC)Copy the code

Slow_query_log_file Specifies the path and file where slow query logs are stored (default with data files)

Step 5 Check the status of slow query

Log out of the MySQL terminal and log in again to refresh the MySQL terminal.

When slow query is configured, it records the following SQL that meets the criteria:

  • The query
  • Data modification statement
  • SQL that has been rolled back

Method 2: Enable the my.cnf (Windows is my.ini) system file

(Version: MySQL5.5 and above)

Add the following configuration in the [mysqld] file of my.cnf to enable slow query, as shown in the following figure

Slow query is enabled after the database restarts. The query verification is as follows:

mysql> show variables like '%_query_%'; +------------------------------+-----------------------------------+ | Variable_name | Value | +------------------------------+-----------------------------------+ | have_query_cache | YES | | long_query_time | 1.000000 | | slow_query_log | ON | | slow_query_log_file | / var/lib/mysql/localhost - missile. The log | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 6 rows in the set (0.01 SEC)Copy the code

1-2. Slow Query logs

As shown above, SQL statements that take more than 1 second to execute (test)

  • Line 1: Record the time
  • The second line: user name, user IP information, thread ID number
  • Third line: execution time [unit: second], execution time to acquire the lock, number of result rows obtained, number of data rows scanned
  • Line 4: The timestamp of the SQL execution
  • Line 5: The concrete SQL statement

Explain slow query SQL

Analysis of mysql slow query logs, using the explain keyword can simulate the optimizer to execute SQL query statements, to analyze SQL slow query statements, below our test table is a 137W data APP information table, we come to analyze for example;

The following is an example of SQL:

-- 1.185s SELECT * from vio_basic_domain_info where app_name like '% domain_info ';Copy the code

This is a general fuzzy query statement, the query time: 1.185s, found 148 data; The query type is full table scan, and the number of rows scanned is 137W.

Mysql > EXPLAIN SELECT * from vio_basic_domain_info where app_name like '% domain_info '; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ ----+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | vio_basic_domain_info | NULL | | NULL ALL | NULL | NULL | NULL | 1377809 | | 11.11 Using where | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

When the SQL is used for the index, the SQL is as follows: Query time: 0.156 seconds, and 141 data items are found

-- 0.156s SELECT * from vio_basic_domain_info where app_name like '%';Copy the code

Explain analysis results are shown in the following table; Table information shows that the SQL uses the idx_app_name index. The query type is index range query, and the number of scanned rows is 141. Since not all the columns in the query are in the index (SELECT *), the table is returned once to fetch data from other columns.

Mysql > EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%'; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ ----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ ----------+-----------------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | 515 | NULL | 141 | | 100.00 Using index condition | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

When the SQL is used to overwrite the index, the SQL is as follows: Query time: 0.091s, and 141 data items are found

-- 0.091s SELECT app_name from vio_basic_domain_info where app_name like '%';Copy the code

Explain analysis results are shown in the following table; The query column is included in the index column, saving 0.06 seconds of return time to the table.

Mysql > EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '%'; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ ----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ ----------+--------------------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | range | idx_app_name | idx_app_name | | NULL 515 | 141 | | 100.00 Using the where; Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+ -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set, 1 warning (0.00 SEC)Copy the code

So how do YOU analyze SQL from EXPLAIN parsing results? What does each column represent? Let’s look down.

2-1. Introduction to the attributes of each column:

  • Id: indicates the serial number of the SELECT query, which indicates the execution priority. For sub-queries, the SERIAL number of the ID increases. A larger ID has a higher priority and the query is executed earlier
  • Select_type: indicates the query type.
  • Table: Displays the table of the output result set. If an alias is configured, the table is also displayed
  • Partitions: matches partitions
  • Type: access mode of the table
  • Possible_keys: possible index during query
  • Key: indicates the actual index
  • Key_len: length of the index field
  • Ref: comparison of columns to indexes
  • Rows: number of rows scanned (estimated number of rows)
  • Filtered: percentage of rows filtered by table criteria
  • Extra: Description and description of execution

The above categories of stars are often used to optimize slow queries

2-2. Slowly query and analyze commonly used attributes

1, type:

Table access mode, also called access type, indicates the way MySQL finds rows in a table.

ALL, index, range, ref, eq_ref, const, system, NULL (from left to right, performance from low to high)

ALL :(Full Table Scan) MySQL will Scan the entire Table to find a matching row

Index: (Full index Scan) The index type is different from ALL. Only the index tree is traversed

Range: Retrieves only rows in a given range, using an index to select rows

2, the key

The key column shows the actual index used by SQL. It is usually one of the possible_keys columns. The MySQL optimizer will calculate the number of scanned rows to select a more suitable index, or return NULL if no index is selected. Of course, the MySQL optimizer can select index errors and force MySQL to “use or ignore an index” by modifying the SQL.

  • FORCE an INDEX: FORCE INDEX (index_name), USE INDEX (index_name)
  • Force to IGNORE an INDEX: IGNORE INDEX (index_name)

3, rows

Rows is the number of rows that MySQL estimates to be read (scanned) in order to find the desired rows, and may not be exact.

4, Extra

This column shows some additional information, which is important.

Using index

The columns in the query are overwritten by the index, and the where filter criterion is the leading column in the index, and the Using index in Extra. This means that the index lookup can directly find the eligible data, without the need to return to the table.

Note: Leading columns generally refer to the first column or “previous columns” in a joint index, as well as in the case of a single column index; I’m just going to call it a leading column for the sake of understanding.

Using where

Note the MySQL server filters rows after the storage engine retrieves them. That is, there is no use of index, back to the table query.

Possible causes:

  1. The column of the query is not overwritten by the index;
  2. Where filters for leading columns that are not indexed or do not use the index correctly;

Using temporary

This means that MySQL uses a temporary table when sorting query results.

Using filesort

MySQL uses an external index sort for the results, rather than reading rows from the table in index order.

Using index condition

The columns in the query are not all in the index; the where condition is a range of leading columns

Using where; Using index

The query column is overwritten by the index, and the where filter condition is one of the index columns, but not the leading column of the index, or other conditions that affect the direct use of the index (such as the existence of range filter condition, etc.), in Extra, is Using WHERE; Using index: indicates that data matching conditions cannot be queried directly through index lookup.

Third, some slow query optimization experience sharing

3-1. Optimize LIMIT paging

Operations that require paging on the system are typically implemented using the limit plus offset method, along with the appropriate ORDER by clause. If there is a corresponding index, it is usually efficient, otherwise MySQL will need to do a lot of file sorting.

A major headache is when the offset is very large, such as limit 100000010, mysql will need to query 1000000 entries and only return the last 10 entries. The first 1000000 entries will be discarded, which is costly and causes slow queries.

One of the simplest ways to optimize such queries is to use index overrides whenever possible, rather than querying all columns. Then do an association as needed and return the desired columns. This is much more efficient for large offsets.

For the following query:

-- execution time: 1.379s SELECT * from vio_basic_domain_info LIMIT 1000000,10;Copy the code

Explain analysis results:

Mysql > EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ ----+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ - + -- -- -- -- -- -- -- + | | 1 SIMPLE | vio_basic_domain_info | NULL | | NULL ALL | NULL | NULL | NULL | 1377809 | | NULL | 100.00 +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+------ ----+-------+ 1 row in set, 1 Warning (0.00 SEC)Copy the code

The biggest problem with this statement is that the offset M in N is too large. As a result, each query must find the first M records in the entire table. Then, the first M records must be discarded and the first N records must be found from the first M records. This can be very costly if the table is very large, the filter field has no suitable index, and M is very large.

So if our next query can mark the location of the once upon a time a query after began to search, find meet the conditions of 10 records, and write down the location of the next query should start, in order to begin the next query can directly from the position, so that every time you don’t have to query first from the entire table, first find meet the conditions before M records, sacrifice, I have 10 more records starting from M plus 1 that satisfy this condition.

There are several ways to handle paged slow queries

Idea 1: Construct an overwrite index

SQL > select app_name, createTime from app_NAME, createTime from app_name, createTime from app_name, createTime from app_name, createTime from app_name, createTime from app_name, createTime from app_name This method is recommended when the number of query columns is small.

Time: 0.390 s

Mysql > EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------- --+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------- --+----------+-------------+ | 1 | SIMPLE | vio_basic_domain_info | NULL | index | NULL | idx_app_name | 515 | NULL | 1377809 | | 100.00 Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set, 1 warning (0.00 SEC)Copy the code

Idea 2: Optimize offset

Overwrite indexes are not available, so the key is to quickly filter out the first 100W of data. We can use the condition of self-increment primary key order, first query the ID value of the 1000001 data, then look up 10 rows later; This parameter is applicable to the scenario where the primary key ID is automatically increased. Time: 0.471 s

SELECT * from vio_basic_domain_info where 
  id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

Copy the code

Principle: Query the primary key ID corresponding to the 1000001 data based on the index, and then query the next 10 data based on the VALUE of the ID. You can see the two-step execution flow of this SQL in the EXPLAIN analysis results below.

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit Limit of 10, 1000000, 1); +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+-- --------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+-- --------+-------------+ | 1 | PRIMARY | vio_basic_domain_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 10 | 100.00 | Using the where | | 2 | SUBQUERY | vio_basic_domain_info | NULL | index | NULL | PRIMARY 8 | NULL | | 1000001 | 100.00 | Using index | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+-- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rows in the set, 1 warning (0.40 SEC)Copy the code

Method 3: “Delayed association”

Time: 0.439s Delay association applies to tables with a large order of magnitude, and the SQL is as follows:

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);
Copy the code

Here, we use the covered index + delayed associated query, which is equivalent to only query the ID column first, use the covered index to quickly find the 10 data ids of the page, and then put the returned 10 ids into the table through the primary key index for secondary query. (The rapid growth of table data has little influence on this method.)

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000, 10) as myNew using (id); +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+------- --+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows  | filtered | Extra | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+------- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | < derived2 > | NULL | | NULL ALL | NULL | NULL | NULL | 1000010 | | 100.00 NULL | | 1 | PRIMARY | vio_basic_domain_info | NULL | eq_ref | PRIMARY | PRIMARY 8 | | myNew. Id 100.00 | | 1 | NULL | | 2 | DERIVED | vio_basic_domain_info | NULL | index | NULL | PRIMARY 8 | NULL | | 1000010 | | 100.00 Using index | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+------- - + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set, 1 warning (0.00 SEC)Copy the code

3-2. Check if the index does not work

Fuzzy queries should avoid starting with the wildcard ‘%’ character, which will cause the database engine to abandon the index for a full table scan. As follows:

SELECT * FROM t WHERE username = '% 'Copy the code

Optimization: Use fuzzy queries after fields as much as possible. As follows:

SELECT * FROM t WHERE username = '%'Copy the code

If the requirement is to use fuzzy queries up front,

  • Use the MySQL built-in function INSTR(STR,substr) to match, similar to Java indexOf(), to find the position of the corner in the string.
  • Use FullText FullText index and match against
  • For a large amount of data, you are advised to use ElasticSearch and Solr. The data search speed of 100 million is in seconds
  • When tables are small, use like ‘%xx%’ instead of being fancy.

However, fuzzy matching of large fields in MySQL is a serious problem. After all, it takes too much performance to ensure ACID properties of transactions. Therefore, you are advised to replace big data storage engines such as ElasticSearch and Hbase if similar service requirements exist in actual scenarios. Feelings have nothing to do with this

2. Do not use not in. Otherwise, the engine scans all tables. It is recommended to use not exists instead as follows:

SELECT * FROM t WHERE name not IN (' timo ',' timo '); Select * from t as t1 where name IN (select * from t as t2 where id = t2.id); select * from t as t1 where name IN (select * from t as t2 where id = t2.id);Copy the code

3. Avoid using OR as much as possible. As a result, the database engine will abandon the index and perform full table scan. As follows:

SELECT * FROM t WHERE id = 1 OR id = 3

Optimization method: Union can be used instead of OR. As follows:

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3
Copy the code

4. Avoid null values, which may cause the database engine to abandon indexes and perform full table scan. As follows:

SELECT * FROM t WHERE score IS NULL

Optimization method: You can add the default value 0 to the field to determine the value 0. As follows:

SELECT * FROM t WHERE score = 0

5. Avoid expressions and function operations on the left side of the where condition, which will cause the database engine to abandon the index and perform full table scan.

You can move expressions and function operations to the right of the equals sign. As follows:

SELECT * FROM T WHERE score = 10 SELECT * FROM T WHERE score = 10Copy the code

6. When there is a large amount of data, avoid using the where 1=1 condition. This condition is usually used by default to make it easier to assemble a query condition, and the database engine will discard the index for a full table scan. As follows:

SELECT username, age, sex FROM T WHERE 1=1
Copy the code

Optimise: use code to assemble SQL to judge, no where condition remove WHERE, where condition add and.

7. Do not use <> or! =

When querying with index columns as criteria, avoid using <> or! = and other judgment conditions. If the unequal symbol is used, you need to re-evaluate the index to avoid creating an index on this field and replace it with another index field in the query condition.

8. The WHERE condition contains only composite index non-leading columns

Such as: composite index (joint) contains key_part1, key_part2, key_part3 three columns, but the SQL statement does not contain the index lead column “key_part1”, in accordance with the principle of joint MySQL index leftmost match, will not go joint index.

Select col1 from table where key_part2=1 and key_part3=2 select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2Copy the code

9. Implicit type conversions cause no use of indexes

In the following SQL statement, the index queue type is VARCHAR, but the given value is a numeric value, which involves implicit type conversion. As a result, the index cannot be entered correctly.

select col1 from table where col_varchar=123;
Copy the code

Summary:

What we need is to find and solve slow queries in time. In fact, many slow queries are passive, for example, due to a business data volume surge order of magnitude change, due to the change of business requirements and change of fields or operation of existing indexes. It’s not your fault, but you may have to take the fall

Today’s share has ended, please forgive and give advice!