商品搜索

配置索引 Mapping

  1. PUT goods
  2. {
  3.   "mappings": {
  4.     "dynamic": false,
  5.     "properties": {
  6.       "goods_id": {
  7.         "type": "integer"
  8.       },
  9.       "store_id": {
  10.         "type": "integer"
  11.       },
  12.       "goods_name": {
  13.         "type" : "text",
  14.         "analyzer" : "ik_max_word",
  15.         "fields": {
  16.           "word": {
  17.             "type": "text",
  18.             "analyzer": "standard"
  19.           }
  20.         }
  21.       },
  22.       "cate_id": {
  23.         "type": "integer"
  24.       },
  25.       "brand_id": {
  26.         "type": "integer"
  27.       },
  28.       "closed": {
  29.         "type": "byte"
  30.       },
  31.       "is_deleted": {
  32.         "type": "byte"
  33.       },
  34.       "if_show_b2b": {
  35.         "type": "byte"
  36.       },
  37.       "wholesale_price": {
  38.         "type": "double"
  39.       },
  40.       "sale_region": {
  41.         "type": "integer"
  42.       },
  44.       "recommend_sort": {
  45.         "type": "integer"
  46.       },
  47.       "last_update": {
  48.         "type": "integer"
  49.       },
  50.       "cate_name": {
  51.         "type" : "text",
  52.         "analyzer" : "ik_max_word",
  53.         "fields": {
  54.           "word": {
  55.             "type": "text",
  56.             "analyzer": "standard"
  57.           }
  58.         }
  59.       },
  60.       "wholesale_sales": {
  61.         "type": "integer"
  62.       }
  63.     }
  64.   },
  65.   "settings": {
  66.     "analysis": {
  67.       "analyzer": {
  68.         "ik_max_word_html_strip": {
  69.           "tokenizer": "ik_max_word",
  70.           "char_filter": ["html_strip"]
  71.         }
  72.       }
  73.     }
  74.   }
  75. }

Logstash 配置同步 Mysql 数据

为了方便管理，建议每个 Index 索引设置一个配置文件夹

  1. # 创建索引goods配置目录
  2. mkdir /logstash/config/goods
  3. # 创建JDBC同步配置文件
  4. touch /logstash/config/goods/jdbc.conf
  5. # 创建JDBC同步配置SQL文件
  6. touch /logstash/config/goods/jdbc.sql
  1. input {
  2.     jdbc {
  3.       # mysql 数据库链接,db_fengche为数据库名 ?tinyInt1isBit=false 禁止整型转换为bool
  4.       jdbc_connection_string => "jdbc:mysql://192.168.9.201/db_fengche?tinyInt1isBit=false"
  5.       # 用户名和密码
  6.       jdbc_user => "db_fengche"
  7.       jdbc_password => "HbDZF63D5865BjjE"
  9.       # 驱动
  10.       jdbc_driver_library => "/logstash/logstash-core/lib/jars/mysql-connector-java-8.0.28.jar"
  11.       # 驱动类名
  12.       jdbc_driver_class => "com.mysql.jdbc.Driver"
  13.       jdbc_paging_enabled => "true"
  14.       jdbc_page_size => "50000"
  16.       # 启动追踪，如果为true，则需要指定 tracking_colum
  17.       use_column_value => true
  18.       # 指定追踪的字段
  19.       tracking_column => "last_update"
  20.       # 追踪字段的类型，目前只有数字（numeric）和时间类型（timestamp），默认是 numeric
  21.       tracking_column_type => "numeric"
  22.       # 记录最后一次运行的结果
  23.       record_last_run => true
  24.       # 上次运行结果的保存位置
  25.       last_run_metadata_path => "/logstash/config/goods/jdbc-position.txt"
  27.         # 执行的sql 文件路径+名称
  28.       statement_filepath => "/logstash/config/goods/jdbc.sql"
  29.       # 设置监听间隔  各字段含义（由左至右）分、时、天、月、年，全部为*默认含义为每分钟都更新
  30.       schedule => "* * * * *"
  31.     }
  32. }
  34. output {
  35.     elasticsearch {
  36.         # ES的IP地址及端口
  37.         hosts => ["192.168.9.220:9200"]
  38.         # 索引名称
  39.         index => "goods"
  40.         document_type => "_doc"
  41.         # 自增ID 需要关联的数据库中有有一个id字段，对应索引的id号
  42.         document_id => "%{goods_id}"
  43.     }
  44.     stdout {
  45.         # JSON格式输出
  46.         codec => json_lines
  47.     }
  48. }
  1. SELECT 
  2.   g.goods_id,
  3.   g.store_id,
  4.   g.goods_name,
  5.   g.cate_id,
  6.   g.brand_id,
  7.   g.closed,
  8.   g.is_deleted,
  9.   g.if_show_b2b,
  10.   g.wholesale_price,
  11.   g.sale_region,
  12.   g.recommend_sort,
  13.   g.last_update,
  14.   gc.cate_name,
  15.   st.wholesale_sales
  16. FROM
  17.   fc_goods AS g
  18.   INNER JOIN fc_gcategory AS gc
  19.     ON g.cate_id = gc.cate_id
  20.   LEFT JOIN fc_goods_statistics AS st
  21.     ON st.goods_id = g.goods_id
  22. WHERE g.last_update > :sql_last_value
  1. /logstash/bin/logstash -f /logstash/config/goods/jdbc.conf

