MySQL学习笔记七：存储引擎
1.MySQL存储引擎的设计采用“插件式”方案，用户可以很方便地选择使用哪种存储引擎，想使用mysql没有提供的引擎时，可以自己安装进去。
查看支持的存储引擎
mysql> show engines\G *************************** 1. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO //是否支持事务 XA: NO //是否支持分布式 Savepoints: NO //是否支持保存点
给表指定存储引擎
create table tb_1 engine=MyISAM as select * from tb_2;
alter table tb_1 engine=InnoDB
2.常见存储引擎：
MEMORY存储引擎
memory存储引擎可以帮助mysql建立内存表，但memory引擎的表只有表结构的定义文件，不保存数据，一旦关闭mysql服务，memory引擎表所有数据都会丢失。
分配给memory引擎表的内存，正常情况下不会释放，除非删除，重建这个表，其占用的内存才会被回收。memory引擎表占用内存的大小跟max_heap_table_size系统变量有关默认为16M。可以通过set max_heap_table_size = xxx来改变。
基于其特性，一般用它来保存临时数据或或者来自其他表的数据。
archive存储引擎
archive存储引擎采用zlib无损压缩算法，基于它的对象能够大量压缩数据，节省存储空间，可以支持select,insert操作，插入效率很高，适合存储大量的历史数据，极少被访问的数据。
mysql> create table tb_4 as select * from users; Query OK, 59 rows affected (0.05 sec) Records: 59 Duplicates: 0 Warnings: 0 mysql> alter table tb_4 engine=myisam; Query OK, 59 rows affected (0.08 sec) Records: 59 Duplicates: 0 Warnings: 0 mysql> insert into tb_4 select * from tb_4; Query OK, 30208 rows affected (0.22 sec) Records: 30208 Duplicates: 0 Warnings: 0 mysql> show table status like ‘tb_4‘\G *************************** 1. row *************************** Name: tb_4 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 60416 Avg_row_length: 88 Data_length: 5365760 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2015-04-14 17:29:11 Update_time: 2015-04-14 17:29:11 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> create table tb_5 engine=archive as select * from tb_4; Query OK, 60416 rows affected (0.25 sec) Records: 60416 Duplicates: 0 Warnings: 0 mysql> show table status like ‘tb_5‘\G *************************** 1. row *************************** Name: tb_5 Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 60416 Avg_row_length: 0 Data_length: 42820 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: 2015-04-14 17:32:27 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
MERGE存储引擎
merge存储引擎就是将一一组MyISAM存储引擎表聚合在一起，可以简化查询操作，merge存储引擎表有.frm和.mgr文件，记录表结构的定义以及其数据来源，本身并不存储数据
mysql> create table t1(id int not null primary key)engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1); Query OK, 1 row affected (0.00 sec) mysql> create table t2(id int not null primary key)engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> insert into t2 values (2); Query OK, 1 row affected (0.00 sec) mysql> create table t3(id int not null primary key)engine=MRG_MYISAM union=(t1,t2) insert_method=first; Query OK, 0 rows affected (0.06 sec) mysql> select * from t3; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
--------这个结果好像在执行select * from t1 union all select * from t2-----------------
XA: NULL Savepoints: NULL *************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO *************************** 3. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO //是否支持事务 XA: NO //是否支持分布式 Savepoints: NO //是否支持保存点
给表指定存储引擎
create table tb_1 engine=MyISAM as select * from tb_2;
alter table tb_1 engine=InnoDB
2.常见存储引擎：
MEMORY存储引擎
memory存储引擎可以帮助mysql建立内存表，但memory引擎的表只有表结构的定义文件，不保存数据，一旦关闭mysql服务，memory引擎表所有数据都会丢失。
分配给memory引擎表的内存，正常情况下不会释放，除非删除，重建这个表，其占用的内存才会被回收。memory引擎表占用内存的大小跟max_heap_table_size系统变量有关默认为16M。可以通过set max_heap_table_size = xxx来改变。
基于其特性，一般用它来保存临时数据或或者来自其他表的数据。
archive存储引擎
archive存储引擎采用zlib无损压缩算法，基于它的对象能够大量压缩数据，节省存储空间，可以支持select,insert操作，插入效率很高，适合存储大量的历史数据，极少被访问的数据。
mysql> create table tb_4 as select * from users; Query OK, 59 rows affected (0.05 sec) Records: 59 Duplicates: 0 Warnings: 0 mysql> alter table tb_4 engine=myisam; Query OK, 59 rows affected (0.08 sec) Records: 59 Duplicates: 0 Warnings: 0 mysql> insert into tb_4 select * from tb_4; Query OK, 30208 rows affected (0.22 sec) Records: 30208 Duplicates: 0 Warnings: 0 mysql> show table status like ‘tb_4‘\G *************************** 1. row *************************** Name: tb_4 Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 60416 Avg_row_length: 88 Data_length: 5365760 Max_data_length: 281474976710655 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2015-04-14 17:29:11 Update_time: 2015-04-14 17:29:11 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> create table tb_5 engine=archive as select * from tb_4; Query OK, 60416 rows affected (0.25 sec) Records: 60416 Duplicates: 0 Warnings: 0 mysql> show table status like ‘tb_5‘\G *************************** 1. row *************************** Name: tb_5 Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 60416 Avg_row_length: 0 Data_length: 42820 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: 2015-04-14 17:32:27 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
MERGE存储引擎
merge存储引擎就是将一一组MyISAM存储引擎表聚合在一起，可以简化查询操作，merge存储引擎表有.frm和.mgr文件，记录表结构的定义以及其数据来源，本身并不存储数据
mysql> create table t1(id int not null primary key)engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> insert into t1 values (1); Query OK, 1 row affected (0.00 sec) mysql> create table t2(id int not null primary key)engine=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> insert into t2 values (2); Query OK, 1 row affected (0.00 sec) mysql> create table t3(id int not null primary key)engine=MRG_MYISAM union=(t1,t2) insert_method=first; Query OK, 0 rows affected (0.06 sec) mysql> select * from t3; +----+ | id | +----+ | 1 | | 2 | +----+ 2 rows in set (0.00 sec)
--------这个结果好像在执行select * from t1 union all select * from t2-----------------
FEDERATED存储引擎
FEDERATED引擎可以实现远端mysql数据库的访问，有点类似oracle数据库的dblink，不过该引擎mysql默认情况下是没有启用的，因此是不支持的。
开启该引擎，只需在my.ini配置文件[mysqld]下添加federated即可，如果没有安装该插件，则须先安装该插件。
[mysqld] # The next three options are mutually exclusive to SERVER_PORT below. # skip-networking # enable-named-pipe # The Pipe the MySQL Server will use # socket=mysql #配置主从数据库 server-id=3306 #主从复制是通过二进制文件来进行，所以要开启日志功能 log-bin=mysql-bin #主机，读写都可以 read-only=0 #需要备份数据，多个写多行 binlog-do-db=test #不需要备份的数据库，多个写多行 binlog-ignore-db=mysql # The TCP/IP Port the MySQL Server will listen on port=3306 federated --------------------------------------安装federated------------------------- mysql> select @@have_dynamic_loading; +------------------------+ | @@have_dynamic_loading | +------------------------+ | YES | +------------------------+ 1 row in set (0.00 sec) mysql> install plugin federated soname ‘ha_federated.so‘; mysql> show engines\G *************************** 1. row *************************** Engine: FEDERATED Support: YES Comment: Federated MySQL storage engine Transactions: NO XA: NO Savepoints: NO
FEDERATED引擎表并不保存远端数据库的数据，只有表结构的定义，其包括了远端mysql数据库的连接信息，创建该引擎表时其表结构一定要和远端数据库的一致，并且必须是mysql数据库，该引擎表不支持事务和表结构的更改，以下实例。
mysql> create table b(id int not null primary key)engine=federated connection="mysql://root:123456@127.0.0.1:3307/test/ a"; Query OK, 0 rows affected (0.01 sec) //connection字符串也可以使用server服务名来代替,创建server服务名语法 mysql> create server aa foreign data wrapper mysql options (USER ‘root‘,PASSWORD ‘123456‘,host ‘127.0.0.1‘,PORT 3307,dat abase ‘test‘); Query OK, 1 row affected (0.05 sec) mysql> select * from b; +----+ | id | +----+ | 10 | +----+ 1 row in set (0.01 sec) ----------------------------也可以进行insert,delete等操作-------------------- mysql> insert into b values (12); Query OK, 1 row affected (0.05 sec) mysql> select * from b; +----+ | id | +----+ | 10 | | 12 | +----+ mysql> show variables like ‘port‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3307 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select * from a; +----+ | id | +----+ | 10 | | 12 | +----+
MyISAM存储引擎
MyISAM存储引擎在mysql5.5版本之前是非常收到欢迎，也是mysql默认存储引擎，直到5.5版本中被InnoDB所取代。MyISAM不支持分布式（集群），不支持事务，不支持外键这些关键特性，但该引擎表查询快，写入快。MyISAM支持三种存储格式，静态，动态和压缩。
静态表就是不包含varchar、blob、text等变长的列，而动态表就相反。但并不是拥有varchar,varbinary类型的表就一定要是动态表，可以通过row_format=fixed强制转换成静态表，如果拥有blob,text就只能是动态表了。
静态表在处理性能会比动态表高，这是它们的特性决定的。静态表行的长度是固定的，就很容易得到数据文件的位置和查找记录，只需索引中的行号*行的长度就可得到该行的具体位置，而动态表的行的长度是不一定的，因此每行还需一个行头来记录行的长度。但是动态表比静态表节约更多空间。
InnoDB存储引擎
InnoDB存储引擎是mysql数据库的默认存储引擎，支持集群，事务，外键，行级锁等关键特性。InnoDB引擎默认只对应一个系统表空间，所有的InnoDB表和索引都保存在该表空间对应的数据文件中。该引擎部分配置如下：
指定InnoDB引擎表数据文件存放地址：
InnoDB_data_file_path=ibdata1(file_name):2048M(file_size):autoextend:max:50G //如有多个数据文件需要设置以分号;隔开即可
设置InnoDB数据文件的存放地址
InnoDB_data_home_dir=E:\mysqlData
启用多重表空间，多重表空间比单独的系统表空间相比具有维护成本低，灵活，支持数据压缩，空间自动扩展等有点
Set global InnoDB_file_per_table=N (1 开启多重表空间 0关闭)
设置日志文件大小，在my.in配置文件加上以下的参数
Innodb_log_file_size = 50M Innodb_log_files_in_group = 3 //指定日志文件组数量，至少2个
设置InnoDB关闭模式
set global innodb_fast_shutdown=N //N=0 快速模式 1正常模式 2强制终止
在mysql5.6和以后版本可以设置单独的undo表空间（没实验），只需在配置文件中添加以下参数即可
innodb_undo_directory = {path} //undo日志存放的物理位置 innodb_undo_tablespaces = N //undo日志表空间的数量，也就是undo数据文件的数量
开启事务：
禁止自动提交
mysql> set autocommit=false; Query OK, 0 rows affected (0.07 sec) mysql> show variables like ‘autocommit‘; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | OFF | +---------------+-------+ 1 row in set (0.00 sec)
显式启动事务，即使使用commit提交了也能回滚
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb_1(username,email) values (‘test‘,‘[email protected]‘); Query OK, 1 row affected (0.00 sec) mysql> rollback -> ; Query OK, 0 rows affected (0.00 sec)
联机DDL测试（须5.6版本之后）
首先创建一个大表，要不小表就没什么意义了，因为其执行操作的效率都很高。
mysql> select count(1) from tb_5; +----------+ | count(1) | +----------+ | 120832 | +----------+ 1 row in set (0.00 sec) mysql> alter table tb_6 add index uname (userName),algorithm=copy;
mysql> alter table tb_6 add index uname (userName),algorithm=in-place;
MyISAM和InnoDB对比：
MySAM不支持事务安全，InnoDB支持事务安全（包括ACID特性），外键等。
MySAM不支持集群，支持全文检索，InnoDB支持集群，不支持全文检索等。
MySAM支持锁的粒度最小为表，InnoDB则为行级锁，但不绝对，在执行某个SQL时不能确定扫描范围时。
在执行大量的insert，update操作时，InnoDB处理性能更好。在执行大量的select时，MyISAM是更好的选择。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
