とある日

Perconaをみてて気になる記事を発見！

Low Value in range_optimizer_max_mem_size Might Cause Full Table Scan

翻訳↓

range_optimizer_max_mem_sizeの値が低いと、全表スキャンが発生する可能性があります

気になったので検証します。

記事概要

詳しい内容が知りたい方は、実際の記事を御覧ください。

要約すると、サーバーシステム変数である、range_optimizer_max_mem_sizeの設定が低すぎると、範囲オプティマイザが使用できるメモリが少なすぎてINDEXを使用した検索ができなるなりフルスキャンが発生するといった内容。

それぞれの用語について少し解説を加えてから検証します。

結果が知りたい方は検証結果を御覧ください。

例え話

DBのことがわからない友人に説明してていい例え話がないか考えた結果生まれた例え話。

f:id:Updraft:20210205003228p:plain

道を通るのに人数に応じて通行料がかかるとする。

道は2つ近道と回り道が存在する。

財布には一定のお金が入っている。

財布が大きければ財布に入れれる金額も多くなる。

すると、大人数でも近道を通ることができる。

しかし、財布が小さいと少額の金額しか保持できず回り道しか選択できなくなるっていう話。

図と文章中の用語をDBに変換した対応表

図上の名前	文章の名前	DBの名前
サ	財布	range_optimizer_max_mem_size
通	人	一行のデータ
回り道（100km）	回り道	フルインデックススキャン（全表スキャン）
近道（1km）	近道	範囲インデックススキャン（range）
門	門番	オプティマイザ
料金	金額	消費メモリ量

この例え話で、大体のイメージが掴んでいただけると嬉しい。

絵が下手なのはご愛嬌。

range_optimizer_max_mem_sizeとは

MySQL8.0リファレンスマニュアル

範囲オプティマイザのメモリ消費の制限。値0は、「制限なし」を意味します。」オプティマイザによって検討実行計画は、レンジアクセスメソッドを使用していますが、オプティマイザは、この方法のために必要なメモリの量が制限値を超えると推定した場合は、計画を放棄し、他の計画を検討します。詳細については、範囲最適化のためのメモリ使用の制限を参照してください。

サーバーシステム変数の一つで。

範囲オプティマイザのメモリ制限のパラメータとして使用される。

範囲オプティマイザ

rangeアクセス方法は、1つまたはいくつかの指標値間隔内に含まれる表の行のサブセットを取得するために、単一のインデックスを使用します。単一部分または複数部分のインデックスに使用できます。次のセクションでは、オプティマイザが範囲アクセスを使用する条件について説明します。

今回は単一部分インデックスを使用した検索での検証を行います。

なので、少し解説。

複数部分インデックスはまたの機会に・・・。

単一部分インデックス

単一部分のインデックスの場合、インデックス値の間隔は、「間隔」ではなく範囲条件WHEREとして示される、句内の対応する条件によって便利に表すことができます。」

単純なWHERE句の比較時に使用される認識で良いと思います。

単一部分インデックス使用条件

両方のためBTREE及び HASH使用する場合索引、一定の値を有するキー部の比較はレンジ条件で =、 <=>、 IN()、IS NULL、またはIS NOT NULL演算子。
また、のためにBTREE使用した場合、インデックス、一定の値とキー部分の比較はレンジ条件がある >、 <、 >=、 <=、 BETWEEN、 !=、または <> オペレータ、またはLIKE 比較の引数があれば LIKEワイルドカード文字で始まらない定数文字列です。
すべてのインデックスタイプについて、複数の範囲条件が範囲条件と組み合わされるORか AND、範囲条件を形成します。

単一部分インデックス例

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

検証

では、実際に検証したいと思います。

検証環境

MySQL8.0.23で検証します。

updraft.hatenadiary.com

下準備

検証テーブル作成

CREATE TABLE ran_mem (
    i int AUTO_INCREMENT PRIMARY KEY,
    ts date NOT NULL,
    s varchar(255) NOT NULL
);

INDEXを使用するため、INDEXを作成するPRIMARY KEYなどの設定を忘れずに。

検証データ生成

INSERT INTO
    ran_mem (ts, s)
SELECT
    DATE_ADD('1900-02-03', INTERVAL 10000 * RAND() DAY),
    SUBSTRING(MD5(RAND()), 1, 10)
FROM
    performance_schema.events_errors_summary_by_thread_by_error;

performance_schema.events_errors_summary_by_thread_by_errorの部分は、お好きなテーブルを指定してください。

検証用に100万件ぐらいのデータを挿入するのでテーブル件数は多いほうが良いです。

どのテーブルにどの程度の行数があるかわかるSQL↓

SELECT
    TABLE_SCHEMA,
    table_name,
    table_rows
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA IN (
        'mysql',
        'sys',
        'information_schema',
        'performance_schema'
    )
ORDER BY
    TABLE_ROWS DESC;

実行結果↓

適当なテーブル選択してお好きな件数挿入してください。

mysql> SELECT TABLE_SCHEMA, table_name, table_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ( 'sys', 'information_schema', 'performance_schema' ) ORDER BY TABLE_ROWS DESC LIMIT 3\G;
*************************** 1. row ***************************
TABLE_SCHEMA: performance_schema
  TABLE_NAME: events_errors_summary_by_thread_by_error
  TABLE_ROWS: 385280
*************************** 2. row ***************************
TABLE_SCHEMA: performance_schema
  TABLE_NAME: events_errors_summary_by_host_by_error
  TABLE_ROWS: 192640
