MySQL慢查询解析

文档课题：MySQL慢查询解析.
数据库：mysql 5.7.21
1、建测试表
1.1、建表结构.
mysql> use booksDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` tinyint(4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2、建存储过程
--运用存储过程insert 100万条数据.
delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_insert;
CREATE PROCEDURE proc_batch_insert()
BEGIN
DECLARE pre_name BIGINT;
DECLARE ageVal INT;
DECLARE i INT;
SET pre_name=1012079728;
SET ageVal=100;
SET i=1;
WHILE i < 1000000 DO
INSERT INTO t_user(name,age,create_time,update_time) VALUES(CONCAT(pre_name,'@qq.com'),(ageVal+i)%30,NOW(),NOW());
SET pre_name=pre_name+100;
SET i=i+1;
END WHILE;
END $$

1.3、执行存储过程
mysql> delimiter ;
mysql> call proc_batch_insert();
Query OK, 1 row affected (34 min 23.34 sec)

1.4、检查表信息
mysql> desc t_user;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| age         | tinyint(4)   | YES  |     | NULL    |                |
| create_time | datetime     | YES  |     | NULL    |                |
| update_time | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select count(*) from t_user;
+----------+
| count(*) |
+----------+
|   999999 |
+----------+
1 row in set (0.15 sec)

2、开启慢查询功能
2.1、开启慢查询
--查慢查询日志是否开启.
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

--开启慢查询日志.
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.01 sec)

2.2、修改慢查询阀值
--查慢查询阀值.
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

--更改阀值.
mysql> set long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)

2.3、慢查询日志位置
mysql> show variables like 'slow_query_log_file';
+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log_file | /mysql/data/mysql-leo-percona-slow.log |
+---------------------+----------------------------------------+
1 row in set (0.00 sec)

3、测试慢查询
3.1、查询语句
mysql> select id,name,age from t_user where name='[email protected]';
+----+-------------------+------+
| id | name              | age  |
+----+-------------------+------+
|  1 | [email protected] |   11 |
+----+-------------------+------+
1 row in set (0.21 sec)

3.2、慢查询日志
# Time: 2023-07-29T09:14:08.312852Z
# User@Host: root[root] @ localhost []  Id:     7
# Schema: booksDB  Last_errno: 0  Killed: 0
# Query_time: 0.216009  Lock_time: 0.000221  Rows_sent: 1  Rows_examined: 999999  Rows_affected: 0
# Bytes_sent: 196
SET timestamp=1690622048;
select id,name,age from t_user where name='[email protected]';

说明：慢查询日志显示sql的执行语句以及执行时间，同时还显示查询的行数.

4、慢查询优化
4.1、查执行计划
mysql> explain select id,name,age from t_user where name='[email protected]';
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996508 |    10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

说明：此处主要检查type列，ALL表示全文检索，所以该条sql响应缓慢.

4.2、添加索引
mysql> alter table t_user add index ind_name(name);
Query OK, 0 rows affected (2.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id,name,age from t_user where name='[email protected]';
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user | NULL       | ref  | ind_name      | ind_name | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

说明：此时执行计划显示已不再走全表扫描.

4.3、查询验证
mysql> select id,name,age from t_user where name='[email protected]';
+----+-------------------+------+
| id | name              | age  |
+----+-------------------+------+
|  1 | [email protected] |   11 |
+----+-------------------+------+
1 row in set (0.00 sec)

说明：如上所示，添加索引后SQL性能得到很大提升.虽然索引能加快查询速度，但也会降低更新表的速度，如INSERT、UPDATE和DELETE，因为更新表时不仅要更新数据，同时需维护索引，而且索引会占用磁盘空间.

参考文档：https://blog.csdn.net/XZB119211/article/details/127651432.