某种特定查询条件下的 MYSQL 效率问题

V2EX = way to explore

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

现在注册

已注册用户请登录

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

SELECT count (*) FROM 表 1,表 2 WHERE 表 1.type='text' AND 表 1.tid=表 2.id AND 表 2.user='b';

我在表 1 里建立了索引 tid 和 type ，但这句话的效率依然极烂，大约要执行 2 秒以上。

请问这种查询条件下，我该如何建立索引（或者改写语句）才能让查询效率高起来

tid

type

索引

查询

16 条回复 • 2015-08-19 17:13:01 +08:00

wmttom

2015-08-19 12:07:10 +08:00

你的写法是 cross join 相当于两个表做了笛卡尔积，根据情况改用 inner join 应该会快很多

heat

2015-08-19 12:15:50 +08:00

@wmttom 大概意思懂点，但请问具体改怎么写呢？

我这样写效率是一样的：
SELECT count (*) FROM 表 1 WHERE 表 1.type='go' AND (SELECT user FROM 表 2 WHERE 表 2.id=表 1.tid )='b'

honeycomb

2015-08-19 12:17:47 +08:00 via Android

@wmttom 这个方法是有用的

mhycy

2015-08-19 12:24:09 +08:00

SELECT count (*) FROM table_1
JOIN table_2 ON table_2.user='b' AND table_1.tid = table_2.id
WHERE table_1.type='text'

试试？

heat

2015-08-19 12:29:29 +08:00

@mhycy 执行了 2.19 秒和我写的那段效率基本没差...

Explain 的结果如下
1 SIMPLE 表 1 ALL tid,type NULL NULL NULL 114299 Using where
1 SIMPLE 表 2 eq_ref PRIMARY PRIMARY 4 user.表 1.tid 1 Using where

skydiver

2015-08-19 12:32:21 +08:00

你的 table2 没建索引。

heat

2015-08-19 12:38:07 +08:00

@skydiver user 字段么？我试过建立索引和不建立效果一模一样，现在瓶颈是 table1

pubby

2015-08-19 12:41:37 +08:00

先表 2 ，再 left join 表 1
select count (*) from t2 left join t2 on t2.id=t1.tid and t1.type='text' where t2.user='b' and t1.tid IS NOT NULL

skydiver

2015-08-19 13:07:37 +08:00

@heat 因为你先表 1 然后 join 表 2 ，这样只能用上 2 的索引，然后 2 还没索引。
所以要么 2 加上索引，要么拿 2 做驱动表， join 表 1

skydiver

2015-08-19 13:08:20 +08:00

@heat tid 字段

skydiver

2015-08-19 13:09:40 +08:00

@pubby 哦对，表 2 的 id 应该是已经有索引了。那就是 join 顺序的问题了，试试反过来。把数据少的表放前面，数据多的放后面 join

heat

2015-08-19 13:34:12 +08:00

@pubby
@skydiver pubby 的方法也试了，效率一样。

gamexg

2015-08-19 13:35:36 +08:00 via Android

确认四个字段都有索引。

然后看
http://blog.csdn.net/mchdba/article/details/9190771

感如果还是没问题，那么需要考虑更改方案了。

gamexg

2015-08-19 13:47:08 +08:00

刚刚手机回复。

请确认表 2.user 有索引。

猜测表 2 是用户表，应该优先使用表 2.user 的索引获得用户 id ，然后再根据用户 id 查询用户相关的帖子。

如果表 2.user 没有索引，那么 mysql 只能先将表 1.type='text' 的记录全部找出来，然后每个记录都去查询一下表 2 ，速度绝对慢啊。

fwings260

2015-08-19 14:07:31 +08:00

SQL 不断句，不换行。。。看的蛋疼。。。

pubby

2015-08-19 17:13:01 +08:00

explain 表 2 left join 表 1 那个查询，贴上来看看