mysql 독립형 데이터베이스 최적화의 몇 가지 사례_MySQL

지원되는 데이터 양에 따라 독립형 데이터베이스와 하위 데이터베이스의 두 단계로 나눌 수 있으며, 전자는 일반적으로 내부 데이터를 지원할 수 있습니다. 500W 또는 10G. 이 값을 초과하는 경우 데이터베이스 테이블 분할을 고려해야 합니다. 또한 대기업과의 인터뷰는 종종 단일 머신 데이터베이스에 대한 질문으로 시작한 다음 하위 데이터베이스와 테이블에 대해 단계별로 질문하며 중간에 많은 데이터베이스 최적화 질문이 산재해 있습니다. 이 기사에서는 독립형 데이터베이스 최적화의 몇 가지 사례를 설명하려고 합니다. 데이터베이스는 mysql을 기반으로 합니다. 불합리한 점이 있으면 수정해 주십시오.

1. 테이블 구조 최적화

애플리케이션 구축을 시작할 때 데이터베이스의 테이블 구조 설계는 나중에 애플리케이션 성능에 영향을 미치는 경우가 많습니다. 특히 사용자 수가 증가할 때 더욱 그렇습니다. 따라서 테이블 구조 최적화는 매우 중요한 단계입니다.

1.1, 문자셋

일반적으로 GBK는 데이터를 저장할 때 UTF-8보다 저장 공간을 적게 사용하지만 UTF-8은 실제로 이 저장 공간을 위해 확장성을 희생할 필요가 없습니다. . 실제로 나중에 GBK에서 UTF-8로 변환하려면 지불해야 하는 가격이 매우 높고, 데이터 마이그레이션이 필요하며, 하드디스크를 확장하는 데 돈을 투자하면 저장공간도 해결할 수 있다.

1.2. 기본 키

mysql의 innodb를 사용할 때 innodb의 기본 스토리지 모델은 기본 키를 클러스터형 인덱스로 사용하고 리프 노드로 삽입된 데이터를 기본 키를 통해 빠르게 찾을 수 있으므로 빠르게 얻을 수 있는 B+ 트리입니다. 기록. 따라서 테이블을 디자인할 때 기본키를 추가해야 하며, 자동으로 늘려주는 것이 가장 좋습니다. 자동 증가 기본 키를 사용하면 삽입된 데이터가 기본 키 순서대로 기본 B+ 트리의 리프 노드에 삽입될 수 있으므로 이러한 삽입은 기존의 다른 데이터를 거의 이동할 필요가 없습니다. 이므로 삽입 효율이 매우 높습니다. 기본 키가 자동 증가하지 않으면 기본 키의 값은 매번 거의 무작위가 될 것입니다. 이때 B+ 트리의 특성을 보장하기 위해 많은 양의 데이터를 이동해야 하므로 불필요한 오버헤드가 추가될 수 있습니다. .

1.3, 필드

1.3.1. 인덱스 필드는 null이 아닌 제약 조건을 추가하고 기본값을 설정해야 합니다

1.3.2 소수점 이하 자릿수를 저장하려면 소수점 이하 자릿수를 사용하는 것이 좋습니다.

1.3.3 많은 양의 데이터를 저장하기 위해 Text/blob을 사용하는 것은 권장되지 않습니다. 큰 텍스트를 읽고 쓰면 상대적으로 큰 I/O 오버헤드가 발생하고 mysql 캐시를 많이 차지하므로 데이터베이스가 크게 줄어들기 때문입니다. 처리량을 위해 대용량 텍스트 데이터는 특수 파일 저장 시스템에 저장하는 것이 좋습니다. MySQL은 이 파일의 액세스 주소만 저장합니다. 예를 들어 블로그 게시물은 파일에 저장할 수 있으며 MySQL은 파일의 상대 주소만 저장합니다.

1.3.4.varchar 유형의 길이는 8K를 초과하지 않는 것이 좋습니다.

1.3.5. 시간 유형에는 타임스탬프 대신 날짜시간을 사용하는 것이 좋습니다. 날짜시간은 8바이트를 차지하고 타임스탬프는 4바이트만 차지하지만 후자는 비어 있지 않아야 하며 시간대에 민감합니다.

