( ̄∞ ̄)

前言

数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是存储引擎

存储引擎查看

MySQL给开发者提供了查询存储引擎的功能,我这里使用的是MySQL5.5,可以使用:

1
SHOW ENGINES

命令来查看MySQL使用的引擎,命令的输出为(我用的Navicat Premium):
img
MySQL给用户提供了多种存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。

如果要想查看数据库默认使用哪个引擎,可以通过使用命令:

1
SHOW VARIABLES LIKE 'storage_engine';

查询结果为:
img

修改数据库引擎:

方式一:
修改配置文件my.ini
将mysql.ini另存为my.ini,在[mysqld]后面添加default-storage-engine=InnoDB,重启服务,数据库默认的引擎修改为InnoDB
方式二:
在建表的时候指定

1
2
3
4
create table mytbl(   
id int primary key,
name varchar(50)
)type=MyISAM;

方式三:
建表后更改

1
alter table table_name type = InnoDB;

怎么查看修改成功
方式一:

1
show table status from table_name;

方式二:

1
show create table table_name

方式三:
使用数据库管理工具啊。

在MySQL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用:YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎 。下面来看一下其中几种常用的引擎。

InnoDB(默认的存储引擎)

定义:
InnoDB是一个事务型的存储引擎,有行级锁定和外键约束。
Innodb引擎提供了对数据库ACID(原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability))事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。
//这个就是select锁表的一种,不明确主键。增删改查都可能会导致锁全表,在以后我们会详细列出。

1
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

适用场景:
1)经常更新的表,适合处理多重并发的更新请求。
2)支持事务。
3)可以从灾难中恢复(通过bin-log日志等)。
4)外键约束。只有他支持外键。
5)支持自动增加列属性auto_increment。

MySQL官方对 InnoDB 的讲解:
1)InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。
2)InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读,这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。
3)InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。
4)InnoDB是为处理巨大数据量时的最大性能设计,它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
5) InnoDB被用来在众多需要高性能的大型数据库站点上产生。

MyISAM:

定义:
MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。
MyISAM 存储引擎独立于操作系统,也就是可以在windows上使用,也可以比较简单的将数据转移到linux操作系统上去。
意味着:引擎在创建表的时候,会创建三个文件,一个是.frm文件用于存储表的定义,一个是.MYD文件用于存储表的数据,另一个是.MYI文件,存储的是索引。操作系统对大文件的操作是比较慢的,这样将表分为三个文件,那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作,其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。

适用场景:
1)不支持事务的设计,但是并不代表着有事务操作的项目不能用MyISAM存储引擎,可以在service层进行根据自己的业务需求进行相应的控制。
2)不支持外键的表设计。
3)查询速度很快,如果数据库insert和update的操作比较多的话比较适用。
4)整天 对表进行加锁的场景。
5)MyISAM极度强调快速读取操作。
6)MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。
缺点:
就是不能在表损坏后恢复数据。(是不能主动恢复)

补充:ISAM索引方法–索引顺序存取方法
定义:
是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到 数据库被查询的次> 数要远大于更新的次数。
特性:
ISAM执行读取操作的速度很快,而且不占用大量的内存和存储资源。
在设计之初就预想数据组织成有固定长度的记录,按顺序存储的。—ISAM是一种静态索引结构。
缺点:
1.它不 支持事务处理
2.也不能够容错。如果你的硬盘崩溃了,那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里,那就必须经常备份你所有的实 时数据,通过其复制特性,MYSQL能够支持这样的备份应用程序。

Memory(也叫HEAP):

定义:
使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH索引。
但是一旦服务关闭,表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快,但是它所管理的数据是不稳定的,而且如果在关机之前没有进行保存,那么所有的数据都会丢失。在数据行被删除的时候,HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

