docker-compose安装kafka version: '3' services: zookeeper: image: wurstmeister/zookeeper container_name: zookeeper1 ports: - "2181:2181" environment: PATH: /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin JAVA_HOME: /usr/lib/jvm/java-7-openjdk-amd64 ZOOKEEPER_VERSION: 3.4.13 ZK_HOME: /opt/zookeeper-3.4.13 kafka: image: wurstmeister/kafka ports: - "8092:8092" volumes: - /www/docker/kafka/logs/kafka-logs:/kafka/kafka-logs - /etc/localtime:/etc/localtime environment: KAFKA_BROKER_ID: 0 KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181 KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://192.168.113.2:8092 KAFKA_LISTENERS: PLAINTEXT://0.0.0.0:8092 PATH: /usr/local/openjdk-11/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/opt/kafka/bin JAVA_HOME: /usr/local/openjdk-11 JAVA_VERSION: 11.0.15 KAFKA_VERSION: 2.8.1 SCALA_VERSION: 2.13 KAFKA_HOME: /opt/kafka KAFKA_LOG_DIRS: /kafka/kafka-logs LANG: C.UTF-8 container_name: kafka1 tty: true privileged: true stdin_open: true

elasticsearch6.5.0设置密码并用php连接 首先，您需要安装并配置Elasticsearch 6.5.0。确保您的Elasticsearch实例正在运行，并且您可以使用curl访问它。使用curl命令设置Elasticsearch的密码。打开命令行终端，运行以下命令：curl -XPOST -u elastic 'http://localhost:9200/_security/user/elastic/_password' -H 'Content-Type: application/json' -d '{"password" : "your_password"}'请将your_password替换为您要设置的实际密码。这将设置elastic用户的密码。在PHP脚本中连接Elasticsearch。使用以下PHP代码连接Elasticsearch： <?php require 'vendor/autoload.php'; // 如果您使用的是Composer，请引入自动加载的文件 $client = \Elasticsearch\ClientBuilder::create() ->setBasicAuthentication('elastic', 'your_password') ->build(); $params = [ 'index' => 'your_index', 'type' => 'your_type', 'id' => 'your_id', 'body' => ['your_field' => 'your_value'] ]; $response = $client->index($params); print_r($response); ?>确保将上面的代码your_password替换为您设置的实际密码，your_index，your_type和your_id替换为您要索引的实际数据。运行上述PHP脚本，它将连接到Elasticsearch实例并将数据索引到指定的索引和类型中。

MySQL使用FIND_IN_SET函数查询数据为空 想要查询表中user_id字段包含31556的数据，一开始使用以下语句查询不出结果SELECT * FROM `cor_diy_solution` WHERE FIND_IN_SET( '31556','user_id');后来发现user_id字段如果用单引号是不会识别成字段名的，需要用``括住才行SELECT * FROM `cor_diy_solution` WHERE FIND_IN_SET( '31556',`user_id`);这样查询就有数据了

PHP连接SQLserver报错：SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the followin 确保已安装PHP pdo_sqlsrv扩展，以及安装Microsoft ODBC驱动，参考 Debian11安装ODBC

