Início / dba / Perguntas / 30798
Accepted
ashuthosh
Asked: 2012-12-20 21:05:39 +0800 CST

Como otimizar essa consulta?

  • 772

Consulta:

Select * 
from `t_event` 
where `create_user_id`=7 
and (`event_create_date`)=('2012-12-18 00:00:00')
and `event_type_cd`=11
and `domain_id` =602
and `job_id` =1
limit 1

Estrutura da tabela:

mysql> show create table t_event\G
*************************** 1. row ***************************
       Table: t_event
Create Table: CREATE TABLE `t_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_user_id` int(11) DEFAULT NULL,
  `event_create_date` date DEFAULT NULL,
  `event_type_cd` int(11) NOT NULL,
  `event_desc` varchar(512) NOT NULL,
  `IsGlobalEvent` int(2) DEFAULT NULL,
  `event_start_date` datetime NOT NULL,
  `event_end_date` datetime NOT NULL,
  `job_id` int(11) DEFAULT NULL,
  `domain_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `event_user_FK` (`create_user_id`),
  KEY `domain_id_event_type_cd` (`domain_id`,`event_type_cd`)
) ENGINE=InnoDB AUTO_INCREMENT=8095673 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

Explique:

+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
| id | select_type | table   | type        | possible_keys                         | key                                   | key_len | ref  | rows  | Extra                                                               |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+
|  1 | SIMPLE      | t_event | index_merge | event_user_FK,domain_id_event_type_cd | domain_id_event_type_cd,event_user_FK | 8,5     | NULL | 14669 | Using intersect(domain_id_event_type_cd,event_user_FK); Using where |
+----+-------------+---------+-------------+---------------------------------------+---------------------------------------+---------+------+-------+---------------------------------------------------------------------+

Como posso otimizar esta consulta? Ele tem índices, mas está demorando muito para ser executado.

mysql performance

2 respostas

  1. Sua consulta tem 5 condições de igualdade em 5 colunas diferentes:

    select * 
from t_event 
where create_user_id=7 
  and event_create_date = '2012-12-18 00:00:00'  
  and event_type_cd = 11 
  and domain_id = 602 
  and job_id =1 

limit 1 ;                  -- why is this for?

    Agora você tem 2 índices ( domain_id_event_type_cde event_user_FK), que cobrem apenas 3 das 5 colunas/condições da consulta.

    Portanto, o plano que o otimizador escolhe é uma index_mergeoperação, por exemplo, ele usa os dois índices para encontrar linhas que correspondem às condições 2 e 1 respectivamente, depois os cruza ( Using intersect) para encontrar as linhas comuns e depois faz uma pesquisa adicional na tabela ( Using where) para as demais 2 condições não contempladas pelos índices.

    Você pode adicionar um índice composto em todas essas colunas. A ordem não importa para esta consulta, importa para outras consultas que você possa ter e se ela pode ser usada para elas:

    ALTER TABLE t_event 
    ADD INDEX i5
        (domain_id, event_type_cd, create_user_id, event_create_date, job_id) ;

    Em seguida, o Explainmostraria apenas Using indexe apenas o índice acima seria usado.

    • 8
  2. Best Answer

    Sugiro que você indexe para job_id supondo que haja muitos eventos para o mesmo trabalho e event_date supondo que haja muitos eventos na mesma data supondo que haja consultas que usam essas colunas para filtros.

    No entanto, veja a resposta de @ypercube abaixo, que tem uma explicação melhor para índices

    PS: Atualizado para remover sugestões errôneas que são deixadas abaixo para referência:

    b) Na consulta, posso ver que você está restringindo por uma data e hora, mas event_create_date é um campo de data, você pode melhorar a comparação usando TO_DAYS(event_create_date) = TO_DAYS('2012-12-18 00:00:00' )

    c) Conselhos adicionais sobre índices - adicione índices àquelas colunas com muitos dados repetitivos, os índices não são muito eficazes se os dados variarem muito ... (É por isso que @Rohan solicitou mais detalhes sobre os dados)

    • 1

relate perguntas