MySQL 쿼리 최적화에 대한 자세한 소개

1. 소개

좋은 웹 애플리케이션에서 가장 중요한 것은 뛰어난 접속 성능을 갖는 것입니다. 데이터베이스 MySQL은 웹 애플리케이션의 필수적인 부분이자 성능을 결정하는 중요한 부분입니다. 따라서 MySQL의 성능을 향상시키는 것이 중요합니다.

MySQL 성능 향상은 하드웨어, 네트워크, 소프트웨어 세 부분으로 나눌 수 있습니다. 그 중 하드웨어와 네트워크는 회사의 재정 자원에 의존하고 많은 돈이 필요하므로 여기서는 다루지 않겠습니다. 소프트웨어는 여러 유형으로 세분화됩니다. 여기서는 MySQL 쿼리 최적화를 통해 성능 향상을 달성합니다.

최근 쿼리 최적화에 관한 책을 읽었고, 온라인에서 선배들이 쓴 글도 읽었습니다.

다음은 내가 컴파일하고 배운 쿼리 최적화에 대한 몇 가지 요약입니다.

2. SQL 문 가로채기

1. 종합 쿼리 로그

2 . 느린 쿼리 로그

3. 바이너리 로그

4. 프로세스 목록

SHOW FULL PROCESSLIST;

. . .

3. 쿼리 최적화를 위한 기본 분석 명령

1. EXPLAIN {PARTITIONS|EXTENDED}

2. SHOW CREATE TABLE 탭;

3. SHOW 탭의 인덱스;

　4. 'tab'과 같은 테이블 상태 표시;

