MySQL 인덱싱 및 최적화에 대한 지식 포인트는 무엇입니까?

인덱스란 무엇인가요?

• Index는 MySQL이 효율적인 쿼리를 수행하는 데 도움이 되는 데이터 구조입니다. 책의 목차처럼 쿼리 속도를 높일 수 있습니다

색인의 구조는?

인덱스에는 B-Tree 인덱스와 Hash 인덱스가 있을 수 있습니다. 인덱스는 스토리지 엔진에서 구현됩니다.

InnoDB / MyISAM은 B-Tree 인덱스만 지원합니다.

Memory/Heap은 B-Tree 인덱스 및 해시 인덱스를 지원합니다.

• B-Tree

  B-Tree는 매우 적합한 데이터 구조입니다. 디스크 작업을 위해. 다중 방향 균형 탐색 트리입니다. 높이는 일반적으로 2-4이며 리프가 아닌 노드와 리프 노드는 데이터를 저장합니다. 모든 리프 노드는 동일한 레이어에 있습니다. 아래 그림은 B-Tree

• B+ Tree입니다. B+ Tree는 B-Tree를 기반으로 최적화된 것입니다. B-트리와 주요 차이점은 B+ 트리의 모든 데이터가 리프 노드에 저장되고 리프 노드는 연결 리스트로 연결된다는 것입니다. 아래 그림은 B+ 트리입니다

InnoDB의 페이지 크기는 16KB입니다(페이지는 B+ 트리의 노드입니다). 테이블의 기본 키가 INT이고 크기가 4바이트인 경우. , 그 노드에는 4K 키 값도 저장할 수 있습니다. 포인터와 키 값이 동일한 크기를 차지한다고 가정하면 높이가 3인 B+ 트리는 두 번째 레이어에 2048개의 노드가 있고 리프 노드의 수는 다음과 같습니다. 세 번째 레이어는 2048*2048 = 4194304이고 하나의 노드는 16KB이며 총 67108864KB, 즉 65536MB 또는 64G의 데이터를 수용할 수 있습니다.

리프 노드는 연결리스트로 묶여 있기 때문에 인덱스 열 기준으로 정렬하면 기본적으로 정렬되므로 효율성이 매우 높습니다.

• MyISAM 인덱스
  MyISAM 인덱스와 데이터는 별도로 저장됩니다. MyISAM의 기본 키 인덱스에서는 레코드 주소가 B+ 트리 리프 노드에 저장되므로 MyISAM은 인덱스 쿼리를 통해 2개의 IO를 거쳐야 합니다

MyISAM의 보조 인덱스는 유일한 차이점 예, 보조 인덱스의 키는 반복 가능하지만 기본 키 인덱스의 키는 반복될 수 없습니다

• InnoDB 인덱스
  InnoDB 데이터와 인덱스가 함께 저장되며 클러스터형 인덱스라고도 합니다. . 데이터는 기본 키로 인덱싱되고 기본 키 인덱스 B+ 트리의 리프 노드에 저장됩니다.
  InnoDB 기본 키 인덱스는 리프 노드에 이미 데이터가 포함되어 있습니다. 즉, 인덱스와 데이터가 함께 저장되는 클러스터형 인덱스입니다.

InnoDB의 보조 인덱스인 리프 노드에는 주소 대신 기본 키 값이 저장됩니다. 보조 색인을 사용하려면 두 번의 검색이 필요합니다.

InnoDB와 MyISAM 인덱스의 차이점:

• InnoDB는 클러스터형 인덱스를 사용하며 기본 키 인덱스 리프 노드는 데이터를 직접 저장하는 반면 보조 인덱스의 리프 노드는 기본 키 값을 저장합니다.

• MyISAM 비클러스터형 인덱스를 사용하는 경우 데이터와 인덱스가 동일한 파일에 있지 않습니다. 기본 키 인덱스의 리프 노드에는 행 레코드의 주소가 저장되고, 보조 인덱스의 리프 노드에도 레코드의 주소가 저장됩니다. 레코드의 주소, 보조 인덱스의 주소만 키는 반복될 수 있지만 기본 키 인덱스의 키는 반복될 수 없습니다
  

Question:

• InnoDB를 사용하지 않는 이유 기본 키로 너무 긴 필드?
  기본 키가 너무 길면 보조 인덱스가 많은 공간을 차지하게 됩니다

• InnoDB에서 자동 증가 기본 키를 사용하는 것이 권장되는 이유는 무엇인가요?
  자동 증가 기본 키를 사용하면 새 레코드가 삽입될 때마다 현재 인덱스 노드의 다음 위치에 새 레코드가 순차적으로 추가되므로 한 페이지가 가득 차면 새 페이지가 열립니다. 인덱스 구조 만들기 매우 컴팩트하며 삽입할 때마다 기존 데이터를 이동할 필요가 없어 매우 효율적입니다. 자동 증가 기본 키를 사용하지 않으면 새 레코드를 삽입할 때마다 삽입 위치를 선택해야 하고, 데이터를 이동해야 할 수도 있어 효율성이 비효율적이고 인덱스 구조가 컴팩트하지 않습니다

• B-트리가 없는 이유는 무엇인가요?

인덱스는 어디에 있나요?

• 인덱스 자체도 비교적 크고 일반적으로 디스크에 저장됩니다. 인덱스와 데이터는 별도로 저장되거나(MyISAM의 비클러스터형 인덱스) 함께 저장될 수 있습니다(InnoDB의 클러스터형 인덱스)

Index What 장점과 단점이 있나요?

