成人在线亚洲_国产日韩视频一区二区三区_久久久国产精品_99国内精品久久久久久久

您的位置:首頁(yè)技術(shù)文章
文章詳情頁(yè)

mysql優(yōu)化 - mysql慢查詢(xún)問(wèn)題

瀏覽:81日期:2022-06-20 11:41:33

問(wèn)題描述

表結(jié)構(gòu)(表的數(shù)據(jù)量在100萬(wàn)左右)

CREATE TABLE `t_user_notification` ( `notify_id` bigint(20) NOT NULL, `user_id` bigint(20) NOT NULL, `notify` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `priority` tinyint(1) NOT NULL DEFAULT ’0’, `insert_time` datetime DEFAULT NULL, PRIMARY KEY (`notify_id`), KEY `idx_user_notification__priority_user_id` (`user_id`,`priority`), KEY `idx_userid_notifyid` (`user_id`,`notify_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_cisql語(yǔ)句一:

select notify_id, notify from t_user_notification where user_id = 1 and notify_id > 1 and priority = 1 order by notify_id asc limit 20G

這條語(yǔ)句執(zhí)行大概花了10s,explain結(jié)果如下:

*************************** 1. row *************************** id: 1 select_type: SIMPLEtable: t_user_notification_0399 type: index_mergepossible_keys: PRIMARY,idx_user_notification__priority_user_id,idx_userid_notifyid key: idx_user_notification__priority_user_id,PRIMARY key_len: 17,8 ref: NULL rows: 22629Extra: Using intersect(idx_user_notification__priority_user_id,PRIMARY); Using where; Using filesort語(yǔ)句二:

SELECT notify_id, notify, priority FROM t_user_notificationWHERE user_id = 1AND ((priority = 1 AND notify_id > 123) OR (priority = 0 AND notify_id > 345))ORDER BY notify_id ASC LIMIT 20G

這條語(yǔ)句執(zhí)行時(shí)間不定,偶爾出現(xiàn)超時(shí)

問(wèn)題

如何優(yōu)化索引使sql執(zhí)行速度加快

問(wèn)題解答

回答1:

第一個(gè)語(yǔ)句:從sql語(yǔ)句本身來(lái)說(shuō),是單表查詢(xún),已經(jīng)使用了索引,且索引中選擇性較高的字段userid也放到了前面,感覺(jué)沒(méi)什么可以?xún)?yōu)化的地方。從業(yè)務(wù)邏輯來(lái)說(shuō),可以考慮是否能增加insert_time的條件,并創(chuàng)建(userid, insert_time)的索引,這樣通過(guò)索引能夠過(guò)濾掉的記錄更多。還有一個(gè)思路就是拿空間換時(shí)間,創(chuàng)建一個(gè)包括查詢(xún)結(jié)果和條件所有字段的索引,如(user_id, priority, notify_id, notify),這樣只用查詢(xún)索引就能得到結(jié)果,避免了回表的查詢(xún)成本。另外就是看業(yè)務(wù)上是否允許不排序,這樣可以減少mysql做排序的成本。

第二個(gè)語(yǔ)句:除了第一個(gè)語(yǔ)句的優(yōu)化思路外,可以考慮重寫(xiě)sql,讓mysql使用索引idx_userid_notifyid

SELECT notify_id, notify, priority FROM t_user_notificationWHERE user_id = 1AND notify_id > 123AND (priority = 1 OR (priority = 0 AND notify_id > 345))ORDER BY notify_id ASC LIMIT 20回答2:

語(yǔ)句1:

select notify_id, notify from t_user_notification where user_id = 1 and priority = 1 and notify_id > 1 order by notify_id asc limit 20G

語(yǔ)句2:不懂怎么優(yōu)化,但應(yīng)該盡量避免用OR。

回答3:

比較奇怪,為什么不用自增主鍵?然后看樣子應(yīng)該用了分區(qū)吧,按user_id分嗎?

這個(gè)索引:

KEY `idx_user_notification__priority_user_id` (`user_id`,`priority`)

改成這樣

KEY `idx_user_notification__priority_user_id` (`user_id`,`priority`,`notify_id`)

select t1.notify_id, t1.notify from t_user_notification t1, (select notify_id from t_user_notification where user_id = 1 and notify_id > 1 and priority = 1 order by notify_id asc limit 20) t2where t1.notify_id = t2.notify_id;回答4:

語(yǔ)句2改成這樣試試

SELECT notify_id, notify, priorityFROM t_user_notificationJOIN(SELECT notify_id FROM t_user_notificationWHERE user_id = 1AND notify_id > 123AND (priority = 1 OR (priority = 0 AND notify_id > 345))ORDER BY notify_id ASC LIMIT 20)as tmp using(notify_id)回答5:

強(qiáng)制使用索引吧,你這里涉及到索引合并 select * from xxx FORCE INDEX(primary_key)

相關(guān)文章: