MySQL Advanced – Query and slow query log analysis

“This is the 28th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Causes of SQL performance degradation

In the daily operations of the process, often encounter the DBA will some execution efficiency is low with many developers find analysis of SQL, when we get the SQL statement, after the analysis of these SQL before, need clear can lead to analyze the reasons of the decrease of SQL execution, execution performance degradation can be embodied in the following two aspects:

• Long wait time

1. Lock table leads to query in waiting state. Subsequently, we analyze the principle of SQL execution from MySQL lock mechanismCopy the code

• Long execution time

Invalid index 3. Too many associated query joins 4. Server tuning and Settings of various parametersCopy the code

Optimization principles that need to be followed

Query optimization is a complex project involving hardware, parameter configuration, parsers of different databases, optimizer implementation, execution order of SQL statements, indexes, and collection of statistics, etc

Here are a few key principles for writing SQL that can help you write more efficient SQL queries

• Rule 1: Return only the desired results
  • Be sure to specify a WHERE condition for the query statement to filter out unwanted rows
  • Avoid select * FROM because it queries all the fields in the table
• Second: Make sure the query uses the correct index
  • Fields that frequently appear in WHERE conditions are indexed to avoid full table scans
  • Add the fields sorted BY ORDER to the index to avoid additional sorting operations
  • The performance of join queries can be improved by indexing the associated fields of multi-table join queries
  • Add the GROUP BY GROUP action field to the index to complete the grouping using the index
• Rule 3: Avoid invalidating indexes
  • Expression operations on index fields in the WHERE clause or use of functions will invalidate the index
  • Index cannot be used if the wildcard appears on the left side of a LIKE match
  • If an index is created on a field in a WHERE condition, try to set it to NOT NULL

The execution order of SQL

• SQL written by programmers

• SQL executed by MySQL

1. FORM clause: Cartesian product of the left and right tables
2. ON: Filters the data that meets the conditions
3. JOIN: If it is an inner JOIN it will be fine, but if it is an outer JOIN it will add back some rows that were filtered out in the previous step
4. WHERE: Rows that do not meet conditions are removed and cannot be recovered
5. GROUP BY: Only the first row of each GROUP or the value of the aggregate function can be obtained after grouping
6. HAVING: Filters the grouped data
7. SELECT: Perform the SELECT operation to obtain the required columns
8. DISTINCT: go to heavy
9. ORDER BY: sort
10. LIMIT: Retrieves the record for the specified row and returns the result

• View the following SQL analysis execution order

select
    id,
    sex,
    count(*) AS num
from
    employee
where name is not null
group by sex
order by id
Copy the code

• The SQL execution sequence is as follows

1. Start by executing the FROM clause to assemble the data source FROM the Employee table
2. Execute the WHERE clause to filter all data in the Employee table whose name is not NULL
3. Execute the GROUP BY clause to GROUP BY “gender” column
4. Perform the select operation to obtain the required columns
5. Finally, order by is executed to sort the final results

JOIN Query in seven ways

• There are seven types of JOIN, which can be divided into four types: inner JOIN, left JOIN, right JOIN and full JOIN

JOIN query SQL preparation

Create table insert data

Table - department
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
    `id` varchar(40) NOT NULL.`name` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

- the employees table
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
    `id` varchar(40) NOT NULL.`name` varchar(40) DEFAULT NULL.`age` int(3) DEFAULT NULL.`deptid` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `deptid` (`deptid`),
    CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert department data
INSERT INTO `t_dept` VALUES ('1'.'研发部');
INSERT INTO `t_dept` VALUES ('2'.'Personnel Department');
INSERT INTO `t_dept` VALUES ('3'.'Finance Department');

-- Insert employee data
INSERT INTO `t_emp` VALUES ('1'.'zhao four'.23.'1');
INSERT INTO `t_emp` VALUES ('2'.'liu can'.25.'2');
INSERT INTO `t_emp` VALUES ('3'.'wide he'.27.'1');
INSERT INTO `t_emp` VALUES ('4'."Yutian".43.NULL);
Copy the code

In the connection

SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id
Copy the code

Left connection

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
Copy the code

Left join to remove overlap

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL;
Copy the code

