ホームページ > 記事 > データベース > mysql のインデックスと FROM_UNIXTIME の問題の詳細な説明

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

小云云オリジナル: 2018-01-17 10:45:081897ブラウズ

この記事では主に mysql のインデックスと FROM_UNIXTIME の問題に関する情報を紹介します。必要な方は参考にしていただければ幸いです。

ゼロ、背景

いくつかの情報を収集したところ、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 だと概要を教えてくれました。

# 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;

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

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

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

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

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

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

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

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

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

記事 4 を見たとき、異なる型が存在する可能性があると述べられていました。不整合の原因インデックスを調べると、FROM_UNIXTIME の戻り値を文字列型に変換できないのでしょうか?

それでは、FROM_UNIXTIME 関数の戻り値をクエリします。

返されるのは時刻型ですが、強制的に文字列型にするのはどうでしょうか？ MySQL FROM_UNIXTIME() returns a date /datetime from a version of unix_timestamp.

MySQL [d_union_stat]> explain SELECT 
  ->   *
  -> FROM
  ->   t_local_cache_log_meta
  -> where
  ->   `c_mtime` = CONCAT(FROM_UNIXTIME(1494485402)) \G
*************************** 1. row ***************************
      id: 1
 select_type: SIMPLE
    table: t_local_cache_log_meta
     type: ref
possible_keys: index_mtime
     key: index_mtime
   key_len: 137
     ref: const
     rows: 1
    Extra: Using where
1 row in set (0.01 sec)

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

2.まとめ

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

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

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

関連記事