MySQL의 InnoDB와 MyISAM 스토리지 엔진의 차이점

MySQL 데이터베이스가 다른 데이터베이스와 구별되는 매우 중요한 기능은 데이터베이스가 아닌 테이블을 기반으로 하는 플러그인 테이블 스토리지 엔진입니다. 각 스토리지 엔진에는 고유한 특성이 있으므로 각 테이블에 가장 적합한 스토리지 엔진을 선택할 수 있습니다. MySQL数据库区别于其他数据库的很重要的一个特点就是其插件式的表存储引擎，其基于表，而不是数据库。由于每个存储引擎都有其特点，因此我们可以针对每一张表来挑选最合适的存储引擎。

作为DBA，我们应该深刻的认识存储引擎。今天介绍两种最常见的存储引擎和它们的区别：InnoDB和MyISAM。

InnoDB存储引擎

InnoDB存储引擎支持事务，其设计目标主要就是面向OLTP（On Line Transaction Processing 在线事务处理）的应用。特点为行锁设计、支持外键，并支持非锁定读。从5.5.8版本开始，InnoDB成为了MySQL的默认存储引擎。

InnoDB存储引擎采用聚集索引（clustered）的方式来存储数据，因此每个表都是按照主键的顺序进行存放，如果没有指定主键，InnoDB会为每行自动生成一个6字节的ROWID作为主键。

MyISAM存储引擎

MyISAM存储引擎不支持事务、表锁设计，支持全文索引，主要面向OLAP（On Line Analytical Processing 联机分析处理）应用，适用于数据仓库等查询频繁的场景。在5.5.8版本之前，MyISAM是MySQL的默认存储引擎。该引擎代表着对海量数据进行查询和分析的需求。它强调性能，因此在查询的执行速度比InnoDB更快。

InnoDB和MyISAM的区别

事务

为了数据库操作的原子性，我们需要事务。保证一组操作要么都成功，要么都失败，比如转账的功能。我们通常将多条SQL语句放在begin和commit之间，组成一个事务。

InnoDB支持，MyISAM不支持。

主键

由于InnoDB的聚集索引，其如果没有指定主键，就会自动生成主键。
MyISAM支持没有主键的表存在。

外键

为了解决复杂逻辑的依赖，我们需要外键。比如高考成绩的录入，必须归属于某位同学，我们就需要高考成绩数据库里有准考证号的外键。

InnoDB支持，MyISAM不支持。

索引

为了优化查询的速度，进行排序和匹配查找，我们需要索引。比如所有人的姓名从a-z首字母进行顺序存储，当我们查找zhangsan或者第44位的时候就可以很快的定位到我们想要的位置进行查找。

InnoDB是聚集索引，数据和主键的聚集索引绑定在一起，通过主键索引效率很高。如果通过其他列的辅助索引来进行查找，需要先查找到聚集索引，再查询到所有数据，需要两次查询。

MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据的指针。

从InnoDB 1.2.x版本，MySQL5.6版本后，两者都支持全文索引。

auto_increment自增

对于自增数的字段，InnoDB要求该列必须是索引，同时必须是索引的第一个列，否则会报错：

mysql> create table test(
    -> a int auto_increment,
    -> b int,
    -> key(b,a)
    -> ) engine=InnoDB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

把(b,a)顺序替换为(a,b)即可。

而MyISAM可以将该字段与其他字段随意顺序组成成联合索引。

表行数

很常见的需求是看表中有多少条数据，此时我们需要select count(*) from table_name。

InnoDB不保存表行数，需要进行全表扫描。MyISAM用一个变量保存，直接读取该值，更快。当时当带有where查询的时候，两者一样。

存储

数据库的文件都是需要在磁盘中进行存储，当应用需要时再读取到内存中。一般包含数据文件、索引文件。

InnoDB分为：

• .frm表结构文件
• .ibdata1共享表空间
• .ibd表独占空间
• .redo日志文件

MyISAM分为三个文件：

• .frm存储表定义
• .MYD存储表数据
• .MYI

  

asDBA, 우리는 스토리지 엔진에 대한 깊은 이해가 있어야 합니다. 오늘은 가장 일반적인 두 가지 스토리지 엔진과 그 차이점인 InnoDB와 MyISAM을 소개합니다.

InnoDB 스토리지 엔진

