时间戳

首页 > 解决方案 > 准备好的语句不使用预期的索引

php - 准备好的语句不使用预期的索引

问题描述

我有一个非常大的 IOT 样本表，我正在尝试对其运行相对简单的查询。使用 MySql CLI 正常运行查询会在 ~0.07 秒内返回结果。如果我首先通过 PDO 或通过运行 SQL PREPARE语句准备查询，则请求需要一分钟。

我启用了优化器跟踪功能，看起来在准备语句时，MySql 忽略了它应该使用的索引并对整个表进行文件排序。如果我做错了什么或者这看起来像一个 MySql 错误，我想要任何见解。

该表本身包含超过 1 亿个样本，其中至少有 30 万个与此处查询的设备相关联。我用 MySql 8.0.23 运行了这些测试，但是当我升级到 8.0.25 时，问题仍然存在。

表定义（省略了一些数据行）

Create Table: CREATE TABLE `samples` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int unsigned NOT NULL,
  `device_id` int unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `raw_reading` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `samples_organization_id_foreign` (`organization_id`),
  KEY `samples_reverse_device_id_created_at_organization_id_index` (`device_id`,`created_at` DESC,`organization_id`),
  CONSTRAINT `samples_device_id_foreign` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `samples_organization_id_foreign` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=188315314 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

在 < 1 秒内运行的 Sql

select *
from `samples`
where `samples`.`device_id` = 5852
  and `samples`.`device_id` is not null
  and `id` != 188315308
order by `created_at` desc
limit 1;

在一分钟内运行的 Sql

prepare test_prep from 'select * from `samples` where `samples`.`device_id` = ? and `samples`.`device_id` is not null and `id` != ? order by `created_at` desc limit 1';
set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;

可以在我的 gist中找到未准备好的 SQL 的跟踪，但相关部分是

{
  "reconsidering_access_paths_for_index_ordering": {
    "clause": "ORDER BY",
    "steps": [
    ],
    "index_order_summary": {
      "table": "`samples`",
      "index_provides_order": true,
      "order_direction": "asc",
      "index": "samples_reverse_device_id_created_at_organization_id_index",
      "plan_changed": false
    }
  }
},

可以在我的其他要点中找到准备好的查询的跟踪，但相关部分是

{
  "reconsidering_access_paths_for_index_ordering": {
    "clause": "ORDER BY",
    "steps": [
    ],
    "index_order_summary": {
      "table": "`samples`",
      "index_provides_order": false,
      "order_direction": "undefined",
      "index": "samples_reverse_device_id_created_at_organization_id_index",
      "plan_changed": false
    }
  }
},

标签： phpmysqlsql

解决方案

摆脱这个，因为= 5852保证它是错误的：

 and `samples`.`device_id` is not null

那么你的索引，或者这个索引，应该可以正常工作。

 INDEX(device_id, created_at, id)

不要使用@variables；优化器似乎不看它们包含的值。也就是说，而不是

set @a = 5852;
set @b = 188315308;
execute test_prep using @a, @b;

简单地做

execute test_prep using 5852, 188315308;

考虑在 bugs.mysql.com 上写一份错误报告

我怀疑"order_direction": "undefined"是问题的一部分。

推荐阅读