MySQL 최적화 고려 사항

데이터베이스를 운영할 때 mysql을 최적화해야 합니다. 이 글에서는 최적화 주의사항에 대해 설명합니다.

첫 번째, 하드웨어가 너무 낡았습니다
하드웨어는 주로 CPU, 메모리, 디스크의 세 가지 측면에 대해 이야기합니다. 기사의 길이로 인해 네트워크 카드, 컴퓨터실 네트워크 등과 같은 몇 가지 요소도 있습니다. , 앞으로는 하나씩 소개하지 않겠습니다. 기회가 있으면 이야기해 보겠습니다.
먼저 MySQL의 CPU 활용 특성을 살펴보겠습니다.
5.1은 4코어, 5.5는 24코어, 5.6은 64코어를 사용할 수 있습니다. 예를 들어 MySQL5.6은 잘 작동하면 48개 이상의 코어를 사용할 수 있습니다. , 64 CORE까지 사용이 가능합니다. (48CORE~64CORE 사이에서는 공식 발표는 48 CORE 이고, 실제 테스트에서는 64 CORE 까지 도달할 수 있습니다.)
MySQL 5.6은 48코어 이상을 사용할 수 있습니다
*MySQL 5.1은 이전에는 최대 4코어까지 사용할 수 있습니다 **
요즘 일반 프로덕션 환경 서버는 32CORE 이상입니다.
그래서 저는 여러분의 회사 서버가 코어가 4개나 코어가 1개뿐인 매우 오래된 서버를 사용하고 있지 않는 한 여기 있는 모든 사람들에게 가능한 한 MySQL5.5 또는 MySQL5.6을 사용하도록 권장합니다.
5.1 이전(5.0과 동일)이기 때문에 내부 코드에 하드코딩되어 있고 innobase 스토리지 엔진 기반이어서 데이터베이스의 하드웨어 활용도가 좋지 않았습니다. InnoDB 엔진으로 진화한 이후에는 훨씬 좋아졌습니다.
각 연결은 스레드(스레드 풀 아님)이며 각 쿼리는 하나의 코어만 사용할 수 있습니다.
또한 MySQL에서는 각 쿼리가 하나의 CPU만 사용할 수 있습니다.
Oracle은 병렬 SQL과 병렬 쿼리를 사용합니다. 이런 종류의 기능은 MySQL에 존재하지 않습니다.
실행 계획 캐시 없음(SQL 실행 계획 사전 컴파일 없음)
둘째, MySQL 내부에는 SQL 사전 컴파일이 없습니다. 따라서 오라클의 메모리 구조에는 라이브러리 캐시와 같은 구조가 없습니다. 따라서 MySQL에는 하드 구문 분석만 있고 소프트 구문 분석은 물론 소프트 구문 분석도 없습니다.
MySQL은 연결 수가 늘어날수록 성능 저하가 발생합니다.
이것도 MySQL의 결점이지만 MySQL 버전이 발전하면서 많은 솔루션이 등장했습니다.
예: 공식적으로 출시된 스레드 풀(TP라고 함). 동시 연결 수가 너무 많은 문제를 해결하기 위한 것이지만 이는 MySQL의 추가 구성 요소이며 공식 TP를 구입하려면 추가 비용이 필요합니다.
또한 비슷한 문제를 해결하기 위해 OneSQL 미들웨어를 개발한 중국의 Lou Fangxin이라는 사람이 있습니다.
결과 캐시가 있지만 쓸모가 없습니다.
MySQL에도 Oracle과 유사한 Query Cache라는 결과 캐시가 있지만 상대적으로 쓸모가 없는 기능이므로 거의 사용되지 않습니다.
실제 프로덕션 환경은 대부분 업데이트와 수정 작업이 잦은 OLTP 시스템이기 때문에, 데이터가 자주 업데이트되고 수정되는 환경에서 이 쿼리 캐시를 사용하면 MySQL의 성능이 심각하게 저하되므로 일반적으로 거의 사용되지 않습니다.
이제는 MySQL을 사용하기 때문에 기본적으로 InnoDB 스토리지 엔진을 사용합니다. 이전 MyISAM 엔진은 거의 사용되지 않습니다. (스토리지 엔진이 뭐죠? 모르시면 gg)
InnoDB 엔진은 트랜잭션 스토리지 엔진 자체이기 때문에 이 Query Cache를 전혀 켤 필요가 없고, InnoDB를 사용한다는 것은 트랜잭션 처리 기능은 확실히 빈번한 데이터 업데이트 및 수정이 발생합니다.
MySQL의 메모리 활용 특성을 다시 살펴보겠습니다
64비트 운영체제를 사용하는 서버는 ((2^64-1)/1024/1024/1024)G의 메모리를 사용할 수 있습니다
고속 동시 환경에서는, 메모리 캐싱은 기본적으로 디스크 IO 영향을 줄이기 위해 사용됩니다.
일반적으로 메모리는 실제 데이터의 15%-20%에 따라 계획됩니다. 특히 데이터가 많이 사용되는 경우 캐시를 위해 더 큰 비율을 고려해야 합니다. data
이 데이터의 15~20%를 일반적으로 핫 데이터라고 합니다. (이것도 일반적인 경험치입니다)
예를 들어, MySQL의 전체 데이터 용량을 약 500G로 추정한다면 MySQL이 제공하는 메모리는 75G(5000.15)일 수 있으며, 약 128G의 메모리가 필요할 수 있습니다. 섬기는 사람.
또한 QQ Farm과 같은 일부 기업은 특히 핫하고 대용량의 핫 데이터(15%~20% 범위를 크게 초과할 수 있음)를 보유하게 됩니다.
예전에 QQ팜, 해피팜 등 음식훔치기 게임은 다들 해봤을 거라 믿습니다. (12306 티켓 예매 사이트도 있습니다)
이러한 비즈니스는 우리 업계에서 많은 주목을 받고 있습니다. 이러한 비즈니스의 특징은 데이터가 핫할 때 기본적으로 100% 핫 데이터라는 것입니다. 예를 들어 QQ Farm에서 모두가 플레이하면 모두가 찾아옵니다. 매일 놀고, 가끔씩 야채를 훔쳐가곤 했습니다. 많은 사람들이 한밤중에 일어나 화장실에 갈 때 야채를 한 줌 훔쳤습니다.
따라서 이러한 유형의 비즈니스를 위해서는 MySQL 데이터베이스의 메모리 구성을 늘려야 합니다. 15~20%로는 충분하지 않습니다.
요약: ****일반 비즈니스에서는 사용자 센터, 주문 및 기타 일반 비즈니스와 같은 핫 데이터를 계획하는 데 15%-20%가 사용됩니다. 일부 특수 사업의 경우 구체적인 상황을 자세히 분석해야 합니다.
쿼리 응답 시간을 기준으로 가이던스 할당이 가능합니다
이러한 대규모 온라인 아키텍처(대규모 데이터베이스 계획 및 설계)를 수행할 때,
SQL 쿼리의 응답 시간도 매우 중요한 지표입니다.
이런 대규모 시스템에서는 온라인으로 비즈니스를 수행하려면 수백만 또는 수천만 명의 사용자를 동시에 수용해야 합니다. SQL 쿼리(쿼리)의 응답 시간은 엄격하게 제어되어야 합니다. 시스템은 얼마나 많은 시간 내에 제어되어야 합니다.
예를 들어 핵심 라이브러리의 경우 쿼리의 응답 시간(평균 응답)이 30ms 미만이어야 합니다. 30ms를 초과하는 경우 데이터베이스가 부하 한도에 도달한 것으로 판단하여 데이터베이스를 확장해야 합니다.
또한, 이 Query 응답 시간에 대한 장기적인 지표 모니터링이 필요합니다.
이것이 핵심 라이브러리입니다. 예를 들어 로그를 저장하는 라이브러리나 성능 요구 사항이 너무 높지 않은 일부 라이브러리가 있는 경우 쿼리 응답 시간을 1초 또는 2초 이내로 완화할 수 있습니다.
비즈니스의 중요성에 따라 이 쿼리 응답 시간의 임계값을 결정합니다.
이것은 매우 중요한 지침 원칙입니다. 쿼리 응답 시간을 기준으로 성능 용량을 계획하세요.
용량에는 성능 용량과 공간 용량의 두 가지 유형이 있습니다. 공간 용량은 매우 간단합니다. 즉, SIZE 데이터를 몇 개 배치하는지, T.
성능 용량이 더 중요하며 비즈니스 압박과 부하를 처리할 수 있는지 여부를 결정합니다.
모두가 기억해야 할 점: 처리하려는 비즈니스가 수백 명의 사용자가 아닌 수백만 명의 활성 사용자라면 성능이 가장 중요하며 비즈니스 요구 사항을 충족하는 것이 가장 중요합니다.
아무리 기능이 훌륭하고 제품이 아무리 우수하더라도 성능이 타의 추종을 불허하면 다른 모든 것은 말도 안 되는 일입니다. 수십만 명의 사람들이 전체 시스템과 프로젝트를 몇 초 만에 중단시킬 수 있습니다. 눈이 멀게 될 것이다.
열심히 일해주신 유저분들도 대량으로 손실이 나고 손실이 클 것입니다.
성능이 기본입니다. 전체 아키텍처는 성능이 이를 견딜 수 있는 경우에만 의미가 있습니다. 성능이 만족스럽지 않으면 나중에 고가용성을 고려하는 것은 쓸모가 없습니다.
MySQL의 디스크 활용 특성
Binlog, redo log, undo log 순차적 IO
MySQL에는 다양한 IO 유형이 있습니다.
Binlog, redolog, undolog는 모두 순차적 IO 쓰기입니다.
이런 것들을 SSD에 넣을 필요가 없습니다. 기존 기계식 디스크에 순차적으로 쓰는 것도 매우 빠릅니다. 게다가 SSD에는 쓰기 손실과 쓰기 수명의 문제가 없습니다. SSD에 넣어야 합니다. 기존 SAS 디스크에 넣는 것만으로도 충분합니다. SSD를 넣을 필요가 없습니다.
SSD는 데이터 파일을 저장하는 데 사용됩니다. 데이터파일에서 발생하는 IO의 대부분은 Random IO이기 때문에 SSD에서는 Random IO를 실행하는 것이 매우 유리합니다. SSD 솔리드 스테이트 디스크 + 기존 디스크 SAS 디스크를 혼합하여 저장합니다. 또한 백업 디스크로 SSD를 사용하지 마십시오.
데이터파일 무작위 IO와 순차 IO 결합
순차 IO가 항상 더 빠릅니다. 데이터베이스 설계에서 당신이 훌륭한 DBA인지 훌륭한 설계자인지를 결정하는 것은 비즈니스를 최대한 순차 IO로 설계하면서 무작위 IO를 줄일 수 있는지 여부에 달려 있습니다. 예를 들어 친구 관계 비즈니스를 디자인할 때 순차적 IO를 통해 쿼리로 친구 관계를 꺼낼 수 있기를 바랍니다. 그러면 어떻게 디자인해야 할까요?
MySQL의 InnoDB에서는 InnoDB의 기능인 클러스터형 인덱스 테이블을 활용할 수 있습니다. (오라클의 IOT와 유사)
이 기능을 사용하면 사용자의 친구 데이터를 한 페이지 또는 여러 인접 페이지에 최대한 수집할 수 있습니다. 읽기 시 순차 읽기 IO가 가능해 성능이 크게 향상됐다.
친구 관계 테이블의 구조는 다음과 같습니다(전제 테이블은 InnoDB 엔진입니다):
owner_id friend_id(친구 ID)
위 두 필드는 기본 키로 사용됩니다. InnoDB의 기본 키는 클러스터형 인덱스입니다. 그러면 이 두 필드를 읽는 순서는 반드시 IO 가 될 수 있습니다.
과거에는 모든 데이터베이스 설계 서적에서 각 테이블에 자동 증가 기본 키에 대한 사양을 추가해야 한다고 항상 언급했습니다. 실제로 사양은 죽었고 위에서 예시한 친구 관계는 사용하지 않습니다. 비즈니스 속성이 있고 기본 키로 자주 읽는 두 개의 비즈니스 필드를 사용하는 대신 성능이 더 좋습니다.
그러므로 공부할 때 이 책에 나와 있는 규범과 규정을 외우지 말고, InnoDB의 내부 원리를 학습한 후 실제 작업에 도움을 받는 등 어떤 것의 원리를 실제로 이해해야 합니다. 원리, 원리를 사용하여 비유를 통해 학습합니다.
InnoDB의 원리는 엄청난 양의 지식이므로 시간이 지남에 따라 학습해야 합니다. 제 공식 계정에 좀 더 관심을 가져주시면 되며, InnoDB에 관한 일부 기사도 속속 공개될 예정입니다.
OLTP 비즈니스에는 더 많은 Random IO가 필요합니다.
메모리를 캐싱에 사용할 수 있으므로 Random IO가 줄어듭니다.
OLAP 비즈니스에는 더 많은 순차 IO가 필요합니다.
메모리 캐시는 그다지 유용하지 않습니다.
MySQL 5.6 이전에는 페이지 수정이 지원되지 않았으며 기본값은 16K였습니다.
MySQL5.6 이후 변경 가능합니다. 이 매개변수는 innodb_page_size이지만 MySQL5.6은 8K 또는 4K로만 변경 가능하며 MySQL5.7 이상까지는 32K 또는 64K로 변경할 수 없습니다.
OLAP 시스템의 경우 OLAP 시스템은 상대적으로 큰 쿼리가 있고 많은 데이터를 검색하므로 페이지가 클수록 성능 향상에 도움이 됩니다.
두 번째 포인트: 데이터베이스 디자인이 좋지 않습니다
예를 들어 트리거, 파티션, 많은 저장 프로시저, 함수 등 데이터베이스 기능이 많이 사용됩니다.
우리는 흔히 작은 것이 아름답다는 말을 합니다. 이는 단순함이 최고라는 뜻입니다. 데이터베이스의 모든 기능을 사용하게 되면 자연스럽게 데이터베이스의 성능이 저하되고, 버그 및 근본적인 오류가 발생할 확률이 높아집니다.
그러므로 좋은 데이터베이스 프로젝트 디자인은 작고, 아름답고, 간결하고 간결하다는 점을 모두가 이해해야 합니다. 또한 데이터베이스는 전체 프로젝트의 일부일 뿐입니다. 트리거 및 저장 프로시저와 같은 것들은 전체 프로젝트에서 애플리케이션 코드를 사용하여 확실히 구현할 수 있습니다.
그래서 우리는 MySQL을 사용할 때 MySQL의 모든 기능을 사용하기보다는 테이블, 인덱스, 트랜잭션과 같은 강력한 기능만 사용합니다.
또 다른 점은 MySQL 5.6 이전에는 프로덕션 환경의 기본 데이터베이스에서 하위 쿼리가 허용되지 않았다는 것입니다.
MySQL 5.6 이전의 하위 쿼리 성능은 특히 나빴습니다. (구문은 지원되지만 SQL 성능은 매우 낮습니다.)
예를 들어, 현재 Oracle을 사용하고 있고 Oracle을 MySQL로 마이그레이션하려는 경우 MySQL 5.6 버전을 사용하는 것이 좋습니다. MySQL 5.6은 하위 쿼리 지원 및 성능이 크게 향상되었습니다.
MySQL 5.6 하위 쿼리의 성능이 크게 향상됩니다.
포인트 3: 프로그램이 제대로 작성되지 않았습니다

