mysql查询优化in_mysql 查询中in的优化

原来表结构：

HOST A:

album_friends | CREATE TABLE album_friends (

id int(11) NOT NULL,

ownerId int(11) NOT NULL,

ownerName varchar(255) collate utf8_bin NOT NULL,

title varchar(255) collate utf8_bin NOT NULL,

photo_count int(11) NOT NULL,

uptime datetime NOT NULL,

headurl varchar(255) collate utf8_bin NOT NULL,

univId int(11) NOT NULL,

univName varchar(255) collate utf8_bin NOT NULL,

tinyurl varchar(255) collate utf8_bin NOT NULL,

control int(11) NOT NULL default '0',

stage int(11) NOT NULL default '20',

time datetime NOT NULL default '1970-01-01 00:00:00',

mainurl varchar(255) collate utf8_bin NOT NULL default '',

PRIMARY KEY (id),

KEY album_fresh_uptime_index (uptime),

KEY ownerid (ownerId,uptime),

KEY univ (univId),

KEY stage (stage,uptime),

KEY univId (univId,time,photo_count)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_binsql

处理：

alter table album_friends drop index univId, drop index stage ,drop index univ,drop index album_fresh_uptime_index ,drop index ownerid,drop primary key ,add primary key (ownerId,id), add unique id (id),add index (uptime)

HOST B:

album_friends | CREATE TABLE album_friends (

id int(11) NOT NULL,

ownerId int(11) NOT NULL,

ownerName varchar(255) collate utf8_bin NOT NULL,

title varchar(255) collate utf8_bin NOT NULL,

photo_count int(11) NOT NULL,

uptime datetime NOT NULL,

headurl varchar(255) collate utf8_bin NOT NULL,

univId int(11) NOT NULL,

univName varchar(255) collate utf8_bin NOT NULL,

tinyurl varchar(255) collate utf8_bin NOT NULL,

control int(11) NOT NULL default '0',

stage int(11) NOT NULL default '20',

time datetime NOT NULL default '1970-01-01 00:00:00',

mainurl varchar(255) collate utf8_bin NOT NULL default '',

PRIMARY KEY (ownerId,id),

UNIQUE KEY uid (id),

KEY uptime (uptime)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

加索引并重启mysql后,目前看来效果不错~