1.3.6. 데이터 생성 수정 시간을 기록하려면 테이블에 gmt_create 및 gmt_modified 두 개의 필드를 추가하는 것이 좋습니다. 이 두 필드를 설정하는 이유는 문제 해결을 용이하게 하기 위한 것입니다.

1.4.인덱스 생성

1.4.1. 이 단계에서는 비즈니스를 이해하지 못하기 때문에 무턱대고 인덱스를 추가하지 말고 반드시 사용될 일부 필드에만 일반 인덱스를 추가하도록 하세요.

1.4.2.innodb 단일 열 인덱스를 생성하는 길이는 767바이트를 초과할 수 없으며, 이를 초과할 경우 처음 255바이트가 접두사 인덱스로 사용됩니다

1.4.3.innodb 결합 인덱스 생성 시 각 컬럼 인덱스의 길이는 767바이트를 초과할 수 없으며, 총합은 3072바이트를 초과할 수 없습니다

2. SQL 최적화

일반적으로 SQL의 종류는 기본 추가, 삭제, 수정, 쿼리, 페이징 쿼리, 범위 쿼리, 퍼지 검색, 다중 테이블 연결 등 몇 가지뿐입니다

2.1.기본 쿼리

일반 쿼리는 인덱스가 필요하며, 인덱스가 없는 경우 쿼리를 수정하여 해당 필드를 인덱스로 추가하는 것이 좋습니다. 쿼리 호출량이 큰 경우, 예를 들어 매일 10W 이상의 호출이 발생하는 경우 새 인덱스를 추가해야 합니다. 예를 들어 매일 100개 이상의 호출이 발생하는 경우 유지하는 것을 고려할 수 있습니다. 그대로입니다. 또한 가능한 한 select *를 사용하지 마십시오. sql 문에 사용되는 필드를 모두 추가하세요. 불필요한 필드는 확인하지 마세요. I/O와 메모리 공간이 낭비됩니다.

2.2 효율적인 페이징

m,n 제한의 핵심은 먼저 제한 m+n을 실행한 다음 m번째 행에서 n번째 행을 가져오는 것입니다. 이런 식으로 제한 페이지를 뒤로 넘기면 m이 더 커지고 성능이 향상됩니다. 낮추다. 예를 들어

A 한도 100000,10에서 *를 선택하세요. 이 SQL 문의 성능은 다음 버전으로 변경하는 것이 좋습니다.

A에서 id,name,age 선택, 여기서 id >=(A 제한 100000,1에서 id 선택) 제한 10

2.3. 범위 쿼리

범위 쿼리에는 사이, 보다 큼, 보다 작음 및 내부가 포함됩니다. MySQL의 in 쿼리 조건은 개수가 제한되어 있으며, 개수가 작을 경우 인덱스 쿼리를 사용할 수 있다. 사이, 보다 큼, 보다 작음 등의 쿼리는 인덱싱되지 않으므로 인덱싱되는 쿼리 조건 뒤에 넣으십시오.

2.4, 퍼지 쿼리와 같은

%name%과 같은 문을 사용하면 인덱스를 사용하지 않습니다. 이는 전체 테이블 스캔과 같습니다. 데이터 양이 적을 경우에는 큰 문제가 없습니다. 성능이 급격하게 떨어지므로, 데이터 양이 많아지면 검색 엔진을 사용하여 퍼지 검색을 대체하는 것이 불가능할 경우 퍼지 쿼리 이전에 인덱싱 조건을 추가해야 합니다.

2.5다중 테이블 연결

