Início / dba / Perguntas / 30335
Accepted
ashuthosh
Asked: 2012-12-13 22:24:44 +0800 CST

como melhorar o desempenho da consulta

  • 772
## Only 1 query which returns 10 rows. 
## It took about 66 secs

mysql> explain 
    SELECT al.log_id , al.user_id , al.page_name , 
           al.section_name , al.submit_time , al.url  , 
           alfm1.value as 'source_env'  , 
           alfm2.value as 'website_id'  , 
           alfm3.value as 'index_id'  
    FROM  admin_logs al 
      LEFT OUTER JOIN 
        (select * from admin_log_field_map where field_id = 3351 
        )  alfm1 ON al.log_id = alfm1.log_id  
      LEFT OUTER JOIN 
        (select * from admin_log_field_map where field_id = 911 
        )  alfm2 ON al.log_id = alfm2.log_id  
      , admin_log_field_map alfm3  
    WHERE 1=1  
      AND al.page_name='Index Management'  
      AND al.log_id=alfm3.log_id 
      AND alfm3.field_id=891  
      AND alfm3.value='jewelry'  
    ORDER BY al.log_id DESC;

+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys      | key        | key_len | ref                 | rows  | Extra                                        |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | system | NULL               | NULL       | NULL    | NULL                |     0 | const row not found                          |
|  1 | PRIMARY     | al                  | ref    | idx_622,idx_pgname | idx_pgname | 78      | const               | 27720 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | alfm3               | ref    | idx_1533,idx_0311  | idx_1533   | 10      | cms.al.log_id,const |     1 | Using where                                  |
|  1 | PRIMARY     | <derived3>          | ALL    | NULL               | NULL       | NULL    | NULL                | 35358 |                                              |
|  3 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     | 33098 | Using where                                  |
|  2 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     |     1 | Using where                                  |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
6 rows in set (0.20 sec)

mysql>
mysql>
mysql> show create table admin_logs\G
*************************** 1. row ***************************
       Table: admin_logs
Create Table: CREATE TABLE `admin_logs` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `page_name` varchar(75) COLLATE latin1_bin DEFAULT NULL,
  `section_name` varchar(125) COLLATE latin1_bin DEFAULT NULL,
  `submit_time` datetime DEFAULT NULL,
  `url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `source_environment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `log` longtext COLLATE latin1_bin,
  UNIQUE KEY `idx_622` (`log_id`),
  KEY `idx_pgname` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> \! hostname
fol-piwikdb01.dca.ftd.untd.com
mysql> show create table admin_log_field_map\G
*************************** 1. row ***************************
       Table: admin_log_field_map
Create Table: CREATE TABLE `admin_log_field_map` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `field_id` int(11) DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_1533` (`log_id`,`field_id`),
  KEY `idx_0311` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)


how to get better performance using above query?
mysql performance

1 respostas

  1. Best Answer

    Primeiro, vamos reescrever a consulta descartando a sintaxe de junção implícita ANSI-89 e removendo as tabelas derivadas:

    SELECT 
       al.log_id , al.user_id , al.page_name , 
       al.section_name , al.submit_time , al.url  , 
       alfm1.value as 'source_env'  , 
       alfm2.value as 'website_id'  , 
       alfm3.value as 'index_id'  
FROM  
    admin_logs AS al 
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id 
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm2
      ON  alfm2.field_id = 911
      AND alfm2.log_id = al.log_id  
  INNER JOIN 
    admin_log_field_map AS alfm3  
      ON  alfm3.field_id = 891  
      AND alfm3.value = 'jewelry' 
      AND alfm3.log_id = al.log_id
WHERE 
    al.page_name = 'Index Management'  
ORDER BY 
    al.log_id DESC ;

    Também movi algumas das condições da WHEREcláusula para a cláusula de resposta ON, para que fique mais claro quais índices você precisa.

    Das duas junções externas, fica claro que da tabela admin_log_field_map, você só precisa das linhas com um fixo field_id(3351 ou 911) e apenas as colunas log_id(para fazer a junção) e value(para serem retornadas na SELECTlista):

    SELECT 
--
       alfm1.value as 'source_env'  , 
--
  LEFT OUTER JOIN 
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id

    Portanto, um bom índice para essas 2 junções à esquerda seria(field_id, log_id, value)

    A terceira junção (interna) para admin_log_field_map, é diferente. Você só precisa das linhas com fixo field_id(891) e fixo value('jóia') e depois log_id(para fazer o join). Depois disso, nada é realmente necessário para a SELECTlista (o valuejá é conhecido e corrigido):

    SELECT 
--
       alfm3.value as 'index_id'  
--
  INNER JOIN 
    admin_log_field_map AS alfm3  
      ON  alfm3.field_id = 891  
      AND alfm3.value = 'jewelry' 
      AND alfm3.log_id = al.log_id

    Portanto, um bom índice para essas 2 junções à esquerda seria(field_id, value, log_id)

    Para a admin_logstabela, você já tem um índice ativado (page_name), então provavelmente será usado para a WHEREcondição.

    Então, o que você pode fazer é adicionar esses dois índices:

    ALTER TABLE admin_log_field_map 
    ADD INDEX idx__field_id__log_id__value
        (field_id, log_id, value)
  , ADD INDEX idx__field_id__value__log_id
        (field_id, value, log_id) ;
    • 4

relate perguntas