7.Mysql存储引擎

7.表类型(存储引擎)的选择
7.1 Mysql存储引擎概述
mysql支持插件式存储引擎,即存储引擎以插件形式存在于mysql库中。
mysql支持的存储引擎包括:MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等。
其中:InnoDB和BDB提供事务安全表,其他存储引擎都是非事务安全表;
默认存储引擎(即建表时不指定存储引擎),mysql5.5开始默认存储引擎为InnoDB,mysql5.5之前默认存储引擎为MyISAM。
1.查看默认存储引擎:InnoDB
select @@default_storage_engine;
show variables like 'default_storage_engine';
修改默认存储引擎,在配置文件(mysql.cnf)中修改参数default_storage_engine。
2.查看当前mysql支持的存储引擎
show engines;
select * from information_schema.ENGINES;
show variables like 'have%';
3.建表时通过engine关键字设置表的存储引擎
create table test1(id bigint(20) not null auto_increment,primary key(id)) engine=Innodb/myisam default charset=gbk/utf8;
4.修改现有表的存储引擎
alter table test1 engine=Innodb/myisam;
show create table test1;

7.2 各种存储引擎的特性
常用存储引擎对比:
特点 MyISAM InnoDB MEMORY MERGE NDB
存储限制 有 64TB 有 没有 有
事务安全 -- 支持 -- -- --
锁机制 表锁 行锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 -- -- 支持 -- 支持
全文索引 支持 -- -- -- --
集群索引 -- 支持 -- -- --
数据缓存 -- 支持 支持 -- 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持 -- -- -- --
空间使用 低 高 N/A 低 低
内存使用 低 高 中等 低 高
批量插入速度 高 低 高 高 高
支持外键 -- 支持 -- -- --
7.2.1 MyISAM
MyISAM不支持事务、不支持外键,访问速度快,适用于不要求数据完整性或以Select和Insert为主的应用系统。
1.MyISAM表存储
MyISAM表在磁盘上存储为3个文件,文件名都与表名相同,扩展名分别为:
.frm(存储表定义)
.MYD(存储表数据,MyISAM data)
.MYI(存储表的索引数据,MyISAM Index)。
数据文件和索引文件可以放置在不同的目录,以平均分布IO提升性能;
在建表时通过DATA DIRECTORY和INDEX DIRECTORY指定数据文件和索引文件的存放路径(绝对路径)即可。
MySQL创建表默认在放在datadir路径下,当datadir空间不足时,可再建表时指定DATA DIRECTORY、INDEX DIRECTORY将新表数据存在其他磁盘例如:/data/lottery/;
注意:
INNODB 表要是独立表空间(innodb_file_per_table=1)。
目前只有5.6后才支持单表指定目录,且目录是mysql:mysql。
在Windows中将忽略DATA DIRECTORY和INDEX DIRECTORY选项
DATA/INDEX DIRECTORY不支持开启skip-symbolic-links参数;http://www.51edu.com/it/bckf/117508.html
普通表 DATA/INDEX DIRECTORY只在建表时指定,不可以alter table更改;
分区表可alter add PARTITIONS data DIRECTORY....
查看mysql数据文件存放路径
show global variables like "%datadir%";
'datadir', 'C:\\Program Files\\MySQL\\Data\\Data\\'

例子:
drop table test1;
create table test1(id bigint(20) not null auto_increment,primary key(id))
engine=myisam default charset=gbk INDEX DIRECTORY='C:\\';

2.MyISAM表损坏
因各种原因,MyISAM表可能会损坏。
可以用check table来检查MyISAM表是否损坏;
并用repair table来修复损坏的MyISAM表。
例子:
check table test1;
repair table test1;

3.MyISAM表存储格式类型
MyISAM表支持3种类型的存储格式:
静态(固定长度)表:默认的存储格式,列全部采用非变长类型,每条记录使用固定的长度,长度不足时使用空格补齐,返回时不带尾部空格。
优点:存储快速、容易缓存、容易故障恢复;
缺点:占用空间多,数据尾部空格丢失。
动态表:包含变长类型字段,记录长度不固定,优点占用空间小,缺点频繁的更新和删除记录将产生磁盘空间碎片,
且mysql不会自动合并磁盘空间碎片(Oracle通过pct free和pct used参数控制,当插入数据空间需求小于总的可用空间,且任何一块可用空间又不插入数据空间需求时,Oracle将自动合并空间碎片),
需要手动定期执行optimize table语句或者myisamchk -r 命令来改善性能,并且故障恢复比较困难。
压缩表:由myisampack工具创建,占用空间小,每条记录被单独压缩。

