100分请人帮忙建索引！大家快来help me!!!

Question

数据库为mysql,所有的查询条件结合如下：
EXPLAIN 
SELECT 
id,post,myname,mysex,xueli,born_year,image,sendtime 
FROM perresume 
WHERE 
1=1 
and mysex=0 
and ( place=1 or place=2 or place=3 or place=4 or place=5) 
and ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) 
and ( pro_a=12 or pro_a=7 or pro_a=11 or pro_a=6) 
and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38 
and ( (JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=12 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
and image is not null 
and post LIKE '%业务%' 
and TO_DAYS(sendtime)>=TO_DAYS(2008-04-22) 
and showstate=1 
order by sendtime desc LIMIT 0,20
但是有可能用户没有选择这些条件，或者只选择了其中的任意项，或多项！
mysex为 int 类型
place为 int 类型
xueli为 int 类型
born_year为 varchar字串类型，存储的日期格式yyyy-mm-dd
JobMainClass为 int 类型
JobSubClass为 char类型
image为 varchar类型
sendtime为日期类型
showstate为 int 类型请帮助我建一下索引，谢谢！回复格式请直接以：
KEY `ssssss` (`pro_b`,`JobMainClass`,`JobSubClass`,`sendtime`)这种形式回复，thank's!

Answer 1

补充：and ( pro_a=12 or pro_a=7 or pro_b=11 or pro_b=6) 
还有一pro_a与pro_b的过滤，就是专业大类跟小类。都是int 类型

Answer 2

1.将所有的Or,都改成in2.专业最好写个函数3、建立主键，在关键的字段上建立索引

Answer 3

( place=1 or place=2 or place=3 or place=4 or place=5)->
PLACE-1>=0( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) ->
XUELI-2>=0( pro_a=12 or pro_a=7 or pro_a=11 or pro_a=6)->
pro_a-6>=0JobMainClass上建立索引

Answer 4

TO WWWWA 是建立复合索引还是单列索引？

Answer 5

这个place与lueli、pro_a、pro_b是复选框模式，是跟所用户的选择来组装的sql,本身我不知道这个是什么条件！
所以没办法写成你讲的这种吧？

Answer 6

满足你的要求，而且精简SQL语句，如PLACE-1>=0 ，则PLACE必然是＞＝1的数
根据你的SQL语句，单列索引

Answer 7

如PLACE只能是1－5
则修改
PLACE-1>=0 －＞
PLACE-1　BETWEEN　0 　AND　4
OR
PLACE　BETWEEN　1　AND　4

Answer 8

谢谢 wwwwa, place的选项为1到7，xueli的选项为1至8
pro_a与pro_b为系统自增的id,所以不知道有多少我是不是把place与xueli的条件改成PLACE BETWEEN 1 AND 7？

Answer 9

这个是多选的，比如place的值为1,2,3,4,5,那么用户就选择了前五项，也有可能是1,3,4,5,7,等等情况

Answer 10

请看组装条件的存储过程：
CREATE PROCEDURE `search_resume`(sex INT,place varchar(20),edu varchar(80),pro varchar(45),ageStart INT,ageEnd INT,workStartDate INT,workEndDate INT,job VARCHAR(80),photo INT,keyword varchar(45),sendTime INT)
BEGIN
#组装条件
DECLARE sb TEXT DEFAULT '1=1 ';
#要取的列
DECLARE str TEXT DEFAULT 'SELECT id,post,myname,mysex,xueli,born_year,image,sendtime FROM perresume WHERE ';
#专业循环需要用到的变量
DECLARE proSize INT DEFAULT 0;
#求职类别循环需要用到的变量
DECLARE jobSize INT DEFAULT 0;
DECLARE placeSize INT DEFAULT 0;
DECLARE eduSize INT DEFAULT 0;
#判断是否过滤性别
IF sex > -1 AND sex < 2 THEN
SET sb = CONCAT(sb,'and mysex=',sex);
END IF;
#过滤所在地区
IF LENGTH(place)>0 THEN
#SET sb = CONCAT(sb,' and (SELECT FIND_IN_SET(place,cost_location) FROM com_search_table where cost_comId=',comId,')');
SET sb = CONCAT(sb,' and (');
WHILE INSTR(place,',')>0 DO
IF placeSize=0 THEN
SET sb = CONCAT(sb,' place=',SUBSTRING(LEFT(place,INSTR(place,',')-1),1));
ELSE
SET sb = CONCAT(sb,' or place=',SUBSTRING(LEFT(place,INSTR(place,',')-1),1));
END IF;
SET placeSize = placeSize+1;
SET place = SUBSTRING(place,INSTR(place,',')+1);
END WHILE;
SET sb = CONCAT(sb,')');
END IF;
#过滤学历
IF LENGTH(edu)>0 THEN
#SET sb = CONCAT(sb,' and (SELECT FIND_IN_SET(xueli,cost_edu) FROM com_search_table where cost_comId=',comId,')');
SET sb = CONCAT(sb,' and (');
WHILE INSTR(edu,',')>0 DO
IF eduSize=0 THEN
SET sb = CONCAT(sb,' xueli=',SUBSTRING(LEFT(edu,INSTR(edu,',')-1),1));
ELSE
SET sb = CONCAT(sb,' or xueli=',SUBSTRING(LEFT(edu,INSTR(edu,',')-1),1));
END IF;
SET eduSize = eduSize+1;
SET edu = SUBSTRING(edu,INSTR(edu,',')+1);
END WHILE;
SET sb = CONCAT(sb,')');
END IF;
#过滤专业
IF LENGTH(pro)>0 THEN
SET sb = CONCAT(sb,' and (');
WHILE INSTR(pro,',')>0 DO
IF LEFT(pro,1)='A' THEN
IF proSize=0 THEN
SET sb = CONCAT(sb,' pro_a=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
ELSE
SET sb = CONCAT(sb,' or pro_a=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
END IF;
ELSE
IF proSize=0 THEN
SET sb = CONCAT(sb,' pro_b=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
ELSE
SET sb = CONCAT(sb,' or pro_b=',SUBSTRING(LEFT(pro,INSTR(pro,',')-1),2));
END IF;
END IF;
SET proSize = proSize+1;
SET pro = SUBSTRING(pro,INSTR(pro,',')+1);
END WHILE;
SET sb = CONCAT(sb,')');
END IF;
#过滤年龄
IF ageStart>16 OR ageEnd<60 THEN
SET sb = CONCAT(sb,' and (TO_DAYS(now())-TO_DAYS(born_year))/365>=',ageStart,' and (TO_DAYS(now())-TO_DAYS(born_year))/365<=',ageEnd);
END IF;
#过滤工作经验
IF workStartDate >1 OR workEndDate<8 THEN
SET sb  = CONCAT(sb,' and WorkYear>=',workStartDate,' and WorkYear<=',workEndDate);
END IF;
#过滤求职类别
IF LENGTH(job)>0 THEN
SET sb = CONCAT(sb,' and (');
WHILE INSTR(job,',')>0 DO
IF LEFT(job,1)='A' THEN
IF jobSize=0 THEN
SET sb = CONCAT(sb,' JobMainClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),2));
ELSE
SET sb = CONCAT(sb,' or JobMainClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),2));
END IF;
ELSE
IF jobSize=0 THEN
SET sb = CONCAT(sb,' (JobMainClass=',LEFT(job,INSTR(job,'B')-1),' and JobSubClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),INSTR(job,'B')+1),')');
ELSE
SET sb = CONCAT(sb,' or (JobMainClass=',LEFT(job,INSTR(job,'B')-1),' and JobSubClass=',SUBSTRING(LEFT(job,INSTR(job,',')-1),INSTR(job,'B')+1),')');
END IF;
END IF;
SET jobSize = jobSize+1;
SET job = SUBSTRING(job,INSTR(job,',')+1);
END WHILE;
SET sb = CONCAT(sb,')');
END IF;
#是否有相片
IF photo=1 THEN
SET sb = CONCAT(sb,' and image is not null');
END IF;
#关键字过滤
IF LENGTH(keyword)>0 THEN
SET sb = CONCAT(sb," and post LIKE '%",keyword,"%'");
END IF;
#过滤简历刷新日期
IF sendTime > 0 THEN
SET sb = CONCAT(sb,' and TO_DAYS(sendtime)>=TO_DAYS(',CURDATE() - INTERVAL sendTime DAY,')');
END IF; SET sb = CONCAT(sb,' and showstate=1');
SET str = CONCAT(str,sb,' order by sendtime desc LIMIT 0,20');
SELECT str;
/*SET @sql:= str;
PREPARE exec FROM @sql;
EXECUTE exec;
DEALLOCATE PREPARE exec;*/
END

Answer 11

yesplace　BETWEEN 1 AND 7　满足条件

Answer 12

( place=1 or place=2 or place=3 or place=4 or place=5)-> 
PLACE-1>=0 ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) -> 
XUELI-2>=0 ( pro_a=12 or pro_a=7 or pro_a=11 or pro_a=6)-> 
pro_a-6>=0  
这三个条件可以直接写，只要在储存过程中判断一下就可以了

Answer 13

小弟愚昧，没怎么明白大哥的意思，如果用户随便选择1,5,6
那传入存储过程的place值即为：1,5,6,
我存储过程自动组装的语句是这样的：
#过滤所在地区
    IF LENGTH(place)>0 THEN
        SET sb = CONCAT(sb,' and (');
        WHILE INSTR(place,',')>0 DO
            IF placeSize=0 THEN
                SET sb = CONCAT(sb,' place=',SUBSTRING(LEFT(place,INSTR(place,',')-1),1));
            ELSE
                SET sb = CONCAT(sb,' or place=',SUBSTRING(LEFT(place,INSTR(place,',')-1),1));
            END IF;
            SET placeSize = placeSize+1;
            SET place = SUBSTRING(place,INSTR(place,',')+1);
        END WHILE;
        SET sb = CONCAT(sb,')');
    END IF;
您的意思是如何来组装这个条件呢？

Answer 14

IF LENGTH(place)>0 THEN 
place　BETWEEN 1 AND 7
END IF; 
可以考虑去掉循环部份，当然，我不知道与其它的部份有无联系

Answer 15

我调用存储过程的例子如下：
call search_resume(0,'1,2,3,4,5,','2,3,4,5,6,','A12,A7,B11,B6,',16,38,1,8,'1B0001,1B0004,12B0007,A3,A7,',1,'业务',7)

Answer 16

与调用值没有关系，只要判断
IF LENGTH(place)>0 THEN 
place　BETWEEN 1 AND 7
END IF;  
就可以直接赋值了

Answer 17

呵呵，传入的place格式是2,3,4,5,6,这种形式的哦，不是单选，是复选。

Answer 18

2,3,4,5,6是PLACE的值吧，如是
只要判断
IF LENGTH(place)>0
就可以直接赋值了，就是这个意思。

Answer 19

不行的，大哥，你把我都搞蒙了，呵呵！！！
place　BETWEEN 1 AND 7的意思是：place这个字段中满足任意1至7的值的数据都行，那这个条件有什么意思呢？
如果用户选择了 1,3,5呢？select '1,3,4' BETWEEN 1 AND 7等返回的都为1，其实我只想显示1,3,4类别的数据

Answer 20

我是根据你的SQL语句提出建议的，没有看到整体，索引太多，影响插入速度，要权衡一下

Answer 21

这张表的insert与update都是单列操作，没有批量的操作，对插入与更新影响应该不是非常大吧！

Answer 22

thank's WWWWA，回头请你看MM，呵呵偶滴神呀，那如何才能两全！！！查询速度是一定要解决的！！
希望大家给些建这些复合索引的建议！！！

Answer 23

EXPLAIN 的结果贴出来看看，什么地方耗时多

Answer 24

感谢版版与wwwwb大哥驾临，呵呵我现在建了一个索引:
 KEY `adv_alls` (`mysex`,`place`,`xueli`,`pro_a`,`pro_b`,`born_year`,`JobMainClass`,`JobSubClass`,`sendtime`)
然后：
EXPLAIN 
SELECT 
id,post,myname,mysex,xueli,born_year,image,sendtime 
FROM perresume 
WHERE 1=1 
and mysex=0 
and ( place=1 or place=2 or place=3 or place=4 or place=5) 
and ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) 
and ( pro_a=12 or pro_a=7 or pro_b=11 or pro_b=6) 
and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38 
and ((JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=12 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
order by sendtime desc 
LIMIT 0,20
为什么还会有Using filesort的提示呢？它还会额外的进行排序！

Answer 25

对了版版，你讲把or改成UNION ALL，效率应该赶不上or吧，在我印象中UNION ALL很慢的呢。。

Answer 26

EXPLAIN 上面的SQL，索引是用到了adv_alls，但是结果的type为ref，表示是不是并不理想？而且还出现了Using filesort问题。哎！这些索引倒底要如何建才是好呀！！

Answer 27

如果用了order by就可能出现using filesort
在sendtime上建立索引试试

Answer 28

以下信息为引用:在explain我们所使用的sql的时候，经常会遇到using filesort这种情况，原以为是由于有相同列值的原因引起，结果昨天看到公司的一个sql，跟同事讨论了下加上自己又做了一些测试，突然发现自己原来的想法是错误的。首先，只有在order by 数据列的时候才可能会出现using filesort，而且如果你不对进行order by的这一列设置索引的话，无论列值是否有相同的都会出现using filesort。因此，只要用到order by 的这一列都应该为其建立一个索引。其次，在这次测试中，使用了一个稍微有点复杂的例子来说明这个问题，下面详细用这个例子说一下：SELECT * FROM DB.TB WHERE ID=2222 AND FID IN (9,8,3,13,38,40) ORDER BY INVERSE_DATE LIMIT 0, 5
里面建立的索引为一个三列的多列索引：IDX（ID,FID ,INVERSE_DATE） 。INVERSE_DATE这个是时间的反向索引。对于这个sql我当时最开始认为应该是个优化好的状态，应该没有什么纰漏了，结果一explain才发现竟然出现了：Using where; Using filesort。为什么呢，后来经过分析才得知，原来在多列索引在建立的时候是以B-树结构建立的，因此建立索引的时候是先建立ID的按顺序排的索引，在相同ID的情况下建立FID按 顺序排的索引，最后在FID 相同的情况下建立按INVERSE_DATE顺序排的索引，如果列数更多以此类推。有了这个理论依据我们可以看出在这个sql使用这个IDX索引的时候只是用在了order by之前，order by INVERSE_DATE 实际上是using filesort出来的。。汗死了。。因此如果我们要在优化一下这个sql就应该为它建立另一个索引IDX（ID,INVERSE_DATE），这样就消除了using filesort速度也会快很多。问题终于解决了。

Answer 29

ORDER BY 本来就是很消耗资源的。
假如你经常ORDER BY 的话。建议增加你的SORT_BUFFER_SIZE的值。
网友回复:SORT_BUFFER_SIZE值的调整有什么讲究吗？
网友回复:看你的这个状态。假如一直增加的话就需要增加sort_buffer_size
mysql> show status like 'Sort_merge_passes';
------------------- -------
¦ Variable_name ¦ Value ¦
------------------- -------
¦ Sort_merge_passes ¦ 0 ¦
------------------- -------
1 row in set (0.00 sec) 

Answer 30

sendtime有建立单列索引的，但是无效！
呵呵，而且你引用的那个例子我看了好几遍了，也尝试过了，但是没有摆脱~~~ :（:（:（:（

Answer 31

呵呵，去掉ORDER　BY看看速度如何

Answer 32

去掉order by肯定速度不错啥，非常快！加上order by就不行了对了，请教大哥，索引建立原则讲尽量将索引建立在重复数据少的数据列中，唯一所以最好
例如：生日列，可以建立索引，但性别列不要建立索引那我这个复合索引，如果不建立mysex的索引，那最左前缀原则不是不成立了？这样的话，我是否可以将这个复合索引的列mysex、place、xueli、pro_a、pro_b去掉？但是如果去掉这些列的索引的话，那么这些列的条件是放到有索引列的前面好呢还是放在有索引列的后面好呢？
如放在前面：
WHERE 1=1 
    and mysex=0 
    and ( place=1 or place=2 or place=3 or place=4 or place=5) 
    and ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) 
    and ( pro_a=12 or pro_a=7 or pro_b=11 or pro_b=6) 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38 
    and ((JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=12 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
这样好些？还是放到后面要好些：
WHERE 1=1 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38 
    and ((JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=12 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
    and mysex=0 
    and ( place=1 or place=2 or place=3 or place=4 or place=5) 
    and ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) 
    and ( pro_a=12 or pro_a=7 or pro_b=11 or pro_b=6) 

Answer 33

还是要mysex索引好一些，问题主要在ORDER　BY上，将sendtime加入复合索试试

Answer 34

有加呢，呵呵：
KEY `adv_alls` (`mysex`,`place`,`xueli`,`pro_a`,`pro_b`,`born_year`,`JobMainClass`,`JobSubClass`,`sendtime`)
单列也建了的，但是没用得

Answer 35

select * from (
SELECT 
    id,post,myname,mysex,xueli,born_year,image,sendtime 
FROM perresume 
WHERE 
    1=1 
    and mysex=0 
    and ( place=1 or place=2 or place=3 or place=4 or place=5) 
    and ( xueli=2 or xueli=3 or xueli=4 or xueli=5 or xueli=6) 
    and ( pro_a=12 or pro_a=7 or pro_a=11 or pro_a=6) 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
    and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38 
    and ( (JobMainClass=1 and JobSubClass=0001) or (JobMainClass=1 and JobSubClass=0004) or (JobMainClass=12 and JobSubClass=0007) or JobMainClass=3 or JobMainClass=7) 
    and image is not null 
    and post LIKE '%业务%' 
    and TO_DAYS(sendtime)>=TO_DAYS(2008-04-22) 
    and showstate=1 )
order by sendtime desc LIMIT 0,20
这样如何？

Answer 36

EXPLAIN 
select * from (SELECT id,post,myname,mysex,xueli,born_year,image,sendtime  FROM perresume  WHERE  1=1  and mysex=0)  as temps order by temps.sendtime desc LIMIT 0,20慢得很，单一执行里面的select有用到索引adv_alls，速度相当的快，但是加上外层的select后，执行EXPLAIN命令都相当的慢，我想原回是因为里层的结果为temps，这个temps根本就没有任何的索引，它不可能会用到perresume表的sendtime索引，你说对吧？

Answer 37

索引只对FROM perresume....有效，对from(......)as temp没有任何效果吧！所以这个order by是对temp的sendtime排序

Answer 38

我已经把
and (TO_DAYS(now())-TO_DAYS(born_year))/365>=16 
and (TO_DAYS(now())-TO_DAYS(born_year))/365<=38
优化成：
and born_year <= CURDATE()- INTERVAL 16 YEAR 
and born_year >= CURDATE()- INTERVAL 38 YEAR

Answer 39

目前如果不用order by的话，里面的查询条件是相当快的，就是order by会出现using filesort问题，sendtime没有用到索引，进行了额外的排序