当前位置：个人在线分享 > 高效处理海量慢SQL日志文件：Java与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 + "	" + appName + "	" + count + "
" +
time + "
" +
userAndHost + "
" +
schema + "
" +
queryTime + "
" +
bytesSent + "
" +
timestamp + "
" +
sql + "
";
}
/**
* 去重 不同参数的相同格式的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("	", " ");
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日志处理的效率和准确性，为企业的数据处理和优化提供坚实的基础。

    本站无任何商业行为
    个人在线分享 » 高效处理海量慢SQL日志文件：Java与JSQLParser去重方案详解
    admin

    admin 钻石

    分享到：

    相关推荐

    E-->