存储引擎

1.什么是存储引擎?

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

2.MySQL支持哪些存储引擎?

mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。

3.什么是事务?

事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。

例如：你给我转账5块钱，流程如下
    a.从你银行卡取出5块钱，剩余计算money-5
    b.把上面5块钱打入我的账户上，我收到5块，剩余计算money+5.
上述转账的过程，对应的sql语句为：
   update 你_account set money=money-5 where name='你'；
   update 我_account set money=money+5 where name='我'；
        上述的两条SQL操作，在事务中的操作就是要么都执行，要么都不执行，不然钱就对不上了。
        这就是事务的原子性(Atomicity)。
事务的四大特性：
  1.原子性(Atomicity)
      事务是一个不可分割的单位，事务中的所有SQL等操作要么都发生，要么都不发生。
  2.一致性(Consistency)
      事务发生前和发生后，数据的完整性必须保持一致。
  3.隔离性(Isolation)
      当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。也就是其他人的操作在这个事务的执行过程中是看不到这个事务的执行结果的，也就是他们拿到的是这个事务执行之前的内容，等这个事务执行完才能拿到新的数据。
  4.持久性(Durability)
      一个事务一旦被提交，它对数据库中的数据改变就是永久性的。如果出了错误，事务也不允撤销，只能通过'补偿性事务'。
        
事务的开启：
    数据库默认事务是自动提交的，也就是发一条sql他就执行一条。如果想多条sql放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql会自动回滚事务。或者我们使用rollback命令手动回滚事务。

4.各种搜索引擎的特性?

并发性：某些应用程序比其他应用程序具有很多的颗粒级锁定要求（如行级锁定）。

事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如ACID兼容等。

引用完整性：通过DDL定义的外键，服务器需要强制保持关联数据库的引用完整性。

物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。

索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如B-tree索引）对几乎所有的存储引擎来说是共同的。

内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。

性能帮助：包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。

其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。

5.各种搜索引擎介绍

InnoDB

	MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。

MyISAM

	MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。

Memory

	在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失.
	Memory 存储引擎中的数据都存放在内存中,数据库重启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。

BLACKHOLE

	黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。可以应用于主备复制中的分发主库。

CSV

	它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。

NDB

	(又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。
	NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。
	目前能够支持MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。


Merge

	允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。

Federated

提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。

Example

这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的 "存根"。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。

Infobright
	第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。
	
NTSE 
	网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

6.几种常用存储引擎详细介绍

MyISAM引擎

MyISAM引擎特点：
    1.不支持事务
        事务是指逻辑上的一组操作，组成这组操作的各个单元，要么全成功要么全失败。
    2.表级锁定
        数据更新时锁定整个表：其锁定机制是表级锁定，也就是对表中的一个数据进行操作都会将这个表锁定，其他人不能操作这个表，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能。
    3.读写互相阻塞
        不仅会在写入的时候阻塞读取，MyISAM还会再读取的时候阻塞写入，但读本身并不会阻塞另外的读。
    4.只会缓存索引
        MyISAM可以通过key_buffer_size的值来提高缓存索引，以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据。    
    5.读取速度较快
        占用资源相对较少
    6.不支持外键约束，但只是全文索引
    7.MyISAM引擎是MySQL5.5版本之前的默认引擎，是对最初的ISAM引擎优化的产物。
    
    
    
MyISAM引擎适用的生产业务场景
    1.不需要事务支持的业务(例如转账就不行，充值也不行)
    2.一般为读数据比较多的应用，读写都频繁场景不适合，读多或者写多的都适合。
    3.读写并发访问都相对较低的业务(纯读纯写高并发也可以)(锁定机制问题)
    4.数据修改相对较少的业务(阻塞问题)
    5.以读为主的业务，例如：www.blog,图片信息数据库，用户数据库，商品库等业务
    6.对数据一致性要求不是很高的业务。
    7.中小型的网站部分业务会用。
    
小结：单一对数据库的操作都可以示用MyISAM，所谓单一就是尽量纯读，或纯写(insert,update,delete)等。




MyISAM引擎调优精要
    1.设置合适的索引(缓存机制)(where、join后面的列建立索引，重复值比较少的建索引等)
    2.调整读写优先级，根据实际需求确保重要操作更优先执行，读写的时候可以通过参数设置优先级。
    3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)。
    4.尽量顺序操作让insert数据都写入到尾部，较少阻塞。
    5.分解大的操作，降低单个操作的阻塞时间，就像操作系统控制cpu分片一样。
    6.降低并发数(减少对MySQL访问)，某些高并发场景通过应用进行排队队列机制Q队列。
    7.对于相对静态(更改不频繁)的数据库数据，充分利用Query Cache(可以通过配置文件配置)或memcached缓存服务可以极大的提高访问频率。
    8.MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问。
    9.可以把主从同步的主库使用innodb，从库使用MyISAM引擎。主库写，从库读可以(不推荐，有些麻烦的地方，市场上有人这么用)。
    
