MySQL 성능 최적화 경험

쿼리에 대한 쿼리 최적화

대부분의 MySQL 서버에는 쿼리 캐싱이 활성화되어 있습니다. 이는 성능을 향상시키는 가장 효과적인 방법 중 하나이며 MySQL 데이터베이스 엔진에 의해 처리됩니다. 다수의 동일한 쿼리가 여러 번 실행되면 이러한 쿼리 결과는 캐시에 저장되므로 이후의 동일한 쿼리는 테이블을 조작할 필요 없이 캐시된 결과에 직접 액세스할 수 있습니다.

여기서 가장 큰 문제는 프로그래머가 이 문제를 쉽게 간과한다는 것입니다. 일부 쿼리 문으로 인해 MySQL이 캐시를 사용하지 않게 되기 때문입니다. 다음 예를 살펴보십시오.

// 查询缓存不开启
$r = mysql_query("SELECT username FROM user WHERE     signup_date >= CURDATE()");
// 开启查询缓存
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

위 두 SQL 문의 차이점은 CURDATE()입니다. MySQL의 쿼리 캐시는 이 함수에서 작동하지 않습니다. 따라서 NOW() 및 RAND()와 같은 SQL 함수 또는 기타 유사한 함수는 이러한 함수의 반환이 일시적이기 때문에 쿼리 캐싱을 활성화하지 않습니다. 따라서 필요한 것은 MySQL 함수를 캐싱을 활성화하는 변수로 바꾸는 것뿐입니다.

EXLAIN 사용 방법 알아보기

EXPLAIN 키워드를 사용하면 MySQL이 SQL 문을 처리하는 방법을 알 수 있습니다.

select id, title, cate from news where cate = 1

쿼리가 느린 것을 발견한 후 cate 필드에 인덱스를 추가하면 쿼리 속도가 빨라집니다.

한 행의 데이터만 필요한 경우 LIMIT 1을 사용하세요.

테이블의 일부를 쿼리하고 데이터는 하나만 필요합니다. 제한 1을 사용하세요.

색인을 올바르게 사용하세요

색인이 반드시 기본 키나 고유 필드에 필요한 것은 아닙니다. 테이블에 검색, 사진, 조건에 항상 사용하는 필드가 있는 경우 해당 필드에 대한 인덱스를 생성하세요.

ORDER BY RAND()

매우 비효율적인 임의 쿼리를 사용하지 마세요.

Avoid SELECT *

데이터베이스에서 읽는 데이터가 많을수록 쿼리 속도가 느려집니다. 또한 데이터베이스 서버와 웹 서버가 두 개의 독립된 서버인 경우 네트워크 전송 부하도 증가합니다. 필요한 것은 무엇이든 가져가는 좋은 습관을 길러야 합니다.

VARCHAR 대신 ENUM을 사용하세요

ENUM 유형은 매우 빠르고 컴팩트합니다. 실제로는 TINYINT를 보유하지만 문자열로 나타납니다. 이런 식으로 이 필드를 사용하여 선택 목록을 만드는 것이 매우 완벽해집니다.

"성별", "국가", "민족", "상태" 또는 "부서"와 같은 필드가 있고 이러한 필드의 값이 제한적이고 고정되어 있다는 것을 알고 있는 경우 다음을 사용해야 합니다. VARCHAR 대신 ENUM.

NOT NULL 사용

NULL 값을 사용해야 하는 매우 구체적인 이유가 있지 않는 한, 필드를 항상 NOT NULL로 유지해야 합니다. 다소 논란의 여지가 있을 수 있으니 계속 읽어보시기 바랍니다.

먼저 "Empty"와 "NULL"(INT인 경우 0과 NULL)의 차이점이 무엇인지 자문해 보세요. 둘 사이에 차이가 없다고 생각되면 NULL을 사용하면 안 됩니다. (아시나요? Oracle에서는 NULL과 빈 문자열이 동일합니다!)

NULL에는 공백이 필요하지 않고 추가 공간이 필요하며 비교를 수행하면 프로그램이 더 복잡해질 것이라고 생각하지 마십시오. 물론 이것이 NULL을 사용할 수 없다는 의미는 아닙니다. 현실은 매우 복잡하며 여전히 NULL 값을 사용해야 하는 상황이 있을 것입니다.

다음은 MySQL 자체 문서에서 발췌한 것입니다

"NULL 열은 해당 값이 NULL인지 여부를 기록하기 위해 행에 추가 공간이 필요합니다. MyISAM 테이블의 경우 각 NULL 열은 가장 가까운 바이트로 반올림되어 1비트를 추가로 사용합니다. "

IP 주소는 UNSIGNED INT로 저장됩니다.

