性能优化--表索引优化

先写总结：每张表的聚簇索引（主键上自动加）很珍贵，不一定要用在ID上，可将ID列改为唯一约束。将常查询的M个列中，N个可以联合保证唯一性的列作为主键（其上将默认加聚簇索引，N尽量取到最大），其余M-N个列加非聚簇索引。查询效果很好。

详细例子：

引自：http://www.csdn.net/article/2014-04-28/2819531/2

对于每个表来讲，聚集索引只有一个，利用好了，查询速度会有意想不到的提升效果。

以MySql为例，InnoDB选取聚集索引参照列的顺序是

1）如果声声明了主键（primarykey），则这个列会被做为聚集索引；

2）如果没有声明主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚集索引；

3）上面二个条件都不满足，InnoDB会自己产生一个虚拟的聚集索引。

[java] view plain copy

1. CREATETABLE`timeline_raw`(  
2.   
3. `rawId`bigint(20)NOTNULLAUTO_INCREMENT,  
4.   
5. `uid`bigint(20)DEFAULTNULL,  
6.   
7. `did`bigint(20)DEFAULTNULL,  
8.   
9. `channelId`char(1)NOTNULLDEFAULT'1'COMMENT'1:qvga;2:720p',  
10.   
11. `fileId`bigint(20)DEFAULTNULL,  
12.   
13. `sectionId`bigint(20)DEFAULTNULL,  
14.   
15. `headerFilePath`varchar(120)DEFAULTNULL,  
16.   
17. `startTime`bigint(20)DEFAULTNULL,  
18.   
19. `endTime`bigint(20)DEFAULTNULL,  
20.   
21. `updateTime`datetimeDEFAULTNULL,  
22.   
23. `createTime`datetimeDEFAULTNULL,  
24.   
25. PRIMARYKEY(`rawId`),  
26.   
27. KEY`index_uid_did_startTime`(`uid`,`did`,`startTime`)USINGBTREE,  
28.   
29. KEY`index_uid_did_endTime`(`uid`,`did`,`endTime`)USINGBTREE,  
30.   
31. KEY`index_time`(`startTime`)USINGBTREE,  
32.   
33. KEY`index_uid_did_fileId`(`uid`,`did`,`sectionId`)USINGBTREE,  
34.   
35. KEY`index_sectionId`(`sectionId`)  
36.   
37. )ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8  

这个表有四个索引：主键rawId、sectionId、`uid`,`did`、startTime。

项目的iBatis2中有这样一条查询语句：

[java] view plain copy

1. <selectid="getRawFileList"parameterClass="java.util.HashMap"resultClass="com.defonds.mysql.raw.entity.TimelineRaw">  
2.   
3. SELECT*FROMtimeline_raw_  
4.   
5. WHEREuid=#uid#  
6.   
7. ANDdid=#did#  
8.   
9. ANDchannelId=#channelId#  
10.   
11. <isNotNullproperty="sectionId">ANDsectionId=#sectionId#</isNotNull>  
12.   
13. AND  
14.   
15. (  
16.   
17. (startTimeBETWEEN#startTime#and#endTime#)  
18.   
19. OR  
20.   
21. (endTimeBETWEEN#startTime#and#endTime#)  
22.   
23. OR  
24.   
25. (  
26.   
27. <![CDATA[  
28.   
29. startTime<=#startTime#  
30.   
31. ]]>  
32.   
33. AND  
34.   
35. <![CDATA[  
36.   
37. endTime>=#endTime#  
38.   
39. ]]>  
40.   
41. )  
42.   
43. )  
44.   
45. ORDERBYstartTime;  
46.   
47. </select>  

根据实际业务向timeline_raw表注入一千万条数据，进行模拟测试，发现getRawFileList的执行平均时间为160ms以上。这是不能接受的。

考虑到实际业务中对于主键rawId查询条件甚少，我们把rawId主键索引取消掉，改为唯一约束，却把sectionId+startTime+endTime作为主键（业务上能够保证其唯一性，根据InnoDB索引规则，这个索引将成为我们新表的聚集索引）。然后把sectionId、startTime两个索引也取消掉，仅保留`uid`,`did`索引。

这样子，我们新表的索引实际上只有两个了：一个聚集索引(sectionId+startTime+endTime)一个非聚集索引(`uid`,`did`)。

再次进行模拟测试，同样的数据、数据量，同样的查询结果集，getRawFileList执行平均时间已经降到了11ms。结果是令人振奋的，不是么？