# 1. 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的,所以存储引擎也可以被称为表引擎。 Mysql在V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDB

  • 查看表的存储引擎
-- 查询当前表创建时使用的存储引擎
mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `age` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

ERROR: 
No query specified
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
  • 查看支持那些存储引擎
-- mysql支持的所有存储引擎
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

-- mysql 默认使用的存储引擎

mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  • 指定存储引擎创建表
-- $table_name: 表名
-- $engine_name: 需要指定的存储引擎名称
create table $table_name (
    ...
)engine = $engine_name

1
2
3
4
5
6

# 2. InnoDB 存储引擎

InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.1 之后,InnoDB 是默认的 MySQL 引擎。

特点:
  DML 操作遵循 ACID 模型,支持事务
  行级锁,提高并发访问性能
  支持外键约束,保证数据的完整性和正确性

文件:
  $filename.ibd: $filename表示表名, innodb引擎的每张表都会对应这样一个表空间文件,存储该表的表结构、数据和索引。

参数:
  innodb_file_per_table,决定多张表共享一个表空间还是每张表对应一个表空间
1
2
3
4
5
6
7
8
9
10
11
12
  • 查看环境变量
-- 查看 Mysql 变量
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)
msyql> 

-- 从innodb存储的文件中提取出文件内容
idb2sdi $filename

1
2
3
4
5
6
7
8
9
10
11
12
13
  • innodb 逻辑存储结构 image

# 3. myisam 存储引擎

MyISAM 是 MySQL 早期的默认存储引擎。


特点:
  不支持事务,不支持外键
  支持表锁,不支持行锁
  访问速度快

文件:
  $filename.sdi: 存储表结构信息
  $filename.MYD: 存储数据
  $filename.MYI: 存储索引
1
2
3
4
5
6
7
8
9
10
11
12

# 4. Memory存储引擎

Memory 引擎的表数据是存储在内存中的,受硬件问题、断电问题的影响,只能将这些表作为临时表或缓存使用。


特点:
  存放在内存中,速度快
  hash索引(默认)
文件:
  $filename.sdi: 存储表结构信息
1
2
3
4
5
6
7
8

# 5. 存储引擎特点

特点 InnoDB MyISAM Memory
存储限制 64TB 4GB 内存
事务安全 支持 - -
锁机制 行锁 表锁 表锁
B+tree索引 支持 支持 支持
Hash索引 - - 支持
全文索引 支持(5.6版本之后) 支持 -
空间使用 高 低 N/A
内存使用 高 低 中等
批量插入速度 低 高 高
支持外键 支持 - -

# 6. 存储引擎的选择

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。

1) InnoDB: 如果应用对事物的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,则 InnoDB 是比较合适的选择

2) MyISAM: 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,那这个存储引擎是非常合适的。

3) Memory: 将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性

4) 电商中的足迹和评论适合使用 MyISAM 引擎,缓存适合使用 Memory 引擎
1
2
3
4
5
6
7
Last Updated: 5/5/2023, 2:33:10 AM