mysql 단일 서버 데이터베이스 최적화의 일부 실천

database optimization은 많은 것이 있습니다. 데이터의 지원 양에 따라 두 단계로 나눌 수 있습니다: 단일 서버 데이터베이스와 데이터베이스 분할 및 테이블 분할, 전자는 일반적으로500W 또는10G 이내의 데이터는, 이 값을 초과하면 데이터베이스 분할과 테이블 분할을 고려해야 합니다. 또한, 일반적으로 대기업에서는 단일 서버 데이터베이스로부터 시작하여 단계적으로 데이터베이스 분할과 테이블 분할로 이동하며, 중간에 많은 데이터베이스 최적화 문제가 들어갑니다. 이 기사는 단일 서버 데이터베이스 최적화의 일부 실천을 설명하려고 합니다. 데이터베이스는 MySQL을 기반으로 하며, 불합리한 부분이 있다면 반대의 의견을 환영합니다.

1table structure optimization

application을 시작할 때, 데이터베이스 테이블 구조 설계는 후속 성능에 큰 영향을 미칩니다. 특히 사용자 수가 많아지면 성능에 더 큰 영향을 미칩니다. 따라서 테이블 구조 최적화는 매우 중요한 단계입니다.

1.1charset

generally choose UTF-8although GBK compared to UTF 저장 중에는-8사용하는 저장 공간은 적지만, UTF-8다양한 언어와 호환되지만, 이 점의 저장 공간을 확장성을 희생하지 않아야 합니다. 사실, 후에는 GBK에서 UTF로 변환할 필요가 있습니다.-8때문에 매우 높은 비용을 지불해야 하며, 데이터 이동이 필요하며, 저장 공간은 돈을 지불하여 하드디스크를 확장할 수 있습니다.

1.2primary key

MySQL의 InnoDB를 사용할 때, InnoDB의 하위 저장 모델은 B+트리는 primary key를 클러스터 인덱스로 사용하고, 입력 데이터를 잎 노드로 사용하여 primary key를 통해 잎 노드를 빠르게 찾아서 기록을 빠르게 얻을 수 있습니다. 따라서 테이블을 설계할 때에는 primary key를 추가해야 하며, 자동 증가가 좋습니다. 자동 증가 primary key는 입력 데이터를 primary key 순서로 하위 B에 insert할 수 있습니다.+트리의 잎 노드에서는 순서대로 정렬되어 있기 때문에, 이 insert는 기존의 다른 데이터를 이동할 필요가 거의 없어서 insert 성능이 매우 높습니다. primary key가 자동 증가가 아니라면, 매번 primary key의 값은 근사히 무작위이므로, 이때 많은 데이터를 이동해야 B를 보장할 수 있습니다.+트리의 특성은 불필요한 비용을 증가시킵니다.

1.3、字段

1.3.1、建了索引的字段必须加上not null约束，并且设置default值

1.3.2、不建议使用float、double来存小数，防止精度损失，建议使用decimal

1.3.3、不建议使用Text/blob来保存大量数据，因为对大文本的读写会造成比较大的I/O开销，同时占用mysql的缓存，高并发下会极大的降低数据库的吞吐量，建议将大文本数据保存在专门的文件存储系统中，mysql中只保存这个文件的访问地址，比如博客文章可以保存在文件中，mysql中只保存文件的相对地址。

1.3.4、varchar类型长度建议不要超过8K。

1.3.5、时间类型建议使用Datetime，不要使用timestamp，虽然Datetime占用8byte，而timestamp只占用4byte，但是后者要保证非空，而且后者是对时区敏感的。

1.3.6、建议表中增加gmt_create和gmt_modified两个字段，用来记录数据创建的修改时间。这两个字段建立的原因是方便查问题。

1.4、索引创建

1.4.1、这个阶段由于对业务并不了解，所以尽量不要盲目加索引，只为一些一定会用到索引的字段加普通索引。

