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

原创 2016年08月29日 14:57:10

一 概要

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

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

一步一步学MySQL----19 MySQL中的存储引擎和数据类型

存储引擎制定了表的类型,即如何存储和索引数据、是否支持事务等,同时存储引擎也决定了表在计算机中的存储方式。 19.1 查看MySQL5.5所支持的存储引擎命令: mysql> show engin...

mysql几种存储引擎介绍

前言 在数据库中存的就是一张张有着千丝万缕关系的表,所以表设计的好坏,将直接影响着整个数据库。而在设计表的时候,我们都会关注一个问题,使用什么存储引擎。等一下,存储引擎?什么是存储引擎? 什么是存...

MySQL技术内幕:InnoDB存储引擎

  • 2017年08月13日 21:06
  • 47.77MB
  • 下载

MySQL+InnoDB存储引擎的一些参数.doc

  • 2014年01月09日 15:42
  • 210KB
  • 下载

mysql的存储引擎类型和索引类型

mysql的存储引擎,常用的有innodb和myisam innodb支持外键,事务,行锁,安全性更高,写入快查询慢,适合大数据量 myisam查询快写入慢,支持全文索引,表锁(MyISA...
  • nuli888
  • nuli888
  • 2016年07月08日 23:29
  • 6993

MySQL技术内幕InnoDB存储引擎

  • 2015年03月19日 19:00
  • 154KB
  • 下载

MySQL存储引擎【InnoDB、MyISAM、Memory】

数据库,MySQL这样存在多存储引擎的数据库软件,清楚常见的存储引擎的区别,使用合适的存储引擎,使得项目跑的更顺畅,有时候对于一个项目,甚至比项目本身都重要。这篇文章,旨在浅谈常见的三种存储引擎的区别...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MySql(19)------mysql存储引擎
举报原因:
原因补充:

(最多只允许输入30个字)