1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
TABLE_ID NAME FLAG N_COLS SPACE FILE_FORMAT ROW_FORMAT ZIP_PAGE_SIZE
11 SYS_FOREIGN 0 7 0 Antelope Redundant 0
12 SYS_FOREIGN_COLS 0 7 0 Antelope Redundant 0
13 SYS_TABLESPACES 0 6 0 Antelope Redundant 0
14 SYS_DATAFILES 0 5 0 Antelope Redundant 0
table_id pos mtype prtype len name
11 0 1 524292 0 ID
11 1 1 524292 0 FOR_NAME
11 2 1 524292 0 REF_NAME
11 3 6 0 4 N_COLS
12 0 1 524292 0 ID
12 1 6 0 4 POS
12 2 1 524292 0 FOR_COL_NAME
12 3 1 524292 0 REF_COL_NAME
13 0 6 0 4 SPACE
13 1 1 524292 0 NAME
13 2 6 0 4 FLAGS
14 0 6 0 4 SPACE
14 1 1 524292 0 PATH
index_id table_id type n_fields space name
11 11 3 1 0 ID_IND
12 11 0 1 0 FOR_IND
13 11 0 1 0 REF_IND
14 12 3 2 0 ID_IND
15 13 3 1 0 SYS_TABLESPACES_SPACE
16 14 3 1 0 SYS_DATAFILES_SPACE
SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS
WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name', 'id', 'host', 'port')
ORDER BY index_id, pos;
index_id pos name
12 0 FOR_NAME
13 0 REF_NAME
14 1 POS
15 0 SPACE
16 0 SPACE
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID FOR_NAME REF_NAME N_COLS TYPE
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID FOR_COL_NAME REF_COL_NAME POS
SET GLOBAL innodb_file_format=`Barracuda`;
SET GLOBAL innodb_file_per_table=ON;
DROP TABLE IF EXISTS t_redundant, t_compact, t_compressed, t_dynamic;
CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
SELECT name, n_cols, file_format, row_format
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE space > 0;
name n_cols file_format row_format
mysql/innodb_index_stats 11 Antelope Compact
mysql/innodb_table_stats 9 Antelope Compact
mysql/slave_master_info 26 Antelope Compact
mysql/slave_relay_log_info 11 Antelope Compact
mysql/slave_worker_info 15 Antelope Compact
test/t_compact 5 Antelope Compact
test/t_compressed 5 Barracuda Compressed
test/t_dynamic 5 Barracuda Dynamic
test/t_redundant 5 Antelope Redundant
SELECT name, file_format, row_format
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
WHERE name LIKE 'test%';
name file_format row_format
test/t_compact Antelope Compact or Redundant
test/t_compressed Barracuda Compressed
test/t_dynamic Barracuda Dynamic
test/t_redundant Antelope Compact or Redundant
SELECT path FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
WHERE path LIKE '%test%';
path
MYSQLD_DATADIR/test/t_compact.ibd
MYSQLD_DATADIR/test/t_compressed.ibd
MYSQLD_DATADIR/test/t_dynamic.ibd
MYSQLD_DATADIR/test/t_redundant.ibd
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
count(*)
9
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID FOR_NAME REF_NAME N_COLS TYPE
test/constraint_test test/child test/parent 1 1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID FOR_COL_NAME REF_COL_NAME POS
test/constraint_test parent_id id 0
INSERT INTO parent VALUES(1);
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name num_rows ref_count
test/parent 1 1
SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
NAME FLAG N_COLS
SYS_DATAFILES 0 5
SYS_FOREIGN 0 7
SYS_FOREIGN_COLS 0 7
SYS_TABLESPACES 0 6
mysql/innodb_index_stats 1 11
mysql/innodb_table_stats 1 9
mysql/slave_master_info 1 26
mysql/slave_relay_log_info 1 11
mysql/slave_worker_info 1 15
test/child 1 5
test/parent 1 4
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%parent%");
name n_fields
PRIMARY 1
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name n_fields
GEN_CLUST_INDEX 0
par_ind 1
SELECT name, pos, mtype, len
from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
name pos mtype len
id 0 6 4
parent_id 1 6 4
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
PRIMARY KEY (id, newid)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
ON DELETE CASCADE) ENGINE=INNODB;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID FOR_NAME REF_NAME N_COLS TYPE
test/constraint_test test/child test/parent 2 1
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID FOR_COL_NAME REF_COL_NAME POS
test/constraint_test id id 0
test/constraint_test parent_id newid 1
INSERT INTO parent VALUES(1, 9);
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
id newid
1 9
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
name num_rows ref_count
test/parent 1 2
DROP TABLE child;
DROP TABLE parent;
|
