1 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Полнотекстовый поиск в MySQL 5.1 Новые возможности Александр Рубин Senior Consultant, MySQL AB

2 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Полнотекстовый поиск Популярный способ поиска информации (Яндекс, Google) Легко и удобно пользоваться: ввел слова и нажал кнопку

3 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database В этой презентации Новые возможности полнотекстового поиска в MySQL 5.1 Как увеличить производительность полнотекстового поиска в MySQL? Как искать с автоматическим исправлением ошибок? Результаты тестирования

4 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Типы полнотекстового поиска -В MySQL 5.1: по умолчанию сортировка по релевантности

5 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Булев поиск - MySQL: в булевом поиске нет сортировки по умолчанию

6 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Поиск фразы

7 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Разные решения для полнотекстового поиска ТипРешение Встроенный в MySQLFull Text Index (MyISAM only) Может интегрироваться с MySQL Sphinx ВнешнийLucene MnogoSearch КоробкиGoogle box Fast box

8 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Полнотекстовый индекс в MySQL Работает только с MyISAM Поддерживает Natural Language Search и boolean search ft_min_word_len = 4 (по умолчанию) Список стоп слов Релевантность рассчитывается на основе частности слов –Расстояние между словами не учитывается

9 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database MySQL Full Text: Создаем таблицу mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> ) engine=MyISAM;

10 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Natural Language mode mysql> SELECT * FROM articles -> WHERE MATCH (title,body) -> AGAINST ('database' IN NATURAL LANGUAGE MODE); | title | body | | MySQL vs. YourSQL | In the following database comparison... | | MySQL Tutorial | DBMS stands for DataBase... | В «Natural Language Mode» сортировка по релевантности!

