フィールドの文字セットの違いによる MySQL のインデックス失敗の解決策

インデックスとは何ですか?なぜインデックスを作成するのですか?

インデックスは、列に特定の値を持つ行をすばやく見つけるために使用されます。インデックスがない場合、MySQL は最初のレコードからテーブル全体を読み取って、関連する行を見つける必要があります。テーブルが大きいほど、データのクエリにかかる時間は長くなります。テーブル内のクエリ対象の列にインデックスがある場合、MySQL はすべてのデータを調べなくてもデータファイルを検索する場所にすばやく到達できるため、多くの時間を節約できます。

たとえば、20,000 件のレコードがあり、20,000 人の個人の情報が記録されている person テーブルがあるとします。各人の電話番号を記録する Phone フィールドがあります。ここで、電話番号が xxxx である人の情報を照会します。

インデックスがない場合、情報が見つかるまでテーブルは最初のレコードから次のレコードまで走査されます。

インデックスがある場合、Phone フィールドは特定の方法で保存されるため、このフィールドの情報を照会するときに、20,000 のデータ項目を走査することなく、対応するデータをすばやく見つけることができます。 MySQL には、BTREE と HASH の 2 種類のインデックスストレージがあります。つまり、ツリーまたはハッシュ値を使用してフィールドを保存します。詳細な検索方法を知るには、アルゴリズムを知る必要があります。ここで、インデックスの役割と機能を知る必要があります。

導入

今日は SQL ステートメントを書きました。関係するテーブルのデータ量は約 500,000 でした。クエリには 8 秒かかりました。これはあくまでテストサーバーのデータです。公式サーバーに置くと、実行するとすぐにクラッシュするはずです。

選択
 注文。いいえ、
 ガイド番号、
 注文.作成時間、
 sum(OrderItem.Quantity) AS 数量、
 Brand.NAME は BrandName として、
 メンバー.モバイル、
 配送先住所のストリート、
 エリア
から
 注文
内部結合 OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN ブランド ON ブランド.Id = Orders.BrandId
メンバーの内部結合メンバー on member.Id = 13
メンバーアドレスをメンバー.Id = メンバーアドレス.MemberId に内部結合します
どこ
 注文.GuidNo IN (
  選択
   注文支払.注文ガイド番号
  から
   支払い記録
  orderpayment を paymentrecord.`No` に LEFT JOIN します = orderpayment.PaymentNo
  どこ
   支払い記録.支払い方法 = 'メンバーカード'
  AND 支払記録.支払者 = 13
 ）
グループ化
 ガイド番号;

そこでEXPLAINを使って解析してみると、Ordersテーブルがインデックスにヒットしていないことがわかりました。しかし、OrdersのGuidNoにはインデックスが設定されていたのですが、ヒットできませんでした。

解決プロセス

次に、上記のステートメントを 2 つのステートメントに分割します。まず、SQL ステートメントを次のように変更します。サブクエリデータを SQL ステートメントに直接書き込むと、クエリには 0.12 秒かかります。

選択
 注文。いいえ、
 ガイド番号、
 注文.作成時間、
 sum(OrderItem.Quantity) AS 数量、
 Brand.NAME は BrandName として、
 メンバー.モバイル、
 配送先住所のストリート、
 エリア
から
 注文
内部結合 OrderItem ON Orders.GuidNo = OrderItem.OrderGuidNo
INNER JOIN ブランド ON ブランド.Id = Orders.BrandId
メンバーの内部結合メンバー on member.Id = 13
メンバーアドレスをメンバー.Id = メンバーアドレス.MemberId に内部結合します
どこ
 注文.GuidNo IN (
  '0A499C5B1A82B6322AE99D107D4DA7B8'、
  '18A5EE6B1D4E9D76B6346D2F6B836442',
  '327A5AE2BACEA714F8B907865F084503',
  'B42B085E794BA14516CE21C13CF38187'、
  'FBC978E1602ED342E5567168E73F0602'
 ）
グループ化
 ガイド番号

2番目: サブクエリのみを実行するSQLも0.1秒しかかかりません

選択
   注文支払.注文ガイド番号
  から
   支払い記録
  orderpayment を paymentrecord.`No` に LEFT JOIN します = orderpayment.PaymentNo
  どこ
   支払い記録.支払い方法 = 'メンバーカード'
  AND 支払記録.支払者 = 13

これで問題は明らかです。これはサブクエリと親クエリに関連する問題であるはずです。サブクエリは単独では非常に高速であるため、親クエリもサブクエリデータを直接使用すると非常に高速になりますが、2 つを組み合わせると非常に遅くなります。この問題は、大まかに言って、関連する 2 つのフィールド OrderGuidNo に起因します。

最後に、orderpayment テーブルと Orders テーブルの文字セットが異なることがわかります。一方のテーブルの文字セットは utf8_general_ci で、もう一方のテーブルの文字セットは utf8mb4_general_ci です。 (確認するまでわかりません。データベースでは、多くのテーブルに異なる文字セットがあることがわかります。)

orderpayment テーブルの文字セットとテーブル内の OrderGuidNo の文字セットを utf8_general_ci に変更します。

ALTER TABLE orderpayment DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; //テーブルの文字セットを変更します ALTER TABLE orderpayment CHANGE OrderGuidNo OrderGuidNo VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; //フィールドの文字セットを変更します

次に EXPLAIN を使用して分析すると、インデックスが使用されていることがわかります。