問題描述

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語句一：

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

這條語句執(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語句二：

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

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

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

問題解答

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

第二個語句：除了第一個語句的優(yōu)化思路外，可以考慮重寫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：

語句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

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

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

這個索引：

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：

語句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：

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