大数据兼云计算（王明龙）讲师-MYSQL-DAY04-存储引擎

存储引擎

mysql 区别于其它数据库系统的一个重要特点是支持插入式存储引擎。

一．什么是存储引擎
存储引擎就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型
（即存储和操作此表的类型）。

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而 mysql 数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，
用户也可以根据自己的需要编写自己的存储引擎。

二．mysql中有哪些存储引擎？

常用几种存储引擎

1.MyISAM：
这种引擎是 mysql 最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。
当数据受损时，恢复工作也比较容易做。

动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次
修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进
行碎片整理。

压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在
读取的时候要先时行解压缩。

但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。 


2.MyISAM Merge引擎：
这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。

3.InnoDB：
InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。

4.memory(heap)：
这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。

5.archive：
这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。


如何选择两种引擎选择
1. 默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。
2. InnoDB支持事务，并且提供行级的锁定，处理大数据，多用户操作高并发量支持等
3. Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。


事务的特性： 
事务有以下四个标准属性的缩写ACID，通常被称为：
原子性: 确保工作单元内的所有操作都成功完成，否则事务将被中止在故障点，和以前的操作将回滚到以前的状态。
一致性: 确保数据库正确地改变状态后，成功提交的事务。
隔离性: 使事务操作彼此独立的和透明的。
持久性: 确保提交的事务的结果或效果的系统出现故障的情况下仍然存在。



三．存储引擎的操作

1. 查看数据库可以支持的存储引擎
mysql> show engines\G; 


*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.00 sec)



2.查看数据表的结构
mysql> desc ttt;
+----------+----------+------+-----+---------+----------------+
| Field  | Type   | Null | Key | Default | Extra     |
+----------+----------+------+-----+---------+----------------+
| id    | int(11) | NO  | PRI | NULL  | auto_increment |
| username | char(20) | NO  |   | NULL  |        |
| sex   | char(2) | YES |   | NULL  |        |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

3.显示表的创建语句
mysql> show create table t7;
+-------+-------------------------------------------+
| Table | Create Table 
+-------+-------------------------------------------+
| t7  | CREATE TABLE `t7` (
 `name` char(3) DEFAULT NULL,
 `id` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |


4.显示表的当前状态值
mysql> show table status like 't7'\G;
*************************** 1. row ***************************
      Name: t7
     Engine: MyISAM
    Version: 10
   Row_format: Fixed
      Rows: 8
 Avg_row_length: 14
  Data_length: 112
Max_data_length: 3940649673949183
  Index_length: 1024
   Data_free: 0
 Auto_increment: NULL
  Create_time: 2014-03-02 18:43:34
  Update_time: 2014-03-03 15:51:33
   Check_time: NULL
   Collation: utf8_general_ci
    Checksum: NULL
 Create_options: 
    Comment: 
1 row in set (0.07 sec)

综上可见，后两种方式都可以帮助我们查看某一表的存储引擎类型（图中已用红色方框标出）。

5.设置或修改表的存储引擎

创建数据库表时设置存储存储引擎的基本语法是：
Create table 表名(字段1 type(数据类型) attri(属性设置), 
        字段2 type(数据类型) attri(属性设置), 
        ..) engine = engineName 

例如,假设要创建一个名为user的表,此表包括id,用户名username和性别sex三个字段，并且要设置表类型为merge。则可用如下的方式创建此数据表，
mysql> create table user(
     id int not null auto_increment,
     username char(20) not null,
     sex char(2),
     primary key(id)
     )engine=merge;
修改存储引擎，可以用命令Alter table tableName engine =engineName。
若需要将表user的存储引擎修改为archive类型，

6.改变已存在表的存储引擎
alter table 表名 engine=引擎名;


7.更改mysql默认引擎为Innodb
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mysql默认是关闭InnoDB存储引擎的使用的，将InnoDB设置为默认的引擎如下。

1. 查看mysql存储引擎情况： 

mysql> show engines\G;           //InnoDB | YES              : 如果是yes, 说明此mysql数据库服务器支持InnoDB引擎


*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.00 sec)




2. 设置InnoDB为默认引擎：在配置文件 my.cnf 中的 [mysqld] 下面加入default-storage-engine=InnoDB

4. 重启mysql服务器

6. 登录mysql数据库，show engines\G; 如果出现 InnoDB |DEFAULT，则表示设置InnoDB为默认引擎成功。


mysql> show engines\G; 


*************************** 1. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 5. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
5 rows in set (0.01 sec)