MySQLの1テーブルに多くの件数を保持したい場合に出来る対策は何がありますか。

Question

実現したいこと

MySQLを使ったシステムのテーブル定義を考えています。
1テーブルに多くの件数を保持したい場合、どのような対策をしたら良いのか知りたいです。

前提

ユーザが質問に対してどのような回答したかを保持したいです。
テーブルのカラムとしては以下を想定しています。

ID	ユーザID	質問ID	回答ID
1	2	3	4

【件数の想定】
ユーザの件数は2000件、質問の件数は300件を想定しています。
IDがプライマリキー、ユーザIDと質問IDがユニークキーになります。

【使用イメージ】
システムが動いている間、絶えず検索、追加、変更が行われるテーブルです。
検索時はユーザIDのみで検索、ユーザIDと質問IDで検索する2パターンがあります。

疑問

件数を想定する際、念のため数倍を想定した方が良いのかと思いました。
仮に3倍を想定すると

ユーザ6,000件×質問900件＝レコード数は5,400,000件

となり、相当アクセスに時間がかかるテーブルになってしまうのではないかと懸念しています。

考えたこと

件数が多くなるのでユーザIDと質問IDを組み合わせたインデックスを定義しようと考えました。
ただ、頻繁に追加が発生するテーブルなので追加の処理が遅くなる方が不都合なので
インデックスは解決案としてふさわしくない気がしました。

次にパーティションを考えました。
調べたところ、キーじゃないと指定できないとあったので、ユーザIDと質問IDを指定すれば良いのかと考えています。

ただ、本テーブルの検索時はユーザIDのみで検索する場合が多いので
IDとユーザIDをプライマリキーにし直すことになるのか？というあたりが分かっていません。

教えていただきたいこと

1. テーブルのインデックスは今回の場合ふさわしくないという考え方は合っていますか。
2. パーティションを使えば良いという考え方は合っていますか。合っている場合、どのカラムをパーティションのキーにすれば良いか、どのように考えれば良いのか教えてください。
3. 他に出来る対策があれば教えてください。

Answer 1

いろいろフワフワしてて、まだプロトタイプを作る段階でもない感じなのかと思いますが、多分モノがないとイメージできないのだと思うので、何か作る前提で考えてみた結果を書いておきます。回答となるのかどうかは分かりません。

テーブルについて

回答についてのテーブルなら回答テーブルでいいと思います。

想定件数について

現実的な予想が必要です。想定ができないなら無理にする必要はなく、一旦これくらいと置いた仮定の数字が1つあればいいと思います。例えばteratailなら、およそ

• ユーザー15万くらい(ランキングから分かる)
• 質問40万くらい(この質問のデータから目算)
• 回答30万くらい(同上目分量で)

ではないかと思います。テストデータの都合からこれを

• ユーザー26万くらい
• 質問52万くらい
• 回答52万くらい

にして一度試してみればいいでしょう。

本来は要件から出る数字なので、勝手に決めちゃいけませんし、これによりハードウェアスペック、引いては予算などを決めます。逆に言えば使えるハードウェアスペックが限られているならそちらの要件に寄せる必要があり、そのスペックの中で、およそここまでなら行けそうだという数字を提示する必要があります。

登録・検索速度について

目標とする性能があるなら、数字として出さないといけません。例えば

• 日に何件登録可能である
• 1時間に何件程度検索できる必要がある
• 登録に1件何秒まで待てる
• 検索に1件何秒まで待てる

などの数字です。ないならないで、テストデータを作り、この件数でこの内容のデータならそれぞれどれくらい捌けるという数字を提示する必要があります。

テストデータについて

現状何も決まっておらず、何の想定もなく、スペックが足りるのかどうかも分からないので、それらが決められるように作りましょう。普通に考えると、インデックスは必要に決まってるので、つけましょう。

とりあえず作ったサンプルだけ置いておきます。

