数据库事务管理及存储引擎

http://blog.csdn.net/xiaoyu714543065/article/details/8211265

事务具有四个特征:原子性( Atomicity )、一致性( Consistency )、隔离性( Isolation )和持续性( Durability )。这四个特性简称为 ACID 特性。

MySQL5.5以后默认使用InnoDB存储引擎,其中InnoDB和BDB提供事务安全表,其它存储引擎都是非事务安全表。
若要修改默认引擎,可以修改配置文件中的default-storage-engine。可以通过:show variables like ‘default_storage_engine‘;查看当前数据库到默认引擎。命令:show enginesshow variables like ‘have%‘可以列出当前数据库所支持到引擎。其中Value显示为disabled的记录表示数据库支持此引擎,而在数据库启动时被禁用。在MySQL5.1以后,INFORMATION_SCHEMA数据库中存在一个ENGINES的表,它提供的信息与show engines;语句完全一样,可以使用下面语句来查询哪些存储引擎支持事物处理:select engine from information_chema.engines where transactions = ‘yes‘;
可以通过engine关键字在创建或修改数据库时指定所使用到引擎。
主要存储引擎:MyISAM、InnoDB、MEMORY和MERGE介绍:
在创建表到时候通过engine=...type=...来指定所要使用到引擎。show table status from DBname来查看指定表到引擎。

(一)MyISAM
  
它不支持事务,也不支持外键,尤其是访问速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件,其中文件名和表名都相同,但是扩展名分别为:

  • .frm(存储表定义)
  • MYD(MYData,存储数据)
  • MYI(MYIndex,存储索引)

  数据文件和索引文件可以放置在不同的目录,平均分配IO,获取更快的速度。要指定数据文件和索引文件的路径,需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定,文件路径需要使用绝对路径。
  每个MyISAM表都有一个标志,服务器或myisamchk程序在检查MyISAM数据表时会对这个标志进行设置。MyISAM表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器以为当机或崩溃,这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行,可以在启动服务器时使用--myisam-recover现象。这会让服务器在每次打开一个MyISAM数据表是自动检查数据表的标志并进行必要的修复处理。MyISAM类型的表可能会损坏,可以使用CHECK TABLE语句来检查MyISAM表的健康,并用REPAIR TABLE语句修复一个损坏到MyISAM表。
  MyISAM的表还支持3种不同的存储格式:

  • 静态(固定长度)表
  • 动态表
  • 压缩表

  其中静态表是默认的存储格式。静态表中的字段都是非变长字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格,但是在访问的时候并不会得到这些空格,这些空格在返回给应用之前已经去掉。同时需要注意:在某些情况下可能需要返回字段后的空格,而使用这种格式时后面到空格会被自动处理掉。
  动态表包含变长字段,记录不是固定长度的,这样存储的优点是占用空间较少,但是频繁到更新删除记录会产生碎片,需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能,并且出现故障的时候恢复相对比较困难。
  压缩表由myisamchk工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。 
(二)InnoDB
  
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
1)自动增长列:
  
InnoDB表的自动增长列可以手工插入,但是插入的如果是空或0,则实际插入到则是自动增长后到值。可以通过"ALTER TABLE...AUTO_INCREMENT=n;"语句强制设置自动增长值的起始值,默认为1,但是该强制到默认值是保存在内存中,数据库重启后该值将会丢失。可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入多条记录,那么返回的是第一条记录使用的自动增长值。
对于InnoDB表,自动增长列必须是索引。如果是组合索引,也必须是组合索引的第一列,但是对于MyISAM表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引到前面几列排序后递增的。
2)外键约束:
  
MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。
      在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括restrict、cascade、set null和no action。其中restrict和no action相同,是指限制在子表有关联的情况下,父表不能更新;casecade表示父表在更新或删除时,更新或者删除子表对应的记录;set null 则表示父表在更新或者删除的时候,子表对应的字段被set null。
  当某个表被其它表创建了外键参照,那么该表对应的索引或主键被禁止删除。
  可以使用set foreign_key_checks=0;临时关闭外键约束,set foreign_key_checks=1;打开约束。
(三)MEMORY
  
memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常快,因为它到数据是放在内存中的,并且默认使用HASH索引,但是一旦服务器关闭,表中的数据就会丢失,但表还会继续存在。
      默认情况下,memory数据表使用散列索引,利用这种索引进行“相等比较”非常快,但是对“范围比较”的速度就慢多了。因此,散列索引值适合使用在"="和"<=>"的操作符中,不适合使用在"<"或">"操作符中,也同样不适合用在order by字句里。如果确实要使用"<"或">"或betwen操作符,可以使用btree索引来加快速度。
  存储在MEMORY数据表里的数据行使用的是长度不变的格式,因此加快处理速度,这意味着不能使用BLOB和TEXT这样的长度可变的数据类型。VARCHAR是一种长度可变的类型,但因为它在MySQL内部当作长度固定不变的CHAR类型,所以可以使用。

create table tab_memory engine=memory select id,name,age,addr from man order by id;

  使用USING HASH/BTREE来指定特定到索引。

create index mem_hash using hash on tab_memory(city_id);

  在启动MySQL服务的时候使用--init-file选项,把insert into...select或load data infile 这样的语句放入到这个文件中,就可以在服务启动时从持久稳固的数据源中装载表。
  服务器需要足够的内存来维持所在的在同一时间使用的MEMORY表,当不再使用MEMORY表时,要释放MEMORY表所占用的内存,应该执行DELETE FROM或truncate table或者删除整个表。
  每个MEMORY表中放置到数据量的大小,受到max_heap_table_size系统变量的约束,这个系统变量的初始值是16M,同时在创建MEMORY表时可以使用MAX_ROWS子句来指定表中的最大行数。
(四)MERGE
  
merge存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,MERGE表中并没有数据,对MERGE类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的MyISAM表进行操作。对于对MERGE表进行的插入操作,是根据INSERT_METHOD子句定义的插入的表,可以有3个不同的值,first和last值使得插入操作被相应的作用在第一个或最后一个表上,不定义这个子句或者为NO,表示不能对这个MERGE表进行插入操作。可以对MERGE表进行drop操作,这个操作只是删除MERGE表的定义,对内部的表没有任何影响。MERGE在磁盘上保留2个以MERGE表名开头文件:.frm文件存储表的定义;.MRG文件包含组合表的信息,包括MERGE表由哪些表组成,插入数据时的依据。可以通过修改.MRG文件来修改MERGE表,但是修改后要通过flush table刷新。

create table man_all(id int,name varchar(20))engine=merge union=(man1,man2) insert_methos=last;

几个常用存储引擎的特点

下面我们重点介绍几种常用的存储引擎并对比各个存储引擎之间的区别和推荐使用方式。

特点 Myisam BDB Memory InnoDB Archive
存储限制 没有 没有 64TB 没有
事务安全   支持   支持  
锁机制 表锁 页锁 表锁 行锁 行锁
B树索引 支持 支持 支持 支持  
哈希索引     支持 支持  
全文索引 支持        
集群索引       支持  
数据缓存     支持 支持  
索引缓存 支持   支持 支持  
数据可压缩 支持       支持
空间使用 N/A 非常低
内存使用 中等
批量插入的速度 非常高
支持外键       支持  

最常使用的2种存储引擎:

  • Myisam是Mysql的默认存储引擎。当create创建新表时,未指定新表的存储引擎时,默认使用Myisam。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是.frm(存储表定义)、.MYD (MYData,存储数据)、.MYI (MYIndex,存储索引)。数据文件和索引文件可以放置在不同的目录,平均分布io,获得更快的速度。
  • InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

如何选择合适的存储引擎

选择标准:根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

下面是常用存储引擎的适用环境:

    1. MyISAM:默认的MySQL插件式存储引擎,它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一
    2. InnoDB:用于事务处理应用程序,具有众多特性,包括ACID事务支持。
    3. Memory:将所有数据保存在RAM中,在需要快速查找引用和其他类似数据的环境下,可提供极快的访问。
    4. Merge:允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起,并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。
时间: 10-22

数据库事务管理及存储引擎的相关文章

DML 事务 隔离级别 存储引擎对比

DML 事务 隔离级别 存储引擎对比 DML insert into 第一种: insert into tb_name [(col1,col2,....)]{values|value}(val1,val2,...)[,(val21,val22,....),....] 第二种: insert into tb_name set col_name=val1,col2=val2,.... 第三种(将一个表中的数据插入到另外一张表中): insert into tb_name select clause

Mysql数据库的触发器、存储引擎和存储过程

