目录
存储引擎
什么是存储引擎
数据库存储引擎是数据底层软件组件，数据库管理系统使用数据引擎进行创建，查询，更新和删除数据操作。不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。MySQL的核心就是存储引擎。
MySQK5.7支持特定的存储引擎
MySQL支持多种类型的数据引擎，可分别根据多个引擎的功能和特性为不同的数据库处理任务提供不同的适应性和灵活性。在MySQL中，可以利用show engines语句来显示可用的数据库引擎和默认引擎。
MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中，不需要在整个服务器中使用不同的一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。
MySQL5.7支持的存储引擎有InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、BLACKHOLE等。可以使用SHOW ENGINES语句查看系统所支持的引擎类型
如何选择MySQL存储引擎
不同索引有不同的需求
| 功能 | innodb | myisam | memory | archive |
| 存储限制 | 64TB | 256TB | RAM（存储） | None |
| 支持事务 | Yes | No | No | No |
| 支持全文索引 | No | Yes | No | No |
| 支持树索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | No | Yes | No |
| 支持数据缓存 | Yes | No | N/A | No |
| 支持外键 | Yes | No | No | No |
InnoDB事务数据库的首选引擎，支持事务安全表，支持行锁定和外键。InnoDB作为默认存储引擎
MyISAM是基于ISAM的存储引擎，并对其进行扩展，是在Web、数据仓储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务
MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他数据提供快速访问。
查看存储引擎
mysql> show engines;
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# cd data/
[root@localhost data]# cd mysql
[root@localhost mysql]# ls
myisam:
frm:表的结构信息
myd:数据部分
myi:索引
创建表
mysql> create database auth;
mysql> use auth
mysql> create table users(id int(10),name char(20));
[root@localhost data]# cd auth
[root@localhost auth]# ls
db.opt users.frm users.ibd
innoDB
ibd：数据
frm：数据表的元meta数据
opt：mysql的配置信息
修改存储引擎
根据不同表修改不同的存储引擎，以表为单位存储数据，不支持1事务处理，提供了较高的插入和查询速度，运行后users表的存储引擎回会从当前的引擎更改为MyISAM.
mysql> alter table users engine=myisam;
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: 18295873486192639
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2024-07-29 23:16:06
Update_time: 2024-07-29 23:16:06
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)创建表时指定存储引擎
写存储引擎名字的时候不限大小写
mysql> use auth
mysql> create table t1 (id int(10),name char(20)) engine=myisam;
临时修改存储引擎
确保新建的表在没有明确指定存储引擎的情况下，默认使用 MyISAM 存储引擎。，但退出后自动恢复默认的存储引擎
mysql> set default_storage_engine=myisam;
mysql> create table t3 (id int(10),name char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> show table status from auth where name='t3'\G
*************************** 1. row ***************************
Name: t3
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 18295873486192639
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2024-07-29 23:34:12
Update_time: 2024-07-29 23:34:12
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
退出
mysql> use auth
mysql> create table t6 (id int(10),name char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> show table status from auth where name='t6'\G
*************************** 1. row ***************************
Name: t6
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-07-29 23:36:17
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
比较myisam和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;
mysql> show create table tm\G
*************************** 1. row ***************************
Table: tm
Create Table: CREATE TABLE `tm` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table ti\G
*************************** 1. row ***************************
Table: ti
Create Table: CREATE TABLE `ti` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
设置sql语句结束语
$语句是设置sql语句的结束符为“$”
mysql> delimiter $
创建俩个存储过程
create procedure insertm()
begin
set @i=1;
while @i<=10000000
do
insert into tm(name)values(concat("zhangsan",@i));
set @i=@i+1;
end while;
end
$
#SET @i = 1; 初始化一个变量 @i，初始值为 1。
#WHILE @i <= 10000000 DO 是一个循环语句，当 @i 小于等于 10000000 时执行循环体。
#INSERT INTO tm(name) VALUES (CONCAT("zhangsan", @i)); 是一个插入语句，将格式为 "zhangsan" 加上当前 @i 的值插入到 tm 表的 name 字段中。
#SET @i = @i + 1; 增加 @i 的值，使得循环可以逐步递增。
create procedure inserti()
begin
set @i=1;
while @i<=10000000
do
insert into ti(name)values(concat("zhangsan",@i));
set @i=@i+1;
end while;
end
$向表 tm 中插入大量数据，插入的数据格式为 "zhangsan1", "zhangsan2", ..., "zhangsan10000000"。
利用存储过程向俩个表添加数据
mysql> call insertm;
Query OK, 0 rows affected (1 min 23.34 sec)
mysql> call inserti;
Query OK, 0 rows affected (20 min 58.71 sec)
写入：myisam更快
读（无索引）：myisam更快
读（带索引）：innodb 更快
查询某一范围的数据
没有索引的列
mysql> select * from tm where name>"zhangsan100" and name<"zhangsan1000000";
+--------+----------------+
| id | name |
+--------+----------------+
| 1000 | zhangsan1000 |
| 10000 | zhangsan10000 |
| 100000 | zhangsan100000 |
+--------+----------------+
3 rows in set (1.12 sec)
mysql> select * from ti where name>"zhangsan100" and name<"zhangsan1000000";
+--------+----------------+
| id | name |
+--------+----------------+
| 1000 | zhangsan1000 |
| 10000 | zhangsan10000 |
| 100000 | zhangsan100000 |
+--------+----------------+
3 rows in set (1.78 sec)
mysql> select * from ti where id>10 and id<10000000;
9999989 rows in set (3.02 sec)
mysql> select * from tm where id>10 and id<10000000;
9999989 rows in set (1.98 sec)
总结
- 写入速度：MylSAM更快
- 读取速度（无索引）：MyISAM更快
- 读取速度（有索引）：InnoDB更快
750
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