서브 쿼리와 조인 모두 여러 테이블에서 데이터를 가져오는 데 사용할 수 있지만 서브 쿼리의 성능은 조인으로 변경하는 것이 좋습니다. mysql의 조인은 이전 테이블 쿼리의 결과 집합을 통해 다음 테이블을 쿼리하는 Nested Loop Join 알고리즘을 사용한다. 예를 들어 이전 테이블의 결과 집합은 100개의 데이터이고, 후자의 테이블은 10W이다. 그런 다음 100*10W 데이터 세트를 필터링하여 최종 결과 세트를 얻어야 합니다. 따라서 작은 결과 집합을 가진 테이블을 사용하여 큰 테이블을 조인하는 동시에 조인 필드에 인덱스를 생성해 보십시오. 인덱스를 만들 수 없는 경우에는 조인 버퍼 크기를 충분히 크게 설정해야 합니다. 위의 기술 중 어느 것도 조인으로 인한 성능 저하 문제를 해결할 수 없다면 조인 사용을 중단하고 조인 쿼리를 두 개의 간단한 쿼리로 분할하세요. 또한 다중 테이블 연결은 3개 테이블을 초과해서는 안 됩니다. 일반적으로 3개 이상의 테이블을 사용하면 성능이 매우 저하됩니다. SQL을 분할하는 것이 좋습니다.

3. 데이터베이스 연결 풀 최적화

데이터베이스 연결 풀은 본질적으로 높은 동시성에 저항하는 수단인 캐시입니다. 데이터베이스 연결 풀 최적화는 주로 매개변수 최적화에 관한 것입니다. 일반적으로 DBCP 연결 풀을 사용하며 구체적인 매개변수는 다음과 같습니다.

3.1 초기 크기

초기 연결 수, 여기서 초기는 애플리케이션이 시작될 때가 아니라 처음으로 getConnection을 얻은 때를 나타냅니다. 초기값은 동시성 과거 평균으로 설정할 수 있습니다

3.2, minIdle

유지할 최소 유휴 연결 수입니다. DBCP는 유휴 연결을 재활용하기 위해 백그라운드에서 스레드를 시작합니다. 스레드가 유휴 연결을 재활용하면 minIdle 연결 수가 유지됩니다. 일반적으로 5로 설정하고, 동시성 양이 정말 작을 경우 1로 설정할 수 있습니다.

3.3, maxIdle

예약된 유휴 연결의 최대 수는 비즈니스 동시성 피크에 따라 설정됩니다. 예를 들어 동시성 최대치가 20인 경우 최대치가 지나면 이러한 연결은 즉시 재활용되지 않습니다. 짧은 시간 후에 또 다른 최대치가 발생하는 경우 연결 풀은 자주 생성할 필요 없이 이러한 유휴 연결을 재사용할 수 있습니다. 그리고 가까운 연결.

3.4, maxActive

최대 활성 연결 수는 허용 가능한 동시성 극값에 따라 설정됩니다. 예를 들어 단일 시스템 동시성의 허용 가능한 극한 값은 100입니다. 그러면 maxActive를 100으로 설정한 후에는 동시에 100개의 요청만 처리할 수 있으며 최대 대기 시간이 지나면 초과 요청은 중단됩니다. 악의적인 동시성 공격을 방지하고 데이터베이스를 보호하려면 이 값을 설정해야 합니다.

3.5, 최대대기

요청이 연결을 기다리는 동안에는 스레드를 해제할 수 없고 스레드가 해제될 수 없기 때문에 요청이 빨리 실패할 수 있도록 연결을 얻기 위한 최대 대기 시간을 3초와 같이 짧게 설정하는 것이 좋습니다. 단일 시스템의 동시성은 제한됩니다. 인터넷에서 권장하는 60초와 같이 이 시간을 너무 길게 설정하면 이러한 요청이 더 많은 한 이 스레드는 60초 내에 해제될 수 없습니다. 해당 응용 프로그램에 대한 서비스를 더 이상 사용할 수 없게 됩니다.

3.6, minEvictableIdleTimeMillis

연결이 재활용되지 않고 유휴 상태로 유지되는 시간은 기본값은 30분입니다.

3.7, 유효성 검사 쿼리

연결이 유효한지 확인하는 데 사용되는 sql 문은 일반적으로 간단한 sql로 설정하는 것이 좋습니다.

3.8, testOnBorrow

연결 신청 시 연결이 감지되면 성능에 심각한 영향을 미치므로 켜는 것을 권장하지 않습니다

3.9, testOnReturn

연결을 되돌릴 때 연결이 감지됩니다. 성능에 심각한 영향을 미치므로 켜지 않는 것이 좋습니다

3.10, testWhileIdle

