使用 ORDER by 的 MySQL 性能问题

Question

我遇到了性能问题，因此在大约 2.5 秒内执行以下命令仅返回 40 行:

SELECT DISTINCT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
INNER JOIN (
    SELECT song_id 
    FROM song_genre 
    WHERE genre_id IN ('25')
) genre1 ON genre1.song_id = song.song_id 
INNER JOIN (
    SELECT song_id 
    FROM song_production 
    WHERE production_id IN ('8')
) production1 ON production1.song_id = song.song_id 
WHERE approved='1' 
ORDER by song.priority DESC, song.song_id DESC 
LIMIT 0, 40

运行查询并丢弃 ORDER BY 在 0.01 秒内执行等

我知道这个问题可能与信息的计数方式有关，因为我正在使用 JOINS，所以可能需要嵌套查询，但我不是 100% 知道我会怎么做？

id   select_type   table             type     possible_keys   key       key_len   ref                   rows   Extra 1   PRIMARY       <derived3>        ALL      NULL            NULL      NULL      NULL                   321   Using temporary; Using filesort 1   PRIMARY       <derived2>        ALL      NULL            NULL      NULL      NULL                  3424   Using join buffer 1   PRIMARY       song              eq_ref   PRIMARY         PRIMARY   4         production1.song_id      1   Using where 3   DERIVED       song_production   ref      PRIMARY         PRIMARY   4                                339   Using index 2   DERIVED       song_genre        index    NULL            PRIMARY   8         NULL                  3424   Using where; Using index

Table song:

CREATE TABLE `song` (
`song_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` tinytext,
`length` varchar(5) DEFAULT NULL,
`Type` varchar(1) DEFAULT NULL,
`Vocals` varchar(10) DEFAULT NULL,
`Themes` varchar(10) DEFAULT NULL,
`Explicit` varchar(10) DEFAULT NULL,
`timesig` varchar(3) DEFAULT NULL,
`keysig` varchar(250) NOT NULL,
`bpm` int(3) DEFAULT NULL,
`speed` varchar(7) DEFAULT NULL,
`Era` varchar(10) DEFAULT NULL,
`Language` varchar(10) DEFAULT NULL,
`Keywords` varchar(10) DEFAULT NULL,
`description` mediumtext,
`search_description` longtext NOT NULL,
`key` varchar(25) NOT NULL,
`priority` int(2) NOT NULL,
`approved` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`song_id`),
FULLTEXT KEY `description` (`description`),
FULLTEXT KEY `search_description` (`search_description`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `head_desc` (`title`,`search_description`)
) ENGINE=MyISAM 
  AUTO_INCREMENT=1388 
  DEFAULT CHARSET=utf8 ;

表song_genre:

CREATE TABLE `song_genre` (
`genre_id` int(10) NOT NULL,
`song_id` int(10) NOT NULL, 
PRIMARY KEY (`genre_id`,`song_id`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 ;

表song_production:

CREATE TABLE `song_production` (
`production_id` int(10) NOT NULL,
`song_id` int(10) NOT NULL,
PRIMARY KEY (`production_id`,`song_id`)
) ENGINE=MyISAM 
  DEFAULT CHARSET=latin1 ;

Answer 1

第一步:

我猜 genre_id、production_id 和 approved 是 integer 列而不是 char，所以像 '25' 这样的数字用引号括起来是没有用的。取消引用它们:25

如果删除 DISTINCT 会发生什么？ (如果没有表的结构、主键、外键和唯一约束，我们无法知道)。结果中是否有多个相同的行？如果不是，只需删除 DISTINCT。如果是，将其删除并添加 GROUP BY song.song_id:

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
  INNER JOIN (
    SELECT song_id 
    FROM song_genre 
    WHERE genre_id IN (25)
  ) genre1 ON genre1.song_id = song.song_id 
  INNER JOIN (
    SELECT song_id 
    FROM song_production 
    WHERE production_id IN (8)
  ) production1 ON production1.song_id = song.song_id 
WHERE approved = 1 

----- GROUP BY song.song_id           --- not needed at all (with these tables)
                                                        --- (and structure)
ORDER BY song.priority DESC, song.song_id DESC 
LIMIT 0, 40 ;

---

第二步:

添加有用的索引。 (approved, priority, song_id) 上的索引可能有助于查询。

您还将 song.song_id 列定义为 UNSIGNED INT 而 song_genre.song_id 和 song_production.song_id 被定义为 SIGNED INT。如果您也将它们转换为 UNSIGNED INT 就好了。

我还会在 (song_id, genre_id) 和 (song_id, production_id) 上添加(唯一)索引。它们可能对此查询没有用，但您肯定会在其他情况下需要此类索引。

---

第三步:

尝试以其他方式重写查询。例如没有派生表:

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
  INNER JOIN
    song_genre AS genre1 
        ON genre1.song_id = song.song_id 
  INNER JOIN 
    song_production AS production1
        ON production1.song_id = song.song_id 
WHERE song.approved = 1
  AND genre1.genre_id IN (25)
  AND production1.production_id IN (8)
ORDER BY song.priority DESC
       , song.song_id DESC 
LIMIT 0, 40 ;

或使用EXISTS:

SELECT song.song_id, song.title, song.length, song.bpm, song.keysig 
FROM song 
WHERE song.approved = 1
  AND EXISTS
      ( SELECT *
        FROM song_genre AS genre1 
        WHERE genre1.song_id = song.song_id 
          AND genre1.genre_id IN (25)
      ) 
  AND EXISTS
      ( SELECT *
        FROM song_production AS production1
        WHERE production1.song_id = song.song_id
          AND production1.production_id IN (8)
      )
ORDER BY song.priority DESC
       , song.song_id DESC 
LIMIT 0, 40 ;

并测试哪一个执行得更快。