Smail_的gravatar头像
Smail_2017-08-30 11:34:53
mysql大数据查询优化经验分享

正儿八经mysql优化!

mysql数据量少,优化没必要,数据量大,优化少不了,不优化一个查询10秒,优化得当,同样查询10毫秒。

这是多么痛的领悟!

mysql优化,说程序员的话就是:索引优化和where条件优化。

实验环境:MacBook Pro MJLQ2CH/A,mysql5.7,数据量:212万+

 

 

ONE:

select * from article

INNER JOIN ( 

SELECT id 

FROM article

WHERE

    length(content_url) > 0 and

    (select status from source where id = article.source_id)=1 and 

    (select status from category where id = article.category_id)=1 and 

    status = 1 and id < 2164931

order by stick desc,pub_time desc 

limit 240,15 

) AS t 

USING(id);

咋一看,大佬肯定会想杀了我,没事做啥自关联,还是inner join。XX楼的,把我的杀猪刀拿来,我要宰了博主!!!

说实话,早上出门我的脑袋没被门挤,我也不想这样的。

1.数据量大了,你要做offset很大的分页查询,还真的这样提速,原因 ---> 用join子表中的id覆盖到全表,避免全表扫描。

看我的order by(细语:不就是个order by,TM谁不会写),你把这个order by换成你自己的表中的字段desc or explain看看。Extra ---> filesort ! shit !

2.针对这种多个条件的order by,通常我们会直接给两个字段分别加index,然而还是会Extra ---> filesort。另辟蹊径,给order by后面的所有条件加一个联合索引,注意顺序一定要和你的order by顺序一致。这样Extra就只剩下where了。

再看看where,(select status from source where id = article.source_id)=1 and ...又啥JB写法!

3.想过用join+index的方式,最后测试出来,和这种方式几乎无差别。生产环境是这样写的,那就这样吧,还能少两个索引(source_id,category_id),懒病犯了谁都阻挡不了,以后吃亏了又回来继续优化呗。

4.这个点是我昨晚才get到的,where条件的满足顺序是优先满足最后一个条件,从右到左,经过删除index测试,确实有效果,能从6秒降到4秒,优化了index之后再次测试发现顺序对耗时影响几乎可以忽略不计,0.X毫秒。

 

TWO:

select * from article 

INNER JOIN (

SELECT id FROM article WHERE INSTR(ifnull(title,''),'战狼') > 0 and status != 9 

order by pub_time desc 

limit 100,10 

) AS t USING(id); 

嗯——又是inner join.......

INSTR(ifnull(title,''),'战狼') > 0,为啥不用like......

1.考虑到这是管理平台的搜索,没有去搜索引擎上搜,搜索引擎是一个小时才同步一次数据,数据不全。管理人员搜索时只管他要的结果,like %XX%不能走索引,效率比instr低了5倍,又测试了regexp '.*XX*.',还是比instr耗时多一点,索性.....

desc or explain看看,filesort.....给pub_time加个index看看,还是filesort.....

2.这种情况有另外一种方案,SELECT id FROM article force index(pub_time),指定使用这个索引。但是这种写法太缺灵活性了,OUT!百度一下,有高人指点迷津:把status和pub_time建个联合索引(pub_time_status,order的条件在前),让where查询的时候,把这个index自动force上。

 

THREE:

select * from article where status != 9 order by pub_time desc limit 100000,25;

desc or explain,还是filesort.....前面不是给status和pub_time建了联合索引了吗,tell me why......

好吧,我也不知道,把status和pub_time再建个联合索引status_pub_time,这次where条件在前,explain没filesort了,但是这个index却没有被使用,它勾搭出了pub_time_status。搞不懂啊

同时我又explain了TWO的SQL,都是如下图:

mysql大数据查询优化经验分享

这二者中删除任何一个都不行,删除一个,就有sql会filesort!

 

FOUR:

SELECT * from follow

where (((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054

ORDER BY sort limit 15,15;

 

SELECT * from follow inner join(

SELECT id from follow

where (((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) or ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054

ORDER BY sort limit 15,15

) as t using(id);
(SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1 and user_id=10054)

union all

(SELECT id, source_id, user_id, temporary, sort, follow_time, read_time,type from follow where (select status from topic WHERE id=follow.source_id)=1 and follow.type=2 and user_id=10054)

ORDER BY sort limit 15,15;

看看这三句sql,interesting,是不是!

为了公平起见,我已经优化了索引,user_id_sort(user_id,sort),让where在用user_id判断时force上这个索引。

第一句:0.48ms

第二句:0.42ms

第三句:6ms,导致时间长那么多的原因是union(查询两次表,合并成子表)后不能用index覆盖到order by的sort上

有的时候union不一定比or快。

 

FIVE:

没了!

有,就更新!

 

总结,mysql想将就到所有的查询,难!只能尽量优化了,选择查询量大,查询次数多的SQL优化,优先满足这些SQL。


打赏

已有1人打赏

最代码官方的gravatar头像

分享到:

最近浏览
小清新前天
暂无贡献等级
暂无贡献等级
hunteryan11月21日
最代码贡献等级说明
coding喵11月19日
最代码贡献等级说明
weienqing11月17日
最代码贡献等级说明
是时候了11月14日
最代码贡献等级说明
Mingelam11月13日
最代码贡献等级说明
1143260211月11日
暂无贡献等级
ftd2010wmq11月3日
暂无贡献等级
jt8281011月2日
暂无贡献等级
sherto11月1日
暂无贡献等级
duchaochen10月25日
暂无贡献等级
hellozhao10月18日
暂无贡献等级
chenyp04001110月16日
最代码贡献等级说明
懒代码10月13日
暂无贡献等级
zhaodaren10月13日
暂无贡献等级
wwwzzzjjj10月13日
最代码贡献等级说明
流转的时空10月11日
最代码贡献等级说明
黑夜丶黎明10月11日
暂无贡献等级
顶部客服微信二维码底部
>扫描二维码关注最代码为好友扫描二维码关注最代码为好友