首页 > 数据库 >高效处理海量慢SQL日志文件：Java与JSQLParser去重方案详解

高效处理海量慢SQL日志文件：Java与JSQLParser去重方案详解

时间：2024-06-11 10:33:19浏览次数：22  
标签：info Java log SQL toString JSQLParser sql import jsqlparser

在大数据处理环境下，慢SQL日志优化是一个必要的步骤，尤其当日志文件达到数GB时，直接操作日志文件会带来诸多不便。本文将介绍如何通过Java和JSQLParser库来解析和去重慢SQL日志，以提高性能和可维护性。

背景

公司生产环境中，某些操作产生的SQL执行时间较长，会记录在慢SQL日志文件中。慢SQL日志文件包含了SQL的执行时间、用户信息、查询语句等内容。由于这些日志文件可能包含大量重复的SQL语句，逐条查看和处理既耗时又低效，因此有必要进行去重操作。

目标

本文旨在通过以下步骤实现慢SQL日志的去重：

  1. 读取日志文件内容，解析出注释和SQL语句。
  2. 解析SQL，定义SQL相同的标准。
  3. 实现对象存储解析出来的各个部分，重写equals和hashCode方法。
  4. 使用Set集合去重。
  5. 将去重后的结果写入文件。

工具和依赖

为了实现上述目标，我们将使用以下工具和依赖：

  • Java 8或以上版本
  • Maven
  • JSQLParser库

Maven依赖

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.9</version>
</dependency>

SQL日志示例

以下是一个慢SQL日志的示例，其中包含了时间、用户信息、数据库模式、查询时间、发送的字节数、时间戳以及SQL语句：

# Time: 2024-05-10T20:30:12.035337+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13708199
# Schema: laterdatabase  Last_errno: 0  Killed: 0
# Query_time: 5.000000  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 610953  Rows_affected: 0
# Bytes_sent: 56
SET timestamp=1715344212;
SELECT * FROM emp where name = '%三%';
# Time: 2024-05-10T11:28:27.315966+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13666423
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 3.290658  Lock_time: 0.000131  Rows_sent: 0  Rows_examined: 0  Rows_affected: 1
# Bytes_sent: 11
SET timestamp=1715311707;
insert into scott.emp        ( name, age)        values            ('张三',            38);
# Time: 2024-05-10T20:30:12.035337+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13708199
# Schema: laterdatabase  Last_errno: 0  Killed: 0
# Query_time: 5.000000  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 610953  Rows_affected: 0
# Bytes_sent: 56
SET timestamp=1715344212;
SELECT * FROM emp where name = '%三%';
# Time: 2024-05-14T16:18:03.879351+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13966826
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 3.120938  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 1  Rows_affected: 1
# Bytes_sent: 52
SET timestamp=1715674683;
UPDATE emp      SET `ename` = '张三',      `age` = 18 WHERE      `id` = 1045983421034180     ;
# Time: 2024-05-10T20:30:12.035337+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13708199
# Schema: laterdatabase  Last_errno: 0  Killed: 0
# Query_time: 5.000000  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 610953  Rows_affected: 0
# Bytes_sent: 56
SET timestamp=1715344212;
SELECT * FROM emp where name = '%三%';
# Time: 2024-05-06T01:58:36.959671+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13387119
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 6.161219  Lock_time: 0.000875  Rows_sent: 0  Rows_examined: 2137468  Rows_affected: 0
# Bytes_sent: 11
SET timestamp=1714931916;
delete  from emp where id = 1;
# Time: 2024-05-10T20:30:12.035337+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13708199
# Schema: laterdatabase  Last_errno: 0  Killed: 0
# Query_time: 5.000000  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 610953  Rows_affected: 0
# Bytes_sent: 56
SET timestamp=1715344212;
SELECT * FROM emp where name = '%三%';

去重后的效果

# slow.log	未知服务	4
# Time: 2024-05-10T20:30:12.035337+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13708199
# Schema: laterdatabase  Last_errno: 0  Killed: 0
# Query_time: 5.000000  Lock_time: 0.000122  Rows_sent: 1  Rows_examined: 610953  Rows_affected: 0
# Bytes_sent: 56
SET timestamp=1715344212;
SELECT * FROM emp where name = '%三%';