11 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database MySQL Full Text: Булев поиск mysql> SELECT * FROM articles -> WHERE MATCH (title,body) -> AGAINST (cat AND dog' IN BOOLEAN MODE); В булевом поиске MySQL не сортирует выдачу

12 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Новые возможности полнотекстового поиска в MySQL 5.1

13 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Новые возможности полнотекстового поиска Булев поиск быстрее в MySQL 5.1 –Реализован новый алгоритм smart index merge (forge.mysql.com/worklog/task.php?id=2535) Возможность установить свой Plug-in –Можно заменить парсер по умолчанию Более полная поддержка Unicode –Работает более аккуратно с пробельными символами и символами пунктуации Unicode (forge.mysql.com/worklog/task.php?id=1386)

14 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database MySQL 5.0 vs 5.1 MySQL 5.1: полнотекстовый поиск в режиме «булев поиск» на % быстрее чем в MySQL 5.0 –Поиск по релевантности и поиск фраз не изменился в MySQL 5.1. Данные для тестирования –CDDB (музыкальная база данных) –Автор и название, 2 мил. CDs., varchar(255). –CDDB ~= amazon.coms CD/books inventory

15 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Полнотекстовый поиск в MySQL 5.1

16 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database MySQL 5.1: загружаемые плагины Можно заменить обычный парсер: –Внедрить специальные правила обработки, например, морфологию, разделение слов и пр. –Пре-парсинг – индексирование PDF / HTML Тоже самое можно делать и для запросов –Можно привести слова запросов к нормальной форме (для морфологии)

17 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Примеры плагинов Готовые плагины: ищите по слову FullText на forge.mysql.com/projects Стемминг –MnogoSearch ( –Porter stemming fulltext plugin Парсеры N-Gram (Японский) –Есть простой парсер для N-Gram

18 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример: MnogoSearch Stemming MnogoSearch включает в плагин стемминга ( udmstemmer.html) Инсталяция: –Configure –mysql> INSTALL PLUGIN stemming SONAME 'libmnogosearch.so'; –CREATE TABLE my_table ( my_column TEXT, FULLTEXT(my_column) WITH PARSER stemming ); –SELECT * FROM t WHERE MATCH a AGAINST('test' IN BOOLEAN MODE);

19 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример: MnogoSearch Stemming Конфигурация: stemming.conf –MinWordLength 2 –Spell en latin1 american.xlg –Affix en latin1 english.aff Словари Ispell (not Aspell) можно взять на dictionaries.html#English-dicts Если stemming.conf изменен нужно перегрузить MySQL

20 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример: MnogoSearch Stemming Стеммер ухудшает производительность! mysql> insert into searchindex_stemmer select * from enwiki.searchindex limit 10000; Query OK, rows affected (44.03 sec) mysql> insert into searchindex select * from enwiki.searchindex limit 10000; Query OK, rows affected (21.80 sec)

21 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример запросов со стеммером mysql> SELECT count(*) FROM searchindex WHERE MATCH si_text AGAINST('color' IN BOOLEAN MODE); count(*): 861 mysql> SELECT count(*) FROM searchindex_stemmer WHERE MATCH si_text AGAINST('color' IN BOOLEAN MODE); count(*): 1017

22 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Другие фичи Ключевое слово FullText на forge.mysql.com CTYPE table for unicode character sets (WL#1386), complete Enable fulltext search for non-MyISAM engines (WL#2559), Assigned Stemming for fulltext (WL#2423), Assigned Combined BTREE/FULLTEXT indexes (WL#828) Другие фичи

23 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как сделать...? Советы и стандартные решения

24 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как настроить полнотекстовый поиск Индексы должны влезать в память! –Увеличить размер оперативной памяти –Set key_buffer =. Макс 4GB! –Сразу загружать индексы в память

25 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как настроить полнотекстовый поиск mysql> set global ft_key.key_buffer_size= 4*1024*1024*1024; mysql> CACHE INDEX S1, S2, IN ft_key; mysql> LOAD INDEX INTO CACHE S1, S2 …;

26 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как настроить полнотекстовый поиск Partitioning (разбивка таблицы на кусочки) –Partitioning уменьшает размер индекса Поиск и обновление будут идти быстрее

27 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как настроить полнотекстовый поиск Настроить репликацию и поставить несколько slaves –Уменьшит кол-во запросов, идущих на каждый сервер –Уменьшит нагрузку на процессов (сортировка – процесс требующий ресурсов) –На каждом slave сервере может быть свой набор данных

28 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример использования репликации Master Slave 1 Slave 3 Full Text Requests Write Requests Slave 2 Load Balancer

29 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database MySQL 5.1 как слайв Master (MySQL 5.0) Обычный Slave (MySQL 5.0) Полнотекст. Slave (MySQL 5.1) Запросы с полнотекстовы м поиском Обычные запросы

30 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database «Запросы убийцы сервера» –Запросы с Order by/Group by Поиск по релевантности: сортировка по релевантности Булев поиск: без сортироки! Запрос с «Order by date» намного медленее чем без order by –SQL_CALC_FOUND_ROWS Select SQL_CALC_FOUND_ROWS from T1 …limit 10 Несмотря на Limit 10 будет просканнирована все таблица

31 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример из реальной жизни Почему этот запрос выполняется так медленно? SELECT … FROM `ft` WHERE MATCH `album` AGAINST (the way i am)

32 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Пример из реальной жизни Список стоп слов и ft_min_word_len! The – стоп слово Way - стоп слово I - не стоп слово Am - стоп слово Из запроса the way i am будет выброшенно все кром I (если ft_min_word_len =1 в настроках mysql) My.cnf: ft_min_word_len =1

33 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Поиск с коррекцией ошибок Пример: поиск музики –Поиск по названию и исполнителю –Поиск с коррекцией ошибок Bob Dilane (ошибка) -> –Bob Dylan (исправленно) Как сделать: –Использовать функцию soundex() –Soundex = sounds similar mysql> select soundex('Dilane'); D450 mysql> select soundex('Dylan'); D450

34 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Поиск с коррекцией ошибок Как сделать: 1.Alter table artists add art_name_sndex varchar(80) 2.Update artists set art_name_sndex = soundex(art_name) 3.Select art_name from artists where art_name_sndex = soundex(Bob Dilane') limit 10

35 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Поиск с коррекцией ошибок: сортировка Сортировка по популярности –Select art_name from artists where art_name_sndex = soundex(Dilane') order by popularity limit 10 Сортировка по релевантности – Расстояние Левенштейна –Расстояние Левенштейна - это мера разницы двух последовательностей символов относительно минимального количества операций вставки, удаления и замены, необходимых для перевода одной строки в другую. –Для вычисления расстояния Левенштейна можно использовать UDF или stored procedure

36 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Сфинкс Возможности –Open Source, –Быстро ищет по большим массивам данных –Разработан для индексирования содержимого баз данных –Поддерживает распределнные вычисления –Интегрируется с MySQL (плагин) –Быстрое индексирование (до 10M/sec) Недостатки –Нужно интегрировать –Нет автоматически обновляемых индексов

37 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как интегрировать Сфинкс и MySQL Плагин сфинкса – не система хранения –Нужно использовать searchd Нужно скомпилировать MySQL со Сфинксом –MySQL 5.0: нужно патчить сорцы –MySQL 5.1: патчить не нужно, можно просто установить плагин

38 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Как интегрировать Сфинкс в MySQL CREATE TABLE t1 (id INTEGER NOT NULL, weight INTEGER NOT NULL, query VARCHAR(3072) NOT NULL, group_id INTEGER, INDEX(query) ) ENGINE=SPHINX CONNECTION="sphinx://localhost:3312/e nwiki"; SELECT * FROM enwiki.searchindex docs JOIN test.t1 ON (docs.si_page=t1.id) WHERE query="one document;mode=any" limit 1;

39 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database Вопросы?

40 Copyright 2006 MySQL AB The Worlds Most Popular Open Source Database DRBD и полнотекстовый поиск Active DRBD Server InnoDB Tables Passive DRBD Server InnoDB Tables Normal Replication Slave InnoDB Tables Synchronous Block Replication FullText Slaves MyISAM Tables with FT Indexes Full Text Requests