MySQL学习笔记七：存储引擎

2022-11-14 23:14:42

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-----------------

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 '',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 ( 开启多重表空间 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','test@qq.com');

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

　　

上一篇：What’s the difference between an interface and an abstract class in Java?


下一篇：C#操作XML文档(XmlDocument、XmlNode、XmlAttribute、SelectSingleNode、SelectNodes、XmlNodeList)