🎜🎜InnoDB 스토리지 엔진은 트랜잭션 및 설계 목표를 지원합니다. 주로 OLTP(On Line Transaction Process)를 위한 어플리케이션입니다. 기능에는 행 잠금 설계, 외래 키 지원, 비잠금 읽기 지원이 포함됩니다. 5.5.8 버전부터 InnoDB가 MySQL의 기본 스토리지 엔진이 됩니다. 🎜🎜InnoDB스토리지 엔진은 클러스터형 인덱스를 사용하여 데이터를 저장하므로 기본 키가 지정되지 않은 경우 InnoDB 6바이트 ROWID가 각 행에 대해 기본 키로 자동 생성됩니다. 🎜🎜🎜MyISAM 스토리지 엔진 🎜🎜🎜MyISAM 스토리지 엔진은 트랜잭션 및 테이블 잠금을 지원하지 않습니다. design 은 주로 OLAP(온라인 분석 처리) 애플리케이션을 위한 전체 텍스트 인덱싱을 지원하며 데이터 웨어하우스와 같이 쿼리가 자주 발생하는 시나리오에 적합합니다. 5.5.8 버전 이전에는 MyISAM이 MySQL의 기본 스토리지 엔진이었습니다. 이 엔진은 대량의 데이터를 쿼리하고 분석해야 하는 필요성을 나타냅니다. 성능을 강조하여 InnoDB보다 빠르게 쿼리를 실행할 수 있습니다. 🎜🎜🎜InnoDB와 MyISAM🎜🎜🎜🎜Transactions🎜🎜🎜데이터베이스의 경우 연산 원자성을 위해서는 트랜잭션이 필요합니다. 자금 이체 기능과 같은 일련의 작업이 모두 성공하거나 실패하는지 확인합니다. 우리는 일반적으로 begin과 commit 사이에 여러 개의 SQL 문을 배치하여 트랜잭션을 형성합니다. 🎜🎜InnoDB는 지원하지만 MyISAM은 지원하지 않습니다. 🎜🎜🎜기본 키🎜🎜🎜InnoDB의 클러스터형 인덱스로 인해 기본 키를 지정하지 않으면 자동으로 기본 키가 생성됩니다.
MyISAM은 기본 키가 없는 테이블의 존재를 지원합니다. 🎜🎜🎜외래 키🎜🎜🎜복잡한 논리의 종속성을 해결하려면 외래 키가 필요합니다. 예를 들어, 대학 입시 점수 항목은 특정 학생의 것이어야 하므로 수험표 번호가 포함된 대학 입시 점수 데이터베이스에 대한 외래 키가 필요합니다. 🎜🎜InnoDB는 지원하지만 MyISAM은 지원하지 않습니다. 🎜🎜🎜Index🎜🎜🎜쿼리 속도를 최적화하고 검색을 정렬하고 일치시키려면 인덱스가 필요합니다. 예를 들어, 모든 사람의 이름은 a-z의 첫 글자부터 순차적으로 저장됩니다. zhangsan 또는 44 위치를 검색하면 빠르게 찾을 수 있습니다. 우리가 원하는 위치를 찾아보세요. 🎜🎜InnoDB는 클러스터형 인덱스입니다. 기본 키를 통한 인덱스는 데이터가 매우 효율적입니다. 다른 열의 보조 인덱스를 통해 검색하려면 클러스터형 인덱스를 먼저 찾은 다음 모든 데이터를 쿼리해야 하므로 두 번의 쿼리가 필요합니다. 🎜🎜MyISAM은 비클러스터형 인덱스이며 데이터 파일이 분리되어 있으며 인덱스는 데이터의 포인터를 저장합니다. 🎜🎜InnoDB 1.2.x 버전과 MySQL5.6 버전부터 모두 전체 텍스트 인덱싱을 지원합니다. 🎜🎜🎜auto_increment자동 증가🎜🎜🎜자동 증가 필드의 경우 InnoDB에서는 열이 인덱스여야 하고 인덱스의 첫 번째 열이어야 합니다. 그렇지 않으면 오류 보고: 🎜rrreee🎜 (b,a)의 순서를 (a,b)로 바꾸세요. 🎜🎜그리고 MyISAM은 이 필드를 다른 필드와 순서에 관계없이 결합하여 공동 인덱스를 형성할 수 있습니다. 🎜🎜🎜테이블 행 수🎜🎜🎜가장 일반적인 요구 사항은 테이블에 데이터 조각이 몇 개 있는지 확인하는 것입니다. 이때 table_name에서 개수(*)를 선택해야 합니다. 🎜🎜InnoDB는 테이블 행 수를 저장하지 않으며 전체 테이블 스캔이 필요합니다. MyISAM은 변수에 저장되며 값을 직접 읽을 수 있어 속도가 더 빠릅니다. 당시 where로 쿼리하면 둘은 동일했습니다. 🎜🎜🎜Storage🎜🎜🎜 데이터베이스의 파일은 디스크에 저장한 다음 애플리케이션에 필요할 때 메모리로 읽어야 합니다. 일반적으로 데이터 파일과 인덱스 파일이 포함됩니다. 🎜🎜InnoDB는 다음으로 나뉩니다: 🎜

