mysql 全文索引 （二）ranking

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (2.48 sec)

mysql> select * from articles;
Empty set (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','This database tutorial ...'),
    -> ("How To Use MySQL",'After you went through a ...'),
    -> ('Optimizing Your Database','In this database tutorial ...'),
    -> ('MySQL vs. YourSQL','When comparing databases ...'),
    -> ('MySQL Security','When configured properly, MySQL ...'),
    -> ('Database, Database, Database','database database database'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..');
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from articles;
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |
|  2 | How To Use MySQL             | After you went through a ...        |
|  3 | Optimizing Your Database     | In this database tutorial ...       |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |
|  5 | MySQL Security               | When configured properly, MySQL ... |
|  6 | Database, Database, Database | database database database          |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |
+----+------------------------------+-------------------------------------+
8 rows in set (0.00 sec)

mysql> select id,title,body,match(title,body) against("databas fulltext") from articles where match(title,body) against("databas fulltext");
+----+-------------------------+---------------------------------+-----------------------------------------------+
| id | title                   | body                            | match(title,body) against("databas fulltext") |
+----+-------------------------+---------------------------------+-----------------------------------------------+
|  8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. |                            0.8155715465545654 |
+----+-------------------------+---------------------------------+-----------------------------------------------+
1 row in set (0.04 sec)

　　

实验：

mysql> select id,title,body,match(title,body) against("database" in boolean mode) as score from articles where match(title,body) against("database fulltext") order by score desc;
+----+------------------------------+---------------------------------+---------------------+
| id | title                        | body                            | score               |
+----+------------------------------+---------------------------------+---------------------+
|  6 | Database, Database, Database | database database database      |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...   | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...      | 0.18144935369491577 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a .. |                   0 |
+----+------------------------------+---------------------------------+---------------------+
4 rows in set (0.00 sec)

score的计算方法：

以id=6的行做例：

总记录数为：8

所有匹配到database的行数为：3

该行一共有database个数为6

IDF = log10（8/3）

TF = 6

单个单词搜索时候ranking搜索方法：

${rank} = ${TF} * ${IDF} * ${IDF}

mysql> select (log10(8/3)*6*log10(8/3));
+---------------------------+
| (log10(8/3)*6*log10(8/3)) |
+---------------------------+
|         1.088696164686938 |
+---------------------------+
1 row in set (0.00 sec)

多个单词搜索时候ranking的计算方法：

${rank} = ${TF} * ${IDF} * ${IDF} + ${TF} * ${IDF} * ${IDF}

mysql> select id,title,body,match(title,body) against ("mysql tutorial" in boolean mode) as score from articles order by score desc;
+----+------------------------------+-------------------------------------+----------------------+
| id | title                        | body                                | score                |
+----+------------------------------+-------------------------------------+----------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |   0.7405621409416199 |
|  3 | Optimizing Your Database     | In this database tutorial ...       |   0.3624762296676636 |
|  5 | MySQL Security               | When configured properly, MySQL ... | 0.031219376251101494 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     | 0.031219376251101494 |
|  2 | How To Use MySQL             | After you went through a ...        | 0.015609688125550747 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        | 0.015609688125550747 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... | 0.015609688125550747 |
|  6 | Database, Database, Database | database database database          |                    0 |
+----+------------------------------+-------------------------------------+----------------------+
8 rows in set (0.00 sec)

mysql> select (1*log10(8/6)*log10(8/6)+2*log10(8/2)*log10(8/2));
+---------------------------------------------------+
| (1*log10(8/6)*log10(8/6)+2*log10(8/2)*log10(8/2)) |
+---------------------------------------------------+
|                                0.7405621541938003 |
+---------------------------------------------------+
1 row in set (0.00 sec)

　　

参考资料：https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html