• 장점

• IO 비용 절감 및 데이터 쿼리 효율성 향상

• 정렬 비용 절감 (인덱싱된 열이 자동으로 정렬되며, order 사용 시 효율성이 훨씬 향상됩니다.) by)

• 단점

• 인덱스는 추가 저장 공간을 차지합니다.

• 인덱스는 테이블 데이터 업데이트의 효율성을 감소시킵니다. 연산 추가, 삭제, 수정 시에는 데이터 저장뿐만 아니라 해당 인덱스도 업데이트해야 합니다.

인덱스 분류

• 단일 열 인덱스

• 기본 키 인덱스

• 고유 인덱스

• 일반 인덱스

• 결합 인덱스

인덱스 사용

• 인덱스 생성

 CREATE INDEX index_name ON table_name(col_name);
-- 或者
ALTER TABLE table_name ADD INDEX index_name(col_name)

• 인덱스 삭제

DROP INDEX index_name ON table_name;

• 색인 생성이 필요한 시나리오

• 쿼리 조건으로 자주 사용되는 컬럼, 인덱스 생성 필요

• 다중 테이블 연관에서 관련 필드 인덱스 생성 필요

• 쿼리에서 필드 정렬, 인덱스 생성 필요

• 인덱싱이 적용되지 않는 시나리오

• 다중 읽기 쓰기 인덱스 구축에 적합하지 않은 테이블이 거의 없음

• 자주 업데이트되는 필드가 인덱스 구축에 적합하지 않음

실행 계획을 설명

사용자가 있습니다. 인덱스는 다음과 같은 테이블

where name, age, address 세 필드를 하나의 인덱스로 사용합니다

Explain을 사용하여 특정 SQL 문에 대한 성능 분석을 수행할 수 있습니다

explain select * from user where name = 'am';

possible_keys
사용 가능성이 있는 인덱스
key
실제로 사용되는 인덱스
key_len
쿼리에 사용된 인덱스의 길이
ref
동등한 쿼리라면 const
rows
예상값 스캔해야 하는 행 수(정확한 값 아님)
extra

추가 정보(예:

• where
  사용)는 스토리지 엔진에서 반환된 결과를 SQL 계층 계층에서 필터링해야 함을 의미합니다.

• 인덱스 사용
  은 테이블에 다시 쿼리할 필요가 없음을 의미합니다. 일반적으로 이 값은 포함 인덱스를 사용할 때 사용됩니다. Covering index는 select의 열이 모두 인덱스 열임을 의미합니다. 테이블에 반환할 필요가 없는 쿼리는 보조 인덱스로 가서 직접 인덱스 컬럼의 값을 얻을 수 있다는 뜻이다. 인덱스 조건을 이용해 기본키 인덱스에서 레코드를 가져올 필요가 없다.

  MySQL은 5.6.x(인덱스 조건 푸시다운) 이후 ICP 기능을 지원하므로 조건을 충족하지 않는 레코드를 먼저 읽은 다음 필터링하는 대신 검사 조건을 스토리지 엔진 계층에 푸시할 수 있습니다. 이전과 같이 SQL Layer 레이어를 사용하면 스토리지 엔진이 줄어듭니다. 레이어에서 스캔하는 행 수

• 
  

filesort 사용

정렬 시 인덱스를 사용할 수 없습니다

• 
  

  type

system : 테이블에 데이터 행이 1개만 있거나 테이블이 비어 있습니다

• const: 고유 인덱스 또는 기본 키 인덱스를 사용하고 동일한 위치에서 쿼리하면 반환되는 레코드는 1행입니다. 고유 인덱스 스캔이라고도 합니다.

ref: 고유하지 않은 인덱스의 경우 동등한 where 조건을 사용하거나 가장 왼쪽 접두사 규칙에 대한 쿼리를 사용합니다.

• 다음은 만족하는 가장 왼쪽 접두사 규칙, 즉 idx_name_age_add에 대해 가장 왼쪽 접두사가 만족되고 첫 번째 인덱스는 name

range: 인덱스 범위 스캔, 공통 >, < ;, between, in, like 및 기타 쿼리

like일 때 와일드카드 문자 %는 시작 부분에 배치할 수 없습니다. 그렇지 않으면 전체 테이블 스캔이 발생합니다

index: 인덱스에 정확히 일치하는 항목은 없지만 테이블에 다시 쿼리할 필요는 없습니다.

all: 전체 테이블을 스캔한 다음 SQL Layer 계층의 요구 사항을 충족하는 레코드

索引使用规范（索引失效分析）

1. 全值匹配
   在索引列上使用等值查询

explain select * from user where name = 'y' and age = 15;

2. 最左前缀

组合索引中，查询条件要从组合索引的最左列开始，如上述example中组合索引idx_name_age_add，是建立在三个列name，age，address的，若跳过name，直接用age查询，则会变为全表扫描

explain select * from user where age = 15;

3. 不要在索引列上做计算

4. 范围条件右侧的索引列会失效

看到第一个SQL语句，没有用上addresss索引

5. 尽量使用覆盖索引

explain select name,age from user where name = 'y' and age = 1;

可以避免回表查询

6. 索引字段不要使用不等（!= 或 ）,不要判断null（is null/ is not null）
会导致索引失效，转为全表扫描

7. 索引字段上使用like时，不要以%开头

8. 索引字段如果是字符串，记得加单引号

9. 索引字段不要用or

例子总结：

위 내용은 MySQL 인덱싱 및 최적화에 대한 지식 포인트는 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!