MySQL中按creation_date范围查询的SQL有什么性能问题？

业务表结构 #

CREATE TABLE IF NOT EXISTS `test`.`job` (
  `id` BIGINT(20) NOT NULL,
  ...
  `creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` BIGINT(20) NOT NULL,
  `last_modified_date` DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `last_modified_by` BIGINT(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `test_date_idx` (`last_modified_date` DESC))
ENGINE = InnoDB;

待排查的查询SQL #

SELECT * FROM "test"
WHERE creation_date >= "{startingTime}"
AND creation_date <= "{endingTime}"

• 核心问题：查询过滤字段无匹配索引，触发全表扫描
  这是性能差的最主要原因。当前表仅对last_modified_date字段建立了索引，但查询的WHERE条件是用creation_date做时间范围过滤，该字段没有任何索引支撑。数据库执行这条SQL时无法通过索引快速定位符合时间范围的记录，只能逐行扫描整张表的所有数据做条件判断。表数据量较小时感知不明显，数据量达到十万级以上后查询耗时会急剧升高，高并发场景下还会抢占数据库IO资源，影响其他正常查询执行。
• 次要问题：SELECT *写法带来不必要的资源消耗
  直接查询全字段会读取大量业务不需要的列数据，既会增加磁盘读取数据页的IO开销，也会放大内存占用、网络传输的成本，如果表中存在TEXT、BLOB类大字段，这部分开销会更加明显。
• 额外语法隐患（非性能问题，但会直接导致查询失败）
  • 用双引号包裹表名不符合MySQL默认语法规则，默认配置下双引号包裹的内容会被识别为字符串而非表标识符，会直接抛出语法错误，MySQL中标识符引用需要使用反引号。
  • 建表时表是test库下的job表，SQL中直接写FROM "test"会被识别为查询当前库下名为test的表，和实际业务表不匹配。

• 为creation_date字段建立索引，如果业务中经常组合使用创建人、创建时间等字段做过滤，可以结合高频查询条件建立联合索引，进一步提升查询效率。
• 替换SELECT *写法，只明确查询业务实际需要的字段。
• 修正表名写法，正确引用为`test`.`job`，如果已经切换到test库，也可以直接写FROM job。

内容的提问来源于stack exchange，提问作者CHL