目录
一、存储引擎的介绍
数据库存储引擎是数据库底层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎,还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。
二、操作存储引擎
- 查看搜索引擎
# 查看搜索引擎
SHOW engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
# 查看当前使用的搜索引擎
SHOW variables LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set, 1 warning (0.04 sec)
- 指定搜索引擎
# 指定默认搜索引擎
SET DEAFAULT_STORAGE_ENGINE='搜索引擎名字';
# 创建表时指定搜索引擎
CREATE TABLE tb1(id INT, lname CHAR(20)) ENGINE='存储引擎名字';
- 创建表之后修改搜索引擎
ALTER TABLE 表名 ENGINE = '搜索引擎名字';
三、常见搜索引擎介绍
1、InnoDB存储引擎
介绍:
InnoDB是事务型数据库的首选引擎,是目前MYSQL的默认事务型引擎,是目前最重要、使用最广泛的存储引擎。支持事务安全表(ACID),支持行锁定和外键。
InnoDB主要特性:
- InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中,可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来,甚至在同一个查询中也可以混合
- InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
- InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘文件)。这与MyISAM表不同,比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上
- InnoDB支持外键完整性约束,存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键
- InnoDB被用在众多需要高性能的大型数据库站点上
- InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
- InnoDB 文件存储方式为.frm文件存储表结构,.ibd文件存储数据内容。
格式文件介绍:
- .frm文件:保存的是表结构定义描述文件
- .ibd文件:保存的是employee表中的数据内容
适用场景:
由于其支持事务处理,支持外键,支持崩溃修复能力和并发控制,所以适用于以下场景。
- 对事务的完整性要求比较高的(比如银行)
- 要求实现并发控制的数据库(比如售票)
- 需要频繁的更新、删除操作的数据库(因为支持事务的提交(commit)和回滚(rollback))。
2、MyISAM存储引擎
介绍:
MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度,但不支持事物和外键。
MyISAM主要特性有:
- MyISAM 有一个变量专门来保存整个表的行数,查询count很快(注意不能加任何 where 条件)
- MyISAM 支持全文索引;
- MyISAM 可以被压缩后进行查询操作,节省空间容量;
- MyISAM 支持表级锁,不支持行级锁;
- MyISAM 中主键不是必须的;
- MyISAM 文件存储方式为.frm文件存储表结构,.MYD文件存储数据内容,.MYI文件存储索引文件。
- 可以把数据文件和索引文件放在不同目录
- 每个字符列可以有不同的字符集
存储格式:
- 静态表(默认):字段都是非变长的(每个记录都是固定长度的)。存储非常迅速、容易缓存,出现故障容易恢复;占用空间通常比动态表多。
- 动态表:占用的空间相对较少,但是频繁的更新删除记录会产生碎片,需要定期执行optimize table或myisamchk -r命令来改善性能,而且出现故障的时候恢复比较困难。
- 压缩表:使用myisampack工具创建,占用非常小的磁盘空间。因为每个记录是被单独压缩的,所以只有非常小的访问开支。
静态表的数据在存储的时候会按照列的宽度定义补足空格,在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格,在返回结果的时候也会被去掉。(其实是数据类型char的行为,动态表中若有这个数据类型也同样会有这个问题)
使用MyISAM引擎创建数据库,将产生3个文件。文件的名字以表名字开始,扩展名之处文件类型:.frm文件存储表定义、数据文件的扩展名为.MYD(MYData)、索引文件的扩展名时.MYI(MYIndex)。
场景:
如果表主要是用于插入新记录和读出记录,那么选择MyISAM能实现处理高效率。
3、MERGE存储引擎
介绍:
MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表结构必须完全相同,尽管其使用不如其它引擎突出,但是在某些情况下非常有用。说白了,Merge表就是几个相同MyISAM表的聚合器;Merge表中并没有数据,对Merge类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行操作。场景:
对于服务器日志这种信息,一般常用的存储策略是将数据分成很多表,每个名称与特定的时间端相关。例如:可以用12个相同的表来存储服务器日志数据,每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表,这意味着需要编写并更新多表查询,以反映这些表中的信息。与其编写这些可能出现错误的查询,不如将这些表合并起来使用一条查询,之后再删除Merge表,而不影响原来的数据,删除Merge表只是删除Merge表的定义,对内部的表没有任何影响。
4、ARCHIVE存储引擎
介绍:
Archive是归档的意思,在归档之后很多的高级功能就不再支持了,仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前,Archive是不支持索引,但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制,它使用zlib压缩库,在记录被请求时会实时压缩,所以它经常被用来当做仓库使用。
场景:
由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎,但是前提是不经常对该表进行查询操作。
5、Memory存储引擎
Memory存储引擎(在以往的版本中,又叫 Heap 存储引擎)。数据存储在内存中,在实际的应用场景中用到的很少,但是它与优化器查询有很大的关系,对临时表很有用。
Memory中基于Hash索引,底层是 Hash 表,对于精确查询非常高效。即:在’='、'in’条件下高效,无法通过索引做区间查询。只能扫描全表。(范围查询、排序、组合索引效率不高)
特点:
- 数据都是存储在内存中,IO效率比其他引擎高很多;(优点:效率高)
- 服务重启后数据会丢失,内存数据表默认只有16M(缺点:保证不了持久性)
- 支持 Hash索引,B Tree索引,默认为 Hash索引
- 不支持大数据存储类型,如 blog,text 等
- 表级锁
应用场景:
- 等值查找热度较高数据;
- 查询结果在内存中进行计算,大多数都是采用这种存储引擎作为临时表存储需要计算的数据;
6、CSV 存储引擎
介绍:
使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件(所以,它可以将CSV类型的文件当做表进行处理),这种文件是一种普通文本文件,每个数据行占用一个文本行,而每个数据之间用逗号隔开。该种类型的存储引擎不支持索引,即使用该种类型的表没有主键列;另外也不允许表中的字段为null。csv的编码转换需要格外注意。场景:
这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件,将其存放在MySQL服务器的数据目录中,服务器就能够马上读取相关的CSV文件。同样,如果写数据库到一个CSV表,外部程序也可以立刻读取它。在实现某种类型的日志记录时,CSV表作为一种数据交换格式,特别有用。
7、BLACKHOLE存储引擎(黑洞引擎)
介绍:
该存储引擎支持事务,而且支持mvcc的行级锁,写入这种引擎表中的任何数据都会消失,主要用于做日志记录或同步归档的中继存储,这个存储引擎除非有特别目的,否则不适合使用。场景:
如果配置一主多从的话,多个从服务器会在主服务器上分别开启自己相对应的线程,执行binlogdump命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽,可以单独建立一个伪的从服务器或者叫分发服务器。
8、PERFORMANCE_SCHEMA存储引擎
介绍:
该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能:提供进程等待的详细信息,包括锁、互斥变量、文件信息;保存历史的事件汇总信息,为提供MySQL服务器性能做出详细的判断;对于新增和删除监控事件点都非常容易,并可以随意改变mysql服务器的监控周期,例如(CYCLE、MICROSECOND)。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
场景:
DBA能够较明细得了解性能降低可能是由于哪些瓶颈。
9、Federated存储引擎
介绍:
该存储引擎可以不同的Mysql服务器联合起来,逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据,针对federated存储引擎表的查询会被发送到远程数据库的表上执行,本地是不存储任何数据的。缺点:
- 对本地虚拟表的结构修改,并不会修改远程表的结构
- truncate 命令,会清除远程表数据
- drop命令只会删除虚拟表,并不会删除远程表
- 不支持 alter table 命令
- select count(*), select * from limit M, N 等语句执行效率非常低,数据量较大时存在很严重的问题,但是按主键或索引列查询,则很快,如以下查询就非常慢(假设 id 为主索引)select id from db.tablea where id >100 limit 10 ; 而以下查询就很快:select id from db.tablea where id >100 and id<150;
- 如果虚拟虚拟表中字段未建立索引,而实体表中为此字段建立了索引,此种情况下,性能也相当差。但是当给虚拟表建立索引后,性能恢复正常。
- 类似 where name like "str%" limit 1 的查询,即使在 name 列上创建了索引,也会导致查询过慢,是因为federated引擎会将所有满足条件的记录读取到本地,再进行 limit 处理。
场景:
dblink。
四、存储引擎的选择
功 能 | 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 |