时间戳

首页 > 解决方案 > MySQL 显示“possible_keys”但不使用它

mysql - MySQL 显示“possible_keys”但不使用它

问题描述

我有一个包含超过一百万个条目和大约 42 列的表。我正在尝试在此表上运行 SELECT 查询，这需要一分钟才能执行。为了减少查询执行时间，我在表上添加了一个索引，但该索引并没有被使用。

表结构如下。尽管该表有 42 列，但我只在此处显示与我的查询相关的列

CREATE TABLE `tas_usage` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(255) DEFAULT NULL,
  `companyid` varchar(255) DEFAULT NULL,
  `SERVICE` varchar(2000) DEFAULT NULL,
  `runstatus` varchar(255) DEFAULT NULL,
  `STATUS` varchar(2000) DEFAULT NULL,
  `servertime` datetime DEFAULT NULL,
  `machineId` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2992891 DEFAULT CHARSET=latin1

我添加的索引如下

ALTER TABLE TAS_USAGE ADD INDEX last_quarter (SERVERTIME,COMPANYID(20),MACHINEID(20),SERVICE(50),RUNSTATUS(10));

我的选择查询

EXPLAIN SELECT DISTINCT t1.COMPANYID, t1.USERID, t1.MACHINEID FROM TAS_USAGE t1 
LEFT JOIN TAS_INVALID_COMPANY INVL ON INVL.COMPANYID = t1.COMPANYID
LEFT JOIN TAS_INVALID_MACHINE INVL_MAC_ID ON INVL_MAC_ID.MACHINEID = t1.MACHINEID
WHERE t1.SERVERTIME >= '2018-10-01 00:00:00' AND t1.SERVERTIME <= '2018-12-31 00:00:00' AND 
INVL.companyId IS NULL AND INVL_MAC_ID.machineId IS NULL AND 
t1.SERVICE NOT IN ('credentialtest%', 'webupdate%') AND  
t1.RUNSTATUS NOT IN ('Failed', 'Failed Failed', 'Failed Success', 'Success Failed', '');

EXPLAIN 结果如下

+----+-------------+-------------+------------+--------+-----------------------+-----------------------+---------+-----------------------------+---------+----------+------------------------------------------------+
| id | select_type | table       | partitions | type   | possible_keys         | key                   | key_len | ref                         | rows    | filtered | Extra                                          |
+----+-------------+-------------+------------+--------+-----------------------+-----------------------+---------+-----------------------------+---------+----------+------------------------------------------------+
|  1 | SIMPLE      | t1          | NULL       | ALL    | last_quarter          | NULL                  | NULL    | NULL                        | 1765296 |    15.68 | Using where; Using temporary                   |
|  1 | SIMPLE      | INVL        | NULL       | ref    | invalid_company_index | invalid_company_index | 502     | servicerunprod.t1.companyid |       1 |   100.00 | Using where; Not exists; Using index; Distinct |
|  1 | SIMPLE      | INVL_MAC_ID | NULL       | eq_ref | machineId             | machineId             | 502     | servicerunprod.t1.machineId |       1 |   100.00 | Using where; Not exists; Using index; Distinct |
+----+-------------+-------------+------------+--------+-----------------------+-----------------------+---------+-----------------------------+---------+----------+------------------------------------------------+

我的查询的解释

我想从表中选择所有记录TAS_USAGE

  1. 日期范围（包括）2018 年 10 月 1 日至 2018 年 12 月 31 日之间
  2. 在表中没有列COMPANYID和MACHINEID匹配，TAS_INVALID_COMPANY以及TAS_INVALID_MACHINE
  3. 列中不包含值（'credentialtest%'、'webupdate%'）和 列中SERVICE的值（'Failed'、'Failed Failed'、'Failed Success'、'Success Failed'、''）RUNSTATUS

标签： mysqlindexing

解决方案

关注日期范围，MySQL 基本上有两种选择：

  1. 连续阅读完整的表格并丢弃不符合日期范围的记录

  2. 使用索引识别日期范围内的记录，然后单独查找表中的每条记录（使用主键）（“随机访问”）

连续读取比随机访问快得多，但您需要读取更多数据。将会有一些收支平衡点，使用索引会比简单地读取所有内容要慢，而 MySQL 假设这里就是这种情况。如果这是正确的选择，很大程度上取决于它猜测该范围内实际有多少记录的正确程度。如果您使范围更小，它实际上应该在某个时候使用索引。

如果您知道（或想测试是否）使用索引更快，您可以强制 MySQL 将其与

... FROM TAS_USAGE t1 force index (last_quarter) LEFT JOIN ...

您应该使用不同的范围对其进行测试，并且如果您动态生成查询，则仅在您确定时才强制索引（因为如果您指定一个包含所有行的范围，MySQL 将不会更正您）。

有一种解决缓慢随机访问表的重要方法，尽管不幸的是它不适用于您的前缀索引，但我提到它以防您可以减小字段大小（或将它们更改为查找/枚举）。您可以使用覆盖索引包含 MySQL 评估查询所需的每一列：

包含查询检索到的所有列的索引。查询不是使用索引值作为指针来查找完整的表行，而是从索引结构中返回值，从而节省磁盘 I/O。

如前所述，由于在前缀索引中，部分数据丢失，不幸的是，这些列不能用于覆盖。

实际上，它们也根本不能用于太多，尤其是在进行随机访问之前不要过滤记录，因为要评估您的where-conditionRUNSTATUS或SERVICE，无论如何都需要完整的值。因此，您可以检查 egRUNSTATUS是否非常重要 - 可能 99% 的记录处于“失败”状态 - 在这种情况下，只需添加一个无前缀过滤器 (SERVERTIME, RUNSTATUS)（MySQL 甚至可能会自行选择该索引）。

推荐阅读