Mysql - where子句比完全扫描慢

Question

查询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)

我不明白为什么查询 B 需要 20 秒而查询 A 需要 0 秒。 “状态”索引。生产和开发的结果相同。服务器。

Answer 1

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

据我所知，您没有任何关于 id 的索引

ANSI standard (查找“Scalar expressions 125”)声明 COUNT(*) 给出表的行数:它旨在从一开始就进行优化。

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

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

什么是聚簇索引，什么是PK？你有两者(即使索引相同)，对吧？