MySQL索引使用查询优化

Question

我有以下 MySQL (MyISAM) 表，其中包含大约 300 万行。

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node` smallint(6) NOT NULL,
  `pid` int(11) NOT NULL,
  `job` int(11) NOT NULL,
  `a_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `state` int(11) NOT NULL,
  `start_time` int(11) NOT NULL,
  `end_time` int(11) NOT NULL,
  `stop_time` int(11) NOT NULL,
  `end_stream` int(11) NOT NULL,
  `message` varchar(255) DEFAULT NULL,
  `rate` float NOT NULL,
  `exiting` int(11) NOT NULL DEFAULT '0',
  `bytes` int(11) NOT NULL,
  `motion` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `a_id` (`a_id`),
  KEY `job` (`job`),
  KEY `state` (`state`),
  KEY `end_time` (`end_time`),
  KEY `start_time` (`start_time`),
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;

现在，当我运行以下查询时，MySQL 仅使用 a_id 索引并且需要扫描几千行。

SELECT count(id) AS tries FROM `tasks` WHERE ( job='1' OR job='3' ) 
AND a_id='614' AND state >'80' AND state < '100' AND start_time >='1386538013';

当我添加一个额外的索引KEY newkey (a_id,state,start_time)时，MySQL仍然尝试仅使用 a_id 而不是 newkey。只有在查询中使用提示/强制索引时，它才会被使用。更改查询中的字段没有帮助。

有什么想法吗？我不一定希望在我的陈述中得到提示。 MySQL 没有自动执行此操作的事实向我表明我的表、键或某处查询存在问题。非常感谢任何帮助。

附加信息:

mysql> show index in tasks;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tasks |          0 | PRIMARY   |            1 | id          | A         |     3130554 |     NULL | NULL   |      | BTREE      |         |               |
| tasks |          1 | a_id      |            1 | a_id        | A         |        2992 |     NULL | NULL   | YES  | BTREE      |         |               |
| tasks |          1 | job       |            1 | job         | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| tasks |          1 | state     |            1 | state       | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| tasks |          1 | end_time  |            1 | end_time    | A         |     1565277 |     NULL | NULL   |      | BTREE      |         |               |
| tasks |          1 | newkey    |            1 | a_id        | A         |        2992 |     NULL | NULL   | YES  | BTREE      |         |               |
| tasks |          1 | newkey    |            2 | state       | A         |        8506 |     NULL | NULL   |      | BTREE      |         |               |
| tasks |          1 | newkey    |            3 | start_time  | A         |     3130554 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

带引号和不带引号的解释:

mysql> DESCRIBE SELECT count(id) AS tries FROM `tasks` WHERE ( job='1' OR job='3' )  AND a_id='614' AND state >'80' AND state < '100' AND start_time >='1386538013';
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys              | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tasks | ref  | a_id,job,state,newkey      | a_id      | 5       | const |  740 | Using where |
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
1 row in set (0.10 sec)

mysql> DESCRIBE SELECT count(id) AS tries FROM `tasks` WHERE ( job=1 OR job=3 )  AND a_id = 614 AND state > 80 AND state < 100 AND start_time >= 1386538013;
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys              | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | tasks | ref  | a_id,job,state,newkey      | a_id      | 5       | const |  740 | Using where |
+----+-------------+-------+------+----------------------------+-----------+---------+-------+------+-------------+
1 row in set (0.01 sec)

Answer 1

一些事情......我会在上面有一个单一的复合索引(a_id, job, state, start_time)

这有助于优化所有条件的查询，我认为这是最佳调整顺序。一个“A_ID”，然后是两个作业，一个小的状态范围，然后是基于时间的。接下来，请注意没有引号...您似乎正在将数字转换为字符串比较，将它们保留为数字以进行比较——比字符串更快。

此外，通过将它们全部作为索引的一部分，它是一个 COVERING 索引，这意味着它不必转到原始页面数据来获取其他值来测试是否包含符合条件的记录。

SELECT 
      count(*) AS tries 
   FROM 
      tasks
   WHERE 
          a_id = 614
      AND job IN ( 1, 3 ) 
      AND state > 80 AND state < 100 
      AND start_time >= 1386538013;

现在，索引的原因……考虑以下场景。你有两个有盒子的房间......在第一个房间里，每个盒子都是一个“a_id”，其中是按顺序排列的作业，每个作业中是状态范围，最后是开始时间。

在另一个房间里，你的盒子按开始时间排序，在那个 a_id 中排序，最后是状态。

哪个更容易找到您需要的东西。这就是您应该如何看待索引。我宁愿为“A_ID = 614”转到一个框，然后跳转到作业 1 和另一个作业 3。在每个作业 1、作业 3 中，抓取 80-100，然后是时间。但是，您更了解每个标准考虑因素中的数据和数量，并且可以进行调整。

最后，count(ID) 与 count(*)。我只关心一个合格的记录。我不需要知道实际的 ID，因为过滤条件已经符合包含或不包含的条件，为什么要查找(在这种情况下)实际的“ID”。