目录
存储引擎
概念
数据库存储引擎是数据库底层组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。
不同的存储引擎提供不同的存储机制、索引功能、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
存储引擎是针对数据表的，在同一个数据库中，不同的表可以使用不同的存储引擎
类型
| 功能 | MylSAM | MEMORY | InnoDB |
| 存储限制 | 256TB | 内存 | 64TB |
| 支持事务 | No | No | Yes |
| 支持全文索引 | Yes | No | No |
| 支持树索引 | Yes | Yes | Yes |
| 支持哈希索引 | No | Yes | No |
| 支持数据缓存 | No | N/A | Yes |
| 支持外键 | No | No | Yes |
InnoDB：行级锁定（select * from tableName where name = 'tom' for update;）锁定该表的更新功能
MyISAM：表级锁定
示例
数据库文件
MylSAM
cd到mysql存放数据的data目录下的mysql目录
ls一下可以看到很多.frm、.MYD、MYI文件，这些文件都是MyISAM存储引擎生成的
[root@localhost data]# cd /usr/local/mysql/data/mysql
[root@localhost mysql]# ls
user.frm
user.MYD
user.MYI-
.frm
- 数据表结构，比如表有多少列，每个列的名字
-
.MYD
- 存放表中数据
-
.MYI
- 专门存放索引的信息的文件
InnoDB
先登录进数据库，然后创建一个数据库和表，再退出数据库
[root@localhost mysql]# mysql -uroot -p
mysql> create database auth;
mysql> use auth
mysql> create table users(id int(10), name char(20));
mysql> quit退出后，在当前位置cd到上一级目录下，也就是/usr/local/mysql/data目录下
ls一下，可以看到一个和刚刚创建的数据库相同名称的目录，进入该目录
就可以看到，在auth的库中，一共有三个文件，这些文件都是由InnoDB生成的，这里每个表都对应了两个文件，分别是：.frm和.ibd文件
-
.frm
- 存放数据表的元数据
-
.ibd
- 和MyISAM的.MYD文件类似，存放数据表中的数据
-
.opt
- 存放MySQL的配置信息
[root@localhost mysql]# cd ..
[root@localhost data]# ls
auth
[root@localhost data]# cd auth
[root@localhost auth]# ls
db.opt users.frm users.ibd此时我们再次登录进数据库然后在auth库中创建第二个表
[root@localhost auth]# mysql -uroot -p
mysql> use auth
mysql> create table users001(id int(10), name char(20));
mysql> quit再退出数据库，ls一下，可以看到又多了两个文件，而不是像第一次创建表时，多了三个文件
[root@localhost auth]# ls
db.opt users001.frm users001.ibd users.frm users.ibd
其实opt文件存储的是该数据库的配置信息，编码规则等信息，该库中的所有表共用该.opt文件
查看指定表的存储引擎类型
登录进MySQL，使用下方代码块内的show命令查看指定库指定表的状态信息
- Engine: InnoDB：该表使用的存储引擎
- Create_time：创建该表的时间
- Collation: utf8_general_ci：校对字符集
[root@localhost auth]# mysql -uroot -p
mysql> show table status from auth where name = 'users'\G;
*************************** 1. row ***************************
Name: users
Engine: InnoDB
# ...
Create_time: 2024-07-29 09:21:14
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
# 省略其他内容查看系统支持的存储引擎
mysql> show engines;修改存储引擎
通过配置文件指定存储引擎
在[mysqld]单元内，修改存储引擎类型
[mysqld]
default-storage-engine=MYISAM使用alter修改
进入要操作的数据库，通过alter命令修改表结构，来修改users表的存储引擎
修改完后，再使用show命令查询，可以看到存储引擎已经变成MyISAM
mysql> use auth
mysql> alter table users engine=myisam;
mysql> show table status from auth where name = 'users'\G;
*************************** 1. row ***************************
Name: users
Engine: MyISAM
# 省略其他内容
mysql> quit此时再退出数据库，ls查看当前目录下的文件，可以看到由于修改了存储引擎，又多出了两个文件
假如在一个表中设有事务相关的指令，如果此时把存储引擎从InnoDB改为MyISAM，由于MyISAM不支持事务，所以可能会导致该表出现错误无法使用
[root@localhost auth]# ls
db.opt users001.frm users001.ibd users.frm users.MYD users.MYI创建表的同时指定存储引擎
登录MySQL，进入auth数据库，创建表时在末尾使用engine选项指定存储引擎
创建完后，再使用show命令查看该表的状态信息，可以看到存储引擎是MyISAM
[root@localhost auth]# mysql -uroot -p
mysql> use auth
mysql> create table test (id int(10), name char(20)) engine=myisam;
mysql> show table status from auth where name = 'test'\G;
*************************** 1. row ***************************
Name: test
Engine: MyISAM
# ...使用set命令临时指定存储引擎
使用set命令临时指定默认存储引擎为MyISAM
新创建的表将默认使用 MyISAM 存储引擎，但在新的会话中或者重新启动 MySQL 后，这个设置会被重置
mysql> set default_storage_engine=myisam;
mysql> show table status from auth where name = 't2'\G;
*************************** 1. row ***************************
Name: t2
Engine: MyISAM比较MyISAM和InnoDB
写入速度比较
比较处理数据的速度
创建用于比较两个存储引擎性能的数据库，然后进入该数据库
分别创建两个表，tm表示测试myisam的表，ti表示测试innodb的表
mysql> create database test;
mysql> use test
mysql> create table tm(id int(20) primary key auto_increment,name char(30)) engine=myisam;
mysql> create table ti(id int(20) primary key auto_increment,name char(30)) engine=innodb;插入数据
这里我们使用存储过程插入一千万行数据，来比较两个存储引擎的存储效率
因为要在存储过程中写SQL语句，需要加分号（；）结尾，但是在编写的时候加分号结尾就直接退出了整个存储过程
所以需要先修改SQL语句的结束标识，才能正常编写存储过程的命令
mysql> delimiter $tm表的存储过程
修改完结束标识符后，直接在mysql中输入以下语句
向表tm中插入从tom1到 tom10000000 这一千万条数据
create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm (name) values (concat("tom", @i));
set @i=@i+1;
end while;
end
$ti表的存储过程
向表ti中插入从tom1到 tom10000000 这一千万条数据
create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti (name) values (concat("jerry", @i));
set @i=@i+1;
end while;
end
$测试
先把结束标识符改回分号（；）以便进行下一步操作
使用 call 命令调用存储过程，然后等待执行完毕
mysql> delimiter ;
mysql> call insertm;
# 等待执行完毕...
Query OK, 0 rows affected (2 min 30.66 sec) # 用时2分30.66秒
mysql> call inserti;
# 等待执行完毕...
Query OK, 0 rows affected (22 min 9.11 sec) # 用时22分9.11秒由此可见，MyISAM的写入速度比InnoDB更快
查询数据速度比较
没有索引的列
mysql> select * from tm where name > "tom100" and name < "tom10000000";
+---------+------------+
| id | name |
+---------+------------+
| 1000 | tom1000 |
| 10000 | tom10000 |
| 100000 | tom100000 |
| 1000000 | tom1000000 |
+---------+------------+
4 rows in set (1.02 sec)
mysql> select * from ti where name > "jerry100" and name < "jerry10000000";
+---------+--------------+
| id | name |
+---------+--------------+
| 1000 | jerry1000 |
| 10000 | jerry10000 |
| 100000 | jerry100000 |
| 1000000 | jerry1000000 |
+---------+--------------+
4 rows in set (2.12 sec)有索引的列
mysql> select * from tm where id > 10 and id < 999999;
999988 rows in set (1.42 sec)
mysql> select * from ti where id > 10 and id < 999999;
999988 rows in set (0.40 sec)总结
- 写入速度：MyISAM更快
- 读取速度（无索引）：MyISAM更快
- 读取速度（有索引）：InnoDB更快
232
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