# slow.log	未知服务	1
# Time: 2024-05-14T16:18:03.879351+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13966826
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 3.120938  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 1  Rows_affected: 1
# Bytes_sent: 52
SET timestamp=1715674683;
UPDATE emp      SET `ename` = '张三',      `age` = 18 WHERE      `id` = 1045983421034180     ;

# slow.log	未知服务	1
# Time: 2024-05-06T01:58:36.959671+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13387119
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 6.161219  Lock_time: 0.000875  Rows_sent: 0  Rows_examined: 2137468  Rows_affected: 0
# Bytes_sent: 11
SET timestamp=1714931916;
delete  from emp where id = 1;

# slow.log	未知服务	1
# Time: 2024-05-10T11:28:27.315966+08:00
# User@Host: root[root] @  [192.168.110.110]  Id: 13666423
# Schema: scott  Last_errno: 0  Killed: 0
# Query_time: 3.290658  Lock_time: 0.000131  Rows_sent: 0  Rows_examined: 0  Rows_affected: 1
# Bytes_sent: 11
SET timestamp=1715311707;
insert into scott.emp        ( name, age)        values            ('张三',            38);

额外信息说明

  1. # slow.log 未知服务 4为去重时额外添加进去的行,slow.log代表sql所在原文件(如果慢sql日志文件被切分成好多个小文件时方便定位第一次出现的位置)
  2. 未知服务代表服务名称,如果项目是微服务架构,可以将ip替换为服务名,可读性更高,只需要关注自己负责的未付即可,也可以在输出去重后的文件时按照微服务名称命名,每个微服务的日志单独写入到一个文件.
  3. # slow.log 未知服务 4 最后的数字表示该sql出现的次数

去重与统计

import lombok.Data;
import lombok.experimental.Accessors;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.springframework.util.StringUtils;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Data
@Accessors(chain = true)
public class SlowQueryMetadata {
    private int count = 1;
    private String time;
    private String userAndHost;
    private String schema;
    private String queryTime;
    private String bytesSent;
    private String timestamp;
    private String sql;
    private String fileName;
    private String appName;

    /**
     * 和元数据保持一致格式
     */
    @Override
    public String toString() {
        return
                "# " + fileName + "\t" + appName + "\t" + count + "\n" +
                        time + "\n" +
                        userAndHost + "\n" +
                        schema + "\n" +
                        queryTime + "\n" +
                        bytesSent + "\n" +
                        timestamp + "\n" +
                        sql + "\n";
    }

    /**
     * 去重 不同参数的相同格式的sql
     * 计数
     */
    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        SlowQueryMetadata slowQueryMetadata = (SlowQueryMetadata) o;
        boolean equals = Objects.equals(uniqueString(sql), uniqueString(slowQueryMetadata.sql));
        if (equals) {
            slowQueryMetadata.setCount(slowQueryMetadata.getCount() + 1);
        }
        return equals;
    }

    @Override
    public int hashCode() {
        return Objects.hash(uniqueString(sql));
    }

    /**
     * 去除sql中的参数
     */
    private String uniqueString(String sql) {
        if (!StringUtils.hasText(sql)) {
            return "";
        }
        try {
            sql = sql.replace("\t", " ");
            sql = sql.replaceAll("\\s+", " ");
            // 替换掉注释部分 /*...*/
            sql = sql.replaceAll("/\\*.*?\\*/", "");
            // 相对比较耗时
            Statement statement = CCJSqlParserUtil.parse(new StringReader(sql));
            if (statement instanceof Insert) {
                return insertStatement((Insert) statement);
            }
            if (statement instanceof Update) {
                return updateStatement((Update) statement);
            }
            if (statement instanceof Delete) {
                return deleteStatement((Delete) statement);
            }
            if (statement instanceof Select) {
                return selectStatement(statement);
            }
        } catch (Exception e) {
            return sql;
        }
        return sql;
    }

    private String selectStatement(Statement statement) {
        List<String> tables = new ArrayList<>(new TablesNamesFinder().getTables(statement));
        Select select = (Select) statement;
        try {
            List<SelectItem<?>> selectItems = select.getPlainSelect().getSelectItems();
            return "select " + selectItems + " " + tables;
        } catch (Exception e) {
            return "select " + tables;
        }
    }

    /**
     * delete语句只要表名相同 删除条件列相同 即可认为是同一条sql
     */
    private String deleteStatement(Delete delete) {
        StringBuilder builder = new StringBuilder("delete ");
        builder.append(delete.getTable().getName().trim());
        builder.append(" where ");
        String[] ands = delete.getWhere().toString().toLowerCase().split("and");
        for (String and : ands) {
            builder.append(and.split("=")[0].trim()).append(" ");
        }
        return builder.toString();
    }

    /**
     * 更新语句只要表名 要更新的列名 where条件列名 相同就认为是同一条sql
     */
    private String updateStatement(Update update) {
        StringBuilder builder = new StringBuilder("update ");
        builder.append(update.getTable().getName().trim());
        builder.append(" column ");
        for (UpdateSet updateSet : update.getUpdateSets()) {
            builder.append(updateSet.getColumns().toString().trim());
        }
        builder.append(" where ");
        String[] ands = update.getWhere().toString().toLowerCase().split("and");
        for (String and : ands) {
            builder.append(and.split("=")[0].trim()).append(" ");
        }
        return builder.toString();
    }

    /**
     * 新增语句只要表相同列相同即可认为是相同sql
     */
    private String insertStatement(Insert statement) {
        return "insert " + statement.getTable().getName() + " " + statement.getColumns().toString();
    }

}

