Mysql 存储引擎学习笔记

由于各存储引擎功能特性差异较大,选择合适的存储引擎来存储数据对业务的优化也是非常重要的.今天对各类存储引擎学习学习.

MYSQL存储引擎类型:MYISAM,INNODB,MEMORY,ARCHIVE,CSV,KERKELEY DB,MERGE,Cluster/NDB等.

MyISAM 存储引擎:

特性

不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用

表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能

读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

适用场景

不需要事务支持（不支持）

并发相对较低（锁定机制问题）

数据修改相对较少（阻塞问题）

以读为主

数据一致性要求不是非常高

最佳实践

尽量索引（缓存机制）

调整读写优先级，根据实际需求确保重要操作更优先

启用延迟插入改善大批量写入性能

尽量顺序操作让insert数据都写入到尾部，减少阻塞

分解大的操作，降低单个操作的阻塞时间

降低并发数，某些高并发场景通过应用来进行排队机制

对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率

MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

总结: MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

InnoDB 存储引擎:

特性

具有较好的事务支持：支持4个事务隔离级别，支持多版本读

行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响

读写阻塞与事务隔离级别相关

具有非常高效的缓存特性：能缓存索引，也能缓存数据

整个表和主键以Cluster方式存储，组成一颗平衡树

所有Secondary Index都会保存主键信息

适用场景

需要事务支持（具有较好的事务特性）

行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成

数据更新较为频繁的场景

数据一致性要求较高

硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

最佳实践

主键尽可能小，避免给Secondary index带来过大的空间负担

避免全表扫描，因为会使用表锁

尽可能缓存所有的索引和数据，提高响应速度

在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交

合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性

避免主键更新，因为这会带来大量的数据移动

总结:InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

NDBCluster  存储引擎:

特性

分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分

支持事务：和Innodb一样，支持事务

可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互

内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

适用场景

具有非常高的并发需求

对单个请求的响应并不是非常的critical

查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

最佳实践

尽可能让查询简单，避免数据的跨节点传输

尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点

在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

日常使用当中主要是以上存储引擎了.

下面是关于存储引擎的一些基本操作

查看系统存储引擎:

mysql> show engines;

默认的存储引擎:

mysql> show variables like ‘%storage_engine%‘;

你要看某个表使用什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

mysql> show create table 表名;

其中DEFAULT表明系统的默认存储引擎，可以通过修改配置参数来变更：

default-storage-engine=MyISAM

查看某个存储引擎的使用状态信息:

mysql> show engine InnoDB status\G;

指定系统默认存储引擎为INNODB

default-storage-engine=INNODB

1.可以在启动数据库服务器时在命令行后面加上--default-storage-engine或--default-table-type选项。

2.更灵活的方式是在随MySQL服务器发布同时提供的MySQL客户端时指定使用的存储引擎。最直接的方式是在创建表时指定存储引擎的类型，向下面这样:

CREATE TABLE mytable (id int, titlechar(20)) ENGINE = INNODB

修改表的存储引擎方法：

1> 直接修改的方式: ALTER TABLE engineTest ENGINE = INNODB；

2> 间接修改的方法: 修改tablename 表的存储引擎为 INNODB;

  例: 1.use databasename;show create table tablename; 查看建表SQL;

      2.通过建表SQL,创建新的表名:  tablename_bak; //对查询出的建表SQL 做简单修改,表名与存储引擎.

      3.insert into tablename_bak select * from tablename;  //将数据插入新表,如果在线写数据表比较大,可以指定id;

      4.alter table tablename rename tablename_date;alter table tablename_bak rename tablename;  //备份表与在线表进行切换;

注意: 3,4步骤需要连贯操作,以免出现数据丢失.

     由于修改表的存储引擎会进行表锁,对大表的存储引擎切换使用 "间接修改"的方法,可以减少锁表时间.

修改数据库默认存储引擎：

在mysql配置文件（linux下为/etc/my.cnf），在mysqld后面增加default-storage-engine=INNODB即可。

但是如果表建立的时候是MyISAM，要更改整个数据库表的存储引擎，一般要一个表一个表的修改，比较繁琐，可以采用先把数据库导出，得到SQL，把MyISAM修改成INNODB，再导入的方式

批量修改导出的数据存储引擎: sed -i s/=MyISAM/=INNODB/g  mysqldump_database;

将进行大量表的引擎转换时,注意需要提前对转换后的存储引擎进行优化.

Innodb 存储引擎使用可以选择使用独立表空间与 共享表空间存储数据.

设置独立表空间存储数据:

   innodb_file_per_table  = 1   //这是一个动态参数,可以进行全局设置,不需要重启数据库.

查看当独立表空间设置情况:

mysql> show variables like ‘%per_table‘;

+-----------------------+-------+

| innodb_file_per_table | OFF   |

设置独立表空间存储:

mysql> set global innodb_file_per_table = 1;

检查操作设置结果:

mysql> show variables like ‘%per_table‘;

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

注意: 只有新创建的表才会使用独立表空间存储,生成 database_name/tablename.ibd 的数据与索引 共同存储文件.

经测试,独立表空间下面创建的表,在非独立表空间下面可以正常读写操作.

独立表空间与共享表空间两者之间的优缺点

共享表空间：

优点:可以放表空间分成多个文件存放到各个磁盘上（表空间文件大小不受表大小的限制，如一个表可以分布在不同步的文件上）。数据和文件放在一起方便管理。

缺点:所有的数据和索引存放到一个文件中意味着将有一个很常大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙.

特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：

优点:

1．  每个表都有自已独立的表空间,表的数据和索引都会存在自已的表空间中。

2.   可以实现单表在不同的数据库中移动。

3．  空间数据回收情况:（除drop table操作处，表空不能自已回收）

      Drop table操作自动回收表空间，如果对于统计分析或是日值表，删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。

      对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。

      使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点:

单表增加过大，如超过100个G。

相比较之下，使用独占表空间的效率以及性能会更高一点

学习参考:http://isky000.com/database/mysql-performance-tuning-storage-engine

本文出自 “旋木的技术博客” 博客，请务必保留此出处http://mrxiong.blog.51cto.com/287318/1370005

Mysql 存储引擎学习笔记,古老的榕树,5-wow.com