使用MAX函数获取表中某个字段最大值导致MySQL索引失效CPU占用100%解决办法 使用MAX函数获取表中某个字段最大值的查询优化业务场景是获取表中fid的最大值做增量同步，使用以下语句查询，导致阿里云RDS云数据库CPU占用100%SELECT MAX(`fid`) AS tp_max FROM `hp_erp_barcode` WHERE `hp_erp_barcode`.`delete_time` IS NULL表结构/******************************************/ /* DatabaseName = xietong */ /* TableName = hp_erp_barcode */ /******************************************/ CREATE TABLE `hp_erp_barcode` ( `id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id', `fid` int(32) unsigned NOT NULL DEFAULT '0' COMMENT '条码主档 BD_BarCodeMainFile 实体主键', `material_id` int(32) unsigned NOT NULL DEFAULT '0' COMMENT '物料id', `material_number` varchar(64) DEFAULT '' COMMENT '物料编码', `material_name` varchar(128) DEFAULT '' COMMENT '物料名称', `barcode` varchar(64) DEFAULT '' COMMENT '条形码', `forbid_status` varchar(32) DEFAULT '' COMMENT '禁用状态', `remark` text COMMENT '备注', `stock_id` int(32) unsigned NOT NULL DEFAULT '0' COMMENT '仓库id', `stock_number` varchar(64) DEFAULT '' COMMENT '仓库编码', `stock_name` varchar(64) DEFAULT '' COMMENT '仓库名称', `bill_code` varchar(64) DEFAULT '' COMMENT '单据编号', `add_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间', `update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '修改时间', `delete_time` int(11) unsigned DEFAULT NULL COMMENT '删除时间', PRIMARY KEY (`id`), KEY `barcode` (`barcode`), KEY `bill_code` (`bill_code`), KEY `idx_fid` (`fid`) USING BTREE, KEY `idx_delete_time` (`delete_time`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=2769985 DEFAULT CHARSET=utf8mb4 COMMENT='ERP条码主档' ;从表结构可看到，fid与delete_time字段都有建立索引，用explain分析idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEhp_erp_barcode refidx_delete_timeidx_delete_time5const1165839100Using index condition表中有两百多万行数据，根据上面的结果，大致找到所需记录需要读取的行数为1165839，虽然有索引，但效果不理想，分析之后，发现可以通过order by fid desc limit 1的方式获取fid的最大值SELECT fid FROM `hp_erp_barcode` WHERE `delete_time` IS NULL ORDER BY `fid` DESC limit 1使用explain分析idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEhp_erp_barcode indexidx_delete_timeidx_fid4 250.00Using where找到所需记录需要读取的行数为2，大大提高了搜索的效率。

如何使用Elasticsearch做商品搜索（公司商城商品搜索重构篇） 重构前商品搜索现状搜索不准确，搜出来跟关键词不相关的商品搜索无结果，输入关键词搜索不出结果搜索排序不准确商品发布、修改后不能即时同步到搜索引擎分词效果不好搜索效率较低搜索问题原因分析技术层面商品模块ER图 重构前的数据表与es索引关系图重构前的搜索逻辑图 索引数据结构设计不合理。 从上面的图片可看出，系统有两种商品类型，分别是单品、整套，单品和整套都有sku规格，除此之外还有客户物料编码，与ERP物料表以及单品sku相关联，用于客户快速找到自己熟悉的物料，这些商品信息分四个索引存储，分别为商品信息cor_goods索引、单品sku cor_products索引、整套sku cor_pack索引、客户物料编码cor_user_material索引，搜索商品时需要对四个索引进行搜索，因此降低了搜索的效率、提高了编写搜索条件的复杂度,以及不能进行准确的排序搜索的逻辑不合理。 根据上面的搜索逻辑图，用户输入关键词点击搜索之后，首先在客户物料编码cor_user_material索引进行搜索，如果存在结果，则返回给用户，不再对其它的索引进行搜索，如果不存在结果，则在下一个索引搜索，以此类推，直到最后在商品信息cor_goods索引搜索后，不管是否有结果也返回给用户。这样就会出现搜索不准确的问题，例如用户想搜索以AB开头的商品编码，因为在第一个客户物料编码cor_user_material索引存在商品编码为AB的商品，所以直接返回了商品编码为AB的商品，不会返回存在其它索引中编码为ABC、ABCD、ABCDE的商品。再如用户想搜索编码为ABC的商品，由于分词的影响，在第一个客户物料编码索引就会命中编码为AB的商品，不会再继续在其它索引搜索编码为ABC的商品同步商品到elasticsearch的方式或方法不合理。 使用Linux的crontab定时任务每分钟查询一次需要同步的商品，商品信息变更后不会及时同步到elasticsearch，如果商品数量太多，更新的时间会更长需要对搜索结果进行客户屏蔽、品牌屏蔽等进行二次处理。 商品和指定品牌的商品会对某些客户进行屏蔽，屏蔽的逻辑没有做到搜索引擎，搜索出来之后再进行二次处理，由于需要到MySQL查询屏蔽关系，而且搜索条件较复杂，所以处理效率较低。对elasticsearch认识不够深，不熟悉，没有使用好。 例如用户只记得编码中的前面、中间或是后面的部分，由于没有使用prefix和wildcard搜索方式配合，所以用户搜不出想要的结果，还有没有更合理地使用boost导致排序的不准确等等业务层面客户对于同一款产品的叫法可能会跟公司不一样，例如公司叫B，客户叫C，当客户拿C去搜索的时候是搜索不出来B的，因此需要去维护好这些关系才能让客户更好地搜索到商品重构后达到的效果客户能够更好更准确地搜索到商品搜索结果排序准确搜索效率得到提高商品信息变更之后及时同步到搜索引擎重构过程重新设计索引结构数据表与es索引关系图 为了提高搜索的准确度与效率，以及解决排序的问题，将单品、成套、以及配置整套（这次迭代新增的商品）三种商品的信息，包括商品名称、商品编码、商品分类、sku信息、客户屏蔽关系、品牌屏蔽关系、客户物料编码、关键词与商品关联关系等，整合到一个商品文档，用商品类型字段区分，保存到同一个商品goods索引。 管理端修改商品信息时，将修改的商品ID投递到商品更新队列，实时同步到elasticsearch商品goods索引。 用户使用客户端从商品goods索引进行商品搜索 索引结构json：{ "settings": { "number_of_shards": 1, "number_of_replicas": 0, "analysis": { "analyzer": { "my_analyzer": { "type": "custom", "tokenizer": "ik_max_word", "char_filter": [ "html_strip" ], "filter": [ "my_stopwords", "lowercase" ] } }, "filter": { "my_stopwords": { "type": "stop", "stopwords": [ "the", "a", ".", "," ] } } } }, "mappings": { "new_goods_index_type": { "_source": { "enabled": true }, "properties": { "goods_id": { "type": "integer" }, "goods_type": { "type": "byte" }, "goods_pack": { "type": "byte" }, "brand_id": { "type": "short" }, "brand_name": { "type": "keyword" }, "category_id": { "type": "short" }, "category_name": { "type": "keyword" }, "goods_name_full": { "type": "keyword" }, "goods_name": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "goods_sn_full": { "type": "keyword" }, "goods_sn": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "goods_subhead": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "goods_desc": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "goods_desc_mobile": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "keywords": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "brand_is_restrict": { "type": "byte" }, "show_brand_customer_ids": { "type": "nested", "properties": { "customer_id": { "type": "integer" } } }, "is_restrict": { "type": "byte" }, "show_customer_ids": { "type": "nested", "properties": { "customer_id": { "type": "integer" } } }, "show_customer_rank_ids": { "type": "nested", "properties": { "customer_rank_id": { "type": "integer" } } }, "market_price": { "type": "double" }, "shop_price": { "type": "double" }, "measure_unit": { "type": "text", "index": false }, "comment_num": { "type": "integer" }, "goods_img": { "type": "text", "index": false }, "pack_images": { "type": "text", "index": false }, "goods_thumb": { "type": "text", "index": false }, "is_on_sale": { "type": "byte" }, "onsale_time": { "type": "integer" }, "is_alone_sale": { "type": "byte" }, "is_delete": { "type": "byte" }, "is_show_index": { "type": "byte" }, "is_best": { "type": "byte" }, "is_hot": { "type": "byte" }, "is_new": { "type": "byte" }, "sale_num": { "type": "integer" }, "collect_num": { "type": "integer" }, "click_count": { "type": "integer" }, "sort_order": { "type": "integer" }, "video": { "type": "text", "index": false }, "desc_video": { "type": "text", "index": false }, "is_integral": { "type": "integer" }, "need_integral": { "type": "integer" }, "is_integral_exchange": { "type": "integer" }, "is_integral_off_sale": { "type": "integer" }, "is_unite_promotion": { "type": "integer" }, "goods_sku": { "type": "nested", "properties": { "product_id": { "type": "integer" }, "product_sn_full": { "type": "keyword" }, "product_sn": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "is_restrict": { "type": "byte" }, "show_customer_ids": { "type": "nested", "properties": { "customer_id": { "type": "integer" } } }, "user_material": { "type": "nested", "properties": { "user_id": { "type": "integer" }, "user_material_sn": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "user_material_sn_full": { "type": "keyword" }, "user_material_name": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "user_material_name_full": { "type": "keyword" }, "hopo_material_sn": { "type": "keyword" } } }, "goods_attr_str": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" } } }, "pack_sku": { "type": "nested", "properties": { "pack_id": { "type": "integer" }, "pack_sn_full": { "type": "keyword" }, "pack_sn": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "is_restrict": { "type": "byte" }, "show_customer_ids": { "type": "nested", "properties": { "customer_id": { "type": "integer" } } }, "pack_attr_str": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" } } }, "keyword_goods_assoc": { "type": "nested", "properties": { "goods_keyword": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }, "goods_sort": { "type": "integer" } } } } } } }调整搜索逻辑商品搜索逻辑图从原先依次对四个索引搜索改为在一个索引搜索，将之前在代码层面处理的分页排序、客户屏蔽、品牌屏蔽转移到搜索引擎处理。搜索语句示例：{ "query":{ "bool":{ "must":[ { "term":{ "is_on_sale":1 } }, { "term":{ "is_alone_sale":1 } }, { "term":{ "is_delete":0 } }, { "terms":{ "brand_id":[ 1, 4, 16, 19, 20 ] } }, { "bool":{ "should":[ { "term":{ "goods_name_full":{ "value":"PH", "boost":3 } } }, { "match":{ "goods_name":{ "query":"PH", "boost":2, "minimum_should_match":"100%" } } }, { "wildcard":{ "goods_name_full":{ "value":"*PH*", "boost":2 } } }, { "term":{ "goods_sn_full":{ "value":"PH", "boost":3 } } }, { "match":{ "goods_sn":{ "query":"PH", "boost":2, "minimum_should_match":"90%" } } }, { "wildcard":{ "goods_sn_full":{ "value":"*PH*", "boost":2 } } }, { "prefix":{ "goods_sn_full":{ "value":"PH", "boost":2 } } }, { "nested":{ "path":"goods_sku", "query":{ "term":{ "goods_sku.product_sn_full":{ "value":"PH", "boost":3 } } } } }, { "nested":{ "path":"goods_sku", "query":{ "match":{ "goods_sku.product_sn":{ "query":"PH", "boost":2, "minimum_should_match":"100%" } } } } }, { "nested":{ "path":"goods_sku", "query":{ "prefix":{ "goods_sku.product_sn_full":{ "value":"PH", "boost":2 } } } } }, { "nested":{ "path":"goods_sku", "query":{ "wildcard":{ "goods_sku.product_sn_full":{ "value":"*PH*", "boost":2 } } } } }, { "nested":{ "path":"goods_sku", "query":{ "match":{ "goods_sku.goods_attr_str":{ "query":"PH", "minimum_should_match":"100%" } } } } }, { "nested":{ "path":"pack_sku", "query":{ "term":{ "pack_sku.pack_sn_full":{ "value":"PH", "boost":3 } } } } }, { "nested":{ "path":"pack_sku", "query":{ "match":{ "pack_sku.pack_sn":{ "query":"PH", "boost":2, "minimum_should_match":"100%" } } } } }, { "nested":{ "path":"pack_sku", "query":{ "prefix":{ "pack_sku.pack_sn_full":{ "value":"PH", "boost":2 } } } } }, { "nested":{ "path":"pack_sku", "query":{ "wildcard":{ "pack_sku.pack_sn_full":{ "value":"*PH*", "boost":2 } } } } }, { "nested":{ "path":"pack_sku", "query":{ "match":{ "pack_sku.pack_attr_str":{ "query":"PH", "minimum_should_match":"100%" } } } } }, { "nested":{ "path":"keyword_goods_assoc", "query":{ "match":{ "keyword_goods_assoc.goods_keyword":{ "query":"PH", "minimum_should_match":"99%" } } } } } ] } }, { "bool":{ "filter":{ "bool":{ "must":[ { "term":{ "is_restrict":{ "value":0 } } }, { "term":{ "brand_is_restrict":{ "value":0 } } }, { "bool":{ "should":[ { "bool":{ "must_not":[ { "nested":{ "path":"goods_sku", "query":{ "exists":{ "field":"goods_sku.is_restrict" } } } } ] } }, { "nested":{ "path":"goods_sku", "query":{ "term":{ "goods_sku.is_restrict":0 } } } } ] } }, { "bool":{ "should":[ { "bool":{ "must_not":[ { "nested":{ "path":"pack_sku", "query":{ "exists":{ "field":"is_restrict" } } } } ] } }, { "nested":{ "path":"pack_sku", "query":{ "term":{ "is_restrict":0 } } } } ] } }, { "bool":{ "must_not":[ { "nested":{ "path":"show_customer_rank_ids", "query":{ "exists":{ "field":"customer_id" } } } } ] } } ] } } } } ] } }, "sort":[ { "_score":{ "order":"desc" } }, { "sale_num":{ "order":"desc" } }, { "sort_order":{ "order":"ASC" } }, { "is_new":{ "order":"desc" } } ], "_source":[ ], "highlight":{ "fields":{ "pack_sku.pack_sn_full":{ }, "goods_sku.product_sn_full":{ } } }, "from":40, "size":"20" }关键词与商品关联规则维护 通过关键词搜索监控表，筛选出命中率低的关键词，再以这个关键词创建关联规则，将这个关键词与单品、成套、配置整套进行关联，提高命中率重构遇到的问题与解决办法通过商品编码的前缀、中间部分、后缀搜索不到商品原因是prefix、wildcard搜索的字段是text类型，text类型会被分词再进行存储，因此搜索不到结果 解决办法是使用keyword类型字段进行prefix、wildcard搜索，keyword类型与text类型的区别可以参考这篇文章 elasticsearch text类型与keyword类型区别通过sku编码精确搜索商品出来后，跳转到详情默认选中不是该编码问题如果用户通过sku的完整编码精确搜索出来商品后，点击商品进入详情时需要默认选中这个sku，这就需要知道该次搜索是不是通过商品的sku编码精确搜索出来的 网上查阅资料后发现，可以使用搜索结果高亮的方法解决这个问题，具体方式是在搜索条件加上sku编码的高亮字段设置,如下"highlight":{ "fields":{ "pack_sku.pack_sn_full":{ }, "goods_sku.product_sn_full":{ } } }由于sku编码是唯一的，如果通过sku编码命中之后，搜索结果只会存在一个商品信息，highlight字段对应的sku编码字段的值也只有一个元素，以下是通过PH903.00.069sku编码搜索出来的结果 { "msg":"搜索成功", "error_code":0, "content":{ "lists":{ "total":1, "current_page":"1", "per_page":"20", "last_page":1, "data":[ { "goods_id":3490, "goods_type":1, "goods_pack":0, "brand_id":1, "brand_name":"HOPO", "category_id":405, "category_name":"推拉门（方轴）", "goods_name_full":"PH903-执手平开门窗推拉门窗方轴执手", "goods_name":"PH903-执手平开门窗推拉门窗方轴执手", "goods_sn_full":"PH903", "goods_sn":"PH903", "goods_subhead":"执手转动时带机械声增强手感；执手美观大方；执手、方轴分离出货，方便用户更换", "goods_desc":"商品描述", "goods_desc_mobile":"", "keywords":[ "方钢执手、七字执手" ], "brand_is_restrict":0, "show_brand_customer_ids":[ ], "is_restrict":0, "show_customer_ids":[ ], "show_customer_rank_ids":[ ], "market_price":"0.00", "shop_price":"0.00", "measure_unit":"个", "comment_num":0, "goods_img":"images\/202110\/goods_img\/286_thumb_P_16345880204861.jpg", "pack_images":"", "goods_thumb":"images\/202110\/goods_img\/286_thumb_P_16345880204861.jpg", "is_on_sale":1, "onsale_time":0, "is_alone_sale":1, "is_delete":0, "is_show_index":1, "is_best":0, "is_hot":0, "is_new":0, "sale_num":2097290, "collect_num":13, "click_count":14932, "sort_order":100, "video":"", "desc_video":"", "is_integral":0, "need_integral":0, "is_integral_exchange":0, "is_integral_off_sale":0, "is_unite_promotion":0, "goods_sku":[ { "product_id":1608, "product_sn":"PH903.00.012", "product_sn_full":"PH903.00.012", "is_restrict":0, "user_material":[ ], "show_customer_ids":[ ], "goods_attr_str":"喷银|无轴" }, { "product_id":1609, "product_sn":"PH903.00.043", "product_sn_full":"PH903.00.043", "is_restrict":1, "user_material":[ ], "show_customer_ids":[ { "customer_id":"1048" } ], "goods_attr_str":"喷古铜|无轴" }, { "product_id":1770, "product_sn":"PH903.00.044", "product_sn_full":"PH903.00.044", "is_restrict":0, "user_material":[ ], "show_customer_ids":[ ], "goods_attr_str":"无轴|喷深古铜" }, { "product_id":3235, "product_sn":"PH903.00.069H5", "product_sn_full":"PH903.00.069H5", "is_restrict":1, "user_material":[ ], "show_customer_ids":[ { "customer_id":"1048" }, { "customer_id":"272" }, { "customer_id":"4615" } ], "goods_attr_str":"无轴|亚黑" }, { "product_id":4129, "product_sn":"PH903.00.011H1", "product_sn_full":"PH903.00.011H1", "is_restrict":1, "user_material":[ ], "show_customer_ids":[ { "customer_id":"763" }, { "customer_id":"3076" }, { "customer_id":"468" } ], "goods_attr_str":"无轴|氧银" }, { "product_id":4130, "product_sn":"PH903.00.045H5", "product_sn_full":"PH903.00.045H5", "is_restrict":1, "user_material":[ ], "show_customer_ids":[ { "customer_id":"763" }, { "customer_id":"1350" }, { "customer_id":"453" }, { "customer_id":"387" }, { "customer_id":"21" }, { "customer_id":"1048" }, { "customer_id":"2142" }, { "customer_id":"136" }, { "customer_id":"1967" }, { "customer_id":"443" } ], "goods_attr_str":"无轴|新氧化深古铜" } ], "pack_sku":[ ], "keyword_goods_assoc":[ ], "highlight":{ "goods_sku.product_sn_full":[ "<em>PH903.00.069H5<\/em>" ] }, "sku_id":3235, "price_format":{ "number":1, "price":"32.77", "price_format":"￥32.77" } } ] } }, "scope":null, "log_id":"c715126e1cfce6dff48fad51ff7d5634" }如果命中的关键词只有一个，而且是当前商品sku的其中一个，说明这个商品就是通过sku编码精确搜索的，可以通过以下代码查到此次命中的sku protected function getHitSku($goods) { if (isset($goods['highlight']['goods_sku.product_sn_full'])){ $productSnArr = $goods['highlight']['goods_sku.product_sn_full']; $skuArr = array_column($goods['goods_sku'], 'product_id', 'product_sn'); } if (isset($goods['highlight']['pack_sku.pack_sn_full'])){ $productSnArr = $goods['highlight']['pack_sku.pack_sn_full']; $skuArr = array_column($goods['pack_sku'], 'pack_id', 'pack_sn'); } if (isset($productSnArr)&& isset($skuArr) && is_array($productSnArr) && count($productSnArr) == 1 && !empty($productSnArr[0])){ $keywords = $this->params['keywords']; $keywordsEm = "<em>{$keywords}</em>"; if ($keywordsEm == $productSnArr[0]){ return $skuArr[$keywords] ?? 0; } } return 0; }通过规格搜索不准的问题规格字段goods_attr_str的类型是text，而且设置了保存与搜索的分词器"goods_attr_str": { "type": "text", "analyzer": "my_analyzer", "search_analyzer": "my_analyzer" }从索引结构可看到用的是ik_max_word分词器 "settings": { "number_of_shards": 1, "number_of_replicas": 0, "analysis": { "analyzer": { "my_analyzer": { "type": "custom", "tokenizer": "ik_max_word", "char_filter": [ "html_strip" ], "filter": [ "my_stopwords", "lowercase" ] } }, "filter": { "my_stopwords": { "type": "stop", "stopwords": [ "the", "a", ".", "," ] } } } }规格保存的方式是字符串，使用|分割 "goods_attr_str":"喷银|无轴"由于使用的是match_phrase查询，查询前关键词会被分词，goods_attr_str保存时也会进行分词后再保存，因此当用户选择多个规格筛选时会出现搜索不准确 解决办法是新增一个goods_attr_str_full字段，类型为keyword来保存商品规格，然后采用wildcard查询，示例如下 { "query": { "bool": { "must": [ { "wildcard": { "goods_attr_full": { "value": "*黑色*" } } }, { "wildcard": { "goods_attr_full": { "value": "*内开*" } } } ] } } }搜索大小写问题商品名称和编码用的是大写的字母，创建索引时使用了keyword类型来保存，分别goods_name_full和goods_sn_full字段，对商品名称和编码进行搜索时，用户输入了小写字母的关键字，例如ph312，搜索不出编码为PH312.00.1T或名称以PH312.00.1T开头的商品。解决办法：创建normalizer，goods_name_full和goods_sn_full字段设置normalizer{ "settings":{ "number_of_shards":1, "number_of_replicas":0, "analysis":{ "analyzer":{ "my_analyzer":{ "type":"custom", "tokenizer":"ik_max_word", "char_filter":[ "html_strip" ], "filter":[ "my_stopwords", "lowercase" ] }, "normalizer":{ "case_insensitive":{ "type":"custom", "filter":"lowercase" } } }, "filter":{ "my_stopwords":{ "type":"stop", "stopwords":[ "the", "a", ".", "," ] } } } }, "mappings":{ "new_goods_index_type":{ "_source":{ "enabled":true }, "properties":{ "goods_name_full":{ "type":"keyword", "normalizer":"case_insensitive" }, "goods_sn_full":{ "type":"keyword", "normalizer":"case_insensitive" } } } } }感想通过这一次重构，对elasticsearch有了更深入的理解，提高了使用elasticsearch的能力，在这个过程当中也遇到了各种各样的问题,大部分是自己对elasticsearch不熟悉导致的，还需继续加强对elasticsearch基础知识的学习

