MySQL LIKE查询太慢？手把手搭建Elasticsearch站内搜索

📌 今日关键词：全文检索、Elasticsearch、MySQL LIKE、倒排索引、数据同步、Logstash、Canal

大家好，我是数据库小学妹 👋

我们之前学了索引、B+ 树底层原理，知道了索引能让查询飞起来。但最近做一个站内搜索功能时，遇到了一个新问题：

用 LIKE '%关键词%' 查询标题，数据量一上来，查询速度直接卡死，用户体验极差。

我也想过建索引，但 LIKE 后面带通配符 %，索引根本用不上。那怎么办？

有人推荐我用 Elasticsearch，但我心里一堆问号：

为什么 MySQL 索引用不上？
Elasticsearch 真的那么神奇？
MySQL 和 ES 的数据怎么同步？
搭建一个搜索功能到底有多复杂？

今天就聊聊这段时间踩过的坑、查过的资料，从 MySQL LIKE 到 Elasticsearch 的实战之路。

一、MySQL LIKE 为什么这么慢？

先看一个场景：用户搜索包含"数据库"的文章标题。

传统方案：LIKE 模糊查询

-- 创建测试表
CREATE TABLE articles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入测试数据（假设有 10 万条）
INSERT INTO articles (title, content) VALUES
('数据库入门教程', '这是一篇关于数据库的入门文章...'),
('MySQL 索引优化指南', '本文教你如何优化索引...'),
-- ... 更多数据

-- 搜索包含"数据库"的文章
SELECT * FROM articles WHERE title LIKE '%数据库%';

为什么这么慢？

执行计划分析：
EXPLAIN SELECT * FROM articles WHERE title LIKE '%数据库%';

结果：
type: ALL  ← 全表扫描！
rows: 100000  ← 扫描 10 万行
Extra: Using where

问题分析：

LIKE '%数据库%' 的模式：

• 前导通配符 %，导致无法使用索引
• 数据库必须逐行扫描每一条记录
• 对比时间复杂度是 O(n)

随着数据量增长：

• 1 万条数据：查询时间约 100ms
• 10 万条数据：查询时间约 1 秒
• 100 万条数据：查询时间约 10 秒
• 用户体验直接崩塌

LIKE 的常见误区

误区 1：给 title 字段建索引就能提速

-- 建索引
CREATE INDEX idx_title ON articles (title);

-- 搜索（还是用不上！）
SELECT * FROM articles WHERE title LIKE '%数据库%';

索引完全没用，依然是全表扫描。

误区 2：用 OR 多个关键词

-- 搜索"数据库"或"教程"
SELECT * FROM articles WHERE title LIKE '%数据库%' OR title LIKE '%教程%';

多个 OR 条件会让查询更慢。

二、方案对比：MySQL 全文索引 vs Elasticsearch

面对 LIKE 性能问题，主要有两种解决方案：

方案 1：MySQL 原生全文索引

MySQL 提供了全文索引功能，专门处理文本搜索。

MySQL 全文索引的使用

-- 创建全文索引（仅支持 MyISAM/InnoDB，且字段类型必须是 TEXT/CHAR/VARCHAR）
CREATE FULLTEXT INDEX ft_title ON articles (title, content);

-- 使用全文索引查询（注意语法变化！）
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

MySQL 全文索引的特点

MySQL 全文索引的局限

-- 中文分词问题（ngram 模式）
CREATE FULLTEXT INDEX ft_title ON articles (title) WITH PARSER ngram;

-- 搜索"数据库"
SELECT * FROM articles
WHERE MATCH(title) AGAINST('数据库' IN NATURAL LANGUAGE MODE);

-- 问题：
-- 1. ngram 是按字符切分，不是按词切分，精度不高
-- 2. 不支持同义词、拼音搜索
-- 3. 不支持复杂的搜索逻辑（AND、OR、NOT 组合）
-- 4. 性能仍然比不上专业的搜索引擎

MySQL 全文索引适合小数据量的英文搜索，但中文支持和灵活性有限。

方案 2：Elasticsearch（推荐）

Elasticsearch 是基于 Lucene 的分布式搜索引擎，专为全文检索设计。

Elasticsearch 的核心优势

Elasticsearch 原理：倒排索引

B+ 树（MySQL 索引）vs 倒排索引（ES 索引）：

B+ 树（传统索引）：
文档 1 → [数据库, 教程, 入门]
文档 2 → [MySQL, 索引, 优化]

查询"数据库"：需要遍历所有文档

倒排索引（ES 索引）：
数据库 → [文档 1, 文档 3, 文档 5]
教程 → [文档 1, 文档 2]
入门 → [文档 1]

查询"数据库"：直接从"数据库"的倒排列表获取文档

倒排索引的优势：

• 查询速度 O(1)，与数据量无关
• 支持复杂的布尔查询（AND、OR、NOT）
• 支持模糊匹配、同义词、拼音搜索

