裏MySQL MySQLでサンプリング あれこれ

mysql

MySQLでビックデータを扱う場合、データをサンプリングしたいことがある。MS-SQL Serverなどではサンプリング関数が用意されているが、MySQLにはない。

SELECT * FROM report ORDER BY RAND() LIMIT 5

と単純にクエリを書くこともできるが、ビックデータでこれをやるとMySQLがfilesortで死んでしまう。

SELECT * FROM 
  `report` rp,
   (SELECT ROUND(RAND()*@max) as report_id 
     FROM 
       `report`,
       (SELECT @max:=AUTO_INCREMENT 
          FROM information_schema.tables 
         WHERE TABLE_SCHEMA=database()  
           AND TABLE_NAME='report' 
       ) setup
     LIMIT 5 -- サンプリング数
   ) rs 
WHERE 
 rp.report_id=rs.report_id

そこで、プライマリキーがあってかつ、auto_incrementが設定されている場合は上のようにプライマリキーのMAX値を取ってきてその範囲内でランダムな値を生成するようにする。

ただし、これも問題があってプライマリキーに歯抜けや、auto_index値がおかしな値だった場合にうまくサンプルできない。auto_increment値が存在するレコードのプライマリキーのMAX値を超えている場合は以下のようにリセットする必要がある。

SET @MAX:=(SELECT MAX(report_id) FROM report)+1;
SET @SQL=CONCAT('ALTER TABLE report AUTO_INCREMENT = ',@MAX);

PREPARE init_auto_inc FROM @SQL;
EXECUTE init_auto_inc ;

SELECT * FROM 
  `report` rp,
   (SELECT @report_id:=@report_id+CEIL(RAND()*@max/5) as report_id 
     FROM 
       `report`,
       (SELECT @max:=report_id,@report_id:=0  
          FROM report  
         ORDER BY report_id desc 
         LIMIT 1 
       ) setup
     LIMIT 5    ) rs 
WHERE 
 rp.report_id=rs.report_id