sql - 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；使用 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

尝试使用USE INDEX。如果 mysql 决定需要读取表的某个部分，它将诉诸表扫描。使用 USE INDEX，您告诉 mysql 将表扫描视为非常昂贵的。