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

因为“所有者”上有索引

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, 'SIMPLE', 'city_matrix', '', 'ref', 'ownerIndex', 'ownerIndex', '4', 'const', 169724, 100.00, ''

Answer 1

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

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

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

如果是这种情况:

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

MySQL 仍然可以过滤掉所有者的所有记录，但需要一些时间来重新排列所有 city1_id 数据，以便您收到排序结果。由于这需要时间，因此在此期间 show processlist 可能会向您显示查询正在对数据进行重新排序。

为了帮助 MySQL 更快地完成工作，我们可以使用称为覆盖索引的东西。覆盖索引包含查询中使用的所有字段，因此 MySQL 只需读取索引即可获取数据，而无需接触基础表。 Owner 和 city1_id 的复合索引将帮助 MySQL 使用单个索引来过滤数据，并再次使用同一索引对数据进行排序，然后对其进行计数。

那么，让我们创建覆盖索引:

create index idx_city_matrix_city1_owner on city_matrix(owner, city1_id)

正如您所注意到的，MySQL 花了一些时间来创建索引，一旦索引准备就绪，它就可以很快地浏览数据以提供计数。

编辑:重要的是要注意，当您像有关 do 的语句那样执行 count(*) 时，不需要排序。结果集是标量——只有一个值。按任何字段排序不会影响您的计数。例如，计算 table 上的所有水果将得到与按大小排序的 table 上的所有水果相同的结果。