mysql: Оптимальный поисковый запрос и структура для запроса большого набора данных

Оптимальный поисковый запрос и структура для запроса большого набора данных

mysql performance full-text-search sql-like

63 просмотра

2 ответа

Я создал индексатор файлов, который просто вставляет имена файлов в указанную таблицу. Сейчас я рассматриваю лучший способ поиска по именам файлов. В таблице может быть более 100 000 файлов, поэтому производительность важна.

Имя файла может быть различным - 10, 20, 50 и более символов. По крайней мере, сейчас в моем тестовом наборе данных нет файлов с пробелами в именах. Пользователь может выполнить частичный поиск, например, поиск «1001» должен вернуть файл с именем 10_1001_20_30_40_50.

Моя текущая структура таблицы:

CREATE TABLE `file` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_category` int(10) unsigned NOT NULL,
  `filename` varchar(255) NOT NULL,
  `file_ext` varchar(3) NOT NULL,
  `date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`id_category`),
  KEY `idx_file_filename` (`filename`) USING BTREE,
  KEY `fk_file_1_idx` (`id_category`),
  FULLTEXT KEY `filename` (`filename`)
) ENGINE=MyISAM AUTO_INCREMENT=24974 DEFAULT CHARSET=utf8;

INSERT INTO `file` (`id`,`id_category`,`filename`,`file_ext`,`date_added`) VALUES (22474,14199,'095_98_1002_1003_148_98_1001_003','pdf','2016-03-19 19:02:12');
INSERT INTO `file` (`id`,`id_category`,`filename`,`file_ext`,`date_added`) VALUES (22475,14199,'095_98_1002_1003_148_98_1001_001','pdf','2016-03-19 19:02:11');

Я пытался использовать MATCH () AGAINST (), но оказалось, что это не очень хорошая идея, если у вас нет пробелов в строке и вы хотите сделать «если строка содержит поиск», например:

SELECT id, filename FROM `file` WHERE MATCH(filename) AGAINST ('1002*' IN BOOLEAN MODE);

Это не собирается возвращать то, что мне нужно. Я собираюсь использовать FULLTEXT, разделив все имена файлов при импорте на 3 части (минимальная длина строки, которую может предоставить пользователь), разделенных пробелами, и они используют такие запросы:

SELECT * FROM `file` WHERE MATCH(filename) AGAINST ('100*' IN BOOLEAN MODE);

Конечно, я могу оставить имена файлов без изменений и использовать оператор LIKE:

SELECT * FROM `file` WHERE filename LIKE '%100%'

но есть много отрицательных мнений об использовании LIKE для больших наборов данных. Мне интересно, если мое решение с добавлением пробелов в именах файлов будет хорошей идеей.

Автор: webrama.pl Источник Размещён: 26.10.2019 12:13

Ответы (2)

0 плюса

Попытка использовать FULLTEXT: требует пробела, ограничивает вас (в основном) полными «словами», становится неэффективным с «короткими» словами, пропускает «стоп-слова» и т. Д.

LIKE '%100%', хотя и неэффективно, потому что он должен проверить каждую строку, это то, что вам нужно.

Вы подразумеваете, что все соответствующие части имен файлов являются числами? И что вы хотите проверить только на целые детали? То есть 22_100_33будет искаться 22, 100и 33, но не для 2, 10, 00и т.д. ?? Если все это так, то LIKEне будет работать правильно. Пример: 101_1000будет пойман LIKE '%100%'.

Так, может быть , вы хотите , чтобы построить «инвертированный индекс»: Для 10_1001_20_30_40_50, вы бы иметь 6 строк в таблице: 10, 1001и т.д., и как все остальные столбцы, или какой - либо идентификатор (ы) для присоединения к fileстолу.

Автор: Rick James Размещён: 20.03.2016 06:04

0 плюса

есть много отрицательных мнений об использовании LIKE для больших наборов данных

Скорее всего, это будет достаточно для вашего случая, я бы проверил это в первую очередь.

Если Вы действительно хотите ускорить его, я могу придумать один вариант, но жертвы будут огромны - память, время вставки, ремонтопригодность, гибкость, сложность ... Вы можете создать «инвертированный индекс» для суффиксов. Таблица будет выглядеть так (псевдокод):

CREATE TABLE Pref(
    prefix varchar(255) NOT NULL,
    fileid bigint(20) unsigned NOT NULL,

CONSTRAINT [PK_Pref] PRIMARY KEY CLUSTERED 
(
    prefix ASC,
    fileid ASC
))

и иметь такие данные

'095_98_1002_1003_148_98_1001_003', 22474
'95_98_1002_1003_148_98_1001_003', 22474
'5_98_1002_1003_148_98_1001_003', 22474
'_98_1002_1003_148_98_1001_003', 22474
'98_1002_1003_148_98_1001_003', 22474
...
'03', 22474
'3', 22474

это будет кластеризованный первичный ключ на обеих колонках. Таким образом, он будет упорядочен по префиксу, и вы сможете изменить поиск по инфиксам на поиск '%abcd%'по префиксам 'abcd%'. Запрос тогда будет иметь форму

SELECT id, filename FROM `file` 
WHERE id IN (SELECT fileid FROM Pref WHERE prefix like 'abcd%')

Вы просто должны сделать триггеры, чтобы синхронизировать его с основной таблицей. Помните, что когда вы удаляете строку в этой таблице, вам следует избегать поиска fileid без заданного префикса, иначе производительность будет катастрофической.

Автор: Antonín Lejsek Размещён: 23.03.2016 03:19

Оптимальный поисковый запрос и структура для запроса большого набора данных

Ответы (2)

Вопросы из категории :