MySQL Casual Talks Vol.4 「MySQL-5.6で始める全文検索 〜InnoDB FTS編〜」

MySQL Casual Talks Vol.4 でのライトニングトークに利用した資料です。
MySQL-5.6.4より「InnoDB FTS」としてInnoDBに全文検索機能が加わりました。
この全文検索機能を利用し、日本語の全文検索エンジンとしての可能性を探ります。

Transcript

1. .Z42-Ͱ࢝ΊΔશจݕࡧ ʙ*OOP%#
   '54ฤʙ MySQL Casual Talks #4 Kentaro Yoshida @yoshi_ken
2. ຊ೔ͷྲྀΕ ࣗݾ঺հ ࠓճͷςʔϚ .Z42-࣌୅ͷશจݕࡧϓϩμΫτ঺հ શจݕࡧͷಈ࡞σϞ ϕϯνϚʔΫ ·ͱΊ ࣍ճ༧ࠂ
3. ࣗݾ঺հ Α͚͠Μ͞Μ ͷதͷਓ ݚڀ։ൃܥͷ ΠϯϑϥΤϯδχΞ ڵຯ෼໺ MySQL, Fluentd, Ruby, KVM, DRBD, Nginx, Redis, MongoDB, etc...
4. ࠓճͷςʔϚ MySQL-5.6.4ͷInnoDBͰ͸શจݕࡧ͕Մೳͱͳͬͨ ͔͠͠MeCabTokenizerͲ͜Ζ͔Ngramʹ͢ΒඇରԠ εϖʔε۠੾ΓͳΒݕࡧͰ͖ΔʂʢϙδςΟϒࢥߟʣ ࣗલͰ෼͔ͪॻ͖͢Ε͹ಈ͘ͷͰ͸ʁͰ΋଎౓͸ʁ ͜Μͳঢ়گʹՌ׶ʹ௅ઓͨ͠ϨϙʔτͰ͢
5. MySQL-5.6࣌୅ͷ શจݕࡧϓϩμΫτ঺հ ʢmroonga / InnoDB-FTS / SphinxSEʣ
6. mroonga http://mroonga.github.io/ja/docs/characteristic.html
7. InnoDB Full-Text Search https://blogs.oracle.com/mysqlinnodb/entry/ innodb_full_text_search_is
8. SphinxSE http://sphinxsearch.com/ ͜Ε͸ผ෺
9. mroonga (groonga) Tritonn (Senna) ͷޙܧϓϩμΫτ ࢀরϩοΫϑϦʔ ׬શసஔࡧҾΛ࠾༻ MeCab΍N-gramͰͷ෼͔ͪॻ͖ʹରԠ N-gram͓ΑͼCJKʹରԠ
10. InnoDB FTS (FullTextSearch) MySQL-5.6.4͔Βਖ਼ࣜରԠʢ͔͠΋ඪ४ʣ ࢀরϩοΫϑϦʔ mroonga (groonga) ಉ༷ʹ׬શసஔࡧҾΛ࠾༻ ۭന۠੾Γͷ୯ޠͷΈݕࡧՄೳ ݱঢ়ɺN-gram͓ΑͼCJKʹ͸ඇରԠ
11. Sphinx SE (Search Engine) ֓ཁ by wikipedia http://en.wikipedia.org/wiki/ Sphinx_(search_engine) ೔ຊޠղઆ by IBM http://www.ibm.com/developerworks/jp/ opensource/library/os-sphinx/? cmp=dw&cpb=dwope&ct=dwrss&cr=dwrss& ccy=jp&csr=120911 storage pluginରԠ http://sphinxsearch.com/docs/ current.html#sphinxse-using ϦΞϧλΠϜΠϯσοΫ εඇରԠʢٙ࿭ʣ http://www.slideshare.net/conmame/ ss-12117195/9 ϕϯνϚʔΫ http://www.percona.com/files//presentations/ opensql2008_sphinx.pdf http://www.slideshare.net/billkarwin/practical-full- text-search-with-my-sql Πϯετʔϧํ๏ http://www.howtoforge.com/sphinx-as- mysql-storage-engine-sphinxse ೔ຊޠ (CJK) ରԠঢ়گ http://www.ivinco.com/blog/using-sphinx- search-engine-with-chinese-japanese-and- korean-language-documents/ MySQL-5.6.10ରԠঢ়گ http://sphinxsearch.com/bugs/view.php? id=1419
12. શจݕࡧσϞ (mroonga) ςʔϒϧ࡞੒ CREATE TABLE search_with_mroonga ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT INDEX (subject,content) ) ENGINE = mroonga DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
13. શจݕࡧσϞ (mroonga) σʔλొ࿥ INSERT INTO search_with_mroonga (subject, content) VALUES ( ‘MySQL’, ’MySQLʢϚΠΤεΩϡʔΤϧʣ͸ɺΦϥΫϧ͕։ ൃ͢ΔRDBMSʢϦϨʔγϣφϧσʔλϕʔεΛ؅ཧɺӡ ༻͢ΔͨΊͷγεςϜʣͷ࣮૷ͷҰͭͰ͋Δɻ’ );
14. શจݕࡧσϞ (mroonga) σʔλݕࡧ SELECT * FROM search_with_mroonga WHERE MATCH(subject,content) AGAINST('+ΦϥΫϧ' IN BOOLEAN MODE);
15. શจݕࡧσϞ (InnoDB FTS) ςʔϒϧ࡞੒ CREATE TABLE search_with_innodb ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT INDEX (subject,content) ) ENGINE=InnoDB DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
16. શจݕࡧσϞ (InnoDB FTS) ෼͔ͪॻ͖ with CLI echo "MySQLʢϚΠΤεΩϡʔΤϧʣ͸ɺΦϥΫϧ͕ ։ൃ͢ΔRDBMSʢϦϨʔγϣφϧσʔλϕʔεΛ؅ཧɺ ӡ༻͢ΔͨΊͷγεςϜʣͷ࣮૷ͷҰͭͰ͋Δɻ" | mecab --output-format-type wakati
17. શจݕࡧσϞ (InnoDB FTS) ෼͔ͪॻ͖ with Ruby # -*- encoding: utf-8 -*- require 'MeCab' wakati = MeCab::Tagger.new('-O wakati') puts wakati.parse('ຊ೔΋ྑ͍ఱؾͰ͢')
18. શจݕࡧσϞ (InnoDB FTS) σʔλొ࿥ INSERT INTO search_with_innodb (subject, content) VALUES ( ‘MySQL’, ’MySQL ʢ ϚΠΤεΩϡʔΤϧ ʣ ͸ ɺ ΦϥΫ ϧ ͕ ։ൃ ͢Δ RDBMS ʢ ϦϨʔγϣφϧ σʔλ ϕʔε Λ ؅ཧ ɺ ӡ༻ ͢Δ ͨΊ ͷ γεςϜ ʣ ͷ ࣮૷ ͷ Ұͭ Ͱ ͋Δ ɻ’ );
19. શจݕࡧσϞ (InnoDB FTS) σʔλݕࡧ SELECT * FROM search_with_innodb WHERE MATCH(subject,content) AGAINST('+ΦϥΫϧ' IN BOOLEAN MODE);
20. ϕϯνϚʔΫ OracleެࣜMySQL-5.6.10ʹmroonga-3.02ΛೖΕɺ mroongaͱInnoDB-FTSͷੑೳൺֱࢼݧΛߦ͏ ॻ͖ࠐΈੑೳ શจݕࡧੑೳ ར༻Ϛγϯ Intel Xeon L5520 2.27GHz, 16GB Memory CentOS 6.4 (x86_64)
21. ϕϯνϚʔΫ ςʔϒϧߏ੒ mroonga ετϨʔδϞʔυ ΠϯσοΫε࡞੒࣌ͷΦϓγϣϯʹͯ ෼͔ͪॻ͖ํ๏Λࢦఆ͢Δ search_with_mroonga_ngram search_with_mroonga_mecab
22. ϕϯνϚʔΫ search_with_mroonga_ngram CREATE TABLE search_with_mroonga_ngram ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT (subject,content) ) ENGINE=mroonga DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
23. ϕϯνϚʔΫ search_with_mroonga_mecab CREATE TABLE search_with_mroonga_mecab ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT (subject,content) COMMENT 'parser "TokenMecab"' ) ENGINE=mroonga DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
24. ϕϯνϚʔΫ ςʔϒϧߏ੒ InnoDB FTS ෼͔ͪॻ͖ػೳΛ಺ଂ͍ͯ͠ͳ͍ͨΊɺ ࣄલʹRubyϓϩάϥϜʹͯ෼͔ͪॻ͖Λߦ͏ search_with_innodb_ngram search_with_innodb_mecab
25. ϕϯνϚʔΫ search_with_innodb_ngram CREATE TABLE search_with_innodb_ngram ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT (subject,content) ) ENGINE=InnoDB DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
26. ϕϯνϚʔΫ search_with_innodb_mecab CREATE TABLE search_with_innodb_mecab ( id INT PRIMARY KEY AUTO_INCREMENT, subject TEXT, content TEXT, FULLTEXT (subject,content) ) ENGINE=InnoDB DEFAULT CHARSET utf8 collate utf8_unicode_ci ;
27. ϕϯνɿߋ৽ੑೳ 5ສ݅×15ճͷINSERTॴཁඵ਺Λܭଌ͠ɺ ݅਺͕૿͑Δ͜ͱͰͷੑೳྼԽΛଌΓ·͢ αϯϓϧσʔλʹ͸ɺdbpedia.org ΑΓೖखͨ͠ Wikipedia೔ຊޠ൛ͷ75ສ݅ͷهࣄΛར༻͠·͢
28. ϕϯνɿߋ৽ੑೳ tsvσʔλͷ༰ྔ ngram͸bi-gramͷͨΊɺ2ഒҎ্ͷ༰ྔͱͳΓ·͢ɻ $ ls -sh wikipedia_*.tsv 408M wikipedia_mecab.tsv 798M wikipedia_ngram.tsv 345M wikipedia_plain.tsv
29. ϕϯνɿߋ৽ੑೳ ΫΤϦྫ LOAD DATA LOCAL INFILE '/path/to/wikipedia_mecab.tsv' INTO TABLE search_with_innodb_mecab FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id,subject,content);
30. ϕϯνɿߋ৽ੑೳ 0 1000 2000 3000 4000 75ສ݅ͷొ࿥ॴཁ࣌ؒʢඵʣ 3054.54 329.8 629.57 178.01 mroonga mecab InnoDB FTS mecab mroonga ngram InnoDB FTS ngram
31. tsvσʔλΛ5ສߦຖͷϑΝΠϧ15ݸʹ෼ׂ͠ɺ LOAD DATA LOCAL INFILEΛཱͯଓ͚ʹߦͬͨࡍɺ ଎౓͕ͲͷΑ͏ʹมԽ͍͔ͯ͘͠Λܭଌ͠·ͨ͠ɻ
32. ϕϯνɿߋ৽ੑೳ 0 100 200 300 400 50k 100k 150k 200k 250k 300k 350k 400k 450k 500k 550k 600k 650k 700k 750k mroonga mecab InnoDB FTS mecab mroonga ngram InnoDB FTS ngram 5ສ݅୯ҐͰͷॻ͖ࠐΈॴཁ࣌ؒʢඵʣ
33. 5ສ݅୯ҐͰͷॻ͖ࠐΈॴཁ࣌ؒΛܭଌͨ͠ͱ͜Ζɺ InnoDB FTS ngram͸μϯτπͰ஗͍ͨΊɺ άϥϑ͔Β֎͠ɺMeCabͷΈͰൺֱ͠·͢ɻ
34. ϕϯνɿߋ৽ੑೳ 0 15 30 45 60 50k 100k 150k 200k 250k 300k 350k 400k 450k 500k 550k 600k 650k 700k 750k mroonga mecab InnoDB FTS mecab 5ສ݅୯ҐͰͷॻ͖ࠐΈॴཁ࣌ؒʢඵʣ
35. ϕϯνɿߋ৽ੑೳ ݁Ռ mroonga͕μϯτπͰ࠷଎ InnoDB FTS΋MeCabσʔλΛೖΕΔͳΒ ۀ຿ཁ݅࣍ୈͰ͸࣮༻తͳൣғ
36. ϕϯνɿશจݕࡧੑೳ 5ສ݅ͷ୯ޠΛ௚ྻʹશจݕࡧ͢Δࡍͷॴཁඵ਺Λ ܭଌ͠ɺશจݕࡧੑೳͷൺֱΛߦ͍·͢ ୯ޠσʔλ͸ɺMeCab-IPAࣙॻͷ໊ࢺ൛ʢݻ༗໊ ࢺɾ஍Ҭʣ͔Β5ສ݅Λநग़͍ͯ͠·͢ $ nkf -w src/mecab-ipadic-2.7.0-20070801/ Noun.place.csv | cut -d"," -f1 | sort | uniq | sort -R | head -n 50000
37. ϕϯνɿશจݕࡧੑೳ ΫΤϦྫ SELECT SQL_NO_CACHE count(*) FROM search_with_innodb_mecab WHERE match(subject,content) against('+දࢀಓ' IN BOOLEAN MODE);
38. ϕϯνɿશจݕࡧੑೳ 1ϫʔυ 0 50 100 150 200 61.638 187.944 55.152 163.824 mroonga mecab InnoDB FTS mecab mroonga ngram InnoDB FTS ngram 75ສϨίʔυʹରͯ͠5ສ୯ޠΛॱ࣍SELECTͨ͠ࡍͷ࣮ߦॴཁ࣌ؒʢඵʣ
39. 5ສ୯ޠΛ༻ҙͯ͠௚ྻʹॱ࣍ݕࡧΛߦ͍·͕ͨ͠ɺ ֘౰݅਺͕0݅ͱͳΔ୯ޠ͕൒਺͋ΔࣄΛิ଍͠·͢ɻ search_with_mroonga_mecab: Noresult count: 33658 search_with_innodb_mecab: Noresult count: 24950 search_with_mroonga_ngram: Noresult count: 23129 search_with_innodb_ngram: Noresult count: 35500
40. ·ͱΊ MySQL-5.6.4 Ҏ߱Ͱ࢖͑ΔΑ͏ʹͳͬͨ InnoDB FTS͸ҙ֎ͱ࣮༻తͰ͢ InnoDB FTSΛ࢖͏ͳΒΞϓϦଆͰ MeCabΛ༻͍ͨ෼͔ͪॻ͖Λ͠·͠ΐ͏ MySQL୯ମͰશจݕࡧΛ࣮ݱ͍ͨ͠৔߹ͷ mroongaҎ֎ͷબ୒ࢶ͕ɺ1ͭ૿͑·ͨ͠
41. ࠷ޙʹ ৄࡉͳղઆهࣄ͸௥ͬͯΞοϓ͠·͢ɻ ͦͷࡍ͸Twitterʹ͓ͯ஌Βͤ͠·͢ɻ https://twitter.com/yoshi_ken http://d.hatena.ne.jp/yoshi-ken/
42. ࣍ճ༧ࠂ
43. ࣍ճ༧ࠂ 5SJUPOO
    .Z42- 4FOOB ͔Βͷ NSPPOHB
    .Z42-
    ҠߦΨΠυ
44. ͝ਗ਼ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