sql - 我的 mysql 索引没有任何作用

Question

我有以下查询:

mysql> explain SELECT Exhibition.venue_id, Exhibition.name, Exhibition.slug, Venue.name, Venue.slug, Venue.location_id, Venue.id, Exhibition.id FROM exhibitions AS Exhibition LEFT JOIN venues AS Venue ON (Exhibition.venue_id = Venue.id) LEFT JOIN temperatures AS Temperature ON (Temperature.ref = Exhibition.id) WHERE Exhibition.active = '1' AND Exhibition.ends <= CURDATE() ORDER BY Temperature.temperature DESC LIMIT 5;
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Exhibition  | ALL    | NULL          | NULL    | NULL    | NULL                     | 1530 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Venue       | eq_ref | PRIMARY       | PRIMARY | 108     | narb.Exhibition.venue_id |    1 |                                              | 
|  1 | SIMPLE      | Temperature | ALL    | NULL          | NULL    | NULL    | NULL                     | 2649 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

现在由于在 (Exhibition.venue_id, Exhibition.active, Exhibition.ends) 和 Temperature.ref 上有一个索引，我希望第一个和最后一个连接能够使用该索引。如您所见，事实并非如此。

起初我虽然它必须与计算日期有关，但即使没有日期过滤器也不会使用索引。我尝试了几种索引变体，但我没有取得更多进展。我在这里缺少什么？

相关的表索引是:

ALTER TABLE exhibitions ADD INDEX(slug);
ALTER TABLE exhibitions ADD INDEX(venue_id, ends);

ALTER TABLE temperatures ADD INDEX(ref);

并且，根据要求，Exhibition 也创建表查询:

CREATE TABLE `exhibitions` (
  `id` char(36) NOT NULL,
  `clue` char(6) NOT NULL DEFAULT '',
  `venue_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text,
  `starts` date DEFAULT NULL,
  `ends` date DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `user_id` char(36) NOT NULL,
  `featured` tinyint(1) NOT NULL DEFAULT '0',
  `permanent` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `cidn` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `slug` (`slug`),
  KEY `cidn` (`cidn`),
  KEY `venue_id` (`venue_id`,`ends`),
  KEY `venue_id2` (`venue_id`),
  FULLTEXT KEY `name` (`name`,`description`,`url`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

添加统计信息:

mysql> SELECT  *
-> FROM    information_schema.statistics
-> WHERE   UPPER(table_name) = UPPER('temperatures')
->         AND UPPER(column_name) = UPPER('ref');
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME   | NON_UNIQUE | INDEX_SCHEMA | INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | COLLATION | CARDINALITY | SUB_PART | PACKED | NULLABLE | INDEX_TYPE | COMMENT |
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
| NULL          | narb         | temperatures |          0 | narb         | unique_ref |            1 | ref         | A         |        2655 |     NULL | NULL   |          | BTREE      |         | 
| NULL          | narb         | temperatures |          1 | narb         | ref        |            1 | ref         | A         |        2655 |     NULL | NULL   |          | BTREE      |         | 
+---------------+--------------+--------------+------------+--------------+------------+--------------+-------------+-----------+-------------+----------+--------+----------+------------+---------+
2 rows in set (0.02 sec)

根据 Quassnoi 的建议更新了结果:

mysql> EXPLAIN
-> SELECT  Exhibition.venue_id, Exhibition.name, Exhibition.slug,
->         Venue.name, Venue.slug, Venue.location_id,
->         Venue.id, Exhibition.id
-> FROM    exhibitions AS Exhibition
-> LEFT JOIN
->         venues AS Venue
-> ON      Venue.id = Exhibition.venue_id
-> LEFT JOIN
->         temperatures AS Temperature FORCE INDEX (unique_ref)
-> ON      Temperature.ref = Exhibition.id
-> WHERE   Exhibition.active = '1'
->         AND Exhibition.ends <= CURDATE()
-> ORDER BY
->         Temperature.temperature DESC
-> LIMIT 5;
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                      | rows | Extra                                        |
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Exhibition  | ALL    | NULL          | NULL    | NULL    | NULL                     | 1536 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Venue       | eq_ref | PRIMARY       | PRIMARY | 108     | narb.Exhibition.venue_id |    1 |                                              | 
|  1 | SIMPLE      | Temperature | ALL    | NULL          | NULL    | NULL    | NULL                     | 2662 |                                              | 
+----+-------------+-------------+--------+---------------+---------+---------+--------------------------+------+----------------------------------------------+
3 rows in set (0.00 sec)

添加了温度创建表:

CREATE TABLE `temperatures` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `ref` char(36) NOT NULL,
  `views` int(10) unsigned NOT NULL,
  `ratings` int(10) unsigned NOT NULL,
  `comments` int(10) unsigned NOT NULL,
  `bookmarks` int(10) unsigned NOT NULL,
  `tags` int(10) unsigned NOT NULL,
  `collected` int(10) unsigned NOT NULL default '0',
  `trips` int(10) unsigned NOT NULL,
  `fans` int(10) unsigned NOT NULL,
  `temperature` int(10) NOT NULL default '1000',
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_ref` (`ref`),
  KEY `ref` (`ref`)
) ENGINE=MyISAM AUTO_INCREMENT=2743 DEFAULT CHARSET=latin1;

Answer 1

能否请您发布此查询的结果:

SELECT  *
FROM    information_schema.statistics
WHERE   UPPER(table_name) = UPPER('temperatures')
        AND UPPER(column_name) = UPPER('ref')

更新 3:

您的exhibition 表定义为CHARACTER SET UTF8，而temperatures 定义为CHARACTER SET LATIN1。

由于 UTF8 不能隐式转换为 LATIN1，因此将隐式 COLLATE UTF8 添加到 Temperature.ref , 使其不可压缩且索引不可用。

您需要将查询重写为:

SELECT  Exhibition.venue_id, Exhibition.name, Exhibition.slug,
        Venue.name, Venue.slug, Venue.location_id,
        Venue.id, Exhibition.id
FROM    exhibitions AS Exhibition
LEFT JOIN
        venues AS Venue
ON      Venue.id = Exhibition.venue_id
LEFT JOIN
        temperatures AS Temperature FORCE INDEX (unique_ref)
ON      Temperature.ref = CAST(Exhibition.id AS CHAR CHARACTER SET latin1)
WHERE   Exhibition.active = '1'
        AND Exhibition.ends <= CURDATE()
ORDER BY
        Temperature.temperature DESC
LIMIT 5

，或者更好的是，将 temperatures 转换为 UTF8:

ALTER TABLE temperatures MODIFY COLUMN ref CHAR(36) CHARACTER SET utf8 NOT NULL, CHARACTER SET utf8