1.4.2、创建innodb单列索引的长度不要超过767bytes，如果超过会用前255bytes로 전자 인덱스로 사용

1.4.3、创建innodb组合索引的各列索引长度不要超过767bytes，全部加起来不要超过3072bytes

2、SQL 최적화

一般来说sql就那么几种：基本的增删改查，分页查询，范围查询，模糊搜索，多表连接

2.1、基本查询

一般查询需要走索引，如果没有索引建议修改查询，把有索引的那个字段加上，如果由于业务场景没法使用这个字段，那么需要看这个查询调用量大不大，如果大，比如每天调用10W+，这就需要新增索引，如果不大，比如每天调用100+，那么可以考虑保持原样。另外，select * 가능한 한 적게 사용하세요. 필요한 필드가 무엇인가면 sql 문장에 추가하세요. 필요하지 않은 필드는 검색하지 마세요. I/O와 메모리 공간.

2.2、高效分页

limit m,n其实质就是先执行limit m+n，然后从第m行取n行，这样当limit翻页越往后翻m越大，性能越低。比如

select * from A limit 100000,10，这种sql语句的性能是很差的，建议改成下面的版本:

selec id,name,age from A where id >=(select id from A limit 100000,1) limit 10

2.3범위 검색

범위 검색은 between, 큰 수, 작은 수 및 in을 포함합니다. MySQL에서 in 쿼리의 조건은 수량 제한이 있으며, 수량이 적을 때 인덱스 쿼리를 수행할 수 있습니다. 수량이 많을 때는 전체 테이블 스캔이 됩니다. 그리고 between, 큰 수, 작은 수와 같은 쿼리는 인덱스를 탐색하지 않으므로, 인덱스를 탐색하는 쿼리 조건의 뒤에 두는 것이 좋습니다.

2.4불명확한 검색 like

such as like %name%는 인덱스를 탐색하지 않으며, 전체 테이블 스캔과 같습니다. 데이터가 적을 때는 큰 문제가 없지만, 데이터가 많아지면 성능이 매우 떨어집니다. 따라서 데이터가 많아지면 검색 엔진을 사용하여 이러한 불명확한 검색을 대체하거나, 불명확한 검색 전에 인덱스를 탐색할 수 있는 조건을 추가하는 것이 좋습니다.

2.5다중 테이블 조인

서브 쿼리와 조인은 다중 테이블 간에서 데이터를 추출할 수 있지만, 서브 쿼리의 성능은 나쁩니다. 따라서 서브 쿼리를 조인으로 변경하는 것이 좋습니다. MySQL의 조인은 Nested Loop Join 알고리즘을 사용하며, 이는 전자 테이블의 결과 집합을 사용하여 후자 테이블에서 검색하는 것입니다. 예를 들어, 전자 테이블의 결과 집합은100개의 데이터가 있고, 다음 테이블에10W 데이터가 있다면,100*10W 데이터 집합에서 최종 결과 집합을 필터링하여 얻습니다. 따라서, 작은 결과 집합의 테이블을 큰 테이블과 조인하려면, 조인 필드에 인덱스를 설정해야 합니다. 인덱스를 설정할 수 없다면, 충분히 큰 조인 버퍼 크기를 설정해야 합니다. 만약 이러한 기술들이 조인에서 발생하는 성능 하락 문제를 해결할 수 없다면, 조인을 사용하지 마시고, 한 번의 조인 쿼리를 두 번의 간단한 쿼리로 분할하세요. 또한, 다중 테이블 조인은 일반적으로 세 개 이상의 테이블을 초과하지 않도록 하고, 세 개 이상의 테이블을 초과하면 성능이 매우 나빠지므로 SQL을 분할하는 것이 좋습니다.

3DBCP 연결 풀 최적화

데이터베이스 연결 풀은 본질적으로 캐시이며, 고객이 많아지는 상황을 견뎌내는 수단입니다. 데이터베이스 연결 풀 최적화는 주로 매개변수를 최적화하는 것으로, 일반적으로 DBCP 연결 풀을 사용합니다. 그의 구체적인 매개변수는 다음과 같습니다:

