Início / dba / Perguntas / 158075
Accepted
Chris Muench
Asked: 2016-12-14 11:48:12 +0800 CST

indexação de texto completo MYSQL múltipla para innodb e memória + eficiência de disco

  • 772

Eu tenho a seguinte tabela: (Create table statement)

CREATE TABLE `phppos_people` (
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `person_id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`person_id`),
  KEY `phppos_people_ibfk_1` (`image_id`),
  FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
  FULLTEXT KEY `first_name_search` (`first_name`),
  FULLTEXT KEY `last_name_search` (`last_name`),
  FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
  FULLTEXT KEY `email_search` (`email`),
  FULLTEXT KEY `phone_number_search` (`phone_number`),
  CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

Tenho requisitos para uma pesquisa rápida usando nome, sobrenome, e-mail e número de telefone de uma só vez; mas também ser capaz de pesquisar cada campo individual. Eu também gosto da classificação de relevância do texto completo que não pode ser feito com Btree

As consultas funcionam bem, mas de acordo com meu provedor de banco de dados em nuvem, eles recomendam índices btree para melhor uso de memória e disco.

Tenho alguma outra opção/configuração ou forma de criar índices que não usem muito disco + memória? Tenho cerca de 40 índices de texto completo por banco de dados que seguem um padrão semelhante. O desempenho é ótimo com esses índices e realmente acelerou o produto.

O texto completo do Innodb foi projetado para usar muita memória + disco? Aqui estão minhas configurações de variável mysql

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

Aqui estão algumas estatísticas do meu provedor

Após a revisão, tive uma ideia do tamanho real do conjunto de dados armazenado no InnoDB. Foi uma surpresa quando descobri que o tamanho do conjunto de dados é muito menor do que o uso do disco, conforme listado abaixo:

Essa discrepância entre o uso do disco e do espaço de tabela me preocupou inicialmente com o fato de termos algo órfão, como tabelas temporárias antigas, mas depois descobri que isso estava sendo causado por um uso intenso de índices InnoDB Full Text . 

PHP-POS-DB-01
  On Disk:                              18GB
  Total DataSet Size:                   4.56GB
  Unused allocated Tablespace:          3.10GB  ## Fragmentation
  Total innodb used plus fragmentation: 7.66GB

PHP-POS-DB-2-01
  On Disk:                              9.7G
  Total DataSet Size:                   923.85MB
  Unused allocated Tablespace:          363.00MB
  Total innodb used plus fragmentation: 1.29GB
mysql innodb

1 respostas

  1. Best Answer

    first_nametem uma palavra, correto? FULLTEXT(first_name) is not a use case for that type of indexing. I recommend you use simpleINDEXes` para quaisquer colunas simples.

    Isso funciona de maneira muito eficiente, provavelmente mais rápido do que qualquer coisa que possa ser feita com FULLTEXT:

    WHERE last = 'Muench' AND first LIKE 'C%'
INDEX(last, first)

    Você ainda pode querer FULLTEXT(first, last, email, phone)lidar com o caso em que qualquer um deles pode ser inserido. (No entanto, não está claro se este é um bom caso de uso.)

    Enquanto isso, não use cegamente (255)para todas as colunas de string; use limites sensatos.

    Muitas vezes, é aconselhável fazer com que a interface do usuário construa a WHEREcláusula com base no que o usuário faz/não fornece. No seu caso, isso pode incluir alternar entre MATCH..AGAINST, =e LIKE, talvez até REGEXP.

    Você também pode procurar @descobrir e-mails, todos os dígitos para descobrir telefones, etc., evitando assim pesquisar o sobrenome [email protected].

    Como alternativa, você pode adicionar uma coluna extra que contenha todas as 'palavras' para nome, e-mail, etc. Em seguida, tenha um único FULLTEXTíndice nessa coluna; não há necessidade de 40. A desvantagem é a necessidade de manter essa informação redundante. Com um novo MySQL ou MariaDB suficiente, pode ser uma coluna 'gerada'.

    • 4

relate perguntas