其他介绍:
    不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

InnoDB引擎

InnoDB引擎
    介绍：InnoDB引擎是MySQL数据库的另一个重要的存储引擎，正称为目前MySQL AB所发行新版的标准，被包含在所有二进制安装包里。和其他的存储引擎相比，InnoDB引擎的优点是支持兼容ACID的事务(类似于PostGreSQL)，以及参数完整性(即对外键的支持)。Oracle公司与2005年10月收购了Innobase。Innobase采用双认证授权。它使用GNU发行，也允许其他想将InnoDB结合到商业软件的团体获得授权。

InnoDB引擎特点：
    1.支持事务：支持4个事务隔离界别，支持多版本读。
    2.行级锁定(更新时一般是锁定当前行)：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
    3.读写阻塞与事务隔离级别相关(有多个级别，这就不介绍啦~)。
    4.具体非常高效的缓存特性：能缓存索引，也能缓存数据。
    5.整个表和主键与Cluster方式存储，组成一颗平衡树。(了解)
    6.所有SecondaryIndex都会保存主键信息。(了解)
    7.支持分区，表空间，类似oracle数据库。
    8.支持外键约束，不支持全文索引(5.5之前)，以后的都支持了。
    9.和MyISAM引擎比较，InnoDB对硬件资源要求还是比较高的。
        
    小结：三个重要功能：Supports transactions，row-level locking，and foreign keys

    
    
InnoDB引擎适用的生产业务场景
    1.需要事务支持(具有较好的事务特性，例银行业务)
    2.行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成。
    3.数据更新较为频繁的场景，如：BBS(论坛)、SNS(社交平台)、微博等
    4.数据一致性要求较高的业务，例如：充值转账，银行卡转账。
    5.硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘IO，可以通过一些参数来设置.
    6.相比MyISAM引擎，Innodb引擎更消耗资源，速度没有MyISAM引擎快
    
    
InnoDB引擎调优精要
    1.主键尽可能小，避免给Secondery index带来过大的空间负担。
    2.避免全表扫描，因为会使用表锁。
    3.尽可能缓存所有的索引和数据，提高响应速度，较少磁盘IO消耗。
    4.在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交，有开关可以控制提交方式。
    5合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
    如果innodb_flush_log_at_trx_commit的值为0，log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。
    6.避免主键更新，因为这会带来大量的数据移动。
    
    
其他介绍:
    InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用于建立其表空间。InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

7.MySQL常用存储引擎及应用场景

InnoDB

用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。

MyISAM

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。

Memory

将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。

8.存储引擎相关MySQL语句

查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎:
mysql> show engines;

查看正在使用的搜索引擎:
mysql>show variables like "storage_engine%";

指定存储引擎建表
在建表时指定:
mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 

mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;

在配置文件中指定:
#my.ini文件
[mysqld]
default-storage-engine=INNODB

补充:在Oracle 和SQL Server等所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。

　　Oracle中不存在引擎的概念，数据处理大致可以分成两大类：联机事务处理OLTP（on-line transaction processing）、联机分析处理OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。