记一次因线上mysql优化器误判引起慢查询事件

下面我会详细讲解“记一次因线上MySQL优化器误判引起慢查询事件”的完整攻略。

背景

在进行线上MySQL性能优化的过程中，经常会遇到一些奇怪的问题，比如出现奇怪的慢查询，尤其是当使用了ORM框架之后，更容易出现这些问题。在本次攻略中，我们将讨论一次因线上MySQL优化器误判引起的慢查询事件。

问题描述

某天，我们的应用开始出现了一些慢查询，比如说从一个表中查询10条记录，需要10秒钟，而在同样的环境下，同样的查询只需要1秒钟左右，因此我们需要找到这个问题的根本原因，解决掉这个问题。

解决过程

1. 首先，我们需要排除环境问题，比如说网络或者硬件问题，因此我们抓取了一些慢查询的日志，并进行了分析，发现这些日志中的查询都是从一个特定的表中查询数据，并且这个表只有一些简单的字段。

2. 我们进一步分析了这个表的结构、索引情况等信息，发现这个表有一个联合索引，可以用于支持我们的查询，因此我们决定尝试使用这个联合索引来查询数据。但是，在使用这个联合索引之后，我们发现查询时间并没有得到改善，速度还是很慢。

3. 接着，我们进一步分析了问题所在，发现这个问题是由于MySQL优化器误判引起的。我们的联合索引中的字段顺序是有问题的，导致优化器认为这个索引不能用于这个查询，并且使用了一个更差的索引来进行查询，从而导致了性能问题。

4. 我们经过一些试验，调整了联合索引中的字段顺序，使得优化器可以正确地使用这个索引进行查询，性能问题得到了改善，并且查询时间由10秒钟降低到1秒钟以内。

示例：

示例1

我们有一个表user，包含以下字段：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` tinyint(4) DEFAULT '0',
  `email` varchar(50) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

我们需要查询一个年龄在25-30岁之间的名字为“Tom”的用户，我们可以使用以下语句进行查询：

SELECT * FROM `user` WHERE `age` >= 25 AND `age` <= 30 AND `name` = 'Tom';

但是，这个查询速度非常慢，需要10秒钟才能返回结果。我们可以查看一下这个查询的执行计划：

EXPLAIN SELECT * FROM `user` WHERE `age` >= 25 AND `age` <= 30 AND `name` = 'Tom';

得到的输出结果如下：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 1124 |     1.25 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

我们发现MySQL选择了一个不太合适的索引进行查询，通过一些调整，我们可以正确地让MySQL选择更好的索引，提升查询速度。

示例2

我们有一个表order，包含以下字段：

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

我们需要查询一个用户在过去一个月内购买了某个产品，并且这个订单的状态为成功的记录，我们可以使用以下语句进行查询：

SELECT * FROM `order` WHERE `user_id` = 1000 AND `status` = 2 AND `product_id` = 100 AND `created_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY);

但是，这个查询速度非常慢，需要10秒钟才能返回结果。我们可以查看一下这个查询的执行计划：

EXPLAIN SELECT * FROM `order` WHERE `user_id` = 1000 AND `status` = 2 AND `product_id` = 100 AND `created_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY);

得到的输出结果如下：

+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | order | NULL       | ref  | idx_user_id,idx_idx | idx_user_id | 4     | const |  180 |    20.97 | Using index |
+----+-------------+-------+------------+------+---------------------+------+---------+------+------+----------+-------------+

我们发现MySQL选择了一个不太合适的索引进行查询，通过一些调整，我们可以正确地让MySQL选择更好的索引，提升查询速度。

总结

在这个攻略中，我们讨论了一次因线上MySQL优化器误判引起的慢查询事件。通过分析问题，我们发现出现问题的根本原因是MySQL优化器认为我们使用的索引不适用，选择了一个更差的索引进行查询。通过调整索引中的字段顺序，我们成功地解决了这个问题，提升了查询速度。