package com.study.jsqlparser;

import com.study.jsqlparser.dto.SlowQueryMetadata;
import com.study.jsqlparser.utils.FileUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

@Slf4j
public class SlowQueryAnalysis {
    @SneakyThrows
    public static void main(String[] args) {
//        statistics4Directory();
        statistics4File();
    }


    /**
     * 对单个慢sql文件文件去重
     */
    private static void statistics4File() throws IOException {
        String fileFullName = "slow.log";
        // 去重后的sql文件
        String deduplicationFileFullName = "deduplication.log";
        File slowFile = new File(fileFullName);
        List<String> list = FileUtils.readFileByLine(slowFile);
        log.info("从文件中读取到的总行数:{}", list.size());
        List<SlowQueryMetadata> slowQueryMetadataList = getSqlDTOList(list, slowFile.getName());
        log.info("提取出了:{}条sql", slowQueryMetadataList.size());
        HashSet<SlowQueryMetadata> set = new HashSet<>(slowQueryMetadataList);
        log.info("去重后的sql条数:{}", set.size());
        List<String> deduplication = set.stream().sorted(Comparator.comparingInt(SlowQueryMetadata::getCount).reversed()).map(SlowQueryMetadata::toString).collect(Collectors.toList());
        FileUtils.write2File(new File(deduplicationFileFullName), deduplication);

    }


    /**
     * 对文件夹下所有慢sql文件去重
     */
    private static void statistics4Directory() throws IOException {
        String directoryFullName = "E:\\xinao\\sql优化\\0516慢SQL已分割\\sql\\";
        // 去重后的sql文件
        String deduplicationFileFullName = "deduplication.sql";
        Set<SlowQueryMetadata> set = new HashSet<>();
        for (File file : new File(directoryFullName).listFiles()) {
            String fileName = file.getName();
            log.info(fileName);
            List<String> list = FileUtils.readFileByLine(file);
            log.info("从文件中读取到的总行数:{}", list.size());
            List<SlowQueryMetadata> slowQueryMetadataList = getSqlDTOList(list, fileName);
            log.info("提取出了:{}条sql", slowQueryMetadataList.size());
            set.addAll(slowQueryMetadataList);
        }
        log.info("去重后的sql条数:{}", set.size());
        List<String> deduplication = set.stream().sorted(Comparator.comparingInt(SlowQueryMetadata::getCount).reversed()).map(SlowQueryMetadata::toString).collect(Collectors.toList());
        FileUtils.write2File(new File(deduplicationFileFullName), deduplication);


    }

