MySQL vs файлы. Скорость поиска строки по подстроке. Sql поиск подстроки в строке
Поиск, если в строке содержится подстрока
Вопрос: Разбивка строки на подстроки
Здравствуйте!Понимаю что вопрос не новый, но чтение раньше созданных тем не помогло, так что прошу помощи.В таблице invd, есть строка check вида:#1) SFG.SGRFD.14.SR 458245965271524892 458265965272158435 100 #2) SFG.SRFD.14.SR 458245945277532658 458268965225462357 20 #3) SFG.SRFD.142.SR 4582459445680325527 45268967890546527 20 ZFG.FD.14.SR 4582459645673245527 458266789065459657 10 #4) SFG.SRFD.142.SR 4582459445680325527 45268967890546527 20 ZFG.SFD.14.SR 4582459645673245527 458266789065459657 80 4582459445680325527 45268967890546527 20
Количество символов в подстроках разное, разделены пробелом (chr (09)), в строке до шестисот символов. Девятый оракл.
select substr (t.check, 1, instr (t.check, chr (09), 1, 1) - 1) a, substr (t.check, instr (t.check, chr (09), 1, 1) + 1, instr (t.check, chr (09), 1, 2) - instr (t.check, chr (09), 1, 1) - 1) b, substr (t.check, instr (t.check, chr (09), 1, 2) + 1, instr (t.check, chr (09), 1, 3) - instr (t.check, chr (09), 1, 2) - 1) c, -- до этого момента работает, дальше выбирает только варианты указанные в строках подобных #3-4, в виде '2 ZFG.S.14.SR 4'. Строки #1-2 - null. substr (t.check_sim, instr (t.check, chr (09), 1, 3) + 1, instr (t.check, chr (09), 1, 4) - instr (t.check, chr (09), 1, 3) - 1) d from invd tОтвет: Вопрос снят, с уставших глаз не увидел что помимо chr (09), в строке присутствует и другой тип символов.
forundex.ru
MySQL vs файлы. Скорость поиска строки по подстроке.
На днях задался вопросом целесообразности хранение строковых данных в БД вместо файлов с точки зрения скорости поиска.
Хотя, конечно, вопрос правильней поставить так — чем быстрее осуществлять поиск в массиве строк, средствами языка программирования (в данном случае php) или средствами РСУБД (MySQL)? А сравнение «MySQL vs файлы» только для наглядности, ведь данные в базах тоже хранятся в файлах (а в чем же еще?) и используется все та же файловая система.
Решил провести небольшой тест. Сгенерировал 100к строк вида
MP0bWDXN1AxhI9yCZiGpKUZObSBOSrFv6vxTYkxPLUXjUmLJuiZ53PK4xcJgteCqAZ9p9w5LhTU15wBqFrlz6VtuX3Bg83xzSwOSTjt0seSoBkE6BPvyBPSoJjvHcS6VfLDYlXPD1ySsImp91Lxsrg
Каждая строка при генерации писалась одновременно в файл (*.txt) и в четыре таблицы — таблицу типа MyISAM, тип данных «text», таблицу MyISAM, тип данных «varchar(100)», таблицу InnoDB, тип данных «text» и таблицу InnoDB, тип данных «varchar(100)».
При генерации 100к строк из каждой тысячной строки из середины копировалось 10 символов, которые писались в отдельный файл. Таким образом получилось 100 подстрок, по которым и осуществляется поиск полной строки в файле и базе данных.
Суть теста заключается в следующем — берем первую подстроку, ищем ее вхождение в строках, сохраненных в файле и БД, потом берем вторую подстроку и так далее сто раз. При каждом поиске засекаем время, необходимое для нахождения строки по ее подстроке, потом вычисляем среднее и сравниваем.
Поиск при хранении строк в текстовом файле осуществлялся четырьмя способами:
1. При каждом поиске (которых всего, как говорилось выше, сто) происходит чтение файла в массив функцией file(), потом осуществляется проход по получившемуся массиву конструкцией foreach(), где ищется вхождение подстроки в строку строковой функцией strpos().
2. Аналогично, только для поиска подстроки вместо строковой функции используются регулярные выражения, функция preg_match().
Третий и четвертый способы повторяют первые два, за исключением того, что при подсчете времени, затраченного на каждый поиск, не учитывается время формирования массива из файла. т.е. считается только время, необходимое на поиск по уже находящемуся в оперативке массиву.
Поиск по каждой из четырех созданных таблиц осуществлялся восемью способами:
1. Открытие соединения с БД. Поиск подстроки с использованием в SQL запросе операции сравнения LIKE. Закрытие соединения.
2. Открытие соединения с БД. Поиск подстроки с использованием в SQL запросе операции сравнения REGEXP. Закрытие соединения.
Третий и четвертый способы аналогичны предыдущим двум, но в них не учитывается время, необходимое на открытие и закрытие соединения с БД. Точнее, соединение с БД не закрывается до тех пор, пока не будут выполнены все сто запросов.
И 5-8 способы повторяют 1-4, но только в настройках MySQL отключается кэширование запросов (по умолчанию кэширование осуществляется). При использовании кэширования, поиск фактически происходит не по базе данных, а по кэшу (запрос хэшируется и ищется в кэше, если совпадения найдены - результат запроса возвращается из кэша, а сам запрос не делается).
Ниже представлена таблица с полученными в ходе теста данными.
| Хранение данных | Способ поиска | Учет времени на чтение файла или открытие/закрытие соединения с БД | Среднее время поиска, с |
| Файл, *.txt | Строковые функции, strpos() | Да | 0.252027 |
| Файл, *.txt | Регулярные выражения, preg_match() | Да | 0.334894 |
| Файл, *.txt | Строковые функции, strpos() | Нет | 0.129508 |
| Файл, *.txt | Регулярные выражения, preg_match() | Нет | 0.303539 |
| БД, MyISAM, text, без кэширования запросов | Операция сравнения like "%pattern%" | Да | 0.202112 |
| БД, InnoDB, text, без кэширования запросов | Операция сравнения like "%pattern%" | Да | 0.335769 |
| БД, MyISAM, varchar(100) , без кэширования запросов | Операция сравнения like "%pattern%" | Да | 0.194496 |
| БД, InnoDB, varchar(100), без кэширования запросов | Операция сравнения like "%pattern%" | Да | 0.261031 |
| БД, MyISAM, text, без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Да | 1.074974 |
| БД, InnoDB, text, без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Да | 1.225922 |
| БД, MyISAM, varchar(100), без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Да | 1.070542 |
| БД, InnoDB, varchar(100), без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Да | 1.143679 |
| БД, MyISAM, text, без кэширования запросов | Операция сравнения like "%pattern%" | Нет | 0.190953 |
| БД, InnoDB, text, без кэширования запросов | Операция сравнения like "%pattern%" | Нет | 0.333155 |
| БД, MyISAM, varchar(100), без кэширования запросов | Операция сравнения like "%pattern%" | Нет | 0.189853 |
| БД, InnoDB, varchar(100), без кэширования запросов | Операция сравнения like "%pattern%" | Нет | 0.262696 |
| БД, MyISAM, text, без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Нет | 1.070554 |
| БД, InnoDB, text, без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Нет | 1.231657 |
| БД, MyISAM, varchar(100), без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Нет | 1.063102 |
| БД, InnoDB, varchar(100), без кэширования запросов | Операция сравнения regexp ".*pattern.*" | Нет | 1.139206 |
| БД, MyISAM, text, кэширование запросов | Операция сравнения like "%pattern%" | Да | 0.000332 |
| БД, InnoDB, text, кэширование запросов | Операция сравнения like "%pattern%" | Да | 0.000357 |
| БД, MyISAM, varchar(100) , кэширование запросов | Операция сравнения like "%pattern%" | Да | 0.000294 |
| БД, InnoDB, varchar(100), кэширование запросов | Операция сравнения like "%pattern%" | Да | 0.000377 |
| БД, MyISAM, text, кэширование запросов | Операция сравнения regexp ".*pattern.*" | Да | 0.000332 |
| БД, InnoDB, text, кэширование запросов | Операция сравнения regexp ".*pattern.*" | Да | 0.000349 |
| БД, MyISAM, varchar(100), кэширование запросов | Операция сравнения regexp ".*pattern.*" | Да | 0.000335 |
| БД, InnoDB, varchar(100), кэширование запросов | Операция сравнения regexp ".*pattern.*" | Да | 0.000365 |
| БД, MyISAM, text, кэширование запросов | Операция сравнения like "%pattern%" | Нет | 0.000071 |
| БД, InnoDB, text, кэширование запросов | Операция сравнения like "%pattern%" | Нет | 0.000126 |
| БД, MyISAM, varchar(100), кэширование запросов | Операция сравнения like "%pattern%" | Нет | 0.000071 |
| БД, InnoDB, varchar(100), кэширование запросов | Операция сравнения like "%pattern%" | Нет | 0.000108 |
| БД, MyISAM, text, кэширование запросов | Операция сравнения regexp ".*pattern.*" | Нет | 0.000072 |
| БД, InnoDB, text, кэширование запросов | Операция сравнения regexp ".*pattern.*" | Нет | 0.000070 |
| БД, MyISAM, varchar(100), кэширование запросов | Операция сравнения regexp ".*pattern.*" | Нет | 0.000071 |
| БД, InnoDB, varchar(100), кэширование запросов | Операция сравнения regexp ".*pattern.*" | Нет | 0.000109 |
Исходя из полученных данных, можно сделать несколько выводов, банальных и не очень.
Открытие или закрытие какого-то ресурса (будь то файл или соединение с БД) — очень времязатратная процедура. Оформлять кусок кода в функцию, где что-то открывается, пишется, закрывается, и потом эту функцию вызывать несколько раз в разных местах программы или даже в цикле — неправильное решение с точки зрения производительности.
Строковые функции работают быстрее, чем регулярные выражения. Поэтому то, что можно сделать строковыми функциями, надо делать именно строковыми функциями, а не регулярками.
Скорость поиска строковых данных в таблицах типа MyISAM быстрее, чем в таблицах типа InnoDB. Поэтому если при выборе таблицы того или иного типа основным критерием является скорость чтения и не требуется каких-то специфичных свойств (механизм транзакций, работа с внешними ключами), то использовать следует MyISAM.
Скорость поиска по данным типа text и varchar при использовании таблиц типа MyISAM примерно сопоставима. При использовании же таблиц типа InnoDB, скорость поиска по данным text осуществляется медленнее, чем по данным типа varchar.
Поиск с использованием в SQL запросах операции сравнения «REGEXP» работает в разы медленнее, чем с использованием операции сравнения «LIKE». Что еще раз подтверждает тот факт, что регулярными выражениями надо пользоваться только при отсутствии альтернатив.
В MySQL очень мощная система кэширования (которая по умолчанию включена). Если вы делаете какой-то запрос более одного раза, то повторный поиск не происходит, а вам отдаются данные из кэша со скорость в десяти-стотысячные доли секунды.
Вывод из сравнения можно сделать такой: без учета времени на открытие файла/соединение с БД, поиск средствами php по данным, находящимся в оперативной памяти, осуществляется в среднем в два раза быстрее, чем средствами MySQL по данным, находящимся в БД. Однако MySQL имеет систему кэширования запросов, поэтому если требуется регулярно обращаться к одним и тем же данным и делать одинаковые выборки, то выигрыш в скорости при использовании баз данных становится весьма значительным.
blog.tulvit.net
Функция SUBSTRING_INDEX | Трепачёв Дмитрий
Функция SUBSTRING_INDEX возвращает подстроку из строки перед появлениям N вхождений разделителя.
Если N положителен, то возвращается все, что находится слева от последнего разделителя (считая слева). Если N отрицателен, то возвращается все, что находится справа от последнего разделителя (считая справа).
См. также функцию LEFT, которая вырезает символы с начала строки.
См. также функцию RIGHT, которая вырезает символы с конца строки.
См. также функции SUBSTRING и MID которые вырезают символы с любого места строки.
Синтаксис
SELECT SUBSTRING_INDEX(поле, разделитель, количество_разделителей) FROM имя_таблицы WHERE условиеПримеры
Все примеры будут по этой таблице texts, если не сказано иное:
| 1 | слово1-слово2-слово3-слово4-слово5 |
| 2 | word1-word2-word3-word4-word5 |
Пример
В данном примере вернется подстрока перед первым вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', 1) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово1 |
| 2 | word1 |
Пример
В данном примере вернется подстрока перед вторым вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', 2) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово1-слово2 |
| 2 | word1-word2 |
Пример
В данном примере вернется подстрока перед третьим вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', 3) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово1-слово2-слово3 |
| 2 | word1-word2-word3 |
Пример
В данном примере вернется подстрока перед первым с конца вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', -1) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово5 |
| 2 | word5 |
Пример
В данном примере вернется подстрока перед вторым с конца вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', -2) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово4-слово5 |
| 2 | word4-word5 |
Пример
В данном примере вернется подстрока перед третьим с конца вхождением разделителя '-':
SELECT *, SUBSTRING_INDEX(text, '-', -3) as text FROM textsSQL запрос выберет следующие строки:
| 1 | слово3-слово4-слово5 |
| 2 | word3-word4-word5 |
code.mu
Самый быстрый способ найти строку подстрокой в SQL?
У меня огромная таблица с двумя столбцами: Id и Title. Id is bigint, и я могу выбрать тип столбца заголовка: varchar, char, text, whatever. Заголовок столбца содержит случайные текстовые строки, такие как «abcdefg», «q», «allyourbasebelongtous» с максимум 255 символами.
Моя задача - получить строки данной подстрокой. Подстроки также имеют случайную длину и могут быть начальными, средними или конечными строками. Самый очевидный способ его выполнения:
SELECT * FROM t LIKE '%abc%'Меня не волнует INSERT, мне нужно только быстро выбирать. Что можно сделать для выполнения поиска как можно быстрее?
Я использую MS SQL Server 2008 R2, полнотекстовый поиск будет бесполезным, насколько я вижу.
22
2017-07-11 14:45
источник
Ответы:
Если вы хотите использовать меньше места, чем ответ Рэнди, и в ваших данных много повторений, вы можете создать структуру данных дерева N-Ary, где каждое ребро является следующим символом и повесить каждую строку и конечную подстроку на ваши данные.
Вы набираете узлы в глубину первого порядка. Затем вы можете создать таблицу длиной до 255 строк для каждой вашей записи, с идентификатором вашей записи и идентификатором узла в дереве, который соответствует строковой или конечной подстроке. Затем, когда вы выполняете поиск, вы обнаружите идентификатор узла, который представляет строку, которую вы ищете (и все конечные подстроки), и выполните поиск диапазона.
7
2017-07-11 15:14
если вы не заботитесь о хранении, тогда вы можете создать другую таблицу с частичными записями заголовка, начиная с каждой подстроки (до 255 записей на обычное название).
таким образом, вы можете индексировать эти подстроки и соответствовать только началу строки, что должно значительно повысить производительность.
13
2017-07-11 14:54
Похоже, вы исключили все хорошие альтернативы.
Вы уже знаете, что ваш запрос
SELECT * FROM t WHERE TITLE LIKE '%abc%'не будет использовать индекс, он будет выполнять полное сканирование таблицы каждый раз.
Если вы были уверены, что строка была на начало поля, вы могли бы сделать
SELECT * FROM t WHERE TITLE LIKE 'abc%'который будет использовать индекс в заголовке.
Вы уверены, что полнотекстовый поиск не поможет вам здесь?
В зависимости от ваших бизнес-требований я иногда использовал следующую логику:
- Сделайте запрос «начинается с» ( LIKE 'abc%'), который будет использовать индекс.
- В зависимости от того, возвращены ли какие-либо строки (или сколько), условно перейдите к «сложному» поиску, который будет выполнять полное сканирование ( LIKE '%abc%')
Разумеется, зависит от того, что вам нужно, но я использовал это в ситуациях, когда сначала я могу показать самые простые и наиболее распространенные результаты и только при необходимости переходить к более сложному запросу.
4
2017-07-11 14:52
Вы можете добавить еще один вычисленный столбец в таблице: titleLength как len (title) PERSISTED. Это сохранит длину столбца «title». Создайте для этого индекс.
Кроме того, добавьте еще один вычисленный столбец с именем: ReverseTitle как Reverse (title) PERSISTED.
Теперь, когда кто-то ищет ключевое слово, проверьте, является ли длина ключевого слова такой же, как titlelength. Если да, выполните поиск «=». Если длина ключевого слова меньше длины titleLength, тогда сделайте LIKE. Но сначала сделайте заголовок LIKE 'abc%', затем сделайте reverseTitle LIKE 'cba%'. Подобно подходу Брэда - т. Е. Вы выполняете следующий сложный запрос только в случае необходимости.
Кроме того, если правила 80-20 применяются к вашим ключевым словам / подстрокам (т. Е. Если большая часть поисковых запросов находится на меньшем количестве ключевых слов), вы также можете рассмотреть возможность своего рода кеширования. Например, скажем, вы обнаружите, что многие пользователи ищут ключевое слово «abc», и поиск этого ключевого слова возвращает записи с идентификаторами 20, 22, 24, 25 - вы можете сохранить это в отдельной таблице и индексировать. И теперь, когда кто-то ищет новое ключевое слово, сначала загляните в эту таблицу «cache», чтобы узнать, был ли поиск уже выполнен более ранним пользователем. Если это так, не нужно снова смотреть в основной таблице. Просто верните результаты из таблицы «кеш».
Вы также можете объединить вышеуказанное с SQL Server TextSearch. (при условии, что у вас есть веская причина не использовать его). Но вы, тем не менее, можете использовать текстовый поиск, чтобы вкратце установить результат. а затем запустить SQL-запрос к вашей таблице, чтобы получить точные результаты, используя идентификаторы, возвращаемые TExt Search, в качестве параметра вместе с вашим ключевым словом.
Все это, очевидно, предполагает, что вам нужно использовать SQL. Если нет, вы можете исследовать что-то вроде Apache Solr.
3
2017-11-19 06:45
Создайте индексный указатель, есть новая функция в sql create index в столбце, который вам нужно искать и использовать это представление после поиска, что даст вам более быстрый результат.
0
2017-11-22 11:53
- использование ASCII кодировка с кластеризованное индексирование столбца char. Кодировка влияет на эффективность поиска из-за данных размер как на диске, так и на диске. Узким местом часто является ввод-вывод.
- Ваша колонка имеет длину 255 символов, поэтому вы можете использовать обычный индекс на поле вашего символа, а не полный текст, что происходит быстрее. Не выберите ненужные столбцы в свой оператор select.
- Наконец, добавьте больше ОЗУ на сервер и Увеличьте размер кэша ,
0
2017-11-23 11:55
Сделайте одно, используйте первичный ключ на конкретном столбце и индексируйте его в виде кластера.
Затем выполните поиск по любому методу (wild card or = или any), он будет искать оптимально, потому что таблица уже находится в кластерной форме, поэтому он знает, где он может найти (поскольку столбец уже в отсортированной форме)
0
2017-11-26 12:09
programmerz.ru