三、实战：搭建 MySQL + Elasticsearch 站内搜索

整体架构

┌─────────────┐       ┌─────────────┐       ┌─────────────┐
│   用户查询   │  --->  │  ES 搜索    │  <---  │  MySQL      │
│   (前端)    │       │  (7层/9200) │       │  (3306)    │
└─────────────┘       └─────────────┘       └─────────────┘
                           │                        │
                           └────────┬───────────────┘
                                    │
                            数据同步
                         (Logstash/Canal)

步骤 1：安装 Elasticsearch

# 下载 ES 8.x（使用 Docker）
docker run -d \
  --name elasticsearch \
  -p 9200:9200 \
  -p 9300:9300 \
  -e "discovery.type=single-node" \
  -e "ES_JAVA_OPTS=-Xms512m -Xmx512m" \
  docker.elastic.co/elasticsearch/elasticsearch:8.12.0

# 验证安装
curl http://localhost:9200

步骤 2：安装 IK 中文分词器

# 进入 ES 容器
docker exec -it elasticsearch /bin/bash

# 安装 IK 分词器
./bin/elasticsearch-plugin install https://github.com/medcl/elasticsearch-analysis-ik/releases/download/v8.12.0/elasticsearch-analysis-ik-8.12.0.zip

# 重启 ES
docker restart elasticsearch

步骤 3：创建 ES 索引

# 创建索引（定义字段类型和分词器）
curl -X PUT "localhost:9200/articles" -H 'Content-Type: application/json' -d'
{
  "settings": {
    "analysis": {
      "analyzer": {
        "ik_max_word": {
          "type": "custom",
          "tokenizer": "ik_max_word"
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "id": {"type": "long"},
      "title": {
        "type": "text",
        "analyzer": "ik_max_word",
        "search_analyzer": "ik_smart"
      },
      "content": {
        "type": "text",
        "analyzer": "ik_max_word",
        "search_analyzer": "ik_smart"
      },
      "created_at": {"type": "date"}
    }
  }
}'

步骤 4：数据同步方案

数据从 MySQL 同步到 ES，有三种主流方案：

方案 A：Logstash（推荐新手）

官方支持、配置简单、适合定时同步，但实时性差（需要定时轮询）。

# 下载 Logstash
docker run -d \
  --name logstash \
  --link elasticsearch:elasticsearch \
  -v /path/to/logstash.conf:/usr/share/logstash/pipeline/logstash.conf \
  docker.elastic.co/logstash/logstash:8.12.0

logstash.conf 配置：

input {
  jdbc {
    jdbc_driver_library => "/path/to/mysql-connector-j-8.0.33.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://mysql:3306/your_db"
    jdbc_user => "root"
    jdbc_password => "password"
    schedule => "* * * * *"  # 每分钟同步一次
    statement => "SELECT * FROM articles WHERE updated_at > :sql_last_value"
    use_column_value => true
    tracking_column => "updated_at"
    tracking_column_type => "timestamp"
  }
}

output {
  elasticsearch {
    hosts => ["elasticsearch:9200"]
    index => "articles"
    document_id => "%{id}"
  }
}

方案 B：Canal（推荐生产环境）

实时同步（基于 Binlog）、高性能，但配置复杂、需要额外部署。

# 部署 Canal Server
git clone https://github.com/alibaba/canal.git
cd canal
mvn clean install -Dmaven.test.skip=true

# 启动 Canal
sh bin/startup.sh

Canal 适配器配置（canal-adapter/conf/es7/xxx.yml）：

dataSourceKey: defaultDS
outerAdapterKey: esKey
destination: example
groupId: g1
esMapping:
  _index: articles
  _id: _id
  upsert: true
  sql: "SELECT id, title, content, created_at FROM articles"
  commitBatch: 3000

方案 C：应用层双写（最灵活）

实时性强、逻辑可控，但需要改代码、可能失败。

// Java 伪代码
@Service
public class ArticleService {
   

    @Autowired
    private ArticleMapper articleMapper;

    @Autowired
    private ElasticsearchRestTemplate esTemplate;

    @Transactional
    public void createArticle(Article article) {
   
        // 1. 写入 MySQL
        articleMapper.insert(article);

        // 2. 同步写入 ES
        try {
   
            esTemplate.save(article);
        } catch (Exception e) {
   
            // 写入失败，记录日志或重试
            log.error("ES 写入失败", e);
        }
    }
}

四、实战代码：搜索功能实现

Python 示例（使用 Flask + ES）

from flask import Flask, request, jsonify
from elasticsearch import Elasticsearch

app = Flask(__name__)
es = Elasticsearch(["http://localhost:9200"])

@app.route('/search', methods=['GET'])
def search():
    keyword = request.args.get('q', '')
    page = int(request.args.get('page', 1))
    size = int(request.args.get('size', 10))

    # ES 查询
    query = {
   
        "query": {
   
            "multi_match": {
   
                "query": keyword,
                "fields": ["title^2", "content"],  # title 权重更高
                "type": "best_fields"
            }
        },
        "from": (page - 1) * size,
        "size": size,
        "highlight": {
   
            "fields": {
   
                "title": {
   },
                "content": {
   }
            }
        }
    }

    result = es.search(index="articles", body=query)

    # 格式化结果
    hits = result['hits']['hits']
    total = result['hits']['total']['value']

    articles = []
    for hit in hits:
        articles.append({
   
            'id': hit['_source']['id'],
            'title': hit['_source']['title'],
            'content': hit['_source']['content'][:200] + '...',  # 截取前 200 字
            'highlight': hit.get('highlight', {
   })
        })

    return jsonify({
   
        'total': total,
        'page': page,
        'size': size,
        'data': articles
    })

if __name__ == '__main__':
    app.run(debug=True)

测试搜索

# 搜索"数据库教程"
curl "http://localhost:5000/search?q=数据库教程&page=1&size=10"

# 响应示例
{
   
  "total": 125,
  "page": 1,
  "size": 10,
  "data": [
    {
   
      "id": 1,
      "title": "数据库入门教程",
      "content": "这是一篇关于数据库的入门文章...",
      "highlight": {
   
        "title": ["<em>数据库</em>入门<em>教程</em>"],
        "content": ["这是一篇关于<em>数据库</em>的入门文章..."]
      }
    }
  ]
}

五、新手避坑指南（血泪总结）

❌ 坑 1：忘记处理中文分词

# 错误：直接用默认分词器（不支持中文）
curl -X PUT "localhost:9200/articles" -d'{
  "mappings": {
    "properties": {
      "title": {"type": "text"}  ❌ 没有指定中文分词器
    }
  }
}'

# 搜索"数据库"可能搜不到

正确做法：

# 使用 IK 分词器
curl -X PUT "localhost:9200/articles" -d'{
  "settings": {
    "analysis": {
      "analyzer": {
        "ik_max_word": {
          "type": "custom",
          "tokenizer": "ik_max_word"
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "title": {
        "type": "text",
        "analyzer": "ik_max_word"  ✅
      }
    }
  }
}'

❌ 坑 2：数据同步延迟导致搜索不到

用 Logstash 定时同步，用户刚发布的文章搜索不到。

解决方案：

1. 使用 Canal 实时同步
2. 或应用层双写（写 MySQL 后立即写 ES）

❌ 坑 3：ES 索引字段类型错误

# 错误：把 id 定义为 text 而不是 long
curl -X PUT "localhost:9200/articles" -d'{
  "mappings": {
    "properties": {
      "id": {"type": "text"}  ❌ text 类型不能排序
    }
  }
}'

# 结果：按 id 排序会报错

正确做法：

curl -X PUT "localhost:9200/articles" -d'{
  "mappings": {
    "properties": {
      "id": {"type": "long"}  ✅
    }
  }
}'

