勉強したことのメモ

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まで。

mysql> show columns from item;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(11)          | NO   | PRI | NULL    | auto_increment |
| key          | varchar(30)      | YES  |     | NULL    |                |
| point        | int(10) unsigned | YES  |     | NULL    |                |
| created_date | datetime         | YES  | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

#インデックスなし
SELECT *
FROM item 
WHERE created_date BETWEEN '2014-01-01 00:00:00' AND '2014-01-01 23:59:59'
#実行結果1.004 秒

#インデックスあり
SELECT *
FROM item 
WHERE created_date BETWEEN '2014-01-01 00:00:00' AND '2014-01-01 23:59:59'
#実行結果0.044 秒

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

 

 

注意点

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

 - MySQL

  関連記事

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

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

MySQLで複数のコード及びスコアで検索して、スコアが無いものは0を取得する

やりたかった事は下記の通り。 ・ユニークなコードがあり、そのコードに対して複数の ...

phpMyAdminやAdminer上だけ文字化け

レンタルサーバーのプログラムでWebページ上では文字化けしていないのに、phpM ...

mysqliで検索

今後、PHPでmysql関数はエラーでる! という話を聞き、 取り急ぎmysql ...

MySQLのクエリキャッシュ設定

以前クエリキャッシュを設定していたけど、 これだとmysqlを再起動した時に再度 ...

MySQLで特定カラムのbrタグを改行コードに一括置換

MySQLで特定カラムの<br>タグを改行コードに一括置換したかった ...

phpMyAdminより高速で軽量なAdminerが凄い

MySQLをGUIで管理できるphpMyAdmin。特に不満はないんだけど、もっ ...

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

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

MySQLで速度改善

よく見るけど実際に対面してみて難しさが分かった。 150万件ほどのデータがあって ...

SQLで同じ値を複数のレコードに対してUPDATE

やりたかった事は、複数のレコードに対して 同じ値に更新したいというもの。 $sq ...