TIL MySQL 21/11/26

A I PK検索を使用してatを作成する
の原因となる
履歴書表があります.中には約178万部の資料があります.ご覧のように、auto increment PKを除いて、インデックスがない場合は避けます.

CREATE TABLE `foo_history` (
  `foo_history_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Foo History ID',
  `foo_id` int(10) unsigned NOT NULL COMMENT 'Foo ID',
  /* 다른 컬럼도 많지만 여기서는 생략 */
  `register_date` datetime NOT NULL COMMENT '등록 일자',
  `modify_date` datetime DEFAULT NULL COMMENT '수정 일자',
  PRIMARY KEY (`foo_history_id`),
  KEY `nix-foo_history-foo_id` (`foo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1791092 DEFAULT CHARSET=utf8mb4 AVG_ROW_LENGTH=963 COMMENT='Foo History'

ここで10分前~今から生成するfoo_idを選びます思わず質問を出した.これは、上記のように実行すればよいクエリーなので、実行計画も悪くないように見えます.

SELECT /* 중략 */
FROM foo_history fh
WHERE fh.foo_id IN (179)
AND fh.register_date >= now() - INTERVAL 10 MINUTE

でもやってみたの?50秒以上かかります.
結果は出るが、これは納得できない困難なレベルだ.どうしよう.50秒我慢しますか?私は何かを見たいのではないでしょうか.どこかで聞いた最高のインデックスをふと思い出して、クエリーを栓に変えました.

SELECT /* 중략 */
FROM foo_history fh
WHERE fh.foo_id IN (179)
AND fh.foo_history_id >= (
    SELECT MIN(foo_history_id) FROM foo_history
    WHERE register_date >= now() - INTERVAL 10 MINUTE
);

強すぎるように見えますが、EXPLAINもよく見えません.「計画出力の実行に時間がかかるのを見たのは初めてです.」そうですね.このように改造されたクエリーが成功するはずがありません.では、今は数倍の時間がかかります.すぐに確認すればいいだけです.
うん???出てこないと思った結果が出てきたもっと速いです.11.281 s+3 msだそうです.これは…です.ベロッグに行かなきゃ!!
n/a原理
まず、なぜ遅いクエリーが遅いのかを説明するには、簡単に言います.foo_history.register_date列にインデックスはありません.それをチェックするには、フルスキャンをしなければなりません.この部分のこの異常の詳細な説明は省略する.
しかしfoo_history表では、幸いにも自動増減基本キーfoo_history_idがある.もしそうであれば,その基本鍵が大きいほどregister_dateの値も大きくなることが期待できる.次の場合:

は、まずfoo_historyを巡回し、register_dateの条件を満たす最小PK値Fを見つけ、

を見つける.

以降のfoo_historyを問い合わせると、PK >= Fをユーザに検索させるだけで

が見つかる.

MySQLは考えずにPKインデックスを閲覧し、PK < Fの資料を全く見ません.

だから速度が上がる.
最初のフェーズ自体に時間がかかるため、クエリのパフォーマンスは向上しないと予想されます.しかし、その結果、困難なことをすると、次のステップからインデックスを使用して資料全体の90%以上を無視することができ、最終的にはパフォーマンスが改善されます.
教訓

インデックスを創造的に利用します.

いずれにしても、何でも試してみましょう.

一度に難しい仕事を完成した経験から何かを学び、その後の仕事は容易になります.検索と人生...