一张用户消息表 user_message, 数据量200W+, innodb引擎, 表结构大概如下:
message_id bigint(20) PRIMARY
user_id bigint(20)
list_type int(4)
create_time bigint(20) 13位时间戳
content varchar(2048)有索引如下;
idx_user_id, 用户ID字字段现有如下需求,根据user_id, list_type, create_time 查询数据, 有两条SQL语句,分别为:
语句1:
select * from
user_message where message_id in
(select message_id
from user_message
where user_id = 411683
and list_type = 1
and create_time >= 1477916769000)
order by message_id desc
limit 0,20;
语句2:
select a.*
from
(select *
from user_message
where (user_id = 411683 or user_id = 0)
and list_type = 1
and create_time >= 1477916769000) as a
order by a.message_id desc
limit 0,20;按最初的想法,两条语句的执行时间应该不会相差太多,但结果大大出乎意料,语句1的执行时间是0.04s, 语句2的执行时间是59s如果把语句2的limit 0,20 或者order by a.message_id desc 去掉,执行时间和语句1相同用explain 查看两条语句的相信执行计划
发现语句2还要比语句1更优语句1的explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: ref
possible_keys: PRIMARY,idx_user_id,i_list_type_create_time
key: idx_user_id
key_len: 8
ref: const
rows: 27660
filtered: 32.33
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: guanba_v2.user_message.message_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)语句2的explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: index
possible_keys: idx_user_id,i_list_type_create_time
key: PRIMARY
key_len: 8
ref: NULL
rows: 1422
filtered: 0.45
Extra: Using where
1 row in set, 1 warning (0.00 sec)请大神解释一下,到底问题出在哪里?
message_id bigint(20) PRIMARY
user_id bigint(20)
list_type int(4)
create_time bigint(20) 13位时间戳
content varchar(2048)有索引如下;
idx_user_id, 用户ID字字段现有如下需求,根据user_id, list_type, create_time 查询数据, 有两条SQL语句,分别为:
语句1:
select * from
user_message where message_id in
(select message_id
from user_message
where user_id = 411683
and list_type = 1
and create_time >= 1477916769000)
order by message_id desc
limit 0,20;
语句2:
select a.*
from
(select *
from user_message
where (user_id = 411683 or user_id = 0)
and list_type = 1
and create_time >= 1477916769000) as a
order by a.message_id desc
limit 0,20;按最初的想法,两条语句的执行时间应该不会相差太多,但结果大大出乎意料,语句1的执行时间是0.04s, 语句2的执行时间是59s如果把语句2的limit 0,20 或者order by a.message_id desc 去掉,执行时间和语句1相同用explain 查看两条语句的相信执行计划
发现语句2还要比语句1更优语句1的explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: ref
possible_keys: PRIMARY,idx_user_id,i_list_type_create_time
key: idx_user_id
key_len: 8
ref: const
rows: 27660
filtered: 32.33
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: guanba_v2.user_message.message_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)语句2的explain:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_message
partitions: NULL
type: index
possible_keys: idx_user_id,i_list_type_create_time
key: PRIMARY
key_len: 8
ref: NULL
rows: 1422
filtered: 0.45
Extra: Using where
1 row in set, 1 warning (0.00 sec)请大神解释一下,到底问题出在哪里?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货