2022-05-06 12:24:13

MySQL インデックスとパーティションを観察してみる

要約

• 日付を範囲検索したときにインデックスを有効にする場合は SELECT で取得するカラムに注意しないと有効にならなかった
  • 具体的にはインデックスも主キーでも無いカラムを含めて取得すると遅くなった
• パーティション化した場合は SELECT に含めてなくても範囲は絞られた

準備

ホストPCで以下の操作をします

# MySQL イメージ取得
docker pull mysql

# コンテナ初回起動
docker run --name mysql_study_references -it --env MYSQL_ROOT_PASSWORD=password -d mysql

# 次回実行する場合はこちら
# docker start mysql_study_references

コンテナ内で以下を実行します

apt update -y

apt upgrade -y

apt install git -y

cd /home/

git clone https://github.com/datacharmer/test_db.git

cd test_db/

mysql < employees.sql -h localhost -u root -ppassword

mysql -h localhost -u root -ppassword

use employees;

salaries テーブルの確認

主キーと外部キーが設定されています。

mysql> SHOW CREATE TABLE salaries G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int NOT NULL,
  `salary` int NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`),
  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

レコード数は 2844047 です。

mysql> SELECT COUNT(*) FROM salaries;
+----------+
| COUNT(*) |
+----------+
|  2844047 |
+----------+
1 row in set (0.13 sec)

テーブル準備

/*******************************************
* パーティション化されたテーブルを用意します
*******************************************/
-- パーティション化する用の salaries テーブルを用意します
CREATE TABLE salaries_with_partition LIKE salaries;

-- パーティション化します
-- パーティション化コマンドは以下のページのコメント部分を利用しています（テーブル名は変更しています）。
-- https://github.com/datacharmer/test_db/blob/master/employees_partitioned.sql
ALTER TABLE salaries_with_partition
partition by range COLUMNS (from_date)
(
    partition p01 values less than ('1985-12-31'),
    partition p02 values less than ('1986-12-31'),
    partition p03 values less than ('1987-12-31'),
    partition p04 values less than ('1988-12-31'),
    partition p05 values less than ('1989-12-31'),
    partition p06 values less than ('1990-12-31'),
    partition p07 values less than ('1991-12-31'),
    partition p08 values less than ('1992-12-31'),
    partition p09 values less than ('1993-12-31'),
    partition p10 values less than ('1994-12-31'),
    partition p11 values less than ('1995-12-31'),
    partition p12 values less than ('1996-12-31'),
    partition p13 values less than ('1997-12-31'),
    partition p14 values less than ('1998-12-31'),
    partition p15 values less than ('1999-12-31'),
    partition p16 values less than ('2000-12-31'),
    partition p17 values less than ('2001-12-31'),
    partition p18 values less than ('2002-12-31'),
    partition p19 values less than (MAXVALUE)
);

-- salaries テーブルのデータをインサートします
INSERT INTO salaries_with_partition SELECT * FROM salaries;

/*******************************************
* インデックスを貼ったテーブルを用意します
*******************************************/
CREATE TABLE salaries_with_index LIKE salaries;

-- 日付の範囲検索を行ったときにインデックスを利用します
ALTER TABLE salaries_with_index ADD INDEX from_to_date USING BTREE (from_date, to_date);

-- salaries テーブルのデータをインサートします
INSERT INTO salaries_with_index SELECT * FROM salaries;

salaries テーブルに対して日付の範囲検索を行う ( SELECT * FROM )

EXPLAIN SELECT * FROM salaries where from_date >= '1999-01-01' and to_date <= '1999-12-31' G

予想

インデックスもパーティションも利用していないのでフルスキャンするんじゃない？

結果

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

salaries テーブルのレコード数は 2844047 ですが、 rows を見る限りフルスキャンではなさそうですが、全レコードのうち約 99% をスキャンしてます。

感想

フルスキャンが全レコードをスキャンすることなのか知りませんが、ほぼ全てのレコードをスキャンしていますね。おおむね予想通りです。

salaries_with_partition テーブルに対して日付の範囲検索を行う ( SELECT * FROM )

EXPLAIN SELECT * FROM salaries_with_partition WHERE from_date >= '1999-01-01' AND to_date <= '1999-12-31' G

予想

パーティション化しているので範囲が絞られて検索するはず。よく分からないけど、パーティションの p15 を調べるんじゃない？

結果

salaries テーブルのレコード数は 2844047 ですが、 rows を見る限り、全レコードのうち約 31% をスキャンしてます。

mysql> EXPLAIN SELECT * FROM salaries_with_partition WHERE
 from_date >= '1999-01-01' AND to_date <= '1999-12-31' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries_with_partition
   partitions: p15,p16,p17,p18,p19
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 905980
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

partitions の項目の内容はレコードが参照されるパーティションらしいです。

クエリーでレコードが照合されるパーティション。 パーティション化されていないテーブルの場合、この値は NULL です。 https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html

感想

検索するレコード数は確かに減ってますね。パーティションの p15 だけが参照されるかと思いきや p19 まで参照しているのは謎です。

salaries_with_index テーブルに対して日付の範囲検索を行う ( SELECT * FROM )

EXPLAIN SELECT * FROM salaries_with_index WHERE from_date >= '1999-01-01' AND to_date <= '1999-12-31' G

予想

インデックスが有効になるはずなので範囲が絞られて検索するはず。

結果

salaries テーブルに対して日付の範囲検索を行ったときと同じレコード数をスキャンしていました。key 項目を見てもインデックスを使用していません。

mysql> EXPLAIN SELECT * FROM salaries_with_index WHERE from_date >= '1999-01-01' AND to_date <= '1999-12-31' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries_with_index
   partitions: NULL
         type: ALL
possible_keys: from_to_date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 16.66
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

感想

インデックスなしで検索したときと同じレコード数をスキャンしてるのは謎なので調べました。

salaries_with_index テーブルに対して日付の範囲検索を行う ( SELECT from_date, to_date FROM )

EXPLAIN SELECT from_date, to_date FROM salaries_with_index WHERE from_date >= '1999-01-01' AND to_date <= '1999-12-31' G

結果

ちゃんとインデックスが有効になりました。salaries テーブルのレコード数は 2844047 ですが、全レコード数のうち約 50% のレコードをスキャンしているみたいです。

mysql> EXPLAIN SELECT from_date, to_date FROM salaries_with_index WHERE from_date >= '1999-01-01' AND to_date <= '1999-1
2-31' G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries_with_index
   partitions: NULL
         type: range
possible_keys: from_to_date
          key: from_to_date
      key_len: 3
          ref: NULL
         rows: 1419213
     filtered: 33.33
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

３つのテーブルの SELECT で取得するカラムと rows の関係

	SELECT * FROM	SELECT from_date, to_date FROM
salaries	99%	99%
salaries_with_partition	30%	30%
salaries_with_index	99%	50%

( EXPLAIN で確認できる rows ) / 全レコード数 をパーセントで表したもの

パーティション化したテーブルは安定して検索レコードが絞られました。

参考

• https://dev.mysql.com/doc/refman/8.0/ja/alter-table.html
• https://dev.mysql.com/doc/refman/8.0/ja/explain-output.html
• https://github.com/datacharmer/test_db
• https://notepad-blog.com/content/31/

終わり

おわり