• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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,都是如下图:

    这二者中删除任何一个都不行,删除一个,就有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 自定义变量的概念及特点
    • MySQL中使用自定义变量 编写偷懒的UNION示例
    • 理解MySQL查询优化处理过程
    • mysql查询优化之100万条数据的一张表优化方案
    • 详解MySQL 联合查询优化机制
    • MySQL查询优化之查询慢原因和解决技巧
    • MySQL之select in 子查询优化的实现
    • Mysql慢查询优化方法及优化原则
    • 30个mysql千万级大数据SQL查询优化技巧详解
    • MySQL百万级数据分页查询优化方案
    • MySQL 使用自定义变量进行查询优化
    上一篇:分享8个不得不说的MySQL陷阱
    下一篇:Mysql5.7.17 winx64.zip解压缩版安装配置图文教程
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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