在大数据处理环境下，慢SQL日志优化是一个必要的步骤，尤其当日志文件达到数GB时，直接操作日志文件会带来诸多不便。本文将介绍如何通过Java和JSQLParser库来解析和去重慢SQL日志，以提高性能和可维护性。
公司生产环境中，某些操作产生的SQL执行时间较长，会记录在慢SQL日志文件中。慢SQL日志文件包含了SQL的执行时间、用户信息、查询语句等内容。由于这些日志文件可能包含大量重复的SQL语句，逐条查看和处理既耗时又低效，因此有必要进行去重操作。
本文旨在通过以下步骤实现慢SQL日志的去重：
为了实现上述目标，我们将使用以下工具和依赖：
<dependency>
<groupId>com.github.jsqlparsergroupId>
<artifactId>jsqlparserartifactId>
<version>4.9version>
dependency>
以下是一个慢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);
# slow.log 未知服务 4为去重时额外添加进去的行,slow.log代表sql所在原文件(如果慢sql日志文件被切分成好多个小文件时方便定位第一次出现的位置)未知服务代表服务名称,如果项目是微服务架构,可以将ip替换为服务名,可读性更高,只需要关注自己负责的未付即可,也可以在输出去重后的文件时按照微服务名称命名,每个微服务的日志单独写入到一个文件.# 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;
}
}
@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
@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
@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
@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
@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)]
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日志处理，还可以扩展到其他类似的日志文件去重需求中。
未来的优化方向可以包括：
通过持续的优化和改进，我们可以进一步提升慢SQL日志处理的效率和准确性，为企业的数据处理和优化提供坚实的基础。