7.2.2 InnoDB
InnoDB提供了具有提交、回滚和崩溃恢复能力的事务安全,写效率较差,占用空间多。
1.自动增长列
drop table test1;
create table test1(id bigint(20) not null auto_increment,name varchar(10),primary key(id));
关键字auto_increment,从1开始每次增加1,每个表只能有一个自增列,一般作为主键列使用
插入数据时,正常情况下,不要插入自增列;
insert into test1 (name) values ('a'),('b');
也可以为了省略列名而给自增列插入0或null,自增列会根据当前值加1;
insert into test1 values (0,'c'),(null,'d');
如果给自增列手动插入比当前序号大的值,将会导致自增序号发生变更,并且后续记录将以插入值作为起始值;
insert into test1 values (6,'e'),(0,'e2'),(18,'f'),(null,'f2');
如果给自增列手动插入与现有值相同的值,将会导致报错。
insert into test1 values (1,'a2'),(6,'e2'); -- 1062
查看上次操作的序号,如果上次操作是批量操作时,则是第一条记录的插入序号:
select last_insert_id();
修改当前序号:
alter table test1 auto_increment=30; -- 注意:该起始序号值保存在内存中,重启服务会丢失。
show create table test1;
select last_insert_id();
insert into test1 (name) values ('a');
select * from test1;
注意:
对Innodb引擎表,自动增长列必须是索引列,如果是组合索引时,也必须是组合索引的第一列。
对Myisam引擎表,自动增长列也必须是索引列,如果是组合索引时,可以是组合索引的非第一列,
此时自动增长列按照组合索引的前几列排序后再自增(当前几列与已有记录不重时,自增列为1,否则自增列加1)。
例子:
drop table test1;
create table test1(id bigint(20) not null auto_increment,name varchar(10),index(name,id)) engine=Myisam;
insert into test1 (name) values ('a'),('b');
select * from test1;
insert into test1 (name) values ('a'),('b');
select * from test1;

2.外键约束
mysql中只有innodb引擎支持外键,
外键要求两张表有相同的列,并且该相同列在其中一张表为主键,称这张表为父表,
在另一张为非主键,称这张表为子表,外键用于表示父表到子表的一对多关系。
在mysql中为子表创建外键,系统会为外键列自动添加索引(oracle中不会自动添加外键索引)。
例子:
use scott;
create table dept1(
deptno int not null auto_increment,
dname varchar(100),
last_update timestamp,
primary key (deptno)
) engine=innodb default charset=utf8;
create table emp1(
empno int not null auto_increment,
ename varchar(100),
birth_date date,
deptno int,
primary key (empno),
key idx_fk_emp_deptno(deptno),
constraint fk_emp_deptno foreign key (deptno) references dept1(deptno) on delete restrict on update cascade
) engine=innodb default charset=utf8;
在创建外键约束时可以指定对父表进行更新、删除时子表的级联动作,分为以下4种:
restrict 限制:当子表相关记录存在时,不允许更新、删除父表;
cascade 级联:当更新、删除父表记录时,同时更新、删除子表记录;
set null 设置为空:当更新、删除父表记录时,将子表记录设置为null;
no action 不处理:当更新、删除父表记录时,不处理子表相应记录。
注意:父表的主键或主键列索引禁止被删除。alter table dept1 drop primary key; -- 1075 自增列必须定义为key(主键或索引)
关闭外键约束:set foreign_key_checks=0;
启用外键约束:set foreign_key_checks=1;

3.存储方式
InnoDB存储方式分两种:共享表空间存储、多表空间存储。
共享表空间存储:表结构保存在.frm文件中,数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的表空间中,可以是多个文件。
C:\Program Files\MySQL\Data\Data\ibdata1
多表空间存储: 表结构保存在.frm文件中,数据和索引保单独存在“表名.ibd”文件中(每个表一个.ibd文件)。对于分区表每个分区一个“表名+分区名.ibd”文件,不同分区的文件可以有不同的存储路径以均衡IO;
多表空间的数据文件没有大小限制,不需要设置初始大小,不需要设置文件的最大限制,扩展大小等参数。
C:\Program Files\MySQL\Data\Data\scott\dept.frm
C:\Program Files\MySQL\Data\Data\scott\dept.ibd
相关参数:
select @@innodb_data_home_dir; -- null
select @@innodb_data_file_path; -- ibdata1:12M:autoextend
select @@innodb_file_per_table; -- 1 启用多表空间(0 不启用多表空间)
一般情况下,共享表空间用于存储InnoDB内部数据字典和在线重做日志;在设置了参数innodb_file_per_table=1时,多表空间用于存储自定义的表。
改变参数innodb_file_per_table的设置(需要修改参数文件并重启服务),即切换共享表空间和多表空间存储方式,不会影响之前已经定义表的存储方式,只影响新建表的存储方式。
对于使用多表空间特性的表,直接复制.frm文件和.ibd文件进行备份后,不能恢复到其他mysql数据库(因为没有共享表空间的数据字典信息);
给本地mysql恢复时需要使用一下命令:
alter table tbl_name discard tablespace;
alter table tbl_name import tablespace;

