勉強したことのメモ

webプログラマ見習いが勉強したことのメモ。

MySQLでdatetime型にもインデックスを使用

      2018/11/28

ログ関連のデータを格納するテーブルがあり、集計を行う際に非常に時間がかかった。レコード数は1億ほどあったので、これはもう仕方がないんじゃないかと思ったんだけど他者が作成したテーブルで3億ほどレコード数があるのに高速で動いていた。色々調べてみた結果のメモ。

 

違いを調べてみた結果

両者のテーブル構造やインデックス構造を確認したところ、高速で動く方はdatetime型のカラムにインデックスが貼られていた。またパーティショニングされていた。

 

パーティショニング

パーティショニングというのは初めて聞いたので調べてみたところ、テーブルを分割して高速化を図る機能みたい。ただ、パーティショニングは途中(2016年とか)で切れていたので今回はあまり関係なさそう。

https://liginc.co.jp/programmer/archives/3832

 

datetime型にインデックス

datetime型にインデックスを張ってもフルスキャンされると思い込んでいたがどうやら違うみたい。試しに以下テーブルを作成し400万ほどランダムなデータを入れた。created_dataの範囲は2014-01-01から2014-06-30まで。

EXPLAINで調べたところtypeはrangeになっていたのでインデックスはきいていることが確認でき、20倍ほど速度が改善された。

 

 

注意点

大量データに後からインデックスを張る場合はサイトはメンテナンスモードにしてMySQLにクエリが入らない状態にして行った方が良さそう。テーブルロックがかかるようで更新ができないし、killで強制終了した場合はデータが破損する可能性がある。

 - MySQL

  関連記事

msyql-image
MySQLで開始から終了日付内を調べたいとき

等号不等号でもできるけど、BETWEENというのがよさそう。 ■参考サイト ht ...

msyql-image
コマンドでMySQLに郵便番号データを入れる

練習としてコマンドから郵便番号データを入れた際のメモ。 --------- 1: ...

msyql-image
MySQLでtext型カラムに入っている数値をint型としてソートする

MySQLでtext型として指定されているカラムがあり、その中には文字列であった ...

msyql-image
MySQLのエラーログ取得関数

場合によってログに出力させたい値が異なる場合があったのでメモ。 function ...

msyql-image
InnoDBのオートインクリメント

MySQLのInnoDBでIDというカラムにオートインクリメント設定していたが、 ...

msyql-image
mysqlでrootのパスワードが分からない場合

pleskで管理しているサーバがあり、mysqlのrootのパスワードが分からな ...

msyql-image
MySQLのtinyintのカラムにbool値を入れた場合

他所のシステムに携わることがあり中身を色々見ているとMySQLでtinyint( ...

msyql-image
MySQLでデータがあれば更新、無ければ挿入

DBにデータがあれば更新、無ければ挿入(新規作成)を 行いたい際に使えるものがあ ...

msyql-image
MySQLで文字列の置換

MySQLで文字列の置換を行いたかった。以下に方法をメモ。   置換方 ...

msyql-image
MySQLでカラム内容によって条件分岐させたい

MySQLでカラムの内容によって条件を分岐させたかった。またPHPで処理するので ...