在laravel中使用mysql fulltext全文索引代替like查詢提高效能

huozi1024發表於2022-08-29
LaravelMySql索引

在laravel中使用mysql fulltext全文索引代替like提高效能(眾所周知like查詢以%開頭，會導致索引失效)。

建立全文索引

首先需要注意：

InnoDB 在 mysql5.6才支援全文索引。全文索引預設是英文分詞(即空格分詞)，在mysql5.7.6中內建了ngram全文解析器, 用來支援亞洲語種的分詞

所以如果需要搜尋中文需要mysql版本>=5.7.6

建立索引SQL：

1.使用CREATE TABLE建立
CREATE TABLE tbl_name(
...
FULLTEXT INDEX [index_name] (key_part,...) WITH PARSER `ngram`
)
2.使用ALTER TABLE建立
ALTER TABLE tbl_name
ADD FULLTEXT INDEX [index_name] (key_part,...) WITH PARSER `ngram`
3.使用CREATE INDEX建立
CREATE FULLTEXT INDEX index_name ON tbl_name (key_part,...)  WITH PARSER `ngram`

執行搜尋

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

使用全文索引進行搜尋時有三種模式：

1.自然語言模式[IN NATURAL LANGUAGE MODE]

預設模式，不能使用運算子，即搜尋的詞必須要出現

例子：SELECT * FROM tbl_name WHERE MATCH(name,summory) AGAINST (‘測試’)

2.布林模式[IN BOOLEAN MODE]

可以使用運算子，支援指定關鍵詞必須出現(+)、必須不能出現(-)或權重高低

例子：SELECT * FROM tbl_name WHERE MATCH(name,summory) AGAINST (‘+測試 -公司’ IN BOOLEAN MODE)

3.查詢擴充套件模式[IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION]

基於自然語言模式，根據自然模式搜尋到的結果擴充套件查詢

例子：SELECT * FROM tbl_name WHERE MATCH(name,summory) AGAINST (‘測試’ IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION)

在laravel中使用

1.laravel框架在9.x中已支援全文索引
// 自然語言模式
DB::table('tbl_name')->whereFullText('name', '測試')->get();
// 布林模式
DB::table('tbl_name')->whereFullText(['name', 'summory'], '+測試 -公司', ['mode' => 'boolean'])->count();
// 自然擴充套件模式
DB::table('tbl_name')->whereFullText('name', '測試', ['expanded' => true])->paginate(10);
// 模型使用
User::query()->whereFullText('name', '測試')->get();
2.laravel9以下的版本需要自己擴充套件Builder和MySqlGrammer

找到框架AppServiceProvider的boot()方法，增加如下程式碼

程式碼參考laravel9.x的官方實現，用法上也完全相同。框架升級時也可以避免重新修改業務程式碼。

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Grammars\MySqlGrammar;

...

public function boot()
{
    /**
     * 擴充套件 MySqlGrammar
     */
    MySqlGrammar::macro('whereFulltext', function(QueryBuilder $query, $where) {
        $columns = implode(',', array_map(function($column) use ($query){
            return $this->wrap($column);
        }, $where['columns']));

        $value = $this->parameter($where['value']);

        $mode = ($where['options']['mode'] ?? []) === 'boolean'
            ? ' in boolean mode'
            : ' in natural language mode';

        $expanded = ($where['options']['expanded'] ?? []) && ($where['options']['mode'] ?? []) !== 'boolean'
            ? ' with query expansion'
            : '';

        return "match ({$columns}) against (".$value."{$mode}{$expanded})";
    });

    /**
     * 擴充套件 Builder
     */
    Builder::macro('whereFullText', function($columns, $value, array $options = [], $boolean = 'and') {
        $type = 'Fulltext';

        $columns = (array) $columns;

        $this->wheres[] = compact('type', 'columns', 'value', 'options', 'boolean');

        $this->addBinding($value);

        return $this;
    });
}

參考文件

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章