MySQL 데이터베이스 최적화 기술에 대한 인덱스 활용 기술 요약_MySQL

이 기사의 예에는 MySQL 데이터베이스 최적화 기술의 인덱스 사용이 요약되어 있습니다. 참고할 수 있도록 모든 사람과 공유하세요. 자세한 내용은 다음과 같습니다.

다음은 인덱스 최적화 기술을 더 자세히 분석하기 위해 이전 기사 "MySQL 데이터베이스 최적화 기술의 구성 기술 요약"을 따릅니다.

(7) 테이블 최적화

1. 적절한 데이터 엔진을 선택하세요

MyISAM: 대량의 읽기 작업에 적합한 테이블

InnoDB: 대량의 쓰기와 읽기에 적합한 테이블

2. 적절한 열 유형을 선택하세요

SELECT * FROM TB_TEST PROCEDURE ANALYSE()를 사용하여 이 테이블의 각 필드를 분석하고 열 유형 최적화를 위한 제안 제공

3. NULL 값을 저장하지 않는 열에는 NOT NULL을 사용하세요. 이는 색인을 생성하려는 열에 특히 중요합니다.

4. 적절한 색인 생성

5. 가변 길이 필드보다 빠른 고정 길이 필드를 사용하세요

(8) 색인 생성 원칙

1. 인덱스를 적절하게 사용하세요

테이블은 쿼리에서 하나의 인덱스만 사용할 수 있습니다. EXPLAIN 문을 사용하여 최적화 프로그램의 작동을 확인하세요

분석을 사용하면 최적화 프로그램이 인덱스 사용량에 대해 더 정확하게 예측할 수 있습니다.

2. 검색, 정렬, 그룹화 및 기타 작업과 관련된 데이터 열에 대해 인덱스를 생성해야 합니다.

3. 중복 데이터가 적은 데이터 열에 인덱스를 구축하는 것이 가장 좋습니다

예: 생일 열은 색인화할 수 있지만 성별 열은 색인화하면 안 됩니다

4. 더 짧은 값을 색인화해 보세요.

디스크 IO 작업을 줄이고 인덱스 버퍼에 더 많은 키 값을 수용할 수 있으며 적중률이 향상됩니다

긴 문자열을 색인화하는 경우 접두사 길이를 지정할 수 있습니다

5. 다중 열 인덱스의 올바른 사용

쿼리를 위해 여러 조건을 자주 조합해야 하는 경우 다중 열 인덱스를 사용해야 합니다(테이블은 하나의 쿼리에 하나의 인덱스만 사용할 수 있고, 하나만 사용하여 여러 단일 열 인덱스를 생성할 수 있기 때문입니다)

6. 가장 왼쪽 접두어를 최대한 활용하세요

즉, 다중 열 인덱스에서 열의 순서가 합리적으로 배열되어야 하며, 가장 많이 사용되는 열이 먼저 순위를 매겨야 합니다

7. 인덱스를 너무 많이 만들지 마세요

where, order by, group by에서 자주 사용되는 필드만 색인화하면 됩니다.

8. 느린 쿼리 로그를 사용하여 느린 쿼리 찾기(log-slow-queries, long_query_time)

(9) 인덱스를 최대한 활용

1. 동일한 데이터 유형의 데이터 열을 비교해 보세요

2. 비교식에서 인덱스 열을 최대한 독립적으로 만듭니다. WHERE mycol < 4 / 2는 인덱스를 사용하고 WHERE mycol * 2 <

3. 쿼리 필드에 함수를 추가하지 마세요.

예: WHERE YEAR(date_col) < 1990은 WHERE date_col <

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < 컷오프는 WHERE date_col < DATE_ADD(CURDATE(), INTERVAL 컷오프 DAY)로 변환됩니다.

4. LIKE 패턴 시작 부분에 와일드카드 문자를 사용하지 마세요

5. 직선 조인을 사용하여 옵티마이저가 FROM 절의 순서로 조인하도록 강제하거나 직선 조인 b에서 *를 선택하여 두 테이블의 순서를 강제로 적용할 수 있습니다. .

6. 지정된 인덱스를 강제로 사용하려면 강제 인덱스를 사용하세요. 예를 들어, song_lib에서 강제 인덱스(song_name)를 선택하면 강제 인덱스를 사용하지 않는 것보다 효율적입니다.

7. MySQL 자동 유형 변환을 사용하지 마십시오. 그렇지 않으면 인덱스를 사용할 수 없습니다. 예를 들어 int 유형의 num_col에 대해 where num_col='5'

을 사용하십시오.

(10) SQL 문 최적화

1. 대규모 테이블에서 데이터를 쿼리할 가능성을 줄이기 위해 적절한 통계 중간 결과 테이블을 만듭니다

2. 하위 쿼리를 사용하지 말고 대신 조인을 사용하세요. 예:

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post
FROM authors a

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

SELECT a.id, MAX(p.created) AS latest_post
FROM authors AS a
INNER JOIN posts p ON (a.id = p.author_id)
GROUP BY a.id

select song_id from song_lib where singer_id in
(select singer_id from singer_lib
where first_char='A'
) limit 2000

다음으로 변경됨:

select song_id from song_lib a
inner join singer_lib b on a.singer_id=b.singer_id and first_char='A' limit 2000

