MySQLの整数型とauto_increment
MySQLのauto_incrementを使ったカラムで値が枯渇したので、
調査した際のメモとauto_incrementの枯渇対象を探す方法を記載します。
はじめに
その前に・・・復習がてら調査した内容。
おさらい
MySQLの整数型について
データ型 | データ容量 | 数値の範囲 | 数値の範囲(unsigned) |
TINYINT | 1 バイト | -128~127 | 0~255 |
SMALLINT | 2 バイト | -32768~32767 | 0~65535 |
MEDIUMINT | 3 バイト | -8388608~8388607 | 0~16777215 |
INT | 4 バイト | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 バイト | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
上述しているように、選択する整数型によって扱える数値の範囲が異なるのが、注意点です。
auto_increment枯渇問題もここが肝になります。unsignedの使用有無も影響します。
unsignedって何?
unsignedは、0以下の数値を扱わない場合に設定します。
マイナス値を使用しないため、unsignedを使用しない項目に比べ、プラス側に2倍の値を使用可能です。
auto_incrementを使用する場合は、ほぼマイナス値を使用しないことになるため、この設定をいれておいたほうが無難です。
int(N)のNの意味って?
int(N)のN値部分は、Oracleのnumber型と異なり桁の上限を定義する値ではありません。
N値部分は、足りない桁を0で埋めるための設定であるzerofilを使用しないと意味がありません。
例えば、int(5)+zerofilを定義したカラム に123をinsertすると表示上は下記になります。
00123
int(5)の5は、桁制限ではないため6桁以上の数字も当然入力可能です。
そのため例えば、int(5)+zerofil に123456を入れると表示が、
123456
となります。
zerofilを利用しない限り数値型に桁を設定することは無意味となります。むしろ桁の上限と混同しがちなので、意図しない場合は、N値を設定しないほうが無難でしょう。
ただし、設定をしない場合は、各整数型に合わせたデフォルト値が設定されます。
※intならint(11)とかなるんですが、これどうにかN値なしにする方法ないんですかね…
ちなみに何故かN値は、255まで設定可能で256から設定ができません。
mysql> create table num_test (num_a bigint(256) );ERROR 1439 (42000): Display width out of range for column 'num_a' (max = 255)
auto_incrementを使用している対象の調査方法
調査方法は何パターンかあります。
調査方法
- show create tableでテーブル定義を確認
- information_schemaのtablesとcolumnsテーブルの値を確認
前者の方法は、すでに対象のテーブルが分かっている場合は有効ですが、量が多い場合は、テーブル1つ1つに実行していくため手間がかかります。
後者の場合は、そのホスト内の全databaseを1回のSQLで確認可能なので私はこちらで調査を行いました。
その際に使用したサンプルのシェルスクリプトとなります。
$ cat host_list.txt
host001
host002
host003
$ cat out_auto_increment.sh
#!/bin/bash
tmp_dir=/tmp
host_list=`cat ${tmp_dir}/host_list.txt`
for i in ${host_list}
{
mysql --login-path=user -N -s -h ${i} information_schema -e "select
'${i}'
,col.TABLE_SCHEMA
,col.TABLE_NAME
,col.COLUMN_NAME
,col.COLUMN_TYPE
,ta.AUTO_INCREMENT
,case
when col.DATA_TYPE='TINYINT' and col.COLUMN_TYPE like '%unsigned%' then 255 - ta.AUTO_INCREMENT
when col.DATA_TYPE='SMALLINT' and col.COLUMN_TYPE like '%unsigned%' then 65535 - ta.AUTO_INCREMENT
when col.DATA_TYPE='MEDIUMINT' and col.COLUMN_TYPE like '%unsigned%' then 16777215 - ta.AUTO_INCREMENT
when col.DATA_TYPE='INT' and col.COLUMN_TYPE like '%unsigned%' then 4294967295 - ta.AUTO_INCREMENT
when col.DATA_TYPE='BIGINT' and col.COLUMN_TYPE like '%unsigned%' then 18446744073709551615 - ta.AUTO_INCREMENT
when col.DATA_TYPE='TINYINT' then round((255-1)/2 - ta.AUTO_INCREMENT,0)
when col.DATA_TYPE='SMALLINT' then round((65535-1)/2 - ta.AUTO_INCREMENT,0)
when col.DATA_TYPE='MEDIUMINT' then round((16777215-1)/2 - ta.AUTO_INCREMENT,0)
when col.DATA_TYPE='INT' then round((4294967295-1)/2 - ta.AUTO_INCREMENT,0)
when col.DATA_TYPE='BIGINT' then round((18446744073709551615-1)/2 - ta.AUTO_INCREMENT,0)
end as amari
from
COLUMNS col
left outer join tables ta
on col.TABLE_SCHEMA=ta.TABLE_SCHEMA and col.table_name=ta.table_name
where
col.EXTRA like '%auto_increment%'
and ta.AUTO_INCREMENT is not null
and col.TABLE_SCHEMA <> 'mysql'
;" >>${tmp_dir}/auto_increment_list.txt
}
exit
$
/tmpのhost_list.txtを作成後に、調査対処のホストを記述してシェルスクリプトを実行すると、ta.AUTO_INCREMENTとamari列が出力します。
ta.AUTO_INCREMENT/(ta.AUTO_INCREMENT + amari)=auto_increment使用率
上記から使用率が分かるので、危険な値に達している対象がわかりやすくなると思います。
まとめ
最近はストレージも安いため、レコードが大量に更新されるテーブルでauto_incrementを使用する場合の整数型はbigintが良いでしょう。
整数型による性能差は、確認してませんが、bigintを使用したため性能問題が起きるケースはまずないと思われます。
※シェルスクリプトは、あくまでサンプルなので動作保証はしかねます
自己表明と紹介(初ブログ)
初めましてmarnakです。
普段はDB関連のエンジニアとして働き、30代半ばで今更ながらブログを始めます。
まずは始めるに至った経緯と目的を、ブログ運営に迷った時に立ち戻る指針として書いておきます。
当ブログをやろうと思った経緯と目的
-
エンジニアとしての記録
今まで何社かの企業で働いてきましたが、やっていくことは少しずつ変化していきました。そして記録に残していないことは、どんどんと忘れていきます…。
私自身の変化した転換点が色々とあったのですが、過去を振り返った時になるべく点を繋いで線で思い起こせるように記録を残したいと思いました。
また知識・技術・経験などを共有することで第三者の指摘をプラスして精度を高めて記録できればと考えました。 -
情報(知識・技術)を発信するところに情報が集まる
技術で有名な人のブログを見たり、最近読んだいくつかの本でも書いてある通り、情報は発信する人のところに、自然と集まるようです。また情報発信することで、新しい出会いがあったり、新しいコミュティに参加し、知らないことに近づけたら良いと思いました。
とまぁ、エンジニア生活は長いとあと30年近くあり、定年後もエンジニアを続けているかもしれません。
少しでも長く今の仕事を続けて行くため、技術に関わることを記録に残し、発信していくことは、エンジニア寿命と需要を高めるために必要と感じました。
これから発信していくことに感じることがあればコメント頂ければ幸いです。
marnak 2016/07/04