質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

1回答

2234閲覧

MySQLにて大量の行があり、SELECTが遅いため高速化したい

KohnoseLami

総合スコア17

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

2クリップ

投稿2023/02/03 18:22

実現したいこと

~175,916,268行あるMySQLのテーブルからSELECT句を実行するのが遅いため高速化したいです

前提

現在色々なデータを組み合わせ一つに整形したデータをMySQLにて所持しており、行数がとてつもなく多くなるためSELECTを行うととても遅く早くても5分などかかってしまうためどのようにユーザーが多いサービスなどは高速にSELECTなどを行っているかを知りたいです。
ただのスペックの問題なのか、スペックだとしても共有サーバーなので十分なメモリ速度やメモリサイズ、CPUコアについても128近くあるサーバーのはずなので全てを使えないにしても不自由ないスペックかと思います。

発生している問題・エラーメッセージ

Primary KeyからのSELECTでも5分近くかかってしまう。
これは単純なSELECTで複雑な条件分岐などは行っていません

補足情報(FW/ツールのバージョンなど)

MySQL (MariaDB) 10.5.13
Localhost

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

m.ts10806

2023/02/03 23:14

テーブル定義なども提示されていない現状から言えることは非常に少ないのでこちらに書きますが、実行計画などから地道に原因突き止めて対策するしかないのでは
yuma.inaura

2023/02/03 23:33

パーティショニングとか?
68user

2023/02/03 23:34

1.7億レコードってことでいいですか? インデックス張ってあるなら CPU1コア・メモリ4GB でも普通は一瞬です。なにしろ二分探索で28回でたどり着ける程度の量ですから。 仮にサーバそのもののリソースが潤沢だとしても、いまどきの共有サーバは各ユーザごとにCPUやメモリの割り当て上限を決めているものが多いと思われますので、どれだけ使用可能なのか問い合わせましょう
KohnoseLami

2023/02/04 07:38

68user様 その通りでございます! インデックスなど知らなかったので勉強になりました よくあるユーザーを格納したデータベースでemail user pass idなどが1.7億件ございます 現在はインデックス?などは全く使用しておらずただbulk insertを行い検索をかけているだけです、よく見かけるサービスなどでもいくら多くなってもサーバーが強くなさそうな場所でも直ぐに検索が出来たりしたので実装方法が間違っているのかと思いましたが理解できなかったのでまず基礎から教わりたく聞いてみました 共有サーバーはほぼ上限は無く使えることを確認済みです 意図的ではなく間違えてStableDiffusionをサーバーのCPUで動かしてしまった際に普通に一番下のプランでも40コア、40GB RAMまで使用できたので確実にスペックには問題が無いと言い切れます もちろんそんな量をずっと占有することは出来ませんが数分であれば利用可能です
KohnoseLami

2023/02/04 07:43

皆様ありがとうございます。これまでORM?を使用しておりテーブルの定義などは特に気にせずやってきたためそういったことが重要なのが分かりませんでした 単純なものなので email varchar(255) id varchar(255) created_at (datetime) 全てデフォルト無しのNULLは許可していません 実行したクエリは書いてあるとおりemailやidを用いてSELECTするだけのほんとに単純な奴です
yuma.inaura

2023/02/04 07:46

参考に具体的なクエリ例も載せておきませんか (その単純というやつ
yuma.inaura

2023/02/04 07:47

「用いてSELECT」がなんなのか明確でないので
KohnoseLami

2023/02/04 07:52

SELECT * FROM users WHERE email = 'example@example.com' みたいなかんじですね emailから行を取得したい場合でもこれ以外のクエリの実装方法があるんですか? これまで片付けとかしないやつばっかだったので細かいところまで関係あるの初めてで全然重要とか分からなかったです
68user

2023/02/04 17:43

単純な奴とか片付けとかしないやつとか、そういう情報は解決につながらないので、これまで回答者がアレを出せコレを出せといった情報を、編集したりせずコピペして出すとよいでしょう。 具体的には下記。 ・DESC [テーブル名] の結果 ・SHOW INDEX [テーブル名] の結果 ・EXPLAIN SELECT .... の結果 ・ORM? の定義 インデックス付けてないならそりゃ遅いので付けましょう (CREATE INDEX か、あるいは ORM でそういう書き方をする)。 Primary key なら普通はインデックスついてくるはずですが、ORM の書き方がおかしいのか、あるいは SQL の書き方がおかしいのか。そういうのも↑の情報をコピペすればわかります
KohnoseLami

2023/02/07 14:53

すみません、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以外知らないことなども相まってほぼ情報を載せていませんでした 自己解決にてユーザー様を引用して解決とさせていただきます
guest

回答1

0

自己解決

返信にていち早く指摘してくださった 68user 様のインデックスが無い という回答を元にインデックスを作成したところ解決いたしましたので引用しての自己解決とさせていただきます。
ありがとうございました

投稿2023/02/07 14:55

KohnoseLami

総合スコア17

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問