mysql的基础慢日志查询与语句优化_select count(*) as aggregate from `offers` where `-CSDN博客

本文链接：https://blog.csdn.net/weixin_47616219/article/details/116023117

本文介绍了如何查看MySQL的慢查询日志，包括检查慢语句、索引使用情况，通过添加索引显著提高了查询效率。尽管执行时间看似未显著减少，但要注意MySQL的查询缓存机制。调整查询缓存相关参数如query_cache_type、query_cache_size等，能有效优化缓存效果，并提高查询性能。同时，理解并计算命中率对于评估缓存效果至关重要。

摘要由CSDN通过智能技术生成

查看慢日志系统状态

 SHOW VARIABLES LIKE "%slow%";
+-------------------------+------------------------+
| Variable_name            | Value                  |
+-------------------------+------------------------+
| log_slow_admin_statements | OFF                   |
| log_slow_slave_statements | OFF                   |
| slow_launch_time         | 2                      |
| slow_query_log           | ON                    |
| slow_query_log_file       | /var/lib/mysql/data/slow.log |
+-------------------------+------------------------+
5 rows in set (0.02 sec)

查看慢日志时间

mysql> SHOW VARIABLES LIKE "long_query_time";
+----------------+----------+
| Variable_name   | Value    |
+----------------+----------+
| long_query_time | 2.000000 |
+----------------+----------+
1 row in set (0.03 sec)

查看慢语句

mysql> SHOW FULL PROCESSLIST;
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
| Id        | User  | Host            | db    | Command | Time | State            | Info                                                                                            |
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
| 162249371 | ciscn | 192.168.1.4:53908 | ciscn | Query    |    0 | init              | SHOW FULL PROCESSLIST                                                                         |
| 162250930 | ciscn | 192.168.1.4:55479 | ciscn | Sleep    |   13 |                  | NULL                                                                                           |
| 162250931 | ciscn | 192.168.1.4:55480 | ciscn | Sleep    |   84 |                  | NULL                                                                                           |
| 162251225 | ciscn | 192.168.1.4:55808 | ciscn | Execute  |    0 | Sending data      | select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0')                |
| 162251226 | ciscn | 192.168.1.4:55810 | ciscn | Execute  |    1 | Creating sort index | select * from `team_relations` where `team_id` = '8720' and (`is_delete` = '0') order by `id` asc                   |
| 162251229 | ciscn | 192.168.1.4:55813 | ciscn | Execute  |    1 | Sending data      | select count(*) as aggregate from `team_relations` where `status` = '1' and (`team_id` = '9533') and (`is_delete` = '0') |
| 162251238 | ciscn | 192.168.1.4:55823 | ciscn | Sleep    |    1 |                  | NULL                                                                                           |
+-----------+-------+-----------------+-------+----------+------+------------------+------------------------------------------------------------------------------------------------+
7 rows in set (0.04 sec)

查看索引使用情况

mysql> explain select count(*) as aggregate from `notification_user` where `user_id` = '76973' and `status` = '0';
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
| id | select_type | table           | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
|  1 | SIMPLE     | notification_user | ref  | user_id       | user_id | 4       | const |     4 | Using where |
+----+------------+----------------+------+--------------+--------+---------+-------+-------+-------------+
1 row in set (0.04 sec)

可见没有索引的使用

mysql> explain  select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0');
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
|  1 | SIMPLE     | team_relations | ALL | NULL         | NULL | NULL    | NULL | 38723 | Using where |
+----+------------+--------------+------+--------------+------+---------+------+-------+-------------+
1 row in set (0.04 sec)

添加索引

mysql> ALTER TABLE `ciscn`.`team_relations` ADD INDEX `team_id`(`team_id`);

去添加索引后在查看

mysql> explain  select count(*) as aggregate from `team_relations` where `team_id` = '9570' and (`is_delete` = '0');
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
| id | select_type | table         | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
|  1 | SIMPLE     | team_relations | ref  | team_id      | team_id | 4       | const |     4 | Using where |
+----+------------+--------------+------+--------------+---------+---------+-------+-------+-------------+
1 row in set (0.04 sec)

在对条件字段 team_id 添加普通索引后,可见语句执行只扫描了4行.对比优化前的 38723 行扫描,效率提升是非常明显的。

但在结果返回的操作时间都是 0.04 sec 好像又效率不高的错觉。其中mysql 是有语句查询缓存的
查看跟缓存相关的变量

mysql> show global variables like 'query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
5 rows in set (0.02 sec)