mysql 查询中in的优化

作/译者：陶会祥 来源：http://blog.chinaunix.net/u3/107145/ 欢迎转载,请注明作者及出处~

mysql　查询中in的优化:

上周未,相册的DB CPU突然升到80%,服务down机~

检查发现主要用两种ＳＱＬ慢查：

select count(1) from album_friends where photo_count > 0 and ownerid in ( '64490847','95589567','185688181',......) and control != -1;

desc select count(1) from album_friends where photo_count > 0 and ownerid in ( '64490847','95589567','185688181',......) and control != -1　order by id desc limit 0, 20;

监控如下：

qirong分析，这个值高了，是sql中有没用到索引的读飙了

上图表示应该出现不少的tmp table

原来表结构：

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后,目前看来效果不错~