*************************** 3. row ***************************
TABLE_SCHEMA: performance_schema
  TABLE_NAME: events_errors_summary_by_account_by_error
  TABLE_ROWS: 192640
3 rows in set (0.01 sec)

データ確認

mysql> SELECT
    -> COUNT(*)
    -> FROM
    -> ran_mem;
+----------+
| COUNT(*) |
+----------+
|  1038460 |
+----------+
1 row in set (0.11 sec)

参考記事は、200万件でしたが自分は100万件ぐらいにしました。

下準備完了。

range_optimizer_max_mem_size 変更前

range_optimizer_max_mem_sizeを変更する前に、現在のINDEXなどを確認します。

range_optimizer_max_mem_size確認

mysql> SHOW session  VARIABLES  LIKE 'range_optimizer_max_mem_size';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| range_optimizer_max_mem_size |8388608|
+------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

実行速度

range_optimizer_max_mem_sizeの値を変更すると、速度が変動するので要確認。

mysql> select * from ran_mem where i in (1,2,3,4,5,6,7,8,9,10);
+----+------------+------------+
| i  | ts         | s          |
+----+------------+------------+
|  1 | 1908-06-22 | 593ef73243 |
|  2 | 1916-12-20 | a7afc80380 |
|  3 | 1926-04-03 | 06b61c0060 |
|  4 | 1916-09-29 | 604d150749 |
|  5 | 1926-04-12 | 695f21ee12 |
|  6 | 1919-03-10 | 665040f79c |
|  7 | 1917-09-16 | daba0aa8c6 |
|  8 | 1926-01-07 | 92a69e8403 |
|  9 | 1905-08-13 | 042057bafa |
| 10 | 1908-07-21 | b59eb44ead |
+----+------------+------------+
10 rows in set (0.00 sec)

EXPLAIN

mysql> explain select * from ran_mem where i in (1,2,3,4,5,6,7,8,9,10)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ran_mem
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type:rangeでINDEXが使用されていることが確認できます。

range_optimizer_max_mem_size変更後

range_optimizer_max_mem_size変更

SET SESSION range_optimizer_max_mem_size = 500;

任意の値を設定してください。

あまりrange_optimizer_max_mem_sizeの値を多く設定したり、クエリの条件式を少なくするとメモリ範囲内で検索可能となり検証したい動作にならない可能性があるのでご注意を。

値の再変更がめんどくさい場合は、SESSIONをつければ接続解除後にデフォルト値に戻ります。

range_optimizer_max_mem_size確認

mysql> SHOW session  VARIABLES  LIKE 'range_optimizer_max_mem_size';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| range_optimizer_max_mem_size |  500  |
+------------------------------+-------+
1 row in set, 1 warning (0.00 sec)

実行速度

mysql> select * from ran_mem where i in (1,2,3,4,5,6,7,8,9,10);
+----+------------+------------+
| i  | ts         | s          |
+----+------------+------------+
|  1 | 1908-06-22 | 593ef73243 |
|  2 | 1916-12-20 | a7afc80380 |
|  3 | 1926-04-03 | 06b61c0060 |
|  4 | 1916-09-29 | 604d150749 |
|  5 | 1926-04-12 | 695f21ee12 |
|  6 | 1919-03-10 | 665040f79c |
|  7 | 1917-09-16 | daba0aa8c6 |
|  8 | 1926-01-07 | 92a69e8403 |
|  9 | 1905-08-13 | 042057bafa |
| 10 | 1908-07-21 | b59eb44ead |
+----+------------+------------+
10 rows in set, 1 warning (0.40 sec)

変更前と比べて0.4 secと実行時間が増えている。

EXPLAIN

mysql> explain select * from ran_mem where i in (1,2,3,4,5,6,7,8,9,10)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ran_mem
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1036256
     filtered: 50.00
        Extra: Using where
1 row in set, 2 warnings (0.00 sec)

type:ALLとINDEXが使用されていなくなっている。

検証結果

MySQL8.0.23の環境下で、range_optimizer_max_mem_sizeを低く設定すると、フルスキャンになり実行速度が低下する。

range_optimizer_max_mem_size	type	テーブル件数	実行速度(sec)
8388608（デフォルト値）	range	100万	0.0
500	ALL	100万	0.4

メモリ設定は、計画的にってことですね。

〆

参考にした記事もぜひご覧ください。

今回は、サーバーシステム変数の知識を増やすために調べたりしたので、補足検証とかは行いませんでした。

とあるSQLでINDEXを設定しててなぜかフルスキャンになる場合range_optimizer_max_mem_sizeが不足しているみたいなこともある可能性がありますね。

他にも気なったこととか新しい発見などあったので今後記事にしようかと思います。

参考記事

https://www.percona.com/blog/2021/01/21/low-value-in-range_optimizer_max_mem_size-might-cause-full-table-scan/

https://dev.mysql.com/doc/refman/8.0/en/range-optimization.html

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_optimizer_max_mem_size

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、range_optimizer_max_mem_sizeの値が低いと、全表スキャンが発生する可能性がありますの日。

目次

とある日

記事概要

例え話

range_optimizer_max_mem_sizeとは

範囲オプティマイザ

単一部分インデックス

単一部分インデックス使用条件

単一部分インデックス例

検証

検証環境

下準備

検証テーブル作成

検証データ生成

データ確認

range_optimizer_max_mem_size 変更前

range_optimizer_max_mem_size確認

実行速度

EXPLAIN

range_optimizer_max_mem_size変更後

range_optimizer_max_mem_size変更

range_optimizer_max_mem_size確認

実行速度

EXPLAIN

検証結果

〆

参考記事