mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
181 lines
6.4 KiB
Text
181 lines
6.4 KiB
Text
SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent = 0;
|
|
SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables
|
|
WHERE name = 'mysql/innodb_table_stats';
|
|
SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables
|
|
WHERE name = 'mysql/innodb_index_stats';
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
|
|
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id;
|
|
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
|
|
11 SYS_FOREIGN 0 7 0 Redundant 0 System
|
|
12 SYS_FOREIGN_COLS 0 7 0 Redundant 0 System
|
|
13 SYS_VIRTUAL 0 6 0 Redundant 0 System
|
|
16 mysql/transaction_registry 33 8 6 Dynamic 0 Single
|
|
SELECT table_id,pos,mtype,prtype,len,name
|
|
FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
|
|
WHERE table_id NOT IN (@table_stats_id, @index_stats_id)
|
|
ORDER BY table_id, pos;
|
|
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 8 TABLE_ID
|
|
13 1 6 0 4 POS
|
|
13 2 6 0 4 BASE_POS
|
|
16 0 6 1800 8 transaction_id
|
|
16 1 6 1800 8 commit_id
|
|
16 2 3 526087 7 begin_timestamp
|
|
16 3 3 526087 7 commit_timestamp
|
|
16 4 6 1022 1 isolation_level
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES
|
|
WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id;
|
|
INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE MERGE_THRESHOLD
|
|
# ID_IND # 3 1 # # 50
|
|
# FOR_IND # 0 1 # # 50
|
|
# REF_IND # 0 1 # # 50
|
|
# ID_IND # 3 2 # # 50
|
|
# BASE_IDX # 3 3 # # 50
|
|
# PRIMARY # 3 1 # # 50
|
|
# commit_id # 2 1 # # 50
|
|
# begin_timestamp # 0 1 # # 50
|
|
# commit_timestamp # 0 2 # # 50
|
|
SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS
|
|
WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name')
|
|
ORDER BY index_id, pos;
|
|
index_id pos name
|
|
11 0 ID
|
|
12 0 FOR_NAME
|
|
13 0 REF_NAME
|
|
14 0 ID
|
|
14 1 POS
|
|
15 0 TABLE_ID
|
|
15 1 POS
|
|
15 2 BASE_POS
|
|
18 0 transaction_id
|
|
19 0 commit_id
|
|
20 0 begin_timestamp
|
|
21 0 commit_timestamp
|
|
21 1 transaction_id
|
|
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
|
|
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 KEY_BLOCK_SIZE=2;
|
|
CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
|
|
=== information_schema.innodb_sys_tables and innodb_sys_tablespaces ===
|
|
Table Name Tablespace Table Flags Columns Row Format Zip Size
|
|
test/t_compact test/t_compact 1 5 Compact 0
|
|
test/t_compressed test/t_compressed 37 5 Compressed 2048
|
|
test/t_dynamic test/t_dynamic 33 5 Dynamic 0
|
|
test/t_redundant test/t_redundant 0 5 Redundant 0
|
|
=== information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
|
|
Space_Name Page_Size Zip_Size Path
|
|
innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
|
|
innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
|
|
innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
|
|
test/t_redundant DEFAULT DEFAULT MYSQLD_DATADIR/test/t_redundant.ibd
|
|
test/t_compact DEFAULT DEFAULT MYSQLD_DATADIR/test/t_compact.ibd
|
|
test/t_compressed DEFAULT 2048 MYSQLD_DATADIR/test/t_compressed.ibd
|
|
test/t_dynamic DEFAULT DEFAULT MYSQLD_DATADIR/test/t_dynamic.ibd
|
|
innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
|
|
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
|
|
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
|
|
count(*)
|
|
6
|
|
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);
|
|
InnoDB 0 transactions not purged
|
|
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
|
|
WHERE name NOT LIKE 'sys/%';
|
|
NAME FLAG N_COLS
|
|
SYS_FOREIGN 0 7
|
|
SYS_FOREIGN_COLS 0 7
|
|
SYS_VIRTUAL 0 6
|
|
mysql/innodb_index_stats 33 11
|
|
mysql/innodb_table_stats 33 9
|
|
mysql/transaction_registry 33 8
|
|
test/child 33 5
|
|
test/parent 33 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
|
|
InnoDB 0 transactions not purged
|
|
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;
|
|
#
|
|
# MDEV-29479 I_S.INNODB_SYS_TABLESPACES doesn't have
|
|
# temporary tablespace information
|
|
#
|
|
SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name like 'innodb_temporary';
|
|
SPACE
|
|
4294967294
|
|
SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
|