Início / dba / Perguntas / 177489
Accepted
Daniel Kanchev
Asked: 2017-06-29 04:00:37 +0800 CST

Como otimizar esta consulta MySQL específica que examina 2,84 milhões de linhas e 29,49 mil páginas distintas do InnoDB

  • 772

Estou tentando descobrir por que uma consulta precisa de ~ 5 segundos para ser concluída e como otimizá-la. Aqui estão as informações que reuni até agora:

1. EXPLAIN statement for the query:

EXPLAIN EXTENDED
SELECT SUM(meta2.meta_value) FROM ****_posts as posts 
LEFT JOIN ****_postmeta AS meta ON posts.ID = meta.post_id 
LEFT JOIN ****_postmeta AS meta2 ON posts.ID = meta2.post_id 
WHERE
meta.meta_key       = '_customer_user' AND
meta.meta_value     = '15448' AND
posts.post_type     = 'shop_order' AND 
posts.post_status   IN ( 'wc-processing','wc-completed' ) AND
meta2.meta_key      = '_order_total';


+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
| id | select_type | table | type   | possible_keys            | key      | key_len | ref                          | rows   | filtered | Extra       |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+
|  1 | SIMPLE      | meta2 | ref    | post_id,meta_key         | meta_key | 576     | const                        | 141630 |   100.00 | Using where |
|  1 | SIMPLE      | posts | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | barberkl_wp821.meta2.post_id |      1 |   100.00 | Using where |
|  1 | SIMPLE      | meta  | ref    | post_id,meta_key         | post_id  | 8       | barberkl_wp821.meta2.post_id |     18 |   100.00 | Using where |
+----+-------------+-------+--------+--------------------------+----------+---------+------------------------------+--------+----------+-------------+

3 linhas em conjunto, 1 aviso (0,06 seg)

2. pt-query-digest stats

 Profile
 Rank Query ID           Response time Calls R/Call V/M   Item
 ==== ================== ============= ===== ====== ===== ===============
    1 0x5C7505FDD18B918C 4.7628 100.0%     1 4.7628  0.00 SELECT 

 Query 1: 0 QPS, 0x concurrency, ID 0x5C7505FDD18B918C at byte 0 ________
 This item is included in the report because it matches --limit.
 Scores: V/M = 0.00
 Time range: all events occurred at 2017-06-28 07:16:34
 Attribute    pct   total     min     max     avg     95%  stddev  median
 ============ === ======= ======= ======= ======= ======= ======= =======
 Count        100       1
 Exec time    100      5s      5s      5s      5s      5s       0      5s
 Lock time    100   173us   173us   173us   173us   173us       0   173us
 Rows sent    100       1       1       1       1       1       0       1
 Rows examine 100   2.84M   2.84M   2.84M   2.84M   2.84M       0   2.84M
 Rows affecte   0       0       0       0       0       0       0       0
 Bytes sent   100      77      77      77      77      77       0      77
 Merge passes   0       0       0       0       0       0       0       0
 Tmp tables     0       0       0       0       0       0       0       0
 Tmp disk tbl   0       0       0       0       0       0       0       0
 Tmp tbl size   0       0       0       0       0       0       0       0
 Query size   100     412     412     412     412     412       0     412
 InnoDB:
 IO r bytes     0       0       0       0       0       0       0       0
 IO r ops       0       0       0       0       0       0       0       0
 IO r wait      0       0       0       0       0       0       0       0
 pages distin 100  29.49k  29.49k  29.49k  29.49k  29.49k       0  29.49k
 queue wait     0       0       0       0       0       0       0       0
 rec lock wai   0       0       0       0       0       0       0       0

Estou executando o Percona 5.6 e, se necessário, posso fornecer a configuração. Por favor, me oriente por onde começar. Obrigado!

