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

V2EX = way to explore

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

For Existing Member Sign In

This topic created in 3912 days ago, the information mentioned may be changed or developed.

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 replies • 2015-08-19 17:13:01 +08:00

wmttom

Aug 19, 2015

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

heat

Aug 19, 2015

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

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

honeycomb

Aug 19, 2015 via Android

@wmttom 这个方法是有用的

mhycy

Aug 19, 2015

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

Aug 19, 2015

@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

Aug 19, 2015

你的 table2 没建索引。

heat

Aug 19, 2015

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

pubby

Aug 19, 2015

先表 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

Aug 19, 2015

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

skydiver

Aug 19, 2015

@heat tid 字段

skydiver

Aug 19, 2015

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

heat

Aug 19, 2015

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

gamexg

Aug 19, 2015 via Android

确认四个字段都有索引。

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

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

gamexg

Aug 19, 2015

刚刚手机回复。

请确认表 2.user 有索引。

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

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

fwings260

Aug 19, 2015

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

pubby

Aug 19, 2015

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