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

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

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

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

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Laravel 5

Laravel 5は、PHPフレームワークLaravelの最新バージョンで、2014年11月に発表予定です。ディレクトリ構造がが現行版より大幅に変更されるほか、メソッドインジェクションやFormRequestの利用が可能になります。

Q&A

解決済

3回答

8790閲覧

LaravelのMySQLクエリの高速化

cat_breed

総合スコア123

MySQL

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

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Laravel 5

Laravel 5は、PHPフレームワークLaravelの最新バージョンで、2014年11月に発表予定です。ディレクトリ構造がが現行版より大幅に変更されるほか、メソッドインジェクションやFormRequestの利用が可能になります。

0グッド

1クリップ

投稿2016/12/15 07:16

編集2016/12/15 12:33

Laravel5.3でのページネーションするために発行されるクエリ"select count(*) as aggregate from"の発行時間を短縮して高速化したいです。where句で使用されるカラムstats_per_daychecked_atはインデックスを貼っております。具体的なクエリのログは以下になります。二番目のクエリは14ミリ秒しかかかっていないのに対して、最初のクエリが1616ミリ秒もかかってしまっております。

[1] => Array ( [query] => select count(*) as aggregate from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > ? [bindings] => Array ( [0] => Carbon\Carbon Object ( [date] => 2016-12-13 07:11:25.000000 [timezone_type] => 3 [timezone] => UTC ) ) [time] => 1616.25 ) [2] => Array ( [query] => select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > ? order by `stars_per_day` desc limit 10 offset 0 [bindings] => Array ( [0] => Carbon\Carbon Object ( [date] => 2016-12-13 07:11:25.000000 [timezone_type] => 3 [timezone] => UTC ) ) [time] => 14.98 )

###追加情報1

データーベース情報は以下のようになりました。何故かMySQLクライアントからだと高速に発行できます

Mysql

1 2mysql> EXPLAIN select count(*) as aggregate from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000'; 3+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 4| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 5+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 6| 1 | SIMPLE | repositories | NULL | range | stars_per_day,checked_at | stars_per_day | 5 | NULL | 16287 | 50.00 | Using index condition; Using where; Using MRR | 7+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+-----------------------------------------------+ 81 row in set, 1 warning (0.00 sec) 9 10 11mysql> EXPLAIN select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000' order by `stars_per_day` desc limit 10 offset 0; 12+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 13| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 14+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 15| 1 | SIMPLE | repositories | NULL | range | stars_per_day,checked_at | stars_per_day | 5 | NULL | 16287 | 50.00 | Using index condition; Using where | 16+----+-------------+--------------+------------+-------+--------------------------+---------------+---------+------+-------+----------+------------------------------------+ 171 row in set, 1 warning (0.01 sec) 18 19 20mysql> show create table repositories; 21| repositories | CREATE TABLE `repositories` ( 22 `id` int(11) NOT NULL AUTO_INCREMENT, 23 `repository_id` int(11) NOT NULL, 24 `full_name` varchar(255) DEFAULT NULL, 25 `name` varchar(255) DEFAULT NULL, 26 `master_branch` varchar(255) DEFAULT NULL, 27 `readme` longtext, 28 `stargazers_count` int(11) DEFAULT NULL, 29 `language_id` int(11) DEFAULT NULL, 30 `description` longtext, 31 `description_ja` longtext, 32 `homepage` longtext, 33 `created_at` datetime DEFAULT NULL, 34 `updated_at` datetime DEFAULT NULL, 35 `pushed_at` datetime DEFAULT NULL, 36 `stars_per_day` float DEFAULT NULL, 37 `similar_repositories` longtext, 38 `checked_at` datetime DEFAULT NULL, 39 `avatar_url` longtext, 40 PRIMARY KEY (`id`), 41 UNIQUE KEY `repositories_repository_id` (`repository_id`), 42 KEY `stars_per_day` (`stars_per_day`), 43 KEY `checked_at` (`checked_at`), 44 FULLTEXT KEY `repositories` (`full_name`,`name`,`description`) 45) ENGINE=InnoDB AUTO_INCREMENT=45097 DEFAULT CHARSET=utf8mb4 |

