7. 表类型(存储引擎)的选择

MySQL中存储引擎针的概念，针对不同的存储需求选择最优的存储引擎。提高数据的存取读取效率。

7.1 MySQL存储引擎概述

MySQL支持的存储引擎包括MyISAM、InnoDB、MEMORY、MERGE、BDB等(前两种常用，后两种了解即可)。其中InnoDB和BDB提事务安全表，其他存储引擎都是非事务安全表。

MySQLv5.1之后默认的存储引擎为InnoDB，之前是MyISAM。

1· 查看mysql支持哪些引擎：show engines G;

engine:引擎名称;support:显示是否支持，default显示当前默认存储引擎;comment:简单描述;transactions:是否支持事务

2· 修改默认存储引擎： set default_storage_engine=innodb;

修改后只是本次会话有效，其他会话无效，可通过global选项设置全局会话有效，无论本次会话还是全局会话都有效，但是重启后都会失效。

3· 创建新表的时候，通过增加engine关键字则设置新表的存储引擎，也可以使用alter table tb_name engine=innodb修改表的存储引擎。

mysql> create table t (
    -> id int(10) not null auto_increment,
    -> primary key(id)
    -> )ENGINE=MyISAM DEFAULT CHARSET=UTF8;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t;
+-------+------------------------------+
| Table | Create Table                                                                                                         |
+-------+------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+------------------------------+


mysql> alter table t engine=InnoDB;
mysql> show create table t;
+-------+------------------------------+
| Table | Create Table                                                                                                         |
+-------+------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------+

7.2 各种存储引擎的特性

7.2.1 MyISAM

​ MySQL5.1之前的默认存储引擎。不支持事务、表级锁、不支持外键，优点是访问速度快。对事务完整性没有要求或者以SELECT、INSERT为主的应用可以使用这个引擎来创建表。

1· 每个MyISAM在磁盘上存储成3个文件，其文件名和表名相同，但扩展名分别是：

• .frm（存储表定义）
• .MYD（MYData，存储数据）
• .MYI (MYIndex，存储索引)

数据文件和索引文件可以放置在不同的目录，平均分布IO，获得更快的速度。

2有误

2· MyISAM的表支持3种不同的存储格式，分别是

• 静态（固定长度）表

  默认的存储格式，表中字段都是固定长度，如果列不满足定义的列宽则空格填充。

  优点：访问速度快

  缺点：占用空间

• 动态表

  存储的字段变长

  优点：占用空间少。

  缺点：容易产生碎片。

• 压缩表

mysql并没有什么静态、动态、压缩表，但是有静态动态压缩行，静态和动态是针对具体行中定义的数据类型，当保存具体的数据时所呈现的结果是什么样的。详情参考

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

varchar类型的对末尾空格不做处理，char类型的字段会去掉末尾空格

# varchar类型的对末尾空格不做处理，char类型的字段会去掉末尾空格
mysql> insert into t values(1,'abc'),(3,'abc  '),(4,'   abc');
mysql> select name,length(name) from t;
+--------+--------------+
| name   | length(name) |
+--------+--------------+
| abc    |            3 |
| abc    |            5 |
|    abc |            6 |
+--------+--------------+
3 rows in set (0.00 sec)

mysql> show create table t;
+-------+------+
| Table | Create Table                                                                   
+-------+---+
| t     | CREATE TABLE `t` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+-------+-------+
1 row in set (0.00 sec)

mysql> alter table t modify name char(10);      

mysql> select name,length(name) from t;
+--------+--------------+
| name   | length(name) |
+--------+--------------+
| abc    |            3 |
| abc    |            3 |
|    abc |            6 |
+--------+--------------+
#插入记录后面的空格都被去掉了，前面的空格保留。

7.2.2 InnoDB

​ MySQL5.1之后默认的存储引擎，InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，支持外键。但是对比 MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

应用场景：如果应用对事务的完整性有比较高大要求，要求在并发条件下要求数据的一致性，，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。

1. 自动增长列

• InnoDB 表的自动增长列可以手工插入，但是插入的值如果是空或者 0，则实际插入的将是自动增长后的值。
• 对于 InnoDB 表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于 MyISAM 表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引的前面几列进行排序后递增的。
• 可以通过alter table tb_name auto_increment = n;设置自动增长列的初始值。只适用于本次回话（重启失效）。可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。

示例：

mysql> create table autoincre_demo (
    -> i smallint not null auto_increment,
    -> name varchar(10),primary key(i));

mysql> insert into autoincre_demo values(1,'1'),(0,'2'),(null,'3');
mysql> select * from autoincre_demo;
+---+------+
| i | name |
+---+------+
| 1 | 1    |
| 2 | 2    |
| 3 | 3    |
+---+------+

2. 外键约束

MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

外键的使用需要满足下列的条件：

1. 两张表必须都是InnoDB表，并且它们没有临时表。

2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

3. 建立外键关系的对应列必须建立了索引。

(1)在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包 RESTRICT、CASCADE、SET NULL 和 NO ACTION。

1. CASCADE: 父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

2. SET NULL: 父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

3. NO ACTION: InnoDB拒绝删除或者更新父表。

4. RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

外键约束使用最多的两种情况无外乎：

1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

ON UPDATE CASCADE ON DELETE RESTRICT

2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

ON UPDATE CASCADE ON DELETE CASCADE

(2)当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除。
在导入多个表的数据时，如果需要忽略表之前的导入顺序，可以暂时关闭外键的检查；
同样，在执行 LOAD DATA 和 ALTER TABLE 操作的时候，可以通过暂时关闭外键约束来加快处理的速度，关闭的命令是SET FOREIGN_KEY_CHECKS = 0;，执行完成之后，通过执行SET FOREIGN_KEY_CHECKS = 1;语句改回原状态。

例如对下面创建的两个表，子表的外键指定是 ON DELETE RESTRICT ON UPDATE CACADE

mysql> CREATE TABLE country ( 
    ->   country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    ->   country VARCHAR(50) NOT NULL, 
    ->   last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    ->   PRIMARY KEY  (country_id) 
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE city ( 
    ->   city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
    ->   city VARCHAR(50) NOT NULL, 
    ->   country_id SMALLINT UNSIGNED NOT NULL, 
    ->   last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    ->   PRIMARY KEY  (city_id), 
    ->   KEY idx_fk_country_id (country_id), 
    ->   CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON 
    -> DELETE RESTRICT ON UPDATE CASCADE 
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into country(country_id,country) values(1,'ShanDong');
Query OK, 1 row affected (0.00 sec)

mysql> select * from country;
+------------+----------+---------------------+
| country_id | country  | last_update         |
+------------+----------+---------------------+
|          1 | ShanDong | 2020-07-21 10:21:48 |
+------------+----------+---------------------+

mysql> insert into city(city_id,city,country_id) values(111,'QingDao',1);

mysql> select * from city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|     111 | QingDao |          1 | 2020-07-21 10:22:53 |
+---------+---------+------------+---------------------+
# 删除主表中一条被子表关联的一条记录，报错
mysql> delete from country where country_id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`city`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
# 更新主表中的记录，子表有对应记录也会跟新
mysql> update country set country_id=1000 where country_id=1;

mysql> select * from city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|     111 | QingDao |       1000 | 2020-07-21 10:22:53 |
+---------+---------+------------+---------------------+

mysql> select * from country;
+------------+----------+---------------------+
| country_id | country  | last_update         |
+------------+----------+---------------------+
|       1000 | ShanDong | 2020-07-21 10:23:48 |
+------------+----------+---------------------+

#上面已经演示了，当删除一条被其他表的外键关联字段时会报错，我们可以通过将外键约束禁止掉，就可以删除了
#默认外键约束开启
mysql> show variables like '%FOREIGN_KEY_CHECKS%';       
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+

mysql> set foreign_key_checks=off;
mysql> show variables like '%FOREIGN_KEY_CHECKS%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
# 成功删除
mysql> delete from country where country_id=1000;

mysql> select * from country;
Empty set (0.00 sec)

3. 存储方式

innoDB存储数据和索引有共享表空间存储和独占表空间存储两种方式，通过参数innodb_file_per_table控制，0表示共享空间，也是默认的，1表示独占空间。两种方式的表结构（描述）都保存在.frm文件中。

共享表空间：

每一个数据库的所有表的数据、索引都保存在一个文件中，默认在data目录下，名为ibdata1，大小为10M的文件，可以通过参数innodn_data_file_path=/data/ibdata1:2000M来指定存储路径。

优点：

（1）可以将表空间分为多个文件放在不同的磁盘上，分布IO，提高性能。采用共享空间存储，存储空间的大小不受文件系统下文件大小的限制了，而取决于自身的限制，官方文档显示，表空间的最大限制是64TB。

（2）表数据和表结构放在一起，方便管理

缺点：由于所有的数据和索引都是在一个文件中混合存储，这样的话对一个表做了大量的删除操作后，表空间中会产生大量的空隙

独占表空间存储：

每一张表都有自己独立的表空间，表的结构依然在.frm文件中，还有一个后缀为.ibd的文件，保存了这张表的数据和索引。

优点：

每张表都有自己独立的表空间，可实现单表在不同数据库中移动空间可回收。drop table会自动回收；删除数据后，通过alter table emp engine=innodb也可回收不用的表空间，效率和性能会好一些。
缺点：由于每个表的数据都是以一个单独的文件来存放，所以会受到文件系统的大小限制

7.2.3 MEMORY

​ MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉。

MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。

7.2.4 MERGE

MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同，MERGE表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。

MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

7.3 status和variables

show status: 查看系统运行的实时状态，便于dba查看mysql当前运行的状态，做出相应优化，动态的，不可人为进行修改，只能系统自动update。

show variables : 查看系统参数，系统默认设置或者dba调整优化后的参数，静态的。可以通过set或者修改my.cnf配置文件修改。

首先可以通过下属两个命令来查看mysql的相应的系统参数

# 支持模糊查询
show status like '%abc%';
show variables like '%abc%';

参考:mysql之status和variables区别及用法详解

系统变量又分global和session，比如当用show variables like 'character_set%';不选中数据库和选中数据库时查询的记过是不一样的。