sql - 使用两个 "in"子句优化查询

Question

我正在尝试优化 MySQL 上的查询，该查询大约需要 15-20 秒才能运行。我的数据表有大约 1000 万行，查询试图返回与 144 个“运行”字段和 35 个“名称”字段匹配的 68,000 条记录。因为查询使用了两个 in 子句，所以我的索引似乎没有太大帮助。

这是查询:

select * from data d where 
d.data_type='Result' and 
(d.run in ('8a7aee1f2a6232b1012a624da9201b92', '8a7aee1f2a6232b1012a625432a314ef' , 

... [144 runs]

)) and (d.name like 'itema[%]' or d.name like 'itemb[%]')

这是表定义

CREATE TABLE `data` (
  `data_type` varchar(31) NOT NULL,
  `id` char(32) NOT NULL,
  `entry_time` datetime default NULL,
  `name` varchar(255) NOT NULL,
  `step` int(11) default NULL,
  `value` double NOT NULL,
  `run` char(32) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK2EEFAA8ECCC6F3` (`run`),
  KEY `data2` (`run`,`step`),
  KEY `data3` (`data_type`,`name(10)`,`run`),
  CONSTRAINT `FK2EEFAA8ECCC6F3` FOREIGN KEY (`run`) REFERENCES `run_archive` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Explain 告诉我查询正在使用关键数据3。

id      select_type     table   type    possible_keys   key     key_len ref    rows    Extra
1       SIMPLE  d    range   FK2EEFAA8ECCC6F3,data2,data3    data3    223     NULL    113271  Using where

我曾经运行 144 个查询(每次运行一个)。执行一个查询似乎快了一倍，但仍然太慢了。

优化建议？我的想法是:

• 寻找加速的神奇索引
  这个起来

• 反规范化数据(它是轻松摆脱奔跑，但
  名字更难)

• split 不同表之间的数据(很难用我的 Java/Hibernate方法)

或者我只是在问不可能的事情？

编辑:原来最大的修复是增加我的 innodb_buffer_pool 的大小。执行此操作后，查询时间缩短到大约 1 秒半。我已将稍微改进的修复程序标记为“回答”。

Answer 1

考虑将 result 记录从 data 表中分离出来？我没看清您的结果 是多少百分比，但也许值得在您的 Prod 数据库的 Dev 副本中进行基准测试。

你能 FK 那些 run 值吗？如果它们是可重用的(？)，也许创建一个 Run 表？我的估计是 144 个字符串匹配，即使是索引，也比 int 或 smallint 慢。同样，对这个建议或任何建议进行基准测试显然会证明这个理论。

当 name 属性中不包含 like 子句时，查询计划有何不同？