MySQL慢查询分析与索引

Question

最近，我们注意到在缓慢的查询日志中出现了一个特定的查询，需要相当长的时间。我已经尽了我最大的努力去分析它，但是我不知道为什么要花这么长的时间，以及为什么我们建立的索引没有被使用。

下面是查询的简化(即可读性)版本，用于示例：

SELECT processstage.id AS processstage_id, 
  processstage.job_id AS processstage_job_id, 
  processstage.event_id AS processstage_event_id, ...
FROM processstage INNER JOIN jobevent ON jobevent.id = processstage.event_id 
WHERE processstage.due_date <= '2009-10-28 16:07:59' AND (EXISTS (
  SELECT 1 FROM job 
  WHERE jobevent.job_id = job.id AND job.brand_id = 1
)) ORDER BY processstage.due_date;

此外，为了更好地衡量，注意表processstage的大小是很重要的

mysql> SELECT COUNT(id) FROM processstage;
+-----------+
| COUNT(id) |
+-----------+
|    596183 | 
+-----------+

当我在查询上运行EXPLAIN时，我发现processstage表正在读取大量行(请参见“使用where；Using filesort")，因为没有使用索引(我可以知道)：

mysql> EXPLAIN SELECT processstage.id AS processstage_id, processstage.job_id AS processstage_job_id, processstage.event_id AS processstage_event_id     FROM processstage INNER JOIN jobevent ON jobevent.id = processstage.event_id      WHERE processstage.due_date <= '2009-10-28 16:07:59' AND (EXISTS (SELECT 1      FROM job      WHERE jobevent.job_id = job.id AND job.brand_id = 1)) ORDER BY processstage.due_date;
+----+--------------------+--------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+-----------------------------+
| id | select_type        | table        | type   | possible_keys                                     | key     | key_len | ref                          | rows   | Extra                       |
+----+--------------------+--------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+-----------------------------+
|  1 | PRIMARY            | processstage | ALL    | ix_processstage_due_date,processstage_event_id_fk | NULL    | NULL    | NULL                         | 606045 | Using where; Using filesort | 
|  1 | PRIMARY            | jobevent     | eq_ref | PRIMARY                                           | PRIMARY | 4       | processstage.event_id |      1 | Using where                 | 
|  2 | DEPENDENT SUBQUERY | job          | eq_ref | PRIMARY,ix_job_brand_id                           | PRIMARY | 4       | jobevent.job_id       |      1 | Using where                 | 
+----+--------------------+--------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

奇怪的是，我们在查询的WHERE子句中使用了列的索引：

mysql> SHOW INDEXES FROM processstage;
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| processstage |          0 | PRIMARY                    |            1 | id          | A         |      614150 |     NULL | NULL   |      | BTREE      |         | 
| processstage |          1 | ix_processstage_job_id     |            1 | job_id      | A         |       47242 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_stop_date  |            1 | stop_date   | A         |      614150 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_order      |            1 | order       | A         |          16 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_start_date |            1 | start_date  | A         |      122830 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_milestone  |            1 | milestone   | A         |       12794 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_due_date   |            1 | due_date    | A         |       51179 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | ix_processstage_process_id |            1 | process_id  | A         |       76768 |     NULL | NULL   | YES  | BTREE      |         | 
| processstage |          1 | processstage_event_id_fk   |            1 | event_id    | A         |        3722 |     NULL | NULL   | YES  | BTREE      |         | 
+--------------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

对于为什么我们的指数看起来不被使用，有什么想法吗？也许我错过了一些很明显的东西，或者是完全错误地对待这件事。任何意见或建议都是非常感谢的！

Answer 1

你得到了多少个job.brand = 1的地方？如果这是一个合理的数量，您可以尝试以下查询来缓解一些联接和子查询。

第一个查询：

select distinct jobevent.id from jobevent
inner join job on job.id = jobevent.job_id
where job.brand = 1

紧接着是

select processstage.id as processstage_id, 
  processstage.job_id as processstage_job_id, 
  processstage.event_id as processstage_event_id, ...
from processstage 
where processstage.due_date <= '2009-10-28 16:07:59' and 
processstage.event_id in (list of event ids from the previous query) 
order by processstage.due_date;

以前的想法：

您是否尝试过对作业执行常规连接，而不是对从属子查询进行连接？类似于：

SELECT processstage.id AS processstage_id, 
  processstage.job_id AS processstage_job_id, 
  processstage.event_id AS processstage_event_id, ...
FROM processstage 
INNER JOIN jobevent ON jobevent.id = processstage.event_id 
INNER JOIN job ON job.id = jobevent.job_id 
WHERE processstage.due_date <= '2009-10-28 16:07:59' AND 
job.brand = 1 
ORDER BY processstage.due_date;

你有没有工作的工作事件吗？

Answer 2

尝试使用使用索引。如果mysql决定需要读取表中的某一部分，则需要进行表格扫描。通过使用索引，您可以告诉mysql考虑非常昂贵的表扫描。

Answer 3

一些想法：

• 从字符串'2009-10-28 16:07:59‘到日期进行显式类型转换。现在可能发生的情况是，您的processstage.due_date在比较之前被转换为字符串。进行类型转换(不确定MySQL中的系统，但应该类似于CAST (<your date string> as DATE) )将帮助优化器确定使用索引。
• 有多少条记录满足日期条件？如果大多数表符合条件，或者索引不是很有选择性，那么使用索引可能没有任何好处。