3. 중복 키를 확인하기 위해 삽입하는 경우 ON DUPLICATE KEY UPDATE를 사용하세요.

코드는 다음과 같습니다.

db_action.action_today(user_id,song_id,action_count) 값(1,1,1)에 삽입 ON DUPLICATE KEY UPDATE action_count=action_count+1;

4. 커서 사용을 피하세요

임시 테이블 추가, 다중 테이블 쿼리, 다중 테이블 업데이트 등을 사용하여 작업을 완료할 수 있으므로 커서의 작동 효율성이 매우 낮습니다.

(11) explain을 사용하여 SQL 문에서 인덱스 사용을 분석합니다

SELECT 문 앞에 EXPLAIN 키워드를 넣으면 MySQL은 SELECT를 처리하는 방법을 설명하고 테이블이 조인되는 방식과 순서에 대한 정보를 제공합니다. EXPLAIN의 도움으로 테이블이 언제 조인되어야 하는지 알 수 있습니다. indexed 레코드를 찾기 위해 인덱스를 사용하는 더 빠른 SELECT를 얻으려면 최적화 프로그램이 최적의 순서로 테이블을 조인하는지 여부도 알아야 합니다. 최적화 프로그램이 SELECT 문에 특정 조인 순서를 사용하도록 하려면 STRAIGHT_JOIN 절을 추가하세요. .

EXPLAIN 명령의 일반 구문은 다음과 같습니다. EXPLAIN 예: a.id=b.id의 내부 조인 b에서 *를 선택하십시오.

EXPLAIN 분석 결과 매개변수에 대한 자세한 설명:

1.table: 테이블의 이름입니다.

2.type: 연결 작업 유형입니다.

시스템: 테이블에 레코드가 하나만 있습니다(실제 응용에서는 데이터가 하나만 있는 테이블은 거의 사용되지 않음)

const: 테이블에는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값과 비교할 때 사용되는 최대 하나의 일치 행이 있습니다.

예:

select * from song_lib where song_id=2

(song_id는 테이블의 기본 키입니다)

eq_ref: 이전 테이블의 각 행 조합에 대해 UNIQUE 또는 PRIMARY KEY 인덱스를 사용하여 테이블에서 행을 읽습니다.

예:

코드는 다음과 같습니다.

song_lib에서 *를 선택하고 a.singer_id=b.singer_id에서 singer_lib b 내부 조인

(b의 유형 값은 eq_ref입니다)

ref: 이전 테이블의 각 행 조합에 대해 UNIQUE 또는 PRIMARY KEY 이외의 인덱스를 사용하여 테이블에서 행을 읽습니다.

예:

코드는 다음과 같습니다.

song_lib에서 *를 선택하고 내부 조인 singer_lib b on a.singer_name=b.singer_name

그리고

코드는 다음과 같습니다.

select * from singer_lib b where singer_name='ccc'

(b.singer_name은 일반 인덱스이므로 b의 유형 값은 ref입니다.)

ref_or_null: 이 조인 유형은 ref와 비슷하지만 NULL 값이 포함된 행을 구체적으로 검색하기 위해 MySQL이 추가되었습니다.

예:

코드는 다음과 같습니다.

singer_name='ccc' 또는 singer_name이 null인 singer_lib에서 *를 선택하세요

index_merge: 이 조인 유형은 인덱스 병합 최적화 방법이 사용됨을 나타냅니다

키: MySQL에서 실제로 사용하는 인덱스 이름을 나타냅니다. 비어 있거나 NULL이면 MySQL은 인덱스를 사용하지 않습니다.

key_len: 인덱스에서 사용된 부분의 길이(바이트)입니다.

3.ref: ref 열은 테이블에서 행을 선택하기 위해 키와 함께 사용되는 열 또는 상수를 보여줍니다.

4.rows: MySQL이 올바른 결과를 찾기 전에 스캔해야 한다고 생각하는 레코드 수입니다. 분명히 여기서 이상적인 숫자는 1입니다.

5.추가: 여기에는 다양한 옵션이 나타날 수 있으며, 대부분은 쿼리에 부정적인 영향을 미칩니다. 일반적으로:

using where: where 조건이 사용됨을 나타냅니다

using filesort: 파일 정렬을 사용한다는 의미, 즉 order by 절을 사용하고, order by의 필드 인덱스를 사용하지 않으므로 추가적인 정렬 오버헤드가 필요합니다. 따라서 using filesort가 나타나는 경우입니다. 이는 정렬 효율이 매우 높다는 것을 의미하며, 힘 지수를 사용하는 등의 최적화가 필요합니다

더 많은 MySQL 관련 콘텐츠에 관심이 있는 독자는 이 사이트의 특별 주제인 "MySQL 인덱스 작업 기술 요약", "MySQL 로그 작업 기술 종합 모음", "MySQL 트랜잭션 작업 기술 요약"을 확인할 수 있습니다. , "MySQL 저장 프로시저 기술 종합 모음" , "MySQL 데이터베이스 잠금 관련 기술 요약" 및 "자주 사용되는 MySQL 함수 요약"

이 기사가 MySQL 데이터베이스를 계획하는 모든 사람에게 도움이 되기를 바랍니다.