勉強したことのメモ

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

  関連記事

MySQLでテーブル構造とデータをコピーする

MySQLで既にあるテーブルとデータに対して実験を行う必要があった。本番テーブル ...

MySQLでグループ化したものを条件で絞る(HAVING)

正規化したテーブルがあってその中には idとtagのカラムがある。 でtagの方 ...

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

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

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

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

MySQLで実行結果の行数取得

DBにデータがあるか調べる際にしばしば使用してたのでメモ。 $sql = " S ...

MySQLのREGEXPで半角記号を検索したい場合

MySQLのREGEXPで半角記号を検索したい場合 \\を付けてエスケープしない ...

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

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

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

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

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

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

権限が無い場合のエラー

SQLでINSERTする際、見たことに無いエラーに遭遇した。 INSERT co ...