For Want Of A Better Word

【mysql】チューニング|インデックス作成してみたら実行時間が2分⇒0.4秒に。

   

インデックスってすげぇ….

■参考リンク
http://ameblo.jp/principia-ca/entry-11923272810.html
http://blog.layer8.sh/ja/2011/12/23/mysql%E3%82%92%E9%AB%98%E9%80%9F%E5%8C%96%E3%81%97%E3%81%9F%E3%81%84%E3%81%A8%E3%81%8D%E3%81%AE%E3%83%81%E3%83%A5%E3%83%BC%E3%83%8B%E3%83%B3%E3%82%B0/
http://polidog.jp/2013/11/24/mysql/

 

下記の二つのテーブルあり。 件数は5万件と2万件。
CREATE TABLE tracking_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
member varchar(255) NOT NULL,
visit int,
page_view int,
date datetime,
ip_addr varchar(255),
site text,
page text,
referring_site text,
referring_page text,
referring_query text,
user_agent text,
os varchar(255),
app_key varchar(255),
modified datetime ,
created datetime
);

CREATE TABLE user_visit_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
member varchar(255) NOT NULL,
visit int,
page_view int,
visit_offset int,
date datetime,
visit_last_access_date datetime,
referring_type text,
referring_site text,
referring_page text,
referring_keyword text,
modified datetime ,
created datetime

);
で下記のSQLを発行

SELECT
tr.member,
tr.visit,
uv.visit,
tr.page,
tr.date, uv.date ,
uv.visit_last_access_date ,
timediff(uv.visit_last_access_date, uv.date)
FROM
tracking_logs tr
INNER JOIN
user_visit_logs uv
USING
(member, visit)

これが2分かかる。

mysql> EXPLAIN SELECT tr.member, tr.visit, uv.visit, tr.page, tr.date, uv.date , uv.visit_last_access_date , timediff(uv.visit_last_access_date, uv.date) FROM tracking_logs tr INNER JOIN user_visit_logs uv USING (member, visit);
+—-+————-+——-+——+—————+——+———+——+——-+——————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——-+——————————–+
| 1 | SIMPLE | uv | ALL | NULL | NULL | NULL | NULL | 19968 | |
| 1 | SIMPLE | tr | ALL | NULL | NULL | NULL | NULL | 51717 | Using where; Using join buffer |
+—-+————-+——-+——+—————+——+———+——+——-+——————————–+

でまず

①TypeがALLでインデックスが利用されていない。

現在のインデックスはというと(そもそも作ってないけど..)

mysql> show index from tracking_logs;
+—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| tracking_logs | 0 | PRIMARY | 1 | id | A | 52412 | NULL | NULL | | BTREE | | |
+—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
1 row in set (0.00 sec)

mysql> show index from user_visit_logs;
+—————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+—————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| user_visit_logs | 0 | PRIMARY | 1 | id | A | 18168 | NULL | NULL | | BTREE | | |
+—————–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
1 row in set (0.01 sec)
どっちもプライマリキー(id)にしかありません。

じゃあ、どの項目にインデックスを作成すればよいのか?
項目別インデックスとか、複号インデックスとかあるし、全部につけりゃいいってものじゃないのかな…
したら、これ参考
http://polidog.jp/2013/11/24/mysql/

ということで、双方の使っている項目にindexを作成してみる。
ALTER TABLE tracking_logs ADD INDEX member_idx (member);
ALTER TABLE tracking_logs ADD INDEX visit_idx (visit);
ALTER TABLE tracking_logs ADD INDEX date_idx (date);
ALTER TABLE tracking_logs ADD INDEX page_idx (page(255)); ⇒ textなのでindexが張れない… http://qiita.com/mizuki_takahashi/items/52bc426307817123305e
ALTER TABLE user_visit_logs ADD INDEX member_idx (member);
ALTER TABLE user_visit_logs ADD INDEX visit_idx (visit);
ALTER TABLE user_visit_logs ADD INDEX date _idx (date);
ALTER TABLE user_visit_logs ADD INDEX visit_last_access_date_idx (visit_last_access_date);
+—————+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+—————+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| tracking_logs | 0 | PRIMARY | 1 | id | A | 53107 | NULL | NULL | | BTREE | | |
| tracking_logs | 1 | member_idx | 1 | member | A | 202 | NULL | NULL | | BTREE | | |
| tracking_logs | 1 | visit_idx | 1 | visit | A | 202 | NULL | NULL | YES | BTREE | | |
| tracking_logs | 1 | date_idx | 1 | date | A | 202 | NULL | NULL | YES | BTREE | | |
| tracking_logs | 1 | page_idx | 1 | page | A | 202 | 255 | NULL | YES | BTREE | | |
+—————+————+————+————–+————-+———–+————-+———-+——–+——+————+———+—————+
5 rows in set (0.00 sec)

mysql> show index from user_visit_logs;
+—————–+————+—————————-+————–+————————+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+—————–+————+—————————-+————–+————————+———–+————-+———-+——–+——+————+———+—————+
| user_visit_logs | 0 | PRIMARY | 1 | id | A | 20800 | NULL | NULL | | BTREE | | |
| user_visit_logs | 1 | member_idx | 1 | member | A | 231 | NULL | NULL | | BTREE | | |
| user_visit_logs | 1 | visit_idx | 1 | visit | A | 231 | NULL | NULL | YES | BTREE | | |
| user_visit_logs | 1 | date _idx | 1 | date | A | 231 | NULL | NULL | YES | BTREE | | |
| user_visit_logs | 1 | visit_last_access_date_idx | 1 | visit_last_access_date | A | 231 | NULL | NULL | YES | BTREE | | |
+—————–+————+—————————-+————–+————————+———–+————-+———-+——–+——+————+———+—————+
ふむ増えました。