3.1　 initialSize

초기 연결 수는, 이는 getConnection()를 처음 호출할 때, 애플리케이션이 시작할 때가 아니라는 것을 의미합니다. 초기 값은 동시 접속 고객 수의 역사적인 평균 값으로 설정할 수 있습니다.

3.2minIdle

최소로 유지하는 빈 연결 수입니다. DBCP는 배경에서 빈 연결을 회수하는 스레드를 시작합니다. 이 스레드가 빈 연결을 회수할 때, minIdle 개의 연결을 유지합니다. 일반적으로5로 설정할 수 있습니다.1.

3.3maxIdle

최대로 유지하는 빈 연결 수는 비즈니스 동시 접속 고객 수에 따라 설정됩니다. 예를 들어 동시 접속 고객 수가20, 그렇다면 고객이 많아지는 시간이 지나면 이들 연결이 바로 회수되지 않습니다. 또 다른 고객이 많아지는 시간이 다가오면, 연결 풀을 재사용하여 연결을 빈번히 생성하고 닫을 필요가 없습니다.

3.4maxActive

최대 활성 연결 수, 수용할 수 있는 병목 가능 최대 값에 따라 설정합니다. 예를 들어, 단일 서버의 병목 가능 최대 값은100, 그렇다면 이 maxActive 설정을100 이후에, 동시에100개의 요청을 서비스하면, 최대 기다림 시간 이후에 남은 요청은 버려집니다. 이 값은 설정되어야 하며, 악의적인 병목 공격을 방지하고 데이터베이스를 보호할 수 있습니다.

3.5maxWait

연결을 얻는 최대 기다림 시간을 설정하는 것을 권장합니다. 예를 들어, 짧게 설정하는 것을 권장합니다.3초, 이렇게 하면 요청이 빠르게 실패할 수 있으며, 이는 한 번의 요청이 연결을 얻기 위해 기다리는 동안 스레드가 해제되지 않기 때문입니다. 서버의 스레드 동기화량은 제한적이며, 이 시간이 너무 길게 설정되면, 예를 들어, 인터넷에서 권장하는 것처럼60초, 그렇다면 이 스레드는 이60초 내에는 해제되지 않습니다. 이러한 요청이 많아지면, 애플리케이션의 사용 가능한 스레드가 줄어들고, 서비스가 사용할 수 없게 됩니다.

3.6minEvictableIdleTimeMillis

연결이 비어 있으면서 회수되지 않는 시간, 기본30분.

3.7validationQuery

연결이 유효한지 확인하는 sql 문장을 사용합니다. 일반적으로 간단한 sql이며, 설정을 권장합니다.

3.8testOnBorrow

연결을 요청할 때 연결을 확인하는 것을 권장하지 않습니다. 성능에 심각한 영향을 미칩니다.

3.9testOnReturn

연결을 반환할 때 연결을 확인하는 것을 권장하지 않습니다. 성능에 심각한 영향을 미칩니다.

3.10testWhileIdle

이를 통해, 배경에서 연결을 정리하는 스레드는 일정 시간 간격으로 휴지 연결에 validateObject을 수행하며, 연결이 실패하면 제거됩니다. 성능에 영향을 미치지 않으며, 권장합니다.

3.11numTestsPerEvictionRun

각 번째에 대해 연결을 확인하는 횟수를 나타냅니다. 권장은 maxActive와 같은 크기를 설정하는 것이며, 이렇게 하면 각 번째에 대해 모든 연결을 효과적으로 확인할 수 있습니다.

3.12预热连接池

연결 풀에 대해, 애플리케이션을 시작할 때 예열을 권장합니다. 외부에 대한 접근을 제공하기 전에 간단한 sql 쿼리를 수행하여 필요한 연결 수를 풀에 채우는 것입니다.

4索引优化

