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

今天有一個需求,稽核圖片是否違規,
需要查詢尚未稽核的圖片.
其中status 1表示尚未稽核;0表示已經稽核;-1表示未透過稽核

初始化實驗資料:

drop table if exists t;
create table t(
id int auto_increment primary key,
pictureURL varchar(10),
status int,
createtime datetime
);
drop procedure if exists initData;
delimiter $$
create procedure initData()
begin
set @index=0;
while @index<10000000 do
insert into t(createtime) values(now()-interval @index second);
set @index:=@index+1;
end while;
commit;
end$$
delimiter ;
call initData();
update t set status=0;
update t set status=1 where mod(id,9)=0;
commit;

實驗假設表中有1kw記錄,尚未透過稽核的資料佔全部資料的十分之一左右.

現在需要查詢這些尚未透過稽核的資料資訊。
這種需求還是很常見的.

如果是Oracle資料庫,可以使用函式索引解決這個問題
http://blog.itpub.net/29254281/viewspace-775791/

但是很明顯,MySQL沒有這個功能.
由於結果集很大,MySQL會傾向於全表掃描這個表.效率很低.

這種情況下,即使status欄位建立索引也沒有任何效果,反而降低效能.

這個時候,可以考慮使用List分割槽表處理.
但是status要作為主鍵的一部分,這樣只能是聯合主鍵,對於使用Hibernate的專案,這個改動還是很煩人的.
(分割槽的欄位必須是主鍵的一部分)

drop table if exists t1;
create table t1(
id int auto_increment,
pictureURL varchar(10),
status int,
createtime datetime,
primary key(id,status)
)
partition by list(status)
(
partition s0 values in (0),
partition s1 values in (1),
partition s2 values in (2),
partition other values in (null)
);
insert into t1 select * from t;
commit;

使用List分割槽表之後,該場景的效能大幅提升

主要是應用了分割槽消除,從原來的全表掃描,變成了全分割槽掃描.

這樣掃描的資料從689M降為了57M

MySQL的List分割槽表

相關文章