MySQL의 일반 인덱스와 고유 인덱스의 차이점에 대한 자세한 설명

MySQL의 일반 인덱스와 고유 인덱스의 차이점을 소개하는 글입니다. MySQL을 공부하는 친구들에게 도움이 되었으면 좋겠습니다.

MySQL의 일반 인덱스와 고유 인덱스의 차이점에 대한 자세한 설명

1. 쿼리와 업데이트의 차이점

이 두 가지 인덱스 유형 간에 쿼리 기능에는 차이가 없습니다. 업데이트 성능에 미치는 영향을 고려합니다. 가급적 일반 인덱스를 선택하는 것이 좋습니다.

(무료 학습 영상 튜토리얼 추천: mysql 영상 튜토리얼)

1.1 MySQL 쿼리 연산

■ 일반 인덱스

조건에 맞는 첫 번째 레코드를 찾은 후 첫 번째 레코드까지 계속해서 역순회 조건에 맞지 않는 거죠.

■ 고유 인덱스

인덱스는 고유성을 정의하므로 조건에 맞는 첫 번째 레코드를 찾은 후 바로 검색을 중지합니다.

일반 인덱스는 거의 영향을 주지 않고 한 번 더 검색합니다. InnoDB 데이터는 데이터 페이지 단위로 읽고 쓰기 때문에 데이터를 읽어야 할 때 디스크에서 직접 레코드를 읽지 않고 먼저 데이터 페이지를 메모리로 읽은 다음 데이터 페이지에서 검색합니다.

데이터 페이지의 기본값은 16KB입니다. 정수 필드의 경우 데이터 페이지는 거의 1000개의 키를 보유할 수 있습니다. 읽을 데이터가 데이터 페이지의 마지막 레코드가 아닌 경우에는 다른 데이터 페이지를 읽어야 합니다. 매우 어렵고 CPU 소비도 기본적으로 무시할 수 있습니다.

따라서 데이터 쿼리 측면에서는 일반 인덱스와 고유 인덱스 사이에 차이가 없습니다.

1.2 MySQL 업데이트 작업

업데이트 작업은 디스크의 데이터를 직접 업데이트하지 않습니다. 먼저 디스크의 데이터 페이지를 메모리로 읽어온 다음 데이터 페이지를 업데이트합니다.

■ 일반 인덱스

디스크의 데이터 페이지를 메모리로 읽어와 데이터 페이지를 업데이트합니다.

■ 고유 인덱스

는 디스크의 데이터 페이지를 메모리로 읽어와서 고유한지 확인한 후 데이터 페이지를 업데이트합니다.

MySQL의 버퍼 변경 메커니즘으로 인해 일반 인덱스와 고유 인덱스 간의 업데이트에는 일정한 차이가 있습니다.

변경 버퍼의 기능은 IO 작업을 줄이고 과도한 시스템 로드를 방지하는 것입니다. 변경 버퍼의 데이터 페이지에 데이터를 쓰는 과정을 병합이라고 합니다.

업데이트해야 하는 데이터 페이지가 메모리에 있으면 데이터 페이지가 직접 업데이트되고, 데이터가 메모리에 없으면 업데이트 작업이 먼저 변경 버퍼에 기록됩니다. 다음에 읽으면 데이터 페이지에 병합됩니다. 변경 버퍼에도 일반 병합 전략이 있습니다. 병합은 데이터베이스가 정상적으로 종료되는 동안에도 트리거됩니다.

고유 인덱스의 경우 업데이트하기 전에 데이터가 고유한지 확인해야 합니다(테이블의 데이터와 반복될 수 없음). 데이터 페이지가 메모리에 있으면 직접 확인하고 업데이트할 수 있습니다. 메모리에 없으면 디스크에서 읽어서 고유한지 확인해야 합니다. 그렇다면 업데이트하세요. 변경 버퍼는 사용되지 않습니다. 데이터 페이지가 메모리에 없더라도 읽어야 합니다.

변경 버퍼는 버퍼 풀에 있는 메모리를 사용하기 때문에 무한정 늘릴 수 없습니다. 변경 버퍼의 크기는 innodb_change_buffer_max_size 매개변수를 통해 동적으로 설정할 수 있습니다. 이 매개변수를 50으로 설정하면 변경 버퍼의 크기가 버퍼 풀의 최대 50%까지만 차지할 수 있음을 의미합니다.

