[mysql基础文档]-19-如何设置储存引擎

引言

MySQL数据库目前有两种常见储存引擎,Myisam和Innodb,如果建表时没有显示声明,默认使用Innodb,不同的数据库引擎在数据储存索引以及管理上各有优劣。

文章目录

0×1.如何设置表储存引擎

建表时指定表储存引擎设置:

					--创建表t30和t31,分别使用Innodb以及Myisam引擎,指定储存引擎,只需要在建表语法最后添加engine=[引擎类型]
					mysql> create table t30(id int not null default 0) engine=Innodb;
					mysql> create table t31(id int not null default 0) engine=Myisam;

					--如果不知道表使用的是哪个储存引擎,可以show table status查询指定的表,对于status的输出,暂时只需要了解下面几行即可
					mysql> show table status where name='t30' or name='t31' \G
					************ 1. row ************
					           Name: t30  <--表名
					         Engine: InnoDB <--储存引擎

					    Create_time: 2015-10-10 11:28:14 <--表创建时间
					    Update_time: 2015-10-10 11:28:31 <--表更新时间

					      Collation: latin1_swedish_ci  <--使用的字符编码

					        Comment:   <--是否为视图,不是视图为空
					************ 2. row ************
					           Name: t31
					         Engine: MyISAM <--t31表使用的是MyISAM引擎

					    Create_time: 2015-10-10 11:28:32
					    Update_time: 2015-10-10 11:28:32

					      Collation: latin1_swedish_ci

					        Comment: 
					

0×2.储存引擎特征简析

ubuntu中默认的数据库文件存放在"/var/lib/mysql/"(需要root权限才能进入),本系列文章创建的数据名是"qingsword_com",所以表文件存放在此目录的"qingsword_com"文件下;

如果是windows环境,数据库文件默认目录是安装目录下的data文件中,myisam引擎的表数据存放在次级目录mysql中的数据库名称对应的文件下;

进入/var/lib/mysql/qingsword_com/文件夹后,可以看到很多表数据文件,对应了不同的储存引擎;

● Myisam储存引擎

当创建一张Myisam储存引擎的表时,在数据库目录下会多出三个文件,后缀分别是".frm",".MYD",".MYI":

.frm储存表结构framework(列名称,数据类型,属性等)
.MYD储存表数据Mysql Data(每条记录的数据都储存在这里面)
.MYI索引文件mysql index,储存了索引信息

一张完整的Myisam引擎的表一般都包含上面三个后缀的文件,比如上面创建的t31,就生成了三个文件"t31.frm","t31.MYD","t31.MYI"。

● Innodb储存引擎

Innodb是MySQL5之后默认的储存引擎,Innodb储存方式是由"innodb_file_per_table"参数决定的,可以在my.cnf中[mysqld]下设置(如果没有可以追加);

当此innodb_file_per_table=0时,创建的新表只在对应数据库文件夹中生成一个文件,后缀为".frm",所有表数据共享同一个文件"ibdata1",默认位置"/var/lib/mysql/ibdata1";
当此innodb_file_per_table=1时,创建新表时生成两个单独文件,后缀为".ibd"和".frm",其中frm文件同Myisam引擎,储存的是表结构信息,idb文件储存数据、索引和插入缓冲,而撤销(undo)信息,系统事务信息,二次写缓冲等还是存放在ibdata1文件中,比如上面创建的t30,就生成两个文件"t30.idb","t30.frm",说明默认情况下使用了这种配置;

通过测试发现,在ubuntu中MySQL5.6.25默认使用了Innodb并且innodb_file_per_table=1(虽然my.cnf文件中并没有配置此参数);

下面实例演示如何查看当前Innodb所使用的储存方式,以及如何通过修改innodb_file_per_table参数改变Innodb储存方式:

					--通过查看innodb_file_per_table变量,ON代表开启的独立文件储存(innodb_file_per_table=1)
					mysql> show variables like 'innodb_file_per_table';
					+-----------------------+-------+
					| Variable_name         | Value |
					+-----------------------+-------+
					| innodb_file_per_table | ON    |
					+-----------------------+-------+

					--修改my.cnf文件,使用共享储存,这里有几点要说明一下,一部分ubuntu系统MySQL主配置是存放在/etc/mysql/my.cnf文件中的,经过测试,一些系统默认安装的MySQL主配置实际存放位置是在/etc/mysql/mysql.conf.d/mysqld.cnf文件中,而/etc/mysql/my.cnf文件只有少量的信息,本文所使用的就是后者,使用管理员权限运行下面的命令修改配置文件的内容
					www@qingsword.com:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf 
					--找到[mysqld]段,在其下面添加一行如下
					[mysqld]
					innodb_file_per_table=0

					--每次修改MySQL主配置文件都需要重启MySQL服务才能使配置生效
					www@qingsword.com:~# service mysql restart

					--已经开启了共享文件储存,此时再create table创建出来的表就只会在数据库对应目录下生成后缀是".frm"的单个文件了,大家可以自己测试下,表数据全部共享储存在"/usr/lib/mysql/ibdata1"文件中
					mysql> show variables like 'innodb_file_per_table';
					+-----------------------+-------+
					| Variable_name         | Value |
					+-----------------------+-------+
					| innodb_file_per_table | OFF   |
					+-----------------------+-------+
					

● 两种储存引擎性能比较

特点 Myisam InnoDB
批量插入速度
事务安全 暂不支持 支持
全文索引 支持 暂不支持
锁机制 表锁 行锁
储存限制 64TB
B树索引 支持 支持
哈希索引 暂不支持 支持
集群索引 暂不支持 支持
数据缓存 暂不支持 支持
索引缓存 支持 支持
数据压缩 支持 暂不支持
空间使用
内存使用
外键支持 暂不支持 支持

移植性:myisam储存在数据库的移动方面相对简单,只要将数据库目录拷贝,就是整个数据库的备份,Innodb相对myisam移动没有那么容易,因为所有表的数据都储存在一起(就算innodb_file_per_table=1,单独生成文件存放部分数据,仍有一部分数据是存放在共享的ibdata1文件中的)

安全:InnoDB更加安全,拥有完善的日志功能,能够根据日志恢复数据,Myisam则没有,InnoDB支持事务安全,但不支持全文索引,InnoDB的锁机制能够实现行锁,而Myisam只能实现表锁。