MySQL主键查询扫描5000 x太多行

Question

我试图使用WHERE语句根据主键运行一个简单的SELECT查询。我正在一个具有32核和30 GB内存的GCE实例上运行MySQL 5.7.9。在500个分区中有大约300米的记录。我99.9999%的查询来自于最近两天，这些数据完全符合内存，验证是因为我看到了从磁盘中读取的~0字节。我的CPU始终固定在100%，其中10-20个类似的查询同时运行。

我正在跟踪一些谷歌搜索数据，所以Requested、IsPriority和KeywordID的组合都有100个Google搜索数据。尽管我的主要索引被设计成不需要扫描，但当我查看EXPLAIN时，它显示正在扫描552 k行以返回100行。

平均而言，从RAM返回由主键直接指向的100行需要花费20-40行。我能做些什么来加速这个查询？

EXPLAIN EXTENDED SELECT * FROM data.Rankings
    -> WHERE Requested = '2016-02-26 00:00:00' AND NOT IsPriority AND KeywordID = '7387777811691965572'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Rankings
   partitions: p20160226
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 552598
     filtered: 1.00
        Extra: Using where
1 row in set, 2 warnings (0.01 sec)

这是DB表

CREATE TABLE `Rankings` (
  `KeywordID` char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Requested` timestamp NOT NULL,
  `IsPriority` tinyint(1) NOT NULL,
  `Retrieved` timestamp NOT NULL,
  `Rank` tinyint(4) NOT NULL,
  `Source` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ExternalID` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Phrase` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  // another 40 data columns of varying types
  PRIMARY KEY (`Requested`,`IsPriority`,`KeywordID`,`Rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED
// I have about 500 daily partitions over the last 1.5 years
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(Requested))
(PARTITION p20160222 VALUES LESS THAN (1456185600) ENGINE = InnoDB,
 PARTITION p20160223 VALUES LESS THAN (1456272000) ENGINE = InnoDB,
 PARTITION p20160224 VALUES LESS THAN (1456358400) ENGINE = InnoDB,
 PARTITION p20160225 VALUES LESS THAN (1456444800) ENGINE = InnoDB,
 PARTITION p20160226 VALUES LESS THAN (1456531200) ENGINE = InnoDB,
 PARTITION p20160227 VALUES LESS THAN (1456617600) ENGINE = InnoDB,
 PARTITION p20160228 VALUES LESS THAN (1456704000) ENGINE = InnoDB,
 PARTITION p20160229 VALUES LESS THAN (1456790400) ENGINE = InnoDB) */;

Answer 1

我怀疑NOT造成了问题。使用相等比较如何？

SELECT *
FROM data.Rankings
WHERE Requested = '2016-02-26 00:00:00' AND 
      IsPriority = 0 AND
      KeywordID = '7387777811691965572';