mysql algorithm copy_MySQL学习笔记七：存储引擎

1.MySQL存储引擎的设计采用“插件式”方案，用户可以很方便地选择使用哪种存储引擎，想使用mysql没有提供的引擎时，可以自己安装进去。

查看支持的存储引擎

mysql>show engines\G*************************** 1. row ***************************Engine: FEDERATED

Support: NO

Comment: Federated MySQL storage engine

Transactions:NULLXA:NULLSavepoints:NULL

*************************** 2. row ***************************Engine: MRG_MYISAM

Support: YES

Comment: Collectionofidentical 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 * fromusers;

Query OK,59 rows affected (0.05sec)

Records:59 Duplicates: 0 Warnings: 0mysql> alter table tb_4 engine=myisam;

Query OK,59 rows affected (0.08sec)

Records:59 Duplicates: 0 Warnings: 0mysql> insert into tb_4 select * fromtb_4;

Query OK,30208 rows affected (0.22sec)

Records:30208 Duplicates: 0 Warnings: 0mysql> show table status like 'tb_4'\G*************************** 1. row ***************************Name: tb_4

Engine: MyISAM

Version:10Row_format: Dynamic

Rows:60416Avg_row_length:88Data_length:5365760Max_data_length:281474976710655Index_length:1024Data_free:0Auto_increment:NULLCreate_time:2015-04-14 17:29:11Update_time:2015-04-14 17:29:11Check_time:NULLCollation: utf8_general_ci

Checksum:NULLCreate_options:

Comment:1 row in set (0.00sec)

mysql> create table tb_5 engine=archive as select * fromtb_4;

Query OK,60416 rows affected (0.25sec)

Records:60416 Duplicates: 0 Warnings: 0mysql> show table status like 'tb_5'\G*************************** 1. row ***************************Name: tb_5

Engine: ARCHIVE

Version:10Row_format: Compressed

Rows:60416Avg_row_length:0Data_length:42820Max_data_length:0Index_length:0Data_free:0Auto_increment:NULLCreate_time:NULLUpdate_time:2015-04-14 17:32:27Check_time:NULLCollation: utf8_general_ci

Checksum:NULLCreate_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.01sec)

mysql> insert into t1 values (1);

Query OK,1 row affected (0.00sec)

mysql> create table t2(id int not null primary key)engine=MyISAM;

Query OK,0 rows affected (0.01sec)

mysql> insert into t2 values (2);

Query OK,1 row affected (0.00sec)

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.06sec)

mysql> select * fromt3;+----+

| 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]# Thenext three options are mutually exclusive toSERVER_PORT below.

# skip-networking

# enable-named-pipe# ThePipe 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 onport=3306federated--------------------------------------安装federated-------------------------

mysql> select @@have_dynamic_loading;+------------------------+

| @@have_dynamic_loading |

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

| YES |

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

1 row in set (0.00sec)

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.01sec)//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.05sec)

mysql> select * fromb;+----+

| id |

+----+

| 10 |

+----+

1 row in set (0.01sec)----------------------------也可以进行insert,delete等操作--------------------

mysql> insert into b values (12);

Query OK,1 row affected (0.05sec)

mysql> select * fromb;+----+

| id |

+----+

| 10 |

| 12 |

+----+

mysql> show variables like 'port';+---------------+-------+

| Variable_name | Value |

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

| port | 3307 |

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

1 row in set (0.00sec)

mysql> select * froma;+----+

| 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.07sec)

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.00sec)

mysql> insert into tb_1(username,email) values ('test','test@qq.com');

Query OK,1 row affected (0.00sec)

mysql> rollback

->;

Query OK,0 rows affected (0.00 sec)

联机DDL测试(须5.6版本之后)

首先创建一个大表，要不小表就没什么意义了，因为其执行操作的效率都很高。

mysql> select count(1) fromtb_5;+----------+

| count(1) |

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

| 120832 |

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

1 row in set (0.00sec)

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是更好的选择。