数据库的触发器 1.触发器 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句): DELETE,INSERT,UPDATE 我们可以监视某表的变化,当发生某种变化时,触发某个操作. 能监视:增,删,改 触发操作:增,删,改 2.触发器应用场景: 1.当向一张表中添加或删除记录时,需要在相关表中进行同步操作.比如,当一个订单产生时,订单所购的商品的库存量相应减少. 2.当表上某列数据的值与其他表中的数据有联系时.比如,当某客户进行欠款消费

重新学习MySQL数据库3:Mysql存储引擎与数据存储原理

重新学习Mysql数据库3:Mysql存储引擎与数据存储原理 数据库的定义 很多开发者在最开始时其实都对数据库有一个比较模糊的认识,觉得数据库就是一堆数据的集合,但是实际却比这复杂的多,数据库领域中有两个词非常容易混淆,也就是数据库和实例: 数据库:物理操作文件系统或其他形式文件类型的集合: 实例:MySQL 数据库由后台线程以及一个共享内存区组成: 对于数据库和实例的定义都来自于 MySQL 技术内幕:InnoDB 存储引擎 一书,想要了解 InnoDB 存储引擎的读者可以阅读这本书籍. 数据

MySQL数据库性能优化之存储引擎选择

MyISAM 1.特性 不支持事务:MyISAM存储引擎不支持事务,所以对事务有要求的业务场景不能使用 表级锁定:其锁定机制是表级索引,这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能 读写互相阻塞:不仅会在写入的时候阻塞读取,MyISAM还会在读取的时候阻塞写入,但读本身并不会阻塞另外的读 只会缓存索引:MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO,但是这个缓存区只会缓存索引,而不会缓存数据 2.适用场景 不需要事务支持(不支持) 并发相对较低(锁定机

MySQL密码管理、存储引擎及事务

MYSQL密码管理: 修改用户密码: (1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD('cleartext password'); (2) UPDATE mysql.user SET Password=PASSWORD('cleartext password')  WHERE User='USERNAME' AND Host='HOST'; (3) mysqladmin -uUSERNAME -hHOST -p  password 'NEW_PAS

MySQL数据库MyISAM和InnoDB存储引擎的比较

MyISAM是MySQL的默认存储引擎,基于传统的ISAM类型,支持全文搜索,但不是事务安全的,而且不支持外键.每张MyISAM表存放在三个文件中:frm 文件存放表格定义:数据文件是MYD (MYData):索引文件是MYI (MYIndex). InnoDB是事务型引擎,支持回滚.崩溃恢复能力.多版本并发控制.ACID事务,支持行级锁定(InnoDB表的行锁不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,如like操作时的SQL语句),以及提

深入探讨:MySQL数据库MyISAM与InnoDB存储引擎的比较

From: http://www.jb51.net/article/37766.htm MySQL有多种存储引擎,MyISAM和InnoDB是其中常用的两种.这里介绍关于这两种引擎的一些基本概念(非深入介绍).MyISAM是MySQL的默认存储引擎,基于传统的ISAM类型,支持全文搜索,但不是事务安全的,而且不支持外键.每张MyISAM表存放在三个文件中:frm 文件存放表格定义:数据文件是MYD (MYData):索引文件是MYI (MYIndex).InnoDB是事务型引擎,支持回滚.崩溃恢

【转】MySQL数据库MyISAM和InnoDB存储引擎的比较

MySQL有多种存储引擎,MyISAM和InnoDB是其中常用的两种.这里介绍关于这两种引擎的一些基本概念(非深入介绍). MyISAM是MySQL的默认存储引擎,基于传统的ISAM类型,支持全文搜索,但不是事务安全的,而且不支持外键.每张MyISAM表存放在三个文件中:frm 文件存放表格定义:数据文件是MYD (MYData):索引文件是MYI (MYIndex). InnoDB是事务型引擎,支持回滚.崩溃恢复能力.多版本并发控制.ACID事务,支持行级锁定(InnoDB表的行锁不是绝对的,

mysql数据库之基本操作和存储引擎

一.知识储备 数据库服务器:一台计算机(对内存要求比较高) 数据库管理系统:如mysql,是一个软件 数据库:oldboy_stu,相当于文件夹 表:student,scholl,class_list,相当于一个具体的文件 记录:1 susan  158426544   22,相当于文件中的一行内容 二.初始SQL语言 SQL(Structured Query Language 即结构化查询语言)SQL语言主要用于存取数据.查询数据.更新数据和管理关系数据库系统,SQL语言由IBM开发.SQL语