查看mysql的存储引擎 mysql查看存储引擎指令

转载

数据狂徒 2023-08-13 22:24:36

文章标签 查看mysql的存储引擎 mysql 数据库 database java 文章分类 MySQL 数据库

文章目录

1.什么是存储引擎
2.查看存储引擎

2.1查看默认的存储引擎
2.2临时修改默认引擎
2.3直接指定存储引擎

常见引擎介绍

InnoDB引擎
MyISAM引擎
MyISAM和InnoDB的对比(一个表格搞定)

其他引擎

Archive引擎：用于数据归档
CSV引擎：存储数据时，逗号分割
Memory引擎：置于内存中的表

1.什么是存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统DBMS使用存储引擎对数据进行操作，不同的存储引擎提供不同的储存机制、索引技巧、锁定水平等功能。
Mysql中的存储引擎，就是指表的类型，在以前，储存引擎又被称为表处理器，后来改名为存储引擎。
功能是接受上层传下来的指令，然后对表中的数据进行提取和修改。

2.查看存储引擎

在mysql客户端中，可以查看具体的存储引擎有哪些，是否支持事务等
使用命令：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.01 sec)

2.1查看默认的存储引擎

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, 1 warning (0.01 sec)

可以看到 我们默认的存储引擎是InnoDB

我们随意打开数据库中的一张表，可以看到默认的存储引擎是InnoDB:

mysql> show create table student1;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student1 | CREATE TABLE `student1` (
  `id` int DEFAULT NULL,
  `name` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.2临时修改默认引擎

可以通过SET DEFAULT_STORAGE_ENGINE=MyISAM命令临时修改默认引擎

mysql> SET DEFAULT_STORAGE_ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

2.3直接指定存储引擎

在创建表时，我们可以直接显式的指定存储引擎：

mysql> create table emp4(id int) engine=innoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table emp4;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------+
| emp4  | CREATE TABLE `emp4` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

常见引擎介绍

最常用的引擎有两个：InnoDB和MyISAM，其他的储存引擎在下面我们也会稍微介绍，了解即可。

InnoDB引擎

具备外键支持功能的事物存储引擎

在MySQL5.5版本后，默认采用的引擎是InnoDB引擎
InnoDB支持事务，所以被用来处理大量的短期事务（short-lived)，支持事务的提交(Commit)和回滚（Rollback）功能。
通常情况下，优先考虑使用InnoDB存储引擎
InnoDB适合处理大量的数据，而MyISAM只适合处理体量比较小的数据
对比MyISAM，InnoDB的读写效率稍微慢一些，会占用更多的磁盘空间来保存数据和索引
MyISAM只缓存索引，不缓存真实数据，而InnoDB不仅缓存索引还要缓存在真实数据，对内存要求高一些。

InnoDB的优点总结：

支持外键
支持事务
不仅有增加和查询操作，还有更新和删除操作
支持行级锁

不仅有优点，也有一些缺点：

读写效率稍微慢一些
对内存要求高

读写效率差的一个原因是InnoDB将数据和索引都放在了.ibd结尾的文件中，即"索引即数据"，而MyISAM将索引和数据分开存储，放在了不同的文件中，在加载时，MyISAM只需要加载索引，而InnoDB需要加载索引和数据，所以自然运行速度就慢了一些，但是emm影响不大，该用还得用。

MyISAM引擎

主流的非事务处理存储引擎

不支持事务、行级锁、外键等，最明显的一个缺陷是崩溃后无法恢复
是MySql5.5版本之前的默认引擎
在以select、insert主的应用，访问速度非常快
在数据统计时，有额外的常数存储，所以count(*)的查询效率比较高

MyISAM和InnoDB的对比(一个表格搞定)

经过上面的介绍，我们已经了解到，InnoDB有良好的事务管理、崩溃修复和并发控制能力，有很多的场景中，我们都要求事务完整性，这时非常适合选择InnoDB，但缺点也很明显，读写效率稍差，占用的数据空间比较大

对于MyISAM存储引擎，非常适合小型应用，就是以读取和插入操作为主，只有很少的更新、删除操作的应用，这个引擎的优势就是占用空间下，存储速度快，缺点是不支持事务，崩溃后无法修复

对比	MyISAM	InnoDB
外键		支持
事务		支持
锁方面	表锁，操作一条记录就会锁住整张表，不适合高并发	行锁，操作一条记录对其他行没有影响，适合高并发
缓存	只缓存索引	缓存索引和真实数据，对内存要求高，且内存大小对性能有直接影响
自带表的使用	是
优势	节省资源、消耗少、适合简单业务	支持并发写入和事务，适合复杂业务
默认		是MySQL的默认引擎