PHP 项目开发

安装 Elasticsearch 插件

  1. composer require elasticsearch/elasticsearch "elasticsearch/elasticsearch":"7.1.0"

控制器测试代码

基于 thinkphp6.0 框架

  1. <?php
  2. namespace app\controller;
  4. use app\BaseController;
  5. use think\facade\Request;
  6. use app\logic\ElasticsearchLogic;
  8. class Elasticsearch extends BaseController
  9. {
  10.     public function get_goods_list()
  11.     {
  12.         $keyword = '车灯';
  13.         $where = array(
  14.             'closed'             => 0,
  15.             'keyword'            => $keyword,
  16.             'is_deleted'         => 0,
  17.             'if_show_b2b'        => 1,
  18.             'cate_id'            => [],
  19.             'brand_id'           => [],
  20.             'goods_id'           => [],
  21.             'store_id'           => []
  22.         );
  23.         $page_no = Request::post('page_no/d', 1);
  24.         $page_size = Request::post('page_size/d', 10);
  26.         $elasticsearchLogic = new ElasticsearchLogic();
  27.         $elasticsearchLogic->goodsSearch($where, build_limit($page_no, $page_size));
  28.     }
  29. }

逻辑代码

根据关键字搜索商品名称进行关键字相关度搜索，结合商品上下架等匹配和排序，完成搜索。

  1. <?php
  2. namespace app\logic;
  4. use \Elasticsearch\ClientBuilder;
  6. class ElasticsearchLogic
  7. {
  8.   public $client;
  10.   public function __construct()
  11.   {
  12.     $params = array(
  13.       '192.168.9.220:9200'
  14.     );
  15.     $this->client = ClientBuilder::create()->setHosts($params)->build();
  16.   }
  18.   public function goodsSearch(array $where = [], array $limit = [])
  19.   {
  20.     // 索引名称
  21.     $params['index'] = 'goods';
  22.     // 文档类型
  23.     $params['type'] = '_doc';
  24.     // 返回字段
  25.     $params['body']['_source'] = ["closed", "goods_name", "cate_name", "is_deleted", "if_show_b2b", "cate_id", "brand_id", "goods_id", "store_id", "wholesale_sales", "last_update"];
  26.     // 请求体
  27.     $params['body']['query'] = [];
  28.     // 关键字相关度匹配搜索
  29.     if ($where['keyword']) {
  30.       // should：选择性匹配，其中一项匹配即可，贡献算分
  31.       $should = [];
  32.       $keyword = $where['keyword'];
  33.       $should[] = $this->bulid_match('goods_name', $keyword);
  34.       $should[] = $this->bulid_match('goods_name.word', $keyword, 0.2);
  35.       $params['body']['query']['bool']['should'] = $should;
  36.     }
  37.     // filter：必须匹配，但是不贡献算分，高速缓存
  38.     $filter = [];
  39.     if (isset($where['closed'])) {
  40.       $filter[] = $this->bulid_match('closed', $where['closed']);
  41.     }
  42.     if (isset($where['is_deleted'])) {
  43.       $filter[] = $this->bulid_match('is_deleted', $where['is_deleted']);
  44.     }
  45.     if (isset($where['if_show_b2b'])) {
  46.       $filter[] = $this->bulid_match('if_show_b2b', $where['if_show_b2b']);
  47.     }
  48.     if (isset($where['cate_id']) && $where['cate_id']) {
  49.       $filter[] = $this->bulid_terms('cate_id', $where['cate_id']);
  50.     }
  51.     if (isset($where['brand_id']) && $where['brand_id']) {
  52.       $filter[] = $this->bulid_terms('brand_id', $where['brand_id']);
  53.     }
  54.     if (isset($where['goods_id']) && $where['goods_id']) {
  55.       $filter[] = $this->bulid_terms('goods_id', $where['goods_id']);
  56.     }
  57.     if (isset($where['store_id']) && $where['store_id']) {
  58.       $filter[] = $this->bulid_terms('store_id', $where['store_id']);
  59.     }
  60.     $params['body']['query']['bool']['filter'] = $filter;
  61.     $params['body']['sort'] = [["_score"=> ['order'=> 'desc']], ['wholesale_sales'=> ['order'=> 'desc']], ['last_update'=> ['order'=> 'desc']]];
  62.     $response = $this->client->search($params);
  63.   }
  65.   protected function bulid_match($queryName, $queryValue, $boost = 1)
  66.   {
  67.     $type = 'match';
  68.     if (is_array($queryValue)) {
  69.       $type = 'terms';
  70.     }
  71.     if ($boost === 1) {
  72.       return [$type => [$queryName => $queryValue]];
  73.     } else {
  74.       return [$type => [$queryName => ["query" => $queryValue, 'boost' => 0.2]]];
  75.     }
  76.   }
  77. }