mysql

这篇文章将会总结mysql中Innodb存储引擎与Myisam存储引擎的基本知识点。

 存储引擎分类：

mysql中，存储引擎是插件式的，同一个数据库中的不同的表可以使用不同的存储引擎，所以，存储引擎是表级别的概念，存储引擎也被称为"表类型"，每张表可以使用不同的存储引擎类型。

全部分类：

CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件。

MRG_MYISAM：此引擎也被称为MERGE存储引擎，如果一些myisam表的表结构完全相同，可以将这些MyISAM表合并成的一张MRG_MYISAM虚拟表。

BLACKHOLE：类似于/dev/null，不真正存储数据。

MEMORY：内存存储引擎，速度快，但是一旦断电数据将会丢失，支持hash索引，支持表级锁，常用于临时表。

PERFORMANCE_SCHEMA：从mysql5.5之后，多出了PERFORMANCE_SCHEMA数据库，PERFORMANCE_SCHEMA数据库中的表的表类型均为PERFORMANCE_SCHEMA，此数据库用于存储与数据库的性能相关的信息，用户无法创建使用这种存储引擎的表，但是dba可以通过PERFORMANCE_SCHEMA数据库中的信息进行性能分析，PERFORMANCE_SCHEMA数据库服务启动后此库中将不断的收集数据，mysql停机后此库中的表将不存在数据，类似于linux中的/proc。

FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口。

ARCHIVE: 见名知义，创建此种表类型的表往往用于存储归档信息、安全审计信息、历史信息等，创建数据仓库时，可能会用到此种表类型，使用archive表类型的表只支持select和insert操作，不能更新和删除操作，支持行级锁。

FEDERATED：利用federated引擎可将本地数据表映射至远程 MySQL 数据表，从而就可以解决应用程序中繁多的跨机器连接数据库问题，其实federated相当于一个访问其他远程mysql server的代理接口，它通过创建一个到远程mysql server的客户端连接，通过FEDERATED引擎创建的表只是在本地有表定义文件，数据文件则存在于远程数据库中。

常用引擎：

MYISAM：支持表级锁，不支持行级锁，不支持事务，不支持外检约束，支持全文索引，表空间文件相对小。

INNODB：支持表级锁，行级锁，支持事务，支持外检，不支持全文索引，表空间文件相对较大。

存储引擎管理命令：

查看默认的存储引擎：

 mysql> show engines; 

 查看某张表的存储引擎：

 mysql> show create table index_testG; 

 查看某个库所有表的存储引擎情况：

 mysql> show table statusG; 

 设置存储引擎：

之前已经说过，存储引擎就是表类型，我们可以在创建表的时候就指定当前表使用哪种存储引擎：

 mysql> create table test_engine (id int not null default 0) ENGINE=INNODB; 

 修改表的存储引擎：

 mysql> alter table test_engine engine=myisam; 

注意：此命令我们经常使用他，进行innodb表的碎片整理

 修改默认存储引擎：

1. 临时生效（mysql重启服务后，失效）：

   # 会话级别：只对当前会话生效
   mysql> set default_storage_engine=innodb;
   
   # 全局级别：仅影响新的会话，但是如果mysql服务重启，则失效
   mysql> set global default_storage_engine=innodb;

2. 永久生效：

   # 更改mysql 配置文件
   
   [root@localhost] # vim /etc/my.cnf
   
   # 文件内容：
   [mysqld]
   # 添加内容：
   default_storage_engine=innodb
   
   # 重启mysql服务，配置生效

innodb与myisam的数据文件：

myisam数据文件：

1. myt.frm:   数据字典信息；
2. myt.myd: 数据行信息；
3. myt.myi:  索引信息；

innodb数据文件：

查看mysql的数据文件夹：（eg:data/mysql/data, 具体路径根据自己的my.cnf的设置）

ibdata1：系统数据字典信息(统计信息)，UNDO表空间等数据
ib_logfile0 ~ ib_logfile1: REDO日志文件，事务日志文件。
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

innodb共享表空间：

需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了

innodb独立表空间：

从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：
      元数据            数据行+索引
mysql表数据    =（ibdataX+frm）+ibd(段、区、页)
        DDL             DML+DQL

MySQL的存储引擎日志：
Redo Log: ib_logfile0  ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动

innodb独立表空间设置：

1. 查看`独立表空间`信息：

   mysql> select @@innodb_file_per_table;
   +-------------------------+
   | @@innodb_file_per_table |
   +-------------------------+
   |                       1 |
   +-------------------------+
   1 row in set (0.00 sec)

2. 永久生效配置：

   1、vim /etc/my.cnf
   2、在[mysqld]下面，添加：innodb-file-per-table=1