适用场景:
1)那些内容变化不频繁的代码表,或者作为统计操作的中间结果表,便于高效地堆中间结果进行分析并得到最终的统计结果。
2)目标数据比较小,而且非常频繁的进行访问,在内存中存放数据,如果太大的数据会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小,限制Memory表的最大的大小。
3)数据是临时的,而且必须立即可用得到,那么就可以放在内存中。
4)存储在Memory表中的数据如果突然间丢失的话也没有太大的关系。
注意: Memory同时支持散列索引和B树索引,B树索引可以使用部分查询和通配查询,也可以使用<,>和>=等操作符方便数据挖掘,散列索引相等的比较快但是对于范围的比较慢很多。
特性要求:
1)要求存储的数据是数据长度不变的格式,比如,Blob和Text类型的数据不可用(长度不固定的)。
2)要记住,在用完表格之后就删除表格。

Archive

定义:
基本上用于数据归档;它的压缩比非常的高,存储空间大概是innodb的10-15分之一所以它用来存储历史数据非常的适合,由于它不支持索引同时也不能缓存索引和数据,所以它不适合作为并发访问表的存储引擎。Archivec存储引擎使用行锁来实现高并发插入操作,但是它不支持事务,其设计目标只是提供高速的插入和压缩功能。
Archiv存储引擎的特点:
1、往archive表插入的数据会使用zlib进行数据压缩,磁盘I/O更少,archive支持optimize table、 check table操作。
2、每个archive表在磁盘上存在两个文件 .frm(存储表定义) 和 .arz(存储数据)。

3、archive存储引擎支持insert、replace和select操作,但是不支持update和delete。

4、archive存储引擎支持blob、text等大字段类型。支持auto_increment自增列同时自增列可以不是唯一索引。

5、archive支持auto_increment列,但是不支持往auto_increment列插入一个小于当前最大的值的值。

6、archive不支持索引所以无法在archive表上创建主键、唯一索引、和一般的索引。

7、Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。当数据量非常大的时候(达到1.5GB这个量级,CPU又比较快的时候)Archive的插入性能表现会较MyISAM为佳。

8、较小的空间占用,移植MySQL数据方便。当你需要把数据从一台MySQL服务器转移到另一台的时候,Archive表可以方便地移植到新的MySQL环境,你只需将保存Archive表的底层文件复制过去就可以了。

Mrg_Myisam(水平分表)

定义:
是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。
也就是说,他将MyIsam引擎的多个表聚合起来,但是它的内部没有数据,真正的数据依然是MyIsam引擎的表中,但是可以直接进行查询、删除更新等操作。
比如:我们可能会遇到这样的问题,同一种类的数据会根据数据的时间分为多个表,如果这时候进行查询的话,就会比较麻烦,Merge可以直接将多个表聚合成一个表统一查询,然后再删除Merge表(删除的是定义),原来的数据不会影响。

Blackhole(黑洞引擎)

定义:
任何写入到此引擎的数据均会被丢弃掉, 不做实际存储;Select语句的内容永远是空。
他会丢弃所有的插入的数据,服务器会记录下Blackhole表的日志,所以可以用于复制数据到备份数据库。
使用场景:
1)验证dump file语法的正确性
2)以使用blackhole引擎来检测binlog功能所需要的额外负载
3)充当日志服务器

存储引擎的选择

不同的存储引擎都有各自的特点,以适应不同的需求,如下表所示:

功 能 MyISAM Memory InnoDB Archive
存储限制 256TB RAM 64TB None
支持事物 No No Yes No
支持全文索引 Yes No No No
支持数索引 Yes Yes Yes No
支持哈希索引 No Yes No No
支持数据缓存 No N/A Yes No
支持外键 No No Yes No

如果要提供提交、回滚、崩溃恢复能力的事物安全(ACID兼容)能力,并要求实现并发控制,InnoDB是一个好的选择。

如果数据表主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率。

如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果。

如果只有INSERT和SELECT操作,可以选择Archive,Archive支持高并发的插入操作,但是本身不是事务安全的。Archive非常适合存储归档数据,如记录日志信息可以使用Archive。

使用哪一种引擎需要灵活选择,一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求,使用合适的存储引擎,将会提高整个数据库的性能。