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

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 では、右側の値と左側の値の型が一致しないため、上位インデックスを使用できません。 。

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

以下もご興味があるかもしれません:
  • MySQL インデックスのパフォーマンス最適化の問題に対する解決策
  • MySQL バッチ挿入とユニークインデックスの問題に対する解決策
  • MySQL テーブルの読み取り、書き込み、インデックス作成、その他の操作の SQL ステートメントの効率最適化の問題を分析します。
  • MySQLのINサブクエリによってインデックスが使用できなくなる問題を解決する
  • MySQLインデックスについて理解しておくべきいくつかの重要な問題
  • MySQLのインデックスによって引き起こされるCPU負荷の急増を分析する
  • php mysql インデックスの問題
  • MySQL インデックスの一般的な問題の概要

<<:  SVNサービスバックアップ操作手順の共有

>>:  Vue コンポーネント値転送中のデータ損失の分析と解決

推薦する

Windows 10 の Docker で countly-server を展開して実行するプロセス

私は最近countlyに触れて、慣れてきました。私は、必要に応じてcountlyのクラッシュプラグイ...

シンプルな計算機を実装するためのネイティブ js

この記事の例では、参考までに簡単な計算機を実装するためのjsの具体的なコードを共有しています。具体的...

VUE無限レベルツリーデータ構造表示の実装

目次コンポーネントの再帰呼び出しレンダリングメソッドの使用プロジェクトに取り組んでいると、左側のメニ...

JavaScript の基本オブジェクト

目次1. オブジェクト1.1 オブジェクトとは何ですか? 1.2 なぜオブジェクトが必要なのか? 2...

Vue-cliフレームワークはタイマーアプリケーションを実装します

技術的背景このアプリケーションは vue-cli フレームワークを使用し、カスタム コンポーネント ...

IDEA 2020.3.1 で Tomcat をデプロイし、最初の Web プロジェクトを作成するプロセスの詳細な説明

目次Tomcat の紹介Tomcat の展開Web プロジェクトの作成tomcatの設定プロジェクト...

HTML スタイル タグと関連する CSS リファレンスの詳細な説明

HTML スタイル タグスタイルタグ - ドキュメント内でスタイルを宣言するときにこのタグを使用しま...

vue+springbootでログイン機能を実現

この記事の例では、ログイン機能を実現するためのvue+springbootの具体的なコードを参考まで...

実践で遭遇するフロントエンドの基本(HTML、CSS)

1. div css マウスの手の形は cursor:pointer; です。 2. HTML の相...

ウェブサイトデザインにおいて非常に重要な概念であるdiv+floatの分析

ウェブサイトの構築では、HTML と CSS に関するさまざまな問題に常に遭遇します。ウェブサイト ...

JavaScriptは文字の出現回数をカウントします

この記事の例では、文字の出現回数をカウントするJavaScriptの具体的なコードを参考までに共有し...

中央のテキストの両側に水平線を描くためのCSS

1. vertical-align プロパティは次の効果を実現します。 vertical-alig...

シーケンス関数を実装する MySQL コード

MySQLはシーケンス関数を実装する1. シーケンスレコードテーブルを作成する テーブル `sys_...

Linux gzipコマンドの使用

1. コマンドの紹介gzip (GNU zip) コマンドは、ファイルの圧縮と解凍に使用されます。こ...