一、前言
- 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
MYSQL架构如下
二、存储引擎查看
2.1 查看存储引擎
mysql> show engines;
在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎
2.2 查看默认存储引擎
mysql> SHOW VARIABLES LIKE ‘storage_engine’;
+—————-+——–+
| Variable_name | Value |
+—————-+——–+
| storage_engine | MyISAM |
+—————-+——–+
1 row in set (0.00 sec)
2.3 设置存储引擎
在配置文件my.cnf中的 [mysqld] 下面加入
default-storage-engine=INNODB三、Innodb引擎
该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。
mysql5.5及以后版本默认存储引擎
3.1 Innodb是一种事务型存储引擎
提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。
如果在应用中执行大量insert和update操作，应该选择InnoDB
使用Redo Log和Undo Log来完成一致性
show variables like ‘innodb_log_buffer_size’; 查看redo 缓冲区大小
show variables like ‘innodb_log_file_in_group’; 查看log file数量innodb支持行级锁
行级锁(开销大，并发性高）可以最大程度的支持并发 行级锁是由存储引擎层实现的
表级锁（开销小，并发性低），通常在服务器层实现
使用表空间进行数据存储
查看表空间类型
使用 show variables like ‘innodb_file_per_table’;
on：独立表空间：tablename.ibd
off：系统表空间：ibdataX
修改这个参数使用set global innodb_file_per_table=on(off);来完成两者之间的比较：
1.系统表空间无法简单的收缩文件大小，会造成较大的空间浪费（即删除无效数据之后不会改变文件的大小）
独立表空间可以通过optimize table命令收缩系统文件，这种方法不会影响数据表的正常使用
2.系统表空间会产生IO瓶颈
独立表空间可以同时向多个文件刷新数据（频繁写入的表）
建议：对innodb使用独立表空间
3.2 InnoDB主要特性有
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。
InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合2、InnoDB是为处理巨大数据量的最大性能设计。
它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，
如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件
三、MyIASM引擎
3.1 特性
- 建表会生成frm MYD（特有） MYI（特有）三个文件
3.2 并发性及锁级别
- 表级锁并发性差,因为不支持行级锁和外键
- 表损坏修复：不支持事务
- 可以使用check table tabklename和repair table tablename 来进行myisam表的检查和修复
- 还有mysql的工具myisamchk –help 来进行修复
- MyISAM 支持数据压缩 myisampack *.MYI
- 对于已经压缩的表只能进行读操作
3.3 应用场景：
3.3.1 非事务型应用
- MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM
3.3.2 只读类应用
- 不支持事物也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些
- 如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择
- MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描
3.3.3 空间类应用
四、MEMORY
- MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问
使用MySQL Memory存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当mysqld守护进程崩溃时，所有的Memory数据都会丢失。获得速度的同时也带来了一些缺陷。它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和TEXT这样的长度可变的数据类型，VARCHAR是一种长度可变的类型，但因为它在MySQL内部当做长度固定不变的CHAR类型，所以可以使用。
4.1 Memory存储引擎使用场景
1.目标数据较小，而且被非常频繁地访问。
在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。
Memory同时支持散列索引和B树索引。B树索引的优于散列索引的是，可以使用部分查询和通配查询，也可以使用<、>和>=等操作符方便数据挖掘。散列索引进行“相等比较”非常快，但是对“范围比较”的速度就慢多了，因此散列索引值适合使用在=和<>的操作符中，不适合在<或>操作符中，也同样不适合用在order by子句中。
4.2 表创建时利用USING子句指定要使用的引擎
- 在username字段上使用了HASH散列索引
create table users
(
id smallint unsigned not null auto_increment,
username varchar(15) not null,
pwd varchar(15) not null,
index using hash (username),
primary key (id)
)engine=memory;- 使用BTREE索引。
create table users
(
id smallint unsigned not null auto_increment,
username varchar(15) not null,
pwd varchar(15) not null,
index using btree (username),
primary key (id)
)engine=memory;五、MERGE
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。简单地说Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。Merge存储引擎的使用场景。
对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。
六、ARCHIVE
- zlib对表数据进行压缩，磁盘I/O更少
- 数据存储在ARZ为后缀的文件中
特点
- 只支持insert和select操作
- 只允许在自增ID列上加索引
Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。
七、CSV存储引擎
- 数据以文本方式存储在文件中
- .CSV文件存储表内容
- .CSM文件存储表的元数据如表状态和数据量
- .frm文件存储表结构信息（mysql服务器层所使用）
7.1 特点
- 以CSV格式进行数据存储
- 所有列必须都是不能为NULL的
- 不支持索引（不适合大表，不适合在线处理）
- 可以对数据文件直接编辑，保存文本文件内容
7.2 使用场景
- 适合作为数据交换的中间表
- 电子表格->CSV文件->MySQL数据目录
- 数据->CSV文件->其他web程序
八、存储引擎的选择
8.1 存储引擎的特点及选择
不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示：
如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果
如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能
8.2 MyIASM与InnoDB主要区别：
1、MyIASM是非事务安全的，而InnoDB是事务安全的
2、MyIASM锁的粒度是表级的，而InnoDB支持行级锁
3、MyIASM支持全文类型索引，而InnoDB不支持全文索引
4、MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM
5、MyIASM表保存成文件形式，跨平台使用更加方便
九、解除正在死锁的状态有两种方法
9.1 第一种：
- 1.查询是否锁表
show OPEN TABLES where In_use > 0;- 2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist- 3.杀死进程id（就是上面命令的id列）
kill id9.2 第二种：
- 1.查看下在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;- 2.杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程ID例子：
查出死锁进程：SHOW PROCESSLIST
杀掉进程 KILL 420821;
其它关于查看死锁的命令：
- 1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;- 2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;- 3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
278
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
