【MySQL】MySQL存储引擎以及索引

最新推荐文章于 2024-10-06 19:50:43 发布

justskinny

最新推荐文章于 2024-10-06 19:50:43 发布

阅读量5.6k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/u014496330/article/details/53056271

版权

本文详细介绍了MySQL中的存储引擎，包括InnoDB（默认存储引擎，支持事务和行级锁）、MyISAM（速度快，但不支持事务）和MEMORY（数据存储在内存中）。还探讨了InnoDB和MyISAM的主要区别，如事务支持、存储结构和锁的差异。此外，文章还讨论了索引的数据结构，如B+树和Hash索引，以及MySQL中各种类型的索引使用，如普通索引、唯一索引和主键索引。最后，分析了使用索引的优缺点，如提高查询速度和增加数据维护成本。

摘要由CSDN通过智能技术生成

一、存储引擎

MySQL中的数据用各种不同的技术存储在文件中，这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在 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 |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
+——————–+———+————————————————
—————-+————–+——+————+
9 rows in set (0.00 sec)

support一列代表是否支持该存储引擎，yes表示支持、no表示不支持、default表示默认使用、disabled代表被关闭；transactions一列代表是否支持事务处理；xa表示是否支持分布式事务，savepoints表示是否支持分布式事务。

1.InnoDB（MySQL默认存储引擎从版本5.5.5开始）

支持事务，行级锁，以及外键，拥有高并发处理能力。但是在创建索引和加载数据时，比MyISAM慢。

2.MyISAM

不支持事务和行级锁。所以速度很快，性能优秀。可以对整张表加锁，支持并发插入，支持全文索引。

3.MEMORY

支持Hash索引，内存表，Memory引擎将数据存储在内存中，表结构不是存储在内存中的，查询时不需要执行磁盘I/O操作，所以要比MyISAM和InnoDB快很多倍，但是数据库断电或是重启后，表中的数据将会丢失，表结构不会丢失。

InnoDB跟MyISAM的区别

1.存储结构
MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
2.存储空间
MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
3.可移植性、备份及恢复
MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
4.事务支持
MyISAM：强调的是性能，每次查询具有原子