MySQL 데이터베이스의 인덱싱 원리와 최적화 전략은 무엇입니까?

1 인덱스

인덱스 개념

인덱스는 데이터 테이블의 모든 레코드에 대한 참조 포인터가 포함된 특수 파일입니다. 테이블에 있는 하나 이상의 열에 인덱스를 생성하고 인덱스 유형을 지정할 수 있습니다. 각 인덱스 유형에는 고유한 데이터 구조 구현이 있습니다.

색인의 역할

데이터베이스의 테이블, 데이터, 색인의 관계는 책장에 있는 책, 책 콘텐츠, 책 카탈로그의 관계와 유사합니다. 색인은 책 카탈로그와 유사한 역할을 하며, 데이터를 신속하게 찾고 검색하는 데 사용됩니다. 인덱스는 데이터베이스 성능을 크게 향상시킬 수 있습니다.

인덱스 사용 시나리오

데이터베이스 테이블의 특정 열에 대한 인덱스 생성을 고려하려면 다음 사항을 고려해야 합니다.

• 데이터 양이 많고 이러한 항목에 대해 조건부 쿼리가 자주 수행됩니다. 열.

• 이 데이터베이스 테이블의 해당 열에 대한 삽입 작업 및 수정 작업 빈도는 낮습니다.

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

2 인덱스 분류

• 인덱스 저장 구조로 구분: BTree 인덱스, Hash 인덱스, FULLTEXT 전체 텍스트 인덱스, RTree 인덱스

• 응용 프로그램 수준으로 구분: 일반 인덱스, 고유 인덱스, 기본 키 인덱스, 복합 인덱스

• 인덱스 키 값 종류로 구분, 기본 키 인덱스, 보조 인덱스(보조 인덱스)

• 데이터 저장과 인덱스 키 값의 논리적 관계로 구분: 클러스터형 인덱스(clustered index) 비 -클러스터형 인덱스(비클러스터형 인덱스) 인덱스)

• 인덱스 열 수로 구분: 단일 열 인덱스, 복합 인덱스

B-트리 인덱스와 B+ 트리 인덱스의 차이점

차이점:

데이터 저장 위치가 다릅니다. B+ 트리 저장 리프 노드에서는 모든 노드에 B-트리가 저장되는데, 이는 B+ 트리의 장점을 반영합니다. 노드는 데이터를 저장하지 않으므로 하나의 노드에 저장됩니다. 노드는 더 많은 키를 저장할 수 있습니다. 트리를 더 짧게 만들 수 있으므로 IO 작업 수가 줄어듭니다. 쿼리 성능은 안정적입니다. 각 쿼리는 루트 노드에서 리프 노드로 이동하며 쿼리 경로 길이는 동일합니다. 즉, 각 쿼리는 동일하게 효율적이며 시간 복잡도는 O(log(n))

로 고정됩니다. 리프 노드 지정: B+ 트리 인접한 리프 노드는 포인터를 통해 연결됩니다. B-트리에는

B+ 트리의 장점이 반영되지 않습니다. 모든 리프 노드는 정렬된 연결 리스트를 형성하므로 범위 검색이 용이합니다.

3 인덱스 작업

기본 키 인덱스 만들기

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
-- 创建表以后再添加主键
create table user3(id int, name varchar(30));
alter table user3 add primary key(id);

기본 키 인덱스의 특징:

테이블에는 최대 하나의 기본 키 인덱스가 있습니다. 물론 기본 키 인덱스를 매우 효율적으로 만들 수 있습니다(기본 키는 반복될 수 없습니다)
기본 키 인덱스의 열을 생성합니다. 해당 값은 null일 수 없으며 반복될 수 없습니다.
기본 키 인덱스의 열은 기본적으로 int입니다.
고유 인덱스 생성

• -- 在表定义时，在某列后直接指定unique唯一属性。
  create table user4(id int primary key, name varchar(30) unique);
  -- 创建表时，在表的后面指定某列或某几列为unique
  create table user5(id int primary key, name varchar(30), unique(name));
  -- 创建表以后再添加unique
  create table user6(id int primary key, name varchar(30)）；
  alter table user6 add unique(name);

  unique의 특징 index:

한 테이블에 고유 인덱스가 여러 개 있을 수 있음

쿼리 효율성 높음
특정 열에 고유 인덱스를 생성하는 경우 해당 열에 중복된 데이터가 없도록 해야 합니다
고유 인덱스에 not null을 지정하면 기본 키 인덱스를 생성하는 것과 같습니다.
일반 인덱스

• --在表的定义最后，指定某列为索引
  create table user8(id int primary key,
  name varchar(20),
  email varchar(30),
  index(name)
  );
  --创建完表以后指定某列为普通索引
  create table user9(id int primary key, name varchar(20), email varchar(30));
  alter table user9 add index(name); 
  -- 创建一个索引名为 idx_name 的索引
  create table user10(id int primary key, name varchar(20), email varchar(30));
  create index idx_name on user10(name)；

  일반 인덱스의 특징:

한 테이블에 여러 개의 일반 인덱스가 있을 수 있습니다. . 실제 개발에서는 일반 인덱스가 더 일반적으로 사용됩니다

열을 인덱싱해야 하는데 해당 열에 중복된 값이 있으면 일반 인덱스를 사용해야 합니다
쿼리 인덱스
테이블의 키 표시 name

• mysql> 상품G

  ********** 1. 행 **** *******

  테이블: 상품 <= 테이블 이름
Non_unique: 0 <= 0은 고유 인덱스를 의미

Key_name: PRIMARY <= 기본 키 인덱스

Seq_in_index: 1

Column_name: products_id <= 인덱스 열은 어디에 있습니까
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE <= 이진 트리 형식의 인덱스
Comment:
1 행(0.00초)




테이블 이름의 인덱스 표시;

desc 테이블 이름
인덱스 삭제
기본 키 인덱스 삭제: 테이블 이름 변경 기본 키 삭제,

다른 인덱스 삭제: 테이블 테이블 이름 삭제 인덱스 인덱스 이름 변경, 인덱스 이름은 테이블 이름의 키 표시에 있는 Key_name 필드입니다.

mysql> alter table user10 drop index idx_name;

• 테이블 이름에 인덱스 인덱스 이름을 삭제합니다

• mysql> drop index name on user8

  인덱스 생성 원칙
  • 쿼리 조건으로 자주 사용되는 필드는 인덱스를 생성해야 합니다.

  고유성이 낮은 필드는 인덱스 생성에 적합하지 않습니다. 단독으로 쿼리 조건으로 자주 사용되더라도
  • 매우 자주 업데이트되는 필드는 인덱스 생성에 적합하지 않습니다

  • where 절에 표시되지 않는 필드는 인덱스를 생성하면 안 됩니다

  위 내용은 MySQL 데이터베이스의 인덱싱 원리와 최적화 전략은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!