Início / dba / Perguntas / 35821
Accepted
Mark Tower
Asked: 2013-03-03 14:18:30 +0800 CST

Possível INDEX em um campo VARCHAR no MySql

  • 772

Estou trabalhando em um banco de dados MySql , com uma tabela assim:

+--------------+
|  table_name  |
+--------------+
|    myField   |
+--------------+

...e eu preciso fazer muitas consultas como esta (com 5-10 strings na lista) :

SELECT myField FROM table_name
WHERE myField IN ('something', 'other stuff', 'some other a bit longer'...)

Haverá cerca de 24.000.000 linhas únicas

1) Devo usar uma tecla FULLTEXTou e INDEXpara o meu VARCHAR(150)?
2) Se eu aumentar os chars de 150 para 220 ou 250... faria uma grande diferença? (Existe alguma maneira de calculá-lo?)
3) Como eu disse, eles serão únicos, então myField deve ser uma PRIMARY KEY . Não é raro adicionar uma PRIMARY KEY a um campo que já é um VARCHAR INDEX/FULLTEXT?

mysql index

2 respostas

  1. Best Answer

    SUGESTÃO #1 : Indexação Padrão

    CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    key (myfield)
);

    Se você indexar assim, poderá procurar a string inteira ou fazer pesquisas LIKE orientadas à esquerda

    SUGESTÃO #2 : Indexação de TEXTO COMPLETO

    CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    primary key (id),
    fulltext (myfield)
);

    Você pode efetivamente usar pesquisas por palavras-chave individuais, bem como frases inteiras. Você precisará definir uma lista de palavras irrelevantes personalizada porque o MySQL não indexará 543 palavras .

    Aqui estão meus outros posts dos últimos dois anos em índices FULLTEXT

    SUGESTÃO #3 : Indexação de Hash

    CREATE TABLE mytable
(
    id int not null auto_increment,
    myfield varchar(255) not null,
    hashmyfield char(32) not null,
    primary key (id),
    key (hashmyfield)
);

    Se você estiver procurando por um valor específico e esses valores puderem ter comprimentos muito além de 32 caracteres, você poderá armazenar o valor de hash:

    INSERT INTO mytable (myfield,hashmyfield)
VALUES ('whatever',MD5('whatever'));

    Dessa forma, você apenas procura por valores de hash para recuperar resultados

    SELECT * FROM mytable WHERE hashmyfield = MD5('whatever');

    De uma chance !!!

    • 84

  2. O MySQL permite que você defina um índice prefixado, o que significa que você define os primeiros N caracteres da string original a ser indexada, e o truque é escolher um número N que seja longo o suficiente para fornecer boa seletividade, mas curto o suficiente para economizar espaço. O prefixo deve ser longo o suficiente para tornar o índice quase tão útil quanto seria se você indexasse a coluna inteira.

    Antes de prosseguirmos, vamos definir alguns termos importantes. A seletividade do índice é a razão entre o total de valores indexados distintos e o número total de linhas . Aqui está um exemplo para a tabela de teste:

    +-----+-----------+
| id  | value     |
+-----+-----------+
| 1   | abc       |
| 2   | abd       |
| 3   | adg       |
+-----+-----------+

    Se indexarmos apenas o primeiro caractere (N=1), então a tabela de índice se parecerá com a seguinte tabela:

    +---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| a             | 1,2,3     |
+---------------+-----------+

    Neste caso, a seletividade do índice é igual a IS=1/3 = 0,33.

    Vejamos agora o que acontecerá se aumentarmos o número de caracteres indexados para dois (N=2).

    +---------------+-----------+
| indexedValue  | rows      |
+---------------+-----------+
| ab             | 1,2      |
| ad             | 3        |
+---------------+-----------+

    Neste cenário IS=2/3=0,66 o que significa que aumentamos a seletividade do índice, mas também aumentamos o tamanho do índice. O truque é encontrar o número mínimo N que resultará na seletividade máxima do índice .

    Existem duas abordagens que você pode fazer cálculos para sua tabela de banco de dados. Vou fazer uma demonstração no dump deste banco de dados .

    Digamos que queremos adicionar a coluna last_name na tabela employees ao índice e queremos definir o menor número N que produzirá a melhor seletividade de índice.

    Primeiro vamos identificar os sobrenomes mais frequentes:

    select count(*) as cnt, last_name from employees group by employees.last_name order by cnt