7.2.3 MEMORY
MEMORY存储引擎用于在内存中建表。
MEMORY表只有一个表结构.frm文件,数据存放在内存中,并默认使用HASH索引,服务关闭数据丢失(数据不做持久化)。
例子:
-- 创建memory表,在C:\Program Files\MySQL\Data\Data\scott\下只有一个表结构文件emp_memory.frm
create table emp_memory engine=memory select * from emp;
-- 查询memory表,数据在内存中,所以访问速度快
select * from emp_memory;
-- 查看memory表状态
show table status like 'emp_memory';
-- 给memory表创建索引,索引类型支持hash和btree
create index men_hash using hash on emp_memory(empno);
create index men_btree using btree on emp_memory(ename);
-- 查看索引
show index from emp_memory;
-- 删除索引
drop index men_hash on emp_memory;
在启动MySQL服务时使用--init-file选项加载初始化文件,在该文件中创建memory表或者为memory表加载数据,即可解决重启服务memory表数据丢失的问题。
-- 释放memory表占用的内存,可以选择执行delete语句、truncate table语句、drop table语句
set sql_safe_updates=0;
delete from emp_memory; -- 删除数据
insert into emp_memory select * from emp; -- 加载数据
truncate table emp_memory; -- 截断数据(清空表)
drop table emp_memory; -- 删除表
memory表的数据量受参数max_heap_table_size的控制,默认16MB,可以根据需要调整。
-- 查看参数设置
select @@max_heap_table_size; -- '16777216'
-- 修改参数设置
set max_heap_table_size=33554432; -- 调整为32MB
-- 在创建memory表时指定行数max_rows
create table emp_memory engine=memory max_rows=10 select * from emp;
memory存储引擎适用于内容变化不频繁的代码表,或者统计操作的中间结果表。
memory表适用于快速查询,对memory表的增删改操作都不会被持久化。

7.2.4 MERGE
MERGE表是一组同构MyISAM表的逻辑组合,MERGE表不存储数据,数据在一组同构MyISAM表中的一个表中存储。
对MERGE表的增删改查操作,实际是对一组同构MyISAM表做增删改查操作。
MERGE表包含两个物理文件,.frm文件用来保存MERGE表定义,.MRG文件包含MERGE表和一组同构MyISAM表的映射关系,及插入数据时的依据。
create、alter、drop MERGE表,只会修改MERGE表的.frm文件文件,不会影响一组同构MyISAM表及数据。
可以自行修改.MRG文件,修改后需要使用flush tables命令来刷下表。
例子:
-- 创建两个同构的MyISAM表
create table sale_order_2017(id int not null auto_increment,sal_date date,sal_name varchar(100),primary key (id)) engine=myisam;
create table sale_order_2018(id int not null auto_increment,sal_date date,sal_name varchar(100),primary key (id)) engine=myisam;
-- 创建MERGE表
create table sale_order_merge(id int not null auto_increment,sal_date date,sal_name varchar(100),primary key (id)) engine=merge union=(sale_order_2017,sale_order_2018) insert_method=last;
MERGE表和一组同构MyISAM表的映射关系:union=(sale_order_2017,sale_order_2018)
插入数据时的依据insert_method分为first\last\no\缺省,first指插入到第一张表,last指插入到最后一张表,no和缺省指不允许插入操作。
-- 给两个同构的MyISAM表分别插入数据
insert into sale_order_2017 values (null,'2017-1-1','abc');
insert into sale_order_2017 values (null,'2017-2-1','abcd');
insert into sale_order_2018 values (null,'2018-1-1','abc');
insert into sale_order_2018 values (null,'2018-2-1','abcd');
-- 查看MERGE表
select * from sale_order_merge;
-- 给MERGE表插入数据
insert into sale_order_merge values (null,'2017-12-1','abcde');
-- 查看MERGE表和两个同构的MyISAM表
select * from sale_order_merge;
select * from sale_order_2017;
select * from sale_order_2018; -- 发现给meger表插入的数据由于指定了insert_method=last被插入到第二张表中,而不是根据日期值按范围分区。
鉴于MERGE表的插入只能指定first或last(而分区表则可以根据分区键自动选择分区),其功能性已被分区表所取代。

7.2.5 TokuDB
TokuDB是一个高写性能、支持事务处理的MySQL和MariaDB的第三方存储引擎,具有高扩展性、高压缩率、高效的写入性能、支持在线DDL操作。
特点:
使用Fractal树索引,保证高效的插入性能;
优秀的压缩特性,比InnoDB高近10倍;
Hot Schema Changes特性支持在线创建索引、添加删除属性列等DDL操作;
使用Bulk Loader快速加载大量数据;
提供主从延迟消除技术;
支持ACID和MVCC。
适用场景:
日志数据
历史数据
在线DDL较频繁的场景。

7.3 如何选择合适的存储引擎
MyISAM存储引擎适用于以读取操作和插入操作为主的系统(少量更新和删除操作),并且对事务的完整性、并发性要求不高,一般用于数据仓库类型的系统中。
MERGE存储引擎用于将一系列相同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们,用于突破MyISAM单表的大小限制,并分布在多个磁盘,以均衡IO(以逻辑分区整合MyISAM表),一般用于数据仓库类型的系统中。
InnoDB存储引擎用于事务处理型系统,支持外键,支持事务完整性,支持行锁。
MEMORY存储引擎用于在内存中建表,以提升访问速度。无法在内存中加载大量数据,不可恢复,不支持频繁更新。

7.4 小结

上一篇:day 43 数据库的密码的的更改如何操作


下一篇:ios语音输入崩溃