centos6.10 wdcp面板安装pdo_sqlsrv扩展连接SQLserver 根据php版本到 PECL网站 下载对应的扩展cd /usr/local/src wget https://pecl.php.net/get/pdo_sqlsrv-5.10.1.tgz解压tar -zxvf pdo_sqlsrv-5.6.1.tgz进入解压后的目录执行phpize，注：需要使用对应php版本的phpize/www/wdlinux/phps/71/bin/phpize执行configure，注：指定对应版本./configure --with-php-config=/www/wdlinux/phps/71/bin/php-config编译make && make install报错解决办法 问题1unrecognized command line option “-std=c++11”解决办法：gcc版本太低，通过 gcc 下载新版本编译安装 问题2fatal error: sql.h: No such file or directory解决办法：需要安装unixODBC-devel依赖yum install unixODBC-devel修改php.ini配置文件开启扩展/www/wdlinux/phps/71/bin/php --ini ## 查看配置文件未知 vim /www/wdlinux/phps/71/etc/php.ini ## 添加如下配置 [pdo_sqlsrv] extension_dir =/www/wdlinux/phps/71/lib/php/extensions/no-debug-non-zts-20160303 extension=pdo_sqlsrv.so重启PHP-FPM 找到master进程ps aux | grep "php-fpm: master"root 8282 0.0 0.0 251784 7396 ? Ss Aug19 4:27 php-fpm: master process (/www/wdlinux/phps/71/etc/php-fpm.conf)执行重启命令kill -USR2 8282安装扩展后仍然报错以及解决办法 1.ODBC驱动问题This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712解决办法：安装ODBC驱动，因为centos版本是6.10很老，所以使用yum有很多依赖问题，使用rpm包安装就没有问题， ODBC Driver下载地址wget https://packages.microsoft.com/rhel/6/prod/msodbcsql17-17.0.1.1-1.x86_64.rpm rpm -iv msodbcsql17-17.0.1.1-1.x86_64.rpm