    private static List<SlowQueryMetadata> getSqlDTOList(List<String> list, String fileName) {
        List<SlowQueryMetadata> slowQueryMetadataList = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            String line = list.get(i);
            if (!StringUtils.hasText(line)) {
                continue;
            }
            if (line.trim().startsWith("# Time:")) {
                SlowQueryMetadata slowQueryMetadata = new SlowQueryMetadata();
                slowQueryMetadata.setFileName(fileName);
                slowQueryMetadataList.add(slowQueryMetadata);
                slowQueryMetadata.setTime(line);
                boolean multilineComment = false;
                while (i < list.size() - 1) {
                    i++;
                    line = list.get(i);
                    // 处理多行注释
                    if (line.trim().contains("/*")) {
                        multilineComment = true;
                        continue;
                    }
                    if (line.trim().contains("*/")) {
                        multilineComment = false;
                        continue;
                    }
                    if (multilineComment) {
                        continue;
                    }
                    if (line.trim().startsWith("# Time:")) {
                        i--;
                        break;
                    }
                    if (line.startsWith("# User@Host:")) {
                        slowQueryMetadata.setUserAndHost(line);
                        evaluationAppName(line, slowQueryMetadata);
                    }
                    if (line.startsWith("# Schema:")) {
                        slowQueryMetadata.setSchema(line);
                    }
                    if (line.startsWith("# Query_time:")) {
                        slowQueryMetadata.setQueryTime(line);
                    }
                    if (line.startsWith("# Bytes_sent:")) {
                        slowQueryMetadata.setBytesSent(line);
                    }
                    if (line.startsWith("SET timestamp=")) {
                        slowQueryMetadata.setTimestamp(line);
                    }
                    if (line.toLowerCase().trim().startsWith("insert") || line.toLowerCase().trim().startsWith("delete") || line.toLowerCase().trim().startsWith("update") || line.toLowerCase().trim().startsWith("select")) {
                        StringBuilder sql = new StringBuilder(line);
                        while (i < list.size() - 1) {
                            i++;
                            line = list.get(i);
                            if (line.startsWith("# Time: ")) {
                                i--;
                                break;
                            }
                            if (StringUtils.hasText(line) && !line.trim().startsWith("--")) {
                                sql.append(line);
                            }
                        }
                        slowQueryMetadata.setSql(sql.toString());
                        break;
                    }
                }
            }
        }
        return slowQueryMetadataList;
    }

    private static void evaluationAppName(String line, SlowQueryMetadata slowQueryMetadata) {
        try {
            // # User@Host: root[root] @  [192.168.100.101]  Id: 13523930
            String ip = line.substring(line.lastIndexOf("[")+1,line.lastIndexOf("]"));

            Map<String, String> appMap = getAppMap();
            String appName = appMap.get(ip);
            if (!StringUtils.hasText(appName)) {
                appName = "未知服务";
            }
            slowQueryMetadata.setAppName(appName);
        } catch (Exception e) {
            slowQueryMetadata.setAppName("异常服务");
        }

    }
    private static Map<String, String> getAppMap() {
        Map<String, String> appMap = new HashMap<>();
        appMap.put("192.168.100.101", "com-study-gateway");
        appMap.put("192.168.100.102", "com-study-gateway");
        appMap.put("192.168.100.103", "com-study-registry");
        appMap.put("192.168.100.104", "com-study-registry");
        appMap.put("192.168.100.104", "com-study-obs-web");
        appMap.put("192.168.100.106", "com-study-uid-web");
        return appMap;
    }

}

JSQLParser使用

insert语句

