Q&A
質問は修正できます。
テーブル定義をCREATE TABLE文で、SELECT文と実行計画を Markdownの[コード]を使って提示してください。
https://teratail.com/help/question-tips#questionTips37
実行計画の取得方法
https://thinkit.co.jp/article/9658
実現したいこと
~175,916,268行あるMySQLのテーブルからSELECT句を実行するのが遅いため高速化したいです
前提
現在色々なデータを組み合わせ一つに整形したデータをMySQLにて所持しており、行数がとてつもなく多くなるためSELECTを行うととても遅く早くても5分などかかってしまうためどのようにユーザーが多いサービスなどは高速にSELECTなどを行っているかを知りたいです。
ただのスペックの問題なのか、スペックだとしても共有サーバーなので十分なメモリ速度やメモリサイズ、CPUコアについても128近くあるサーバーのはずなので全てを使えないにしても不自由ないスペックかと思います。
発生している問題・エラーメッセージ
Primary KeyからのSELECTでも5分近くかかってしまう。
これは単純なSELECTで複雑な条件分岐などは行っていません
補足情報（FW/ツールのバージョンなど）
MySQL (MariaDB) 10.5.13
Localhost
テーブル定義なども提示されていない現状から言えることは非常に少ないのでこちらに書きますが、実行計画などから地道に原因突き止めて対策するしかないのでは
パーティショニングとか？
1.7億レコードってことでいいですか? インデックス張ってあるなら CPU1コア・メモリ4GB でも普通は一瞬です。なにしろ二分探索で28回でたどり着ける程度の量ですから。
仮にサーバそのもののリソースが潤沢だとしても、いまどきの共有サーバは各ユーザごとにCPUやメモリの割り当て上限を決めているものが多いと思われますので、どれだけ使用可能なのか問い合わせましょう
68user様
その通りでございます！
インデックスなど知らなかったので勉強になりました
よくあるユーザーを格納したデータベースでemail user pass idなどが1.7億件ございます
現在はインデックス？などは全く使用しておらずただbulk insertを行い検索をかけているだけです、よく見かけるサービスなどでもいくら多くなってもサーバーが強くなさそうな場所でも直ぐに検索が出来たりしたので実装方法が間違っているのかと思いましたが理解できなかったのでまず基礎から教わりたく聞いてみました
共有サーバーはほぼ上限は無く使えることを確認済みです
意図的ではなく間違えてStableDiffusionをサーバーのCPUで動かしてしまった際に普通に一番下のプランでも40コア、40GB RAMまで使用できたので確実にスペックには問題が無いと言い切れます
もちろんそんな量をずっと占有することは出来ませんが数分であれば利用可能です
皆様ありがとうございます。これまでORM？を使用しておりテーブルの定義などは特に気にせずやってきたためそういったことが重要なのが分かりませんでした
単純なものなので
email varchar(255)
id varchar(255)
created_at (datetime)
全てデフォルト無しのNULLは許可していません
実行したクエリは書いてあるとおりemailやidを用いてSELECTするだけのほんとに単純な奴です
参考に具体的なクエリ例も載せておきませんか
(その単純というやつ
「用いてSELECT」がなんなのか明確でないので
SELECT * FROM users WHERE email = 'example@example.com'
みたいなかんじですね
emailから行を取得したい場合でもこれ以外のクエリの実装方法があるんですか？
これまで片付けとかしないやつばっかだったので細かいところまで関係あるの初めてで全然重要とか分からなかったです
単純な奴とか片付けとかしないやつとか、そういう情報は解決につながらないので、これまで回答者がアレを出せコレを出せといった情報を、編集したりせずコピペして出すとよいでしょう。
具体的には下記。
・DESC [テーブル名] の結果
・SHOW INDEX [テーブル名] の結果
・EXPLAIN SELECT .... の結果
・ORM? の定義
インデックス付けてないならそりゃ遅いので付けましょう (CREATE INDEX か、あるいは ORM でそういう書き方をする)。
Primary key なら普通はインデックスついてくるはずですが、ORM の書き方がおかしいのか、あるいは SQL の書き方がおかしいのか。そういうのも↑の情報をコピペすればわかります
すみません、SQLのCLIにアクセスできない仕様でしてphpmyadminからSQLの実行結果として模写しますのでわかりづらいかと思いますが
DESCの結果が
Field Type Null Key Default
email varchar(255) NO PRI NULL
id varchar(255) NO MUL NULL
となっており
SHOW INDEXの結果が
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
users 0 PRIMARY 1 email A 175916268 NULL NULL BTREE
users 1 email 1 email A 175916268 NULL NULL BTREE
users 1 id 1 id A 175916268 NULL NULL BTREE
となっています
とりあえずインデックスが無いという指摘の時点でインデックスを作成してみて再度セレクトしたら改善されたので解決とさせていただきます。
指摘された時点でインデックスを作成してしまったのでShOW INDEXでちゃんとインデックスがあるのだと思いますがおそらくなかったために遅かったのだと思います
ありがとうございました！
ORMというのはあくまでも私がSQLを理解していない理由を軽く述べただけですので気にしなくて大丈夫です
これまでもSQLを利用してきましたがSequelizeというもので一切SQLを触らずにDBを使っていたのでわからなく出来れば細かく指摘していただきたかっただけです
基礎的な部分でインデックスなどの話を聞きたかったので一番最初に教えていただけてとても助かりました！
本当にテキストとしてデータがあり項目ごとに検索が出来なかったりしたのでMySQLに入れてAPIなどとしても利用したかっただけなので本当に複雑なことはしていませんでした
そのためコピペが出来ないことやSQLのコマンドをSELECTやINSERT以外知らないことなども相まってほぼ情報を載せていませんでした
自己解決にてユーザー様を引用して解決とさせていただきます
回答1件
0
自己解決
返信にていち早く指摘してくださった 68user 様のインデックスが無い という回答を元にインデックスを作成したところ解決いたしましたので引用しての自己解決とさせていただきます。
ありがとうございました
投稿2023/02/07 14:55
総合スコア17
あなたの回答
tips
プレビュー