DBA를 해본 학생들이라면 이런 경험을 해보았을 텐데요, 중소기업에서는 프로그래머의 수준이 천차만별입니다.
특히 업계에 갓 입문한 프로그래머(신입생)를 많이 만나면, 이 업계에 갓 입문한 프로그래머들 역시 매우 긴급한 요구 사항을 수행할 가능성이 더 높습니다. 이런 환경에서 개발된 프로그램은 생각하기 어렵습니다.
물론, 우리 프로그래머의 잘못은 아니므로 그들을 비난할 수는 없습니다.
위에서 언급한 현상의 가장 큰 원인은 국내 개발 환경에 있어서 제가 할 수 있는 일이 없습니다. 개발 요구가 시급하고(제품이 매일 활성화됨) 프로그래머들이 바쁘게 일하기 때문입니다(장기 야근). ) 그들은 비즈니스 프로그램을 구현하느라 바빠서 전혀 시간이 없습니다.
물론 이런 환경에서는 우리 DBA들에게는 기회입니다. 프로그래머가 작성한 잘못된 SQL과 복잡한 SQL로 인해 시스템이 느려지거나 심지어 충돌이 발생했습니다. 그런 다음 우리 DBA가 개입하여 이러한 잘못된 SQL과 느린 SQL을 최적화하고 변환했으며 시스템은 정상으로 돌아가 점점 안정되었습니다. 이는 또한 매우 성취감을 주는 일이며 동료와 리더들로부터 존경을 받게 될 것입니다.
동시에 DBA는 프로그래머를 위한 교육을 강화하고 좋은 SQL을 신속하게 작성하는 능력을 향상시킬 수도 있습니다. 더 적은 시간을 소비하고 더 나은 성능과 더 원활한 성능으로 SQL 문을 작성할 수 있습니다. 이를 통해 DBA의 부담도 줄일 수 있다.
저는 개인적으로 프로그래머들과 교육에 대해 이야기하는 것을 선호합니다. 첫째, 기술을 교환하면 누구나 무언가를 얻을 수 있습니다. 둘째, 앞으로 직장에서 협상해야 할 문제가 있으면 도움이 될 것입니다. 논의하기 쉽습니다. 이것은 그들에게 식사를 대접하는 것보다 낫습니다.
잘못 작성된 프로그램에 대해 주로 다음과 같은 해결책이 있습니다.
애플리케이션에서 데이터베이스 연결 풀을 사용하도록 합니다. 특히 JAVA를 기반으로 개발된 대규모 동시성 애플리케이션에서는 연결 풀을 사용해야 합니다.
연결 풀을 사용하면 애플리케이션에서 연결 수를 제한할 수 있다는 장점이 있습니다. 또한 새로 연결을 생성하기 때문에 각각의 추가 연결을 생성할 필요가 없습니다. 연결은 MySQL이 스레드를 생성하는 것과 동일합니다.
그리고 방금 연결 수가 증가하면 MySQL의 성능이 저하될 것이라고 언급했습니다.
프로그램 코드를 작성해 본 학생들은 우리의 일반 PC 노트북(보통 4CORE)에서 400개의 스레드를 생성하고 각 스레드가 1+1+1+1+... 간단한 작업을 수행한다는 것도 알고 있어야 합니다. PC가 멈춰 있는지 여부. PC의 CPU가 거의 가득 찼음을 알 수 있습니다. 감히 600개의 스레드를 만들면 컴퓨터가 곧 다시 시작됩니다. 스레드 오버헤드로 인해 CPU가 가득 차 있기 때문입니다.
복잡한 SQL 문
방금 말했듯이 프로그래머가 작성한 SQL은 결국 너무 바빠서 이 SQL의 성능과 운영을 고려하지 못하는 경우가 많습니다. 어떤 경우에는 프로그래머가 엮은 SQL이 전체 시스템을 직접 다운시킬 수도 있다.
간단한 예를 들어보겠습니다. 우리 애플리케이션 중 하나가 데이터베이스에 대해 10개의 연결을 생성합니다(최대 연결 수 = 10). 이 10개의 연결 각각은 동시에 동일한 복잡한 SQL을 실행하는 데 최소 10분이 걸립니다. 그러면 이 10개의 연결은 10분 이내에 이 복잡한 SQL만 실행할 수 있으며 다른 모든 후속 SQL은 차단됩니다.
대부분의 애플리케이션은 10분 동안 사용할 수 없겠죠? 그리고 눈사태를 일으키고 시스템이 붕괴될 수도 있습니다.
복잡한 SQL의 최적화도 DBA에게 매우 중요한 작업입니다. 이러한 복잡한 SQL, 느린 SQL, 잘못된 SQL을 모니터링 방법을 통해 찾아내고, 프로그래머에게 최적화 제안을 하는 것이 필요합니다. (DBA는 성능 비교 테스트를 진행해야 합니다.) 코드를 수정해야만 시스템이 교통 정체 없는 고속도로처럼 원활하고 병렬적으로 실행될 수 있습니다.
그렇다면 우리 회사 프로그래머들은 정말 대단하군요. 죽어도 SQL 코드를 바꾸지 않고, 죽어도 최적화도 안 하고, 통신도 안 하더군요. 그럼 우리는 어떻게 해야 할까요?
아직도 전용 슬레이브 라이브러리(Slave library)를 구축하여 이를 처리할 수 있는 방법이 있습니다.
예를 들어 우리 회사를 예로 들면, 보고서를 생성하는 백그라운드 시스템은 쿼리를 위해 슬레이브 데이터베이스에 연결되어 있으며 기본 데이터베이스에는 연결되지 않습니다.
잘못된 로직
전체 테이블 스캔
예: update t set a = a + 1; where 조건을 추가하는 것을 잊었습니다.
시스템이 수백만 명의 온라인 사용자를 지원하도록 하려면 SQL 검토 시스템을 추가하여 잘못된 논리가 있는 SQL과 전체 테이블 스캔이 있는 SQL을 제거해야 합니다.
SQL은 DBA의 검토 및 승인을 거친 후에만 온라인으로 공개될 수 있습니다.
또한 이런 대규모 업데이트 SQL은 일괄적으로 업데이트해야 하며, 대규모 SQL 작업을 작은 작업으로 나누어 실행해야 합니다. MySQL에서는 이에 특별한 주의가 필요합니다.
왜 일괄 업데이트하나요?
**이유 1. **위에서 언급했듯이 MySQL 쿼리는 하나의 CORE만 사용할 수 있습니다. SQL 트랜잭션은 너무 크고 복잡하며 실행하는 데 오랜 시간이 걸리므로 쉽게 정체가 발생합니다.
이유 2. 온라인 환경에서 MySQL은 일반적으로 마스터/슬레이브 아키텍처를 가지고 있습니다. 마스터에서 100만 행의 대규모 업데이트 트랜잭션이 발생하면 SLAVE가 단일이므로 SLAVE가 정체될 가능성이 높습니다. -스레드 구조로 인해 동기화 지연이 발생합니다.
MySQL은 SQL을 작성하고 빠르게 실행되고 빠르게 제출되는 작은 트랜잭션 SQL을 생성합니다. 각 쿼리가 더 빠르게 완료되고 연결이 더 빠르게 해제됩니다.

이 글에서는 MySQL 최적화 시 주의사항을 설명하고 있습니다. 관련 내용은 PHP 중국어 홈페이지를 참고해주세요.

관련 추천 :

Discuz!X/Database DB:: 함수 연산 방법

ThinkPHP 프레임워크 문자열 클래스 자세한 설명

JS 기본-수학 배열 Date

위 내용은 MySQL 최적화 고려 사항의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!