全文索引&&地理空间索引

Ⅰ、全文索引

• 搜索引擎的实现核心技术,搜索类似where col like '%xxx%';关键字可以出现再某个列任何位置
• 这种查询条件,B+ tree索引是无法使用的。如果col上创建了索引,因为排序过了,所以能用到索引,但是对其中某个关键字是无法排序的
• 首先需要通过分词进行各词的提取,把各个分词再保存到各个B+ tree索引中
• 支持在varchar,char,text等类型上创建全文索引
• MySQL5.6版本之前仅MyISAM支持全文索引
• MySQL5.6版本InnoDB引擎支持全文索引
• 上面的支持只支持英文的全文索引
• MySQL5.7版本支持中文、日文、韩文的全文索引(真正生产环境可用)
• 目前一张表只能有一个全文索引
• 添加全文索引时表是只读的,不可写入与更新,即不支持online-ddl,这种问题就要用pt了

tips：
之前全文索引不是用MySQL来做的,用lucence做

在title,body列上创建全文索引

alter table xxx add fulltext index idx_xxx (title,body);

全文索引SQL查询,不能使用like进行,需要使用全文索引的语法

1.1 检索方式：

• ①自然语言检索

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

查看相关性

mysql> SELECT id, body, MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE) AS score
    FROM articles WHERE MATCH (title,body) AGAINST
    ('Security implications of running MySQL as root'
    IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

• ②布尔检索(一定要有,- 不可以有)

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+-----------------------+-------------------------------------+
| id | title                 | body                                |
+----+-----------------------+-------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Well | After you went through a ...        |
|  3 | Optimizing MySQL      | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security        | When configured properly, MySQL ... |
+----+-----------------------+-------------------------------------+

• ③查询扩展检索

通常不要使用WITH QUERY EXPANSION ,是一个两次搜索的过程,第二次搜索的搜索短语是与第一次搜索中的几个最高相关性的原始搜索短语

mysql> SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database' WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

最常见的场景：column => tag => mysql,database,it,oracle 表结构设计有问题,应该设计为一对多,一个文章对应多个tag,有张专门tag表用来反过来查

相关参数：

ft_min_word_len = 全文检索的最小许可字符,默认4,中文通常两个字一个词语,设置为2比较好

tips：
如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高

Ⅱ、地理空间索引

• MySQL5.7版本之前仅MyISAM引起支持地理空间索引
• MySQL5.7版本InnoDB引擎支持地理空间索引
• 之前都用mongodb来做地理空间索引
• 追求性能的话用redis

CREATE TABLE nodes (
    id BIGINT(20) DEFAULT NULL,
    geom GEOMETRY NOT NULL,
    user VARCHAR(50) DEFAULT NULL,
    version INT(11) DEFAULT NULL,
    timestamp VARCHAR(20) DEFAULT NULL,
    UNIQUE KEY i_nodeides (id),
    SPATIAL KEY i_geomidx ( geom )
)  ENGINE=INNODB DEFAULT CHARSET=LATIN1;

用来存经纬度

alter table nodes add column tags text, add fulltext index(tags);

UPDATE nodes
SET
    tags = (SELECT
            GROUP_CONCAT(CONCAT(k, v)
                    SEPARATOR ',')
        FROM
            nodetags
        WHERE
            nodetags.id = nodes.id
        GROUP BY nodes.id);

SELECT
    id,
    ST_DISTANCE_SPHERE(POINT(- 73.951368, 40.716743), geom) AS distance_in_meters,
    tags,
    ST_ASTEXT(geom)
FROM
    nodes
WHERE
    ST_CONTAINS(ST_MAKEENVELOPE(POINT((- 73.951368 + (20 / lll)),
                        (40.716743 + (20 / lll))),
                    POINT((- 73.951368 - (20 / 111)),
                        (40.716743 - (20 / lll)))),
            geom)
        AND MATCH (tags) AGAINST ('+thai +restaurant' IN BOOLEAN MODE)
ORDER BY distance_in_meters
LIMIT 10;
查询附近二十公里的泰国餐馆

tips：
地理空间索引经测试性能特别差

真的要这种地理空间服务功能要用5.7的GeoHash,配合函数索引

alter table nodes
add column geohash varchar(128)
as (st_geohash(geom,6)) virtual

alter table nodes add index i_geohash_idx(geohash)

性能提升12倍,基本上用mongodb来存,最好就用redis,MySQL用的不多,快递行业的轨迹可能用得到,量也不大,只是保存一下,做最后的持久化