###追加情報2
offsetの数が大きくなるほど遅くなります

offsetの数が49700の場合は6秒もかかってしまいました

MySQL

1mysql> select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700; 210 rows in set (6.50 sec)

offsetの数が0の場合は爆速です

mysql> select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 0; 10 rows in set (0.01 sec)

Extraは変わりありませんでした

force indexを付け加えた場合

mysql> explain select * from `repositories` force index (idx_1) where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700; +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | repositories | NULL | range | idx_1 | idx_1 | 5 | NULL | 19628 | 33.33 | Using index condition | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

force indexを付け加えない場合

mysql> explain select * from `repositories` where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 11:59:34.000000' order by `stars_per_day` desc limit 10 offset 49700; +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ | 1 | SIMPLE | repositories | NULL | range | idx_1,idx_2 | idx_1 | 5 | NULL | 19628 | 50.00 | Using index condition | +----+-------------+--------------+------------+-------+---------------+-------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

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

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

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

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

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

KiyoshiMotoki

2016/12/15 07:40

"SHOW CREATE TABLE repositories;" の実行結果と、一番目・二番目のクエリの EXPLAIN の結果を質問欄に追記すると、具体的な回答が付きやすくなると思います。その理由は、yambejp様が仰っている通りです。
cat_breed

2016/12/15 08:05

ご回答ありがとうございます。EXPLAINクエリは知りませんでした。情報を追記しました。
guest

回答3

0

ベストアンサー

情報の追記、ありがとうございます。

stars_per_daychecked_atそれぞれの単一カラムインデックスに代わって
stars_per_day + checked_atchecked_at + stars_per_day
という複合インデックスを張るとどうなるか、試してみていただけますか?

sql

1ALTER TABLE repositories 2 DROP INDEX stars_per_day, 3 DROP INDEX checked_at, 4 ADD INDEX idx_1 (stars_per_day, checked_at), 5 ADD INDEX idx_2 (checked_at, stars_per_day);

WHERE句でstars_per_daychecked_atの両方を指定しているため、その複合インデックスを作成すると、改善が見込めるためです。

それでも効果がない場合、FORCE INDEX 句で新たに作成したインデックスを指定した場合の速度も確認してみてください。

sql

1select count(*) as aggregate from `repositories` force index (idx_1) where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000'; 2select count(*) as aggregate from `repositories` force index (idx_2) where `stars_per_day` is not null and `repositories`.`checked_at` > '2016-12-13 07:48:38.000000';

https://dev.mysql.com/doc/refman/5.6/ja/index-hints.html

もし
「FORCE INDEX 句を付けると速度が上がるが、それを付けないと(別のインデックスを使用してしまって)効果が表れない」
となると、Laravel のページネーションをカスタマイズして FORCE INDEX 句を付けてやると、効果が期待できます。
(Laravel は不勉強なので、そんなことができるのか分かりませんが。。)

いずれの場合でも効果がない場合、私が他に思いつく手段は

  • テーブル設計の見直し
  • DBサーバのチューニング
  • またはリソースの追加

