Mysql中级（一）：Mysql 体系结构和存储引擎

定义数据库和数据库实例

数据库：物理操作系统文件或者其他形式文件的集合，数据库中文件可以是frm，MYD，MYI，ibd结尾的文件

数据库实例：Mysql数据库由后台线程以及共享内存区组成，在系统上，数据库实例表现为一个线程。

Mysql读取配置文件的顺序 Mysql数据库是啊按照/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/mysql.cnf -> ~/.mysql.cnf顺序读取配置文件的

Mysql数据库会以读取到的最后一个配置文件中的参数为准

Mysql 体系结构

mysql由一下几部分组成：

连接池组件

管理服务和工具组件

SQL接口组件

查询分析器组件

优化器组件

缓冲组件

插件式存储引擎

物理文件 需要特别注意，存储引擎是基于表的，不是基于数据库的。

Mysql 存储引擎

InnoDB 存储引擎【5.5.8版本默认】

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务的应用 特点是：

行锁设计

支持外键

支持类似于Oracle的非锁定读

InnoDB 存储引擎采用聚集的方式，每张表存储都是按照主键顺序存放

InnoDB 存储引擎是Mysql中最常见的存储引擎

MyISAM 存储引擎【5.5.8版本前默认】

MyISAM存储引擎

不支持事务

不支持表锁设计

支持全文索引

MyISAM存储引擎有MYD和MYI组成，MYD用来存储数据文件，MYI用来存储索引文件

NDB 存储引擎

NDB的特点

数据全部放在内存中

通过主键查找方式极快

Memory 存储引擎

Memroy 存储引擎将表存放在内存中，如果数据库重启，表中的数据将丢失 他非常适合用来存储临时数据的临时表 Memory存储引擎默认使用哈希索引，不是我们熟悉的B+树索引 Memory表在很多场景下都可以发挥很好的作用：

用于查找或者映射表，比如将邮编和州名映射的表

用于缓存周期性聚合的数据结果

用于保存数据分析中产生的中间数据

Archive 存储引擎

Archive存储引擎

只支持INSERT和SELECT操作

Archive存储引擎适合存储归档文件，如日志文件

Archive存储引擎用行锁来实现高并发的插入，但是本身并不是事务安全的存储引擎

设计的目标是提供高速插入和压缩功能

Maria存储引擎

Maria存储引擎是未来替换原有的MyISAM存储引擎

支持缓存文件和索引文件

用用行锁设计

支持MVCC功能

如何选择合适的引擎

这么多存储引擎，我们怎么选择？大部分情况下，InnoDB都是正确的选择，所以Oracle在Mysql5.5版本就将InnoDB作为默认的存储引擎，如何选择存储引擎，可以用一句话概况，除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以代替时，否则都应该优先选择InnoDB引擎。

如果需要应用不同的存储引擎，那么需要从以下几个因素进行考虑：

事务:如果应用需要支持事务，那么InnoDB是目前最稳定的并且经过验证的选择

备份:备份需求也会影响存储迎请的选择，如果需要在线热备份，那么选择InnoDB就是最基本的要求

崩溃恢复:数据量比较大时，系统崩溃后如何快速回恢复是一个需要考虑的问题，MyISAM崩溃后的损坏率肯定要高于InnoDB，所以即使不需要事务支持，很多人也选择InnoDB

特有的特性:如果如法确定，那么就是用InnoDB，这个默认选项是安全的，尤其是在不清楚具体需求的时候。

如何转换引擎

介绍三种常见的转换表存储引擎的方法？

Alter Table

将表的存储引擎修改为另一个存储引擎最简单的方法就是Alter table语句，语法如下：

上述语法适合任何存储引擎，但是有一个问题是需要执行的时间很长。Mysql会按行将数据库从原表复制到一张新表中，在复制期间会消耗系统所有的IO能力，同时原表会加上锁，同时如果转换存储引擎，将会失去和原存储引擎相关的所有特性，例如，如果将一张InnoDB表转换为MyISAM表，然后再转为InnoDB,原来InnoDB上所有的外键将全部丢失。

导出与导入

可以使用mysqldump工具将数据库文件导出，然后修改文件中的存储引擎选项，注意需要同时修改表名，

创建与查询

第三种方法综合了第一种方法的高效和第二种方法的安全，不需要导出整张表，首先创建一张新的存储引擎表，然后利用Insert……Select语法来导出数据 语法大致如下：

如果数据量比较大的话，可以使用分批处理，每段数据执行事务提交操作

总结

Mysql 拥有分层架构，上层是服务器的服务，下层是查询执行引擎，下层是存储引擎。Mysql DBA还是Mysql开发人员，都应该从宏观上了解Mysql的体系结构，特别是Mysql独有的插件式存储引擎的概念。对于不同的存储引擎，InnoDB对于95%的用户来说是最佳选择，当然不可否认其他某些情况下某些存储引擎会更好的满足需求