mysql重大なクエリー速度の問題の1つ
これまでmysqlを使っていても特に遅いとは思わなかったのですが、最近新しく開発されたシステムではページの開きが非常に遅く、1分も開かないことがあります.システムを調べてみると、sql文の実行が遅いために位置しています.このsqlは次のとおりです.
ざっと見てみると、とても深刻な問題があるとは思いませんでしたが、本来はイントラリンクでINと書くべきでした.実行計画の結果を次のように表示します.
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
| 1 | PRIMARY | document | ALL | NULL | NULL | NULL | NULL | 117287 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | subject_document | ref | uk_subject_id_document_id,idx_document_id | uk_subject_id_document_id | 10 | const,func | 1 | Using where; Using index |
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
2 rows in set (0.41 sec)
ネストされたクエリはインデックスを使用していますが、外側のレイヤはインデックスを使用していないため、クエリの速度が大幅に低下します.
内部接続に変更した実行計画を比較します.
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | sd | ref | uk_subject_id_document_id,idx_document_id | uk_subject_id_document_id | 5 | const | 455 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | pscms.sd.document_id | 1 | |
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
2 rows in set (0.05 sec)
これでインデックスが使用できるので、クエリーの速度が非常に速いです.
このプログラムはoracleデータベースからコードを移植したので、特にoracleの実行速度を見てみました.結果oracleでは実行速度が非常に速く、実行計画を見るのを忘れましたが、インデックスを利用していることは明らかです.Oracleのオプティマイザはずっと強いようです.
最後に自分の履歴コードを見てみると、INを使ったところもあります.上のネストされたクエリのサブクエリの結果が空であっても、長い間実行されます.では、IN(1)と直接書くと?結局速度がまた速くなって、これ以上速くなくて、IN条件は本当に何の役にも立たない.この実行計画を確認しました.
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | document | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
ここでまたインデックスを使ったので、またスピードが速くなりました.
まとめてみると、データベースのクエリー速度はインデックスと大きく関係しており、インデックスを正しく利用することでクエリー速度を効果的に速めることができ、また実行計画で分析することも多い.
- SELECT
- COUNT(1) AS value
- , document.sourceType AS lable
- FROM
- document
- WHERE
- document.id
- IN
- (SELECT
- document_id
- FROM
- subject_document
- WHERE
- subject_id = 345
- )
- GROUP BY
- document.sourceType
ざっと見てみると、とても深刻な問題があるとは思いませんでしたが、本来はイントラリンクでINと書くべきでした.実行計画の結果を次のように表示します.
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
| 1 | PRIMARY | document | ALL | NULL | NULL | NULL | NULL | 117287 | Using where; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | subject_document | ref | uk_subject_id_document_id,idx_document_id | uk_subject_id_document_id | 10 | const,func | 1 | Using where; Using index |
+----+--------------------+------------------+------+-------------------------------------------+---------------------------+---------+------------+--------+----------------------------------------------+
2 rows in set (0.41 sec)
ネストされたクエリはインデックスを使用していますが、外側のレイヤはインデックスを使用していないため、クエリの速度が大幅に低下します.
内部接続に変更した実行計画を比較します.
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | sd | ref | uk_subject_id_document_id,idx_document_id | uk_subject_id_document_id | 5 | const | 455 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | pscms.sd.document_id | 1 | |
+----+-------------+-------+--------+-------------------------------------------+---------------------------+---------+----------------------+------+-----------------------------------------------------------+
2 rows in set (0.05 sec)
これでインデックスが使用できるので、クエリーの速度が非常に速いです.
このプログラムはoracleデータベースからコードを移植したので、特にoracleの実行速度を見てみました.結果oracleでは実行速度が非常に速く、実行計画を見るのを忘れましたが、インデックスを利用していることは明らかです.Oracleのオプティマイザはずっと強いようです.
最後に自分の履歴コードを見てみると、INを使ったところもあります.上のネストされたクエリのサブクエリの結果が空であっても、長い間実行されます.では、IN(1)と直接書くと?結局速度がまた速くなって、これ以上速くなくて、IN条件は本当に何の役にも立たない.この実行計画を確認しました.
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| 1 | SIMPLE | document | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using temporary; Using filesort |
+----+-------------+----------+-------+---------------+---------+---------+------+------+----------------------------------------------+
1 row in set (0.00 sec)
ここでまたインデックスを使ったので、またスピードが速くなりました.
まとめてみると、データベースのクエリー速度はインデックスと大きく関係しており、インデックスを正しく利用することでクエリー速度を効果的に速めることができ、また実行計画で分析することも多い.