我一直在测试不同的想法，以优化我们工作中系统中的一些表格。今天我遇到了一个表格，它跟踪我们系统中每辆车的每一个 View 。在下面创建表格。

SHOW CREATE TABLE vehicle_view_tracking;

CREATE TABLE `vehicle_view_tracking` (
  `vehicle_view_tracking_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `public_key` varchar(45) NOT NULL,
  `vehicle_id` int(10) unsigned NOT NULL,
  `landing_url` longtext NOT NULL,
  `landing_port` int(11) NOT NULL,
  `http_referrer` longtext,
  `created_on` datetime NOT NULL,
  `created_on_date` date NOT NULL,
  `server_host` longtext,
  `server_uri` longtext,
  `referrer_host` longtext,
  `referrer_uri` longtext,
  PRIMARY KEY (`vehicle_view_tracking_id`),
  KEY `vehicleViewTrackingKeyCreatedIndex` (`public_key`,`created_on_date`),
  KEY `vehicleViewTrackingKeyIndex` (`public_key`)
) ENGINE=InnoDB AUTO_INCREMENT=363439 DEFAULT CHARSET=latin1;

我正在研究多列和单列索引。我运行了以下查询:

EXPLAIN EXTENDED SELECT dealership_vehicles.vehicle_make, dealership_vehicles.vehicle_model, vehicle_view_tracking.referrer_host, count(*) AS count
FROM vehicle_view_tracking
LEFT JOIN dealership_vehicles
ON dealership_vehicles.dealership_vehicle_id = vehicle_view_tracking.vehicle_id
WHERE vehicle_view_tracking.created_on_date >= '2011-09-07' AND vehicle_view_tracking.public_key IN ('ab12c3')
GROUP BY (dealership_vehicles.vehicle_make) ASC , dealership_vehicles.vehicle_model, referrer_host

+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                  | key                                | key_len | ref                                          | rows  | filtered | Extra                                        |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | vehicle_view_tracking | range  | vehicleViewTrackingKeyCreatedIndex,vehicleViewTrackingKeyIndex | vehicleViewTrackingKeyCreatedIndex | 50      | NULL                                         | 23086 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dealership_vehicles   | eq_ref | PRIMARY                                                        | PRIMARY                            | 8       | vehicle_view_tracking.vehicle_id |     1 |   100.00 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+------------------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(实际选择查询的执行时间为 .309 秒)

然后我将 where 子句中的日期从 '2011-09-07' 更改为 '2011-07-07' 并得到以下解释结果

EXPLAIN EXTENDED SELECT dealership_vehicles.vehicle_make, dealership_vehicles.vehicle_model, vehicle_view_tracking.referrer_host, count(*) AS count
FROM vehicle_view_tracking
LEFT JOIN dealership_vehicles
ON dealership_vehicles.dealership_vehicle_id = vehicle_view_tracking.vehicle_id
WHERE vehicle_view_tracking.created_on_date >= '2011-07-07' AND vehicle_view_tracking.public_key IN ('ab12c3')
GROUP BY (dealership_vehicles.vehicle_make) ASC , dealership_vehicles.vehicle_model, referrer_host


+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys                                                  | key                         | key_len | ref                                          | rows  | filtered | Extra                                        |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+
|  1 | SIMPLE      | vehicle_view_tracking | ref    | vehicleViewTrackingKeyCreatedIndex,vehicleViewTrackingKeyIndex | vehicleViewTrackingKeyIndex | 47      | const                                        | 53676 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | dealership_vehicles   | eq_ref | PRIMARY                                                        | PRIMARY                     | 8       | vehicle_view_tracking.vehicle_id |     1 |   100.00 |                                              |
+----+-------------+-----------------------+--------+----------------------------------------------------------------+-----------------------------+---------+----------------------------------------------+-------+----------+----------------------------------------------+

(实际选择查询的执行时间为 .670 秒)

我看到了 4 个主要变化:

1. 类型从 range 变为 ref
2. 键从 vehicleViewTrackingKeyCreatedIndex 更改为 vehicleViewTrackingKeyIndex
3. key_len 从 50 变为 47(由 key 变化引起)
4. 行从 23086 更改为 53676(由键更改引起)

此时，慢速查询的执行时间仅为 0.6 秒，但我们的数据库中只有大约 10% 的车辆。

时间不早了，我可能忽略了 mysql 文档中的某些内容，但我似乎无法找到为什么在 where 子句中更改日期时键(进而是类型和行)发生变化。

非常感谢您的帮助。我搜索了与导致此更改的日期有相同/相似问题的人，但找不到任何内容。如果我错过了之前的帖子，请给我链接 :-)

最佳答案

不同的搜索策略对不同的数据有意义。特别是，索引扫描(例如范围)通常必须进行查找才能实际读取该行。在某些时候，进行所有这些查找比根本不使用索引要慢。

举一个简单的例子，一个包含三列的表:id(主键)、name(索引)、birthday。说它有很多数据。如果您要求 MySQL 查找 Bob 的生日，它可以相当快地完成:首先，它在名称索引中找到 Bob(这需要几次查找，log(n)，其中 n 是行数)，然后再进行一次查找读取数据文件中的实际行并从中读取生日。这非常快，比扫描整个表格要快得多。

下一步，考虑做一个像'Z%'这样的名字。这可能只是表格的一小部分。因此，在名称索引中找到 Z 的起始位置会更快，然后为每个查找数据文件以读取该行。 (这是范围扫描)。

最后，考虑询问所有以 M-Z 开头的名称。这可能是数据的一半左右。它可以进行范围扫描，然后进行很多的搜索，但是以读取一半行为最终目标在数据文件上随机搜索并不是最佳选择:只进行一次搜索会更快大顺序读取数据文件。因此，在这种情况下，索引将被忽略。

这就是您所看到的 — 除了在您的情况下，它还可以依赖另一个键。 (如果没有其他索引，它也可能实际使用日期索引，它应该选择最快的索引。注意 MySQL 的优化器经常在这方面出错。)

所以，简而言之，这是意料之中的。查询不会说明如何 检索数据，而是说明要检索什么 数据。数据库的优化器应该找到检索它的最快方法。

您可能会在两个 列上找到一个索引，在这两种情况下都以 (public_key,created_on_date) 为首选顺序，并加快您的查询速度。这是因为 MySQL 只能在每个表(每个查询)中使用一个索引。此外，日期在最后，因为范围扫描只能在索引的最后一列上有效地完成。

[我相信 InnoDB 实际上还有另一层间接，但这只会混淆重点。这对解释没有影响。]

关于MySQL EXPLAIN 'type' 由 'range' 变为 'ref' where 语句中的日期更改时？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/7670810/