---
url: /notes/面试/真实面经/上海艾融软件/index.md
---
浦发银行APP贷款项目

## quartz定时任务具体用来做什么的

主要用来生成报表或固定上报数据接口之类

quartz特点是服务分布式部署后，同一个定时任务只会执行一次，使用行锁对单个任务加锁，防止相同的定时任务多次执行

## sql调优方法

尽量避免select \*，明确指定查询字段

避免全表扫描，合理设计索引，对于经常查询的字段

联表查询，使用left join左边最好使用数量量小的，子查询in适合数据量小的，因为会先进行子查询再比对外面的表，exists适合子查询数据量大的，因为会在外层循环比对里面的表

尽量避免使用函数，比如日期函数

联合索引满足，最左匹配

左like无法利用索引

or条件两侧字段未全部索引，可能导致全表扫描

## 数据怎么推送到es

使用logstash同步，定义配置文件input，filter，output

```properties
# Sample Logstash configuration for creating a simple
# Beats -> Logstash -> Elasticsearch pipeline.

input {
  jdbc {
    jdbc_driver_library => "C:\Users\Administrator\AppData\Roaming\JetBrains\DataGrip2021.1\jdbc-drivers\MySQL ConnectorJ\8.0.25\mysql\mysql-connector-java\8.0.25\mysql-connector-java-8.0.25.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://localhost:3306/xiaofei_site_search"
    jdbc_user => "root"
    jdbc_password => "root"
    use_column_value => true
    tracking_column => "updateTime"
    tracking_column_type => "timestamp"
    parameters => { "isDelete" => 1 }
    schedule => "*/5 * * * * *"
    jdbc_default_timezone => "Asia/Shanghai"
    # 记录上次执行位置
    last_run_metadata_path => "D:/tools/Elastic_Search/logstash-7.17.12/data/plugins/last_run_metadata_file"

    statement => "
      SELECT 
        id,
        fileName,
        description,
        file_type as fileType,
        file_extension as fileExtension,
        file_content as textContent,
        description,
        upload_time as uploadTime,
        upload_user_id as uploadUserId,
        biz,
        is_public as isPublic,
        created_at as createdAt,
        updated_at as updatedAt,
        status as status
      FROM file where upload_time > :sql_last_value and upload_time < now() order by upload_time desc
    "
  }
}

filter{
  mutate {
    rename => {
      "file_name" => "fileName"
      "file_path" => "filePath"
      "file_type" => "filetype"
      "file_extension" => "fileExtension"
      "file_content" => "textContent"
      "upload_time" => "uploadTime"
      "upload_user_id" => "uploadUserId"
      "is_public" => "isPublic"
      "created_at" => "createdAt"
      "updated_at" => "updatedAt"
      "description" => "description"
      "status" => "status"
    }
  }
}

output {
  stdout { codec => rubydebug }
  elasticsearch {
        hosts => "127.0.0.1:9200"
        index => "file_v1"
        document_id => "%{id}"
    }
}

```

或者在业务代码里面手动同步

## 系统架构 （服务发现，负载均衡）

服务发现 nacos

负载均衡 nginx

## AOP切面应用

主要用于日志或其它比如多数据源注解

多数据源注解

```java
package com.xiaofei.springbootinit.annotation;

import com.xiaofei.springbootinit.config.datasource.DataSourceType;

import java.lang.annotation.*;

/**
 * @author tuaofei
 * @description 数据源切换注解
 * @date 2024/12/24
 */
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
    DataSourceType value() default DataSourceType.MASTER;
}

```

```java
@Slf4j
@Aspect
@Component
public class DataSourceAspect {

    @Pointcut("@annotation(com.xiaofei.springbootinit.annotation.DataSource)")
    public void apiLogPointcut() {
    }

    @Around("apiLogPointcut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        DataSource dataSource = signature.getMethod().getAnnotation(DataSource.class);
        if (dataSource != null) {
            DataSourceType dataSourceType = dataSource.value();
            log.info("切换数据源到: {}", dataSourceType);
            DataSourceContextHolder.setDataSource(dataSourceType);
        }

        try {
            return point.proceed();
        } finally {
            log.info("清除数据源配置");
            DataSourceContextHolder.clearDataSource();
        }
    }
}
```

请求响应日志 AOP

```java
/**
 * 请求响应日志 AOP
 *
 * @author <a href="http://xiaofei.site>计算机知识杂货铺</a>
 * @from 
 **/
@Aspect
@Component
@Slf4j
public class LogInterceptor {

    /**
     * 执行拦截
     */
    @Around("execution(* com.xiaofei.springbootinit.controller.*.*(..))")
    public Object doInterceptor(ProceedingJoinPoint point) throws Throwable {
        // 计时
        StopWatch stopWatch = new StopWatch();
        stopWatch.start();
        // 获取请求路径
        RequestAttributes requestAttributes = RequestContextHolder.currentRequestAttributes();
        HttpServletRequest httpServletRequest = ((ServletRequestAttributes) requestAttributes).getRequest();
        // 生成请求唯一 id
        String requestId = UUID.randomUUID().toString();
        String url = httpServletRequest.getRequestURI();
        // 获取请求参数
        Object[] args = point.getArgs();
        String reqParam = "[" + StringUtils.join(args, ", ") + "]";
        // 输出请求日志
        log.info("request start，id: {}, path: {}, ip: {}, params: {}", requestId, url,
                httpServletRequest.getRemoteHost(), reqParam);
        // 执行原方法
        Object result = point.proceed();
        // 输出响应日志
        stopWatch.stop();
        long totalTimeMillis = stopWatch.getTotalTimeMillis();
        log.info("request end, id: {}, cost: {}ms", requestId, totalTimeMillis);
        return result;
    }
}
```

## 怎么和用户进行需求沟通

明确需要解决的问题，用户对现有问题的反馈

了解需求背景业务

引导用户描述需求，遇到什么问题，使用场景，期望，重复确认需求

记录跟进