데이터량이 많아지면, sql 최적화로 성능을 향상시키기 어려워지면, 대형 스킬을 사용해야 합니다: 인덱스. 인덱스는 세 가지 등급이 있으며, 일반적으로 이 세 가지 등급을 다룰 필요가 있습니다. 또한, 인덱스를 만드는 필드에 대해 선택성을 고려해야 합니다.

4.1一级索引

where 뒤의 조건에 인덱스를 만들 수 있습니다. 단일 열은 일반 인덱스를 만들 수 있으며, 여러 열은 조합 인덱스를 만들 수 있습니다. 조합 인덱스는 가장 왼쪽 프리كس 원칙을 고려해야 합니다.

4.2二级索引

데이터가 order by 또는 group by를 사용하는 필드가 있을 경우, 이 필드에 인덱스를 만들 수 있습니다. 이렇게 하면 인덱스가 기본적으로 정렬되어 있어 order by 및 group by로 인한 정렬을 피할 수 있으며, 성능을 향상시킬 수 있습니다.

4.3、三级索引

위 두 가지 방법이 모두 성공하지 않으면, 필요한 필드에 인덱스도 추가하여, 이를 통해 인덱스 커버링이 형성됩니다. 이렇게 하면 I를 줄일 수 있습니다./O 작업,因为我:mysql 데이터를 검색할 때, 먼저 주키 인덱스를 검색한 후, 주키 인덱스를 통해 일반 인덱스를 검색한 다음, 일반 인덱스를 통해 해당 기록을 검색합니다. 우리가 필요한 기록이 일반 인덱스에 모두 있으면, 세 번째 단계는 필요하지 않습니다. 물론, 이러한 인덱스 구성 방식은 매우 극단적이며, 일반적인 상황에 적합하지 않습니다.

4.4、인덱스 선택성

인덱스를 구성할 때, 선택성이 높은 필드에 구성하도록 노력합니다. 선택성이 높다는 것은, 이 필드를 통해 검색된 데이터량이 적다는 것을 의미합니다. 예를 들어, 이름을 통해 한 사람의 정보를 검색하면, 검색된 데이터량은 일반적으로 많지 않습니다. 반면, 성별을 통해 검색하면 데이터베이스의 절반 이상의 데이터가 검색될 수 있습니다. 따라서, 이름은 선택성이 높은 필드이며, 성별은 선택성이 낮은 필드입니다.

5、档案权限

데이터량이 1년에 증가500W 시간대에, 인덱스도 무력화됩니다. 이때 일반적인 생각은 분할 데이터베이스와 테이블을 고려하는 것입니다. 사업이 폭발적으로 성장하지 않았지만, 데이터는 점진적으로 증가하면, 복잡한 기술 수단인 분할 데이터베이스와 테이블을 고려하지 않고, 역사 데이터를档案权限하는 것이 좋습니다. 우리는 생명주기가 끝난 역사 데이터에 대해, 예를 들어6개월 전의 데이터를,档案权限归档。凌晨定时将6개월 전의 데이터를 검색하여 원격 hbase 서버에 저장합니다. 물론, 필요할 때마다 역사 데이터를 검색할 수 있는 인터페이스를 제공해야 합니다.

이것이 mysql 단일 서버 데이터베이스 최적화 자료 정리입니다. 이후 추가 자료를 계속 보충하겠습니다. 감사합니다.

선언: 이 문서의 내용은 인터넷에서 수집되었으며, 저작권은 원저자에게 있으며, 인터넷 사용자가 자발적으로 기여하고 업로드한 내용입니다. 이 사이트는 저작권을 소유하지 않으며, 인공 편집을하지 않았으며, 관련 법적 책임을 부담하지 않습니다. 저작권 침해가 의심되는 내용이 있으면, notice#w로 이메일을 보내 주세요.3codebox.com에 (보내는 이메일에서 #을 @으로 변경하십시오) 신고하시고 관련 증거를 제공하시면, 사실 여부를 확인한 후, 이 사이트는 즉시 저작권 침해 내용을 삭제할 것입니다.