Это часть 2 в серии из 3 частей. В первой части мы кратко рассмотрели некоторые начальные настройки полнотекстового поиска InnoDB и обнаружили немного странное поведение; здесь мы собираемся выполнить несколько запросов и сравнить наборы результатов. Мы надеемся, что произойдет одно из двух; Либо результаты, возвращаемые запросом MyISAM FTS, будут точно идентичны тому же запросу при выполнении с данными InnoDB, ИЛИ что результаты, возвращаемые InnoDB FTS, будут как-то «лучше» (насколько это реально возможно сделать за один раз. сообщение в блоге), чем то, что дает нам MyISAM.
Напомним, что у нас есть два разных набора данных, один из которых представляет собой текст из примерно 8000 заполненных SEO тел веб-страниц (мы называем это одним SEO), а другой, который мы называем DIR, — это примерно 800 000 записей каталога с именем, адресом, и тому подобное. Мы используем MySQL 5.5.30 и MySQL 5.6.10 без какой-либо настройки конфигурации, кроме как для установки innodb_ft_min_token_size равным 4 (вместо значения по умолчанию 3), чтобы он соответствовал стандартному значению MyISAM ft_min_word_length.
Во-первых, MyISAM с MySQL 5.5 для набора данных SEO:
mysql: SELECT id, title, MATCH(title, body) AGAINST ('arizona business records'
IN NATURAL LANGUAGE MODE) AS score FROM seo_test_myisam ORDER BY 3 DESC LIMIT 5;
+------+-----------------------------------------------------------------------+--------------------+
| id | title | score |
+------+-----------------------------------------------------------------------+--------------------+
| 458 | Free Business Forms for Starting or Maintaining a Successful Business | 1.3383517265319824 |
| 7112 | Download Idaho Tax Forms for Individual or Business Needs | 0.9273209571838379 |
| 7113 | Illinois Tax Forms for Individuals and Business | 0.8827990889549255 |
| 7121 | Massachusetts Tax Forms | 0.8678107261657715 |
| 454 | Business Accounting and Invoice Forms | 0.8668115139007568 |
+------+-----------------------------------------------------------------------+--------------------+
Тот же запрос, запустите InnoDB на 5.6.10:
mysql: SELECT id, title, MATCH(title, body) AGAINST ('arizona business records'
IN NATURAL LANGUAGE MODE) AS score FROM seo_test_innodb ORDER BY 3 DESC LIMIT 5;
+----+------------------------------------------------------------------------+-------------------+
| id | title | score |
+----+------------------------------------------------------------------------+-------------------+
| 48 | California Free Public Records, Criminal Records And Background Checks | 21.23662567138672 |
| 66 | Michigan Free Public Records, Criminal Records And Background checks | 5.363710880279541 |
| 44 | Alabama Free Public Records, Criminal Records And Background Checks | 5.310127258300781 |
| 57 | Illinois Free Public Records, Criminal Records And Background Checks | 4.569097518920898 |
| 70 | Montana Free Public Records, Criminal Records And Background Checks | 4.516233444213867 |
+----+------------------------------------------------------------------------+-------------------+
Ух ты. Я не уверен, стоит ли мне беспокоиться о том, что * оценки * отличаются, но * совпадения * ПОЛНОСТЬЮ РАЗНЫ между 5.5 / MyISAM и 5.6 / InnoDB. Теперь мы знаем, что в MyISAM FTS есть предостережение с поиском на естественном языке, согласно которому слово, которое присутствует в 50% или более строк, рассматривается как стоп-слово, так что, это объясняет нашу проблему? Это может произойти, потому что слово «Аризона» появляется в 6900 из 7150 строк, а слово «записи» появляется в 7082 из них. Итак, давайте попробуем что-то еще, что менее вероятно, чтобы иметь эту проблему. Слово «корпорация» встречается в 143 документах; слово «формы» появляется в 439 из них, а слово «комиссия» появляется в 130. Здесь могут быть некоторые совпадения, но даже если нет, 143 + 130 + 439 <0,5 * 7150, поэтому ни одно из них следует рассматривать как стоп-слова в MyISAM.
С 5.5:
mysql: SELECT id, title, MATCH(title, body) AGAINST ('corporation commission forms'
IN NATURAL LANGUAGE MODE) AS score FROM seo_test_myisam ORDER BY 3 DESC LIMIT 5;
+------+--------------------+--------------------+
| id | title | score |
+------+--------------------+--------------------+
| 7132 | New York Tax Forms | 7.821961879730225 |
| 7127 | Nebraska Tax Forms | 6.882259845733643 |
| 7123 | Free Forms | 6.748578071594238 |
| 7126 | Montana Tax Forms | 6.4749345779418945 |
| 7119 | Maine Tax Forms | 6.400341510772705 |
+------+--------------------+--------------------+
С 5.6:
mysql: SELECT id, title, MATCH(title, body) AGAINST ('corporation commission forms'
IN NATURAL LANGUAGE MODE) AS score FROM seo_test_innodb ORDER BY 3 DESC LIMIT 5;
+-----+--------------------------------------------------------------------------+--------------------+
| id | title | score |
+-----+--------------------------------------------------------------------------+--------------------+
| 79 | Ohio Free Public Records, Criminal Records And Background Checks | 51.76125717163086 |
| 78 | Free North Dakota Public Records, Criminal Records And Background Checks | 30.32522201538086 |
| 19 | Free Public Printable Forms For All Industries | 27.557558059692383 |
| 408 | Free Colorado DMV Resources and Driving Records | 24.933029174804688 |
| 48 | California Free Public Records, Criminal Records And Background Checks | 24.224336624145508 |
+-----+--------------------------------------------------------------------------+--------------------+
Хорошо, теперь я начинаю немного волноваться. Документы говорят нам, что список стоп-слов по умолчанию существенно отличается между InnoDB и MyISAM, и, как оказалось, в списке InnoDB по умолчанию есть только 36 стоп-слов, но в списке MyISAM по умолчанию 543 стоп-слова. Что произойдет, если мы возьмем стоп-слова MyISAM, вставим их в таблицу и настроим эту таблицу в качестве нашего списка стоп-слов для InnoDB?
Это таблица, которую мы пытаемся эмулировать:
mysql: SHOW CREATE TABLE information_schema.innodb_ft_default_stopword\G
*************************** 1. row ***************************
Table: INNODB_FT_DEFAULT_STOPWORD
Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` (
`value` varchar(18) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
Документы говорят нам, что нам нужно создать таблицу * InnoDB * с одним столбцом VARCHAR с именем «значение». ОК, звучит достаточно просто:
mysql: SHOW CREATE TABLE innodb_myisam_stopword\G
*************************** 1. row ***************************
Table: innodb_myisam_stopword
Create Table: CREATE TABLE `innodb_myisam_stopword` (
`value` varchar(18) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Но когда мы пытаемся использовать эту таблицу, вот что возвращается:
mysql: SET GLOBAL innodb_ft_server_stopword_table='test/innodb_myisam_stopword'; ERROR 1231 (42000): Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'test/innodb_myisam_stopword'
И вот что появилось в журнале ошибок сервера:
InnoDB: invalid column type for stopword table test/innodb_myisam_stopword. Its first column must be of varchar type
Э-э … Означает ли это, что мой следующий пост в блоге должен быть озаглавлен «Когда VARCHAR на самом деле не VARCHAR?» Думая, что, возможно, это был случай GEN_CLUST_INDEX, вызывающий у меня проблемы, я попытался добавить второй столбец в таблицу, который представлял собой целое число PK, и в другой попытке я попытался просто сделать столбец «значение» PK, но ни один из них не сработал , Кроме того, попытка установить innodb_ft_user_stopword_table вызвала ту же ошибку. Я представил отчет об ошибке (68450) , и, как вы можете видеть из обсуждения ошибки, оказалось, что эта таблица чувствительна к набору символов. Если вы собираетесь использовать собственную таблицу стоп-слов для InnoDB FTS, по крайней мере на данный момент, эта таблица должна использовать набор символов latin1 .
mysql: CREATE TABLE innodb_ft_list2 ( value VARCHAR(18) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql: SET GLOBAL innodb_ft_server_stopword_table='test/innodb_ft_list2'; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows affected (0.01 sec) Records: 543 Duplicates: 0 Warnings: 0
Насколько я могу судить, эта маленькая ошибка не упоминается нигде в документации MySQL 5.6; в каждом месте, где говорится о создании одной из этих таблиц стоп-слов, просто упоминается механизм таблиц и имя / тип столбца, поэтому я не уверен, является ли это преднамеренным ограничением, которое просто необходимо лучше документировать или оно является ограничением с функцией InnoDB FTS, которая будет удалена в более поздней версии.
Теперь, когда мы разобрались с этим, давайте отбросим и перестроим наш индекс FT для таблицы InnoDB и попробуем выполнить вышеупомянутые запросы еще раз. Мы уже знаем, какими будут результаты MyISAM; наши результаты InnoDB меняются? Нет, они точно такие же, хотя баллы немного изменились.
А как насчет запроса в булевом режиме? Документы говорят нам, что если мы используем булевский режим и ставим «+» перед нашим поисковым термином, то этот термин * должен * появиться в результатах поиска. Но так ли это?
С 5.5:
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson +arizona'
IN BOOLEAN MODE) AS score FROM dir_test_myisam ORDER BY 3 DESC LIMIT 5;
+-------+--------------------------+-------+
| id | full_name | score |
+-------+--------------------------+-------+
| 74717 | James R Peterson | 1 |
| 1 | Harold Wesley Abbott Iii | 0 |
| 3 | William Doyle Abbott Jr | 0 |
| 5 | Robert Jack Abraham | 0 |
| 7 | Mark Allen Abrell | 0 |
+-------+--------------------------+-------+
А с 5.6:
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson +arizona'
IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 3 DESC LIMIT 5;
+---------+------------------+-------------------+
| id | full_name | score |
+---------+------------------+-------------------+
| 74717 | James R Peterson | 23.63458251953125 |
| 1310720 | Terry Lynn Suter | 0 |
| 1441792 | Jorge E Morrison | 0 |
| 1310976 | Oscar Blakemore | 0 |
| 1442048 | Donald Simmons | 0 |
+---------+------------------+-------------------+
В таблице есть только одна строка, которая фактически соответствует всем трем поисковым терминам, и в этом случае MyISAM и InnoDB FTS работали одинаково и нашли ее. Меня не очень беспокоит тот факт, что следующие четыре строки совершенно разные; баллы равны нулю, что означает «нет совпадений». Это выглядит многообещающе, поэтому давайте рассмотрим дальше. Опять же, из документов, если мы запустим запрос в булевом режиме, в котором некоторые условия поиска будут иметь префикс «+», а другие не будут иметь префикса, результаты с таким термином будут иметь более высокий рейтинг, чем результаты без него. Так, например, если мы изменим вышеуказанный запрос на «+ james + peterson arizona», то мы можем ожидать возвращения нескольких совпадений, содержащих слова «James» и «Peterson», и мы должны ожидать, что запись из Аризоны будет к вершине списка.
С 5.5 это именно то, что происходит:
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson arizona'
IN BOOLEAN MODE) AS score FROM dir_test_myisam ORDER BY 3 DESC LIMIT 5;
+--------+------------------------------+--------------------+
| id | full_name | score |
+--------+------------------------------+--------------------+
| 74717 | James R Peterson | 1.3333333730697632 |
| 14159 | Christopher Michael James | 1 |
| 44427 | James Cyrus Peterson | 1 |
| 53501 | James/Rober T Giles/Peterson | 1 |
| 126373 | Bamish James Peterson | 1 |
+--------+------------------------------+--------------------+
С 5.6 нам не так повезло.
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('+james +peterson arizona'
IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 3 DESC LIMIT 5;
+--------+--------------------------+--------------------+
| id | full_name | score |
+--------+--------------------------+--------------------+
| 741223 | Alphonso Lee Peterson Sr | 59.972904205322266 |
| 925811 | James B Peterson | 59.37348937988281 |
| 269589 | Michael James Peterson | 44.82982635498047 |
| 471273 | James Allen Peterson | 39.58232879638672 |
| 925781 | Anthony James Petersen | 38.03296661376953 |
+--------+--------------------------+--------------------+
Эти результаты даже близко не идентичны. Как выясняется, полная запись «Alphonso Lee Peterson Sr» также содержит имя «James», и слово «Peterson» упоминается там несколько раз, но «Arizona» вообще отсутствует, тогда как запись Ведь у «Джеймса Р. Петерсона» были все три условия поиска, и ни одного из них не было значительного повторения. Используя этот конкретный запрос, «Джеймс Р Петерсон» занимает № 15 в списке.
На данный момент, совершенно очевидно, что способ, которым MyISAM рассчитывает баллы, сильно отличается от того, как это делает InnoDB, и учитывая то, что я говорил ранее о повторении слов в записи «Альфонсо Ли Петерсон-старший» по сравнению с « Джеймс Р. Петерсон », мы можем утверждать, что InnoDB на самом деле ведет себя более правильно, чем MyISAM. Представьте себе, если бы мы просматривали газетные статьи или что-то в этом роде и искали запросы, содержащие слово «MySQL», то есть вероятность того, что статья с 10 экземплярами «MySQL» может быть более желательной для нас, чем статья, которая упоминается только один раз. Поэтому, если я посмотрю на эти результаты с этой точки зрения, то пойму, как и почему.Меня беспокоит то, что, вероятно, найдутся люди, которые считают, что переход на InnoDB FTS — это просто вопрос обновления до 5.6 и запуска ALTER TABLE foo ENGINE = InnoDB. В теории да. На практике даже не близко.
Я попробовал еще один логический поиск, на этот раз искал чье-то полное имя, которое, как я знал, присутствовало только один раз в базе данных, и использовал двойные кавычки, чтобы сгруппировать поисковые термины как одну фразу:
С 5.5:
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('"Thomas B Smith"'
IN BOOLEAN MODE) AS score FROM dir_test_myisam ORDER BY 3 DESC LIMIT 1;
+-------+----------------+-------+
| id | full_name | score |
+-------+----------------+-------+
| 62633 | Thomas B Smith | 1 |
+-------+----------------+-------+
Хорошо выглядит, вот и он. Теперь, что происходит под 5.6?
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('"Thomas B Smith"'
IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 3 DESC LIMIT 1;
+---------+------------------+-------+
| id | full_name | score |
+---------+------------------+-------+
| 1310720 | Terry Lynn Suter | 0 |
+---------+------------------+-------+
В бессмертных словах Гомера Дж. Симпсона, «D’OH !!» Почему MyISAM может найти эту запись, а InnoDB вообще не может ее найти? Я подозреваю, что «B» вызывает проблемы для InnoDB, потому что это только один символ, и мы установили innodb_ft_min_token_size в 4. Таким образом, когда InnoDB анализирует данные и формирует список слов, он полностью игнорирует среднюю инициализацию мистера Смита. , Чтобы проверить эту гипотезу, я сбросил innodb_ft_min_token_size в 1, удалил / перестроил индекс InnoDB и попытался снова.
mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST ('"Thomas B Smith"'
IN BOOLEAN MODE) AS score FROM dir_test_innodb ORDER BY 3 DESC LIMIT 1;
+-------+----------------+--------------------+
| id | full_name | score |
+-------+----------------+--------------------+
| 62633 | Thomas B Smith | 31.749061584472656 |
+-------+----------------+--------------------+
Ага, вот и он! Основываясь на этом результате, я бы предостерег любого, кто разрабатывает приложение, которое будет использовать InnoDB FTS, чтобы он внимательно следил за типами запросов, которые ожидают от пользователей. В частности, если вы ожидаете или собираетесь разрешить пользователям вводить поисковые фразы, которые включают инициалы, числа или любую другую строку длиной менее 3 (по умолчанию), я думаю, что вы будете вынуждены установить innodb_ft_min_token_size равным 1 В противном случае вы столкнетесь с той же проблемой, что и наш мистер Смит. [Это поднимает вопрос о том, почему он работает с MyISAM, когда ft_min_word_length по умолчанию равен 4, но это тема для другого дня.]
Обратите внимание, что могут быть или не быть какие-то последствия для производительности при запуске этого значения до конца; это то, что я еще не проверял, но буду рассказывать в третьей части этой серии. Однако я могу подтвердить, что размер моего набора данных DIR на диске в точности совпадает с настройкой 1 по сравнению с настройкой 4. Это может или не может иметь место в случае с многобайтовыми наборами символов или с идеографическими языками, такими как как японский, хотя японский создает свои собственные уникальные проблемы для FTS любого рода из-за отсутствия традиционных границ слов.
В любом случае, похоже, что мы решили проблему поиска в булевом режиме, но результаты поиска в режиме естественного языка все еще сильно отличаются. Для тех из вас, кто ожидает и нуждается в результатах поиска в стиле MyISAM, есть по крайней мере один потенциальный выходной люк из этой кроличьей норы. При определении КЛЮЧА FULLTEXT вы можете использовать модификатор «WITH PARSER», чтобы указать имя UDF, которое ссылается на ваш собственный написанный пользователем плагин полнотекстового парсера. Таким образом , я имею в виду , что это может быть возможным принять MyISAM полнотекстового код парсера, преобразовать его в плагин, и использовать его для InnoDB FT индексов , где вы ожидали результаты MyISAM стиля. Проверка или опровержение этой гипотезы оставлено читателю в качестве упражнения.
Краткий обзор того, что мы узнали до сих пор:
- Есть части конфигурации InnoDB FTS, которые чувствительны к буквам и буквам. Осторожно!
- Когда вы добавите свой первый КЛЮЧ FULLTEXT в таблицу InnoDB, будьте готовы к перестройке таблицы.
- Расчет результатов матча совершенно различен для двух двигателей; иногда это приводит к совершенно другим результатам.
- Если вы надеялись использовать InnoDB FTS в качестве простой замены вашего текущего MyISAM FTS, результаты могут вас удивить.
Этот последний момент имеет особое значение, поскольку он также иллюстрирует важную передовую практику, даже если FTS не участвует. Всегда проверяйте, как ваше приложение ведет себя в результате масштабного обновления версии MySQL, прежде чем запускать его в производство! В Percona есть инструменты ( pt-upgrade и Percona Playback ), которые могут помочь вам в этом. Эти инструменты являются бесплатными и с открытым исходным кодом, пожалуйста, используйте их. Вы и ваши пользователи будете счастливы, что сделали это.
В третьем и последнем выпуске этой серии мы рассмотрим производительность. Как скорость InnoDB FTS сравнивается с аналогом MyISAM, и какие уловки мы можем использовать, чтобы она работала быстрее? Оставайтесь с нами, чтобы узнать!