美文网首页
记一次 Mysql 慢查询分析

记一次 Mysql 慢查询分析

作者: A文艺钦年 | 来源:发表于2018-04-27 16:53 被阅读22次

背景介绍

公司后台有一个可以分页查询用户的搜索记录的功能，正常的页面是这样


image.png

但是现在一访问直接504

从我知道这个页面功能，至少过去两个月也没有人去修复这张页面（主要因为这页面没人用），正好我做到这块业务，想着顺手把这个问题修复一下。

首先，那块的代码我也没看过，不过我觉得是慢查询问题。

解决思路：

1.先从代码上解决，看看是否存在什么恶心的代码（比如循环查询）。
2.优化数据库查询。

查看代码

代码很短，基本上按照我们的开发规范开发的，按照时间倒序分页查询，但是我总觉得有一点不对劲，但是又说不出来，先不管。

$conditions = $this->getWordsConditions($request->query->all());
    $paginator = new Paginator(
    $request,
    $this->getSearchService()->searchWordsCount($conditions),
    $conditions['num']
  );

$words = $this->getSearchService()->searchWords(
      $conditions,
       array('createdTime' => 'DESC', 'updatedTime' => 'DESC'),
       $paginator->getOffsetCount(),
       $paginator->getPerPageCount()
 );

$users = $this->getLocalUserService()->findUsersByIds(ArrayToolkit::column($words, 'outId'));

return $app['twig']->render('search/words-list.html.twig', array(
    'paginator' => $paginator,
      'users' => $users,
      'words' => $words,
));

查看查询

先看看 mysql 是否开启了慢查询日志

SHOW GLOBAL VARIABLES LIKE '%log%'

所有与log相关的配置都能看得到，不过我们只需关心
slow_query_log 是否开启慢查询日志
slow_query_log_file 慢查询日志位置

我这里已经开启了

| slow_query_log                          | ON                                  |
| slow_query_log_file                     | /var/log/mysql/mysql-slow.log       |

跟踪日志

tail -f /var/log/mysql/mysql-slow.log

下面是结果

# Time: 180427 15:51:30
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 6653937
# Query_time: 3.604676  Lock_time: 0.000055 Rows_sent: 1  Rows_examined: 14837062
SET timestamp=1524815490;
SELECT COUNT(id) FROM search_words search_words;

# Time: 180427 15:54:03
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 6653972
# Query_time: 153.277001  Lock_time: 0.000104 Rows_sent: 20  Rows_examined: 14837082
SET timestamp=1524815643;
SELECT * FROM search_words search_words ORDER BY createdTime DESC, updatedTime DESC LIMIT 20 OFFSET 0;

第一条 query 是查询 总数，用了3.6秒
第二条 query 是查询 记录，用了153秒

分析 sql

优化的第一条准则就是抓大头，所以我们先分析第二条sql

mysql> explain SELECT * FROM search_words search_words ORDER BY createdTime DESC, updatedTime DESC LIMIT 20 OFFSET 0;
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows     | Extra          |
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------+
|  1 | SIMPLE      | search_words | ALL  | NULL          | NULL | NULL    | NULL | 15236068 | Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------+
1 row in set (0.00 sec)

各列的含义如下:

id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type: SELECT 查询的类型.

table: 查询的是哪个表

partitions: 匹配的分区

type: join 类型

possible_keys: 此次查询中可能选用的索引

key: 此次查询中确切使用到的索引.

ref: 哪个字段或常数与 key 一起被使用

rows: 显示此查询一共扫描了多少行. 这个是一个估计值.

filtered: 表示此查询条件所过滤的数据的百分比

extra: 额外的信息

具体的 sql 分析可以查看MySQL 性能优化神器 Explain 使用分析
这里不做过多介绍

问题显而易见，这条 sql 实际上是全表扫描（一千五百万条记录），而且还有一个 order by操作。。。

这张表的索引

