MySQL 8.0.15 Dam 慢选择查询

Question

查询的格言非常简单，找出外键列的最后一个条目。我可以说的伪代码是

从 journeyTable 中选择 vehicleid、last_journey_point、last_journey_time。

这是我的 SQL 语句

-- loconumber 是索引列-- journeyserla 是一个自动编号主键 int(11)-- 表 locojourney 包含 400,000 条记录

-- 下面的代码块在 19 秒内执行

with LocomotiveLastRun AS(
     -- this block of code runs in 0.016 sec
    SELECT locojourney.loconumber , MAX(locojourney.journeyserla) as lastrunid
      FROM locojourney GROUP BY loconumber)

  SELECT locojourney.CurrentCombiners , locojourney.JourneySerla , 
         locojourney.From_RunPoint , locojourney.NEXT_RunPoint 
   FROM  LocomotiveLastRun FORCE INDEX(lastrunid)
   JOIN  locojourney FORCE INDEX(PRIMARY) ON x.lastrunid = locojourney.journeyserla 
  WHERE  locojourney.ishoc = 'n'

EXPLAIN 命令显示了一个派生表，该表不使用索引并使用 where 和 type ALL

这是表定义:

    -- SHOW CREATE TABLE locojourney
CREATE TABLE `locojourney` (
  `trainID` smallint(5) NOT NULL,
  `LocoNumber` varchar(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `CurrentLocoBase` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `CurrentDuedate` date DEFAULT NULL,
  `LocoConsist` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `CurrentLocoDomain` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DomainChange` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `FEDR` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `LADR` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `ISBANKER` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `TrainName` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `WithOutLoad` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N',
  `runRoute` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `From_RunPoint` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `From_RunTime` datetime NOT NULL,
  `NEXT_RunPoint` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `NEXT_RunTime` datetime NOT NULL,
  `Affects_Outage` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `Affects_Mileage` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `GroundDistance` double(5,2) DEFAULT '0.00',
  `SHGallowance` int(11) DEFAULT '0',
  `Outage` double(5,4) DEFAULT '0.0000',
  `UnderServiceType` enum('FHT','CHG','DEP','MIX','DETN') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'FHT',
  `SubServiceHead` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'RUN',
  `IShoc` enum('N','Y') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'N',
  `CurrentCombiners` varchar(28) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `RunSetSerla` varchar(25) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `JourneySerla` int(11) NOT NULL AUTO_INCREMENT,
  `NominationSerla` varchar(50) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `Traction` enum('DSL','AC') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'DSL',
  `Trainload` smallint(4) NOT NULL DEFAULT '0',
  `LeadAssist` enum('Y','N') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N',
  `DEO` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,
  `DEOtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`JourneySerla`),
  KEY `trainID` (`trainID`) USING BTREE,
  KEY `routesection_idx` (`runRoute`) USING BTREE,
  KEY `loconumber_idx` (`LocoNumber`) USING BTREE,
  KEY `runsetserla_idx` (`RunSetSerla`) USING BTREE,
  KEY `subservicehead_idx` (`SubServiceHead`) USING BTREE,
  CONSTRAINT `locojourney_ibfk_1` FOREIGN KEY (`SubServiceHead`) REFERENCES `ineffective` (`IneffectiveHead`) ON UPDATE CASCADE,
  CONSTRAINT `locojourney_ibfk_3` FOREIGN KEY (`runRoute`) REFERENCES `routesections` (`Sectionname`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `loconumber_fk` FOREIGN KEY (`LocoNumber`) REFERENCES `lococontainer` (`LocoNumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=345719 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Answer 1

with LocomotiveLastRun AS(
       -- this block of code runs in 0.016 sec
    SELECT locojourney.loconumber , MAX(locojourney.journeyserla) as lastrunid
      FROM locojourney 
     GROUP BY loconumber)

为什么这个 CTE 子查询很快？因为您的表已经在 (loconumber, journeyserla) 上有了索引。 (InnoDb 自动将主键附加到每个索引。)可以通过对该索引进行松散索引扫描来满足此查询，而且速度很快。

现在您的主要查询:

1. 摆脱 FORCE INDEX()。除非你至少有十年的 SQL 经验，或者你已经阅读了 MySQL 中 InnoDB 索引的源代码，否则不要梦想使用它。值得注意的是，它在 CTE 上完全没用，因为 CTE 没有索引。
2. 为了清晰起见，请将主(详细信息)表放在第一位，将 CTE 放在第二位。
3. 为清楚起见，将 JOIN 重铸为 WHERE...IN...

这三个建议给了我们这个:

   WITH LocomotiveLastRun AS (...)
 SELECT locojourney.CurrentCombiners , locojourney.JourneySerla , 
        locojourney.From_RunPoint , locojourney.NEXT_RunPoint 
   FROM locojourney
  WHERE journeyserla IN (SELECT lastrunid FROM LocomotiveLastRun)
    AND locojourney.ishoc = 'n'

现在，什么索引可以帮助这个查询就很清楚了。

(ishoc) 上的索引会有所帮助。 (它实际上是一个索引，因为 InnoDB，在 (ishoc, journeyserla) 上，所以它有助于满足 WHERE 条件。)查询计划器使用 BTREE 随机访问来查找第一个索引具有 ishoc 值 'n' 的行，然后扫描主键的值以将它们与 IN 子句匹配。

代替该索引，覆盖查询的复合索引会更有帮助。这样的覆盖索引特别有用，因为表的每一行都很大，有很多列。该索引提到了 WHERE 子句中的列以及您要选择的列，如下所示:

(ishoc, journeyserla, CurrentCombiners, From_RunPoint, NEXT_RunPoint)

查询计划器可以完全从索引来满足您的查询，从而节省了磁盘读取时间来满足查询。如果您经常使用查询，那么这个索引是个好主意。但是，它确实会占用磁盘空间并稍微减慢 INSERT 和 UPDATE 操作。

阅读https://use-the-index-luke.com/