Mysql存储引擎&查询缓存

存储引擎概述（部分）

特点	InnoDb	MyISAM	MEMORY
存储限制	64TB	有	有
事务安全	支持
锁机制	行锁	表锁	表锁
B树索引	支持	支持	支持
哈希索引			支持（NDB也支持）
全文索引		支持
集群索引	支持
数据缓存	支持		支持
索引缓存	支持	支持	支持
数据可压缩		支持
空间使用	高	低
内存使用	高	低
批量插入速度	低	高	高
支持外键	支持

查询MySQL支持的存储引擎   mysql> show engines \g

修改存储引擎：mysql> alter table books engine = innodb;

MySQL引擎的特性

 一、InnoDB是一个事务型的存储引擎，有行级锁定和外键约束。

Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

物理存储结构：表结构数据放在.frm中，表数据和索引数据放在.idb文件中（多表空间存储）。innodb_file_per_table参数

适用场景：
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被用来在众多需要高性能的大型数据库站点上产生。

事物：简称ACID
A 。事务的原子性(Atomicity)：指一个事务要么全部执行,要么不执行.也就是说一个事务不可能只执行了一半就停止了.比如你从取款机取钱,这个事务可以分成两个步骤:1划卡,2出钱.不可能划了卡,而钱却没出来.这两步必须同时完成.要么就不完成.
C 。事务的一致性(Consistency)：指事务的运行并不改变数据库中数据的一致性.例如,完整性约束了a+b=10,一个事务改变了a,那么b也应该随之改变.
I 。独立性(Isolation）:事务的独立性也有称作隔离性,是指两个以上的事务不会出现交错执行的状态.因为这样可能会导致数据不一致.

D 。持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚.

二、MyIASM

MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

物理存储结构：引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。

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

就是不能在表损坏后恢复数据。（是不能主动恢复）

myisamchk 可以坚持MyISAM类型表的健康状态修复甚至优化表存储

-e 彻底检查表 -r 修复几乎所有损坏问题 -u解压缩

三、memory

使用存在内存中的内容来创建表。每个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）要记住，在用完表格之后就删除表格。

MySQL查询缓存

缓存机制就是说，如果运行相同的sql语句，服务器会直接从缓存中取结果，而不需要再去解析和执行sql语句。查询缓存会存储最新数据，而不会返回过期数据。当数据被修改后，在查询缓存中任何数据均被清除。

查询缓存的相关参数。   show variables like ‘%query_cache%’

have_query_name 设置是否支持查询缓存区 set session query_cache_type=ON

query_cache_size用来设置mysql可以缓存的总字节数 set @@global.query_cache_size = 10240;

query_cache_limit 用来设置mysql可以缓存的最大结果集

经常使用以下命令监控和维护查询缓存。

1、flush query cache 整理查询缓存，并不会从缓存移除任何查询结果

2、reset query cache 该命令用于移除查询缓存中所有的查询结果

缓存查询利弊：

一方面可以使查询变得更加高效，改善服务器的性能。另一方面，查询缓存本身也需要消耗系统IO资源，具体体现如下：

a、服务器会检测查询缓存中是否存在相同条目

b、如果没有，会将查询结果保存到查询缓存。

c、数据库发生增加操作，服务器查询缓存中相对应的查询结果将会无效