关闭

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

标签: MyISAMInnoDBmysql数据库存储引擎
895人阅读 评论(0) 收藏 举报
分类:

一 概要

插件式存储引擎是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表建立主键时,更新,删除与插入时操作表依据一样

1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:304754次
    • 积分:6495
    • 等级:
    • 排名:第3840名
    • 原创:355篇
    • 转载:2篇
    • 译文:2篇
    • 评论:12条