@Test
public void testInsertStatement() {
    String sql = "INSERT INTO emp (name, age) VALUES ('张三', 38)";
    try {
        Statement parse = CCJSqlParserUtil.parse(sql);
        if (parse instanceof Insert) {
            Insert insert = (Insert) parse;
            Table table = insert.getTable();
            String name = table.getName();
            log.info(name);
            Values values = insert.getValues();
            log.info(values.toString());
            ExpressionList<Column> columns = insert.getColumns();
            log.info(columns.toString());
        }
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}

19:02:07.239 [main] INFO com.study.jsqlparser.JSQLParserTest - emp
19:02:07.242 [main] INFO com.study.jsqlparser.JSQLParserTest - VALUES ('张三', 38)
19:02:07.242 [main] INFO com.study.jsqlparser.JSQLParserTest - name, age

delete语句

@Test
public void testDeleteStatement() {
    String sql = "DELETE FROM emp WHERE id = 1";
    try {
        Statement parse = CCJSqlParserUtil.parse(sql);
        if (parse instanceof Delete) {
            Delete delete = (Delete) parse;
            Table table = delete.getTable();
            log.info(table.toString());
            Expression where = delete.getWhere();
            log.info(where.toString());
        }

    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}

19:08:04.037 [main] INFO com.study.jsqlparser.JSQLParserTest - emp
19:08:04.039 [main] INFO com.study.jsqlparser.JSQLParserTest - id = 1

update 语句

@Test
public void testUpdateStatement() {
    String sql = "UPDATE emp SET ename = '张三', age = 18 WHERE id = 1045983421034180";
    try {
        Statement parse = CCJSqlParserUtil.parse(sql);
        if (parse instanceof Update) {
            Update update = (Update) parse;
            log.info(update.getTable().toString());
            List<UpdateSet> updateSets = update.getUpdateSets();
            for (UpdateSet updateSet : updateSets) {
                ExpressionList<Column> columns = updateSet.getColumns();
                log.info(columns.toString());
                ExpressionList<?> values = updateSet.getValues();
                log.info(values.toString());
            }
            Expression where = update.getWhere();
            log.info(where.toString());
        }
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}

19:19:18.450 [main] INFO com.study.jsqlparser.JSQLParserTest - emp
19:19:18.452 [main] INFO com.study.jsqlparser.JSQLParserTest - ename
19:19:18.452 [main] INFO com.study.jsqlparser.JSQLParserTest - '张三'
19:19:18.452 [main] INFO com.study.jsqlparser.JSQLParserTest - age
19:19:18.452 [main] INFO com.study.jsqlparser.JSQLParserTest - 18
19:19:18.452 [main] INFO com.study.jsqlparser.JSQLParserTest - id = 1045983421034180

简单select语句

@Test
public void testSelectStatement() {
    String sql = "SELECT name,age FROM emp WHERE name LIKE '%三%' group by name,age having avg(age) >18  limit 10";
    try {
        Statement parse = CCJSqlParserUtil.parse(sql);
        if (parse instanceof Select) {
            Select select = (Select) parse;
            PlainSelect plainSelect = select.getPlainSelect();
            log.info(plainSelect.getSelectItems().toString());
            log.info(plainSelect.getFromItem().toString());
            log.info(plainSelect.getWhere().toString());
            log.info(plainSelect.getGroupBy().toString());
            log.info(plainSelect.getHaving().toString());
            log.info(plainSelect.getLimit().toString());
        }
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}

19:47:22.904 [main] INFO com.study.jsqlparser.JSQLParserTest - [name, age]
19:47:22.906 [main] INFO com.study.jsqlparser.JSQLParserTest - emp
19:47:22.906 [main] INFO com.study.jsqlparser.JSQLParserTest - name LIKE '%三%'
19:47:22.906 [main] INFO com.study.jsqlparser.JSQLParserTest - GROUP BY name, age
19:47:22.906 [main] INFO com.study.jsqlparser.JSQLParserTest - avg(age) > 18
19:47:22.907 [main] INFO com.study.jsqlparser.JSQLParserTest -  LIMIT 10

复杂 select 语句

@Test
public void testComplexSelectStatement() {
    String sql = "select d.deptno,d.dname,avg(sal) from emp e join dept d on e.deptno = d.deptno where e.is_deleted = 0 group by d.deptno,d.dname having avg(sal)>10000 ORDER BY avg(sal) limit 10";
    try {
        Statement parse = CCJSqlParserUtil.parse(sql);
        if (parse instanceof Select) {
            Select select = (Select) parse;
            PlainSelect plainSelect = select.getPlainSelect();
            log.info(plainSelect.getSelectItems().toString());
            log.info(plainSelect.getFromItem().toString());
            log.info(plainSelect.getJoins().toString());
            log.info(plainSelect.getWhere().toString());
            log.info(plainSelect.getGroupBy().toString());
            log.info(plainSelect.getHaving().toString());
            log.info(plainSelect.getOrderByElements().toString());
            log.info(plainSelect.getLimit().toString());
        }
    } catch (JSQLParserException e) {
        e.printStackTrace();
    }
}

08:34:32.895 [main] INFO com.study.jsqlparser.JSQLParserTest - [d.deptno, d.dname, avg(sal)]
08:34:32.898 [main] INFO com.study.jsqlparser.JSQLParserTest - emp e
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest - [JOIN dept d ON e.deptno = d.deptno]
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest - e.is_deleted = 0
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest - GROUP BY d.deptno, d.dname
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest - avg(sal) > 10000
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest -  LIMIT 10
08:34:32.899 [main] INFO com.study.jsqlparser.JSQLParserTest - [avg(sal)]

JSQLParserTest完整代码

import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.Values;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;
import org.junit.jupiter.api.Test;

import java.util.List;

@Slf4j
public class JSQLParserTest {
    @Test
    public void testInsertStatement() {
        String sql = "INSERT INTO emp (name, age) VALUES ('张三', 38)";
        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof Insert) {
                Insert insert = (Insert) parse;
                Table table = insert.getTable();
                String name = table.getName();
                log.info(name);
                Values values = insert.getValues();
                log.info(values.toString());
                ExpressionList<Column> columns = insert.getColumns();
                log.info(columns.toString());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testDeleteStatement() {
        String sql = "DELETE FROM emp WHERE id = 1";
        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof Delete) {
                Delete delete = (Delete) parse;
                Table table = delete.getTable();
                log.info(table.toString());
                Expression where = delete.getWhere();
                log.info(where.toString());
            }

        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    //
    @Test
    public void testUpdateStatement() {
        String sql = "UPDATE emp SET ename = '张三', age = 18 WHERE id = 1045983421034180";
        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof Update) {
                Update update = (Update) parse;
                log.info(update.getTable().toString());
                List<UpdateSet> updateSets = update.getUpdateSets();
                for (UpdateSet updateSet : updateSets) {
                    ExpressionList<Column> columns = updateSet.getColumns();
                    log.info(columns.toString());
                    ExpressionList<?> values = updateSet.getValues();
                    log.info(values.toString());
                }
                Expression where = update.getWhere();
                log.info(where.toString());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testSelectStatement() {
        String sql = "SELECT name,age FROM emp WHERE name LIKE '%三%' group by name,age having avg(age) >18  limit 10";
        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof Select) {
                Select select = (Select) parse;
                PlainSelect plainSelect = select.getPlainSelect();
                log.info(plainSelect.getSelectItems().toString());
                log.info(plainSelect.getFromItem().toString());
                log.info(plainSelect.getWhere().toString());
                log.info(plainSelect.getGroupBy().toString());
                log.info(plainSelect.getHaving().toString());
                log.info(plainSelect.getLimit().toString());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testComplexSelectStatement() {
        String sql = "select d.deptno,d.dname,avg(sal) from emp e join dept d on e.deptno = d.deptno where e.is_deleted = 0 group by d.deptno,d.dname having avg(sal)>10000 ORDER BY avg(sal) limit 10";
        try {
            Statement parse = CCJSqlParserUtil.parse(sql);
            if (parse instanceof Select) {
                Select select = (Select) parse;
                PlainSelect plainSelect = select.getPlainSelect();
                log.info(plainSelect.getSelectItems().toString());
                log.info(plainSelect.getFromItem().toString());
                log.info(plainSelect.getJoins().toString());
                log.info(plainSelect.getWhere().toString());
                log.info(plainSelect.getGroupBy().toString());
                log.info(plainSelect.getHaving().toString());
                log.info(plainSelect.getOrderByElements().toString());
                log.info(plainSelect.getLimit().toString());
            }
        } catch (JSQLParserException e) {
            e.printStackTrace();
        }
    }
}

结论

通过本文介绍的方法，可以有效地对数GB的慢SQL日志文件进行去重处理，大幅提高查询效率并减小文件规模。这种方法不仅适用于慢SQL日志处理，还可以扩展到其他类似的日志文件去重需求中。

未来工作

未来的优化方向可以包括：

  1. 更加智能的SQL标准化方法，以处理更复杂的SQL语句。
  2. 将处理逻辑进一步并行化，利用多线程和分布式计算提高处理速度。
  3. 开发图形化界面工具，方便运维人员操作和查看去重后的日志。

通过持续的优化和改进，我们可以进一步提升慢SQL日志处理的效率和准确性，为企业的数据处理和优化提供坚实的基础。

标签：info,Java,log,SQL,toString,JSQLParser,sql,import,jsqlparser
From： https://blog.csdn.net/weixin_41883161/article/details/139528408

相关文章

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99