rails: 使用 fts5/fts 对 sqlite 进行全文搜索 - rails 8.0

一种提升全文搜索速度的 sqlite 扩展

原来的搜索

确认 sqlite3 版本

# sqlie3 版本
sqlite3 --version
# rails console 确认
irb(main):001:0> ActiveRecord::Base.connection.select_value("SELECT sqlite_version()")

posts 的 title 优化

rails g migration CreatePostsFTS

# create virtual model
app/models/post_fts.rb

相关代码

# migration
class CreateVirtualPostFts < ActiveRecord::Migration[6.0]  
  def change    
    create_virtual_table :post_fts, :fts5, ["title"]
  end
end

# app/models/post_fts.rb
class PostFts < ApplicationRecord
  self.primary_key = "rowid"
end

Populate the virtual table

module Post::FullTextSearch
  extend ActiveSupport::Concern

  included do
    has_one :post_fts, foreign_key: "rowid"
  end

  def find_or_create_post_fts
    return if post_fts

    sql = ActiveRecord::Base.sanitize_sql_array(
      [
        "INSERT INTO post_fts (rowid, title) VALUES (?, ?)",
        id, title, overview
      ]
    )
    ActiveRecord::Base.connection.execute(sql)
  end
  
  # full_text_search
  class_methods do
    def full_text_search(input:, limit:)
      where("post_fts MATCH ?", input)
        .joins(:post_fts)
        .limit(limit)
        .order("bm25(post_fts)")
        .distinct
    end
  end
end

搜索方法改造

class PostsController < ApplicationController
  def index
    @posts = if params[:search].present?
      Post.full_text_search(input: params[:search], limit: 40)
    else
      Post.all.limit(40)
    end
  end
end

添加 simple 扩展支持中文

• 中文分词正常
• 速度提升明显

rails8 简化

• 特别注意这里 https://github.com/rails/rails/pull/52354

class CreatePostsFts < ActiveRecord::Migration[8.0]
  def change
    # 创建 FTS5 虚拟表
    create_virtual_table "posts_fts", "fts5", ["title", "tokenize='simple'"]

    # 数据迁移：从 posts 表插入数据到 posts_fts 表
    reversible do |dir|
      dir.up do
        execute <<~SQL
          INSERT INTO posts_fts (rowid, title)
          SELECT id, title FROM posts;
        SQL
      end
      dir.down do
        # 回滚时清空虚拟表
        execute("DELETE FROM posts_fts;")
      end
    end
  end
end