MySQL的List分割槽表

壹頁書發表於2014-09-25
MySql
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. );

  9. drop procedure if exists initData;

  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 ;

  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. );

  17. insert into t1 select * from t;
  18. commit;
使用List分割槽表之後,該場景的效能大幅提升

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



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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1281084/，如需轉載，請註明出處，否則將追究法律責任。

相關文章