MySql(19)------mysql存储引擎

一 概要

插入件式存储引擎是MySql数据库最重要的特征之一，用户可以根据实际应用需要选择如何存储和索引数据，

是否使用事务等等。MySql支持多种存储引擎，以适应不同数据库领域的应用需要，用户可以选择不同的存储引擎，

提高效率，用户也可以按照自己的方式定制和使用自己的存储引擎。

二 存储引擎分类

MySql中注意包括以下存储引擎：

MyISAM, InnoDB,BDB, MEMORY, MERGE, EXAMPLE, NDB Cluster, ACCHIVE, CSV, BLACKHOLE, FEDERATED等扥，

其中常用存储引擎为MyISAM, InnoDB, MEMORY, MERGE,NDB Cluster。

只有InnoDB和BDB提供事务安全表，其他的存储引擎均为非事务安全表。

创建表时如果不指定存储引擎，就使用数据库默认的存储引擎，MySql5.5之前默认提供存储引擎为MyISAM,

5.5之后为InnoDB存储引擎。如果需要修改存储引擎，可以修改MySql配置文件中'default-storage-engine=INNODB'

或default-table-type。

2.1 查看当前默认的存储引擎：

show variables like '%storage_engine%';

2.2 查看当前数据库支持哪些存储引擎：

SHOW ENGINES \G

2.3 修改表的存储引擎

ALTER TABLE table_name ENGINE = engine_name;

2.4 创建表时可以指定存储引擎

CREATE TABLE `t_user_main` (

`f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',

`f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',

`f_age` int(3) DEFAULT NULL COMMENT '年龄',

PRIMARY KEY (`f_userId`)

) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

通过ENGINE显示指定存储引擎。

三 常用存储引擎分析

MySql存储引擎各种各样，有些几乎很少用到，主要分析常用的，工作中经常需要面对的常用存储引擎，如图：

3.1 MyISAM

从上面表格中观察可以知道，该存储引擎不支持事务，也不支持外键，优点是访问速度快，如果对数据完整性没有很高的要求或者

以查询(select)，插入(insert)为主，很少去更新(update)和删除(delete)可以用该引擎创建表。

每个使用MyISAM引擎的表在磁盘上存储3个文件：

(1).frm (存储表定义)

(2).MYD (存储数据)

(3).MYI (存储索引)

创建一个t_user_main表，使用MyISAM存储引擎，看看数据库存储文件eg:

值得注意的是，数据文件和索引文件可以放置在不同的目录，平分IO,以便获取更快的速度。

数据文件和索引放置路径可以通过DATA DIRECTORY='',INDEX DIRECTORY='';分别指定，

使用绝对路径，同时具有访问权限。如果使用MySql图形化工具，工具中设计表时会带有路径指定选项，

如果数据文件和索引文件不分离，不需指定。如，使用Navicat设计表时：

MyISAM表可能出现损坏情况，损坏后的表将不能访问，访问会提示修复或错误结果。

通过CHECK语句检查表的状态，REPAIR TABLE修复一个MyISAM表。

如果表损坏，可能导致书库重启，需要尽快确定原因，想出解决办法。

对于更新频繁，表容易出现碎片，占用巨大内存，一般需要定期通过OPTIMIZE TABLE或

myisamchk-r命令改善性能，出现故障时恢复比较困难。

3.2 InnoDB

InnoDB存储引擎从图表中可以看到支持提交，回滚和崩溃恢复能力的事务安全，以及外键功能。

同时也可以看到，比MyISAM效率低，会占用更多的磁盘空间存储数据和索引。注意有一些特点：

******自动增长列

InnoDB的自动增长列可以手动插入，如果插入的是null或0，则实际插入的是自动增长后的值。

创建一个InnoDB引擎表：

