MySQL简单选择查询很慢

小编典典

MySQL简单选择查询很慢

sql

我有一个大约有110.000.000项的大型mysql表

表设计为：

CREATE TABLE IF NOT EXISTS `tracksim` (
`tracksimID` int(11) NOT NULL AUTO_INCREMENT,
`trackID1` int(11) NOT NULL,
`trackID2` int(11) NOT NULL,
`sim` double NOT NULL,
PRIMARY KEY (`tracksimID`),
UNIQUE KEY `TrackID1` (`trackID1`,`trackID2`),
KEY `sim` (`sim`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

现在我要查询一个普通查询：

SELECT trackID1, trackID2 FROM `tracksim` 
WHERE sim > 0.5 AND 
      (`trackID1` = 168123 OR `trackID2`= 168123)
ORDER BY sim DESC LIMIT 0,100

Explain语句给我：

+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | tracksim | range | TrackID1,sim  | sim  | 8       | NULL | 19980582 |   100.00 | Using where |
+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+

该查询似乎很慢（约185秒），但我不知道这是否仅是由于表中的项目数量所致。您是否提示我如何加快查询或查表的速度？

阅读 165

2021-04-28

共1个答案

小编典典

我通常都同意Drap，但是以下查询变体可能更加有效，尤其是对于更大的LIMIT：

SELECT TS2.*
FROM (
  SELECT tracksimID, sim
  FROM tracksim
  WHERE trackID1 = 168123
    AND sim > 0.5
  UNION
  SELECT trackSimID, sim
  FROM tracksim
  WHERE trackid2 = 168123
    AND ts.sim > 0.5
  ORDER BY sim DESC
  LIMIT 0, 100
) as PreQuery
JOIN TrackSim TS2 USING (TrackSimID);

要求(trackID1, sim)和(trackID2, sim)索引。

2021-04-28