Atualização (MOSTRAR CREATE TABLE):

 mysql> SHOW CREATE TABLE wpn0_postmeta \G;
 *************************** 1. row ***************************
        Table: wpn0_postmeta
 Create Table: CREATE TABLE `wpn0_postmeta` (
   `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
   `meta_key` varchar(255) DEFAULT NULL,
   `meta_value` longtext,
   PRIMARY KEY (`meta_id`),
   KEY `post_id` (`post_id`),
   KEY `meta_key` (`meta_key`(191)),
   KEY `woo_index1` (`post_id`,`meta_key`,`meta_value`(8))
 ) ENGINE=InnoDB AUTO_INCREMENT=6681190 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)

 mysql> SHOW CREATE TABLE wpn0_posts \G;
 *************************** 1. row ***************************
        Table: wpn0_posts
 Create Table: CREATE TABLE `wpn0_posts` (
   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content` longtext NOT NULL,
   `post_title` text NOT NULL,
   `post_excerpt` text NOT NULL,
   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
   `post_password` varchar(255) NOT NULL DEFAULT '',
   `post_name` varchar(200) NOT NULL DEFAULT '',
   `to_ping` text NOT NULL,
   `pinged` text NOT NULL,
   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `post_content_filtered` longtext NOT NULL,
   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
   `guid` varchar(255) NOT NULL DEFAULT '',
   `menu_order` int(11) NOT NULL DEFAULT '0',
   `post_type` varchar(20) NOT NULL DEFAULT 'post',
   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
   `comment_count` bigint(20) NOT NULL DEFAULT '0',
   PRIMARY KEY (`ID`),
   KEY `post_name` (`post_name`(191)),
   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
   KEY `post_parent` (`post_parent`),
   KEY `post_author` (`post_author`),
   KEY `woo_index2` (`post_type`,`post_status`,`ID`)
 ) ENGINE=InnoDB AUTO_INCREMENT=349189 DEFAULT CHARSET=utf8
 1 row in set (0.00 sec)
mysql innodb

2 respostas

  1. Best Answer

    O padrão EAV que é usado na metatabela do Wordpress leva a consultas que precisam de índices compostos. Sugiro estes três índices:

    ****_postmeta 
  (meta_key, meta_value(30), post_id)       -- for meta
****_postmeta 
  (meta_key, post_id, meta_value(30))       -- for meta2
****_posts 
  (post_type, id, post_status)          -- for posts

    A coluna meta_valueé do tipo text, então a coluna inteira não pode ser colocada no índice, então precisamos colocar apenas alguns primeiros caracteres (digamos (30)). Você também pode decidir alterar o tipo da coluna para varchar(100)ou algo assim, mas isso requer mais testes, primeiro que nenhum valor existente seja mais longo e depois algum tipo de verificação futura para que qualquer plug-in do Wordpress que tente armazenar valores mais longos não falhe (não faço ideia de como é fácil).

    Observe também que as LEFTjunções podem ser substituídas por INNERjunções. As WHEREcondições tornam-no equivalente.

    • 5

  2. Índices de "prefixo" geralmente não valem a pena serem usados, especialmente quando não são a última parte de um índice composto.

    Minhas recomendações envolvem a alteração do esquema post_meta em vários was; veja isso, que inclui a justificativa para cada etapa: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

    CREATE TABLE wp_postmeta (
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT NOT NULL,
    PRIMARY KEY(post_id, meta_key),
    INDEX(meta_key)
    ) ENGINE=InnoDB;

    INDEX(meta_key, post_id, meta_value(30))não é tão bom quanto PRIMARY KEY(post_id, meta_key)porque

    • As chaves secundárias primeiro penetram no BTree secundário, depois alcançam o PRIMARYBTree; isso evita o primeiro passo.
    • A indexação de prefixo não ajuda muito.

    Por ter PRIMARY KEY(post_id, meta_key), todas as linhas que você pode precisar varrer são adjacentes, levando a que muito poucos blocos precisem ser buscados.

    Alterar a postmetatabela como acima acelerará a maioria das consultas WP que a envolvem. Mas o verdadeiro problema com o SELECTé o seguinte ...

    A filtragem mais importante (user=15448) está escondida atrás de uma LEFT JOINtabela secundária. O user_id precisa estar dentro postse indexado. Ou pelo menos remova LEFTpara que o Otimizador comece com metaem vez de posts. Nesse ponto, o ypercube's (meta_key, meta_value(30), post_id)seria um pouco útil. Infelizmente, não será "cobrindo" por causa do inevitável "prefixo".

    • 0

relate perguntas