求高手帮助解决 MYSQL 查询性能问题

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 3072 天前的主题，其中的信息可能已经有所发展或是发生改变。

在 MYSQL 数据库中有两个表
表 1: application_instance (id, instance_name,archived,private_instance ) 127 条数据
表 2: frequency_monitor_log(id,archived,instance_id) 2772140 条数据

现在有如下关联统计查询 SQL

select count(ai.id) from frequency_monitor_log ai where ai.archived = 0

and exists ( select a.id from application_instance a where a.archived = 0 and a.private_instance = 0 and a.id = ai.instance_id )

查询时间竟然要 10 秒, 如何优化? 求助
已知 instance_id, id, archived, private_instance 字段都已经建立了索引. 尝试使用 inner join 等方式, 结果也一样很慢.

6 条回复 • 2016-08-03 09:53:04 +08:00

xujif

2016-08-02 23:59:21 +08:00 via iPhone

换成 join 应该不会太慢，你这个语句更好的是把 exists 换成 where ai.instance_id in ( select a.id from application_instance a where a.archived = 0 and a.private_instance = 0) 这个语句不会导致 n*m

billlee

2016-08-03 00:03:36 +08:00

SELECT count(1) FROM frequency_monitor_log AS a JOIN application_instance AS b ON a.instance_id = b.id WHERE a.archived = 0 AND b.archived = 0 AND b.private_instance = 0;
对 frequency_moniter_log 建立 (instance_id, archived) 索引，而不是对两个字段单独建索引。
id 请设置为主键。
遇到问题先 EXPLAIN.

ferock

2016-08-03 00:31:05 +08:00 via Android

干嘛都不用 count(*)

monkeyk

2016-08-03 09:26:52 +08:00

@billlee 感谢, 测试了现在只需要 0.8 秒; 之前用 explain 查看发现用了全表扫描.

monkeyk

2016-08-03 09:27:30 +08:00

@xujif 正确, 用了你的办法, 0.8 秒

li24361

2016-08-03 09:53:04 +08:00

外表大内表小，用 in ，反之用 exists

额。一二楼都说的很明白了