문제 설명
200K 게시물이 있는 Wordpress 사이트는 JOIN이 느린 SQL_CALC_FOUND_ROWS를 게시합니까? 속도 최적화? (Wordpress site with 200K posts SQL_CALC_FOUND_ROWS with JOINs slow? Optimize for speed?)
Intel(R) Xeon(R) CPU E5‑2620 v2 @ 2.10GHz, 24코어/32GB ram 서버를 사용하고 있으며 200,000개의 게시물이 있는 사이트의 쿼리에 놀랐습니다. 다음은 그 중 일부입니다.
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+ | 아이디 | 사용자 | 호스트 | DB | 명령 | 시간 | 상태 | 정보 | 진전 | +‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+ | 187729 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 2 | 데이터 전송 | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta. wp_posts.ID = mt1.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (436695) AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (30,6,6,703,030) AND wp_term_relationship 4646,4647,4648) ) AND ( wp_postmeta.post_id IS NULL OR ( mt1.meta_key = ' _Original Post ID' AND mt1.meta_value = 'deleted' ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC 4 5 | 0.000 | | 187742 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 1 | 데이터 전송 | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) LEFT JOIN wp_postmeta ON(wp_posts.ID = wp_postmeta. wp_posts.ID = mt1.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (289150) AND ( wp_posts. ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (30) ) AND wp_term_relationships.term_taxonomy_id IN (79,308,674) AND ( wp_postmeta.post_id IS NULL AND_meta. ) ) AND wp_posts.post_type = '게시' AND (wp_posts.post_status = '게시') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 45 | 0.000 | | 187755 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 8 | 데이터 전송 | ID 선택 SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id = mt1.post_id' AND_O ) LEFT JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (161366) AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_id) ( ( wp_postmeta.meta_key = '_Photo Source Domain' AND wp_postmeta.meta_value = 'www.site1.com' ) AND ( mt1.post_id IS NULL OR ( mt2.meta_key = '_Original Post ID' AND mt2.meta_value = 'deleted') ) ) ) AND wp_posts.post_type = '게시' AND (wp_posts.post_status = '게시') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 9 | 0.000 | | 187787 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 3 | 데이터 전송 | SQL_CALC_FOUND_ROWS wp_posts를 선택합니다. post_date DESC LIMIT 0, 9 | 0.000 | | 187798 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 2 | 데이터 전송 | ID SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id = mt1.post_id) ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (161180) AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (30) ) _postmeta = (. wp_ '_Photo Source Domain' AND wp_postmeta.meta_value = 'www.site1.com' ) AND ( mt1.post_id IS NULL OR ( mt2.meta_key = '_Original Post ID') AND mt2.meta_value = 'deleted' ) ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 9 | 0.000 | | 187845 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 5 | 데이터 전송 | ID SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id = mt1.post_id) ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (160175) AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (30) ) _postmeta = (. wp_posts. ' _사진 소스 도메인' AND wp_postmeta.meta_value = 'www.site1.com' ) AND ( mt1.post_id IS NULL OR ( mt2.meta_key = '_Original Post ID' AND mt2.meta_value = 'deleted' ) ) ) AND wp_posts.post_type = '게시' AND (wp_posts.post_status = '게시') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 13 | 0.000 | | 187901 | 사이트 닷컴 | 로컬 호스트 | 사이트 닷컴 | 쿼리 | 0 | 데이터 전송 | ID SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id = mt1.post_id) ON ( wp_posts.ID = mt2.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (159918) AND ( wp_posts. ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (30) ) ) AND ( ( wp_postmeta.meta_key = '_Photo Source Domain' AND wp_postmeta.meta_value = 'www.site1.com' ) AND ( mt1.post_id IS mt2.meta_key = '_Original Post ID' AND mt2.meta_value = 'deleted' ) ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts 0, 13 | 0. | +‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑+
Wordpress를 엉망으로 만드는 몇 가지 기능을 시도했지만 post_id_meta_key 인덱스를 추가하려고 시도했지만 그것도 실패. 아이디어가 있습니까? 대단히 감사합니다!! 이 사이트와 mysql로 인해 내 서버는 현재 1000% CPU 사용량으로 로드 평균이 20‑30 이상입니다.
참조 솔루션
방법 1:
WordPress, in my opinion, has not been designed to handle more than a few thousand Posts.
SQL_CALC_FOUND_ROWSrequires (sort of) scanning all 200K rows to get the number. Notice how search engines got away from saying "... out of 212,345" Posts long ago. Maybe you should, too.wp_postmetahas inefficiencies that can be overcome. See https://wordpress.stackexchange.com/questions/248207/simple‑sql‑query‑on‑wp‑postmeta‑very‑slowORis inefficient, try to avoid it.
(There may be more that would help those queries.)
방법 2:
It's been a while but I was able to incredibly reduce the time for queries very similar to the one mentioned in the question by creating the index below:
CREATE INDEX post_id_meta_key ON wp_postmeta (post_id, meta_key(191));
I found this by browsing a Github issue comment posted for the Yoast plugin project
Hopefully it will help others with similar problems in the future.
(by lion、Rick James、fagiani)