InnoDBログをcatしたら見知らぬibdファイルの名前が書いてあることに気が付いた。
mysql/character_sets.ibd なるファイルに書き込みをしているようだが、mysql80> SHOW TABLES FROM mysql LIKE '%char%';
Empty set (0.00 sec)
そんなテーブルは存在しない。
$ ll data/mysql/character_sets.ibd
-rw-r----- 1 yoku0825 yoku0825 163840 Apr 3 14:44 data/mysql/character_sets.ibd
ファイルは確かにある。
なんだこれ…？
と思ってたら、なんか他にもいっぱいあった。
$ diff -y <(./use -sse "SHOW TABLES FROM mysql" | sort) <(ls data/mysql/*.ibd | perl -nle 's/.+\///; s/\.ibd//; print' | sort)
> catalogs
> character_sets
> collations
> columns
columns_priv columns_priv
column_stats column_stats
> column_type_elements
component component
db db
default_roles default_roles
engine_cost engine_cost
> events
> foreign_key_column_usage
> foreign_keys
func func
general_log <
gtid_executed gtid_executed
help_category help_category
help_keyword help_keyword
help_relation help_relation
help_topic help_topic
> index_column_usage
> indexes
> index_partitions
> index_stats
innodb_index_stats innodb_index_stats
innodb_table_stats innodb_table_stats
> parameters
> parameter_type_elements
plugin plugin
procs_priv procs_priv
proxies_priv proxies_priv
role_edges role_edges
> routines
> schemata
server_cost server_cost
servers servers
slave_master_info slave_master_info
slave_relay_log_info slave_relay_log_info
slave_worker_info slave_worker_info
slow_log | st_spatial_reference_systems
> table_partitions
> table_partition_values
> tables
> tablespace_files
> tablespaces
tables_priv tables_priv
> table_stats
time_zone time_zone
time_zone_leap_second time_zone_leap_second
time_zone_name time_zone_name
time_zone_transition time_zone_transition
time_zone_transition_type time_zone_transition_type
> triggers
user user
> version
> view_routine_usage
> view_table_usage
向かって右がibdファイルしかないやつ。
general_logとslow_log（なんか変にst_spatial_reference_systemsと混じってやんの。。）はCSVストレージエンジンだからテーブルしかないのは良いとして、tables.ibdとかがテーブルのメタデータを集めたibdファイルになっているっぽい（
od -c してみたらどうやらそれっぽい）
これが、「 information_schema.tables は実際のInnoDBテーブルへの参照になって、メタデータを .frm から集めなくなるから速くなる」の正体か。
.ibdファイルからレコードを引き出すユーティリティーが欲しくなるなあ。。
とか思ったけど innodb_ruby がまだフツーに使えた。やった。
mysql80> CREATE TABLE t1 (num serial, val varchar(32)) comment = 'yoku0825';
Query OK, 0 rows affected (0.02 sec)
$ innodb_space -s data/ibdata1 -T mysql/tables space-indexes
id name root fseg used allocated fill_factor
35 PRIMARY 5 internal 1 1 100.00%
35 PRIMARY 5 leaf 28 28 100.00%
36 schema_id 6 internal 1 1 100.00%
36 schema_id 6 leaf 0 0 0.00%
37 engine 7 internal 1 1 100.00%
37 engine 7 leaf 0 0 0.00%
38 engine_2 8 internal 1 1 100.00%
38 engine_2 8 leaf 0 0 0.00%
39 collation_id 9 internal 1 1 100.00%
39 collation_id 9 leaf 0 0 0.00%
40 tablespace_id 10 internal 1 1 100.00%
40 tablespace_id 10 leaf 0 0 0.00%
$ innodb_space -s data/ibdata1 -T mysql/tables -p 5 page-dump | less
{:format=>:compact,
:offset=>668,
:header=>
{:next=>112,
:type=>:node_pointer,
:heap_number=>29,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>[],
:lengths=>{},
:externs=>[],
:length=>5},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"BIGINT UNSIGNED", :value=>318}],
:row=>[],
:sys=>[],
:child_page_number=>38,
:length=>12}
$ innodb_space -s data/ibdata1 -T mysql/tables -p 38 page-dump | less
{:format=>:compact,
:offset=>12645,
:header=>
{:next=>112,
:type=>:conventional,
:heap_number=>6,
:n_owned=>0,
:min_rec=>false,
:deleted=>false,
:nulls=>
["se_private_data",
"se_private_id",
"tablespace_id",
"partition_type",
"partition_expression",
"default_partitioning",
"subpartition_type",
"subpartition_expression",
"default_subpartitioning",
"view_definition",
"view_definition_utf8",
"view_check_option",
"view_is_updatable",
"view_algorithm",
"view_security_type",
"view_definer",
"view_client_collation_id",
"view_connection_collation_id"],
:lengths=>{"name"=>2, "engine"=>6, "comment"=>8, "options"=>105},
:externs=>[],
:length=>12},
:next=>112,
:type=>:clustered,
:key=>[{:name=>"id", :type=>"BIGINT UNSIGNED", :value=>322}],
:row=>
[{:name=>"schema_id", :type=>"BIGINT UNSIGNED", :value=>6},
{:name=>"name", :type=>"VARCHAR(192)", :value=>"t1"},
{:name=>"type", :type=>"CHAR(1) UNSIGNED", :value=>"\x01"},
{:name=>"engine", :type=>"VARCHAR(192)", :value=>"InnoDB"},
{:name=>"mysql_version_id", :type=>"INT UNSIGNED", :value=>80000},
{:name=>"row_format", :type=>"CHAR(1) UNSIGNED", :value=>"\x02"},
{:name=>"collation_id", :type=>"BIGINT UNSIGNED", :value=>8},
{:name=>"comment", :type=>"VARCHAR(6144)", :value=>"yoku0825"},
{:name=>"hidden", :type=>"TINYINT", :value=>0},
{:name=>"options",
:type=>"BLOB",
:value=>
"avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;"},
{:name=>"se_private_data", :type=>"BLOB", :value=>:NULL},
{:name=>"se_private_id", :type=>"BIGINT UNSIGNED", :value=>:NULL},
{:name=>"tablespace_id", :type=>"BIGINT UNSIGNED", :value=>:NULL},
{:name=>"partition_type", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"partition_expression", :type=>"VARCHAR(6144)", :value=>:NULL},
{:name=>"default_partitioning", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"subpartition_type", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"subpartition_expression", :type=>"VARCHAR(6144)", :value=>:NULL},
{:name=>"default_subpartitioning",
:type=>"CHAR(1) UNSIGNED",
:value=>:NULL},
{:name=>"created", :type=>"TIMESTAMP", :value=>"2017-04-03 06:47:50"},
{:name=>"last_altered", :type=>"TIMESTAMP", :value=>"2017-04-03 06:47:50"},
{:name=>"view_definition", :type=>"BLOB", :value=>:NULL},
{:name=>"view_definition_utf8", :type=>"BLOB", :value=>:NULL},
{:name=>"view_check_option", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"view_is_updatable", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"view_algorithm", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"view_security_type", :type=>"CHAR(1) UNSIGNED", :value=>:NULL},
{:name=>"view_definer", :type=>"VARCHAR(279)", :value=>:NULL},
{:name=>"view_client_collation_id",
:type=>"BIGINT UNSIGNED",
:value=>:NULL},
{:name=>"view_connection_collation_id",
:type=>"BIGINT UNSIGNED",
:value=>:NULL}],
:sys=>
[{:name=>"DB_TRX_ID", :type=>"TRX_ID", :value=>1571},
{:name=>"DB_ROLL_PTR",
:type=>"ROLL_PTR",
:value=>
{:is_insert=>true, :rseg_id=>58, :undo_log=>{:page=>303, :offset=>272}}}],
:length=>173,
:transaction_id=>1571,
:roll_pointer=>
{:is_insert=>true, :rseg_id=>58, :undo_log=>{:page=>303, :offset=>272}}}
ふむ。。この手の隠しibdはもうちょっと探ってみても面白い鴨。
0 件のコメント :
コメントを投稿