自1107以后，我们银行的核心系统MGR已经上线两周多了，目前还是比较稳定，最近有点时间可以深入学习一下mysql 8.0的一些特性。

mysql.idb这个数据文件是在8.0才出现，我们来具体了解一下。
1、先查看一下mysql数据库
mysql> show tables;
+—————————+
| Tables_in_mysql |
+—————————+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————+
33 rows in set (0.00 sec)

2、随意查看一下表结构
mysql> show create table columns_priv\\G;
1. row
Table: columns_priv
Create Table: CREATE TABLE

columns_priv

(

Host

char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT \’\’,

Db

char(64) COLLATE utf8_bin NOT NULL DEFAULT \’\’,

User

char(32) COLLATE utf8_bin NOT NULL DEFAULT \’\’,

Table_name

char(64) COLLATE utf8_bin NOT NULL DEFAULT \’\’,

Column_name

char(64) COLLATE utf8_bin NOT NULL DEFAULT \’\’,

Timestamp

timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

Column_priv

set(\’Select\’,\’Insert\’,\’Update\’,\’References\’) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT \’\’,
PRIMARY KEY (

Host

,

Db

,

User

,

Table_name

,

Column_name

)
) /!50100 TABLESPACE

mysql

/ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT=\’Column privileges\’
1 row in set (0.01 sec)

ERROR:
No query specified
关注点TABLESPACE

mysql

还有ENGINE=InnoDB 只有innodb的存储引擎才会有.idb这样的文件。
再看看其他的表
mysql> show create table servers\\G;
1. row
Table: servers
Create Table: CREATE TABLE

servers

(

Server_name

char(64) NOT NULL DEFAULT \’\’,

Host

char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT \’\’,

Db

char(64) NOT NULL DEFAULT \’\’,

Username

char(64) NOT NULL DEFAULT \’\’,

Password

char(64) NOT NULL DEFAULT \’\’,

Port

int NOT NULL DEFAULT \’0\’,

Socket

char(64) NOT NULL DEFAULT \’\’,

Wrapper

char(64) NOT NULL DEFAULT \’\’,

Owner

char(64) NOT NULL DEFAULT \’\’,
PRIMARY KEY (

Server_name

)
) /!50100 TABLESPACE

mysql

/ ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT=\’MySQL Foreign Servers table\’
1 row in set (0.00 sec)

ERROR:
No query specifie

其他的表也是使用的同样的mysql表空间，而且是innodb存储引擎。是不是说明mysql.idb这个文件是一个公共的表空间，8.0版本已经把mysql数据库的几乎所有的表都放在mysql这个公共表空间了。

我们再参看一个日志表
mysql> show create table slow_log\\G;
1. row
Table: slow_log
Create Table: CREATE TABLE

slow_log

(

start_time

timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

user_host

mediumtext NOT NULL,

query_time

time(6) NOT NULL,

lock_time

time(6) NOT NULL,

rows_sent

int NOT NULL,

rows_examined

int NOT NULL,

db

varchar(512) NOT NULL,

last_insert_id

int NOT NULL,

insert_id

int NOT NULL,

server_id

int unsigned NOT NULL,

sql_text

mediumblob NOT NULL,

thread_id

bigint unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT=\’Slow log\’
1 row in set (0.00 sec)

CSV存储引擎自然是不能把数据放在mysql.idb中，那应该是单独存放，我们猜测应该还会有一个mysql数据库（一个目录），到datadir下查看

ls -l

total 4255780
-rw-r—– 1 mysql mysql 56 Oct 12 11:39 auto.cnf
-rw-r—– 1 mysql mysql 0 Oct 12 11:42 GCS_DEBUG_TRACE
drwxr-x— 3 mysql mysql 22 Nov 7 03:45 #ib_archive
-rw-r—– 1 mysql mysql 4926 Oct 12 13:51 ib_buffer_pool
-rw-r—– 1 mysql mysql 1073741824 Nov 26 10:34 ibdata1
-rw-r—– 1 mysql mysql 1073741824 Nov 26 10:34 ib_logfile0
-rw-r—– 1 mysql mysql 1073741824 Oct 12 11:39 ib_logfile1
-rw-r—– 1 mysql mysql 1073741824 Oct 12 11:39 ib_logfile2
-rw-r—– 1 mysql mysql 12582912 Oct 12 13:52 ibtmp1
drwxr-x— 2 mysql mysql 187 Oct 12 13:52 #innodb_temp
drwxr-x— 2 mysql mysql 143 Oct 12 11:39 mysql
-rw-r—– 1 mysql mysql 25165824 Nov 25 17:37 mysql.ibd
drwxr-x— 2 mysql mysql 8192 Oct 12 13:52 performance_schema
-rw——- 1 mysql mysql 1676 Oct 12 11:39 private_key.pem
-rw-r–r– 1 mysql mysql 452 Oct 12 11:39 public_key.pem
drwxr-x— 2 mysql mysql 28 Oct 12 11:39 sys
-rw-r—– 1 mysql mysql 12582912 Nov 25 17:51 undo_001
-rw-r—– 1 mysql mysql 12582912 Nov 25 17:51 undo_002

cd 到mysql目录下查看

ls -l

total 28
-rw-r—– 1 mysql mysql 5594 Oct 12 11:39 general_log_201.sdi
-rw-r—– 1 mysql mysql 35 Oct 12 11:39 general_log.CSM
-rw-r—– 1 mysql mysql 0 Oct 12 11:39 general_log.CSV
-rw-r—– 1 mysql mysql 11803 Oct 12 11:39 slow_log_202.sdi
-rw-r—– 1 mysql mysql 35 Oct 12 11:39 slow_log.CSM
-rw-r—– 1 mysql mysql 0 Oct 12 11:39 slow_log.CSV
发现果然有两个日志表使用了CSV存储引擎，这个也不难理解，存储日志或者数据交换数据量比较大更一些，总之通过以上我们对mysql数据库的结构有了进一步的认识和了解。