MySQL 인덱싱 및 쿼리 최적화에 대한 자세한 소개

이 기사는 MySQL 인덱싱 및 쿼리 최적화에 대한 자세한 소개를 제공합니다. 필요한 친구가 참고할 수 있기를 바랍니다.

문서 "MySQL 쿼리 분석"에서는 MySQL의 느린 쿼리 및 explain 명령을 사용하여 성능 병목 현상이 있는 SQL 문을 찾은 후 비효율적인 SQL 문을 최적화해야 합니다. 이 기사에서는 주로 MySQL 인덱스 원칙과 일반적으로 사용되는 SQL 쿼리 최적화에 대해 설명합니다.

간단한 비교 테스트

이전의 경우 c2c_zwdb.t_file_count 테이블에는 자동 증가 ID가 하나만 있고, 인덱싱이 없는 FFileName 필드의 sql 실행 상황은 다음과 같습니다.

위 그림에서는 =all, key=null, 행=33777을 입력합니다. 이 SQL은 인덱스를 사용하지 않으며 매우 비효율적인 전체 테이블 스캔입니다. 공동 쿼리 및 기타 제약 조건이 추가되면 데이터베이스는 메모리를 과도하게 소비하고 프런트 엔드 프로그램 실행에 영향을 미칩니다.

이제 FFileName 필드에 인덱스를 추가합니다.

alter table c2c_zwdb.t_file_count add index index_title(FFileName);

위 쿼리 문을 다시 실행하면 대조가 뚜렷해집니다.

이 그림에서, 유형=ref, 키=색인 이름(index_title), 행=1. 이 SQL은 index_title 인덱스를 사용하며, 인덱스를 기준으로 한 행만 스캔합니다.

인덱싱을 하지 않은 상황과 비교하면, 인덱스를 추가한 후 쿼리 효율성은 매우 분명합니다.

MySQL Index

위 비교 테스트에서 알 수 있듯이 빠른 검색의 핵심은 인덱싱입니다. MySQL 인덱스의 구축은 MySQL의 효율적인 운영을 위해 매우 중요합니다. 데이터의 양이 적을 경우 적절한 인덱스가 없을 때의 영향은 크지 않지만, 데이터의 양이 늘어나면 성능이 급격히 떨어지게 됩니다. 여러 열이 인덱스된 경우(결합 인덱스) 열의 순서가 매우 중요하며 MySQL은 인덱스의 가장 왼쪽 접두사에서만 효과적인 검색을 수행할 수 있습니다.

다음은 몇 가지 일반적인 MySQL 인덱스 유형을 소개합니다.

인덱스는 단일 열 인덱스와 결합 인덱스로 구분됩니다. 단일 열 인덱스는 인덱스가 단일 열만 포함한다는 의미입니다. 테이블은 여러 개의 단일 열 인덱스를 가질 수 있지만 이는 결합된 인덱스가 아닙니다. 결합 인덱스, 즉 인덱스에는 여러 열이 포함됩니다.

1. MySQL 인덱스 유형

(1) 기본 키 인덱스 PRIMARY KEY

null 값을 허용하지 않는 특수한 고유 인덱스입니다. 일반적으로 기본 키 인덱스는 테이블 생성과 동시에 생성됩니다.

물론 ALTER 명령을 사용할 수도 있습니다. 기억하세요: 테이블에는 기본 키가 하나만 있을 수 있습니다.

(2) 고유 인덱스 UNIQUE

고유 인덱스 열의 값은 고유해야 하지만 null 값은 허용됩니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다. 테이블을 생성할 때 이를 지정하거나 다음과 같이 테이블 구조를 수정할 수 있습니다.

ALTER TABLE table_name ADD UNIQUE (column)table_name ADD UNIQUE (column)

(3) 普通索引 INDEX

这是最基本的索引，它没有任何限制。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD INDEX index_name (column)

(4) 组合索引 INDEX

组合索引，即一个索引包含多个列。可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3)

(5) 全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

可以在创建表的时候指定，也可以修改表结构，如：