켜고 나면 백그라운드에서 연결을 정리하는 스레드가 가끔씩 유휴 연결에 대해 유효성을 검사합니다. 연결이 실패하면 성능에 영향을 주지 않고 지워지는 것이 좋습니다.

3.11, numTestsPerEvictionRun

은 매번 확인하는 링크 수를 나타내며, 매번 모든 링크를 효과적으로 확인할 수 있도록 maxActive만큼 크게 설정하는 것이 좋습니다.

3.12.예열연계풀

커넥션 풀의 경우 애플리케이션을 시작할 때 예열하고, 외부 액세스를 제공하기 전에 간단한 SQL 쿼리를 수행하여 필요한 연결 수로 커넥션 풀을 채우는 것이 좋습니다.

4. 인덱스 최적화

데이터의 양이 일정 수준까지 증가하면 SQL 최적화로는 더 이상 성능을 향상시킬 수 없습니다. 이때에는 인덱싱이라는 큰 움직임이 필요합니다. 인덱싱에는 세 가지 수준이 있습니다. 일반적으로 이 세 가지 수준을 익히는 것만으로도 충분합니다. 또한 인덱싱할 필드의 선택성을 고려해야 합니다.

4.1, 기본색인

where 조건에 따라 인덱스를 생성합니다. 단일 컬럼은 일반 인덱스를 생성할 수 있고, 여러 컬럼은 결합 인덱스를 생성할 수 있습니다. 복합 인덱스는 가장 왼쪽 접두사 원칙에 주의해야 합니다.

4.2, 보조지수

order by 또는 group by가 사용되는 필드가 있는 경우 이 필드에 인덱스를 구축하는 것을 고려해 볼 수 있습니다. 이렇게 하면 인덱스가 자연스럽게 정렬되므로 order by 및 group by로 인한 정렬을 피할 수 있습니다. 따라서 성능을 향상시킵니다.

4.3, 3단계 지수

위의 두 가지 방법으로 충분하지 않은 경우 쿼리된 필드에 인덱스를 추가하면 mysql이 데이터를 쿼리할 때 하나의 I/O 작업을 줄일 수 있습니다. 먼저 기본키 인덱스를 확인한 후, 기본키 인덱스를 기준으로 일반 인덱스를 확인하고, 일반 인덱스를 기준으로 해당 레코드를 확인합니다. 필요한 레코드가 일반 색인에 있으면 세 번째 단계는 필요하지 않습니다. 물론 이러한 인덱스 구축 방식은 매우 극단적이며 일반적인 시나리오에는 적합하지 않습니다.

4.4.지수 선택성

인덱스를 구축할 때 매우 선택적인 필드에 구축하도록 노력하세요. 선택성이 높다는 것은 무엇을 의미합니까? 소위 선택성이 높다는 것은 이 분야를 통해 발견되는 데이터의 양이 적다는 것을 의미한다. 예를 들어 사람의 정보를 이름으로 확인하면 일반적으로 발견되는 데이터의 양이 매우 적지만, 성별로 확인하면 절반 정도 된다. 따라서 이름은 선택도가 높은 필드이고 성별은 선택도가 낮은 필드입니다.

5. 과거자료 보관

1년에 데이터 양이 500만 개 증가하면 인덱스는 아무것도 할 수 없게 됩니다. 이때는 하위 데이터베이스와 테이블을 고려하는 것이 일반적입니다. 비즈니스가 폭발적으로 성장하지는 않지만 데이터가 천천히 증가한다면 데이터베이스와 샤딩 테이블을 샤딩하는 복잡한 기술적 수단을 무시하고 대신 기록 데이터를 아카이브할 수 있습니다. 6개월 전의 데이터 등 수명이 종료된 이력 데이터를 보관합니다. Quartz의 스케줄링 작업을 이용하면 정기적으로 6개월 전의 데이터를 이른 아침에 확인한 후 원격 hbase 서버에 저장할 수 있습니다. 물론, 긴급 상황에 대비해 과거 데이터에 대한 쿼리 인터페이스도 제공해야 합니다.

위 내용은 mysql 독립형 데이터베이스의 최적화 정보입니다. 앞으로도 관련 정보를 계속 추가하겠습니다.