経緯とか
業務で検索機能を実施する際に
1ヶ月待っても上から話が降りてこなかった為
とりあえず自分のできる範囲&時間的コストをかけずに実装できないか考えた結果、タイトルに至る。
環境
- Docker
- MySQL 5.7.24 (Docker_image: mysql:5.7)
- 実際に稼働するのはAWS Aurora
- 現在(2019/08/24)でAuroraがMySQL5.7との強い互換性がある為versionは5.7
構築
Dockerでコンテナ取ってくるまでは省く。
テーブル作成
だいぶ簡略化しますがこんな感じのテーブル
CREATE TABLE `search_person` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`tel` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
;
ENGINEはInnoDB。CHARSETとかは元のテーブルに合わせてある。
この時点ではまだ,FULLTEXT INDEXは貼らない方が良さげ。
→データインポート時に重すぎてMySQLが止まる。(→inport方法による?)
データ投入
サンプルデータをCSVで用意し、とりあえず50万件投入。
コンテナ内にCSVファイルをコピーしてからLOAD DATAで取り込む。
LOAD DATA INFILE '/var/lib/mysql-files/search_person_sample.csv' INTO TABLE search_person FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (name,address,tel);
一応
TERMINATED BY ','でカラムの区切り
ENCLOSED BY '"'でカラムの囲い文字
LINES TERMINATED BY '\r\n'で1レコードの区切りを表す
index作成
ALTER TABLE search_person ADD FULLTEXT KEY search_test (`name`, `address`, `tel`) WITH PARSER ngram;
検索に使うカラムで作成。
パーサーはN-gram。
何故?となる人もいるかもだがAuroraではどうやらパーサーにMeCabを指定できないらしい。(いずれサポートされるかも?)
index確認
SHOW TABLE STATUS LIKE 'search_person'\G
SET GLOBAL innodb_ft_aux_table = 'DBNAME/search_person';
SELECT word, doc_count, doc_id, position FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ORDER BY doc_id, position LIMIT 5;
innodb_ft_aux_tableに対処テーブル設定することでINNODB_FT_INDEX_TABLEで確認可能になる。
検証
-- 比較用のLIKE文
SELECT SQL_NO_CACHE COUNT(*) FROM search_person WHERE name LIKE '%tanaka%';
-- 全文検索
SELECT SQL_NO_CACHE COUNT(*) FROM search_person WHERE MATCH(name,address,tel) AGAINST('tanaka' IN BOOLEAN MODE);
噂には聞いてたが、確かに平均10倍くらい早い気がする。
しかもlikeの方はnameだけなのでaddressとtel含めたらもっと早いのでは?
課題とまとめ
問題なく実装はできたが以下の課題があった。
めちゃおそクエリがある
件数を増やしたり、複数キーワードや日本語での検索時にクエリ時間がやたら伸びたりした。
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM search_person WHERE MATCH(name,address,tel) AGAINST('東京都 渋谷区' IN BOOLEAN MODE);
+----------+
| COUNT(*) |
+----------+
| 153823 |
+----------+
1 row in set, 1 warning (31.66 sec)
調べたが明確な理由まではまだわかってない。
ただそもそも件数が多すぎる場合はInnoDBだけではどう頑張っても速度は出なさそうなので別途検索エンジンの導入をすべきだろう。
なので今回は実運用に組み込むことはないだろう。
件数にもよるが数万件程度のボリュームなら間違いなくコスパがいいので、サービス次第ではありだと思う。
またMeCabで形態素解析を使ったパーサーが利用できればより速度が出るだろう。
Tips
データ登録やindex作成時に落ちる
事象と原因
クエリを投げるとERROR 2013 (HY000): Lost connection to MySQL server during queryと言われ、mysqlとの接続(というかコンテナ自体)が落ちる。
原因調べた結果重たいクエリを受け止めきれず落ちてるっぽい。
設定値としてはmax_allowed_packetとinnodb_buffer_pool_sizeあたり
max_allowed_packetはパケットのサイズ上限を指定している。MySQLクライアントとmysqldサーバーのどちらにも設定があるが大体mysqldサーバー側をいじればいいっぽい。
innodb_buffer_pool_sizeはどれだけのデータとindexをメモリ上にキャッシュするかの設定値。キャッシュ出来ないだけなら動く気がしましたが。。。また詳しく調べて追記します。
対策
my.cnfをいじれば行けそう
まずどこのmy.cnf見に行ってるか確認
mysql --help | grep my.cnf
出てきたディレクトリでmy.cnfの作成or追記
[mysqld]
max_allowed_packet = 1G
innodb_buffer_pool_size = 1G
どれだけ割り当てるかは。。。やってみてって感じですかね?
innodb_buffer_pool_sizeの最適解は全データと同等だとか。。。多すぎない?