5. 存储引擎
1. 存储引擎（表类型）
查看当前的默认存储引擎:
show variables like '%engine%';
查看支持的存储引擎:
show engines;
修改表的存储引擎:
alter table t1 engine = innodb;
[mysqld]
default_storage_engine=myisam ----修改数据库默认引擎
2. 常用引擎介绍
MySQL存储引擎比较 - 梦里梦外 - ITeye技术网站
MyISAM
最常用, 拥有较高的插入、查询速度
支持fulltext
不支持事务, 不能建外键
文章表、新闻表等安全性要求不高的
对事务的完整性、并发性要求不是很高的
应用以查询和插入为主, 只有很少的更新和删除操作
mysql数据库中的数据是以文件的形式存储在磁盘上的，可以cp文件的方式备份
存放在my.cnf中datadir所指目录下
1张myisam表对应3个文件
table1.frm 存放表结构 table1.myd 存放数据 table1.myi 存放索引
定期执行optimize table t1来改善性能，整理磁盘空间
MyISAM表还支持3中不同的存储格式：
1 静态表 2 动态表 3 压缩表
静态表 是默认的存储格式，静态表中的字段都是非变长的字段，优点是：存储非常迅速，容易缓存，出现故障容易恢复；缺点是：占用的空间通常比动态表多。（注意： 在存储时，列的宽度不足时，用空格补足，当时在访问的时候并不会得到这些空格）
动态表 的字段是变长的，优点是：占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期改善性能，并且出现故障的时候恢复相对比较困难。
压缩表 占用磁盘空间小，每个记录是被单独压缩的，所以只有非常小的访问开支。
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
InnoDB
支持事务, 支持外键约束
相比myisam, innodb写入数据的效率差一些, 并且会占用更多的磁盘空间
订单表、资金表、 账单表、火车票表 ，对安全性要求高的, 需要事务功能的, 应选用innodb
修改存储方式
ibdata1 表空间文件
ib_logfile0 日志文件 ib_logfile1 日志文件
ls /var/lib/mysql/ds/
仅t1.frm文件(保存表结构)
数据和索引保存在表空间文件中
默认没有指定innodb_data_home_dir
默认在datadir下创建一个autoextend的共享表空间文件ibdata1
将所有库的InnoDB表的数据与索引存储在一个共享表空间文件ibdata1中，删数据后, ibdata1文件不会自动收缩
在磁盘监控时, 也许就报警不断了, 但实际上MySQL还可以运行良好
单个数据库的物理备份也是问题, 通常只能将数据mysqldump导出
[root@server2 database]# /usr/local/mysql56/bin/mysqldump ds stu > /tmp/db
指定表空间文件
innodb_data_home_dir=
innodb_data_file_path=/database2/ibdata1:100M;/tmp/ibdata2:50M:autoextend
把ibdata2也指在/database2下也可
最后一个数据文件必须是自动扩充的, 也只有最后一个文件允许自动扩充
数据首先放在ibdata1中, 存满100M后, 数据就放到ibdata2中, 存满50MB后, ibdata2将自动增长
1. rm -f /database/ib_log* -- 可能要
2. 加2条配置项
3. 建文件夹/database2且chown
4. 启动服务
以后就只用新指定的ibdata1与ibdata2了, 原来的/database/ibdata1就没用上了, 但它里面还存有数据, 要先切回去把它里面的数据导出来
Innodb有两种管理表空间的方法:
1. 共享表空间, 5.5默认
2. 独立表空间, 5.6默认
独立表空间:
1. 每张表都有一个自已独立的表空间文件
2. 每张表的数据和索引都会存在自已的表空间文件中
drop table自动回收表空间 ，删除大量数据后可以通过alter table t1 engine = innodb;回收空间
共享表空间在insert操作上稍有优势, 其它都没独立表空间好
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
memory(heap)
表中的数据放在内存中, 没有实际存入磁盘, 一旦服务关闭, 表中的数据就会丢失
服务器需要足够的内存来存储memory表
极高的增删改查效率
当不再需要memory表里的数据时, 要释放被memory表使用的内存, 应delete from或truncate table或drop table
memory表仅对应1个.frm文件, 访问非常快
支持btree与hash索引, 默认使用hash索引
对于临时中转表, 可以用memory引擎, 速度最快
主要用于统计操作的中间结果表
select case age when 20 then salary*1. 2 when 30 then salary*1.3 else salary*1.4 end from ds.stu;
这是临时结果, 可以存到memory表t1中
create table t1()engine = memory;
insert into t1 select …
insert into t1 select stuID ,stuName,age, case age when 20 then salary*1. 2 when 30 then salary*1.3 else salary*1.4 end as salary from ds.stu
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
merge(mrg_myisam)
merge存储引擎是一组myisam表的组合
这些myisam表必须结构完全相同, merge表本身没有数据
对merge表进行的查询、更新、删除, 实际上是对内部的myisam表进行的
对merge表的插入操作, 是通过insert_method子句定义插入的表, 可以有3个不同的值
insert_method=first|last|no
first插入在第1张表上, last插入在最后一张表上
no或不定义这个子句, 表示不能对这个merge表执行插入操作
drop merge表, 只是删除merge表的定义, 对内部的myisam表无任何影响
merge表在磁盘上的对应文件
create table payment_11
(
id int,
money dec(6, 1)
) engine=myisam;
create table payment_12
(
id int,
money dec(6, 1)
) engine=myisam;
create table payment_all
(
id int,
money dec(6, 1)
) engine=merge union=(payment_11, payment_12) insert_method=last; 创建时不会检查11、12存不存在
通常我们使用merge表来透明地对多个表进行查询和更新操作
