mysql - 如何使用 like 命令提高查询速度

Question

当我尝试运行以下更新查询时，大约需要 40 小时才能完成。所以我添加了一个时间限制(Update query with time limitation)。但仍然需要几乎相同的时间才能完成。有什么办法可以加快此更新速度吗？

编辑:我真正想做的是只获取某些特定日期之间的日志，并对这些记录运行此更新查询。

create table user
(userid varchar(30));

create table logs
( log_time timestamp,
log_detail varchar(100),   
userid varchar(30));

insert into user values('user1');
insert into user values('user2');
insert into user values('user3');
insert into user values('');

insert into logs values('no user mentioned','user3');
insert into logs values('inserted by user2','user2');
insert into logs values('inserted by user3',null);

更新前的表格

log_time |        log_detail | userid |
 ..      |-------------------|--------|
 ..      |   no user mention |  user3 |
 ..      | inserted by user2 |  user2 |
 ..      | inserted by user3 | (null) |

更新查询

update logs join user
set logs.userid=user.userid
where logs.log_detail LIKE concat("%",user.userID,"%") and user.userID != "";

有时间限制的更新查询

update logs join user
set logs.userid = IF (logs.log_time between '2015-08-11 00:39:41' AND '2015-08-01 17:39:44', user.userID, null)
where logs.log_detail LIKE concat("%",user.userID,"%") and user.userID != "";

更新后的表格

log_time |        log_detail | userid |
   ..    |-------------------|--------|
   ..    |  no user mentione |  user3 |
   ..    | inserted by user2 |  user2 |
  ..     | inserted by user3 |  user3 |

编辑: 原始问题 Sql update statement with variable .

Answer 1

日志表每个月很容易填满大量的数据行，即使是最好的索引也无济于事，尤其是在 LIKE 运算符的情况下。您的 log_detail 列长度为 100 个字符，您的搜索查询是 CONCAT("%",user.userID,"%")。在 SQL 命令中使用函数会减慢速度，因为该函数正在进行额外的计算。如果您的用户 ID 是 John，您要搜索的是 %John%。所以你的查询将扫描该表中的每一行，因为索引将是半无用的。如果您没有第一个 %，则查询将能够有效地利用其索引。实际上，您的查询将执行 INDEX SCAN 而不是 INDEX SEEK。

有关这些概念的更多信息，请参阅:

Index Seek VS Index Scan

Query tuning a LIKE operator

好的，你能做些什么呢？两种策略。

• 选项 1 是限制您要搜索的行数通过。你有一个正确的想法使用时间限制来减少要搜索的行数。我的建议是把时间限制作为 WHERE 子句中的第一个表达式。大多数数据库首先执行第一个表达式。所以当第二个表达式开始，它只会扫描返回的行第一个表达式。

  update logs join user 
  set logs.userid=user.userid 
  where logs.log_time between '2015-08-01' and '2015-08-11' 
  and logs.log_detail LIKE concat('%',user.userID,'%')
  

• 选项 2 取决于您对数据库的控制。如果你有总控制(而且你有时间和金钱，MySQL 有一个功能叫做自动分片。这在 MySQL Cluster 和 MySQL 中可用织物。我不会像链接那样详细介绍这些产品下面提供的可以比我更好地解释自己总结一下，但是 Sharding 背后的想法是将行拆分为水平表，可以这么说。它背后的想法是你是不是搜索长数据库表，而是跨几张姐妹 table 同时上 table 。搜索 10 个表1000 万行比搜索 1 个 100 行表更快百万行。

Database Sharding - Wikipedia

MySQL Cluster

MySQL Fabric