国产成人精品久久免费动漫-国产成人精品天堂-国产成人精品区在线观看-国产成人精品日本-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

主站蜘蛛池模板: 97国产精品欧美一区二区三区 | 看国产一级片 | 一本本久综合久久爱 | 国自产精品手机在线视频香蕉 | 免看一级一片一在线看 | 美国三级在线观看 | 国产人成 | 香港日本韩国三级网站 | 日韩一中文字幕 | 91视频一区| 欧美videosex性欧美成人 | 久草综合在线观看 | 欧美丝袜xxxxx在线播放 | 狠狠综合久久久久综合小说网 | 欧美肥婆videoxxx | 精品久久久久久久久久久 | 久久九九爱 | 国产亚洲精品久久麻豆 | 好爽~好硬~好紧~蜜芽 | 亚洲悠悠色综合中文字幕 | 国产人做人爱视频精品 | 怡红院在线观看在线视频 | 久久国产精品视频一区 | 国产美女精品视频 | 午夜影院免费入口 | 玖玖玖精品视频免费播放 | 久久日本精品一区二区免费 | 国产麻豆福利a v在线播放 | 九九精品免视频国产成人 | 91精品国产美女福到在线不卡 | 一级毛片在线看 | 久久er热视频在这里精品 | 日韩欧美视频一区二区在线观看 | 国产欧美日韩在线观看 | 免费在线精品视频 | 国产a级精品特黄毛片 | 亚洲精品在线影院 | 日韩 欧美 自拍 | 一区精品麻豆经典 | 亚洲欧美一二三区 | 97在线视频观看 |