なんの記事?
LTで登壇した内容をqiitaに書き起こしました｡
私は､メモやブックマークにタグを付けて登録するwebツールアプリを作って運用(?)しています｡
そのwebツールアプリのデータベースの構造をどうしてその仕様にしたのか､どう変化したのか､なぜ変化させたのかを書きます｡
当時の大まかな流れ
- 今現在の構造を見てもらう
- 一通り動きを見てもらう(どういう風にデータベースを使うのかイメージしてもらうため)
- 最初期の構造を見てもらう
- 今と最初期の構造を見比べての質疑応答など
前置き
メモとブックマークのデータベースが別々で存在するが､ほぼほぼ同じ仕様､同じ変化をしたので､今回は､ブックマークのデータベースの説明は省略する｡
他説明に関係なさそうな部分は省略する｡
各データベースの説明など
| データベース名 | 目的 |
|---|---|
| users | ユーザーの情報を記録 |
| book_marks | ブックマークに関する情報を記録 |
| tags | タグに関する情報を記録 |
| book_mark_tags | ブックマークに紐づけられたタグを記録 |
環境
| 道具などの名前 | バージョン |
|---|---|
| laravel | 9 |
| php | 8 |
| MariaDB | 10 |
今現在のデータベース構造
users
| 名前 | 何を保存するか |
|---|---|
| id | ユーザーのid |
| name | ユーザー名 |
| ユーザーのメアド | |
| email_verified_at | ぶっちゃけわからない |
| password | パスワード |
| remember_token | おそらくしばらくログインした状態を保つ時に使う |
| ceated_at | 作成日 |
| updated_at | 更新日 |
laravel breezeで自動生成されたテーブルをそのまま使っている
book_marks
| 名前 | 何を保存するか | 補足などのその他コメント |
|---|---|---|
| id | ブックマークのid | |
| user_id | ブックマークを保存したユーザーのid |
usersテーブルのidと外部結合して､カスケード削除も付けたインデックスを貼った |
| count | ブックマークを見た回数 | |
| title | ブックマークのタイトル | |
| url | ブックマークのurl | |
| deleted_at | 論理削除した日付 | |
| created_at | 作成日 | |
| updated_at | 更新日 |
tags
| 名前 | 何を保存するか | 補足などのその他コメント |
|---|---|---|
| id | タグのid | |
| user_id | タグを作ったユーザーのid |
usersテーブルのidと外部結合して､カスケード削除も付けたインデックスを貼った |
| name | タグ名 | |
| count | タグを使った回数 | |
| deleted_at | 論理削除した日付 | |
| created_at | 作成日 | |
| updated_at | 更新日 |
book_mark_tags
| 名前 | 何を保存するか | 補足などのその他コメント |
|---|---|---|
| book_mark_id | ブックマークのid |
book_marksテーブルのidと外部結合して､カスケード削除も付けた |
| tag_id | タグのid |
tagsテーブルのidと外部結合して､カスケード削除も付けた |
| created_at | 作成日 | |
| updated_at | 更新日 |
どんな感じに動くのか
前置きとして以下のようなデータがすでにデータベースにあるとする
tags (説明に不要な部分は省略)
| id | user_id | name | count | deleted_at |
|---|---|---|---|---|
| 1 | 1 | php | 0 | null |
| 2 | 1 | laravel | 0 | null |
| 3 | 1 | データベース | 0 | null |
保存されるデータ
ユーザーid:1のユーザーが以下のようなデータを保存する場合
タイトル:エンジニアになれないへっぽこな私が実装したタグをつける機能とタグの検索機能(n番煎じ)
url :https://qiita.com/hideya670/items/268a9643488e4036706a
紐づけたタグ:[php,laravel,データベース]
データベースは以下のように変化する
book_marks
| id | user_id | count | title | url | deleted_at | created_at | updated_at |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 0 | エンジニアになれないへっぽこな私が実装したタグをつける機能とタグの検索機能(n番煎じ) | https://qiita.com/hideya670/items/268a9643488e4036706a | null | 2023-03-07 11:11:11 | 2023-03-07 11:11:11 |
book_mark_tags
| book_mark_id | tag_id | created_at | updated_at |
|---|---|---|---|
| 1 | 1 | 2023-03-07 11:11:11 | 2023-03-07 11:11:11 |
| 1 | 2 | 2023-03-07 11:11:11 | 2023-03-07 11:11:11 |
| 1 | 3 | 2023-03-07 11:11:11 | 2023-03-07 11:11:11 |
最初期のデータベース構造
一部テーブルは今と形が大きくことなりました｡
users
| 名前 |
|---|
| id |
| name |
| email_verified_at |
| password |
| remember_token |
| ceated_at |
| updated_at |
特にいまと変化無し
book_marks
| 名前 | 補足などのその他コメント |
|---|---|
| id | |
| user_id |
usersテーブルのidと外部結合しているインデックスを貼った |
| title | |
| body | |
| deleted_at | |
| created_at | |
| updated_at |
カスケードは付けてなかった
tags
| 名前 | 補足などのその他コメント |
|---|---|
| id | |
| user_id |
usersテーブルのidと外部結合しているインデックスを貼った |
| name | |
| deleted_at | |
| created_at | |
| updated_at |
カスケードは付けてなかった
book_mark_tags
| 名前 | 補足などのその他コメント |
|---|---|
| id | |
| book_mark_id | |
| tag_id | |
| deleted_at | |
| created_at | |
| updated_at |
質疑応答
book_marks,tagsのuser_idにインデックスを貼った理由は
- 検索速度を早くしたいから｡
- user_idは検索やデータの絞り込みなどでwhere句などに書くことが多いから｡
ブックマークにタグを紐づけるのに中間テーブルを使ったのはどうして?
調べたところ中間テーブルを使う方法がよくある設計のようなので真似した
論理削除を採用した理由は?
最初期での考え
どこかで論理削除の方がデータベースへの負担が軽いと聞いたから
-> 後でわかったことだがそれを裏付ける証拠は何一つとしてなかった｡
今の考え
- バグでデータを物理削除してしまわないように｡
- 論理削除ならバグで削除処理を実行しても復旧ができるから｡
例:送信先のurlなどがまちがえたなど
今現在は半年ほどの猶予期間を設けている
book_mark_tagsに主キー,論理削除を採用してた理由
- ぶっちゃけ特になし｡
- 未熟ゆえ取り敢えず他のテーブルと同じ形で良いかなと考えていた
book_mark_tagsに主キーを削除した理由
- 主キーを使って検索する必要がないから｡
- 一意のデータを探す必要がないから｡
book_mark_tagsを物理削除に変更した理由
- バグの原因になったから
- とある機能をつける時に邪魔だから
- そもそも論理削除したデータを復活させる予定がないから
- もしもバグでデータが消えたとしても､あまり支障が少ないから､取り返しがつくから
カスケードを追加した理由は?
- 初期段階ではカスケードのことを知らなかった｡
- 以前はsql文とシェルスクリプトとcronをつかって論理削除されたブックマークやタグをまとめて削除していたが､もっと楽な方法はないかと探していたら見つけて以前の方法より楽だと考えたから
book_marks,tagsにcountを追加した理由は?
- 閲覧数などを集計するため
- 閲覧数を使ってあまり見てないメモや,ブックマークに,ユーザーが､｢見てないから消そう｣､｢見てないから見よう｣などの行動を起こすきっかけにしてほしいため
- タグもほぼ同じ理由
- 計算する方法もあるがそれでは､検索するたびに計算する必要があり､それではサーバーへ負荷がかかる､それに伴い時間がかかってしまうから
book_markの場合は実装した日から数えることにした
既存のタグは以下のようなマイグレーションを使って数えてデータベースにデータを保存した
Schema::table('tags', function (Blueprint $table) {
// 個数行を追加
$table->unsignedBigInteger('count')->default(0)->after('user_id');
});
// 今までのタグの個数を集計する
// article,bookmarkで使われているタグのid
$artilceTags = DB::table('article_tags')
->select('tag_id')
->whereNotNull('tag_id');
$bookMarkTags = DB::table('book_mark_tags')
->select('tag_id')
->whereNotNull('tag_id');
// 合体
$unioned = $bookMarkTags->unionAll($artilceTags)->toSql();
// タグの数を数える
$counted = DB::table(DB::raw('('.$unioned.') AS unioned'))
->select('unioned.tag_id',DB::raw('count(*) as count'))
->groupBy('unioned.tag_id');
$tags = $counted->get();
// データ移植
foreach($tags as $tag){
DB::table('tags')
->where('id','=',$tag->tag_id)
->update(['count' => $tag->count]);
}
今後の予定
book_mark_tagsのcreate_atとupdate_atは両者とも使う用事がないので消す
book_mark_tagsにuser_idを追加､インデックスも追加
大量のデータのからデータをさがすのに一度user_idで絞るとデータベースが探しやすいのではと予想｡
他
発表後
誤削除したらログから復旧させると良いよ
データベースはできるだけ物理削除のほうが良いよ
とアドバイスを頂きました｡
今すぐに実行はできませんが､ゆくゆくにはこのアドバイス通りにログから復旧できるようにしたいです｡