存储引擎概述
什么是存储引擎
是数据库底层软件组件，数据库管理系统使用数据索引进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧】锁定水平等功能，使用不同的存储引擎可以获得特定的功能
MySQL5.7支持的存储引擎
InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。使用show engines语句可以查看系统支持的引擎类型
| 功能 | MyISAM | MEMORY | InnoDB | Archive |
|---|---|---|---|---|
| 存储限制 | 256TB | RAM | 64TB | None |
| 支持事务 | no | no | yes | no |
| 支持全文索引 | yes | no | no | no |
| 支持树索引 | yes | yes | yes | no |
| 支持哈希索引 | no | yes | no | no |
| 支持数据缓存 | no | N/A | yes | no |
| 支持外键 | no | no | yes | no |
InnoDB 适用于需要提交、回滚和恢复的事务安全（ACID）能力，并要求实现并发控制，是 MySQL的默认存储引擎
MyISAM能够为数据表的插入和查询记录提供较高的处理效率
如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的 MEMORY 引擎中，MySQL 中使用该引擎作为临时表，存放查询的中间结果。
如果只有 INSERT 和 SELECT 操作，可以选择Archive 引擎，Archive 存储引擎支持高并发的插入操作，但是本身并不是事务安全的。Archive 存储引擎非常适合存储归档数据
操作存储引擎
查看mysql支持的存储引擎
show engines；
MyISAM 存储引擎
MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。
每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：
（1）frm(存储表定义)
（2）MYD(MYData，存储数据)
（3）MYI(MYIndex，存储索引)
MyISAM 表还支持 3 种不同的存储格式：
（1）静态(固定长度)表
（2）动态表
（3）压缩表
InnoDB存储引擎
是MySQL的默认存储引擎，在下场景中使 用 InnoDB 存储引擎是最理想的选择：
（1）更新密集的表：InnoDB 存储引擎特别适合处理多重并发的更新请求。
（2）事务：InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
（3）自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。
（4）外键约束：MySQL 支持外键的存储引擎只有 InnoDB。
（5）支持自动增加列 AUTO_INCREMENT 属性。
Innodb的数据文件：
ibd：数据表的数据文件
frm：数据表的元数据
opt：存储的是mysql的一些配置信息，如编码、排序的信息等
修改默认的存储引擎
创建表，并查看默认用的存储引擎
创建表，并查看默认用的存储引擎
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
通过 alter table 修改
mysql> alter table t1 engine=MyISAM;
mysql> show table status from auth where name='t1'\G
通过 create table 创建表时指定存储引擎
[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
临时修改默认存储引擎
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.插入一千万数据，来比较两个存储引擎的存储效率
创建两个存储过程
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 ;
利用存储过程向两个表添加数据
插入（一千万条）MyIsam存储引擎的表中的时间如下：
mysql> call insertm;
Query OK, 0 rows affected (1 min 49.74 sec)
插入（一千万条）InnoDB存储引擎的表中的时间如下：
mysql> call inserti;
Query OK, 0 rows affected (13 min 32.96 sec)
根据结果得出：MyIsam存储引擎在写入方面有优势
3.查询数据总数目
InnoDB的SQL语句的分析：
mysql> desc select count(*) from ti\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ti
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 9732352
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
下面是MyIsam（他的数据存储在其他的表中所以这里是没有影响行数的）的SQL语句的分析：
mysql> desc select count(*) from tm\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)
4.查询某一范围的数据
没有索引的列
mysql> select * from tm where name>"zhangsan100" and name<"zhangsan10000000";
+---------+-----------------+
| id | name |
+---------+-----------------+
| 1000 | zhangsan1000 |
| 10000 | zhangsan10000 |
| 100000 | zhangsan100000 |
| 1000000 | zhangsan1000000 |
+---------+-----------------+
4 rows in set (1.69 sec)
mysql> select * from ti where name>"zhangsan100" and name<"zhangsan10000000";
+---------+-----------------+
| id | name |
+---------+-----------------+
| 1000 | zhangsan1000 |
| 10000 | zhangsan10000 |
| 100000 | zhangsan100000 |
| 1000000 | zhangsan1000000 |
+---------+-----------------+
4 rows in set (2.57 sec)
mysql> select * from ti where name="zhangsan9999999";
+---------+-----------------+
| id | name |
+---------+-----------------+
| 9999999 | zhangsan9999999 |
+---------+-----------------+
1 row in set (2.15 sec)
mysql> select * from tm where name="zhangsan9999999";
+---------+-----------------+
| id | name |
+---------+-----------------+
| 9999999 | zhangsan9999999 |
+---------+-----------------+
1 row in set (0.99 sec)
无索引查询MyIsam有优势
有索引的列
mysql> select * from tm where id>10 and id<999999;
999988 rows in set (1.96 sec)
mysql> select * from ti where id>10 and id<999999;
999988 rows in set (0.52 sec)
有索引查询InnoDB有优势
9706
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
