DB:JOIN

posted by 生活小白 on 3月 13, 2019 No Comments

JOINを使う原因

mysql> explain SELECT `records`.`record_id` ,`info_1`, `info_2`,`like_count`,`create_time`,`description`,`publisher_id` 
FROM `records` 
JOIN ( SELECT `record_id`  FROM `records` ORDER BY `records`.`create_time` desc LIMIT 6 OFFSET 10266 ) AS a  
ON a.record_id =  records.record_id
+----+-------------+------------+--------+----------------------------------------------+------------------+---------+-------------+-------+-------------+
| id | select_type | table      | type   | possible_keys                                | key              | key_len | ref         | rows  | Extra       |
+----+-------------+------------+--------+----------------------------------------------+------------------+---------+-------------+-------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                         | NULL             | NULL    | NULL        |     6 |             |
|  1 | PRIMARY     | records    | eq_ref | PRIMARY,record_publisher_index,id_time_index | PRIMARY          | 4       | a.record_id |     1 |             |
|  2 | DERIVED     | records    | index  | NULL                                         | recordtime_index | 5       | NULL        | 10272 | Using index |
+----+-------------+------------+--------+----------------------------------------------+------------------+---------+-------------+-------+-------------+
3 rows in set (0.00 sec)
まずはid=2のQueryです。つまり、SELECT record_idFROM recordsORDER BY create_timedesc LIMIT 6 OFFSET 5のことです。 selecttype=DERIVEDはsubqueryのことで、検索結果はCacheします。 type=indexは全表スカウトですが、スカウトの時はIndex（recordtimeindex: updatetime）の順番で実行します。 もちろんORDER BY like_count descの時は　likeaccumulateindex: likeaccumulateのIndexを使います。 これのメリットはusing filesortのことを避けますが、デメリットとしてやはり重いな操作です。 refは検索する時どの列が指定されることで、ここはWHEREの条件がない（全表表示）ので、refはNULLになりました。
※　ここのsubqueryの作用はcolumnsがidだけでLIMITとORDERにすることと検索結果を先にCacheに入れます。
次にid=1のQueryです。つまり、SELECT records.record_id ,like_count, info_1,info_2,create_time,description,publisher_id FROM records JOIN aON a.record_id =  records.record_id のJOIN　（recordsとa）のことです。 type=eqrefは検索する時keyは非唯一またはPrimary Keyの場合です。特にJoinがあるQueryにはよくあります。 ここのrecords.recordidの参照値はSubqueryのa.recordidです。 つまり、recordsはJOINの対象として（外表）、PRIMARY（recordid）を使ってaのこと（内表）を検索します。 一般的な場合、MySQLのOptimizerは表の大きさによって外表と内表のことを決めます。 もともとrecordsより小さいaは外表になるはずが、 aはsubqueryで先にcacheされるので（全表スカウトすることが必要がない）、aは内表になりました。 また、はaのことでkeyとrefはNULLは内表の原因です（内表はIndexが使えません）。 しかし、全表にしてもLIMITの５件の資料だし、先にcacheされたし、そんなに重くないと思います。
※　ここのjoin queryの作用はsubqueryでidを使って、他のcolumnsを取ります。 　そして、その5件だけの資料で、Primary KeyでSelectします。

キャッシュの効果について

Queryによってキャッシュの効果が違うと思います。 キャッシュが一番効果があるところはQueryとその検索結果をhashで保存することですが、 キャッシュが適用されていない場合もあります。
下記は注意するべきことです。
  • Query Cacheのsizeは100MBに設定されたので、大量の検索は適用されません
  • INSERT、UPDATE、DELETEなどの操作で表が更新されたら、Query Cacheでのhashは消えます
  • BENCHMARK()、CURDATE()、CURRENT_TIMESTAMP()、NOW()、RAND()、UUID()などの特殊な関数があるQueryは適用されません
  • TEMPORARY TABLEを使うQueryは適用されません
  • SubqueryとJoinを使うQueryは適用されません　
  • TriggerやEventでのQueryは適用されません ...
そして、毎回Query Cacheでの検索結果を使う時もある程度の判別時間がかけます。
  • checking privileges on cached query
  • checking query cache for query
  • invalidating query cache entries
  • sending cached result to client
  • storing result in query cache
また念のため、前キャシューがない検索結果もシェアします。
操作時間（秒）OFFSETが大きな場合(average[range])OFFSETが小さな場合(average[range])平均時間
JoinがないQUERY　　0.01485095[0.01413225]　0.00021405[0.000312]0.0075325
JoinがあるQUERY　　0.0055802[0.00619175]　0.00097285[0.00393825]0.00289715
変化の比率　　　　 +62.4252993916214%-354.496612940902%+61.5380019913707%

0 留言:

發佈留言