[MySQL] MariaDB 文字コードの不一致によりINDEX検索が激遅に

前回までの記事でMariaDBをいろいろと弄っていた所、INDEX作成済み項目でJOINやWHEREをしているにもかかわらず結果取得にめちゃくちゃ時間がかかるケースがあった。

SQLのイメージはこんな感じ。

select * from TABLE1
LEFT JOIN TABLE2 ON TABLE1.KEYCOL1 = TABLE2.KEYCOL1 AND TABLE1.KEYCOL2 = TABLE2.KEYCOL2
WHERE EXISTS (SELECT 1 FROM TABLE3 WHERE KEYCOL1 = TABLE1.KEYCOL1 AND KEYCOL2 = TABLE1.KEYCOL2)

各テーブルのデータ件数がかなり多いため、時間がかかるのは仕方がないのかな...と当初は思っていた。

EXPLAINで調査

SQL速度に困ったらまず実行計画を確認。

実行しようとしているSQL文の頭に「EXPLAIN」を追加すると、SQL文の実行計画を取得することができる。

実行結果イメージはこんな感じ。

+------+--------------------+--------+-------+-----------------+-----------+---------+----------------+----------+----------+--------------------------+
| id   | select_type        | table  | type  | possible_keys   | key       | key_len | ref            | rows     | filtered | Extra                    |
+------+--------------------+--------+-------+-----------------+-----------+---------+----------------+----------+----------+--------------------------+
|    1 | PRIMARY            | TABLE1 | ALL   | NULL            | NULL      | NULL    | NULL           | 243604   |    46.68 | Using where              |
|    1 | PRIMARY            | TABLE2 | ALL   | NULL            | NULL      | NULL    | NULL           | 323873   |   100.00 | Using                    |
|    2 | DEPENDENT SUBQUERY | TABLE3 | index | NULL            | INDEX3    | 182     | NULL           | 2441569  |   100.00 | Using where; Using index |
+------+--------------------+--------+-------+-----------------+-----------+---------+----------------+----------+----------+--------------------------+

ポイントは「type」。

「ALL」はインデックスが効いておらず全データ検索をしており激遅。

「index」はインデックスは効いているものの、インデックス全体をスキャンしており速度低下の原因になる。

大本のTABLE1はまだ未調整なので置いとくとして、JOINとWHEREで使っているTABLE2とTABLE3はKEYCOL1もKEYCOL2もインデックスを張っており、サクッと取って来れそうなんだが...。

FORMAT=JSON指定する

どうやらMySQLのEXPLAINにはいくつかオプションがあり、FORMAT=JSONを指定するとJSON形式で結果取得できる・・・だけではなく、JSON時のみ出力される項目があるらしい。なんだその隠し仕様。

 {
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "TABLE1",
      "access_type": "ALL",
      "rows": 121802,
      "possible_keys": "NULL",
      "attached_condition": "B = 1 and C is not null"
    },
      "table": {
        "table_name": "TABLE2",
        "access_type": "ALL",
        "possible_keys": "NULL",
        "rows": 323873,
        "attached_condition": "convert(TABLE2.KEYCOL1 using utf8mb4) AND convert(TABLE2.KEYCOL2 using utf8mb4) = TABLE2.KEYCOL2",
      }
    },
    "subqueries": [
      {
        "query_block": {
          "select_id": 2,
          "table": {
            "table_name": "TABLE3",
            "access_type": "index_subquery",
            "possible_keys": ["KEYCOL1", "KEYCOL2"],
            "key": "KEYCOL1",
            "key_length": "344",
            "used_key_parts": ["KEYCOL1", "KEYCOL2"],
            "rows": 2441569,
            "attached_condition": "trigcond(convert(TABLE3.KEYCOL1 using utf8mb4) = TABLE1.KEYCOL1 AND convert(TABLE3.KEYCOL2 using utf8mb4) = TABLE1.KEYCOL2)",
            "using_index": true
          }
        }
      }
    ]
  }

↑こちらも一部情報をオミットしている。あくまでもイメージということで。

出てきた情報を眺めていると、WHERE区やJOIN ON区に書いた検索条件"attached_condition"の項目が"convert(TABLE2.KEYCOL1 using utf8mb4) = TABLE1.KEYCOL"となっている。

convertは文字コードの変換を行う関数だが、なぜこんなものが内部的に呼ばれているのか。

テーブル定義を再確認してみる

なぜWHERE区やJOIN ON区の照合で文字コード変換をしているのか...？

テーブルのCREATE文を確認すると、TABLE2とTABLE3だけ文字コードが合っていなかった。

CREATE TABLE `TABLE1` (
  中略
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

CREATE TABLE `TABLE2` (
  中略
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

CREATE TABLE `TABLE3` (
  中略
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

全テーブルutf8mb4を指定していたつもりだったのが、一部だけ不注意でutf8になっていたのだ。

照合する項目の文字コードが一致しておらず、MariaDBが暗黙的に変換してくれていたのだろう（気づかなかった...）。

どちらのテーブルも作りたてだったのでサクッと削除し、utf8mb4で再作成＆データ流し込み。

+------+-------------+--------+------+-----------------+-----------+---------+----------------+--------+----------+--------------------------+
| id   | select_type | table  | type | possible_keys   | key       | key_len | ref            | rows   | filtered | Extra                    |
+------+-------------+--------+------+-----------------+-----------+---------+----------------+--------+----------+--------------------------+
|    1 | PRIMARY     | TABLE1 | ALL  | NULL            | NULL      | NULL    | NULL           | 243604 |    46.68 | Using where              |
|    1 | PRIMARY     | TABLE2 | ref  | INDEX1          | INDEX1    | 242     | TABLE1.KEYCOL1 | 323873 |   100.00 | Using where; Using index |
|    1 | PRIMARY     | TABLE3 | ref  | INDEX2          | INDEX2    | 182     | TABLE1.KEYCOL1 | 157324 |   100.00 | Using where; Using index |
+------+-------------+--------+------+-----------------+-----------+---------+----------------+--------+----------+--------------------------+

EXPLAINで確認するとtypeが"ref"に変わった。primary key（ユニーク）ではないINDEXで検索できている証拠だ。

（この後、データを見直してKEYCOLをprimary key指定したらtypeが"ref"から"const"に変わり、rows=1になった。主キーで検索するのが最も速い）

厳密には照合順序の違いが原因か

今回KEYCOLとして指定していた項目はvarchar型だったので、文字コード違いによる影響を受けたと思われる。

当初はconvert関数の内部呼出回数が多くて速度低下しているのかと思ったが、INDEXを正しく使用できていなかったところを見ると、おそらく文字コードが異なる＝照合順序が異なることでINDEXの照合が正しく行われていなかったのだろう。

SQLの速度問題は様々な原因があり、SQL文の最適化やINDEXの張り方、データの持たせ方などなど、ありがちな部分からチェックしていったのだが、テーブルの文字コード設定まではなかなか気が回らなかった。

余談

調査の中で「カバリングインデックス」（取得項目も含めて全部INDEXにブチ込んだら速いよ！）という手法に出会えたのも幸いだった。

INDEXといえばキー項目に張るものだという思い込みがあり、きちんと調べたことがなかった。

今回WHEREやJOINで使いたいテーブルは「項目数少、データ量大」で、なかなか速度が出ず困っていたので本当に助かった。

7MC.ORG