Início / dba / Perguntas / 25029
Accepted
Ran
Asked: 2012-09-28 00:20:51 +0800 CST

Como posso melhorar esta consulta

  • 772

Eu tenho a seguinte tabela :

CREATE TABLE `twitter_relationships` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `source_and_target` (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index` (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

Esta tabela é bem grande, aproximadamente 150 milhões de registros.

E tenho a seguinte consulta:

SELECT target_twitter_id 
 FROM `twitter_relationships` 
WHERE (`twitter_relationships`.`relationship_status` = ? 
   AND `twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?) 
LIMIT ?, ?

Aqui está a explicação para esta consulta :

           id: 1
  select_type: SIMPLE
        table: twitter_relationships
         type: ref
possible_keys: source_and_target,target_status_and_change_date_index,user_id_index
          key: source_and_target
      key_len: 12
          ref: const,const
         rows: 8560582
        Extra: Using where

Alguma ideia do que posso fazer na consulta ou mesmo na estrutura da tabela para agilizar essa consulta?

mysql query

2 respostas

  1. Best Answer

    Primeiro, como você já tem um índice UNIQUE que contém o user_id, você deve conseguir se livrar do idcampo e usar o índice UNIQUE como PRIMARY KEY:

    CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

    Infelizmente, embora isso remova um índice, pode aumentar os requisitos de armazenamento devido à maneira como o InnoDB indexa os dados. Consulte "Como os índices secundários se relacionam com o índice agrupado" em http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

    Em segundo lugar, enquanto o source_and_targetíndice tem dois dos três campos em sua cláusula WHERE, o MySQL terá que fazer uma leitura adicional para encontrar o arquivo relationship_status.

    Portanto, para melhorar o desempenho, crie um índice que inclua todos os três campos em sua cláusula WHERE:

    CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

    Então, se o MySQL não usar esse índice automaticamente, você pode forçar o uso dele, com:

    SELECT target_twitter_id 
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?) 
LIMIT ?, ?

    Por fim, falta o UNSIGNEDatributo nos campos id, user_id, source_twitter_ide . target_twitter_idSuponho que esses campos nunca armazenarão valores negativos, portanto, faria sentido torná-los UNSIGNED.

    • 2

  2. Acho que a resposta para sua consulta está aqui - https://stackoverflow.com/questions/1823685/when-should-i-use-a-composite-index

    Pelo que me lembro, a ordem é importante na indexação e sua consulta tem where status, user_id, source_twitter_id. Você pode alterar a ordem da consulta para usar o índice source_and_target.

    espero que isto ajude!

    • 0

relate perguntas