MySQL的List分区表

MySQL分区表简介
http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html#partitioning-list

今天有一个需求,审核图片是否违规,
需要查询尚未审核的图片.
其中status 1表示尚未审核;0表示已经审核;-1表示未通过审核

初始化实验数据:

1. drop table if exists t;
   
2. create table t(
   
3.     id int auto_increment primary key,
   
4.     pictureURL varchar(10),
   
5.     status int,
   
6.     createtime datetime
   
7. );
   
8. 
   
9. drop procedure if exists initData;
   
10. 
    
11. delimiter $$
    
12. create procedure initData()
    
13. begin
    
14.     set @index=0;
    
15.     while @index<10000000 do
    
16.         insert into t(createtime) values(now()-interval @index second);
    
17.         set @index:=@index+1;
    
18.     end while;
    
19.     commit;
    
20. end$$
    
21. delimiter ;
    
22. 
    
23. call initData();
    
24. update t set status=0;
    
25. update t set status=1 where mod(id,9)=0;
    
26. commit;

实验假设表中有1kw记录,尚未通过审核的数据占全部数据的十分之一左右.


现在需要查询这些尚未通过审核的数据信息。
这种需求还是很常见的.

如果是Oracle数据库,可以使用函数索引解决这个问题
http://blog.itpub.net/29254281/viewspace-775791/

但是很明显,MySQL没有这个功能.
由于结果集很大,MySQL会倾向于全表扫描这个表.效率很低.

这种情况下,即使status字段创建索引也没有任何效果,反而降低性能.

这个时候,可以考虑使用List分区表处理.
但是status要作为主键的一部分,这样只能是联合主键,对于使用Hibernate的项目,这个改动还是很烦人的.
(分区的字段必须是主键的一部分)

1. drop table if exists t1;
   
2. create table t1(
   
3.         id int auto_increment,
   
4.         pictureURL varchar(10),
   
5.         status int,
   
6.         createtime datetime,
   
7.     primary key(id,status)
   
8. )
   
9. partition by list(status)
   
10. (
    
11.     partition s0 values in (0),
    
12.     partition s1 values in (1),
    
13.     partition s2 values in (2),
    
14.     partition other values in (null)
    
15. );
    
16. 
    
17. insert into t1 select * from t;
    
18. commit;

使用List分区表之后,该场景的性能大幅提升

主要是应用了分区消除,从原来的全表扫描,变成了全分区扫描.



这样扫描的数据从689M降为了57M

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1281084/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1281084/