mysql优化 - mysql慢查询问题

问题描述

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

这条语句执行大概花了10s，explain结果如下：

*************************** 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

这条语句执行时间不定，偶尔出现超时

如何优化索引使sql执行速度加快

问题解答

第一个语句：从sql语句本身来说，是单表查询，已经使用了索引，且索引中选择性较高的字段userid也放到了前面，感觉没什么可以优化的地方。从业务逻辑来说，可以考虑是否能增加insert_time的条件，并创建(userid, insert_time)的索引，这样通过索引能够过滤掉的记录更多。还有一个思路就是拿空间换时间，创建一个包括查询结果和条件所有字段的索引，如(user_id, priority, notify_id, notify)，这样只用查询索引就能得到结果，避免了回表的查询成本。另外就是看业务上是否允许不排序，这样可以减少mysql做排序的成本。

第二个语句：除了第一个语句的优化思路外，可以考虑重写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：不懂怎么优化，但应该尽量避免用OR。

比较奇怪，为什么不用自增主键？然后看样子应该用了分区吧，按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)