PASS="hogepass"
cat >hoge.sql <<EOF
set profiling=1;
CREATE TABLE User (
  id varchar(20) PRIMARY KEY,
  name varchar(100) NOT NULL
);
CREATE TABLE Question (
  id integer AUTO_INCREMENT PRIMARY KEY,
  user varchar(20) NOT NULL,
  content TEXT NOT NULL
);
CREATE TABLE Answer (
  id integer AUTO_INCREMENT PRIMARY KEY,
  question integer NOT NULL,
  user varchar(20) NOT NULL,
  content TEXT NOT NULL
);
EOF
echo "INSERT INTO User VALUES ('1', 'UserName1');" >>hoge.sql
num=1
for i in $(seq 18); do
    echo "INSERT INTO User SELECT cast((cast(id as signed)+${num}) as char), concat('UserName', cast((cast(id as signed)+${num}) as char)) FROM User;"
    num=$(echo $num + $num | bc)
done >>hoge.sql
echo "INSERT INTO Question VALUES(NULL, '1', 'QuestionContent1');" >>hoge.sql
num=1
for i in $(seq 19); do
    echo "INSERT INTO Question select NULL, cast(mod(id+${num}-1, 262144)+1 as char), concat('QuestionContent', cast(id+${num} as char)) from Question;"
    num=$(echo $num + $num | bc)
done >>hoge.sql
echo "INSERT INTO Answer VALUES(NULL, 1, 1, 'AnswerContent1');" >>hoge.sql
num=1
for i in $(seq 19); do
    echo "INSERT INTO Answer select NULL, mod(id+${num}-1, 524288)+1, cast(mod(id+${num}-1, 262144) as char)+1, concat('AnswerContent', cast(id+${num} as char)) from Answer;"
    num=$(echo $num + $num | bc)
done >>hoge.sql
cat >>hoge.sql <<EOF
analyze table User, Question, Answer;
create index Question_user on Question(user);
create index Answer_question on Answer(question);
create index Answer_user on Answer(user);
select count(*) from User;
select count(*) from Question;
select count(*) from Answer;
select U.name as 質問者, Q.content as 質問, A.content as 回答, AU.name as 回答者 from User as U inner join Question as Q on U.id=Q.user inner join Answer as A on Q.id=A.question inner join User as AU on A.user=AU.id where U.id in ('1','3','5');
show profiles;
exit;
EOF
docker run -d --rm --name hoge_mysql -v $(pwd)/data:/var/lib/mysql -v $(pwd):/home/mysql -e MYSQL_ROOT_PASSWORD=${PASS} mysql:8
docker exec -i -w /home/mysql hoge_mysql bash <<EOF
while ! mysql -u root --password=${PASS} </dev/null;do
    sleep 1
done
mysql -u root --password=${PASS} --verbose --table <<EOF2
create database hoge;
exit;
EOF2
LC_CTYPE=C.UTF-8 mysql -u root --password=${PASS} --database=hoge --verbose --table <hoge.sql
exit
EOF
docker stop hoge_mysql

本来はチューニングなども必要です。

Comments

[ra-n]

ご回答いただきありがとうございました。
登録・検索速度について、どのようなところを気にしていかなければならないのか
参考にさせていただきます。

Answer 2

まず定義テーブルについてはユーザID/質問IDのユニーク属性が必須です。
ユニークはインデックスを兼ねるのでそれ以上のインデックスの付加はおそらく不要ですが
検索・集計仕様にあわせて必要でしたら追加してください

またカラム数も少なく500万件程度なら特殊な処理は不要だと思います。
これも検索・集計をどうしたいかによるのでなんともいえません。
テストデータをつくって反応をみてチューニングするくらいでちょうどよいかもしれません

Comments

[ra-n]

ご回答いただきありがとうございます。
500万件なら特殊な処理は不要という具体的な数字をご提示いただけたので、
いったん、このまま何もせずに進んでみようと判断が出来ました。