+-----+-------------+
| cnt | last_name   |
+-----+-------------+
| 226 | Baba        |
| 223 | Coorg       |
| 223 | Gelosh      |
| 222 | Farris      |
| 222 | Sudbeck     |
| 221 | Adachi      |
| 220 | Osgood      |
| 218 | Neiman      |
| 218 | Mandell     |
| 218 | Masada      |
| 217 | Boudaillier |
| 217 | Wendorf     |
| 216 | Pettis      |
| 216 | Solares     |
| 216 | Mahnke      |
+-----+-------------+
15 rows in set (0.64 sec)

    Como você pode ver, o sobrenome Baba é o mais frequente. Agora vamos encontrar os prefixos last_name que ocorrem com mais frequência , começando com prefixos de cinco letras.

    +-----+--------+
| cnt | prefix |
+-----+--------+
| 794 | Schaa  |
| 758 | Mande  |
| 711 | Schwa  |
| 562 | Angel  |
| 561 | Gecse  |
| 555 | Delgr  |
| 550 | Berna  |
| 547 | Peter  |
| 543 | Cappe  |
| 539 | Stran  |
| 534 | Canna  |
| 485 | Georg  |
| 417 | Neima  |
| 398 | Petti  |
| 398 | Duclo  |
+-----+--------+
15 rows in set (0.55 sec)

    Há muito mais ocorrências de cada prefixo, o que significa que temos que aumentar o número N até que os valores sejam quase os mesmos do exemplo anterior.

    Aqui estão os resultados para N=9

    select count(*) as cnt, left(last_name,9) as prefix from employees group by prefix order by cnt desc limit 0,15;

+-----+-----------+
| cnt | prefix    |
+-----+-----------+
| 336 | Schwartzb |
| 226 | Baba      |
| 223 | Coorg     |
| 223 | Gelosh    |
| 222 | Sudbeck   |
| 222 | Farris    |
| 221 | Adachi    |
| 220 | Osgood    |
| 218 | Mandell   |
| 218 | Neiman    |
| 218 | Masada    |
| 217 | Wendorf   |
| 217 | Boudailli |
| 216 | Cummings  |
| 216 | Pettis    |
+-----+-----------+

    Aqui estão os resultados para N=10.

    +-----+------------+
| cnt | prefix     |
+-----+------------+
| 226 | Baba       |
| 223 | Coorg      |
| 223 | Gelosh     |
| 222 | Sudbeck    |
| 222 | Farris     |
| 221 | Adachi     |
| 220 | Osgood     |
| 218 | Mandell    |
| 218 | Neiman     |
| 218 | Masada     |
| 217 | Wendorf    |
| 217 | Boudaillie |
| 216 | Cummings   |
| 216 | Pettis     |
| 216 | Solares    |
+-----+------------+
15 rows in set (0.56 sec)

    Isso são resultados muito bons. Isso significa que podemos fazer o índice na coluna last_name indexando apenas os primeiros 10 caracteres. Na definição da tabela, a coluna last_name é definida como VARCHAR(16), e isso significa que salvamos 6 bytes (ou mais, se houver caracteres UTF8 no sobrenome) por entrada. Nesta tabela existem 1637 valores distintos multiplicados por 6 bytes é cerca de 9KB, e imagine como esse número cresceria se nossa tabela contivesse milhões de linhas.

    Você pode ler outras maneiras de calcular o número de N no meu post Índices prefixados no MySQL .

    Usar funções MD5 e SHA1 para gerar valores que devem ser indexados também não é uma boa abordagem . Por quê? Leia no post Como escolher o tipo de dados correto para uma chave primária no banco de dados MySQL

    • 23

relate perguntas