hoge

ブックマークに登録するのは良いが、一覧できるページにした時、大量のデータの中から登録した人のIDを頼りに探索するとなるとレスポンスがどれだけ掛かるのかは重要な問題である。 実際の環境に近い状態で検証するために、大量のテストデータが必要になったので、SQLで生成する。 ブックマークのデータベースだけではなく、記事のデータベースや記事が掲載されているブログのデータベース、ブログを所有しているメンバーのデータベースが必要になる。 メンバーIDとブログIDはテーブル上で必ず一意に定まり、記事は投稿されるごとに自動採番されるものとする。 まずメンバーのテーブルで1つだけIDを生成する。 ```sql INSERT INTO member(member_id) VALUES(SUBSTRING(MD5(RAND()), 1, 3)); ``` これを元にIDが重複しないように生成できれば良かったのだが、ストアドプロシージャでループが上手くいかなかったので手作業でphpmyadminからポチポチして生成した。 ```sql INSERT INTO member(member_id) SELECT SUBSTRING(MD5(RAND()), 1, 3) FROM member ``` メンバーIDの他に自動採番でナンバーをつけるのだが、これだと番号が抜けたりするのであまりよろしくない。あと普通に重複する可能性がある。 重複しない程度(本来想定は3万だが、今回は500)生成し、メンバーが持つブログIDをブログテーブルに用意する。1ユーザあたり1ブログしか持たないこととした。 ```sql INSERT INTO blog( blog_id, blog_title, member_id ) SELECT SUBSTRING(MD5(RAND()), 1, 4), NULL, member_id FROM member; ``` これでブログIDとメンバーIDが1対1で対応するようになるので、ここでの重複はできない(はず)。 ユーザはブログにいくつかの記事を投稿してるはずなので、次のSQL文でブログあたりの記事を生成する。 ```sql INSERT INTO entry( adate, member_id, blog_id, entry_id, entry_title, entry_body, entry_body_more, entry_excerpt, entry_reserve ) SELECT ADDTIME(CONCAT_WS(' ','2018-01-01' + INTERVAL RAND() * 364 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))), member_id, blog_id, NULL, 'title', 'body', NULL, 'excerpt', ADDTIME(CONCAT_WS(' ','2018-01-01' + INTERVAL RAND() * 364 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))) FROM blog; ``` 記事の中身は今は関係ないので、適当に。 最後にそれらの記事を複数ユーザがブックマークしたとする。 ```sql INSERT INTO bookmark( adate, bookmarked_member_id, blog_id, entry_id, entry_title, entry_excerpt ) SELECT ADDTIME(CONCAT_WS(' ','2019-01-01' + INTERVAL RAND() * 364 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))), SUBSTRING(MD5(RAND()), 1, 4), blog_id, entry_id, entry_title, entry_excerpt FROM entry; ``` 記事をブックマークしたユーザがさらに同じ記事をブックマークするのは問題なので、ブックマークしたメンバーIDと記事IDを紐づけて完了。 メンバーID(500) * ブログID(1) * ブログ1つあたりの記事数(20) * 記事1つあたりのブックマーク数(20)と考えると、これだけで20万のブックマーク数が得られる。 ## 速度検証 速度検証の際は上記のデータではなく、同じブックマークテーブルで適当なデータを800万行ほどで検証した。 適当に選んだbookmarked_member_idでは、100件ほどのentry_idが紐付いていた。実際にブラウザで表示されるまでにどれくらい掛かるのか見たところ、インデックスなしでは10件表示するだけでも5秒ほどかかった。 ``` SELECT * FROM bookmark WHERE bookmarked_member_id = "0000" ORDER BY adate DESC LIMIT 10 ``` そこで、bookmarked_member_idとentry_idに複合インデックスを付けたところ、表示は1秒弱から2秒弱の間で収まるようになった。 phpmyadmin上で同様のSQLを実行するとインデックスなしでは5秒弱かかっていたところ、ミリ秒ほどにまで低下した。 そのため単純にテーブルのデータが多い場合でも適切なインデックスがあればほとんどレスポンスには影響しないことが分かった。