mariadb全文索引
# 前言
如同翻译的那样，Mroonga是一个适用于MySQL的存储引擎。它为所有MySQL用户提供了快速的全文搜索功能。
这里直接演示怎么在MariaDB上启用mroonga存储引擎
# 1) 加载mroonga插件
MariaDB [(none)]>INSTALL SONAME 'ha_mroonga';
MariaDB [(none)]> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
1
2
3
4
2
3
4
# 2）建表测试一下
MariaDB [(none)]>create database jerry;
MariaDB [(none)]>use jerry
MariaDB [jerry]> create table t1 (id int not null,name varchar(100)not null,notes text,fulltext index(notes))engine=mroonga;
1
2
3
4
5
6
2
3
4
5
6
# 3）胡乱插入一些数据
MariaDB [jerry]> insert into t1 values(1,'擦擦擦','我也不会告诉你');
Query OK, 1 row affected (0.00 sec)
MariaDB [jerry]> insert into t1 values(2,'啦啦啦','我也不会告诉你');
Query OK, 1 row affected (0.01 sec)
MariaDB [jerry]> insert into t1 values(3,'dashaba','森马服饰');
Query OK, 1 row affected (0.00 sec)
MariaDB [jerry]> insert into t1 values(4,'victor','大森马投资有限公司');
Query OK, 1 row affected (0.00 sec)
MariaDB [jerry]> insert into t1 values(5,'kukumimilulu','森马跨境电商有限公司');
Query OK, 1 row affected (0.00 sec)
MariaDB [jerry]> insert into t1 values(6,'王思聪','王爸爸联合大森马跨境电商有限公司');
Query OK, 1 row affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 4）用如下命令查询
MariaDB [jerry]> select * from t1 where match(notes) against('大森马' in BOOLEAN MODE);
+----+-----------+--------------------------------------------------+
| id | name | notes |
+----+-----------+--------------------------------------------------+
| 4 | victor | 大森马投资有限公司 |
| 6 | 王思聪 | 王爸爸联合大森马跨境电商有限公司 |
+----+-----------+--------------------------------------------------+
2 rows in set (0.00 sec)
5 ) 通过执行计划我们可以看到已经用到notes这个全文索引，并且只需要扫描一行
MariaDB [jerry]> explain select * from t1 where match(notes) against('大森马' in BOOLEAN MODE);
+------+-------------+-------+----------+---------------+-------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+----------+---------------+-------+---------+------+------+-----------------------------------+
| 1 | SIMPLE | t1 | fulltext | notes | notes | 0 | | 1 | Using where with pushed condition |
+------+-------------+-------+----------+---------------+-------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
上次更新: 2023/03/24, 08:53:10