時刻カラムにおけるパフォーマンス改善手法

はじめに

○年○月○日 ○○:○○:○○といったデータをカラムに入れること多いと思いますが、こういった時刻カラムについてのパフォーマンスについて記事にしたいと思います。

動作確認環境

• PHP 8.0
• Laravel 8.0
• MySQL 8.0

※下位環境でも動作する場合がございます

Laravelの時刻カラム

Laravelはmigrationでは下記のようにマイグレーションを記述すると、自動的にcreate_atとupdated_atカラムを挿入してくれます。

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('samples', function (Blueprint $table) {
        $table->id();
        $table->timestamps();
    });
}

しかし、どちらのデータ型もTIMESTAMP型であるため、表示する時は便利ではありますがSQLのwhere句としてはあまりパフォーマンスがよくありません。以下計測結果です。

計測結果

上記のようなデータが1日ごとで100万件、合計1500万件入っている状態のテーブルに対し、下記クエリーを実行した場合の計測時間は...
※時刻の値実運用データを想定し、ランダムでバラバラな値が入っている

mysql > select * from users where updated_at >= '2021-07-02 00:00:00' and updated_at <= '2021-07-02 23:59:59';
1000000 rows in set (31.83 sec)

mysql> explain select * from users where updated_at >= '2021-07-02 00:00:00' and updated_at <= '2021-07-02 23:59:59';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | index1        | NULL | NULL    | NULL | 14743320 |    13.20 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
--
mysql> select * from users where DATE_FORMAT(updated_at, '%Y-%m-%d') = '2021-07-02';
1000000 rows in set (32.44 sec)

mysql> explain select * from users where DATE_FORMAT(updated_at, '%Y-%m-%d') = '2021-07-02';
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14088086 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+

このような結果でした。それではこの値を基準に以下話を進めたいと思います。

パフォーマンスチューニング1

TimeStamp型よりも数値の方が扱いとして速度が出ます。
時刻をint型のタイムスタンプに直したカラムを用意し、それに対しクエリーを実行した場合の計測時間は...

mysql> SELECT * FROM users where updated_at_timestamp >= 1625151600 and updated_at_timestamp <= 1625237999;
1000000 rows in set (24.15 sec)

mysql> explain SELECT * FROM users where updated_at_timestamp >= 1625151600 and updated_at_timestamp <= 1625237999;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | index2        | NULL | NULL    | NULL | 14743320 |    13.20 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+

数値型の方が比較が速いため短縮されました！
しかしIndexが効いていないためあまり速くはならず、総データ量が多くなれば比例的に時間が伸びてしまうことが考えられる。

パフォーマンスチューニング2

ただ、主目的が1日ごとのデータを取得するのであれば、上記方法では不十分といえます。
timetampのカラムには日時ではなく、年月日のみの情報を入れ、同じ日であれば同じ値になるようにしてしまいましょう。
Laravelであれば、下記のように記述することで現在日時のタイムスタンプ値を取得することができます。

// 今日
Carbon::today('Asia/Tokyo')->timestamp

// 翌日
Carbon::today('Asia/Tokyo')->addDays(1)->timestamp

計測してみます...。

mysql> SELECT * FROM users where updated_at_timestamp = 1625151600;

1000000 rows in set (4.79 sec)

mysql> explain SELECT * FROM users where updated_at_timestamp = 1625151600;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+---------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | index2        | index2 | 5       | const | 2049284 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+---------+----------+-------+

まだまだ重いですがIndexも効いて改善見られました！

まとめ

日付を基準としたクエリー発行したい場合には、数値で扱う方が速いということ改めて確認できました。
この記事が皆さんのパフォーマンスチューニングの役にたてば幸いです

補足

もし、時刻カラムでしか絞り込むことはない、テーブルはログとしての扱いなどの場合には、パーティショニングをすることでパフォーマンス改善することが可能です。