Mysql 최적화 아이디어 공유

1. 전반적인 최적화 아이디어

먼저 쿼리 수, 연결 수 및 기타 데이터를 관찰하는 스크립트를 작성하고 환경적 이유와 내부 SQL 실행 이유를 확인한 다음 특정 이유에 따라 특정 처리를 수행합니다.

권장: "mysql video tutorial"

2. 상태를 관찰하는 스크립트 작성

mysqladmin -uroot -p  ext \G

이 명령은 현재 쿼리 수와 같은 정보를 얻고, 정기적으로 폴링하고 결과를 텍스트로 리디렉션할 수 있습니다. , 차트로 처리합니다.

3.대책

1. 느린 쿼리가 정기적으로 발생하는 경우 캐시 눈사태 문제를 고려하십시오.

이 문제의 경우 캐시가 동시에 비슷한 시간에 만료되지 않도록 캐시 만료 시간만 처리하면 됩니다. 만료 시간은 최대한 이산적이거나 자정까지 집중되어야 합니다.

2. 비정규 쿼리가 느리다면 디자인 최적화 부족을 고려하세요

처리 방법:

a: 프로파일링을 활성화하여 쿼리 작업을 기록하고 명령문 실행 세부정보를 얻습니다.

show variables like '%profiling%';
set profiling=on;
select count(*) from user; 
show profiles;
show profile for query 1;
>>>
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000060 |
| Executing hook on transaction  | 0.000004 |
| starting                       | 0.000049 |
| checking permissions           | 0.000007 |
| Opening tables                 | 0.000192 |
| init                           | 0.000006 |
| System lock                    | 0.000009 |
| optimizing                     | 0.000005 |
| statistics                     | 0.000014 |
| preparing                      | 0.000017 |
| executing                      | 0.001111 |
| end                            | 0.000006 |
| query end                      | 0.000003 |
| waiting for handler commit     | 0.000015 |
| closing tables                 | 0.000011 |
| freeing items                  | 0.000085 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+

b: explain을 사용하여 확인하세요. 명령문 실행, 인덱스 사용, 스캔 범위 등

mysql> explain select count(*) from goods \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: index   
possible_keys: NULL
          key: gid
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index

c: 관련 최적화 기술

테이블 최적화 및 열 유형 선택

열 선택 원칙:

1: 필드 유형 우선순위 정수 > char, varchar > blob

이유: 정수형, 시간 연산이 빨라 공간 절약

char/varchar는 정렬 시 문자 집합과 교정 집합의 변환을 고려해야 하며 속도가 느립니다

blob은 사용할 수 없습니다. 메모리 임시 테이블

2: 충분합니다. 그냥 사용하세요. 관대하지 마세요(예: smallint, varchar(N))

이유: 큰 필드는 메모리를 낭비하고 속도에 영향을 줍니다.

varchar(10), varchar에 저장된 콘텐츠 (300)은 동일하지만 테이블 조인 쿼리 중에 varchar(300)이 더 많은 메모리를 차지합니다

3: NULL 사용을 피하세요

이유: NULL은 인덱싱에 도움이 되지 않으며 특수 바이트로 표시해야 합니다.

디스크에서 차지하는 공간은 실제로 더 큽니다

인덱스 최적화 전략

1.인덱스 유형

1.1 B-트리 인덱스(정렬된 빠른 검색 구조)

참고: Myisam, innodb에서는 기본값이 B-트리입니다. index

1.2 해시 인덱스

In 메모리 테이블에서 기본값은 해시 인덱스이고, 해시의 이론적 쿼리 시간 검토는 O(1)

질문: 해시 인덱스가 이렇게 효율적인데 왜 사용하지 않는 걸까요?

a.hash 함수로 계산한 결과는 무작위입니다. 기본 키를 id로 가정하여 디스크에 데이터를 배치하면 id가 증가함에 따라 해당 id에 해당하는 행이 무작위로 배치됩니다. 디스크.

b. 범위 쿼리는 최적화할 수 없습니다.

c. 접두사 인덱스를 사용할 수 없습니다. 예를 들어 b-tree에서 필드 열의 값은 "helloworld"이고 인덱스 쿼리는 xx=hello/xx=helloworld입니다. 인덱스(왼쪽 접두사 인덱스)를 사용하지만 hash(hello)와 hash(helloworld)는 관련이 없기 때문에 해시 인덱스는 이를 수행할 수 없습니다.

d. 정렬을 최적화할 수 없습니다.

e. 인덱스를 통해 데이터 위치를 얻으려면 행으로 돌아가야 하고, 데이터를 얻으려면 테이블로 돌아가야 합니다.

2. 트리 인덱스

2.1 where 조건에서 일반적으로 사용됨 모든 열에 인덱스 추가

예: cat_id=3 및 가격>100; //세 번째 열 쿼리, 100위안 이상의 제품

오류: cat_id 및 가격에 인덱스를 추가합니다. 실제로 인덱스는 하나만 사용할 수 있으며 모두 독립 인덱스입니다.

2.2 여러 열에 인덱스를 생성한 후에는 어떤 열을 쿼리하든 인덱스가 작동합니다.

2.2 여러 열에 인덱스를 생성한 후 어떤 열이 쿼리하면 인덱스가 작동합니다 모두가 역할을 합니다

정답: 다중 열 인덱스의 경우 인덱스가 작동하려면 인덱스가 왼쪽 접두사 요구 사항(레이어별 인덱스)을 충족해야 합니다

인덱스 가져오기(a ,b,c) 예:

语句 索引是否发挥作用
where a=3 是
where a=3 and b=5 是
where a=3 and b=5 and c=4 是
where b=3 or where c=4 否
where a=3 and c=4 a列能发挥索引作用，c列不能
where a=3 and b>10 and c=7 a,b能发挥索引作用，c列不能

고성능 인덱스 전략

1. innodb의 경우 노드 아래에 데이터 파일이 있기 때문에 innodb의 기본 키의 경우 노드 분할이 느려집니다. , 정수 유형을 사용해보십시오. 증가하는 정수 유형입니다.

2. 인덱스의 길이는 인덱스 파일의 크기에 직접적인 영향을 미치고, 추가, 삭제, 수정 속도에 영향을 미치며, 쿼리 속도에 간접적으로 영향을 미칩니다(더 많은 메모리를 차지함).

3. 열의 값에 대해 왼쪽에서 오른쪽으로 부분을 가로채서 색인을 만듭니다.

a. 컷이 짧을수록 반복이 많아지고, 인덱스 효과가 나빠집니다.

b 컷이 길어질수록 인덱스 파일이 커지고 영향을 미칩니다. speed

그래서 최대한 길게 시도하여 성능을 최대화하기 위한 일반적인 방법은 다양한 길이를 가로채서 지수 구별을 테스트하는 것입니다.

차별 테스트:

select count(distinct left(word, 1)) / count(*) from table;

테스트가 완료된 후 인덱스 테스트를 통해 얻은 최적의 길이에 따라 생성 가능

alter table table_name add index word(word(4));

Ideal Index

1. 자주 사용되는 쿼리

2. 높은 차별화율

3. 자주 사용되는 쿼리 필드를 커버해 보세요.

위 내용은 Mysql 최적화 아이디어 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!