indexごとのcardoinalityが一意な値の見積もりらしい。これが多いとこうかありそうということらしいけど..

とりあえずこれでexplain

mysql> EXPLAIN SELECT tr.member, tr.visit, uv.visit, tr.page, tr.date, uv.date , uv.visit_last_access_date , timediff(uv.visit_last_access_date, uv.date) FROM tracking_logs tr INNER JOIN user_visit_logs uv USING (member, visit);
+—-+————-+——-+——+———————-+————+———+——————–+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+———————-+————+———+——————–+——-+————-+
| 1 | SIMPLE | tr | ALL | member_idx,visit_idx | NULL | NULL | NULL | 53107 | |
| 1 | SIMPLE | uv | ref | member_idx,visit_idx | member_idx | 767 | rtoaster.tr.member | 90 | Using where |
+—-+————-+——-+——+———————-+————+———+——————–+——-+————-+

実検索も実行
⇒ 0.41

やべぇ….
explainの結果 member とidxに複号キーを張るのがよさそうと、思われ思われなので作ってみる。
ALTER TABLE tracking_logs ADD INDEX member_visit_idx (member,visit );
ALTER TABLE user_visit_logs ADD INDEX member_visit_idx (member,visit );

⇒(0.38 sec)

さらにちょっと早くなった。。。

けど上のタイプがALLのままなのが気になるけど、早いのでOKOK。
使わない member_idx,visit_idx 以外を落として終わり。

ALTER TABLE tracking_logs DROP INDEX date_idx;
ALTER TABLE user_visit_logs DROP INDEX date _idx;
ALTER TABLE user_visit_logs DROP INDEX visit_last_access_date_idx;
もう一回検索しても速度は同じ!! OKOKOKOK!!

 

 

 

 

—————————————————————
—————————————————————
■これだけ早くなるとうれしいもんで、別のテーブルにもインデックスを張ってみる!
CREATE TABLE score_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
member varchar(255),
visit int,
score_group varchar(255),
score_item varchar(255),
score_value int,
rtoster_update_date datetime,
modified datetime ,
created datetime
);
SELECT
member ,
DATE_FORMAT(rtoster_update_date, ‘%Y-%m-%d’) as date,
ifnull( sum(case score_item when ‘CKD’ then score_value end), 0 ) as ‘CKD’,
ifnull( sum(case score_item when ‘腎不全・腎症・腎炎’ then score_value end), 0 ) as ‘腎不全・腎症・腎炎’,
ifnull( sum(case score_item when ‘高血圧’ then score_value end), 0 ) as ‘高血圧’,
ifnull( sum(case score_item when ‘臨床試験’ then score_value end), 0 ) as ‘臨床試験’,
ifnull( sum(case score_item when ‘降圧薬’ then score_value end), 0 ) as ‘降圧薬’,
ifnull( sum(case score_item when ‘一般内科’ then score_value end), 0 ) as ‘一般内科’,
ifnull( sum(case score_item when ‘循環器内科’ then score_value end), 0 ) as ‘循環器内科’,
ifnull( sum(case score_item when ‘腎臓内科’ then score_value end), 0 ) as ‘腎臓内科’,
ifnull( sum(case score_item when ‘予防医学’ then score_value end), 0 ) as ‘予防医学’,
ifnull( sum(case score_item when ‘基礎医学’ then score_value end), 0 ) as ‘基礎医学’
FROM score_logs
GROUP BY member, date
;

⇒ (0.53 sec)

+—-+————-+————+——+—————+——+———+——+——-+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——+——-+———————————+
| 1 | SIMPLE | score_logs | ALL | NULL | NULL | NULL | NULL | 33181 | Using temporary; Using filesort |
+—-+————-+————+——+—————+——+———+——+——-+———————————+

こんな感じ
ALTER TABLE score_logs ADD INDEX member_idx (member);
ALTER TABLE score_logs ADD INDEX visit_idx (visit);
ALTER TABLE score_logs ADD INDEX score_group_idx (score_group);
ALTER TABLE score_logs ADD INDEX score_item_idx (score_item);
ALTER TABLE score_logs ADD INDEX score_value_idx (score_value);
ALTER TABLE score_logs ADD INDEX rtoster_update_date_idx (rtoster_update_date);
変わらない
+—-+————-+————+——+—————+——+———+——+——-+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——+——-+———————————+
| 1 | SIMPLE | score_logs | ALL | NULL | NULL | NULL | NULL | 33181 | Using temporary; Using filesort |
+—-+————-+————+——+—————+——+———+——+——-+———————————+

0.5sec

かわらない。

インデックス使うまでもないのかな…

こっちはデータ件数増えたら要チェックだわ。

 - mysql

  関連記事

mysql-logo
【MySQL】query_logを取得した場合の速度影響

あくまでWEB画面表示について。 環境は下記。 Linux CentOs7.1 …

mysql-logo
【PHP】【DB開発】【メモ】MYSQLアクセス・モデルクラスの雛形

私の備忘。良く他のソースコピーして作っているので、検索までの雛形化しておく。 フ …

mysql-logo
プログラミングPHP 第13章( アプリケーションに関するテクニック )ポイント

コードライブラリ 複数の関数をコードライブラリにまとめる際には、「どの関数とどの …

027
php4.3環境の作成

故あって、PHP4.3環境を用意しなければならなくなった。 apacheモジュー …

mysql-logo
【PHP】【DB開発メモ】PDO_LIKEが動かん!プレースホルダの誤用

前もはまった気がする…. 成長しない。 PHP: プ& …