CREATE TABLE `t_user_person_info` (

`f_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',

`f_userId` int(11) NOT NULL COMMENT '用户id',

`f_salary` decimal(14,2) NOT NULL DEFAULT '0.00' COMMENT '工资',

PRIMARY KEY (`f_id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据测试自增涨列插入null或0的情况:

通过ALTER TABLE table_name AUTO_INCREMENT = n修改自增长默认起始值。

eg:

可以使用last_insert_id()查询当前线程最后插入记录使用的值。如果插入时使用多条记录查询时，将返回第一条记录插入的值。

eg:

注意：自增长列必须为主键或唯一索引列，而且只能是int类型，每个表只能有一个自增长列。

******外键约束

MySql中支持外键约束的引擎只有InnoDB。在创建外键的时候要求父表必须有对应的索引，

子表在创建外键的时候也会自动创建对应的索引。

eg:

父表：

CREATE TABLE `t_student` (

`f_userId` int(11) NOT NULL AUTO_INCREMENT,

`f_userName` varchar(50) DEFAULT NULL,

`f_age` int(3) DEFAULT NULL,

PRIMARY KEY (`f_userId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

子表:

CREATE TABLE `t_student_class` (

`f_id` int(11) NOT NULL AUTO_INCREMENT,

`f_userId` int(11) DEFAULT NULL,

`f_calssCode` int(11) DEFAULT NULL,

`f_className` varchar(50) DEFAULT NULL,

PRIMARY KEY (`f_id`),

KEY `sc_fk` (`f_userId`),

CONSTRAINT `sc_fk` FOREIGN KEY (`f_userId`) REFERENCES `t_student` (`f_userId`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

父表操作对子表相应的操作有四种情况：

(1)RESTRICT 和 NO ACTION含义相同，是指限制在子表有关记录的情况下父表不能更新。

(2)CASCADE 父表在更新删除时，更新删除子表对应记录。

(3)SET NULL 父表在更新删除时，子表对应的字段设置成空。

eg:

分别插入两条数据：

INSERT INTO t_student VALUES(1,'one',24),(2,'two',25);

INSERT INTO t_student_class VALUES(1,1,1001,'nnnn'),(2,2,2002,'wwww');

测试一下上面创建的CASCADE是否是级联删除：

从测试结果可以看出级联删除的效果，可以更改子表t_student_class中外键与主表主键的操作关系，做相关测试。

******存储方式

InnoDB有两种存储方式：

(1)使用共享表空间存储，这种方式表结构会保存在.frm文件中，数据和索引报错在innodb_data_home_dir和innodb_data_file_path

定义的表空间中，可以是多个文件。

(2)使用多表空间存储，使用这种方式存储表结构文件为.frm, 数据和索引文件存储在.ibd文件中。命名规则是表明加上.frm,.ibd格式，

例如，t_user_person_info.frm，t_user_person_info.ibd

3.3 MEMORY

MEMORY(记忆)引擎使用存储与内存中的内容创建表。

在磁盘上只有一个.frm文件，检索非常快，默认使用HASH索引，但是，一旦数据库服务关闭，数据将丢失。

创建一个MEMORY引擎表:

CREATE TABLE `t_user_asset` (

`f_id` int(11) NOT NULL AUTO_INCREMENT,

`f_salary` decimal(14,2) DEFAULT NULL,

PRIMARY KEY (`f_id`)

) ENGINE=MEMORY DEFAULT CHARSET=utf8;

磁盘文件格式.frm，只有一个文件:

在该表中插入一条数据:

INSERT INTO t_user_asset VALUES (1,20000);

查看数据，可以看到表中刚才插入的数据,然后我们把mysql服务器停掉,然后再重启mysql服务器，

再次查看t_user_asset表时，发现数据已经丢失了。所以使用MEMORY引擎时，对数据要谨慎。

既然数据都存储在内存中，服务器就需要足够的内存来维持一段时间MEMORY引擎表，当然了，

如果我们不需要数据，可以通过delete或truncate清空数据，有必要可以通过drop table把表删掉。

一般情况下,memory引擎表用于变化不是非常频繁的代码表，或者作为统计的中间结果表，因为其高效性，

可以提高统计的效率。切记对memory引擎表数据做修改，一定时刻提醒自己，找到合理的办法去解决mysql

服务重启后修改的数据何去何从。

3.4 MERGE

******MERGE引擎是一组MyISAM表的组合，这些表结构必须完全相同，MERGE本身并没有数据，对于METGE进行

的增，删，改，查操作实质上操作的是其内部的MyISAM表。

******对于MERGE类型的插入，是通过INSERT_METHOD字句定义插入的表，可以拥有3个不同的值，使用FIRST或LAST

值使得插入操作相应作用在第一个表或最后一个表，不定义这个子句或定义为NO,表示不能对MERGE表进行插入操作。

******可以通过drop对MERGE引擎表进行删除操作，这个删除只是删除了MERGE自身的定义，并不能影响其内部真实

存在的表。

******MERGE磁盘上会有两文件，一个为.frm文件，包含存储定义，一个为.MRG文件包含组合表的信息，

主要包括组成表，插入设定的依据，可以通过修改.MRG文件修改MERGE的定义，需要通过flush tables刷新才能生效。

eg:

先创建2个表结构相同的MyISAM引擎表：

table1:

CREATE TABLE `t_test_merge1001` (

`f_id` int(11) NOT NULL AUTO_INCREMENT,

`f_userName` varchar(50) DEFAULT NULL,

`f_age` int(3) DEFAULT NULL,

PRIMARY KEY (`f_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

table2:

CREATE TABLE `t_test_merge1002` (

`f_id` int(11) NOT NULL AUTO_INCREMENT,

`f_userName` varchar(50) DEFAULT NULL,

`f_age` int(3) DEFAULT NULL,

PRIMARY KEY (`f_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

分别插入两条数据：

INSERT INTO t_test_merge1001 VALUES(1,'one',24),(2,'two',25);

INSERT INTO t_test_merge1002 VALUES(1,'one',24),(2,'two',25);

创建MERGE引擎表，通过INSERT_METHOD指定数据插入时插入第一个表:

CREATE TABLE `t_merge_all` (

`f_id` int(11) NOT NULL AUTO_INCREMENT,

`f_userName` varchar(50) DEFAULT NULL,

`f_age` int(3) DEFAULT NULL,

INDEX (`f_id`)

) ENGINE=MERGE UNION=(t_test_merge1001,t_test_merge1002) INSERT_METHOD=FIRST;

增，删，改，插入数据：

插入TEST:

同时删除TEST:

同时更新TEST：

集合查询TEST:

注意：如果t_merge_all表建立主键时，更新，删除与插入时操作表依据一样