• .frm 테이블 구조 파일 🎜
• .ibdata1 공유 테이블 공간 🎜 .ibd 테이블은 독점 공간을 차지합니다. 🎜
• .redo 로그 파일 🎜🎜🎜MyISAM은 세 개의 파일로 나뉩니다. 🎜
• .frm 테이블 정의 저장 🎜
• .MYD 테이블 데이터 저장 🎜
• .MYI 테이블 인덱스 저장 🎜 🎜🎜 🎜실행 속도🎜🎜

  SELECT와 같이 쿼리 작업이 많은 경우 MyISAM을 사용하면 더 나은 성능을 얻을 수 있습니다.
  대부분의 작업이 삭제 및 변경인 경우 InnoDB를 사용하세요. SELECT，使用MyISAM性能会更好。
  如果大部分是删除和更改的操作，使用InnoDB。

  InnoDB和MyISAM的索引都是B+树索引，通过索引可以查询到数据的主键，不熟悉B+树的可以查看MySQL InnoDB索引原理和算法。两者的性能区别主要在于查询到数据主键后两者的处理方式却不同。

  InnoDB会缓存索引和数据文件，一般以16KB为一个最小单元（数据页大小）和磁盘进行交互，InnoDB在查询到索引数据后实际得到的是主键的ID，它需要在内存中的数据页中查找该行的全部数据，但如果该数据不是加载过的热数据，还需要进行数据页的查找和替换，这其中可能牵涉到多次I/O操作和内存中数据查找，导致耗时较高。

  而MyISAM存储引擎只缓存索引文件，不缓存数据文件，其数据文件的缓存直接使用操作系统的缓存，这点非常独特。此时相同的空间能够加载更多的索引，因此当缓存空间有限时，MyISAM的索引数据页替换次数会更少。根据前面我们知道MyISAM的文件分为MYI和MYD，当我们通过MYI查找到主键ID时，其实得到是MYD数据文件的offset偏移量，查找数据比InnoDB寻址映射要快的多。

  但由于MyISAM是表锁，而InnoDB支持行锁，因此在牵涉到大量写操作时，InnoDB的并发性能比MyISAM好很多。同时InnoDB还通过MVVC多版本控制来提高并发读写性能。

  delete删除数据

  调用delete from table时，MyISAM会直接重建表，InnoDB会一行一行的删除，但是可以用truncate table代替。参考: mysql清空表数据的两种方式和区别。

  锁

  MyISAM仅支持表锁，每次操作锁定整张表。
  InnoDB支持行锁，每次操作锁住最小数量的行数据。

  表锁相比于行锁消耗的资源更少，且不会出现死锁，但同时并发性能差。行锁消耗更多的资源，速度较慢，且可能发生死锁，但是因为锁定的粒度小、数据少，并发性能好。如果InnoDB的一条语句无法确定要扫描的范围，也会锁定整张表。

  当行锁发生死锁的时候，会计算每个事务影响的行数，然后回滚行数较少的事务。

  数据恢复

  MyISAM崩溃后无法快速的安全恢复。InnoDB有一套完善的恢复机制。

  数据缓存

  MyISAM仅缓存索引数据，通过索引查询数据。InnoDB不仅缓存索引数据，同时缓存数据信息，将数据按页读取到缓存池，按LRU（Latest Rare Use 最近最少使用）算法来进行更新。

  如何选择存储引擎

  创建表的语句都是相同的，只有最后的type来指定存储引擎。

  MyISAM

  1、大量查询总count

  2、查询频繁，插入不频繁

  3、没有事务操作

  InnoDB InnoDB와 MyISAM의 인덱스는 모두 B+ 트리 인덱스입니다. B+Tree에 익숙하지 않은 경우 MySQL InnoDB 인덱스 원리 및 알고리즘을 볼 수 있습니다. 둘 사이의 성능 차이는 주로 데이터의 기본 키를 쿼리한 후 처리 방법이 다르기 때문에 발생합니다.

  InnoDB는 인덱스와 데이터 파일을 캐시합니다. 일반적으로 16KB는 디스크와 상호 작용하는 최소 단위(데이터 페이지 크기)로 사용됩니다. code> 인덱스 데이터를 쿼리한 후 실제로 얻는 것은 기본 키의 ID입니다. 그러나 데이터 페이지에 있는 행의 모든 ​​데이터를 메모리에서 찾아야 합니다. 핫 데이터가 로드되지 않았더라도 계속 수행해야 합니다. 데이터 페이지 검색 및 교체에는 여러 I/O 작업과 메모리에서의 데이터 검색이 포함될 수 있으므로 시간이 많이 소모됩니다.

  MyISAM 스토리지 엔진은 인덱스 파일만 캐시하고 데이터 파일은 캐시하지 않습니다. 해당 데이터 파일의 캐시는 운영 체제의 캐시를 직접 사용하는데 이는 매우 독특합니다. 이때 동일한 공간에서는 더 많은 인덱스를 로드할 수 있으므로 캐시 공간이 제한되면 MyISAM의 인덱스 데이터 페이지 교체 시간이 줄어듭니다. 앞서 우리가 알고 있는 바에 따르면, MyISAM 파일은 MYI와 MYD로 나누어져 있는데, 를 통해 기본 키를 찾으면 됩니다. code>MYI >ID, 실제로 MYD 데이터 파일의 offset 오프셋을 가져오고 데이터 검색이 보다 빠릅니다. InnoDB 주소 매핑.

  하지만 MyISAM은 테이블 잠금이고 InnoDB는 행 잠금을 지원하기 때문에 많은 수의 쓰기 작업이 관련되면 InnoDB의 동시성 성능이 저하됩니다. code>가 MyISAM보다 낮을수록 훨씬 좋습니다. 동시에 InnoDB는 MVVC 다중 버전 제어를 통해 동시 읽기 및 쓰기 성능도 향상시킵니다. delete데이터 삭제

  🎜🎜delete from table을 호출하면 MyISAM이 InnoDB code>는 행 단위로 삭제되지만 <code>truncate table로 대체할 수 있습니다. 참고: mysql에서 테이블 데이터를 지우는 두 가지 방법과 차이점. 🎜🎜Lock🎜🎜🎜MyISAM은 테이블 잠금만 지원하며, 각 작업마다 전체 테이블이 잠깁니다.
  InnoDB는 각 작업에 대해 최소 데이터 행 수를 잠그는 행 잠금을 지원합니다. 🎜🎜테이블 잠금은 행 잠금보다 적은 리소스를 소비하고 교착 상태를 일으키지 않지만 동시에 동시성 성능이 낮습니다. 행 잠금은 더 많은 리소스를 소비하고 속도가 느리며 교착 상태를 일으킬 수 있지만 잠금 세분성이 작고 데이터가 작기 때문에 동시성 성능이 좋습니다. InnoDB의 문이 스캔할 범위를 결정할 수 없는 경우 전체 테이블도 잠깁니다. 🎜🎜행 잠금 교착 상태가 발생하면 각 트랜잭션의 영향을 받는 행 수를 계산한 후 행 수가 적은 트랜잭션이 롤백됩니다. 🎜🎜데이터 복구🎜🎜🎜 MyISAM 충돌 후에는 빠르고 안전한 복구가 없습니다. InnoDB에는 완전한 복구 메커니즘이 있습니다. 🎜🎜데이터 캐싱🎜🎜🎜MyISAM은 인덱스 데이터만 캐시하고 인덱스를 통해 데이터를 쿼리합니다. InnoDB는 인덱스 데이터를 캐시할 뿐만 아니라 데이터 정보를 캐시하고 페이지 단위로 캐시 풀에 데이터를 읽어와 LRU(Latest Rare Use) 알고리즘에 따라 업데이트합니다. . 🎜🎜스토리지 엔진 선택 방법🎜🎜🎜테이블을 생성하는 문은 모두 동일하고 마지막 유형 제공 스토리지 엔진을 지정합니다. 🎜🎜<strong><code>MyISAM🎜🎜🎜1. 총 쿼리 수가 개🎜🎜2. 빈번한 쿼리, 드물게 삽입🎜🎜3. InnoDB🎜🎜🎜1. 고가용성 또는 트랜잭션이 필요합니다🎜🎜2. 빈번한 테이블 업데이트🎜🎜추천 학습:🎜MySQL 튜토리얼🎜🎜

위 내용은 MySQL의 InnoDB와 MyISAM 스토리지 엔진의 차이점의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!