MySQL 데이터베이스 최적화(4) - MySQL 인덱스 최적화

1. 인덱스 기본
인덱스 유형:
1. 일반 인덱스: 모든 데이터 유형으로 생성됩니다.
2. 고유 인덱스: 제한된 인덱스 값 고유해야 합니다
3. 전체 텍스트 인덱스: char, varchar 및 텍스트 유형 필드에서만 생성할 수 있으며 주로 텍스트 쿼리 속도를 향상시키는 데 사용됩니다. MyISAM 엔진 지원.
4. 단일 열 인덱스: 테이블의 단일 필드에 대한 인덱스 생성
5. 다중 열 인덱스: 여러 필드에 대한 인덱스 생성
6. 공간 인덱스: 제공할 공간 매개변수를 사용하여 생성 제어 데이터 획득 효율이 높은 시스템
인덱스의 기본 동작:

CREATE TABLE t_user1(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX (userName)  
             );
             
CREATE TABLE t_user2(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     UNIQUE INDEX index_userName(userName)
             );
           
CREATE TABLE t_user3(id INT ,
                     userName VARCHAR(20),
                     PASSWORD VARCHAR(20),
                     INDEX index_userName_password(userName,PASSWORD)//多列索引
             );
             
CREATE  INDEX index_userName ON t_user4(userName);--对已经创建的表指定索引


CREATE  UNIQUE INDEX index_userName ON t_user4(userName);


CREATE  INDEX index_userName_password ON t_user4(userName,PASSWORD);


ALTER TABLE t_user5 ADD INDEX index_userName(userName);--修改索引


ALTER TABLE t_user5 ADD UNIQUE INDEX index_userName(userName);


ALTER TABLE t_user5 ADD INDEX index_userName_password(userName,PASSWORD);


DROP INDEX index_userName ON t_user5;


DROP INDEX index_userName_password ON t_user5;

인덱스를 추가하면 쿼리 효율성이 향상되고 전체 테이블 데이터 쿼리를 피하고 대신 인덱스 검색을 통해 대상 데이터를 찾으세요. t.id=5인 사용자 t에서 t.name을 선택합니다. actor_id 열에 인덱스가 생성되면 mysql은 해당 인덱스를 사용하여 id=5인 행을 찾습니다. 즉, 먼저 인덱스의 값으로 검색합니다. 를 선택한 다음 해당 값에 대한 데이터 행을 포함하는 모든 행을 반환합니다.
인덱스에는 하나 이상의 열이 포함될 수 있습니다. 인덱스에 여러 열이 포함된 경우 MySQL은 인덱스의 가장 왼쪽 접두사 열만 효율적으로 사용할 수 있으므로 열의 순서도 매우 중요합니다. 또한 인덱스를 생성하고 유지 관리하려면 쿼리 효율성을 높이기 위해 효율적인 인덱스를 생성하는 방법과 관련된 시스템 리소스도 필요합니다.
2. MySQL 인덱스 종류
일반적으로 데이터 인덱스 자체도 매우 크고 메모리에 다 저장하는 것이 불가능하기 때문에 인덱스를 자주 사용하는 경우가 있다. 인덱스 파일을 기반으로 양식이 디스크에 저장됩니다. 이 경우 메모리 접근에 비해 인덱스 검색 과정에서 디스크 I/O 소모가 발생하므로 인덱스의 구조적 구성은 검색 과정에서 디스크 I/O 접근 횟수를 최소화해야 한다. 이를 위해서는 데이터베이스 인덱스를 구성하고 저장하기 위한 고품질 데이터 구조가 필요합니다.
일반적인 인덱스 유형은 데이터 구조 알고리즘을 사용합니다. 예를 들어 B-Tree는 MySQL의 인덱스 유형이자 동적 검색 트리인 이진 검색 트리 및 균형 이진 검색 트리, 이진 트리 구조입니다. B-트리/B+-트리/B*-트리(B~Tree).
이러한 MySQL 인덱스 유형은 데이터 구조 알고리즘의 구현을 기반으로 하기 때문에 이를 기반으로 일반적인 개발이 적용되기 시작합니다. 다양한 데이터 구조를 기반으로 생성된 인덱스의 적용 방식도 다릅니다.

3. 인덱스 사용에 대한 일반적인 오해
1. >
의 잘못된 사용: t.age+1=5인 사용자 t에서 t.name을 선택합니다. 이 조건의 age+1=5에서는 mysql이 age에 대해 인덱스를 생성하더라도 자동으로 구문 분석할 수 없습니다. 열의 경우 mysql은 인덱스 쿼리가 쿼리 중에 사용되지 않으며 전체 테이블 스캔이 계속 수행됩니다.
의 잘못된 사용: TO_DAYS(`CURRENT_DATE`())-TO_DAYS(date_col)<=10에서 사용자 t로부터 t.name을 선택합니다.
올바른 원칙: 항상 비교 연산자의 다른 쪽에 인덱스 열을 배치하세요. 2. 다중 열 인덱스

흔히 저지르는 실수: 열 없이 독립적인 인덱스를 만들거나, 잘못 여러 열을 만들었습니다. order Index를 사용하거나 단순히 where 조건의 모든 열을 색인화합니다.
올바른 사용: 다양한 인덱스 유형에 적합한 인덱스 순서를 선택하세요. 예를 들어 사용자 t에서 t.name을 선택하세요. 여기서 t.staffId는 = 2 and custom_id=7;위 쿼리에 대한 응답으로 (staffId, custom_id)를 생성해야 할까요, 아니면 이 두 열의 순서를 반대로 해야 할까요?


B-Tree를 예로 들면 인덱스 열의 순서는 가장 왼쪽 열부터 먼저 왼쪽에서 오른쪽으로 정렬된다는 의미입니다. 일반적으로 필터 조건의 빈도가 가장 높은 필드를 인덱스 앞에 배치하는 것이 좋습니다. 이러한 방식으로 설계된 인덱스는 필요한 행을 최대한 빨리 필터링할 수 있습니다.

4. 요약

이전에 ORM 프레임워크를 사용하여 했던 Java 클라우드 플랫폼 프로젝트를 살펴보세요. 당시 프로젝트의 계단식 쿼리가 느려서 JDBC+ORM 결합 양식 프로그래밍을 결합했습니다. 이제 여러 데이터베이스의 테이블 디자인을 살펴보겠습니다. , 그리고 인덱스 최적화를 위해서는 여전히 다시 적용해야 합니다. 일반적으로 생성된 인덱스는 단일 컬럼 인덱스만 사용하고 기본 키를 사용하여 생성되지만, 쿼리 시간이 많이 걸리는 문제가 발생하는 경우에는 테이블 구조 최적화, 인덱스 최적화 및 쿼리 최적화가 함께 진행되어야 하지만 ORM 프레임워크 재선택 또는 재최적화에만 의존하면 문제를 해결할 수 없습니다.

위 내용은 MySQL 데이터베이스 최적화(4) - MySQL 인덱스 최적화 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!