阿里云rds数据库mysql版cpu占用100%排查问题过程 背景某天去到公司，用户反馈说系统响应很慢，甚至有些功能基本用不了，严重影响工作效率。测试同事说阿里云RDS数据库的CPU占用了100%，我打开阿里云dms的监控页面，确实如此，接着开始排查问题。问题排查过程因为前段时间遇到过代码死循环查询数据库导致了CPU占用100%的问题，所以一开始就往这个方向排查。首先通过阿里云的dms控制台执行show processlist命令查询数据库正在执行的线程如下 发现大量状态为sending data的线程，通过查询资料，这个状态的意思是读取和过滤数据，如果一个表数据量很大且没有索引的话，就容易出现sending data状态，官方解释是，线程正在读取和处理 SELECT 的行记录，发送给客户端，由于在这个状态下的操作会执行大量的磁盘访问（读），因此它通常是查询生命周期最长的状态。但具体为什么会占用大量CPU，还有待学习了解。之前的死循环会出现在一些同步erp数据到业务系统的定时任务中，因为需要同步大量的数据而使用了循环。使用上面的sql语句（info字段）搜索代码，发现只有一个定时任务的方法使用了这个语句，因此接着登录服务器查询这个定时任务，但是没有找到相应的定时任务，后来了解到原来一直没有配置，但是同事在前一天手动执行过这个定时任务，通过如下监控信息 发现从前一天下午5点09分开始数据库的CPU就开始飙升到100%了。通过ps命令找不到相关的定时任务进程正在执行，但为什么那些语句一直存在呢？然后在运维同事的帮助下，使用lsof和ll /proc/pid命令以及上面show processlist结果的端口查询到发起那个语句请求的进程是php-fpm，工作目录是tp框架的public目录，印象中tp框架的php think命令运行的模式是cli，因此导致sending data状态有可能不是定时任务导致的。因为当时没有及时排查出原因，所以采取了同事的建议将服务器重启。 重启后数据库的CPU确实降下来了，但是过了一会又升上去了。确定了不是由于定时任务导致的，接着继续排查代码中调用这个定时任务方法的地方，后来确实发现还有一个接口调用了这个方法，通过查询nginx的access.log日志，发现当时一直有IP频繁地请求着这个接口，初步认定是由于这个接口调用了这个方法导致的问题。接着和同事一起去查看这个方法的代码，但是却没有发现这个方法的问题，逻辑上产生死循环的逻辑也没有被发现。在测试环境模拟复现这个问题，发现在死循环中获取一个数据量为三万的表的同一条数据时（查询条件有加索引并命中）并不会导致CPU飙升，但是没有索引或没有命中索引的情况下，CPU就会一直飙升。 从上面排查的过程中，没有发现死循环，考虑到有些单据数据行比较多，所以模拟了短时间内循环查询并修改大量不同单据的情景，在命中索引的情况下，CPU占用不高，但是没有命中索引的情况下，CPU占用升到百分之五十多。接着看了生产环境相关的表，数据量大概是十万行，而且用于查询的entry_id字段并没有设置索引，因此同步数据时，如果数据量较大，循环插入或者更新数据时会导致数据库CPU占用持续升高。通过下面的监控图 119.23.123.173 - - [21/Apr/2022:11:10:03 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194500 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.176 - - [21/Apr/2022:11:10:07 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194249 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.174 - - [21/Apr/2022:11:10:09 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194045 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.179 - - [21/Apr/2022:11:10:09 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194045 HTTP/1.1" 201 1003 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.174 - - [21/Apr/2022:11:10:14 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194552 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.167 - - [21/Apr/2022:11:10:14 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194552 HTTP/1.1" 201 1892 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.164 - - [21/Apr/2022:11:10:17 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194613 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.154 - - [21/Apr/2022:11:10:26 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194548 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.169 - - [21/Apr/2022:11:10:31 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194229 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 47.112.84.217 - - [21/Apr/2022:11:10:31 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194249 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.165 - - [21/Apr/2022:11:10:32 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194264 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.178 - - [21/Apr/2022:11:10:35 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194592 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.170 - - [21/Apr/2022:11:10:39 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194500 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.159 - - [21/Apr/2022:11:10:41 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194580 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.170 - - [21/Apr/2022:11:10:41 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194580 HTTP/1.1" 201 1125 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.154 - - [21/Apr/2022:11:10:46 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194576 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.170 - - [21/Apr/2022:11:10:46 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194576 HTTP/1.1" 201 1296 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.154 - - [21/Apr/2022:11:10:51 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194536 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.171 - - [21/Apr/2022:11:10:53 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194613 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 47.112.84.213 - - [21/Apr/2022:11:10:56 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194264 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.163 - - [21/Apr/2022:11:10:57 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194524 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.172 - - [21/Apr/2022:11:11:02 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194548 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 47.112.84.216 - - [21/Apr/2022:11:11:02 +0800] "GET /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194500 HTTP/1.1" 499 0 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400" 119.23.123.166 - - [21/Apr/2022:11:11:06 +0800] "OPTIONS /abc_admin/v2/sourceOrderPhoto/getODODetail?inquire_type=2&express_no=10111194508 HTTP/1.1" 200 5 "https://cadmin.hopo.com.cn/" "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.120 Safari/537.36 Core/1.77.103.400 QQBrowser/10.9.4624.400"结合同事在前一天手动执行了定时任务并且是全量获取数据以及nginx的access.log日志的情况，从前一天执行了定时任务开始一直到第二天11点08分的CPU都是占满的状态，然后重启了服务器，CPU降了下来，接着因为相关接口被频繁调用，所以又导致CPU重新占满。问题原因数据量大的表且用于查询的字段没有设置索引，导致查询效率低。执行定时任务时没有传入增量获取的参数，采用了全量获取数据的方式进行同步数据，数据量较大，一直占用着数据库资源。解决办法与预防措施需要给数据量较大的表频繁查询的字段添加索引同步大量数据时，采用增量的方式，如果是需要重新获取全量数据，则尽量在晚上执行，且要考虑同步的时长是否会影响白天系统的使用。优化同步数据的方法，尽量避免在循环中调用数据库，造成数据库资源占用过高，影响系统性能。