先写总结:每张表的聚簇索引(主键上自动加)很珍贵,不一定要用在ID上,可将ID列改为唯一约束。将常查询的M个列中,N个可以联合保证唯一性的列作为主键(其上将默认加聚簇索引,N尽量取到最大),其余M-N个列加非聚簇索引。查询效果很好。
详细例子:
引自:http://www.csdn.net/article/2014-04-28/2819531/2
对于每个表来讲,聚集索引只有一个,利用好了,查询速度会有意想不到的提升效果。
以MySql为例,InnoDB选取聚集索引参照列的顺序是
1)如果声声明了主键(primarykey),则这个列会被做为聚集索引;
2)如果没有声明主键,则会用一个唯一且不为空的索引列做为主键,成为此表的聚集索引;
3)上面二个条件都不满足,InnoDB会自己产生一个虚拟的聚集索引。
- CREATETABLE`timeline_raw`(
- `rawId`bigint(20)NOTNULLAUTO_INCREMENT,
- `uid`bigint(20)DEFAULTNULL,
- `did`bigint(20)DEFAULTNULL,
- `channelId`char(1)NOTNULLDEFAULT'1'COMMENT'1:qvga;2:720p',
- `fileId`bigint(20)DEFAULTNULL,
- `sectionId`bigint(20)DEFAULTNULL,
- `headerFilePath`varchar(120)DEFAULTNULL,
- `startTime`bigint(20)DEFAULTNULL,
- `endTime`bigint(20)DEFAULTNULL,
- `updateTime`datetimeDEFAULTNULL,
- `createTime`datetimeDEFAULTNULL,
- PRIMARYKEY(`rawId`),
- KEY`index_uid_did_startTime`(`uid`,`did`,`startTime`)USINGBTREE,
- KEY`index_uid_did_endTime`(`uid`,`did`,`endTime`)USINGBTREE,
- KEY`index_time`(`startTime`)USINGBTREE,
- KEY`index_uid_did_fileId`(`uid`,`did`,`sectionId`)USINGBTREE,
- KEY`index_sectionId`(`sectionId`)
- )ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8
这个表有四个索引:主键rawId、sectionId、`uid`,`did`、startTime。
项目的iBatis2中有这样一条查询语句:
- <selectid="getRawFileList"parameterClass="java.util.HashMap"resultClass="com.defonds.mysql.raw.entity.TimelineRaw">
- SELECT*FROMtimeline_raw_
- WHEREuid=#uid#
- ANDdid=#did#
- ANDchannelId=#channelId#
- <isNotNullproperty="sectionId">ANDsectionId=#sectionId#</isNotNull>
- AND
- (
- (startTimeBETWEEN#startTime#and#endTime#)
- OR
- (endTimeBETWEEN#startTime#and#endTime#)
- OR
- (
- <![CDATA[
- startTime<=#startTime#
- ]]>
- AND
- <![CDATA[
- endTime>=#endTime#
- ]]>
- )
- )
- ORDERBYstartTime;
- </select>
根据实际业务向timeline_raw表注入一千万条数据,进行模拟测试,发现getRawFileList的执行平均时间为160ms以上。这是不能接受的。
考虑到实际业务中对于主键rawId查询条件甚少,我们把rawId主键索引取消掉,改为唯一约束,却把sectionId+startTime+endTime作为主键(业务上能够保证其唯一性,根据InnoDB索引规则,这个索引将成为我们新表的聚集索引)。然后把sectionId、startTime两个索引也取消掉,仅保留`uid`,`did`索引。
这样子,我们新表的索引实际上只有两个了:一个聚集索引(sectionId+startTime+endTime)一个非聚集索引(`uid`,`did`)。
再次进行模拟测试,同样的数据、数据量,同样的查询结果集,getRawFileList执行平均时间已经降到了11ms。结果是令人振奋的,不是么?