MySQL 最適化のケーススタディ

1. 背景

Youzan の各 OLTP データベースインスタンスには、実行時間が特定のしきい値を超える SQL ステートメントを強制終了するための sql-killer プロセスが設定されています。午後、開発者はSQLが強制終了したというエラーメッセージを受け取り、開発者のトラブルシューティングを支援しました。この記事ではこのケースを紹介します。

第2のシナリオ分析

テーブル構造:

テーブル `xxx_info` を作成します (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',

  `user_id` bigint(20) unsigned NOT NULL DEFAULT '0' ,

  `group_id` bigint(20) unsigned NOT NULL DEFAULT '0',

  `nick_name` varchar(30) NOT NULL DEFAULT '' COMMENT 'ニックネーム',

  `is_del` tinyint(5) NOT NULL DEFAULT '0' COMMENT '0: データが有効、1: データは論理的に削除されている',

  `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',

  `updated_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '変更時刻',

  主キー (`id`)、

  キー `idx_userid_groupid` (`user_id`,`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1382032 デフォルトCHARSET=utf8mb4 ;

問題のSQLは次のとおりです

 xxx_info から id、name、status を選択します。user_id は (670039223,'373149878') であり、group_id は 1 であり、is_del は 0 です。

最初に SQL を見たとき、テーブル構造とインデックス user_id をチェックしました。これは数値型で、インデックスは正常でした。その後、手動実行プランでは idx_userid_groupid インデックスが使用されませんでした。

2つの異なるタイプのフィールドのuser_idが「暗黙の変換」を引き起こしている疑いがあります。パラメータ値を数値型または文字列に変更するか、user_id=数値型またはuser_id=文字列を使用して再度実行してください。

実行計画は正しいです。 2つの問題を解決する必要があります

では、(X,Y,Z) の user_id が異なるタイプである場合、なぜインデックスが通過しないのでしょうか?

実行プランをトレースするには、optimizer_trace を使用します。

セッション optimizer_trace を 'enabled=on' に設定します。

xxx_info から id、nick_name、is_del を選択します。user_id は (670039223,'373149878') であり、group_id は 1 であり、is_del は 0 です。

information_schema.optimizer_trace から * を選択します。

xxx_info から id、nick_name、is_del を選択します。user_id は (670039223,'373149878') であり、group_id は 1 であり、is_del は 0 です。

information_schema.optimizer_trace から * を選択します。

セッション optimizer_trace を 'enabled=off' に設定します。

2つのSQL文の実行計画を取得し、比較します。結果は次のようになります。