PHP后端开发技术学习 - 数据库
type: channel
link: https://www.aibasil.cn/category/数据库/
description: 数据库技术
items: docker-compose安装kafka
items: elasticsearch6.5.0设置密码并用php连接
items: MySQL使用FIND_IN_SET函数查询数据为空
items: PHP连接SQLserver报错：SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the followin
items: 使用MAX函数获取表中某个字段最大值导致MySQL索引失效CPU占用100%解决办法
items: 如何使用Elasticsearch做商品搜索（公司商城商品搜索重构篇）
items: centos6.10 wdcp面板安装pdo_sqlsrv扩展连接SQLserver
items: 阿里云rds数据库mysql版cpu占用100%排查问题过程
items: Redis持久化方式对比
items: MySQL根据某个字段值进行排序

docker-compose安装kafka
type: item
date: 2023-12-25T17:17:18+08:00
description: 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连接
type: item
date: 2023-08-16T11:14:00+08:00
description: 首先，您需要安装并配置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函数查询数据为空
type: item
date: 2023-07-19T16:26:51+08:00
description: 想要查询表中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
type: item
date: 2023-07-06T16:51:00+08:00
description: 确保已安装PHP pdo_sqlsrv扩展，以及安装Microsoft ODBC驱动，参考 Debian11安装ODBC

使用MAX函数获取表中某个字段最大值导致MySQL索引失效CPU占用100%解决办法
type: item
date: 2023-04-15T14:29:00+08:00
description: 使用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_t…

如何使用Elasticsearch做商品搜索（公司商城商品搜索重构篇）
type: item
date: 2023-03-22T15:58:00+08:00
description: 重构前商品搜索现状搜索不准确，搜出来跟关键词不相关的商品搜索无结果，输入关键词搜索不出结果搜索排序不准确商品发布、修改后不能即时同步到搜索引擎分词效果不好搜索效率较低搜索问题原因分析技术层面商品模块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" }, "br…

centos6.10 wdcp面板安装pdo_sqlsrv扩展连接SQLserver
type: item
date: 2022-10-08T14:36:00+08:00
description: 根据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%排查问题过程
type: item
date: 2022-04-21T23:53:00+08:00
description: 背景某天去到公司，用户反馈说系统响应很慢，甚至有些功能基本用不了，严重影响工作效率。测试同事说阿里云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…

Redis持久化方式对比
type: item
date: 2022-03-03T09:39:48+08:00
description: #RDBAOF持久化方式生成某一时刻的数据快照文件实时记录写入命令到文件数据完整性不完整，取决于备份周期相对完整，取决于文件刷盘方式文件大小压缩二进制写入，文件较小原始的操作命令，文件大宕机恢复时间快慢恢复优先级低高持久化代价高、消耗大量CPU和内存低，只占用磁盘IO资源使用场景数据备份、主从全量复制、对丢失数据不敏感的业务场景快速恢复数据对于丢失数据敏感的场景，例如涉及金钱交易相关的业务

MySQL根据某个字段值进行排序
type: item
date: 2020-04-22T13:58:00+08:00
description: 使用case when语句SELECT id,name FROM user ORDER BY CASE WEHN id = '1' TEHN 1 WHEN id = '2' THEN 2 ELSE 3 END ;