データが主食

データエンジニアの備忘録。分析だったり、読んだ本のメモだったり。

MySQL InnoDBのCHAR、VARCHARとTEXTのパフォーマンス比較

はじめに

MySQL InnoDBのVARCHAR型とTEXT型では、内部的な仕組みが同じでパフォーマンスも同じというお話を伺ったので、実験してみました。

実験スクリプト

  • dockerでmysql 5.7.12を起動
  • ランダムに生成した200バイトの文字をN回INSERTする insert.sqlを作成
  • insert.sqlを実行して時間を計測
  • auto increment なPK でランダムにSELECTするselect.sqlを作成
  • select.sqlを実行して時間を計測
docker run --rm \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=my_db \
  -p 33061:3306 \
  --name mysql \
  mysql:5.7.12

create.sql

CREATE TABLE IF NOT EXISTS tbl_char(
        id INT AUTO_INCREMENT,
        test_column CHAR(255),
        PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS tbl_varchar(
        id INT AUTO_INCREMENT,
        test_column VARCHAR(255),
        PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS tbl_text(
        id INT AUTO_INCREMENT,
        test_column TEXT,
        PRIMARY KEY (id)
);
function f_create(){
  mysql -uroot -ppassword -P 33061 -h 127.0.0.1 my_db < create.sql &> /dev/null
}

function f_insert(){
  TABLE=$1
  N=$2
  rm -fr insert.sql
  echo "TRUNCATE ${TABLE}"| mysql -uroot -ppassword -P 33061 -h 127.0.0.1 my_db &> /dev/null
  TEXT=$(cat /dev/urandom | base64 | fold -w 200 | head -n 1)
  for i in `seq 1 ${N}` ; do
    echo "INSERT INTO ${TABLE}(test_column) VALUES('${TEXT}');" >> insert.sql
  done
  CMD="cat insert.sql| mysql -uroot -ppassword -P 33061 -h 127.0.0.1 my_db &> /dev/null"
  echo INSERT ${TABLE} ${N}
  /usr/bin/time sh -c "${CMD}"
}

function f_select(){
  TABLE=$1
  N=$2
  rm -fr select.sql
  for i in `seq 1 ${N}` ; do
        ID=$(($RANDOM % ${N}))
    echo "SELECT * FROM tbl_text WHERE id = ${ID};" >> select.sql
  done
  CMD="cat select.sql |mysql -uroot -ppassword -P 33061 -h 127.0.0.1 my_db &> /dev/null"
  echo SELECT ${TABLE} ${N}
  /usr/bin/time sh -c "${CMD}"
}


f_create
f_insert tbl_char 10
f_select tbl_char 10
f_insert tbl_char 100
f_select tbl_char 100
f_insert tbl_char 1000
f_select tbl_char 1000
f_insert tbl_char 10000
f_select tbl_char 10000
f_insert tbl_char 10
f_select tbl_varchar 10
f_insert tbl_varchar 100
f_select tbl_varchar 100
f_insert tbl_varchar 1000
f_select tbl_varchar 1000
f_insert tbl_varchar 10000
f_select tbl_varchar 10000
f_insert tbl_text 10
f_select tbl_text 10
f_insert tbl_text 100
f_select tbl_text 100
f_insert tbl_text 1000
f_select tbl_text 1000
f_insert tbl_text 10000
f_select tbl_text 10000

結果

$ sh test.sh
INSERT tbl_char 10
        0.10 real         0.02 user         0.01 sys
SELECT tbl_char 10
        0.07 real         0.01 user         0.01 sys
INSERT tbl_char 100
        0.63 real         0.02 user         0.01 sys
SELECT tbl_char 100
        0.32 real         0.02 user         0.01 sys
INSERT tbl_char 1000
        5.33 real         0.05 user         0.06 sys
SELECT tbl_char 1000
        2.66 real         0.03 user         0.03 sys
INSERT tbl_char 10000
       52.14 real         0.27 user         0.30 sys
SELECT tbl_char 10000
       22.40 real         0.30 user         0.28 sys
INSERT tbl_char 10
        0.11 real         0.01 user         0.01 sys
SELECT tbl_varchar 10
        0.06 real         0.01 user         0.01 sys
INSERT tbl_varchar 100
        0.48 real         0.02 user         0.01 sys
SELECT tbl_varchar 100
        0.30 real         0.01 user         0.01 sys
INSERT tbl_varchar 1000
        5.29 real         0.04 user         0.05 sys
SELECT tbl_varchar 1000
        2.14 real         0.03 user         0.03 sys
INSERT tbl_varchar 10000
       54.13 real         0.28 user         0.31 sys
SELECT tbl_varchar 10000
       22.15 real         0.28 user         0.28 sys
INSERT tbl_text 10
        0.10 real         0.01 user         0.01 sys
SELECT tbl_text 10
        0.09 real         0.01 user         0.01 sys
INSERT tbl_text 100
        0.48 real         0.01 user         0.01 sys
SELECT tbl_text 100
        0.31 real         0.02 user         0.01 sys
INSERT tbl_text 1000
        5.45 real         0.04 user         0.05 sys
SELECT tbl_text 1000
        2.21 real         0.05 user         0.03 sys
INSERT tbl_text 10000
       54.53 real         0.28 user         0.31 sys
SELECT tbl_text 10000
       23.52 real         0.49 user         0.30 sys

結果

  • 今回の実験環境だとINSERT/SELECTは同じだった。

所感

  • SSD搭載のMBPで実行したので、HDD環境でも実験してみたい。
  • 今回は実験対象のカラムとは別のPKで検索したが、実験対象のカラムでの検索も実験してみたい。
  • 今回のデータ量だと全てInnnoDbのbuffer poolに乗っていると思うので、もっと大きなデータで試してみたい。

参考