小菜跪求 MySQL 大数据查询优化方案

V2EX = way to explore

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

现在注册

已注册用户请登录

V2EX 提问指南

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

情况：
新人刚到公司，领导丢了一个有300W条的库给我，一共两个表。
要实现多表查询，涉及到12个字段，我现在开了索引，查一条大概要1分钟。
但是领导要求把速度提起来，因小菜出入职场，跪求各位大神给传点经验~~~，谢谢啦~~

跪求

小菜

查询

21 条回复 • 1970-01-01 08:00:00 +08:00

jiangle

2014-04-15 23:38:53 +08:00

$app = $model->table('user a,vipuser c')
->where("a.username=c.vipUser and a.trueName=c.trueName and CONCAT(a.username,a.trueName) LIKE '%$keyword%' or a.numNo = '{$keyword}'")
->field('a.username,a.vipUser,a.Age,a.numNo,a.money,a.Sex,a.Address,a.Home,a.City,a.regTime,c.Phone,c.Email')

->select();
$this->assign('app',$app);
$this->display('index');

这是现在的语句，我用的thinkphp

Suanlatang

2014-04-15 23:40:01 +08:00 via iPhone

同关注下好多被脱裤的资料查起来很麻烦

WildCat

2014-04-15 23:50:25 +08:00 via iPhone

SQL里如果用 WHERE ... AND ... 引擎会不使用索引的

http://blog.csdn.net/gzh0222/article/details/6661779
http://www.cnblogs.com/hlxs/archive/2012/05/07/2487082.html

今天刚学到

anewg

2014-04-15 23:58:48 +08:00

@WildCat 谁告诉你 WHERE + AND 引擎不会用索引的？

tobylee

2014-04-16 00:02:19 +08:00

重点在这里CONCAT(a.username,a.trueName) LIKE '%$keyword%'

zzNucker

2014-04-16 00:05:34 +08:00

where子句里最好不要有函数操作。。。

vibbow

2014-04-16 00:10:43 +08:00

一个 LIKE '%KEYWORD%'
性能怎么可能起来...

jiangle

2014-04-16 00:14:45 +08:00

@vibbow 去掉了也不见的快起来！！！

jones

2014-04-16 00:23:41 +08:00 via Android

@tobylee 正解

manhere

2014-04-16 00:25:27 +08:00

explain下看看哇

11138

2014-04-16 00:30:15 +08:00

优化的方向就是简化查询条件，一定要用上索引，用EXPLAIN分析一下；如果可以，建立一些临时表（定期维护）。。。
加上你现在提供的资料有限，也见不得能得到什么有用的建议。

msg7086

2014-04-16 06:18:49 +08:00

user a,vipuser c

a.username=c.vipUser
and a.trueName=c.trueName
and CONCAT(a.username,a.trueName) LIKE '%$keyword%'

or a.numNo = '{$keyword}'

'a.username,a.vipUser,a.Age,a.numNo,a.money,a.Sex,a.Address,a.Home,a.City,a.regTime,c.Phone,c.Email'

这样的查询条件，如果要优化的话，试试创建临时表？

首先查询 a.numNo = keyword，这个应该是很快的，如果有索引的话
其次是搜索 a.username和a.truename 的LIKE。
我不知道为什么要CONCAT，不过如果一个人username叫aaa，truename叫bbb，期望搜索ab时能搜到这条记录吗？如果不是的话拆成两条语句。
最后给临时表插上索引，然后在和c表做内连接。

至少我觉得这样会快很多。

msg7086

2014-04-16 06:30:31 +08:00

类似这样的顺序
CREATE TEMPORARY TABLE IF NOT EXISTS #RANDOM_NAME
(INDEX(username, trueName))
AS (
SELECT * FROM user
WHERE numNo = ?keyword
)

INSERT INTO #RANDOM_NAME
SELECT * FROM user
WHERE username LIKE ?keyword-like

INSERT INTO #RANDOM_NAME
SELECT * FROM user
WHERE trueName LIKE ?keyword-like

SELECT ... ... ... FROM #RANDOM_NAME a, vipuser c
WHERE ... = ... AND ... = ...

jiangle

2014-04-16 09:06:51 +08:00

@msg7086
@11138

现在表里没有id自增字段，全部是varchar(255)
这个表主要是会员表记录会员的基本信息而已。

mahone3297

2014-04-16 09:55:53 +08:00

@tobylee 这位已经说的比较清楚了我觉得。。。lz explain下八。。。

dong3580

2014-04-16 11:20:00 +08:00

@jiangle
不要用like ‘%xx%’，可以用like 'xx%',切记。
不行存储过程试试动态语句，或许能减少点时间消耗。
12个字段还算少的，我前不久插数据，76个字段，我靠，想死都，

wzzyj8

2014-04-16 21:10:38 +08:00

提升硬件也是解决途径之一，又或者是用RAM Database

jiangle

2014-04-16 23:34:17 +08:00

@msg7086 现在两张表都没有主键自增，是不是给加个ID主键自增好些？

jiangle

2014-04-16 23:37:35 +08:00

@11138
@dong3580
@msg7086

这是 explain的截图，我已经复制表了，字段名改掉了。

11138

2014-04-17 00:12:35 +08:00

很明显啊，表A没用到索引，慢是应该的。

konakona

2014-04-17 19:43:09 +08:00

在功能里，优化SQL已经不能起到多少作用。
考虑采取减轻数据表压力的做法，如View、事务等。
我打个比方：
A功能日均hits达到10w以上，考虑：
1.分布式（require）
2.将常用的表结果缓存（memcached）
3.如果含搜搜需求（where条件叫复杂时或含有like），将每一个搜索结果另外存储至视图中

等等等等