결론: 고유 인덱스는 변경 버퍼를 사용할 수 없으며 일반 인덱스만 사용할 수 있습니다.

2. 변경 버퍼와 리두 로그의 차이점

2.1 변경 버퍼에 적용 가능한 시나리오

변경 버퍼의 기능은 업데이트 작업과 캐시 업데이트 작업의 빈도를 줄이는 것입니다. 이는 업데이트가 적시에 이루어지지 않는다는 단점이 있습니다. 읽기 작업이 빈번한 테이블의 경우 변경 버퍼를 사용하지 않는 것이 좋습니다.

업데이트 작업이 변경 버퍼에 방금 기록되었기 때문에 테이블을 읽고 데이터 페이지를 메모리에 읽어 데이터를 즉시 데이터 페이지에 병합했습니다. 이렇게 하면 성능 소비가 줄어들 뿐만 아니라 변경 버퍼를 유지하는 데 드는 비용도 늘어납니다.

쓰기가 많고 읽기가 적은 테이블에 적용됩니다.

2.2 변경 버퍼와 리두 로그의 차이점

리두 로그와 변경 버퍼를 이해하기 위해 예를 들어 보겠습니다. 다음 SQL 문을 실행합니다.

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

(id1,k1)이 데이터 페이지 페이지 1에 있고, (id2,k2)가 데이터 페이지 페이지 2에 있다고 가정합니다. 그리고 페이지 1은 메모리에 있고 페이지 2는 없습니다.

실행 과정은 다음과 같습니다.

Write (id1,k1) to Page 1;

변경 버퍼에 "Write (id2,k2) to Page 2" 메시지를 적습니다.

모두 변환합니다. 작업은 리두 로그에 기록됩니다.

위 사항을 완료하시면 거래가 완료됩니다. 이 업데이트 문을 실행하는 데 드는 비용은 매우 낮습니다. 즉, 두 개의 메모리 위치에 쓴 다음 디스크에 쓰는 것(두 작업을 결합하여 하나의 디스크 쓰기)이며 순차적으로 기록됩니다.

이 업데이트 문은 메모리, 리두 로그(ib_log_fileX), 데이터 테이블스페이스(t.ibd), 시스템 테이블스페이스(ibdata1)의 네 부분으로 구성됩니다.

如果要读数据的话，过程是怎样的？

mysql> select * from t where k in (k1, k2);

假设读操作在更新后不久，此时内存中还有 Page 1，没有 Page 2，那么读操作就和 redo log 以及 ibdata1 无关了。

从内存中获取到 Page 1 上的最新数据 (id1,k1)；

将数据页 Page 2 读入内存，执行merge 操作，此时内存中的 Page 2 也有最新数据(id2,k2)；

需要注意的是：

redo log中的数据，可能还没有 flush 到磁盘，磁盘中的 Page 1 和 Page 2 中并没有最新数据，但我们依然可以拿到最新数据（内存中的 Page 1 就是最新的，Page 2 虽然不是最新的，但是从磁盘读到内存中后，执行了merge操作，内存中的 Page 2 就是最新的了。）

如果此时 MySQL 异常宕机了，比如服务器异常掉电，change buffer 中的数据会不会丢？

change buffer 中的数据分为两部分，一部分是已经merge到ibdata1中的数据，这部分数据已经持久化，不会丢失。另一部分数据，还在 change buffer 中，没有merge 到ibdata1，分 3 种情况：

（1）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog中也没有fsync到磁盘，这部分数据会丢失；

（2）change buffer 写入数据到内存，redo log 也已经写入（ib-log-filex），但是未 commit，binlog 已写入到磁盘，这部分不会多丢失，异常重启后会先从 binlog 恢复 redo log，再从 redo log 恢复 change buffer；

（3）change buffer 写入数据到内存，redo log 和 binlog 都已经fsync，直接从redo log 恢复，不会丢失。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗

更多MySQL相关教程，请关注PHP中文网！

위 내용은 MySQL의 일반 인덱스와 고유 인덱스의 차이점에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!