ALTER TABLE table_name ADD FULLTEXT (column

(3) 일반 인덱스 INDEX

가장 기본적인 인덱스로 제한이 없습니다. 테이블을 생성할 때 이를 지정하거나 다음과 같이 테이블 구조를 수정할 수 있습니다.

ALTER TABLE table_name ADD INDEX index_name (column)

(4 ) 결합 인덱스 INDEX

결합 인덱스, 즉 인덱스에는 여러 열이 포함됩니다. 테이블을 생성할 때 이를 지정하거나 다음과 같이 테이블 구조를 수정할 수 있습니다. ALTER TABLE table_name ADD INDEX index_name(column1, column2, <code>column3)

🎜🎜(5) 전체 텍스트 인덱스 FULLTEXT🎜🎜🎜전체 텍스트 인덱스(전체 텍스트 검색이라고도 함)는 현재 검색 엔진에서 사용하는 핵심 기술입니다. 단어 분할 기술과 같은 다양한 알고리즘을 사용하여 텍스트에서 핵심 단어의 빈도와 중요성을 지능적으로 분석한 다음 특정 알고리즘 규칙에 따라 우리가 원하는 검색 결과를 지능적으로 필터링할 수 있습니다. 🎜🎜테이블을 생성할 때 이를 지정하거나 다음과 같이 테이블 구조를 수정할 수 있습니다. 🎜🎜ALTER TABLE table_name ADD FULLTEXT (column)🎜🎜2. 인덱스 구조 및 원리 🎜🎜B+Tree는 mysql에서 인덱스로 흔히 사용되지만 클러스터형 인덱스와 비클러스터형 인덱스에 따라 구현 방식이 다릅니다. 🎜🎜🎜b+ 트리 소개🎜🎜🎜아래 b+ 트리 사진은 여러 곳에서 볼 수 있는데, 여기서 이 사진을 선택한 이유는 이 사진이 인덱스 검색 과정을 아주 잘 설명할 수 있다고 생각하기 때문입니다. 🎜🎜🎜🎜🎜🎜

위에서 보듯이 B+트리입니다. 연한 파란색 블록을 디스크 블록이라고 합니다. 각 디스크 블록에는 여러 데이터 항목(짙은 파란색으로 표시)과 포인터(노란색으로 표시)가 포함되어 있습니다. 예를 들어 디스크 블록 1에는 데이터 항목 17과 35가 포함되어 있습니다. P1, P2, P3은 P1은 17보다 작은 디스크 블록을 나타내고, P2는 17에서 35 사이의 디스크 블록을 나타내며, P3은 35보다 큰 디스크 블록을 나타냅니다.

실제 데이터는 리프 노드, 즉 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99에 존재합니다. Non-leaf 노드는 실제 데이터를 저장하지 않고 검색 방향을 안내하는 데이터 항목만 저장합니다. 예를 들어 17과 35는 실제로 데이터 테이블에 존재하지 않습니다.

프로세스 찾기

위 그림에서 데이터 항목 29를 찾으려면 디스크 블록 1이 먼저 디스크에서 로드됩니다. 메모리에 IO가 발생하면 이진 검색을 사용하여 메모리에서 29가 17에서 35 사이인지 확인합니다. 디스크 블록 1의 P2 포인터를 잠급니다. 메모리 시간은 디스크 IO에 비해 매우 짧기 때문에 무시할 수 있습니다. ) 디스크 블록 1을 통해 P2 포인터의 디스크 주소는 디스크에서 메모리로 29가 발생합니다. 디스크 블록 3의 P2 포인터는 잠겨 있습니다. 세 번째 IO 횟수가 발생하고 동시에 메모리에서 이진 검색을 수행하여 29를 찾아 쿼리가 종료되며 총 3번의 IO가 발생합니다. 실제 상황은 3계층 b+ 트리가 수백만 개의 데이터를 나타낼 수 있다는 것입니다. 수백만 개의 데이터 검색에 3개의 IO만 필요한 경우 인덱스가 없으면 각 데이터 항목에 하나의 IO가 있어야 합니다. , 그러면 총 수백만 개의 IO가 필요하며 비용은 분명히 매우 높습니다.

Property

(1) 인덱스 필드는 최대한 작아야 합니다.

위 b+ 트리의 검색 과정을 통해, 또는 리프 노드에 실제 데이터가 존재한다는 사실을 통해 b+ 수의 높이 h에 따라 IO 개수가 달라지는 것을 알 수 있습니다.

