Rubyでuse_resultを使い、Mysqlから大量データをSELECTする

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

Rubyでuse_resultを使い、Mysqlから大量データをSELECTする

■ バックグラウンド処理でのMySQLの大量データの扱い

MySQLで大量データを扱うコツは、フロントエンドとバックグラウンドでは結構異なることがあります。

代表的なもので言えば、インデクスが挙げられます。

フロントエンド処理において大量データから必要なデータを抽出するためには、インデクスの作成がかかせません。

もしインデクスがなければ、テーブル内をフルスキャンを行わなければならず即時に応答を返すことができなくなります。

対してそのテーブルがバックグラウンド処理でしか使用されない場合、インデクスが必要不可欠かと言うとそうでもありません。

即時応答の必要がないからです。

インデクスがあることでテーブルの更新処理が遅くなり、足かせとなってしまう場合もあります。

今回はバックグラウンド処理でMySQLから大量データを取得する際のノウハウの一つが題材です。

■ MySQLでの大量データSELECT

数千万レコードレベル以上のデータが保存されているテーブルから全レコードを取得しなければいけない場合、

通常軽量言語で使用するMySQLライブラリのqueryメソッドで全レコードを取得するような処理を実行すると

データがメモリに乗りきれなくなり、Out Of Memoryエラーとなります。

回避策としてLIMIT・OFFSETを使って、例えば数千件程度ずつに区切ってデータ取得するといった方法もあるでしょう。

しかし、SELECTの途中でテーブルの内容が更新されたりした場合、並び順が変わってしまって正しく全件を取得できない可能性があります。

■ use_result

MySQLのC言語のライブラリにmysql_store_resultというメソッドが用意されています。

SELECTの結果すべてをメモリに乗せて取得するものです。フロントエンド処理で通常使用されているものはこの仕組みです。

対して、mysql_use_resultというメソッドがあります。

これはSELECTの結果を一度に取得せず、一行ずつ取得するものです。一度に使うメモリは一行分のみになるので省メモリとなります。

■ use_resultをRubyから使用するには？

今回はMySQL/Ruby(version. 2.9.1)を使用します。

以下のように使います。

#-*- coding: utf-8 -*-
require "rubygems"
require "mysql"

dbh = Mysql.new("localhost", "user", "passord", "database")
dbh.query("set net_write_timeout=9999") #注意点 後述
dbh.query_with_result = false #query()実行と同時にresultオブジェクトを返さなくなります。
dbh.query("select * from big_table") #大量データテーブルからのSELECT
res = dbh.use_result # use_resultを実行してresultオブジェクトを取得
res.each do |row| 
 #処理
end
res.free
dbh.close

■ 注意点

use_resultの便利な面を説明しましたが、少しクセがあります。

そのうち代表的なものをあげます。

長時間になる場合、timeoutとなり途中でSELECTが中断されてしまう。

net_write_timeoutは通常デフォルトが60秒となっています。

use_resultは一度に結果取得をしない分、net writeの時間が長くなりこのtimeoutを超えてしまうケースがあります。

そのため上記コード例でもあげたとおり、

set net_write_timeout=9999

などとしてtimeout値を伸ばしておく必要があります。

SELECTは中断できない。

一度はじめたSELECTは全件を取得し終えるまでresultオブジェクトを開放できません。

つまり、use_resultをしているDBハンドラは全件を取得するまで別の行動ができません。

どうしてもSELECTの途中でMySQLの別の処理を行う必要がある場合は、

もうひとつDBコネクションを作成し、作成したハンドラで処理を行います。