MySQL优化系列（五）--数据库存储引擎（主要分析对比InnoDB和MyISAM以及讲述Mrg_Myisam分表）

一、存储引擎的概述：
（1）为什么要合理选择数据库存储引擎：
mysql 中的数据用各种不同的技术存储在文件（或内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能在mysql中被称作存储引擎（也称表类型）。mysql默认配置了许多不同的存储引擎，可以预先设置或者在mysql服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。
（2）定义
数据库引擎是同于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图、存储过程）
（3）存储引擎左右：
设计并创建数据库以保护系统所需的关系或xml文档
实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程序，还包括生成使用SQL Server工具和实用工具以使用数据的过程。
为单位或客户部署实现的系统。
提供日常管理支持以优化数据库的性能。
（4）如何修改数据库引擎：
方式一：
修改配置文件my.ini

将mysql.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB

方式二：
在建表的时候指定

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

方式三：
建表后更改

alter table table_name type = InnoDB;
（5）怎么查看修改成功？

方式一：
show table status from table_name;
方式二：
show create table table_name
方式三：
使用数据库管理工具啊。
二、MySQL各大存储引擎：
最好先看下你下的MySQL支持什么数据库引擎
存储引擎主要有： 1. MyIsam , 2. InnoDB, 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam
（1）InnoDB：
定义：（默认的存储引擎）
InnoDB是一个事务型存储引擎，有行级锁定和外键约束。
InnoDB引擎提供了对数据库acid事务的支持，并且实现了sql标准的四种隔离级别。
该引擎该提供了行级锁和外键约束。
它的目标是处理大容量数据库系统，它本身其实就是基于mysql后台的完整数据库系统，mysql运行时innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持fulltext类型的索引，而且他没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个sql语句时mysql不能确定要扫描的范围，innodb表同样会锁全表。

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
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也支持外键强制。你可以再sql查询中自由地将innodb类型的表与其他表类型混合起来
4）innodb是为处理巨大数据量时的最大性能设计的，它的cpu效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的
5）innodb被用来在众多高性能的大型数据库站点上使用

（2）MyIsam：
定义：
MyIsam没有提供对数据库的支持，也不支持行级锁和挖建，因此当insert或者update数据时等写操作需要锁定整个表，效率会低一些。
MyIsam疫情独立于操作系统，也就是可以再windows上使用，也可以比较简单的将数据转移到linux操作系统上去。
引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.myd文件用于存储表的数据，另一个是.myi文件，存储的是索引。操作系统对大文件操作很慢，这样将表分为三个文件，那么.myd这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。myisam还是用一种表格锁定的机制，来优化多个并发读写操作，其代价是你需要经常运行optimize table命令，来恢复被更新机制锁浪费的空间。
适用场景：
1）不支持事务的设计，但是不代表事务操作的项目不能用myisam存储引擎，可以再service层进行根据自己的业务需求进行相应的控制。
2）不支持外键的表设计
3）查询速度很快。
4）整天对表进行枷锁的场景
5）myisam季度强调快速读取操作
6）myisam中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
缺点：
就是不能在表损坏后恢复数据（是不能主动恢复）
补充：ISAM索引方法–索引顺序存取方法
定义：
是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。

特性：
ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。
在设计之初就预想数据组织成有固定长度的记录，按顺序存储的。—ISAM是一种静态索引结构。
缺点：
1.它不 支持事务处理
2.也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
（3）Memory（也叫HEAP）堆内存嘛：
定义：
使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。

但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。
使用场景：
1）使用那些不频繁变化的代码表，或者作为统计操作的中间结果表，便于高效地在中间结果进行分析并得到最终的统计结果。
2）目标数据比较小，而且非常频繁的进行访问，在内存中存放数据，如果数据太大会造成内存溢出。可以通过参数max_heap_table_size控制Memory表的大小，限制Memory表的最大值。
3）数据时临时的，而且必须like可以用到，那么久可以放在内存中
4）存储在memory表中的数据如果突然间丢失的话也灭有太大关系。
注意：memory同时支持散列索引和B树索引，B树索引可以使用部分查询和统配查询，也可以使用<>和>=等操作符方便数据挖掘。散列索引相等的比较快但是对于范围的比较慢很多。
特性要求：
1）要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用（长度不固定的）。
2）要记住，在用完表格之后就删除表格。
（4）Mrg_Myisam：（分表的一种方式–水平分表）
他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作
比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。
（5）Blackhole（黑洞引擎）
定义
任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。

他会丢弃所有的插入的数据，服务器会记录下Blackhole表的日志，所以可以用于复制数据到备份数据库。

使用场景：
1）验证dump file语法的正确性

2）以使用blackhole引擎来检测binlog功能所需要的额外负载

3）充当日志服务器
**三、InnoDB和MyIsam使用及其原理对比：
（2）效果对比总述：
1）事务。MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持已经外部键等高级数据库功能。
InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如updatetable set num=1 where name like “a%”

就是说在不确定的范围时，InnoDB还是会锁表的。
2）性能主题。
MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。

3）行数保存。
InnoDB 中不保存表的具体行数，也就是说，执行select count() fromtable时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。

4）索引存储。
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

InnoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表＆索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM表不同，比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上。

5）服务器数据备份。
InnoDB必须导出SQL来备份，LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

而且MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

6）锁的支持。
MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

//用于把表的拷贝从主服务器转移到从属服务器。
LOAD TABLE tbl_name FROM MASTER
1
2
（3）使用建议：
以下两点必须使用 InnoDB：
1）可靠性高或者要求事务处理，则使用InnoDB。这个是必须的。
2）表更新和查询都相当的频繁，并且表锁定的机会比较大的情况指定InnoDB数据引擎的创建。

对比之下，MyISAM的使用场景：
1）做很多count的计算的。如一些日志，调查的业务表。
2）插入修改不频繁，查询非常频繁的。

MySQL能够允许你在表这一层应用数据库引擎，所以你可以只对需要事务处理的表格来进行性能优化，而把不需要事务处理的表格交给更加轻便的MyISAM引擎。对于 MySQL而言，灵活性才是关键。
四、InnoDB和MyIsam引擎原理：
在此之前，先去理解什么是聚簇和非聚簇索引。
（1）MyIASM引擎的索引结构：
MyISAM索引结构: MyISAM索引用的B+ tree来储存数据，MyISAM索引的指针指向的是键值的地址，地址存储的是数据。

B+Tree的数据域存储的内容为实际数据的地址，也就是说它的索引和实际的数据是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。