현재 데이터 테이블의 데이터 볼륨을 N, 각 디스크 블록의 데이터 항목 수를 m이라고 가정하면, 트리 높이 h=㏒(m+1)N, 데이터 볼륨 N은 일정합니다. m이 클수록 h는 작아집니다. 차지하는 공간이 작을수록 데이터 항목의 개수 m이 늘어나고 트리의 높이 h가 낮아집니다. 이것이 바로 각 데이터 항목, 즉 인덱스 필드가 최대한 작아야 하는 이유입니다. 예를 들어 int는 4바이트를 차지하며 이는 bigint 8바이트보다 절반 작은 크기입니다.

(2) 인덱스의 가장 왼쪽 일치 기능입니다.

b+ 트리의 데이터 항목이 (이름, 나이, 성별)과 같은 복합 데이터 구조인 경우 b+ 번호를 사용하여 왼쪽에서 오른쪽으로 검색 트리를 구성합니다. when(Zhang San, 20, F) 등의 데이터를 검색할 때 b+ 트리는 먼저 이름을 비교하여 다음 검색 방향을 결정합니다. 이름이 동일하면 연령과 성별을 차례로 비교하여 검색된 데이터입니다. (20,F) 이름이 없는 데이터가 오면 b+ 트리는 다음에 어떤 노드를 확인할지 알 수 없습니다. 왜냐하면 이름은 검색 트리를 구성할 때 첫 번째 비교 요소이고 먼저 다음을 기반으로 검색해야 하기 때문입니다. 다음 단계를 알 수 있는 이름입니다. 예를 들어 (Zhang San, F)와 같은 데이터를 검색할 때 b+ 트리는 name을 사용하여 검색 방향을 지정할 수 있지만 다음 필드 age가 누락되어 이름이 Zhang San과 동일한 모든 데이터만 찾을 수 있습니다. 그리고 성별을 일치시키는 것이 F의 데이터입니다. 이는 매우 중요한 속성, 즉 인덱스의 가장 왼쪽 일치 특성입니다.

인덱스 작성의 여러 원칙

(1) 가장 왼쪽 접두사 일치 원칙