❌ 坑 4：忘记处理索引不存在的情况

# 错误：直接搜索，索引不存在会报错
result = es.search(index="articles", body=query)

# 正确：先检查索引是否存在
if not es.indices.exists(index="articles"):
    return jsonify({
   "error": "索引不存在，请先创建"}), 404

result = es.search(index="articles", body=query)

❌ 坑 5：搜索结果没有返回高亮

# 错误：查询里没有 highlight 配置
query = {
   
    "query": {
   ...}
}

# 正确：添加 highlight
query = {
   
    "query": {
   ...},
    "highlight": {
   
        "fields": {
   
            "title": {
   },
            "content": {
   }
        }
    }
}

六、性能对比：LIKE vs ES

实测 10 万条文章数据的搜索性能：

Elasticsearch 在搜索性能上有压倒性优势，数据量越大差距越明显。

七、今日学习心得

今天的内容不少，但核心就这几句话：

1. MySQL LIKE 性能差是因为前导通配符导致无法使用索引
2. MySQL 全文索引适合小数据量的英文搜索，中文支持有限
3. Elasticsearch 基于倒排索引，搜索速度与数据量无关
4. 数据同步有三种方案：Logstash（定时）、Canal（实时）、应用层双写（灵活）
5. 搜索性能提升 100 倍以上，用户体验质的飞跃

以前觉得"搜索功能很简单，直接 LIKE 就行"，现在才知道专业的搜索需要专门的引擎。

就像查书，B+ 树索引像目录页，能快速定位章节；而倒排索引像书后的关键词索引，能快速找到所有提到某个关键词的页面。

不同的场景用不同的工具，这才是技术的智慧。

👋 我是数据库小学妹，一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕

本文示例基于 MySQL 8.0 + Elasticsearch 8.12。理解全文检索原理，对掌握搜索引擎和性能优化非常有帮助。