MySql SQL最適化のヒントの共有

ある日、内部結合を含む SQL ステートメントの実行速度はそれほど遅くはない (0.1 ～ 0.2) ものの、理想的な速度には達していないことに気付きました。 2 つのテーブルは関連付けられており、関連付けられているフィールドは主キーであり、クエリされたフィールドは一意のインデックスです。

SQL は次のとおりです。

選択
p_item_token.*,
p_item.製品タイプ
から
p_item_token
p_item を p_item.itemid = p_item_token.itemid に内部結合します。
どこ
p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';

テーブル p_item_token では、itemid が主キーであり、token が一意のインデックスです。 p_itemのitemidは主キーです

理想的な速度は0.03秒程度です。しかし、実際の値は約 0.2 であり、はるかに遅くなります。

計画を確認するには直接EXPLAINしてください

説明する
選択
  p_item_token.*,
  p_item.製品タイプ
から
  p_item_token
p_item を p_item.itemid = p_item_token.itemid に内部結合します。
どこ
  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';

結果：

上の大きな赤いボックスに注意してください。 p_item テーブルには 20,000 件のレコードがあるため、これは完全なテーブルスキャンになります。

それは普通じゃない。

警告を表示して確認します。注意: 場合によっては、SHOW WARNINGS が効果を持たないことがあります。理由はまだ分かりません。ローカルテストデータベースで実行することをお勧めします。

説明する
選択
  p_item_token.*,
  p_item.製品タイプ
から
  p_item_token
p_item を p_item.itemid = p_item_token.itemid に内部結合します。
どこ
  p_item_token.token = 'db87a780427d4d02ba2bd49fac8xxx';
警告を表示;

結果 2 には code=1003 が表示されます。その背後には SQL ステートメントがあります。このステートメントは、入力した SQL ステートメントをルールに従って書き換えた後に MySQL によって実行される最終ステートメントです。

/* 選択#1 */
選択
  '0000eb612d78407a91a9b3854ffffffff' AS `itemid`, /*注: 値を見つけるには主キーを直接押します*/
  'db87a780427d4d02ba2bd49fac8cf98b' を `トークン` として、    
  '2016-12-16 10:46:53' として `create_time`、        
  '' AS `ftoken`、                    
  `p_db`.`p_item`.`product_type` を `product_type` として  
から
  `p_db`.`p_item_token`
`p_db`.`p_item` を結合します
どこ
  （
    （
      変換する （
        `p_db`.`p_item`.`itemid` utf8mb4 を使用
      ) = '0000eb612d78407a91a9b3854fffffff'
    ）
  ）

それは奇妙ですね。 Where に CONVERT があるのはなぜですか? where 条件の式の左側、つまりクエリ対象のフィールドに関数がある場合、速度低下の原因になることがわかっています。 (私の理解では、インデックスが使用されていないため遅くなります。インデックスの値は元の値ですが、この条件で使用される値は処理された値です。)

この関数は、itemid 列のエンコーディングを utf8mb4 に変換することに注意してください。つまり、この列のエンコーディングは utf8mb4 ではありません。

テーブルを開き、両方のテーブルの itemid 列のエンコーディングを utf8 に変更します。説明をもう一度実行します。

結果を説明することに問題はありません。

結果 2 のステートメントを見てください。

/* 選択#1 */
選択
  '0000eb612d78407a91a9b3854fffffff' として `itemid`、
  'db87a780427d4d02ba2bd49fac8cf98b' を `トークン` として、
  '2016-12-16 10:46:53' として `create_time`、
  '' AS `ftoken`、
  'cxx' を `product_type` として
から
  `toy_item_plat`.`p_item_token`
`toy_item_plat`.`p_item` を結合します
どこ
  1

この選択には定数がいっぱいあります。どうして遅いのでしょうか?

実行結果: 0.036秒。期待に応える

学んだ教訓:

Explain は、実行プランが期待どおりかどうかを確認できます。大きな行がある場合は、完全なテーブルスキャンが発生したことを意味し、将来的にパフォーマンスのボトルネックになります。

show warning コマンドを使用すると、オプティマイザーによって処理されたステートメントを確認できます。元の記述と矛盾がある場合は、慎重に比較して検討することで実際の問題が明らかになることがあります。

以下もご興味があるかもしれません: