国产成人精品久久免费动漫-国产成人精品天堂-国产成人精品区在线观看-国产成人精品日本-a级毛片无码免费真人-a级毛片毛片免费观看久潮喷

您的位置:首頁技術文章
文章詳情頁

數據庫 - MySQL 單表500W+數據,查詢超時,如何優化呢?

瀏覽:131日期:2022-06-13 14:39:08

問題描述

問題解答

回答1:

原因是你對record_global_id這個屬性做篩選,但條件不是等于,所以復合索引后面的部分就用不上了。

status列的區分度如何?加上索引(status, record_global_id)試試看。

回答2:

拆成幾條SQL分開查詢。

回答3:

根據題主的問題,你那條SQL條件那么多,但是只能用到一個索引,豈不可惜,WHERE條件很明顯的一處:如下的那個’OR’:

(( ((`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)OR (`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1) ) AND `type` = 2 AND `qa_id` = 0)OR ------------------- 此處這個OR ----------------------------------(`type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’)) ) AND `status` = 1 AND `record_global_id` < 5407938

可以將整體的大的WHERE分拆開來,思路就是 UNION,好了,直接貼我改造后的結果SQL,如果有作用望采納呦^_^

改造后SQL:

(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE ((`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3)OR (`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1) ) AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938)UNION(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE `type` = 3 AND `to_uid` = 52494 AND `to_type` = 3 AND `from_uid` = 5017446 AND `from_type` = 1 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’) AND `status` = 1 AND `record_global_id` < 5407938)ORDER BY `record_global_id` DESCLIMIT 0 , 20;

如有作用能將執行計劃截圖發到評論里嗎?我想驗證下我的猜想,謝謝!

回答4:

創建復合索引(from_uid,to_uid,from_type,to_type,type,status,record_global_id)修改sql為union如下:

select * from ((SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3 AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20) union(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 52494 AND `from_type` = 3 AND `to_uid` = 5017446 AND `to_type` = 1 AND `type` = 2 AND `qa_id` = 0 AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20) union(SELECT `record_global_id`, `type`, `mark`, `from_uid`, `from_type`, `to_uid`, `to_type`, `send_method`, `action`, `module`, `send_time`, `content`FROM `im_data_record`WHERE`from_uid` = 5017446 AND `from_type` = 1 AND `to_uid` = 52494 AND `to_type` = 3 AND `type` = 3 AND `module` IN (’community.doctor:appointment:notice’ , ’community.doctor:transfer.treatment’, ’community.doctor:transfer.treatment.pay’, ’community.doctor:weiyi.guahao.to.user’, ’community.doctor:weiyi.prescription.to.patient’, ’community.doctor:user.buy.prescription’)AND `status` = 1 AND `record_global_id` < 5407938 ORDER BY `record_global_id` DESC LIMIT 0 , 20)) aa ORDER BY `record_global_id` DESC LIMIT 0 , 20;

如果根據from_uid,to_uid,from_type,to_type,type,status篩選的結果集較少的話,可在union子查詢中不用加AND record_global_id < 5407938 ORDER BY record_global_id DESC LIMIT 0 , 20

主站蜘蛛池模板: 高清不卡毛片 | 久草久草久草 | 午夜欧美成人久久久久久 | 成人一级视频 | 7ass欧美| 毛片一级 | 久久99精品久久久久久国产越南 | 手机看片99| 免费又黄又爽又猛大片午夜 | 国产在线观a免费观看 | 欧美一区二区三区免费不卡 | 国产v综合v亚洲欧美大另类 | 成年女人毛片免费视频永久vip | 好看欧美视频高清va | 91精品专区 | 日韩在线精品视频 | 亚洲国产另类久久久精品小说 | 成人毛片免费观看视频在线 | 久久久久在线观看 | 成人在线免费看 | 日韩免费一级 | 在线视频区 | 国产精品久久久久久久 | 亚洲美女视频一区二区三区 | 国产欧美日韩综合精品一区二区 | 欧美野外性k8播放性迷宫 | 国产精品成人网 | 国产剧情一区二区 | 欧美成人免费高清视频 | 色一欲一性一乱一区二区三区 | 男女视频在线免费观看 | 欧美一级高清视频在线播放 | 成年网站免费视频黄 | 免费观看成年的网站 | 欧美α一级毛片 | 五月桃花网婷婷亚洲综合 | 美女性视频网站 | 久久黄色片 | 澳门毛片在线播放 | 九九九九热精品免费视频 | 男人的天堂2018 |