56,677
社区成员
发帖
与我相关
我的任务
分享
explain select * from T where a='a' and b='b' and c='c'
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t_player_activate_list 0 PRIMARY 1 id A 6 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 1 playerCode A 6 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 2 gameCode A 6 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 3 channelCode A 6 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_gamecode 1 gameCode A 6 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_channelcode 1 channelCode A 6 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 1 channelCode A 6 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 2 gameCode A 6 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 3 activateTime A 6 \N \N BTREE
建表SQL:
/*==============================================================*/
/* Table: t_player_activate_list */
/*==============================================================*/
create table t_player_activate_list
(
id bigint not null auto_increment comment 'ID',
playerCode varchar(64) not null comment '玩家编号',
gameCode varchar(64) not null comment '游戏编号',
activateTime datetime not null comment '激活时间',
channelCode varchar(64) not null comment '渠道编号',
primary key (id)
);
alter table t_player_activate_list comment '玩家激活记录';
/*==============================================================*/
/* Index: idx_playeractivatelist_gamecode */
/*==============================================================*/
create index idx_playeractivatelist_gamecode on t_player_activate_list
(
gameCode
);
/*==============================================================*/
/* Index: idx_playeractivatelist_p_g_c */
/*==============================================================*/
create unique index idx_playeractivatelist_p_g_c on t_player_activate_list
(
playerCode,
gameCode,
channelCode
);
/*==============================================================*/
/* Index: idx_playeractivatelist_channelcode */
/*==============================================================*/
create index idx_playeractivatelist_channelcode on t_player_activate_list
(
channelCode
);
/*==============================================================*/
/* Index: idx_playeractivatelist_c_g_a */
/*==============================================================*/
create index idx_playeractivatelist_c_g_a on t_player_activate_list
(
channelCode,
gameCode,
activateTime
);
执行SQL:
explain select * from t_player_activate_list where channelCode = '1' and gameCode = '1' and activateTime <= now();
执行结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t_player_activate_list ref idx_playeractivatelist_gamecode,idx_playeractivatelist_channelcode,idx_playeractivatelist_c_g_a idx_playeractivatelist_gamecode 194 const 1 Using where
有点长,不知道这有看你会不会觉得乱,如果觉得乱,我就再重新整理下,看能不能整理的简洁一点
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t_player_activate_list 0 PRIMARY 1 id A 100535 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 1 playerCode A 100535 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 2 gameCode A 100535 \N \N BTREE
t_player_activate_list 0 idx_playeractivatelist_p_g_c 3 channelCode A 100535 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_gamecode 1 gameCode A 100535 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_channelcode 1 channelCode A 100535 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 1 channelCode A 100535 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 2 gameCode A 100535 \N \N BTREE
t_player_activate_list 1 idx_playeractivatelist_c_g_a 3 activateTime A 100535 \N \N BTREE