다중 열 인덱스의 경우 , 항상 인덱스의 앞 필드에서 시작하여 계속 진행되며 중간을 건너뛸 수 없습니다. 예를 들어 다중 열 인덱스(이름, 나이, 성별)를 생성하는 경우 이름 필드가 먼저 일치하고 그 다음 나이 필드, 성별 필드가 일치하며 중간은 건너뛸 수 없습니다. MySQL은 범위 쿼리(>,

일반적으로 다중 열 인덱스를 생성할 때 where 절에서 가장 자주 사용되는 열이 가장 왼쪽에 배치됩니다.

가장 왼쪽 접두사 일치 원칙을 준수하고 이 원칙을 준수하는 보완의 비교 예를 살펴보세요.

예: c2c_db.t_credit_detail 테이블에 인덱스가 있습니다(

)

Flistid,Fbank_listid

#🎜🎜 #가장 왼쪽 접두사 일치 원칙을 따르지 않는 SQL 문:

select * from t_credit_detail where Fbank_listid='201108010000199'G

이 SQL은 두 번째 인덱스 필드 Fbank_listid를 직접 사용합니다. 첫 번째 인덱스 필드 Flistid는 건너뛰는데, 이는 가장 왼쪽 접두사 일치 원칙을 따르지 않습니다. explain 명령어를 사용하여 아래와 같이 sql 문의 실행 계획을 볼 수 있습니다. 위 그림에서 보면 sql No index를 사용하고 있는데 이는 비효율적인 Full Table Scan이다.

가장 왼쪽 접두사 일치 원칙을 따르는 SQL 문:

select * from t_credit_detail where Flistid='2000000608201108010831508721' and Fbank_listid='201108010000199' G

이 SQL은 먼저 인덱스의 첫 번째 필드 Flistid를 사용한 다음 인덱스의 두 번째 필드 Fbank_listid를 사용합니다. 이는 가장 왼쪽 접두사 일치 원칙을 준수하는 중간 건너뛰기가 없습니다. explain 명령어를 사용하면 아래와 같이 sql 문의 실행 계획을 볼 수 있습니다.

위 그림에서 볼 수 있듯이 sql은 인덱스를 사용하여 한 행만 스캔합니다.

비교를 통해 전체 테이블 스캔부터 상수 스캔까지 가장 왼쪽 접두사 일치 원칙을 준수하는 SQL 문의 효율성이 이 원칙을 준수하지 않는 SQL 문에 비해 크게 향상됨을 알 수 있습니다.

(2) 차별화가 높은 열을 인덱스로 선택해 보세요.

예를 들어 색인 생성을 위해 학생 번호를 선택하지만 성별은 선택하지 않습니다.

(3) = and in은 순서가 잘못될 수 있습니다

예를 들어 a = 1, b = 2, c = 3인 경우 (a, b, c) 인덱스는 순서에 관계없이 생성될 수 있으며 mysql 쿼리는 최적화 프로그램은 이를 인식 가능한 인덱스 형식으로 최적화하는 데 도움이 됩니다.

(4) 인덱스 열은 계산에 참여할 수 없습니다. 열을 "깨끗하게" 유지하세요.

예: Flistid+1>'2000000608201108010831508721'. 이유는 매우 간단합니다. 인덱스 열이 계산에 참여하면 인덱스를 검색할 때마다 인덱스를 한 번 계산한 다음 비교하게 되므로 비용이 너무 많이 듭니다.

(5) 인덱스를 최대한 확장하고 새 인덱스를 생성하지 마세요.

예를 들어, 테이블에 이미 a의 인덱스가 있는데 이제 (a, b)의 인덱스를 추가하려는 경우 원래 인덱스만 수정하면 됩니다.

인덱스의 단점

인덱스는 쿼리 효율성을 향상시킬 수 있지만 인덱스에도 단점이 있습니다.

인덱스의 추가 오버헤드:

(1) 공간: 인덱스가 공간을 차지해야 합니다.

(2) 시간: 인덱스를 쿼리하는 데 시간이 필요합니다.

(3) 유지 관리: 인덱스를 유지해야 합니다(데이터가 필요한 경우). 변경 사항)

아니요 인덱스 사용을 권장합니다.

(1) 데이터 양이 적은 테이블

(2) 공간이 부족함

일반적으로 사용되는 최적화 요약

많은 최적화 문이 있으며, 주의해야 할 사항이 많습니다. 일반적인 상황에 따른 몇 가지 사항은 다음과 같습니다.

1. 인덱스가 있지만 사용되지 않습니다(권장되지 않음)

(1) 좋아요의 매개변수가 시작될 때 와일드카드 문자로

와일드카드 문자로 시작하는 것과 같은 매개변수를 피하십시오. 그렇지 않으면 데이터베이스 엔진이 인덱스 사용을 포기하고 테이블 스캔 전체 작업을 수행합니다.

와일드카드로 시작하는 SQL 문, 예: select * from t_credit_detail 여기서 Flistid는 '%0'G

이것은 전체 테이블 스캔이며 인덱스가 사용되지 않으며 권장되지 않습니다.

와일드카드로 시작하지 않는 SQL 문, 예: select * from t_credit_detail where Flistid like '2%'G

분명히 이것은 다음으로 시작하는 것보다 범위 검색인 인덱스를 사용합니다. 와일드카드 SQL 문의 효율성이 크게 향상되었습니다.

(2) where 조건이 가장 왼쪽 접두어 일치 원칙을 따르지 않는 경우

가장 왼쪽 접두어 일치 원칙의 내용에 예가 나와 있습니다.

(3) 활용해보세요! = 또는 연산자를 사용하지 마세요

! = 또는 연산자를 사용하지 않으면 데이터베이스 엔진이 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다. >를 사용하는 것이 더 효율적입니다. *t_credit_detail에서 * 선택 Where Flistid! = '20000006082011080831508721'g

(4) 인덱스 계산

where에 필드를 표현하는 것은 피하세요. 그러면 엔진이 포기하게 됩니다. 인덱스를 사용하여 전체 테이블 스캔을 수행합니다.

select * from t_credit_detail where Flistid +1 > '2000000608201108010831508722'G