mysql> show index from search_words;
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| search_words |          0 | PRIMARY     |            1 | id          | A         |    15236181 |     NULL | NULL   |      | BTREE      |         |               |
| search_words |          1 | outId       |            1 | outId       | A         |       24977 |     NULL | NULL   |      | BTREE      |         |               |
| search_words |          1 | words       |            1 | words       | A         |     5078727 |     NULL | NULL   |      | BTREE      |         |               |
| search_words |          1 | createdTime |            1 | createdTime | A         |    15236181 |     NULL | NULL   |      | BTREE      |         |               |
| search_words |          1 | isLegal     |            1 | isLegal     | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

虽然 createdTime 加了索引， 但是根本没用上。所以这里我们可以得出一个结论：order by + limit 是不会使用索引的

如何解决？

好了，问题找到了，但是我们应该如何解决呢？？

其实这个问题可以归结为 千万级别数据下的分页查询问题

如果你遇到了这个问题，你应该思考的第一个问题是：
这个需求合理吗？
这个需求合理吗？
这个需求合理吗？

对，在我这个项目里就是一个伪需求（一千多万条记录，看得过来吗？？），所以这里我打算改成查询最近七天的记录
每天差不多产生 1万多条的搜索记录，即使是七天的记录也有近十万条了。

修改之后

mysql> SELECT * FROM search_words search_words  where createdTime >=1524153600 and createdTime<1524817793 ORDER BY createdTime DESC, updatedTime DESC LIMIT 20 OFFSET 0;
+----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
| id       | outId | userId | words                                     | ip             | status | isLegal | isHit | updatedTime | createdTime |
+----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
| 19637024 |  7325 |      0 | ??                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817791 |  1524817791 |
| 19637025 |  7325 |      0 | ruby                                      | 59.110.167.99  |      0 | legal   |     0 |  1524817791 |  1524817791 |
| 19637022 |    42 |      0 | unity                                     | 106.15.200.131 |      0 | legal   |     1 |  1524817790 |  1524817790 |
| 19637023 | 14140 |      0 | http:www.tudou.comprogramsviewa6w0aokof3q | 106.15.199.78  |      0 | legal   |     0 |  1524817790 |  1524817790 |
| 19637021 |  7849 |      0 | ?                                         | 59.110.167.99  |      0 | legal   |     1 |  1524817783 |  1524817783 |
| 19637020 | 28895 |      0 | ???                                       | 59.110.167.99  |      0 | legal   |     1 |  1524817781 |  1524817781 |
| 19637018 |  8422 |      0 | 11                                        | 106.15.200.131 |      0 | legal   |     1 |  1524817777 |  1524817777 |
| 19637019 |   110 |      0 | ??                                        | 106.15.200.131 |      0 | legal   |     1 |  1524817777 |  1524817777 |
| 19637017 | 51015 |      0 | ????                                      | 59.110.167.99  |      0 | legal   |     1 |  1524817773 |  1524817773 |
| 19637016 | 38353 |      0 | MongoDB                                   | 59.110.167.99  |      0 | legal   |     1 |  1524817771 |  1524817771 |
| 19637015 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817767 |  1524817767 |
| 19637014 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817764 |  1524817764 |
| 19637012 | 14140 |      0 | ????                                      | 106.15.200.131 |      0 | legal   |     1 |  1524817760 |  1524817760 |
| 19637013 | 38353 |      0 | Maven                                     | 59.110.167.99  |      0 | legal   |     1 |  1524817760 |  1524817760 |
| 19637011 | 10441 |      0 | verdi ????                                | 59.110.167.99  |      0 | legal   |     1 |  1524817759 |  1524817759 |
| 19637010 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817758 |  1524817758 |
| 19637009 |  7849 |      0 | ??vip                                     | 59.110.167.99  |      0 | legal   |     1 |  1524817757 |  1524817757 |
| 19637008 |    42 |      0 | C++                                       | 106.15.200.131 |      0 | legal   |     1 |  1524817747 |  1524817747 |
| 19637006 | 38353 |      0 | java ???                                  | 59.110.167.99  |      0 | legal   |     1 |  1524817738 |  1524817738 |
| 19637007 |    42 |      0 | C++                                       | 106.15.200.131 |      0 | legal   |     1 |  1524817738 |  1524817738 |
+----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
20 rows in set (0.52 sec)

