Mysql，在我的矩阵中，不使用索引。

Question

鉴于这一表：

CREATE TABLE  `matrix` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city1_id` int(10) unsigned NOT NULL DEFAULT '0',
  `city2_id` int(10) unsigned NOT NULL DEFAULT '0',
  `timeinmin` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `distancem` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `OWNER` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `city12_index` (`city1_id`,`city2_id`),
  UNIQUE KEY `city21_index` (`city2_id`,`city1_id`),
  KEY `city1_index` (`city1_id`),
  KEY `city2_index` (`city2_id`),
  KEY `ownerIndex` (`OWNER`),
  CONSTRAINT `PK_city_city1` FOREIGN KEY (`city1_id`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `PK_city_city2` FOREIGN KEY (`city2_id`) REFERENCES `city` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5118409 DEFAULT CHARSET=utf8;

有非常大量的数据。

这个SQL运行得非常快：

select count(*) from city_matrix where owner=1

因为"owner“上有索引

select count(*) from city_matrix where owner=1 order by id

这也运行得很快。但这一点：

select count(*) from city_matrix where owner=1 order by city1_id

需要几秒钟，但是city1_id上也有索引！

解释说明如下：

1、“简单”、“城市矩阵”、“”、“参考”、“所有者索引”、“所有者索引”、“4”、“康斯特”、169724、100.00、

Answer 1

这是个很好的问题。MySQL根据许多不同的情况确定正确的索引。其主要目标是寻找最适合快速检索数据的索引。

select count(*) from city_matrix where owner=1 order by id

在此查询中，MySQL确定where owner=1将结果减少到足够小的数目，因此按ID排序相对容易。例如，如果ID也是一个键(主/唯一/索引)(我怀疑是这样)，MySQL可以利用ID进行排序。

如果发生这种情况：

select count(*) from city_matrix where owner=1 order by city1_id

MySQL仍然可以过滤掉所有者的所有记录，但是需要时间对所有city1_id数据进行洗牌，以便接收排序结果。因为这需要时间，所以在这段时间内，show processlist可以向您显示查询正在重新排序数据。

为了帮助MySQL更快地完成这项工作，我们可以使用称为covering index的东西。覆盖索引包含查询中使用的所有字段，因此MySQL只需读取索引即可获得数据，而不必触摸底层表。所有者和city1_id上的复合索引将帮助MySQL使用一个索引过滤数据，而该索引将再次对数据进行排序，然后对其进行计数。

因此，让我们创建覆盖索引：

create index idx_city_matrix_city1_owner on city_matrix(owner, city1_id)

正如您注意到的，MySQL花了一些时间来创建索引，一旦索引准备就绪，它就可以非常快地在数据中进行压缩，从而给出计数。

编辑:重要的是要注意，当你像关于do的语句一样计数(*)时，你不需要排序。结果集是标量--只有一个值。按任何字段排序不会影响您的计数。例如，计数表上的所有水果将给出与按其大小排序的表上所有水果数相同的结果。

Answer 2

检索和索引应用程序如下：

MySQL为密钥所有者检索的中间结果“存储”在临时表中(取决于结果的大小，可以存储在内存中或磁盘上)。

基于中间结果的直方图数据，可以应用一种索引。如果数据不够惟一，索引可能会被丢弃为无用的索引(例如:在169 k结果中只有5个城市)。

围绕着工作

• 用提示应用索引:这被认为是很差的，因为它可能导致不必要的索引使用，加速一个查询，减慢下一个查询(是的，索引可以使查询更慢)；
• 创建一个包含所有者和city1_id的多列索引。

最后一句话

order by在COUNT(*)上总是会减慢一切，因为order by不会改变任何结果。