MySQL存储引擎

最新推荐文章于 2024-08-29 17:29:51 发布

亚特兰蒂斯453

最新推荐文章于 2024-08-29 17:29:51 发布

阅读量311

点赞数 5

文章标签： mysql android adb

本文链接：https://blog.csdn.net/weixin_67764171/article/details/140038750

版权

MySQL存储引擎

存储引擎概述

什么是存储引擎
- 数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。
  
  现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。
MySQL5. 7支持的存储引擎
- MySQL 支持多种类型的数据库引擎，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。在 MySQL 中，可以利用 SHOW ENGINES 语句来显示可用的数据库引擎和默认引擎。
- MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
- MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive 等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型

如何选择MySQL存储引擎

不同的存储引擎都有各自的特点，以适应不同的需求，如表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能

功能	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

选择MySQL存储引擎可以根据以下原则
- 如果要提交、回滚和恢复事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个很好的选择。
- 如果数据表主要用来插入和查询记录，则MyISAM引擎较高的处理效率
- 如果只是临时存储数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存的MEMORY引擎中，MySQL中使用引擎作为临时表，存放查询的中间结过。
- 如果只有INSERT和SELECT操作，可以选择Archive引擎，Archive存储引擎支持高并发发的插入操作，但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据，如记录日志信息可以使用Archive引擎
MySQL默认存储引擎
- InnoDB是系统默认引擎，支持可靠的事务处理
- 使用下面的语句可以秀发i数据库临时默认存储引擎
```
set default_storage_engine=< 存储引擎名 >
```

操作存储引擎

MyISAM存储引擎

MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有要求，以 SELECT、INSERT 为主的应用基本都可以使用这个引擎来创建表。

每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：
- frm(存储表定义)
- MYD(MYData，存储数据)
- MYI(MYIndex，存储索引)
MyISAM表还支持表还支持3种不同的存储格式
- 静态（固定长度）表
- 动态表
- 压缩表
InnoDB存储引擎

使用InnoDB存储引擎的选择：
- 更新密集的表：InnoDB 存储引擎特别适合处理多重并发的更新请求。
- 事务：InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
- 自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。
- 外键约束：MySQL 支持外键的存储引擎只有 InnoDB。
- 支持自动增加列 AUTO_INCREMENT 属性。
InnoDB的数据文件
- ibd：数据表的数据文件
- frm：数据包的元数据
- opt：存储的是mysql的一些配置信息，如编码、排序的信息等
关于MyISAM与InnoDB选择使用
- 如果应用程序一定要使用事务，毫无疑问要选择 InnoDB 引擎。但要注意，InnoDB 的行级锁是有条件的。在 where 条件没有使用主键时，照样会锁全表。比如 DELETE FROM mytable 这样的删除语句。
- 如果应用程序对查询性能要求较高，就要使用 MyISAM 了。MyISAM 索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于 InnoDB。压缩后的索引也能节约一些磁盘空间

修改默认的存储引擎的方法

通过alter table修改

#更改users表的存储引擎为myisam
mysql> alter table users engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status from auth where name='users'\G
*************************** 1. row ***************************
           Name: users
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 17169973579350015
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-06-28 11:03:07
    Update_time: 2024-06-28 11:03:07
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

通过配置文件修改

[root@bogon ~]# vim /etc/my.cnf
#添加以下内容
default-storage-engine=MyISAM
[root@bogon ~]# systemctl restart mysqld
[root@bogon ~]# mysql -uroot -ppwd123
mysql> use auth;
mysql> create table user2 (id int,name char(12));
Query OK, 0 rows affected (0.00 sec)

mysql> show table status from auth where name='user2'\G
*************************** 1. row ***************************
           Name: user2
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 4785074604081151
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-06-28 11:05:56
    Update_time: 2024-06-28 11:05:56
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

在创建表时指定存储引擎

mysql> use auth;
mysql> create table user3 (id int,name char(12)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> show table status from auth where name='user3'\G
*************************** 1. row ***************************
           Name: user3
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2024-06-28 11:07:06
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

临时修改默认存储引擎

SET default_storage_engine=< 存储引擎名 >

MyISAM和InnoDB实例比较

#创建两张表分别以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
#插入一千万数据，来比较两个存储引擎的存储效率
#设置sql语句结束符
mysql> delimiter $
#创建两个存储过程

#第一个
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 ;
#执行存储过程
mysql> call insertm;
mysql> call insertm();
Query OK, 0 rows affected (2 min 13.86 sec)
mysql> call inserti;
Query OK, 0 rows affected (22 min 53.47 sec)
#查询数据总数

对比有无索引情况下查询速度

无索引的列

#查询myisam为引擎的表
myql>select * from tm where name>"wy100" and name <"wy10000000";
#查询innodb为引擎的表
myql> select * from ti where name>"wy100" and name <"wy10000000";

结论：在无索引的情况下misam引擎查询有优势

有索引的列

#查询myisam为引擎的表
myql>select * from tm where id>10 and id<999999;
999988 rows in set (1.45 sec)
#查询myisam为引擎的表
myql>select * from ti where id>10 and id<999999;
999988 rows in set (0.34 sec)

结论：在有索引的情况下innoodb引擎查询有优势