已经减少了0.52 秒了，提升了近300倍！！
这里我看到了又一个根据 updatedTime 排序，很明显我觉得这个是没必要的，去之。

mysql> SELECT * FROM search_words search_words  where createdTime >=1524153600 and createdTime<1524817793 ORDER BY createdTime DESC LIMIT 20 OFFSET 0;        +----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
| id       | outId | userId | words                                     | ip             | status | isLegal | isHit | updatedTime | createdTime |
+----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
| 19637025 |  7325 |      0 | ruby                                      | 59.110.167.99  |      0 | legal   |     0 |  1524817791 |  1524817791 |
| 19637024 |  7325 |      0 | ??                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817791 |  1524817791 |
| 19637023 | 14140 |      0 | http:www.tudou.comprogramsviewa6w0aokof3q | 106.15.199.78  |      0 | legal   |     0 |  1524817790 |  1524817790 |
| 19637022 |    42 |      0 | unity                                     | 106.15.200.131 |      0 | legal   |     1 |  1524817790 |  1524817790 |
| 19637021 |  7849 |      0 | ?                                         | 59.110.167.99  |      0 | legal   |     1 |  1524817783 |  1524817783 |
| 19637020 | 28895 |      0 | ???                                       | 59.110.167.99  |      0 | legal   |     1 |  1524817781 |  1524817781 |
| 19637019 |   110 |      0 | ??                                        | 106.15.200.131 |      0 | legal   |     1 |  1524817777 |  1524817777 |
| 19637018 |  8422 |      0 | 11                                        | 106.15.200.131 |      0 | legal   |     1 |  1524817777 |  1524817777 |
| 19637017 | 51015 |      0 | ????                                      | 59.110.167.99  |      0 | legal   |     1 |  1524817773 |  1524817773 |
| 19637016 | 38353 |      0 | MongoDB                                   | 59.110.167.99  |      0 | legal   |     1 |  1524817771 |  1524817771 |
| 19637015 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817767 |  1524817767 |
| 19637014 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817764 |  1524817764 |
| 19637013 | 38353 |      0 | Maven                                     | 59.110.167.99  |      0 | legal   |     1 |  1524817760 |  1524817760 |
| 19637012 | 14140 |      0 | ????                                      | 106.15.200.131 |      0 | legal   |     1 |  1524817760 |  1524817760 |
| 19637011 | 10441 |      0 | verdi ????                                | 59.110.167.99  |      0 | legal   |     1 |  1524817759 |  1524817759 |
| 19637010 | 28895 |      0 | C#                                        | 59.110.167.99  |      0 | legal   |     1 |  1524817758 |  1524817758 |
| 19637009 |  7849 |      0 | ??vip                                     | 59.110.167.99  |      0 | legal   |     1 |  1524817757 |  1524817757 |
| 19637008 |    42 |      0 | C++                                       | 106.15.200.131 |      0 | legal   |     1 |  1524817747 |  1524817747 |
| 19637007 |    42 |      0 | C++                                       | 106.15.200.131 |      0 | legal   |     1 |  1524817738 |  1524817738 |
| 19637006 | 38353 |      0 | java ???                                  | 59.110.167.99  |      0 | legal   |     1 |  1524817738 |  1524817738 |
+----------+-------+--------+-------------------------------------------+----------------+--------+---------+-------+-------------+-------------+
20 rows in set (0.00 sec)

可以看出来现在基本上没有损耗了！！

结论

当然上面那个问题，就没有一种既能实现需求又保证效率的方案了吗？
答案是肯定有的，一种方案就是分表，根据算法算出页数对应的表。但是你这样做真的值得吗？
解决问题不要单单思考技术方面，还要考虑需求是否合理，即使合理，也要考虑成本与收益。
不要做一个只会实现需求的机器人。

PS：即使我这里将需求改成了查看最近7天的搜索记录，我仍然觉得这个需求不合理，因为这张表页，这么久了也没有人去关注，所以可见这个需求的价值很低很低。

相关文章

网友评论

      本文标题：记一次 Mysql 慢查询分析

      本文链接：https://www.haomeiwen.com/subject/stablftx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|记一次 Mysql 慢查询分析|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！