mysql のインデックスと FROM_UNIXTIME の問題の詳細な説明

この記事では、主に mysql の index と FROM_UNIXTIME の間の問題に関する関連情報を紹介します。必要な方は、

Zero と Background

を参照してください。今週木曜日、私はたくさんのアラートを受け取りました。 DBA に問い合わせました 調べてみると、遅い クエリ を見つけました。

いくつかの情報を収集したところ、DBA を含む多くの人に尋ねた後も、このクエリの遅さの問題が深く隠されていることがわかりました。その理由はまだわかりません。

1. 問題

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

MySQL [d_union_stat]> desc t_local_cache_log_meta;
+----------------+--------------+------+-----+---------------------+
| Field     | Type     | Null | Key | Default       |
+----------------+--------------+------+-----+---------------------+
| 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 rows in set (0.01 sec)

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

MySQL [d_union_stat]> show index from t_local_cache_log_meta \G     
*************************** 1. row ***************************
    Table: t_local_cache_log_meta
  Non_unique: 0
   Key_name: PRIMARY
 Column_name: c_id
  Collation: A
 Cardinality: 6517096
  Index_type: BTREE
*************************** 2. row ***************************
.
.
.
*************************** 6. row ***************************
    Table: t_local_cache_log_meta
  Non_unique: 1
   Key_name: index_mtime
 Column_name: c_mtime
  Collation: A
 Cardinality: 592463
  Index_type: BTREE
6 rows in set (0.02 sec)

それから私は次のような SQL を書きました:

SELECT 
  count(*)
FROM
  d_union_stat.t_local_cache_log_meta
where
  `c_mtime` < FROM_UNIXTIME(1494485402);

ついにある日、DBA がやって来ました。よく言われるのは、この SQL は遅い SQL だということです。

rreee

私の DB はインデックス化されており、SQL は慎重に最適化されているのですが、なぜ SQL が遅いのでしょうか?

SQL が遅い理由を尋ねると、DBA は答えられませんでした。周りの同僚ですら答えられませんでした。

私は深く隠された知識点に遭遇したと密かに思いました。

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

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

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

そこで、問題は FROM_UNIXTIME 関数であると考えました。

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

1. WHERE 句に一致する行を迅速に検索するため。

複数のインデックスから選択がある場合、MySQL は通常、最小の行数を検索するインデックスを使用します。 3. テーブルに複数の列のインデックスがある場合、オプティマイザーはインデックスの左端のプレフィックスを使用して行を検索できます。
4. MySQL は、列が declare されている場合、より効率的に列のインデックスを使用できます。
異なる列の比較 (string 列と一時列または数値列の比較、
例) dir で値を比較できない場合、インデックスが使用される可能性があります。 記事 4 を見たときに、型が異なるとインデックスが作成されない可能性があると記載されていましたが、FROM_UNIXTIME の戻り値を string 型に変換できない可能性があるのでしょうか。 それでは、FROM_UNIXTIME

関数

の戻り値をクエリします。

MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return<p>s a <a href="http://www%20.php.cn/wiki/1255.html" target="_blank">date</a><a href="http://www.php.cn/code/6029.html" target="_blank"> /datetime (unix_timestamp のバージョンから) </a></p></a>
は時刻型を返します。それを文字列型に強制するのはどうでしょうか?

# Time: 170518 11:31:14
# Query_time: 12.312329 Lock_time: 0.000061 Rows_sent: 0 Rows_examined: 5809647
SET timestamp=1495078274;
DELETE FROM `t_local_cache_log_meta` WHERE `c_mtime`< FROM_UNIXTIME(1494473461) limit 1000;

今回はインデックスが使用され、1 つのデータのみがスキャンされていることがわかります。 MySQL FROM_UNIXTIME() <a href="http://www.php.cn/wiki/135.html" target="_blank">return</a>s a <a href="http://www.php.cn/wiki/1255.html" target="_blank">date</a> /datetime from a version of unix_timestamp.

2.まとめ

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

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

はい、これ以上言う必要はありません。この記事は単なる幕間です。後ほどアルゴリズムの紹介を続けましょう。

以上がmysql のインデックスと FROM_UNIXTIME の問題の詳細な説明の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。