source

키/값 테이블인 wp_postmeta에 대한 내부 조인을 많이 사용하여 쿼리 개선

manycodes 2023. 3. 7. 21:37
반응형

키/값 테이블인 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 또는 store라고 불리는 고유한 유형의 테이블입니다.이게 무슨 일이야?몇 가지 사항:

  1. 이 패턴을 사용하면 각 게시물에 대해 1개의 행을 얻을 수 있으며 일부 열은posts테이블 및 특정 애트리뷰트postmeta테이블.
  2. 너야LEFT JOIN입력postmeta테이블에서 Atribute가 누락되어도 행을 얻을 수 있습니다.
  3. 에일리어스명을사용하고있습니다.postmeta테이블. 여기 있습니다.postmeta AS color.
  4. 의 셀렉터를 포함합니다.meta_key(여기 있습니다.'color' = color.meta_key)의ON결합 조건
  5. 에일리어스를 사용하고 있습니다.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 데이터베이스이며, 향후 애플리케이션의 다른 부분이 손상되거나 업그레이드가 복잡해질 수 있기 때문에 스키마를 광범위하게 변경하는 것을 꺼릴 수 있습니다.

이 쿼리의 난이도는 설계의 단점 중 하나를 나타냅니다.이 설계는 런타임에 새로운 속성을 생성할 수 있다는 점에서 유연하지만 이러한 데이터에 대한 많은 쿼리를 기존 테이블보다 더 복잡하게 만듭니다.

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

반응형