키/값 테이블인 wp_postmeta에 대한 내부 조인을 많이 사용하여 쿼리 개선
다음 쿼리를 실행하는 워드프레스 웹사이트와 작업하고 있지만 이 쿼리는 내부 조인을 많이 하고 있어 웹 사이트를 로드하는 데 시간이 오래 걸리고 다운되는 것을 볼 수 있습니다.그리고 같은 결과를 내지만 아직 성공하지 못한 쿼리를 작성하려고 하고 있습니다.
어떻게 하면 좋을지 알고 싶습니다.
SELECT *
FROM wp_posts
INNER JOIN wp_postmeta color ON wp_posts.ID = color.post_id
INNER JOIN wp_postmeta transmission ON wp_posts.ID = transmission.post_id
INNER JOIN wp_postmeta model ON wp_posts.ID = model.post_id
INNER JOIN wp_postmeta brand ON wp_posts.ID = brand.post_id
AND color.meta_key = 'color'
AND color.meta_value = 'red'
AND transmission.meta_key = 'transmission'
AND transmission.meta_value = 'auto'
AND model.meta_key = 'model'
AND model.meta_value = 'model'
AND brand.meta_key = 'brand'
AND brand.meta_value = 'brand'
AND wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'car'
ORDER BY wp_posts.post_title
설명 출력은 다음과 같습니다.
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
| 1 | SIMPLE | color | ref | post_id,meta_key | meta_key | 768 | const | 629 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | wp_posts | eq_ref | PRIMARY,type_status_date,ID | PRIMARY | 8 | tmcdb.color.post_id | 1 | Using where |
| 1 | SIMPLE | brand | ref | post_id,meta_key | post_id | 8 | tmcdb.wp_posts.ID | 4 | Using where |
| 1 | SIMPLE | transmission | ref | post_id,meta_key | post_id | 8 | tmcdb.color.post_id | 4 | Using where |
| 1 | SIMPLE | model | ref | post_id,meta_key | post_id | 8 | tmcdb.transmission.post_id | 4 | Using where |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+
한 게시물당 하나의 행으로 구성된 결과 집합을 가져오려고 시도하는 것 같습니다.car게시물에 각 차량의 다양한 특성을 표시하고 싶은 것 같습니다.이것들은 에 저장되어 있습니다.postmeta.
프로 팁:사용 안 함SELECT *왜 그런 건지 확실히 알지 못하는 한 가지 이유가 있다면 말이죠.특히 많은 정보를 포함하는 쿼리의 경우JOIN조작,SELECT *의미 없는 다중 열을 많이 반환합니다.
WordPress를 위해 알아야 할 디자인 요령이 있습니다.postmeta특정 Atribute를 가져오려면 다음 절차를 따릅니다.
SELECT p.ID, p.post_title,
color.meta_value AS color
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS color ON p.ID = color.post_id AND 'color' = color.meta_key
WHERE p.post_status = 'publish'
AND /* etc etc */
당신이 하려는 일을 할 때 이 패턴을 이해하는 것은 매우 중요하다.이 패턴이 필요한 이유는postmeta는 key-value 또는 entity-value store라고 불리는 고유한 유형의 테이블입니다.이게 무슨 일이야?몇 가지 사항:
- 이 패턴을 사용하면 각 게시물에 대해 1개의 행을 얻을 수 있으며 일부 열은
posts테이블 및 특정 애트리뷰트postmeta테이블. - 너야
LEFT JOIN입력postmeta테이블에서 Atribute가 누락되어도 행을 얻을 수 있습니다. - 에일리어스명을사용하고있습니다.
postmeta테이블. 여기 있습니다.postmeta AS color. - 의 셀렉터를 포함합니다.
meta_key(여기 있습니다.'color' = color.meta_key)의ON결합 조건 - 에일리어스를 사용하고 있습니다.
SELECT제시 조항postmeta.meta_value적절한 열 이름을 가진 항목입니다.여기 있습니다color.meta_value AS color.
이 패턴을 사용하는 것에 익숙해지면, 캐스케이드를 사용해 쌓아 올릴 수 있습니다.LEFT JOIN이렇게 다양한 속성을 얻을 수 있습니다.
SELECT wp_posts.ID, wp_posts.post_title, wp_posts.whatever,
color.meta_value AS color,
transmission.meta_value AS transmission,
model.meta_value AS model,
brand.meta_value AS brand
FROM wp_posts
LEFT JOIN wp_postmeta AS color
ON wp_posts.ID = color.post_id AND color.meta_key='color'
LEFT JOIN wp_postmeta AS transmission
ON wp_posts.ID = transmission.post_id AND transmission.meta_key='transmission'
LEFT JOIN wp_postmeta AS model
ON wp_posts.ID = model.post_id AND model.meta_key='model'
LEFT JOIN wp_postmeta AS brand
ON wp_posts.ID = brand.post_id AND brand.meta_key='brand'
WHERE wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'car'
ORDER BY wp_posts.post_title
패턴을 알기 쉽게 하기 위해 이 쿼리를 여러 번 들여썼습니다.다른 들여쓰기 스타일을 선호할 수 있습니다.
질문의 쿼리에서 성능 문제가 발생한 이유를 알 수 없습니다.아마도 당신이 조합적인 폭발을 겪었기 때문일 겁니다INNER JOIN필터링된 작업입니다.그러나 어쨌든 표시된 쿼리는 행을 반환하지 않았을 수 있습니다.
가 있는 .postmeta (post_id, meta_key, meta_value)컬럼을 클릭합니다.WordPress 플러그인을 작성하는 경우 플러그인 설치 시 수행해야 할 작업일 수 있습니다.
이것은 Wordpress 데이터베이스이며, 향후 애플리케이션의 다른 부분이 손상되거나 업그레이드가 복잡해질 수 있기 때문에 스키마를 광범위하게 변경하는 것을 꺼릴 수 있습니다.
이 쿼리의 난이도는 entity-attribute-value 설계의 단점 중 하나를 나타냅니다.이 설계는 런타임에 새로운 속성을 생성할 수 있다는 점에서 유연하지만 이러한 데이터에 대한 많은 쿼리를 기존 테이블보다 더 복잡하게 만듭니다.
Wordpress용 스키마가 제대로 최적화되지 않았습니다.최신 버전 4.0에서도 인덱스에 오류가 있습니다.
이 특정 쿼리의 경우 다음 두 가지 인덱스가 도움이 됩니다.
CREATE INDEX `bk1` ON wp_postmeta (`post_id`,`meta_key`,`meta_value`(255));
CREATE INDEX `bk2` ON wp_posts (`post_type`,`post_status`,`post_title`(255));
bk1인덱스를 사용하면 올바른 메타 키와 값을 정확하게 검색할 수 있습니다.
bk2인덱스를 사용하면 파일 정렬을 피할 수 있습니다.
인덱스를 수 . 이 인덱스는 인덱스를 포함할 수 없기 때문입니다.post_title ★★★★★★★★★★★★★★★★★」meta_valueTEXT열이 너무 길어서 완전히 인덱싱할 수 없습니다. 하다 보면 VARCHAR(255)그러나 테이블에 긴 문자열을 저장하는 경우 응용 프로그램이 중단될 위험이 있습니다.
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
| 1 | SIMPLE | wp_posts | NULL | ref | bk2 | bk2 | 124 | const,const | 1 | 100.00 | Using index condition |
| 1 | SIMPLE | color | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | transmission | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | model | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index |
| 1 | SIMPLE | brand | NULL | ref | bk1 | bk1 | 1542 | wp.wp_posts.ID,const,const | 1 | 100.00 | Using index |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
★★★의 wp_postmeta자세한 것은, http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta 를 참조해 주세요.
wp_postmeta에 대한 참조가 느린 이유는 무엇입니까?
utf8 문자 집합을 사용하여 innodb 테이블에서 10+ Join SQL 쿼리 성능 문제를 해결하려면 postmeta에서 새 인덱스를 만듭니다.
데이터베이스를 먼저 백업합니다.[wp_]postmeta.meta_key"Specified key was too long; max key length is 767 bytes" 오류를 피하기 위해 length를 191로 설정합니다.
ALTER TABLE wp_postmeta MODIFY meta_key VARCHAR(191);
인덱스 생성
CREATE INDEX wpm_ix ON wp_postmeta (post_id, meta_key);
퍼포먼스 테스트의 경우:
끌어당길 열에 명시적이어야 합니다.필요한 인덱스와 필요하지 않은 인덱스를 확인합니다.꺼낼 행의 양을 제한합니다.
이게 더 나아요?
SELECT
P.*,
C.`meta_value` color,
T.`meta_value` transmission,
M.`meta_value` model,
B.`meta_value` brand
FROM
`wp_posts` P
JOIN
`wp_postmeta` C
ON P.`ID` = C.`post_id` AND C.`meta_key` = 'color'
JOIN
`wp_postmeta` T
ON P.`ID` = T.`post_id` AND T.`meta_key` = 'transmission'
JOIN
`wp_postmeta` M
ON P.`ID` = M.`post_id` AND M.`meta_key` = 'model'
JOIN
`wp_postmeta` B
ON P.`ID` = B.`post_id` AND B.`meta_key` = 'brand'
WHERE
C.`meta_value` = 'red'
AND
T.`meta_value` = 'auto'
AND
M.`meta_value` = 'model'
AND
B.`meta_value` = 'brand'
AND
P.`post_status` = 'publish'
AND
P.`post_type` = 'car'
ORDER BY
P.`post_title`
아직 속도가 느린 경우(아마도 느릴 수 있음), 이 인덱스를 추가해 보십시오.
CREATE INDEX `IX-wp_postmeta-post_id-meta_key-meta_value`
ON `wp_postmeta` (`post_id`, `meta_key`, `meta_value`);
도 이 수 .wp_post
CREATE UNIQUE INDEX `IX-wp_post-post_status-post_type-post_title-ID`
ON `wp_post` (`post_stauts`, `post_type`, `post_title`, `ID`);
리스트를 할 수 은, 한정할 수 있는 SELECT ★★★★★★★★★★★★★★★★★」FROM 좋다사용하지 않을 많은 데이터를 반환하는 것은 의미가 없습니다.전체 선택 목록이 인덱스로 "포함"되면 최상의 성능을 얻을 수 있습니다.
결과를 처리하는 코드를 실제로 변경할 수 있다고 가정하면 훨씬 더 간단한 쿼리를 만들고 결과를 필터링하기 위해 코드를 사용합니다.
SELECT [wp_posts fields you care about], wp_postmeta.meta_key, wp_postmeta.meta_value
FROM wp_posts
INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car'
AND wp_postmeta.meta_key IN ('color', 'transmission', 'model', 'brand')
ORDER BY wp_posts.post_title, wp_postmeta.meta_key, wp_postmeta.meta_value;
아니면, 이런 걸 할 수도 있고
SELECT [wp_posts fields desired], COUNT(*) AS matchCount
FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'car'
AND ((meta_key = 'color' AND meta_value = 'red')
OR (meta_key = 'transmission' AND meta_value = 'auto')
OR [etc...]
)
GROUP BY wp_posts.ID
HAVING matchCount = [number of key-value pairs you're checking]
;
WordPress에는 WP_Query라는 좋은 쿼리 도구가 있습니다.포스트 메타 값을 검색하려면 다음 코드를 사용합니다.
$args = array(
'post_type' => 'post',
'meta_query' => array(
array(
'key' => 'fieldname',
'value' => 'fieldvalue',
'compare' => 'LIKE',
),
),
);
$query = new WP_Query( $args );
if ( $query->have_posts() ) {
while ( $query->have_posts() ) {
$query->the_post();
$custom = get_post_custom();
print_r($post);
print_r($custom);
}
} else {
// no posts found
}
wp_reset_postdata();
query API에 대한 자세한 내용은 이 사이트를 방문하십시오.http://codex.wordpress.org/Class_Reference/WP_Query에는 수많은 예가 있습니다.
언급URL : https://stackoverflow.com/questions/26319613/improving-a-query-using-a-lot-of-inner-joins-to-wp-postmeta-a-key-value-table
'source' 카테고리의 다른 글
| javascript에서 fetch()를 사용하여 JSON 파일을 읽는 방법은 무엇입니까? (0) | 2023.03.07 |
|---|---|
| Jackson JsonNode에서 유형화된 컬렉션으로 (0) | 2023.03.07 |
| Oracle에서 모든 권한을 가진 사용자 생성 (0) | 2023.03.07 |
| 모든 경과 도달 가능 상태를 포함한 리액트컴포넌트를 리셋하려면 어떻게 해야 하나요? (0) | 2023.03.07 |
| application.properties를 사용하여 맵을 전달하는 방법 (0) | 2023.03.07 |