Mysql存储引擎介绍
- 1 什么是存储引擎？

Mysql存储引擎介绍

在数据库中存的就是一张张有着千丝万缕关系的表，所以表设计的好坏，将直接影响着整个数据库。而在设计表的时候，我们都会关注一个问题，使用什么存储引擎。等一下，存储引擎？什么是存储引擎？

1 什么是存储引擎？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。
这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。 MySQL默认配置了许多不同的存储引擎，可以预先设置或者在MySQL服务器中启用。你可以选择适用于服务器、数据库和表格的存储引擎，以便在选择如何存储你的信息、如何检索这些信息以及你需要你的数据结合什么性能和功能的时候为你提供最大的灵活性。

关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表格，类似于Excel的电子表格的形式。有的表简单，有的表复杂，有的表根本不用来存储任何长期的数据，有的表读取时非常快，但是插入数据时去很差；而我们在实际开发过程中，就可能需要各种各样的表，不同的表，就意味着存储不同类型的数据，数据的处理上也会存在着差异，那么。对于MySQL来说，它提供了很多种类型的存储引擎（或者说不通的表类型），我们可以根据对数据处理的需求，选择不同的存储引擎，从而最大限度的利用MySQL强大的功能，其中应用最广泛的是InnoDB和MyISAM两种。

1.1 MyISAM存储引擎

1.1.1 MyISAM引擎特点

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚集索引
读取数据较快，占用资源较少
不支持MVCC高并发
奔溃恢复性较差
Mysql5.5前默认的数据库引擎

1.1.2 MyISAM存储引擎适用场景

只读（或者写较少）
表较小（可以接受长时间进行修复操作）

1.1.3 MyISAM引擎文件

tbl_name.frm 表格式定义
tbl_name.MYD 数据文件
tbl_name.MYI 索引文件

1.2 InnoDB引擎

1.2.1 InnoDB引擎特点

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚集索引
奔溃恢复性更高
支持MVCC高并发
从Mysql5.5后支持全文索引
从Mysql5.5开始为默认的数据库引擎

1.2.2 InnoDB数据库文件

所有InnoDB表的数据和索引位置放置于同一个表空间中

数据文件：ibdata1，ibdata2存放在datadir定义的目录中
表格式定义：tb_name.frm存在在datadir定义的每个数据库对应的目录下

每个表单独使用一个表空间存储表的数据和索引

两类文件放在对应每个数据库独立目录中
数据文件（存储数据和索引）：tb_name.ibd
表格式定义：tb_name.frm

1.3 管理存储引擎

查看Mysql支持的存储引擎

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV 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         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查看当前默认的存储引擎

mysql> show variables like "%storage_engine%";
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+

设置默认的存储引擎

[root@mysql-36 ~]# vim /etc/my.cnf
[mysqld]
default_storage_engine=InnoDB

查看库中所有表使用的存储引擎

mysql> show table status from world;
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| city            | InnoDB |      10 | Dynamic    | 4046 |            101 |      409600 |               0 |       114688 |         0 |           4080 | 2021-09-19 14:01:18 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
| city_bak        | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2021-12-04 18:02:58 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
| country         | InnoDB |      10 | Dynamic    |  239 |            479 |      114688 |               0 |            0 |         0 |           NULL | 2021-09-19 14:01:19 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
| countrylanguage | InnoDB |      10 | Dynamic    |  984 |             99 |       98304 |               0 |        65536 |         0 |           NULL | 2021-09-19 14:01:19 | NULL        | NULL       | utf8mb4_general_ci |     NULL |                |         |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+

查看库中指定表的存储引擎

mysql> show table status like 'city';
mysql> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4

设置表的存储引擎

mysql> create table city(id int,city varchar(255)) ENGINE=InnoDB;
mysql> ALTER table city ENGINE=InnoDB;

1.4 面试题

你用的mysql是哪个引擎，各引擎之间有什么区别？

主要MyISAM与InnoDB两个引擎，其主要区别如下：
1）InnoDB支持事务，MyISAM不支持，这一点是非常重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用：
3）InnoDB支持外键，MyISAM不支持。
4）MyISAM在5.6之前都是默认引擎，5.6之后InnoDB是默认引擎。
5）InnoDB不支持FULLTEXT类型的索引。
6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行。但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表。
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。清空整个表时，InnoDB是一行一行的删除，效率非常慢，MyISAM则会重建表。
8）InnoDB支持行锁，而MyISAM只能支持表锁。
总结：
1、InnoDB支持事务，MyISAM不支持事务
2、InnoDB支持外键，MyISAM不支持
3、InnoDB是聚集索引，MyISAM是非聚集索引
4、InnoDB支持行锁，而MyISAM只能支持表锁

第六章 Mysql存储引擎介绍