mysql のインデックスと FROM_UNIXTIME に関する問題

ゼロ、背景

今週の木曜日にたくさんのアラートを受け取りました。DBA に確認を依頼したところ、遅いクエリが見つかりました。

いくつかの情報を収集しただけで、このクエリが遅い問題が非常に深く隠れていることがわかりました。DBA を含む多くの人に尋ねましたが、誰も原因を知りませんでした。

1. 問題

以下のように定義されたフィールドを持つDBがあります。

MySQL [d_union_stat]> desc t_local_cache_log_meta;
+----------------+--------------+------+-----+---------------------+
| フィールド | タイプ | Null | キー | デフォルト |
+----------------+--------------+------+-----+---------------------+
| c_id | int(11) | NO | PRI | NULL |
| c_key | varchar(128) | NO | MUL | |
| c_time | int(11) | NO | MUL | 0 |
| c_mtime | varchar(45) | NO | MUL | 0000-00-00 00:00:00 |
+----------------+--------------+------+-----+---------------------+
セット内の行数は 17 です (0.01 秒)

インデックスは次のとおりです。

MySQL [d_union_stat]> t_local_cache_log_meta \G からインデックスを表示     
************************** 1. 行 ****************************
    テーブル: t_local_cache_log_meta
  非ユニーク: 0
   Key_name: PRIMARY
 列名: c_id
  照合: A
 基数: 6517096
  インデックスタイプ: BTREE
************************** 2. 行 ****************************
。
。
。
************************** 6. 行 ****************************
    テーブル: t_local_cache_log_meta
  非ユニーク: 1
   キー名: index_mtime
 列名: c_mtime
  照合: A
 基数: 592463
  インデックスタイプ: BTREE
セット内の6行（0.02秒）

次に、次のような SQL を記述しました。

選択 
  カウント（*）
から
  d_union_stat.t_local_cache_log_meta
どこ
  `c_mtime` < FROM_UNIXTIME(1494485402);

ついにある日、DBA がやって来て、トランザクションを私に投げつけ、この SQL は遅い SQL だと言いました。

# 時刻: 170518 11:31:14
# クエリ時間: 12.312329 ロック時間: 0.000061 送信行数: 0 検査行数: 5809647
タイムスタンプを1495078274に設定します。
`t_local_cache_log_meta` から削除します。WHERE `c_mtime`< FROM_UNIXTIME(1494473461) 制限 1000;

私は言葉を失いました。DB はインデックス化され、SQL は慎重に最適化されていたのに、なぜ SQL が遅いのでしょうか?

SQL が遅い理由を尋ねたところ、DBA は答えられず、周囲の同僚に尋ねても答えられませんでした。

私は、非常に隠された知識に出会ったのだと思いました。

疑わしい点は2つあります。1. インデックスが6つあります。 2. 正しい値は FROM_UNIXTIME 関数です。

そこで、公式の MYSQL ドキュメントを確認したところ、6 は問題ないことがわかりました。

すべてのストレージエンジンは、テーブルごとに少なくとも 16 個のインデックスと、少なくとも 256 バイトの合計インデックス長をサポートします。
ほとんどのストレージエンジンには、より高い制限があります。

そこで、問題は FROM_UNIXTIME 関数にあるのではないかと疑いました。

次に、MYSQL の INDEX セクションを見て、いくつかの手がかりを見つけます。

1.WHERE 句に一致する行をすばやく見つけます。
2. 行を考慮から除外します。
複数のインデックスを選択できる場合、MySQL は通常、行数が最も少ないインデックスを使用します。
3. テーブルに複数列のインデックスがある場合、オプティマイザはインデックスの左端のプレフィックスを使用して行を検索できます。
4. MySQL では、列が同じタイプとサイズで宣言されている場合、列のインデックスをより効率的に使用できます。
異なる列の比較（たとえば、文字列列と時間列または数値列の比較）では、値を変換せずに直接比較できない場合、インデックスの使用が妨げられる可能性があります。

記事4を見たところ、型が違うとインデックスに従わない場合があると書いてありました。FROM_UNIXTIMEの戻り値を文字列型に変換することはできないのでしょうか？

次に、FROM_UNIXTIME 関数の戻り値を照会します。

MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.

返される値は時間型ですが、それを強制的に文字列型にするのはどうでしょうか?

MySQL [d_union_stat]> SELECTの説明 
  -> *
  -> から
  -> t_local_cache_log_meta
  -> ここで
  -> `c_mtime` = CONCAT(FROM_UNIXTIME(1494485402)) \G
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: t_local_cache_log_meta
     タイプ: ref
可能なキー: index_mtime
     キー: index_mtime
   キーの長さ: 137
     参照: 定数
     行数: 1
    追加: where の使用
セット内の1行（0.01秒）

今回は、インデックスが使用され、 1 つのデータのみがスキャンされることがわかります。

2. 結論

今回は、FROM_UNIXTIME の戻り値を強制的に変換することでインデックスを使用できます。

したがって、この SQL では、右側の値と左側の値の型が一致しないため、上位インデックスを使用できません。。

さて、これ以上は何も言いません。この記事は単なる幕間です。アルゴリズムについては後ほど引き続き紹介します。

以下もご興味があるかもしれません: