복붙노트

[SQL] 내부를 많이 사용하여 쿼리를 개선하는 것은 wp_postmeta, 키 / 값 테이블에 조인

SQL

내부를 많이 사용하여 쿼리를 개선하는 것은 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                                  |
+----+-------------+-----------+--------+-----------------------------+----------+---------+------------------------+------+----------------------------------------------+

여기에 워드 프레스 스키마.

해결법

  1. ==============================

    1.당신이 형 자동차의 포스트 당 하나의 행과 결과 세트를 취득하려고하는 것 같다. 당신이 게시물에 각 차량의 다양한 속성을 표시하려면, 그 멀리 postmeta에 은닉되어 보인다.

    당신이 형 자동차의 포스트 당 하나의 행과 결과 세트를 취득하려고하는 것 같다. 당신이 게시물에 각 차량의 다양한 속성을 표시하려면, 그 멀리 postmeta에 은닉되어 보인다.

    프로 팁 : 당신이 절대적으로 당신이 그것을하고있는 이유를 알고하지 않는 소프트웨어에 SELECT *를 사용하지 마십시오. 특히 쿼리, JOIN 연산을 많이 포함으로 무의미한 중복 열 * 반환을 많이 선택합니다.

    워드 프레스 postmeta 테이블에 대해 알아야 할 쿼리 디자인 트릭이있다. 특정 속성을 얻고 싶은 경우에,이 작업을 수행 :

     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은 키 - 값이나 단체 - 속성 - 값 저장소라는 테이블의 독특한 유형이기 때문에이 패턴이 필요합니다. 여기 무슨 일이야? 몇 가지:

    이 패턴을 사용에 익숙해지면 왼쪽의 폭포가 작업을 가입, 당신은과 같이, 다른 속성을 많이 얻으려면, 그것을 적재 할 수 있습니다.

         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 다음 여과 작업을 가입 모두와 조합 폭발을 받고 있었기 때문에 그것은 가능합니다. 그러나 어떤 속도로 당신이 보여준 쿼리는 아마 더 행을 반환하지 않았다.

    여전히 성능 문제가있는 경우 (post_id를, meta_key, meta_value) 컬럼에 postmeta에 복합 인덱스를 만들어보십시오. 당신이 워드 프레스 플러그인을 만드는 경우, 그 설치 시간을 플러그인에서 할 수있는 작업은 아마.

  2. ==============================

    2.이것은 워드 프레스 데이터베이스이며, 향후에 응용 프로그램이나 복잡한 업그레이드의 다른 부분을 깰 수 있기 때문에 당신은 스키마에 대한 광범위한 변경을 꺼려 수 있습니다.

    이것은 워드 프레스 데이터베이스이며, 향후에 응용 프로그램이나 복잡한 업그레이드의 다른 부분을 깰 수 있기 때문에 당신은 스키마에 대한 광범위한 변경을 꺼려 수 있습니다.

    이 쿼리의 어려움은 엔티티 - 속성 - 값 설계 단점 중 하나 보여줍니다. 그 디자인은 새로운 속성은 런타임에 생성하는 것이 허용하는 유연한에서, 그러나 그들은 기존의 테이블 것보다 그러한 데이터에 대한 쿼리의 훨씬 더 복잡합니다.

    워드 프레스에 대한 스키마는 잘 최적화되지 않았습니다. 심지어 최신 버전 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 지수는 filesort을 방지하는 데 도움이됩니다.

    POST_TITLE 및 meta_value가 TEXT 열 때문에 이러한 인덱스는 인덱스를 커버 할 수 없으며,이 완전히 인덱싱 할 너무 깁니다. 당신은 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           |
+----+-------------+--------------+------------+------+---------------+------+---------+----------------------------+------+----------+-----------------------+
  3. ==============================

    3.10 +가 UTF8 캐릭터 세트를 사용해 InnoDB의 테이블에 SQL 쿼리를 조인으로 해결 성능 문제에, postmeta에 새 인덱스를 만들 :

    10 +가 UTF8 캐릭터 세트를 사용해 InnoDB의 테이블에 SQL 쿼리를 조인으로 해결 성능 문제에, postmeta에 새 인덱스를 만들 :

    첫 번째 백업 데이터베이스. 피하기 위해 191 [wp_] postmeta.meta_key 길이를 줄이기 '지정된 키가 너무 길어서, 열쇠 최대 길이가 767 바이트 인 "오류.

    ALTER TABLE wp_postmeta MODIFY의 meta_key VARCHAR (191);

    인덱스 만들기

    INDEX wpm_ix ON wp_postmeta (post_id를, meta_key)를 작성;

  4. ==============================

    4.성능 시도의 경우 :

    성능 시도의 경우 :

    당신이 끌어하고자하는 컬럼에 명시해야합니다. 당신이 또는 필요하지 않을 수도 있습니다 어떤 인덱스를 참조하십시오. 행의 양이 끌려 제한합니다.

  5. ==============================

    5.이 낫다?

    이 낫다?

    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 비트)를 제한 할 수 있습니다. 당신이 사용하지 않습니다 많은 데이터를 반환 아무 소용이 없다. 전체 선택 목록이 인덱스에 의해 "포함"되어있는 경우에는 최적의 성능을 얻을 수 있습니다.

  6. ==============================

    6.당신을 가정하면 실제로 핸들 결과, 나는 그것이 훨씬 더 간단한 쿼리를하고 결과를 필터링하는 코드를 사용하는 것이 코드를 변경할 수 있습니다.

    당신을 가정하면 실제로 핸들 결과, 나는 그것이 훨씬 더 간단한 쿼리를하고 결과를 필터링하는 코드를 사용하는 것이 코드를 변경할 수 있습니다.

    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]
;
  7. ==============================

    7.워드 프레스에 좋은 쿼리 도구 WP_Query이있다. 포스트 메타 값을 검색하려면이 코드를 사용할 수 있습니다 :

    워드 프레스에 좋은 쿼리 도구 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();

    쿼리 API에 대한 자세한 내용은이 사이트를 방문, 다양한 예를 거기 : http://codex.wordpress.org/Class_Reference/WP_Query

  8. ==============================

    8.wp_postmeta을 가속화 여기에 자세히 설명되어 있습니다 : http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    wp_postmeta을 가속화 여기에 자세히 설명되어 있습니다 : http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    그리고 왜 wp_postmeta에 대한 참조가 너무 느리다?

    9. from https://stackoverflow.com/questions/26319613/improving-a-query-using-a-lot-of-inner-joins-to-wp-postmeta-a-key-value-table by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의 문 안에 PHP 변수를 포함하는 방법  (0) 2020.07.02
[SQL] SQL에서 계산 델타 (현재 및 이전 행의 차이)  (0) 2020.07.02
[SQL] 열 이름을 가져옵니다  (0) 2020.07.02
[SQL] C #에서 저장된 프로 시저에서 여러 레코드를 반환  (0) 2020.07.02
[SQL] 서열 값에 의해 그룹  (0) 2020.07.02
,

티스토리툴바