The right connection

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
Copy the code

Join right to remove overlap

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL;
Copy the code

All connection

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
Copy the code

The MySQL UNION operator is used to join the results of more than two SELECT statements into a result set. Multiple SELECT statements delete duplicate data.

Their own

SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL

UNION

SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL
Copy the code

Slow query log analysis

Slow Query

MySQL slow query, full name is slow query log, is a type of log provided by MySQL, used to record statements in MySQL whose response time exceeds the threshold.

By default, the MySQL database does not start slow query logs. You need to set this parameter manually.

It is not recommended to enable this parameter unless necessary for tuning, because slow query logging may have some performance impact.

Slow query logging supports writing log records to files and database tables.

Slow Query Parameters

1. Execute the following statement

SHOW VARIABLES LIKE "%query%" ;
Copy the code

2. MySQL > slow query

• Slow_query_log: indicates whether slow query logs are enabled. 1 indicates that slow query logs are enabled, and 0 indicates that slow query logs are disabled.
• Slow-query-log-fifile: specifies the path for storing slow query logs of the MySQL database, new in version 5.6 or later.
• Long_query_time: slow query threshold. When the query time exceeds the threshold, a log is generated.

Slowly query the configuration mode

1. By default, the value of slow_query_log is OFF, indicating that slow query logging is disabled

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
Copy the code

2. This can be enabled by setting the value of slow_query_log

mysql> set global slow_query_log=1;

mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
Copy the code

3. Slow_query_log =1 Slow_query logs are valid only for the current database and are invalid after MySQL restarts. To be permanent, you must modify the configuration file my.cnf (as well as other system variables)

Slow_query_log =1 slow_query_log_file=/var/lib/ mysql.lagou-slow. log -- Restart mysql service  mysqld restart mysql> SHOW VARIABLES LIKE '%slow_query_log%'; +---------------------+-------------------------------+ | Variable_name | Value | +---------------------+-------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/lib/mysql/lagou-slow.log | +---------------------+-------------------------------+Copy the code

4. After the slow query log is enabled, what KIND of SQL is recorded in the slow query log? This is controlled by the long_query_time parameter, which by default is 10 seconds

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+| long_query_time | | + 10.000000-----------------+-----------+

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+| long_query_time | | + 10.000000-----------------+-----------+
Copy the code

5. I changed the variable long_query_time, but the query variable long_query_time is still 10. Didn’t I change it to 10? Note: After using the set global long_query_time=1 command, you need to reconnect or open a new session to see the value changed.

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+| long_query_time | | + 1.000000-----------------+----------+
Copy the code

6. The log_output parameter specifies the log storage mode. Log_output =’FILE’ to save logs to a FILE. The default value is ‘FILE’. Log_output =’TABLE’ stores the log to the database so that the log information is written to the mysql.slow_log TABLE.

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
Copy the code

The MySQL database supports two log storage modes, which are separated by commas (,), for example, log_output=’FILE,TABLE’. Recording logs in the dedicated log table consumes more system resources than recording logs in files. Therefore, to enable slow query logs and achieve higher system performance, you are advised to record logs in files first

7. System variable log-queries-not-using-indexes: The queries that do not use indexes are recorded in the slow query logs (optional). If tuned, it is recommended that this option be enabled.

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+
1 row in set (0.00 sec)
Copy the code

Slow query test

1. Execute the test_index. SQL script to import the test table

2. Execute the following SQL, execute timeout (more than 1 second) we go to the slow query log

SELECT * FROM test_index WHERE
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931';
Copy the code

3. Log contents

Once we have the slow query log, the most important step is to analyze the log. Let’s take a look at what’s actually recorded in the slow log.

The following is one of the SQL records in the slow log. You can see the timestamp, user, query duration, and specific SQL

==> lagou-slow.log <==
# User@Host: root[root] @ [192.168.52.1] Id: 4
# Query_time: 1.681371 Lock_time: 0.000089 Rows_sent: 3 Rows_examined: 5000000
SET timestamp=1604307746;
select * from test_index where
hobby = '20009951' or hobby = '10009931' or hobby = '30009931' LIMIT 0.1000;
# Time: 2020-11-02 T09:02:26. 052231 z
Copy the code