mysql大数据查询优化经验分享(推荐)

站长资源 2024/11/24 佚名

2 0 1

正儿八经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，都是如下图：

这二者中删除任何一个都不行，删除一个，就有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快。

总结

以上所述是小编给大家分享的mysql大数据查询优化经验，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！

mysql大数据优化

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

评论“mysql大数据查询优化经验分享(推荐)”

暂无评论...

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒经典版》的玩家都可以获得奖励。

更新日志

2024年11月24日

mysql大数据查询优化经验分享(推荐)

Mysql5.7.17 winx64.zip解压缩版安装配置图文教程

分享8个不得不说的MySQL陷阱

评论“mysql大数据查询优化经验分享(推荐)”

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

更新日志

友情链接