くらいでしょうか(^^;

投稿2016/12/15 08:55

KiyoshiMotoki

総合スコア4791

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

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

cat_breed

2016/12/15 09:33

KiyoshiMatokiさん、ご丁寧にありがとうございます SQLスクリプトをコピーアンドペーストして複合インデックスを作成した所、topページは爆速になりました!また、オフセットを増加させていくにつれて二番目のクエリが遅くなってしまいました。最もアクセスの多いトップページを快適に見れるようになりましたので複合インデックスにします。LaravelでForce Indexの指定の仕方はよく分かりませんでした。テーブルの設計とチューニング、リソースの追加はよく分からないので止めときます
KiyoshiMotoki

2016/12/15 10:21

結果のご報告、ありがとうございます。 > topページは爆速になりました! 爆速になりましたかw 幸いです。 > オフセットを増加させていくにつれて二番目のクエリが遅くなってしまいました。 これは、 「複合インデックスを作成する前より遅くなった」 という意味でしょうか? であれば、インデックスを張り替えたことで、今度は二番目のクエリが誤ったインデックスを使い始めてしまった可能性があります。 もし、ご興味がおありなら、二番目のクエリで FORCE INDEX 句を付けた場合の結果と EXPLAIN の結果を、それぞれ確認してみてください。 EXPLAIN 実行結果の"key"と"Extra"という項を見ると、 「どのインデックスが使われたか」 など大方の状況は分かると思います。 ちょっと難しいかも知れませんが、参考になりそうなページを以下に紹介させていただきます。 https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain_key https://dev.mysql.com/doc/refman/5.6/ja/explain-output.html#explain-extra-information http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
cat_breed

2016/12/15 12:35

ありがとうございます。じっくり読ませていただきます。追加情報を更新しました
cat_breed

2016/12/15 13:17

>これは、 >「複合インデックスを作成する前より遅くなった」 >という意味でしょうか? すみません、複合インデックスを作成する前から元々遅かったみたいです(笑) 実はPostgresqlでは遅くはなかったのです。 データベースが2つもあるとサーバーのメモリーとCPUを食うのでMySQLだけに統一しました。
KiyoshiMotoki

2016/12/15 16:52 編集

> 複合インデックスを作成する前から元々遅かったみたいです なるほど、了解です。 PostgreSQL は不勉強なので > 実はPostgresqlでは遅くはなかった 理由は分かりませんが、MySQL では、OFFSET の値が大きい場合に速度が遅くなるのは、良く知られた現象です。 https://www.google.co.jp/search?q=mysql+offset+%E9%81%85%E3%81%84 その理由は、例えば "limit 10 offset 49700" と指定した場合、MySQL はクライアントに返却する 49701行目から49710行目を特定するために、少なくとも 49710行のレコードを検査しなければならないからです。 (もっとも、たかだか5万件でそこまで遅くなる、というのは若干 腑に落ちませんが…) 解決策は上述の検索結果からもいろいろと見つけることができますが、 今回のケースに適用でき、かつ簡単に実装できそうなものは、(私が見た限りでは)見当たりませんでした。。 もし、 「1000ページ目より先まで表示しようとするユーザーは滅多にいないはず」 なのであれば、このままにしておくのも費用対効果の観点からはアリだと思います(^^;
cat_breed

2016/12/15 17:57

>(もっとも、たかだか5万件でそこまで遅くなる、というのは若干 腑に落ちませんが…) サーバーと言いましてもさくらVPS1Gの1Gbyteのメモリと2CoreのCPUです。だから遅くなるのかもしれません。MySQLとPostgreSQLを一緒に入れていますとPostgreSQLがバキュームする際にメモリを最大まで使用してスワップしてしまうんです。PostgreSQLも良く分かっていません(笑)詳しいチューニングの仕方を教えてくださってありがとうございました。
guest

0

EXPLAIN の結果からすると、checked_at のキーが使われていません。
ただ、データの分布がわかりませんので、checked_at のキーを使った方が早いのかどうかは微妙ですが…
(stars_per_day が null とそれ以外との比率 と checked_at の条件が成立する/しないの比率 とで比べてどうなるかによる)

複合インデックスを張ってみて、その状態で explain してどうなるかですね。

投稿2016/12/15 08:34

tacsheaven

総合スコア13703

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

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

cat_breed

2016/12/15 09:37

EXPLAINの結果の読み方を教えてくださってありがとうございます。複合インデックスでいこうと思います
guest

0

複合インデックスを適宜貼ればいけそうな気がしますが、
EXPLAINでデバッグするのが賢明です

投稿2016/12/15 07:26

yambejp

総合スコア114572

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

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

cat_breed

2016/12/15 08:05

ご回答ありがとうございます。EXPLAINクエリは知りませんでした。情報を追記しました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問