目次
とある日
PostgreSQLの列の順序は重要です-CYBERTECという記事を見ました。
MySQLだとどうなるのか気になったので試してみた記事です。
検証
もとの記事に沿ってやっていきます。
PostgreSQLとMySQLで関数や仕様が異なるためところどころ変えてます。
MySQL 8.0.29で検証しています。
大きなテーブルを作成する
PostgreSQLだと、色々関数があっていい感じに作成できるらしいのですが、MySQLだと大変なのでPython使って生成しました。
print("CREATE TABLE t_broad (") for i in range(1,4): print(f"t_{i} varchar(1) DEFAULT 'a' ,") print("t_4 varchar(1) DEFAULT 'a'") print(")")
一度1000列で作成すると、エラーが出て怒られたのでカラムの長さ短く設定しています。
↓プログラム実行例。
CREATE TABLE t_broad ( t_1 varchar(1) DEFAULT 'a' , t_2 varchar(1) DEFAULT 'a' , t_3 varchar(1) DEFAULT 'a' , t_4 varchar(1) DEFAULT 'a' );
非常に幅の広いテーブルを作成する
次のステートメントは、1000列を含むテーブルを作成します。
カラム上限が1017であることに注意してください。
試しにやってみて怒られました。
-> t_1018 varchar(1) DEFAULT 'a' -> );ERROR 1117 (HY000): Too many columns
MySQL :: MySQL 8.0 リファレンスマニュアル :: 8.4.7 テーブルカラム数と行サイズの制限
MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.7 InnoDB テーブル上の制限
print("CREATE TABLE t_broad (") for i in range(1,1000): print(f"t_{i} varchar(1) DEFAULT 'a' ,") print("t_1000 varchar(1) DEFAULT 'a'") print(")")
テーブルにデータを入力して、100万行を追加します。
適当に行数の多いテーブルを選択して100万行を追加します。
(root@localhost) [test] 8.0.29 > SELECT table_name, ENGINE, TABLE_SCHEMA, table_rows, avg_row_length, floor((data_length + index_length) / 1024 / 1024) AS ALL_MB, floor(data_length / 1024 / 1024) AS DATA_MB, floor(index_length / 1024 / 1024) AS INDEX_MB FROM information_schema.tables ORDER BY table_rows DESC LIMIT 10; +----------------------------------------------+--------------------+--------------------+------------+----------------+--------+---------+----------+ | TABLE_NAME | ENGINE | TABLE_SCHEMA | TABLE_ROWS | AVG_ROW_LENGTH | ALL_MB | DATA_MB | INDEX_MB | +----------------------------------------------+--------------------+--------------------+------------+----------------+--------+---------+----------+ | roku | InnoDB | test | 1235006 | 33 | 73 | 39 | 33 | | t0 | InnoDB | test | 998123 | 38 | 88 | 36 | 51 | | events_errors_summary_by_thread_by_error | PERFORMANCE_SCHEMA | performance_schema | 403200 | 0 | 0 | 0 | 0 | | events_errors_summary_by_account_by_error | PERFORMANCE_SCHEMA | performance_schema | 201600 | 0 | 0 | 0 | 0 | | events_errors_summary_by_host_by_error | PERFORMANCE_SCHEMA | performance_schema | 201600 | 0 | 0 | 0 | 0 | | events_errors_summary_by_user_by_error | PERFORMANCE_SCHEMA | performance_schema | 201600 | 0 | 0 | 0 | 0 | | variables_by_thread | PERFORMANCE_SCHEMA | performance_schema | 171264 | 0 | 0 | 0 | 0 | | events_waits_summary_by_thread_by_event_name | PERFORMANCE_SCHEMA | performance_schema | 167424 | 0 | 0 | 0 | 0 | | session_account_connect_attrs | PERFORMANCE_SCHEMA | performance_schema | 131072 | 0 | 0 | 0 | 0 | | session_connect_attrs | PERFORMANCE_SCHEMA | performance_schema | 131072 | 0 | 0 | 0 | 0 | +----------------------------------------------+--------------------+--------------------+------------+----------------+--------+---------+----------+ 10 rows in set (0.02 sec) (root@localhost) [test] 8.0.29 > insert into t_broad(t_1) select 'a' from test.roku limit 1000000; Query OK, 1000000 rows affected (4 min 22.76 sec) Records: 1000000 Duplicates: 0 Warnings: 0
行数に差がありますが、そこはinformation_schema.tablesの仕様かなと。
大体2GBぐらいでした。
(root@localhost) [test] 8.0.29 > SELECT table_name, ENGINE, TABLE_SCHEMA, table_rows, avg_row_length, floor((data_length + index_length) / 1024 / 1024) AS ALL_MB, floor(data_length / 1024 / 1024) AS DATA_MB, floor(index_length / 1024 / 1024) AS INDEX_MB FROM information_schema.tables WHERE table_name = 't_broad' ORDER BY (data_length + index_length) DESC; +------------+--------+--------------+------------+----------------+--------+---------+----------+ | TABLE_NAME | ENGINE | TABLE_SCHEMA | TABLE_ROWS | AVG_ROW_LENGTH | ALL_MB | DATA_MB | INDEX_MB | +------------+--------+--------------+------------+----------------+--------+---------+----------+ | t_broad | InnoDB | test | 857268 | 3149 | 2575 | 2575 | 0 | +------------+--------+--------------+------------+----------------+--------+---------+----------+ 1 row in set (0.00 sec)
さまざまな列へのアクセス
count(*)を実行して、どのくらいの時間がかかるかを見てみます。
(root@localhost) [test] 8.0.29 > select count(*) from t_broad; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (3.33 sec)
クエリは約3秒で実行できます。
予想どおり、オプティマイザーはフルスキャンを実行します。
(root@localhost) [test] 8.0.29 > explain select count(*) from t_broad;d; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | SIMPLE | t_broad | NULL | ALL | NULL | NULL | NULL | NULL | 857268 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
これを最初の列のカウントと比較してみます。
(root@localhost) [test] 8.0.29 > select count(t_1) from t_broad; +------------+ | count(t_1) | +------------+ | 1000000 | +------------+ 1 row in set (6.89 sec)
次に、列番号100にアクセスするとどうなるか見てみます。
(root@localhost) [test] 8.0.29 > select count(t_100) from t_broad; +--------------+ | count(t_100) | +--------------+ | 1000000 | +--------------+ 1 row in set (7.00 sec)
少し増加しましたが誤差だと思います。
最後に、列番号1000にアクセスします。
(root@localhost) [test] 8.0.29 > select count(t_1000) from t_broad; +---------------+ | count(t_1000) | +---------------+ | 1000000 | +---------------+ 1 row in set (7.04 sec)
PostgreSQLほど、パフォーマンスの劣化はないです。
その後、何回か試しましたが秒数の差は誤差だと思います。
まとめ
PostgreSQLと違って、あまり列の順序は影響なさそうでした。
ただ、カラム数の多いテーブルはあまり有益ではなさそうだなと思いました。
〆
DBエンジンによって細かい仕様が異なるのは大変ですね。
PostgreSQLの関数が便利だと思いました。