많은 프로그래머는 정수 IP 대신 문자열 형식으로 IP를 저장하기 위해 VARCHAR(15) 필드를 만듭니다. 정수를 사용하여 저장하는 경우 4바이트만 필요하며 고정 길이 필드를 가질 수 있습니다. 또한 이는 특히 ip1과 ip2 사이의 IP와 같은 WHERE 조건을 사용해야 하는 경우 쿼리 시 이점을 제공합니다.

IP 주소가 전체 32비트 부호 없는 정수를 사용하므로 UNSIGNED INT를 사용해야 합니다.

고정 길이 테이블이 더 빠릅니다

테이블의 모든 필드가 "고정 길이"인 경우 전체 테이블은 "정적" 또는 "고정 길이"로 간주됩니다. 예를 들어 테이블에는 VARCHAR, TEXT, BLOB 유형의 필드가 없습니다. 이러한 필드 중 하나를 포함하는 한 테이블은 더 이상 "고정 길이 정적 테이블"이 아니며 MySQL 엔진은 이를 다른 방식으로 처리합니다.

고정 길이 테이블은 MySQL이 더 빠르게 검색하기 때문에 성능을 향상시킵니다. 이러한 고정 길이를 사용하면 다음 데이터의 오프셋을 쉽게 계산할 수 있으므로 자연스럽게 읽는 속도도 빨라집니다. 그리고 필드의 길이가 고정되어 있지 않으면 다음 필드를 찾으려고 할 때마다 프로그램이 기본 키를 찾아야 합니다.

또한 고정 길이 테이블은 캐시 및 재구축이 더 쉽습니다. 그러나 유일한 부작용은 고정 길이 필드가 일부 공간을 낭비한다는 것입니다. 고정 길이 필드는 사용 여부에 관계없이 너무 많은 공간을 필요로 하기 때문입니다.

수직 분할

"수직 분할"은 데이터베이스의 테이블을 열별로 여러 테이블로 변환하는 방법으로, 테이블의 복잡성과 필드 수를 줄여 최적화 목적을 달성할 수 있습니다. 이렇게 분리된 필드로 구성된 테이블을 자주 조인하지 않을 경우 분할되지 않은 경우보다 성능이 저하되어 극단적인 저하가 발생한다는 점에 유의해야 합니다.

대규모 DELETE 또는 INSERT 문 분리

온라인 웹사이트에서 대규모 DELETE 또는 INSERT 쿼리를 실행하는 경우 작업으로 인해 전체 웹사이트가 응답을 중지하지 않도록 매우 주의해야 합니다. 이 두 작업은 테이블을 잠그기 때문에 테이블이 잠기면 다른 작업이 들어갈 수 없습니다.

Apache에는 많은 하위 프로세스나 스레드가 있습니다. 따라서 매우 효율적으로 작동하며 우리 서버는 너무 많은 하위 프로세스, 스레드 및 데이터베이스 링크를 갖는 것을 원하지 않습니다. 이는 많은 서버 리소스, 특히 메모리를 차지합니다.

테이블을 30초 등 일정 시간 동안 잠그면 트래픽 양이 많은 사이트의 경우 이 30초 동안 누적된 액세스 프로세스/스레드 수, 데이터베이스 링크 수, 오픈 파일 수는 웹 서비스가 중단될 뿐만 아니라 전체 서버가 즉시 중단될 수도 있습니다.

열이 작을수록 더 빠릅니다

대부분의 데이터베이스 엔진에서 하드 디스크 작업이 가장 심각한 병목 현상일 수 있습니다. 따라서 데이터를 압축하면 하드 드라이브에 대한 액세스가 줄어들기 때문에 이러한 상황에서 매우 도움이 될 수 있습니다.

올바른 스토리지 엔진 선택

MySQL에는 MyISAM과 InnoDB라는 두 가지 스토리지 엔진이 있으며 각 엔진에는 장단점이 있습니다.

MyISAM은 많은 수의 쿼리가 필요한 일부 애플리케이션에 적합하지만 많은 수의 쓰기 작업에는 적합하지 않습니다. 필드만 업데이트해도 테이블 전체가 잠기며 읽기 작업이 완료될 때까지 다른 프로세스, 심지어 읽기 프로세스도 작동할 수 없습니다. 또한 MyISAM은 SELECT COUNT(*)와 같은 계산 속도가 매우 빠릅니다.

InnoDB의 추세는 매우 복잡한 스토리지 엔진이 될 것이며 일부 소규모 애플리케이션의 경우 MyISAM보다 느릴 것입니다. 또 다른 이유는 "행 잠금"을 지원하므로 쓰기 작업이 많을수록 더 좋아질 것입니다. 또한 트랜잭션과 같은 고급 애플리케이션도 지원합니다.

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