勉強したことのメモ

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

  関連記事

テンポラリテーブルで高速化

5000万件ぐらいデータが入っているテーブルから 日本語検索しないといけないケー ...

MySQLでWhere句を使ってカラム指定でデータコピー

MySQLで大量のデータを保存しているテーブルがあり、そのデータを絞った上で尚且 ...

MySQLでREGEXPについて

SQL文にREGEXPというのを見てこれ何? となったのでメモ。 ■参考サイト ...

お名前.comの共用サーバー(SD)にphpmyadminを入れる

公式で書いてあるやつは3.4.2だけど、もうちょっと新しいバージョンを入れたかっ ...

ランダム表示のページング

MySQLからデータを持ってくる際に、 ランダムな表示を行ってほしいと言われた。 ...

お名前SDサーバにMySQLデータのインポート

お名前.comのレンタルサーバにそこそこ大きいサイズのMySQLデータを移行した ...

MySQLでスレッドの実行状況を表示

データベースサーバが重たく、原因を調査する機会があった。サーバ自体のメモリやCP ...

MySQLでサイズの大きいファイルのインポート

MySQLでサイズの大きいdumpファイルを異なるサーバに移行したかった。また移 ...

MySQLで月別に集計を行う。あと日付時刻関数。

現在進行形でやっているデータ集計系だけど 新たな発見があったのでメモ。 ■日付時 ...

mysqlで正規化できない時はシリアライズ使う

アンケートか何かに機能追加している際、配列を カンマ区切りで結合して文字列でDB ...