こんにちは、千田です。

 

 

3月も中旬に差しかかかり、世のサラリーマンの方々の多くは決算月という事で大変慌ただしい毎日を

お過ごしなのかなと思います。

 

 

スワローの決算は11月なので、世のサラリーマンの方々よりは比較的落ち着いているのかなと思うので、

（仕事はちゃんとやってますよ！！）

 

今のうちに実験的な事も1つ、2つやりたいなーと思う今日この頃です。

 

 

 

という訳で（めちゃくちゃ唐突ですが）、今回は個人的に気になっていた

 

「Blob型に対してインデックスは効果があるのか」

 

というのを検証してみたいと思います。

 

 

検証方法

1. 画像をBase64エンコードしてBlob型のカラムに登録する
2. Blob型のカラムにインデックスを作成する前と作成した後で検索時間を比較する

 

 

環境

端末：MacBook Pro

CPU：2.3 GHz Intel Core i5

メモリ：16 GB 2133 MHz LPDDR3

MySQL バージョン：5.7.24

Node.jsバージョン：10.15.1

 

 

事前準備

テーブルの作成

では、まずは検証用のテーブルを作成します。インデックスはまだ作成しません。

 

[cc_sql]
create table images(
id int not null auto_increment,
image longblob,
file_name varchar(256),
primary key (id)
);
[/cc_sql]

 

file_nameには確認用に元のファイル名を入れてます。

 

 

Blobデータの準備

テーブルを作ったら画像データをBase64にエンコードしてINSERTしていきます。

今回は登録用にNode.jsで下記のようなスクリプトを作成しました。

 

regist.js

[cc_javascript]
const fs = require(‘fs’);
const db = require(‘mysql’);
const imageDir = ‘images/’;
const config = {
host: ‘127.0.0.1’,
user: ‘xxxx’,
password: ‘xxxxx’,
database: ‘xxxxx’,
insecureAuth: true,
};

const con = db.createConnection(config);
let sql = ‘insert into images (image,file_name) values ‘;

fs.readdir(imageDir, (err, files) => {
if (err) {
console.log(‘directory read error’);
} else {
files.forEach((file) => {
fs.readFile(imageDir + file, ‘base64’, (err, data) => {
if (err) {
console.log(‘file read error’);
} else {
let val = ‘(‘ + db.escape(data) + ‘,’ + db.escape(file) + ‘)’;
con.query(sql + val,(err, results, fields) => {
if (err) {
console.log(err);
console.log(‘db error’);
}
});
}
});
});
}
});
[/cc_javascript]

 

それではスクリプトを実行します。

 

[cc_bash]
$ node regist.js
[/cc_bash]

 

はい、これでBlobデータの準備ができました！

 

 

検証

データの準備ができたので、早速検証していきます！

 

前提条件

• レコード数：約31650件
• DBの場所：localhost

 

 

検証用スクリプトの準備

検証用のスクリプトもNode.jsで準備します。

 

search.js

[cc_javascript]
const fs = require(‘fs’);
const db = require(‘mysql’);
const imageDir = ‘images/’;
const imageFile = ‘[検索するファイル名]’;
const config = {
host: ‘127.0.0.1’,
user: ‘xxxx’,
password: ‘xxxxxxx’,
database: ‘xxxxxx’,
insecureAuth: true,
};

const con = db.createConnection(config);

fs.readFile(imageDir + imageFile, ‘base64’, (err, data) => {
if (err) {
console.log(‘file read error’);
} else {
// 検索開始
console.time(‘処理時間’);
con.query(‘select * from images where image = ‘ + db.escape(data) ,
(err, results, fields) => {
// 検索終了
console.timeEnd(‘処理時間’);
if (err) {
console.log(err);
console.log(‘db error’);
} else {
results.forEach((r) => {
console.log(r.id);
});
}
});
}
});
[/cc_javascript]

 

 

計測 その1（インデックスなし）

では、早速スクリプトを実行して計測してみます。

 

[cc_bash]
$ node search.js
処理時間: 8547.065ms
[/cc_bash]

 

1回目	8547.065ms
2回目	8506.852ms
3回目	8465.807ms

 

うーん、思った以上に時間がかかった印象です。。。こんなものなんですかね？

果たしてインデックスを作成するとどの程度変わるのか？？

 

 

インデックスの作成

期待を込めて、インデックスを作成します。

 

[cc_sql]
alter table images add index idx_image(image(255));
[/cc_sql]

 

 

計測その2（インデックスあり）

インデックスを作成したところで、早速検証します。

果たしてどの程度変わるのか？？

 

[cc_bash]
$ node search.js
処理時間: 75.239ms
[/cc_bash]

 

1回目	75.239ms
2回目	62.911ms
3回目	60.580ms

 

 

なんと結果としては10分の1以下に短縮されました。こんなにも変わるものなのですね！！

 

 

総括

というわけで、結果は一目瞭然で「効果あり」でした！

 

 

それでは！