Mysql学习笔记二， 表类型(存储引擎)的选择

Mysql与大多数数据库不同，它有存储引擎的概念。插件式存储引擎是Mysql数据库的最重要的特征之一，选择如何存储和索引数据、是否使用事务来判断使用哪种存储引擎。

• 存储引擎概述

1 存储引擎列表
InnoDB、BDB提供事务安全。
MyISAM、MEMORY、MERGE、 EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、
FEDERATED 非事务安全。
2 查看当前的默认存储引擎

show variables like 'table_type';

3 查看数据库支持的存储引擎

show engines \G;

or

show variables like 'have_%'; --disabled表示支持，但是数据库启动时禁止掉了

4 创建/修改表时指定存储引擎

create table t1 (id int(16), val varchar(10))engine = innodb;
alter table t1 engine = myisam;

---

• 各存储引擎的特性
  

---

• MyISAM存储引擎

1 概述：
Mysql默认的存储引擎， 不支持事务和外键。 优势是访问速度快， 推荐以select、insert为主的应用可以使用。
2 表空间：
|- .frm(存储表定义)
|- .MYD(MYData，存储数据)
|- .MYI(MYIndex，存储索引)
可以指定不同的目录存储索引文件和数据文件，需要在创建表的时候通过data directory 和 index directory 语句指定。 需要指定绝对路径。
3 检查和修复

 check table t1; --检查t1的健康状况
 repair table t1 ;--修复t1

4 存储格式
|- 静态(固定长度)表;
|- 动态表;
|- 压缩表;
4.1 静态表
这是默认的存储格式。要求字段都是定长字段， 每条记录都是定长。
优点： 存储速度快，容易缓存，故障后容易恢复；
缺点： 占用空间较多。
数据存储时会按照长度在尾部自动补空格，返回时把尾部的空格去除后返回给应用，如果此时存入的
数据尾巴有空格，可能会导致预料外的结果。
4.2 动态表
含有变长字段。
优点： 占用空间少。
缺点： 频繁更新删除数据后会产生大量碎片，需要定期执行OPTIMIZE TABLE or myisamchk -r,出现
故障后恢复较困难。
4.3 压缩表
用myisampack工具创建。
有点： 占用空间很小，每条记录都被单独压缩，只有非常小的访问开支。

---

• InnoDB存储引擎

1 概述
InnoDB存储引擎提供了事务安全，对比MyISAM, InnoDB写效率差一些， 并且占用更多的磁盘空间来保
存数据和索引。

2 与其他存储引擎的区别
2.1 自动增长列
可以手工插入自动增长列，但是插入的值是空或者0，则实际插入的将是自动增长后的值。
alter table t3 auto_increment = 3; -- 强制自增从3开始，保留在内存中，重启数据库失效。
select last_insert_id(); --当前线程最后插入记录使用的自增值，如果一次插入多条，取no.1
insert into t3 values(null,"5"),(6,"6");--同时插入两天记录，通过上面函数返回5
对于InnoDB,自动增长列必须是索引。如果是组合索引，也必须是第一列。
对于MyISAM,自动增长列可以是组合索引的其他列，插入数据后，自动增长列按索引的前几列排序后递增,
* 注意递增是针对索引前几列的相同值， 类似db2的partition by
2.2 外键约束
只有InnoDB支持外键

create table t1(id int not null auto_increment primary key, val varchar(16))engine =innodb default charset=utf8; --t1(父表),含字段id
create table t2(id int not null auto_increment primary key,val varchar(16), t1_id int not null, key fk_t1(t1_id), CONSTRAINT `fk_t2_t1` FOREIGN KEY (t1_id) REFERENCES t1 (id) on delete restrict on update cascade) engine=innodb charset=utf8;
--t2(子表),含字段t1_id,作为外键关联到父表的id字段。

|-restrict,no action: 如果子表上存在关联数据，父表无法删除对应的记录。
|-cascade: 父亲上相应字段的更新或删除，需要级联更新或删除子表对应的记录。
|-set null: 父亲上相应字段的更新或删除，子表对应字段被更新为null。
在导入数据/alter table/load data的时候，可以选择暂时关闭外键的检查；
set foreign_key_checks=0; -- 关闭外键的检查。
set foreign_key_chekcs=1; -- 恢复外键的检查
2.3 存储方式
|- 共享空间存储, 表结构存放在.frm中， 数据和索引存放在配置innodb_data_home_dir和
innodb_index_home_dir 定义的表空间下，可以是多个文件。
|- 多表空间存储， 需要设置innodb_file_per_table, 表结构存放在.frm, 每个表的数据和索引
存放在各自的.ibd文件中。如果是个分区表，文件名=表名+分区名。
* 修改存储方式，不影响已经存在的表

---

• MEMORY存储引擎
  每个memory表之对应一个.frm文件， 数据存放在内存中，默认使用hash索引，访问速度快。服务关闭
  则数据被清空。
create table t_memory engine=memory select id,val from t1; --通过t1创建memory表
show table status like 't_memory' \G;--查看表t_memory的状态

  对memory表建立索引，可以指定hash索引，还是btree索引。
create index t_memory_idx using btree on t_memory(id); --创建btree索引
show index from t_memory;

  在启动mysql的时候，使用–init-file选项， 把insert into … select 或者load data infile 这样的语句放在
  文件中，可以在服务启动的时候从持久稳固的数据源转载表。
  使用delete from/truncate table/drop table/重启服务 的方式，清空/删除memory表。 系统变量max_heap_table_size限制每个memory表的大小。定义表的时候可以通过max_rows子句指定表的最大行数
  主要用于变更很少的配置表，或者统计分析的中间表使用。

---

• MERGE存储引擎
  多个MyISAM表的组合，这些表的表结构要求一致
create table t1(id int,var varchar(16))engine = myisam; --创建myisam表t1,
create table t2(id int,var varchar(16))engine = myisam; -- t2表结构同t1
create table tt(id int,var varchar(16))engine = merge union=(t1,t2) insert_method=last;
-- merge引擎表tt,由t1和t2 union得到， 每次插入数据插入到lats table(t2)
  merge表本身并没有数据，所有的操作都是对内部的myisam表进行的， drop操作也只是删除merge表的
  表定义。
  如果没有定义insert_method,或者定义为no， 则不能执行insert操作。
  每个merge表除有一个.frm文件存储表定义外，还有一个.MRG文件包含组合表的信息，可以通过修改.MR
  G文件修改表， 修改后需要通过flush tables刷新信息。