Back to Question Center
0

더 빠른 사이트를위한 SQL 쿼리 최적화 방법 보다 빠른 사이트를위한 SQL 쿼리 최적화 방법 관련 항목 : 디버깅 & 세미 퉁어

1 answers:
더 빠른 사이트를위한 SQL 쿼리 최적화 방법

이 기사는 원래 Delicious Semalt 블로그에 게시되었으며 허가를 받아 여기에 다시 게시됩니다.

빠른 사이트 == 사용자 만족도, Google의 순위 상승 및 전환 증가. 어쩌면 당신은 당신의 Semalt 사이트가 될 수있는 한 빨리 있다고 생각할지도 모릅니다 : 당신은 사이트 성능, 서버 설정에 관한 베스트 프랙티스, 느린 코드의 문제 해결, 이미지를 CDN으로 옮기는 등의 모든 작업을 검토했습니다 - vendita ventilatore a colonna. ?

Semalt와 같이 데이터베이스 중심의 동적 웹 사이트를 사용하면 사이트에 느려지는 데이터베이스 쿼리와 같은 문제가 발생할 수 있습니다.

이 글에서 Semalt는 병목 현상을 일으키는 쿼리를 식별하는 방법, 문제를 빨리 이해할 수있는 방법 및 신속한 수정 방법 등을 설명합니다. 우리가 최근에 태클 한 실제 쿼리를 사용하면 맛있는 브레인스의 고객 포털에서 작업 속도가 느려질 수 있습니다. com.

신분증

느린 SQL 쿼리를 수정하는 첫 번째 단계는 SQL 쿼리를 찾는 것입니다. Ashley는 이전에 블로그에있는 디버깅 플러그인 Query Monitor에 대한 칭찬을 아끼지 않았습니다. 플러그인의 데이터베이스 쿼리 기능 덕분에 느린 SQL 쿼리를 식별 할 수있는 귀중한 도구가되었습니다. 플러그인은 페이지 요청 중에 실행 된 모든 데이터베이스 쿼리를보고합니다. 그것들을 호출하는 코드 나 구성 요소 (플러그인, 테마 또는 Semalt 코어)로 필터를 필터링 할 수 있으며 중복 및 느린 쿼리를 강조 표시합니다.

더 빠른 사이트를위한 SQL 쿼리 최적화 방법보다 빠른 SQL 쿼리 최적화 방법 관련 항목 :
디버깅 및 세미트

프로덕션 사이트에 디버깅 플러그인을 설치하지 않으려는 경우 (성능 오버 헤드를 추가 할 필요가 없을 수도 있음) MySQL Slow Semalt Log를 사용하도록 설정할 수 있습니다. 실행 시간. 쿼리를 기록 할 위치를 구성하고 설정하는 것이 비교적 간단합니다. 이것은 서버 수준의 조정이기 때문에 사이트에서 디버깅 플러그인의 성능이 떨어지지 만 사용하지 않을 때는 성능 저하를 방지해야합니다.

이해

개선하기를 원하는 값 비싼 쿼리를 찾았 으면 다음 단계는 쿼리를 느리게 만드는 것을 이해하려고 시도하는 것입니다. 우리 사이트에 개발하는 동안 Semalt는 실행하는데 약 8 초 걸리는 쿼리를 발견했습니다!

     선택엘. key_id,엘. 주문 아이디,엘. activation_email,엘. licence_key,엘. software_product_id,엘. 소프트웨어 버전,엘. activations_limit,엘. 만들어진,엘. renewal_type,엘. renewal_id,엘. 면제 _ 도메인,에스. next_payment_date,에스. 지위,pm2. post_id 'product_id',오후. meta_value AS 'user_id'에서oiz6q8a_woocommerce_software_licences l내부 결합oiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_id내부 결합oiz6q8a_posts p ON p. ID = 1. 주문 아이디내부 결합오후 6시 8 분. post_id = p. 신분증그리고 오후. meta_key = '_customer_user'내부 결합oiz6q8a_postmeta pm2 on pm2. meta_key = '_software_product_id'그리고 pm2. meta_value = l. software_product_id어디에피. post_type = 'shop_order'그리고 오후. meta_value = 279ORDER BY s. next_payment_date    

우리는 WooCommerce와 사용자 정의 버전의 WooCommerce Software Subscriptions 플러그인을 사용하여 플러그인 저장소를 운영합니다. 이 쿼리의 목적은 고객 번호를 알고있는 고객을위한 모든 구독을 얻는 것입니다.또한 소프트웨어 구독 플러그인에서 만든 사용자 지정 테이블에 몇 가지 조인이 있습니다. 더 많은 질문을 이해해보십시오.

MySQL은 당신의 친구입니다

