Mysql - where 子句比全扫描慢 [英] Mysql - where clause slower than full scan

问题描述

查询 A

Select id from jobs;

| 55966 |
| 55971 |
+-------+
10705 rows in set (0.00 sec)

查询 B

Select id from jobs where status = 0;
| 55966 |
| 55971 |
+-------+
7933 rows in set (**20.22 sec**)

有一个状态索引.

mysql> explain select id from jobs where status = 0;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | status        | NULL | NULL    | NULL | 10705 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)


mysql> show profile for query 1;
+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000023 |
| checking query cache for query |  0.000039 |
| checking permissions           |  0.000006 |
| Opening tables                 |  0.000008 |
| System lock                    |  0.000004 |
| Table lock                     |  0.000016 |
| init                           |  0.000021 |
| optimizing                     |  0.000007 |
| statistics                     |  0.000904 |
| preparing                      |  0.000023 |
| executing                      |  0.000003 |
| Sending data                   | 19.751547 |
| end                            |  0.000009 |
| query end                      |  0.000002 |
| freeing items                  |  0.001561 |
| storing result in query cache  |  0.000122 |
| logging slow query             |  0.000002 |
| logging slow query             |  0.000002 |
| cleaning up                    |  0.000003 |
+--------------------------------+-----------+

mysql> show index from jobs;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| jobs  |          1 | status   |            1 | status      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| jobs  |          1 | date     |            1 | dateinit    | A         |        1784 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
10 rows in set (0.02 sec)

我不明白为什么 Query B 需要 20 秒而 Query A 需要 0 .状态"索引.prod 和 dev 上的结果相同.服务器.

I dont understand why Query B takes 20 seconds while Query A takes 0 . Index on "status". Same result on prod and dev. server.

推荐答案

尝试将索引更改为两列 (status, id)

Try changing the index to both columns (status, id)

据我所知，您在 id 上没有任何索引

As I see it, you don't have any index on id

ANSI 标准(查找标量表达式 125") 声明 COUNT(*) 给出表的行数:它旨在从一开始就进行优化.

The ANSI standard (look for "Scalar expressions 125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

如果指定了 COUNT(*)，则结果为 T 的基数.

If COUNT(*) is specified, then the result is the cardinality of T.

这就是 COUNT(*) 比 COUNT(id) 快得多的原因.COUNT(*) 可以使用 status 索引.COUNT(id) 不会使用这个索引并且没有其他有效索引

This is why COUNT(*) is far quicker then COUNT(id). COUNT(*) can use the status index. COUNT(id) won't use this index and there is no other valid index

什么是聚集索引，什么是PK?你两者(即使索引相同)，对吗?

What is the clustered index, and what is the PK? You have both (even if the same index), right?

这篇关于Mysql - where 子句比全扫描慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！