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

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

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

跪求

小菜

查询

21 replies • 1970-01-01 08:00:00 +08:00

jiangle

Apr 15, 2014

$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

Apr 15, 2014 via iPhone

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

WildCat

Apr 15, 2014 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

Apr 15, 2014

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

tobylee

Apr 16, 2014

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

zzNucker

Apr 16, 2014

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

vibbow

Apr 16, 2014

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

jiangle

Apr 16, 2014

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

jones

Apr 16, 2014 via Android

@tobylee 正解

manhere

Apr 16, 2014

explain下看看哇

11138

Apr 16, 2014

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

msg7086

Apr 16, 2014

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

Apr 16, 2014

类似这样的顺序
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

Apr 16, 2014

@msg7086
@11138

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

mahone3297

Apr 16, 2014

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

dong3580

Apr 16, 2014

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

wzzyj8

Apr 16, 2014

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

jiangle

Apr 16, 2014

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

jiangle

Apr 16, 2014

@11138
@dong3580
@msg7086

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

11138

Apr 17, 2014

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

konakona

Apr 17, 2014

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

等等等等