如果你还在用 MySQL 扛搜索需求我猜你早晚会遇到这么一天一张千万级的商品表用户在前端输入两三个字后端一条LIKE %关键词%打出去数据库 CPU 瞬间飙高接口响应卡在两秒开外。我在接手一个内部管理系统的搜索功能时就被这个场景狠狠教育了一顿排查到最后真正把方案定下来的就是 MySQL 和 Elasticsearch 的组合MySQL 继续当业务系统的事实标准存储Elasticsearch 专职扛起全文检索和聚合分析。这篇文章把我从选型、原理、数据同步到上线踩坑的完整过程写出来希望能给正在这条路上纠结的人一个参考。1. 从 LIKE 地狱到搜索体验为什么会走到 MySQL 和 Elasticsearch 的组合1.1 千万级数据下的 LIKE 查询到底慢在哪里先说当时的具体场景。某内部管理系统的商品池大概有一千两百万行记录前端搜索框支持按商品名称、品牌、类目模糊搜索。最初用的就是 MySQL 的LIKE %关键词%上线头几个月数据量小时没问题等数据涨到几百万行以后搜索接口的 P95 响应时间直接从 200ms 飙升到 2.8 秒。用EXPLAIN看执行计划核心问题一目了然typeALL走的是全表扫描。MySQL 的 B 树索引对LIKE 关键词%这种前缀匹配还能用上索引但业务方要求的是模糊包含匹配%关键词%这个写法会让最左前缀匹配直接失效优化器只能逐行扫描。更痛苦的是业务查询条件不只是单字段SELECT id, product_name, brand_name, price, category_id FROM products WHERE product_name LIKE %手机% OR brand_name LIKE %华为% OR category_name LIKE %数码% ORDER BY sales_volume DESC LIMIT 20;OR组合这三个LIKE等于把三张全表扫描的结果做合并临时表和排序的内存开销全部压上来。我给这条查询做过基准测试一千两百万行数据下单次查询平均耗时 1.7 秒数据库 CPU 占用 85% 以上。当时我还尝试过几种 MySQL 侧的优化方案给字段加全文索引MySQL 5.7 的 ngram parser、拆表、加缓存。ngram 全文索引在短文本上有点效果但对中文分词的支持不稳定ngram_token_size设成 2 之后搜手机壳这种三个字符的词匹配逻辑很别扭。缓存只能扛住热词一旦用户搜索长尾词该慢还是慢。1.2 什么样的业务才真正值得引入 Elasticsearch被 LIKE 折磨过之后我本来想的是继续在 MySQL 里想办法后来跟一个做电商搜索的朋友聊他给我一句话点醒了MySQL 是存储不是搜索引擎。搜索这个动作本质上是在海量文本里快速找到相关的文档这需要倒排索引和相关性算法这恰恰是 ElasticsSearch 的主场。但我要先说清楚不是所有场景都适合上 ES场景是否适合原因商品、文章、知识库的全文模糊搜索非常适合查询条件天然是文本相关性基于多字段组合的海量数据聚合分析非常适合ES 的聚合能力比 MySQL 的 GROUP BY 强太多简单的 ID 点查、强事务写操作不适合ES 没有事务点查性能也没必要绕一层数据量只有几万行、查询不复杂不适合引入 ES 等于自找一套集群要维护具体到我的场景业务需求有三个明显特征一是模糊搜索是核心路径用户输入精确短词或完整商品名的比例不高大部分都是华为小米这种品牌词二是需要按销量、价格、上架时间做组合排序三是还得支持类目聚合筛选。MySQL 在这三条路上每条都很吃力ES 却天生擅长。1.3 架构演进从单库到读写分离再到搜索引擎我们的系统一开始就是简单的主从架构MySQL 一主一从读写分离。业务量涨上来之后先扛不住的是全文搜索因为读压力全堆在从库上一条慢查询就能拖垮从库的其它业务查询。所以最终方案定成了 MySQL 保留所有写操作和事务逻辑ES 只做搜索和聚合的读模型。MySQL 这边数据照常写入通过同步管道把变更推给 ES搜索接口全部走 ES。数据一致性上允许秒级延迟但搜索体验从 2.8 秒降到了 80ms 以内。这个架构里最关键的不是 ES 本身多强而是 MySQL 和 ES 之间那条同步链路怎么做扎实。接下来我把同步方案的选型过程完整摊开讲。2. 同步链路的三条路线我给每条都做了压力测试MySQL 和 ES 之间最重要的不是怎么查而是怎么同步。我见过很多人在这上面翻车要么同步延迟导致查不到最新数据要么数据不一致导致线上事故。同步方案基本就三条路定时脚本、业务侧双写、订阅 binlog我挨个说清楚各自的代价。2.1 定时脚本同步简单但最多只配当兜底最先想到的方案自然是定时任务每隔五分钟把增量数据从 MySQL 拉到 ES。实现上就是一条 SQLSELECT * FROM products WHERE updated_at :last_sync_time然后批量写入 ES。这个方案的好处是代码量极小一个 cron 脚本加一个接口就能跑。但它的毛病也很明显第一扫描updated_at需要索引而且数据量大了之后每次全表扫增量会越来越慢第二删除操作很难感知业务方把商品下架软删除之后updated_at会变化但 u状态字段变了你得额外处理第三五分钟延迟太不可控用户刚创建的商品五分钟搜不到产品经理第一个跳出来投诉。实测下来定时同步适合数据量小、延迟容忍度高、只读业务比如内部报表的数据仓库同步。正经的搜索场景它扛不住。2.2 业务侧双写看起来快实际上是个大坑第二条路线是在业务代码里MySQL 写完的同时直接写 ES。Transactional public void createProduct(Product product) { productMapper.insert(product); esClient.index(products, product); }这个方案最大的优点是实时性好MySQL 写入之后立刻就能搜到。但问题也随之而来一是分布式事务难题。MySQL 提交成功但 ES 写入失败或者反过来两边就出现不一致。用本地消息表或者事务消息能缓解但业务代码会变得异常臃肿。二是双写是双倍的故障面。ES 集群抖动一次你的核心写入链路直接连带报错。实测中 ES 的bulk写入高峰时偶尔会有超时重试如果重试逻辑写得粗糙就会出现 MySQL 有数据、ES 没数据的情况。三是代码侵入性太强。每个写操作都要多写一段 ES 逻辑业务一多必然会漏。当时我们用双写方案跑了两周线上每天能发现几十条不一致记录全靠补偿任务救场。这个方案只适合数据模型极其简单、写入频率低的内部系统电商这种高并发写入的业务场景千万别碰。2.3 订阅 binlog才是最终的正解双写方案暴露的问题让我把目光转向了 MySQL 的 binlog。binlog 是 MySQL 的二进制作业日志每次数据变更都会记录在里面。如果有一个组件能像从库一样监听 binlog解析出增删改事件再转发给 ES那同步过程就能做到和业务代码完全解耦。这里选型基本就是 canal 或者 Debezium。我们用的 canal部署方式简单中文文档也多。canal 的工作原理不复杂把自己伪装成 MySQL 的 slave 节点向主库请求 binlog 数据解析成结构化事件然后可以直连 ES也可以先丢进消息队列解耦。因为我们后面还接了一套日志分析链路所以当时选择了 canal → Kafka → Logstash → ES 这条管道。这条链路的好处是业务代码一行都不用改MySQL 提交的每个事务都会以事件的形式流到 ES秒级内完成同步Kafka 把生产者和消费者彻底解耦ES 集群抖动时 Kafka 会缓冲堆积恢复后自动追平。三条路线对比下来最终选型的结论非常清晰方案实时性代码侵入一致性保障运维成本适用场景定时任务分钟级低弱删除难感知最低数据量小、容忍延迟的报表数据业务双写实时高弱需补偿任务中极简单写入模型低风险系统binlog 订阅秒级零强事件可靠中高生产级搜索、大数据同步3. 倒排索引到底倒排在哪ES 能解决 MySQL 解决不了的问题很多人知道 ES 快但说不清快在哪。我在这里花一整节把底层原理讲明白因为只有理解了原理你后面配置 mapping、选分词器、调查询的时候才不会瞎试。3.1 B 树索引和倒排索引的思维差异MySQL 的 InnoDB 索引结构是 B 树相当于一本按页码排好的字典你要找华为这两个字得先在字典目录里找到华开头的位置然后沿着链往下找。这对于精确匹配或者前缀匹配很高效但如果你要找的是所有包含手机这两个字的段落B 树根本帮不上忙只能把整本字典逐页翻一遍。ES 用的倒排索引逻辑完全反过来。它把每个文档拆成词项然后记录每个词项出现在哪些文档里。比如有三个商品文档1华为 Mate60 手机文档2小米 手机 充电器文档3华为 智能手表ES 建立倒排索引后结构大致是这样词项文档列表华为文档1, 文档3手机文档1, 文档2充电器文档2小米文档2用户搜索手机的时候ES 直接查倒排索引拿到文档1和文档2根本不需要扫描全部数据。这就解释了为什么 ES 在海量数据下的模糊搜索性能远超 MySQL因为它压根不扫而是查索引。3.2 分词器决定搜索质量BM25 决定相关性排序倒排索引的质量取决于一件事分词。MySQL 的 LIKE 是粗暴的字符串包含ES 则是先把文本切成词项再建立词项到文档的映射。中英文混排的时候分词器选不好搜索体验会非常奇怪。默认的 standard 分词器对英文友好但对中文就是一个字一个词效果很差。我们线上用的 IK 分词器ik_max_word会把华为手机切分成华为、手机、华为手机等尽可能多的词索引更全搜索时用ik_smart做粗粒度切分保证查得准。实测下来中文搜索体验质的飞跃。有了倒排索引和分词还得排序。ES 默认的相关性算法是 BM25它的核心思路用一句话概括就是一个词在某个文档里出现得越多越相关但它在整个文档库里出现得越频繁就越没区分度。打个比方手机在一篇文章里出现十次那这篇文章大概率是讲手机的但如果公司这个词在全库一半文档里都出现了那用户搜公司的时候它就不能作为主要排序依据。BM25 就是在这个平衡中计算出每个文档的分数把最相关的排前面。3.3 实测对比同一套数据MySQL 和 ES 的差距纸面原理说完了上个实测数据。我们用同一批一千万行商品数据同样搜索华为手机MySQLLIKE %华为%手机%组合查询P95 耗时 1.4 秒ES 的match查询P95 耗时 55ms。差距接近 25 倍。再对比聚合查询。业务方有一个页面需要按类目统计商品数量并按品牌维度展示热销 TOP10。MySQL 写几十行 SQL 加索引优化跑一次聚合要 5 秒ES 一个terms聚合加top_hits子聚合一条 DSL 解决300ms 返回还能直接把排序和分页全做了。这个差距不是 ES 比 MySQL 聪明纯粹是数据结构的选择不同一个是查找精确值一个是查找包含关系。技术选型没有银弹但模糊搜索这个场景ES 确实更合适。4. 完整落地流水线canal Kafka Logstash 从零搭建实录讲完了为什么下面进入怎么做。这一节我把我们线上跑的完整同步管道每个节点都写出来包括配置文件和关键参数照着做就能跑通一套最小可用版本。4.1 前置准备MySQL 开启 binlog 并创建专用账号canal 要读取 binlogMySQL 必须先开启 binlog而且格式要用 row 模式。检查当前配置mysql SHOW VARIABLES LIKE log_bin;如果没开启修改 my.cnf 并重启[mysqld] log-binmysql-bin binlog-formatROW server-id1然后创建一个 canal 专用账号这个账号只需要SELECT、REPLICATION SLAVE、REPLICATION CLIENT三个权限遵循最小权限原则CREATE USER canal% IDENTIFIED BY your_password; GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO canal%; FLUSH PRIVILEGES;这里踩过一个坑server-id 不能和从库的 server-id 重复否则 canal 连接时会被 MySQL 踢掉。我们一开始没注意canal 日志里全是Slave I/O thread killed due to conflict报错。4.2 canal 部署和配置伪装成一个乖巧的 slavecanal 的部署方式很多我们用的 Docker 方式省心。核心配置如下services: canal-server: image: canal/canal-server:v1.1.7 environment: canal.instance.master.address: 192.168.1.10:3306 canal.instance.dbUsername: canal canal.instance.dbPassword: your_password canal.instance.connectionCharset: UTF-8 canal.instance.filter.regex: test_db\\..* canal.mq.topic: canal-test canal.mq.flatMessage: true canal.mq.servers: 192.168.1.20:9092 ports: - 11111:11111几个配置点解释一下filter.regex是 canal 要监听的库表规则格式是库名\\..*可以用逗号写多个。这一项写错了会导致监听的表不对当时我们没注意转义踩了好几次坑。canal.mq.servers是 Kafka 的地址如果消息要进 MQ 就必须配。flatMessagetrue表示 canal 输出 JSON 平铺消息Logstash 消费时会简单很多。canal 的底层原理一句话讲就是它把自己伪装成 MySQL 的从库向主库发送COM_REGISTER_SLAVE请求然后持续接收 binlog 事件流解析成结构化消息后投递到 MQ。4.3 Kafka 主题设计与消息格式Kafka 这边我们需要一个主题canal-test分区数建议按数据量定。当时我们数据写入量大概是每秒 300 条变更分区设了 6 个Logstash 消费者开了 6 个并行度基本能跟上。canal 切换到flatMessage后消息格式是这样的{ data: [ { id: 10086, product_name: 华为 Mate60 手机, brand_name: 华为, price: 6999.0, status: 1, updated_at: 2025-01-12 10:30:00 } ], database: test_db, table: products, type: UPDATE, ts: 1702345678901 }type字段有三种值INSERT、UPDATE、DELETE。Logstash 后面就要靠这个字段决定对 ES 执行什么操作。4.4 Logstash 消费 Kafka 消息并写入 ESLogstash 的 pipeline 配置是整个流水线的核心它干三件事从 Kafka 取消息、按 canal 消息格式解析、决定 ES 写入动作。input { kafka { bootstrap_servers 192.168.1.20:9092 topics [canal-test] consumer_threads 6 codec json auto_offset_reset latest } } filter { if [type] DELETE { # 删除操作只需要文档 id不需要 data 里其他字段 mutate { add_field { es_id %{[data][0][id]} } } } else { mutate { add_field { es_id %{[data][0][id]} } } } } output { if [type] DELETE { elasticsearch { hosts [http://192.168.1.30:9200] index products action delete document_id %{[es_id]} } } else { elasticsearch { hosts [http://192.168.1.30:9200] index products document_id %{[es_id]} # 这里用 upsert 而不是 index防止旧文档被覆盖时丢失未同步字段 doc_as_upsert true } } }这里有个很关键的细节canal 的data字段是数组结构[data][0]拿到的才是真正的行数据。Logstash 的字段引用语法里%{[data][0][id]}这种写法容易写错尤其是嵌套层级多的时候。我建议你先把一条 canal 消息存成 JSON 文件用 Logstash 的调试模式跑一下看看字段解析结果再写正式的 filter。4.5 索引 mapping 设计先想清楚查询再定字段类型ES 的 index 相当于 MySQL 的表mapping 相当于表结构。设计 mapping 的第一原则是从查询倒推字段类型而不是从业务表原样复制。我们的商品索引进过几次坑先把最终版的 mapping 写出来{ settings: { number_of_shards: 3, number_of_replicas: 1, analysis: { analyzer: { ik_pinyin_analyzer: { type: custom, tokenizer: ik_max_word, filter: [lowercase, pinyin_filter] } } } }, mappings: { properties: { id: { type: long }, product_name: { type: text, analyzer: ik_max_word, search_analyzer: ik_smart, fields: { keyword: { type: keyword, ignore_above: 256 } } }, brand_name: { type: text, analyzer: ik_max_word, search_analyzer: ik_smart }, category_id: { type: keyword }, category_name: { type: text, analyzer: ik_max_word, search_analyzer: ik_smart }, price: { type: double }, sales_volume: { type: long }, status: { type: byte }, updated_at: { type: date, format: yyyy-MM-dd HH:mm:ss } } } }几个设计决策的解释product_name用text类型 ik 分词器这是支持模糊搜索的关键同时加了.keyword子字段方便做精确匹配和排序。category_id用keyword而不是long因为类目 ID 只做筛选不做范围计算。status用byte节省存储空间。中文搜索方面我们后来还加了拼音过滤器用户输入 huawei 也能搜到 华为这个看业务需求不是必须。这里最大的坑是一旦索引创建完成字段类型就不能修改。如果你一开始把price定义成了keyword后面想改成double做范围筛选只能新建索引再 reindex数据量大时这个过程很痛苦。所以一定要在最初设计时就考虑清楚查询需求。4.6 存量数据回填上线前的一此大考binlog 同步只能保证增量数据存量的一千多万条历史数据必须单独回填。我们用的方案是写一个独立的回填程序按主键 ID 分片每片 10000 条分批从 MySQL 查询。每批数据组装成 ES 的bulk请求写入bulk一次提交 5000 条。回填完成后再等五分钟让增量管道追平最新数据。回填过程中有个很典型的坑直接SELECT *全量查 MySQL 会把主库拖垮。我们当时是先在从库上建了只读账号回填程序接从库同时在从库上加了一个二级索引(id, updated_at)回填速度从每秒 3000 条提升到了每秒 2 万条。回填完成之后用一条对比 SQL 验证两边数据量是否一致SELECT COUNT(*) FROM products WHERE status 1;curl -X GET http://192.168.1.30:9200/products/_count?pretty数字对上了才算完成存量数据同步。这个环节别急着上线验证跑完整流程包括 DELETE 的同步。5. 一致性、延迟与运维文档里不写但一定会踩的坑流水线跑通只是开始稳定性才是硬仗。下面这些坑我们线上真实踩过每一个都花了不止一个通宵排查。5.1 秒级延迟怎么控制Kafka 消费能力和 bulk 参数调优binlog 同步链路天然会有秒级延迟正常情况下 1-2 秒可接受但高峰时段如果 Logstash 消费能力跟不上延迟可能积累到几分钟用户搜索立刻就能感知到搜不到刚创建的商品。排查流程是这样先看 Kafka 的消费延迟kafka-consumer-groups.sh --bootstrap-server localhost:9092 --group logstash --describe看LAG列。如果 LAG 持续上涨说明消费速度跟不上生产速度。我们当时的瓶颈在 Logstash 写入 ES 的bulk请求默认一次批量太小。调优后配置改成pipeline.batch.size: 2000 pipeline.batch.delay: 50加上 Elasticsearch output 插件里的bulk_max_size 5000 flush_interval 5延迟从高峰期 45 秒降到了 2 秒以内。另外注意一个容易被忽略的点Logstash 的 JVM 堆内存默认只有 1GB-Xms和-Xmx至少给到 4GB否则大批量时频繁 GC 也会拖慢消费。5.2 数据不一致的补偿机制binlog 丢了怎么办即使有 canal Kafka 这条可靠链路也不能完全排除异常。比如 Kafka 消费端偶发反序列化失败某一条消息被直接丢弃ES 就会漏掉一次更新。我们最终加了三层兜底定时比对任务每天凌晨跑一次 MySQL 和 ES 的 ID 集合比对用哈希分片的方式对比找出两边不一致的 ID再走一遍全量同步。更新操作幂等Logstash 写入用的是doc_as_upsert不管消息是否重复最终文档内容一致。canal 故障自动恢复canal 意外宕机重启后会自动从上次的位置继续消费 binlog不会丢失事件。前提是 Kafka 的 topic 保留时间要合理配置默认 7 天足够覆盖一晚上的排查时间。这三层兜底上线之后数据不一致率降到了十万分之一以下基本可以忽略。5.3 字段类型发布后的修改reindex 是必须掌握的技能ES 有一个很反直觉的设计mapping 一旦创建字段类型就不能改。我第一次不知道这个约束给price字段定义成了keyword结果上线后发现搜索接口要做价格范围过滤直接报错。解决方案是 reindex流程如下新建一个索引products_v2mapping 里把price改成double。用 reindex 接口迁移数据curl -X POST http://192.168.1.30:9200/_reindex -H Content-Type: application/json -d { source: { index: products }, dest: { index: products_v2 } }确认数据迁移完成修改索引别名指向新索引curl -X POST http://192.168.1.30:9200/_aliases -H Content-Type: application/json -d { actions: [ { remove: { index: products, alias: products_search } }, { add: { index: products_v2, alias: products_search } } ] }让 Logstash 写入的 index 从products改成products_v2重启 Logstash。reindex 本身不复杂但是涉及到索引切换的时间窗口里Logstash 还在往旧索引写数据会导致新索引缺数据。我们的处理方式是在 canal 消息里带上updated_at字段reindex 完之后再跑一次增量回填只选updated_at 切换时间的数据从 MySQL 重新同步。5.4 磁盘、连接数与分词器带来的隐性成本ES 集群的磁盘占用比大多数人预想的要大。同样是商品数据MySQL 里 5GBES 里可能膨胀到 20GB。原因是倒排索引、文档原始 JSON、.keyword子字段都会重复存储。我们当时给每个字段都加了.keyword子字段结果索引膨胀得很厉害。后来复盘发现很多字段根本不需要精确匹配删掉多余的.keyword索引体积直接降了 35%。连接数方面ES 默认http.max_content_length是 100MBbulk请求太大就会报content_length异常。当时 Logstash 批量调到 5000 后偶发这个报错要把 ES 的配置调大http.max_content_length: 200mb分词器这边ik_max_word虽然索引完整但生成的词项数量多索引体积和写入延迟都比ik_smart大。如果业务搜索场景只需要查得准不在意穷举所有词直接用ik_smart做索引分词也可以。5.5 一张表看清线上常见的排查方向最后把我这一年多的线上问题排查经验浓缩成一张表遇到问题先对着表快速定位故障现象排查方向常用命令 / 思路搜不到刚写入的数据Kafka 消费延迟kafka-consumer-groups.sh --describe看 LAGES 数据比 MySQL 少增量丢失或回填遗漏跑 ID 集合比对任务ES 写入报 connection 拒绝连接数或内存溢出看 ES 日志调http.max_content_length搜索响应突然变慢堆内存不足 / 分片过多GET _cat/indices?v看分片大小和数量搜索结果排序不对字段类型为 text 而非 keyword检查 mapping 里排序字段必须有keyword子字段中文搜索不准分词器没配 / 用了 standard改用 ik_max_word ik_smartMySQL 和 Elasticsearch 这套组合解决了我这边搜索性能的核心痛点但它的运维成本也确实比单 MySQL 高不少。我的一个很深刻的体会是方案能不能长久关键在于同步管道打得够不够扎实。binlog 订阅这步走对了后面就只是修修补补数据一致性这块就不用人肉扛了。最后再分享一个小技巧如果你在纠结到底要不要引入 ES建议先拿一周的真实查询日志分析一下看看慢查询集中在什么模式。如果有一半以上的慢查询都是LIKE %xxx%那别犹豫ES 就是你要的答案如果只是少量精确点查慢优化 MySQL 索引结构可能就够用了。