Início / dba / Perguntas / 83458
Accepted
ineersa
Asked: 2014-11-25 02:48:34 +0800 CST

MySQL select com otimização entre

  • 772

Tive problemas com algumas consultas de IP geográfico. Então basicamente aqui está o código de criação da tabela:

CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `begin` (`begin`),
    INDEX `end` (`end`),
    INDEX `code` (`code`)
)

E consultar:

SELECT * FROM `geo_ip_city` USE INDEX ( `end` ) WHERE `begin` <= 2523596988 AND `end` >= 2523596988 LIMIT 1

O tamanho da tabela é de cerca de 4,5 milhões de registros.

Essa consulta é executada quando todos os outros critérios não funcionam, mas ainda em horários de pico com bastante frequência.

No pior cenário (onde nada foi encontrado), ele é executado por 1,9444 segundos.

Aqui está explicado:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  geo_ip_city range   end end 8   NULL    2297506 Using index condition; Using where

Então, quando tenho muitos pedidos para esse recurso, meus servidores ficam loucos.

Pergunta:

Posso fazer algo com esta tabela ou consulta para aumentar o desempenho (talvez particionamento ou chaves complexas)? Ou devo assistir de alguma outra forma?

mysql performance

3 respostas

  1. Suas condições estão procurando intervalos. Agora imagine que você está procurando em um dicionário todas as palavras onde a primeira letra é "maior" do que A. Qual a utilidade do índice? Você deseja restringir o intervalo de pesquisa o máximo possível. MySQL na maioria das vezes só pode usar um índice por tabela. Combine aqueles begine endíndices.

    CREATE TABLE `geo_ip_city` (
    `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
    `begin` BIGINT(20) NOT NULL DEFAULT '0',
    `end` BIGINT(20) NOT NULL DEFAULT '0',
    `code` VARCHAR(2) NOT NULL DEFAULT '',
    `city` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    INDEX `idx_begin_end` (`begin`, `end`)
);

    Eu também removi o índice codeporque também é praticamente inútil. É composto por duas letras. Supondo que não haja caracteres/símbolos especiais incluídos, isso deixa você com 26*26=676 valores possíveis para esta coluna. Essa é uma seletividade de 676 / 4.500.000 = 0,0001. Você deseja ter a seletividade o mais próximo possível de 1.

    • 2
  2. Best Answer

    Se você tem 100% de certeza de que os intervalos (begin, end)nunca vão se sobrepor, pode usar essa consulta, que só precisa de um índice em (begin)ou (begin, end)e será muito mais eficiente do que você tem:

    SELECT t.*
FROM 
  ( SELECT g.* 
    FROM geo_ip_city AS g
    WHERE g.begin <= 2523596988 
    ORDER BY g.begin DESC    
    --  ORDER BY g.begin DESC, g.end DESC
    LIMIT 1
  ) AS t
WHERE t.end >= 2523596988 ;

    O único problema com o exposto acima é que essa restrição (intervalos não sobrepostos) não é aplicada pelo banco de dados. O Postgres tem um bom EXCLUDErecurso que pode ser usado exatamente para essa restrição - mas o MySQL não pode fazer isso apenas via DDL. Seu aplicativo ou procedimentos devem aplicá-lo.

    Portanto, se por acidente dois ou mais intervalos sobrepostos forem inseridos na tabela, a consulta poderá retornar 0 linhas - embora realmente exista uma correspondência.

    • 2

  3. se você tentar usar a mesma consulta, poderá ser otimizado adicionando índice composto a geo_ip_city

    o novo índice pode ser

    ALTER TABLE geo_ip_city ADD INDEX ind_begin_end (begin, end)

    com isso, você precisaria remover o índice de uso na declaração selecionada para ser

    SELECT * FROM geo_ip_city WHERE begin <= 2523596988 AND end >= 2523596988 LIMIT 1

    • 1

relate perguntas