　5. '''와 같은 [GLOBAL|SESSION] 상태 표시;

　6. 변수 표시

​. . . .

ps: 개인적으로는 모두 영양학적으로 전혀 영양분이 없는 것 같아요. 여기에 실제 내용이 있습니다.

4. 쿼리 최적화를 위한 여러 방향

1. 전체 텍스트 스캔을 피하고 해당 필드에 인덱스를 추가하고 쿼리에 인덱스를 사용하세요

2. 사용하지 않는 항목 삭제 또는 중복 인덱스

3. 쿼리 재작성, 등가 변환(predicate, subquery, Join 쿼리)

4. 내용을 반복하는 불필요한 문을 삭제하고 문을 간소화합니다

5. 통합 반복적으로 실행되는 문

6. 쿼리 결과 캐시

5. 인덱스 최적화

5.1. 인덱스 장점:

1. 데이터 무결성 유지

　 2. 데이터 쿼리 성능 향상

3. 테이블 연결 연산(jion) 향상

4. 쿼리 결과 정렬. 인덱스가 없으면 내부 파일 정렬 알고리즘을 사용하여 정렬하는 것이 더 효율적입니다

5. 집계 데이터 작업을 단순화합니다

5.2 인덱스 단점

1. 인덱스는 일정량의 공간을 차지해야 합니다. 저장 공간

2. 데이터 삽입, 업데이트, 삭제는 인덱스의 영향을 받아 성능이 저하됩니다. 데이터가 변하기 때문에 인덱스도 업데이트해야 합니다

3. 인덱스가 여러 개일 경우 최적화에 시간이 걸리면 최선의 선택은

5.3 인덱스 선택

1 . 데이터의 양이 많은 경우에는

을 사용하세요. 2. 데이터가 반복되는 경우에는

을 사용하지 마세요. 3. 쿼리에서 데이터의 20% 이상을 검색하는 경우에는 전체 텍스트를 사용하세요. 스캐닝은 인덱싱 없이 사용됩니다

5.4. 인덱스에 대한 자세한 연구

데이터 쿼리:

MySQL의 InnoDB 및 MyISAM은 B-Tree 유형 인덱스입니다

B-Tree에는 PRIMARY KEY, UNIQUE, INDEX 및 FULLTEXT가 포함됩니다

B-Tree 유형 인덱스는 지원되지 않습니다(즉, 필드에서 다음 기호를 사용하는 경우 인덱스가 사용되지 않습니다).

　>, <, >=, <=, BETWEEN, !=, < >,like '%**'

　【먼저 커버링 인덱스를 소개하겠습니다】

　제가 알기 쉽게 소개해보겠습니다. 포함 인덱스는 기본 키 인덱스 및 고유 인덱스처럼 실제로 존재하지 않습니다. 이는 인덱스 적용에 대한 특정 특정 시나리오의 정의일 뿐입니다. [또 다른 이해: 쿼리된 열은 인덱스 열이므로 열이 인덱스에 포함됩니다.] 기존의 한계를 극복하고 위의 연산자를 사용하며 쿼리에 인덱스를 계속 사용할 수 있습니다.

쿼리된 컬럼은 인덱스 컬럼이므로 행을 읽을 필요는 없고 컬럼 필드 데이터만 읽으면 됩니다. [예를 들어, 책을 읽고 있는데 특정 내용을 찾아야 하는데 그 내용이 우연히 목차에 나타나면 페이지를 한 장씩 넘길 필요 없이 책의 표에서 페이지를 찾으면 됩니다. 내용 및 검색]

활성화 방법 커버링 인덱스는 어떻게 되나요? 구체적인 시나리오란 무엇입니까?

인덱스 필드가 선택 항목에만 나타납니다.

복합 인덱스에는 다른 특수 시나리오도 있을 수 있습니다. 예를 들어, 3열 복합 인덱스의 경우 포함 인덱스 사용을 활성화하려면 복합 인덱스의 가장 왼쪽 열이 select, where, group by 및 order by에서 한 번만 나타나도록 하면 됩니다.

보기:

EXPLAIN의 추가 항목은 Using index를 표시하여 이 명령문이 포함 인덱스를 사용함을 나타냅니다.

결론:

쿼리 시 select*from을 사용하는 것은 권장되지 않습니다. 쿼리 성능을 향상하려면 필수 필드를 작성하고 해당 인덱스를 추가해야 합니다.

위 연산자에 대한 실제 측정 결과:

1. select*from 형식에서 where의 기본 키를 사용하여 [like 제외]를 삭제할 수 있습니다(기본 키를 사용하여 쿼리); 인덱스가 전혀 작동하지 않습니다.

2. "위 연산자" 필드가 있는 탭에서 필드 a를 선택하는 형식으로 테스트하면 결과는 여전히 인덱스를 사용하여 쿼리할 수 있습니다. [커버링 인덱스 활용]

　 기타 인덱스 최적화 방법:

1. 인덱스 키워드를 연결 조건으로 사용

2. 복합 인덱스 사용

3. 인덱스 병합 or and, 포함 해당 필드를 복합 인덱스로 병합합니다

4. where에 포함된 필드에 인덱스를 추가하고

별로 그룹화합니다. 6. 하위 쿼리 최적화

from에서 상관되지 않은 하위 쿼리. 하위 쿼리를 상위 레이어로 가져올 수 있습니다. 다중 테이블 조인 쿼리에서는 선택하기 전에 조인 비용을 고려하세요.

쿼리 최적화 프로그램은 일반적으로 하위 쿼리에 대해 중첩 실행, 즉 상위 쿼리의 각 행에 대해 하위 쿼리를 한 번씩 실행하므로 하위 쿼리가 여러 번 실행됩니다. 이 실행 방법은 매우 비효율적입니다.

서브 쿼리를 조인 쿼리로 변환할 때의 장점:

1. 서브 쿼리를 여러 번 실행할 필요가 없습니다.

2. 옵티마이저는 기반에 따라 다양한 방법과 연결 순서를 선택할 수 있습니다. 정보에

3. 하위 쿼리의 연결 조건과 필터링 조건이 상위 쿼리의 필터링 조건이 되어 효율성이 향상됩니다.

최적화:

하위 쿼리 병합. 하위 쿼리가 여러 개인 경우 최대한 병합해 보세요.

하위 쿼리 확장, 즉 풀업은 다중 테이블 쿼리가 됩니다(항상 동일한 변경이 보장됩니다)

참고:

하위 쿼리 확장은 단순 확장만 가능합니다. 하위 쿼리인 경우 쿼리에 집계 함수, GROUP BY, DISTINCT가 포함되어 있으면 끌어올 수 없습니다.

select * from t1(select*from tab where id>10)을 t2로 선택(여기서 t1.age>10 및 t2.age

select*from t1,tab을 t2로 선택). t1.age>10 및 t2.age<25 및 t2.id>10;

특정 단계:

1. 병합, 해당 매개변수 수정

2, where를 where와 병합하고 및를 사용하여 연결

3. 해당 술어를 수정합니다(in이 =로 변경됨)

7. 동등한 술어 다시 작성:

1 . BETWEEEN 및 >=, <= 등으로 다시 작성합니다. 실제 측정: 데이터 100,000개, 다시 쓰기 전후 시간, 1.45s, 0.06s

　2. 다중 또는 변환 시. 필드가 인덱스인 경우 둘 다 인덱스를 사용할 수 있거나

보다 더 효율적입니다. 3. 'abc%'와 같은 이름은 name>='abc' 및 name<'abd';<로 다시 작성됩니다. 🎜 >

참고: 백만 수준 데이터 테스트에서 이름이 색인화되기 전의 쿼리는 후자의 쿼리보다 빠르며, 필드에 인덱스를 추가한 후에는 후자의 쿼리가 조금 더 빠르지만 큰 차이는 없습니다. 인덱스를 쿼리할 때 두 가지 방법이 모두 사용되기 때문입니다.

. . . .

8. 조건부 단순화 및 최적화

1. where, had(groupby 및 집계 기능이 없는 경우), Join-on 조건을 최대한 결합하세요

2. 불필요한 괄호를 삭제하고 구문의 or 및 트리 계층을 줄이고 CPU 소모를 줄입니다

3. 지속적인 전송. a=b 및 b=2는 a=2 및 b=2로 변환됩니다. 변수 a=b 또는 a=@var를 사용하지 마십시오

4. 쓸모없는 SQL 조건을 제거하십시오

5. 등호가 없는 곳의 오른쪽에 있는 표현식을 계산하지 마십시오. 표현식을 계산하고 함수를 사용하는 필드 사용

6. 항등 변환 및 부등 변환. 예: 수백만 개의 데이터 a>b 및 b>10을 테스트하면 상당한 최적화를 통해 a>b 및 a>10 및 b>10이 됩니다

9. 외부 연결 최적화

즉, 외부로 변환하는 것입니다. 연결 내부 조인의 경우

장점:

1. 최적화 프로세서는 내부 조인보다 더 많은 단계로 외부 조인을 처리하며 시간이 많이 걸립니다

2. 외부 조인이 완료된 후 삭제, 최적화 프로그램은 여러 테이블 선택 연결 순서에 대한 선택 사항이 더 많아 가장 적합한 것을 선택할 수 있습니다

3. 가장 엄격한 필터링 조건을 갖춘 테이블을 외관으로 사용할 수 있습니다(연결 순서의 앞부분은 다층 루프 본체의 외부 루프 계층),

불필요한 I/O 오버헤드를 줄이고 알고리즘 실행 속도를 높일 수 있습니다.

　 on a.id=b.id와 where a.id=b.id의 차이는 on이 테이블이 연결된다는 뜻이고 데이터 비교는 어디서 하게 된다는 뜻입니다.

참고: 전제는 결과가 NULL이 엄격히 금지된다는 것입니다(즉, 조건은 NULL 데이터 행으로 제한되지 않으며 의미상 내부 조인입니다)

최적화 원칙:

쿼리 간소화 , 연결 제거, 등가 변환, 중복 테이블 개체 연결 제거

예: 기본 키/고유 키가 연결 조건으로 사용되고 중간 테이블 열은 등가 조건으로만 사용되며, 중간 테이블 연결 제거 가능

10. 기타 쿼리 최적화

1. 다음은 인덱스 쿼리가 중단되고 전체 텍스트 검색이 발생합니다.

1.1. <> where 절의 연산자입니다. 참고: 기본 키 지원. 비기본 키는

을 지원하지 않습니다. 1.2. 사용을 피하거나 사용하므로 구체적인 상황은 사례별로 분석해야 합니다.

　 유사한 최적화:

　　select * from tab name='aa' 또는 name='bb';

　　=>

　　select * from tab name='aa'

　　union all

　　select * from tab name='bb';

　 실제 측정값:

　1. 십만 데이터 테스트, 인덱스가 없으면 위 쿼리는 아래 쿼리보다 두 배 빠릅니다.

2. 30만 데이터 테스트에서 aa와 bb를 별도로 인덱싱했을 때 다음 쿼리 속도는 or보다 약간 빠릅니다.

　1.3.

　not in은 일반적으로 인덱스를 사용할 수 없습니다.

　1.4에서는 null 판단을 사용하지 마세요. >　1.5. Like 앞에는 '%.com'과 같은 퍼센트 기호가 올 수 없습니다

해결책:

1. URL과 같이 %를 앞에 붙여야 하고 데이터 길이가 크지 않은 경우 , 데이터를 뒤집어 저장할 수 있습니다. 데이터베이스에 들어가서 다시 확인하세요. LIKE REVERSE'%.com';

2. 커버링 인덱스 사용

1.6. 인덱스 필드를 조건으로 사용하는 경우 복합 인덱스인 경우 필드 이름의 가장 왼쪽 접두사가 붙습니다. 인덱스를 사용해야 합니다

2. in에서 존재 바꾸기

select num from a where num in (select num from b)

select num from a where presents(1개 선택) from b where num =a.num)

백만 개의 데이터로 59417개의 데이터를 필터링하는 데 6.65초와 4.18초가 걸립니다. 다른 최적화는 수행되지 않았으며 존재하는 항목을 in으로 대체했습니다.

3. 필드 정의는 문자열입니다. 쿼리 시 따옴표가 없으며 전체 텍스트 검색이 수행되지 않습니다.

[다음은 Luantanqin의 블로그 게시물 http://www.cnblogs.com/lingiu/p/3414134.html에서 발췌한 내용입니다. 해당 테스트를 진행하지 않았습니다.]

4. 최대한 활용해보세요

임시 테이블을 대체하는 테이블 변수

5. 시스템 테이블 리소스 소모를 줄이기 위해 임시 테이블을 자주 생성하고 삭제하지 마세요

6. 임시 테이블을 사용하는 경우 , 반드시 저장 프로시저 끝에 추가하세요. 모든 임시 테이블을 명시적으로 삭제하고 먼저 테이블을 자른 다음 테이블을 삭제하세요. 이렇게 하면 시스템 테이블의 장기간 잠금을 피할 수 있습니다.

7. 사용을 피하세요. 커서의 효율성이 떨어지기 때문에 커서 작업이 10,000 행을 초과하는 경우 다시 작성하는 것을 고려해야 합니다

8. 데이터 용량이 너무 큰 경우에는 해당 요구 사항이 합리적입니다.

9. 대규모 트랜잭션 작업을 피하고 시스템 동시성을 향상시키세요.

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