一.Mysql存储引擎
1.什么是存储引擎
数据库存储引擎是数据库底层软件组件,数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能,使用不同的存储引擎还可以获得特定的功能。
现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
2.Mysql 5.7 支持哪些引擎,有什么区别?
(1)在 MySQL 中,可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
3.如何选择Mysql存储引擎
innodb:64T 支持事务 支持数据缓存 支持外键
mysiam:256T 支持全文索引 不支持事务
memory(内存):存储大小跟内存有关 b树索引 hash(哈希)索引
4.可以根据以下原则选择Mysql的存储引擎
- 如果要提供提交、回滚和恢复的事务安全(ACID 兼容)能力,并要求实现并发控制,InnoDB 是一个很好的选择。
- 如果数据表主要用来插入和查询记录,则 MyISAM 引擎提供较高的处理效率。
- 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存的 MEMORY 引擎中,MySQL 中使用该引擎作为临时表,存放查询的中间结果。
- 如果只有 INSERT 和 SELECT 操作,可以选择Archive 引擎,Archive 存储引擎支持高并发的插入操作,但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据,如记录日志信息可以使用 Archive 引擎。
5.Mysql默认存储引擎
InnoDB 是系统的默认引擎,支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎
SET default_storage_engine=< 存储引擎名 >
二.操作存储引擎
1.查看mysql支持的存储引擎
show engines;
2.MyISAM存储引擎
(1)MYD(MYData,存储数据)
(2)MYI(MYIndex,存储索引)
MyISAM 表还支持 3 种不同的存储格式:
(1)静态(固定长度)表
(2)动态表
(3)压缩表
3.INnoDB存储引擎
.frm:元数据 innodb
.idb:核心数据 索引和数据放在一起,并没有单独索引文件
.opt:存储的是mysql的一些配置信息,如编码、排序的信息等
4.MyISAM 与 InnoDB 选择使用
(1)如果应用程序一定要使用事务,毫无疑问要选择 InnoDB 引擎。但要注意,InnoDB
的行级锁是有条件的。在 where 条件没有使用主键时,照样会锁全表。比如 DELETE FROM
mytable 这样的删除语句。
(2)如果应用程序对查询性能要求较高,就要使用 MyISAM 了。MyISAM 索引和数据
是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于 InnoDB。
压缩后的索引也能节约一些磁盘空间
5.修改默认的存储引擎
1)创建表,并查看默认用的存储引擎
mysql> create database auth;
mysql> use auth
mysql> CREATE TABLE t1 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t1'\G
(2)通过 alter table 修改
mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G
(3)通过配置文件修改
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#添加下面语句
default-storage-engine=MyISAM
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -ppwd123
mysql> use auth
mysql> CREATE TABLE t2 (user_name CHAR(16), user_passwd CHAR(48));
mysql> show table status from auth where name='t2'\G
注意:
通过配置文件修改的方法,对以前的老表不会有影响,只会影响以后新创建的表,并且在创建表时没有指定存储引擎的情况。
(4)通过 create table 创建表时指定存储引擎
mysql> use auth
mysql> create table t3 (id int(10),name char(20)) engine=INNODB;
mysql> show table status from auth where name='t3'\G
(5)临时修改默认的存储引擎
SET default_storage_engine=< 存储引擎名 >
三.MyISAM和InnoDB实例比较
1:创建两张表分别以MyIsam和InnoDB作为存储引擎。
create database test;
use test;
create table tm(id int(20) primary key auto_increment,name char(30)) engine=myisam;
create table ti(id int(20) primary key auto_increment,name char(30)) engine=innodb;
mysql> show create table tm\G
mysql> show create table ti\G
两张表内容是一致的但是存储引擎不一样,下面我们从插入数据开始进行测试比较。
2:插入一千万数据,来比较两个存储引擎的存储效率
这里当然不能手动的插入,创建一个存储过程插入一百万的数据。
(1)设置sql语句结束符
mysql> delimiter $
备注:
delimiter $语句是设置sql语句的结束符为“$”
(2)创建两个存储过程
mysql> create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$
mysql> create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti(name) values(concat("wy",@i));
set @i=@i+1;
end while;
end
$
mysql> delimiter ;
备注:
delimiter ;语句是设置sql语句的结束符为“;”