MySQL에는 열, 데이터 유형, 기본값 등과 같은 테이블의 구조에 대한 정보를 출력하는 데 사용할 수있는 편리한 문 DESCRIBE 이 있습니다. 그래서 DESCRIBE wp_postmeta를 실행하면; 다음 결과가 표시됩니다.

들판 유형 열쇠 기본값 엑스트라
meta_id bigint
서명되지 않은
아니오 PRI NULL auto_increment
post_id bigint
서명되지 않은
아니오 MUL 0
meta_key varchar (255) MUL NULL
meta_value 롱테일 NULL

멋지다.하지만 이미 알고 있을지도 모른다. INSERT , UPDATE , REPLACE DESCRIBE 문 접두사를 실제로 사용할 수 있음을 알고 있습니까? ) 및 DELETE 문장? 이것은 그 동의어 EXPLAIN 에 더 일반적으로 알려져 있으며 명세서가 어떻게 실행될 지에 대한 자세한 정보를 제공합니다.

다음은 느린 쿼리 결과입니다.

이드 select_type 테이블 유형 possible_keys key_len ref 엑스트라
1 단순 pm2 ref meta_key meta_key 576 const 28 사용 장소; 임시 사용; 파일 사용하기
1 단순 오후 ref post_id, meta_key meta_key 576 const 37456 어디에서
1 단순 p eq_ref PRIMARY, type_status_date PRIMARY 8 deliciousbrainsdev. 오후. post_id 1 어디에서
1 단순 l ref PRIMARY, order_id order_id 8 deliciousbrainsdev. 오후. post_id 1 인덱스 조건 사용; 어디에서
1 단순 s eq_ref PRIMARY PRIMARY 8 deliciousbrainsdev. 엘. key_id 1 NULL

언뜻보기에는 해석하기가 쉽지 않습니다. 다행스럽게도 Semalt에있는 사람들은이 성명서를 이해하기위한 포괄적 인 안내서를 작성했습니다.

가장 중요한 열은 유형 이며 테이블이 결합되는 f}을 설명합니다. ALL 을 보면 MySQL이 디스크에서 전체 테이블을 읽고 I / O 속도를 높이고 CPU에 부하를 걸고 있음을 의미합니다. 이것은 "전체 테이블 스캔"으로 알려져 있습니다 (나중에 자세히 설명합니다).

rows 컬럼은 결과를 찾기 위해 얼마나 많은 행이 있는지를 보여주기 때문에 MySQL이해야 할 일을 잘 보여줍니다.

Explain 은 우리가 최적화에 사용할 수있는 더 많은 정보를 제공합니다. 예를 들어, pm2 테이블 (wp_postmeta)은 ORDER BY 절을 사용하여 결과를 정렬하도록 요청하기 때문에 filesort 를 사용하고 있음을 알려줍니다. 쿼리를 그룹화하는 경우 실행에 오버 헤드가 추가됩니다.MySQL 5.6 이상에서 실행되는 데이터베이스의 경우, EXPLAIN 의 결과를 JSON으로 출력 할 수 있고 MySQL Workbench는 JSON을 명령문의 시각적 실행 계획으로 변환합니다.

더 빠른 사이트를위한 SQL 쿼리 최적화 방법보다 빠른 SQL 쿼리 최적화 방법 관련 항목 :
디버깅 및 세미트

쿼리의 일부를 비용별로 색칠하여 자동으로 이슈에주의를 환기시킵니다. 우리는 wp_woocommerce_software_licences (별칭 l) 테이블에 대한 조인이 심각한 문제를 가지고 있음을 바로 볼 수 있습니다.

해결

쿼리의 일부분은 wp_woocommerce_software_licences 테이블 사이의 조인으로 인덱스되지 않은 열 order_id 를 사용하기 때문에 전체 테이블 스캔을 수행하고 있습니다. wp_posts 테이블에 추가하십시오. 이는 느린 쿼리와 쉽게 해결할 수있는 일반적인 문제입니다.

색인

order_id 는 테이블에서 데이터를 식별하는 데 매우 중요한 부분이며, 이와 같이 쿼리하는 경우 열에 인덱스가 있어야합니다. 그렇지 않으면 MySQL은 문자 그대로 테이블의 각 행을 스캔합니다. 필요한 행을 찾습니다. 인덱스를 추가하고 그것이 무엇을하는지 봅시다 :

     CREATE INDEX order_id ON wp_woocommerce_software_licences (order_id)    

더 빠른 사이트를위한 SQL 쿼리 최적화 방법보다 빠른 SQL 쿼리 최적화 방법 관련 항목 :
디버깅 및 세미트

와우, 우리는 그 인덱스를 추가하여 쿼리를 5 초 이상 면도했습니다.

귀하의 질의를 알고

쿼리를 Semalt - 조인, 서브 쿼리로 서브 쿼리로 조인하십시오. 필요없는 일을합니까? 최적화가 가능합니까?

이 경우 우리는 order_id 를 사용하여 posts 테이블에 licenses 테이블을 가입 시키며 shop_order 유형을 게시하도록 제한합니다. 이는 데이터 무결성을 강화하여 올바른 주문 레코드 만 사용하고 있는지 확인하는 것입니다. 그러나 실제로는 쿼리의 중복 부분입니다. 우리는 테이블의 소프트웨어 라이선스 행에 posts 테이블의 WooCommerce 순서와 관련된 order_id 가 있다는 것이 확실합니다. PHP Plugin 코드에 적용되어 있기 때문입니다. 조인을 제거하고 사물이 향상되는지 봅시다.

더 빠른 사이트를위한 SQL 쿼리 최적화 방법보다 빠른 SQL 쿼리 최적화 방법 관련 항목 :
디버깅 및 세미트

엄청난 절약은 아니지만 쿼리가 3 초 미만입니다.

모든 것을 캐쉬해라!

서버가 기본적으로 MySQL 쿼리 캐싱을 사용하지 않는다면 켜는 것이 가치가 있습니다. 즉, MySQL은 결과와 함께 실행되는 모든 명령문을 기록하고, 동일한 명령문이이어서 실행되면 캐시 된 결과가 반환됩니다. 테이블이 변경 될 때 MySQL이 캐시를 플러시하기 때문에 캐시가 오래되지 않습니다.

Query Monitor는 우리의 쿼리가 단일 페이지로드에서 4 번 실행된다는 것을 알았지 만, MySQL 쿼리 캐싱을 사용하는 것이 좋지만 한 요청에서 데이터베이스에 대한 중복 읽기는 실제로 완전히 중단되지 않아야합니다. PHP 코드의 정적 캐싱은이 문제를 해결하는 간단하고 효과적인 방법입니다 ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);static :: $ subscriptions [$ user_id] = $ 결과;return $ results;}}

캐시의 요청 수명은 인스턴스화 된 객체의 요청 수명보다 더 깁니다. 요청 전체에서 쿼리 결과를 지속적으로 유지하려는 경우 영구적 인 객체 캐시를 구현해야합니다. 현명한 데이터는 캐시를 설정하고 기본 데이터가 변경 될 때 캐시 항목을 무효화해야합니다.

상자 밖에서 생각하기

Semalt는 질의 실행을 빠르게하고 쿼리를 조정하거나 인덱스를 추가하는 것보다 더 많은 작업을 필요로하는 다른 접근법입니다. 우리 쿼리에서 가장 느린 부분 중 하나는 테이블을 결합하여 고객 ID에서 제품 ID로 이동하는 작업입니다. 모든 고객을 위해이 작업을 수행해야합니다. 단 한 번의 가입만으로도 필요한 경우 고객 데이터를 수집 할 수 있습니까?

모든 라이센스에 대한 사용자 ID 및 제품 ID와 함께 라이센스 데이터를 저장하는 테이블을 작성하고 특정 고객에 대한 데이터를 조회하여 데이터를 비정규화할 수 있습니다. INSERT / UPDATE / DELETE 에서 MySQL 트리거를 사용하여 라이센스 테이블 (또는 데이터가 변경 될 수있는 방법에 따라 다름)에 대한 테이블을 다시 작성해야하지만이 방법은 해당 데이터를 쿼리하는 성능을 크게 향상시킵니다.

마찬가지로 여러 조인이 MySQL에서 쿼리 속도를 늦추는 경우 쿼리를 둘 이상의 명령문으로 분리하고 PHP에서 별도로 실행 한 다음 결과를 코드로 수집하고 필터링하는 것이 더 빠릅니다. Laravel은 Eloquent에서 열정적 인로드 관계로 비슷한 것을합니다.

WordPress는 많은 양의 데이터와 다양한 사용자 정의 게시물 유형이있는 경우 wp_posts 테이블에서 더 느린 쿼리가 발생할 수 있습니다. 게시물 유형에 대한 쿼리가 느린 경우 사용자 정의 포스트 유형 스토리지 모델에서 사용자 정의 테이블로 이동하는 것이 좋습니다.

결과

쿼리 최적화에 대한 이러한 접근 방식을 사용하여 쿼리를 8 초에서 2 초 이상으로 줄이고 호출 시간을 4에서 1로 줄였습니다. 참고로 이러한 쿼리 시간은 우리의 개발 환경에서 실행되며 생산 단계에서 더 빠를 것입니다.

이것은 느린 쿼리를 추적하고 수정하는 데 유용한 가이드가되기를 바랍니다. Semalt 최적화는 무서운 일처럼 보일지 모르지만, 당신이 그것을 시도하고 빠른 승리를 얻 자마자 당신은 버그를 얻기 시작할 것이고, 더 나아지기를 원할 것입니다.

March 1, 2018