(5) where 절에서는 필드에 대한 Null 값 판단을 최대한 피해야 합니다. 그렇지 않으면 엔진이 작동하지 않게 됩니다. 인덱스 사용을 포기하고 다음과 같이 전체 테이블 스캔을 수행합니다. 비효율성: Flistid가 null인 t_credit_detail에서 * 선택;

Flistid에 기본값을 0으로 설정할 수 있습니다. Flistid 열에 null 값이 없는지 확인하세요. 효율적: select * from t_credit_detail where Flistid =0;

(6) 조건 연결에 또는 사용

조건 연결을 위해 where 절에서 또는 사용을 피해야 합니다. 그렇지 않으면 엔진 인덱스 사용을 포기하고 전체 테이블 스캔을 수행합니다. 예: 비효율적: select * from t_credit_detail where Flistid = '2000000608201108010831508721' 또는 Flistid = '10000200001';

다음 쿼리를 사용하여 위 쿼리를 바꿀 수 있습니다. 효율적: Flistid = '2000000 608010831508721'인

from t_credit_detail 선택 t_credit_detail에서 모두 선택

Flistid = '10000200001';

2 *

선택을 피하세요. 파싱 과정에서 '*'가 순차적으로 변환됩니다. 모든 열 이름을 얻으려면 데이터 사전을 쿼리하여 이 작업을 수행하므로 시간이 더 걸립니다.

그래서 필요한 것은 무엇이든 가져가는 좋은 습관을 길러야 합니다.

3. Order by 문 최적화

Order by 문에 색인이 아닌 항목이나 계산 표현식이 있으면 쿼리 속도가 느려집니다.

방법: 1. 인덱스를 사용하도록 명령문별로 순서를 다시 작성합니다.

  2.为所使用的列建立另外一个索引

  3.绝对避免在order by子句中使用表达式。

4 GROUP BY 문 최적화

GROUP BY 문을 사용하면 불필요한 레코드를 GROUP BY

GROUP by JOB

HAVING JOB = 'PRESIDENT'

OR 앞에 배치하여 필터링할 수 있습니다. 작업 = '관리자'

효율성:# 🎜🎜#

작업 선택, AVG(SAL)

EMP에서

작업 위치 = 'PRESIDENT'

OR JOB = ' MANAGER'

GROUP by JOB

5 대신 존재합니다. 🎜#in 대신 presents를 사용하는 것이 여러 번 좋은 선택입니다: select num from a where num in(select num from b) 다음 명령문으로 바꿉니다: select num from a where presents(select 1 from b where num=a .num)

6. char /nchar

char/nchar 대신 varchar/nvarchar를 최대한 사용하세요. 길이 필드는 저장 공간이 작고 저장 공간을 절약할 수 있습니다. 둘째, 쿼리의 경우 상대적으로 작은 필드에서 검색 효율성이 확실히 더 높습니다.

7. DISTINCT를 사용할 수 있으면 GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

#🎜🎜 #은 다음과 같이 변경할 수 있습니다.

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

8을 사용할 수 있으면 UNION

을 사용하지 마세요.

UNION ALL은 SELECT DISTINCT 함수를 실행하지 않으므로 불필요한 리소스가 많이 줄어듭니다.

9. 테이블 조인 시 동일한 유형을 사용하고 인덱스를 생성합니다.

애플리케이션에 JOIN 쿼리가 많은 경우 인덱스된 두 테이블 필드의 조인 수를 확인해야 합니다. 이러한 방식으로 MySQL은 Join SQL 문을 최적화하는 메커니즘을 시작합니다.

또한 Join에 사용되는 필드는 동일한 유형이어야 합니다. 예: DECIMAL 필드를 INT 필드와 결합하면 MySQL은 해당 인덱스를 사용할 수 없습니다. 해당 STRING 유형의 경우에도 동일한 문자 집합이 있어야 합니다. (두 테이블의 문자 집합은 다를 수 있습니다.)

이 기사는 여기서 끝납니다. MySQL에 대한 자세한 내용은 PHP 중국어 웹사이트 🎜🎜#column에서

MySQL 튜토리얼#을 따르세요. ! ! !

위 내용은 MySQL 인덱싱 및 쿼리 최적화에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!