转载

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

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

文章目录

[1].如何设置表储存引擎
[2].储存引擎特征简析

[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:

[2].储存引擎特征简析

ubuntu中默认的数据库文件存放在“/usr/lib/mysql/”，本系列文章创建的数据名是“qingsword_com”，所以表文件存放在此目录的“qingsword_com”文件下；（如果你是windows环境，数据库文件默认目录是安装目录下的data文件中，myisam引擎的表数据存放在次级目录mysql中的数据库名称对应的文件下）

进入/usr/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储存引擎

MySQL5之后默认的储存引擎，Innodb储存方式是由“innodb_file_per_table”参数决定的，可以在my.cnf中[mysqld]下设置（如果没有可以追加）；

当此innodb_file_per_table=0时，创建的新表只在对应数据库文件夹中生成一个文件，后缀为“.frm”，所有表数据共享同一个文件“ibdata1”，默认位置“/usr/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储存方式：

//本文直接在ubuntu下安装的mysql，通过查看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文件中的，本文所使用的就是后者,使用管理员权限运行下面的命令。 www@qingsword.com:~# vi /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只能实现表锁。

[**] 注:如文中未特别声明转载请注明出自:QingSword.COM

正文到此结束