Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
6
Help us understand the problem. What is going on with this article?
@kamedono

僕の好きな TEMPORARY TABLE

More than 1 year has passed since last update.

MySQL Advent Calendar 2019 の19日目です。
アドベントカレンダーをたまたま見にいったら、たまたま今日と明日が空いていたので、
なんとなく飛び入り参加させていただきました。

はじめに

みなさんクエリを書く時JoinしまくったりSubqueries入れまくったりでめちゃくちゃ長いクエリ作ってませんか?🙈
あとで見直して『なんだこの長いクエリ…』とか、説明だけ読んで『あーなるほどそのデータとってくるためのクエリね!なるほどー!読みたくねぇ…』とかなってませんか???🙉
まぁ仕方ないこともあるんですが…🙊
そんなあなたに TEMPORARY TABLE の紹介です。

なにそれ

その名の通り、セッションを繋いでいる間だけ存在する一時テーブルのことです。
テーブルをサクッと作れて、
必要な情報のみを突っ込みクエリをわかりやすく分割できる、
MySQLではもちろんPostgreSQLやOracleでも使える、
なんなら INDEX も張れる
とっても優れた機能です🤗
設定すればRDSでも動かせます。

ただし色々と罠があり、権限ないと使えなかったり、他の人から見えなかったり、1クエリ内で2回呼び出せなかったり、セッション切れてテーブルつくりなおしになったり、性能にものを言わせたり(実は遅くなったり)、リソース食ったりするので、本番で使うときは注意が必要(詳しいことは知らんが😇

つくってみる

基本的には CREATE TABLE と同じように作れます。
ので、普通に定義してみたり

CREATE temporary TABLE `tmp_pref` (
  `pref_id` INT(11) NOT NULL,
  `string`  VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

構造を真似たり

CREATE temporary TABLE `tmp_pref` like pref;

なんなら SELECT したものをそのまま INDEX 付きで突っ込めます⭐️

CREATE temporary TABLE `tmp_pref`
(
    primary key pr (pref_id),
    INDEX string_key (string)
)
SELECT *
FROM station_all
WHERE pref_id = 1 ;

分解してみる

さてさて本題
今年の1月中に本を注文した男性の最寄駅の第1住所を人数付きで知りたい。
というクソみたいなクエリを考えてみます👏
(実際にある駅データさんのデータとken_all、その他適当なテーブルを頭の中で構成したのであっているか怪しい。

SELECT ka.address1,
       count(ka.address1)
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
JOIN station_all sa
    ON sa.station_cd = u.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND p.item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
    AND u.sex = 1
GROUP BY  ka.address1
;

1個ずつ分解してみます。
とはいえ分解量は INDEX が効いている範囲とか、ここまでは一つだろとか、人それぞれだと思うので適当にわかりやすそうな単位で…

まずは今年1月に購入履歴のあるユーザ情報から☝️
ここでitem_type に INDEX を張ってやります。(もともとない想定で

CREATE temporary TABLE `purchase_uesr_tmp`
(
    INDEX item_type_key (item_type)
)
SELECT u.user_id,
       u.station_id,
       p.item_type, 
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND u.sex = 1
; 

INDEX を張ってやったので本を買った人をみていきましょう✌️

CREATE temporary TABLE `book_purchase_uesr_tmp`
(
    INDEX station_cd_key (station_cd)
)
SELECT station_cd
FROM purchase_uesr_tmp put
WHERE 
    item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
;

ではあとやっちゃいたいところですが、一旦 address1 に INDEX を張ってみます🤟

CREATE temporary TABLE `book_purchase_uesr_address_tmp`
(
    INDEX address1_key (address1)
)
SELECT ka.address1,
FROM book_purchase_uesr_tmp bput
JOIN station_all sa
    ON sa.station_cd = bput.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
;

らすと

SELECT address1,
       count(address1)
FROM book_purchase_uesr_address_tmp
GROUP BY  ka.address1
;

まとめ

さてさて分解してみていかがだったでしょうか???
最初より長くなりすぎて逆に分からんって??それは書いていて思ったので、これくらい短いとまとめた方がわかりやすいかもしれませんね…
さらにいうと MySQL8.0 から WITH句 つかえますからね。そんなに使用頻度ないかもしれませんよね。
とはいえ INDEX 貼ることで速くなったりするので、実行計画とにらめっこしながら分解作業するのも速度を上げる1つの手ですよ😭

ということで皆さんもたのしい TEMPORARY TABLE 生活を🤝

おまけ

以前いた職場でDBにioDriveを使っていたため、W/Rがとっても速くて(その時代では)それなら TEMPORARY TABLE 使った方が早いと すごい人Sさん 言われて初めて存在を知りました。
HDDとか書き込み遅いとtmp書き出しに時間が…なんてこともあるはずなので一概に早くあるとは言えません。ご注意ください。

参考になれば資料

https://dev.mysql.com/doc/refman/8.0/en/create-table.html
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
https://www.post.japanpost.jp/zipcode/download.html
https://www.ekidata.jp/

6
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
kamedono
最近自作キーボードが楽しい(๑╹ω╹๑ )

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
6
Help us understand the problem. What is going on with this article?