MAT分析SQL导致的内存溢出

分析一次意外全量查询数据库导致的内存溢出

增加启动命令以在内存溢出时保存hprof

-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath=/root/logs/heapdump/mall-server_%p.hprof

下载MAT

https://archive.eclipse.org/mat/1.10.0/rcp/MemoryAnalyzer-1.10.0.20200225-win32.win32.x86_64.zip

使用MAT打开内存溢出时保存的hprof文件，然后打开Dominator Tree窗口

进来之后已经默认按照内存大小进行排序，重点是排查前几项

可以看到第一项是com.mysql.cj.jdbc.result.ResultSetImpl
这是存储查询结果的类，看到它基本就已经确认是SQL查询导致的内存溢出，接下来我们需要找到罪魁祸首的SQL

选中ResultSetImpl后在左边找到owningStatement(com.mysql.cj.jdbc.ClientPreparedStatement)，copy它的内存值，然后用窗口打开这个对象

进来之后展开query就可以看到originalSql了(使用右键 -> copy -> value 可以复制出来)
但这里的SQL参数还都是？，需要在下面的queryBindings -> bindValues 找到所有的参数

由于我这个sql参数比较多，不好一个一个复制出来，所以还需要其它方法进行还原

1. 复制bindValues的内存地址
2. 使用OQL查询成表格

SELECT
    b.isSet AS isSet,
    b.isNull AS isNull,
    toString(b.targetType) AS mysqlType,
    toString(b.value) AS value
FROM OBJECTS (
    SELECT OBJECTS a[0:-1]
    FROM OBJECTS 内存地址 a
) b

3. 使用代码还原

package org.example.sql;

import com.alibaba.excel.EasyExcel;
import lombok.Data;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Locale;
import java.util.Set;

public class OriginalSql {

    private static final Path DEFAULT_DIRECTORY = Paths.get(
            "src", "main", "java", "org", "example", "sql"
    );

    private static final Set<String> INTEGER_TYPES = new HashSet<>(Arrays.asList(
            "BIT", "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT", "YEAR"
    ));

    private static final Set<String> DECIMAL_TYPES = new HashSet<>(Arrays.asList(
            "DECIMAL", "DEC", "NUMERIC", "FIXED", "FLOAT", "REAL", "DOUBLE", "DOUBLE_PRECISION"
    ));

    public static void main(String[] args) throws IOException {
        Path directory = args.length == 0 ? DEFAULT_DIRECTORY : Paths.get(args[0]);
        Path output = restore(directory);
        System.out.println(new String(Files.readAllBytes(output), StandardCharsets.UTF_8));
        System.out.println("Restored SQL written to: " + output.toAbsolutePath());
    }

    public static Path restore(Path directory) throws IOException {
        Path sqlFile = directory.resolve("originalSql.txt");
        Path bindValuesFile = directory.resolve("bindValues.csv");
        Path outputFile = directory.resolve("restoredSql.txt");

        String sql = new String(Files.readAllBytes(sqlFile), StandardCharsets.UTF_8);
        List<BindValues> bindValues = EasyExcel
                .read(bindValuesFile.toString(), BindValues.class, null)
                .autoCloseStream(Boolean.TRUE)
                .doReadAllSync();

        String restoredSql = restoreSql(sql, bindValues);
        Files.write(outputFile, restoredSql.getBytes(StandardCharsets.UTF_8));
        return outputFile;
    }

    public static String restoreSql(String sql, List<BindValues> bindValues) {
        if (sql == null) {
            throw new IllegalArgumentException("SQL must not be null");
        }
        if (bindValues == null) {
            throw new IllegalArgumentException("Bind values must not be null");
        }

        List<Integer> placeholders = findPlaceholders(sql);
        if (placeholders.size() != bindValues.size()) {
            throw new IllegalArgumentException(
                    "Placeholder count " + placeholders.size()
                            + " does not match bind value count " + bindValues.size()
            );
        }

        StringBuilder restored = new StringBuilder(sql.length() + bindValues.size() * 8);
        int start = 0;
        for (int i = 0; i < placeholders.size(); i++) {
            int placeholder = placeholders.get(i);
            restored.append(sql, start, placeholder);
            restored.append(toSqlLiteral(bindValues.get(i), i + 1));
            start = placeholder + 1;
        }
        restored.append(sql, start, sql.length());
        return restored.toString();
    }

    private static String toSqlLiteral(BindValues bindValue, int parameterIndex) {
        if (bindValue == null) {
            throw new IllegalArgumentException("Bind value " + parameterIndex + " must not be null");
        }
        if (!Boolean.parseBoolean(bindValue.getIsSet())) {
            throw new IllegalArgumentException("Bind value " + parameterIndex + " is not set");
        }
        if (Boolean.TRUE.equals(bindValue.getIsNull())) {
            return "NULL";
        }

        String value = bindValue.getValue();
        if (value == null) {
            throw new IllegalArgumentException(
                    "Bind value " + parameterIndex + " has no value but isNull is false"
            );
        }

        String mysqlType = bindValue.getMysqlType() == null
                ? "VARCHAR"
                : bindValue.getMysqlType().trim().toUpperCase(Locale.ROOT).replace(' ', '_');
        if ("BOOLEAN".equals(mysqlType) || "BOOL".equals(mysqlType)) {
            return toBooleanLiteral(value, parameterIndex);
        }

        boolean unsigned = mysqlType.endsWith("_UNSIGNED");
        String baseType = unsigned
                ? mysqlType.substring(0, mysqlType.length() - "_UNSIGNED".length())
                : mysqlType;
        if (INTEGER_TYPES.contains(baseType)) {
            validateNumericLiteral(value, parameterIndex, unsigned, false);
            return value;
        }
        if (DECIMAL_TYPES.contains(baseType)) {
            validateNumericLiteral(value, parameterIndex, unsigned, true);
            return value;
        }
        return "'" + escapeMysqlString(value) + "'";
    }

    private static String toBooleanLiteral(String value, int parameterIndex) {
        if ("true".equalsIgnoreCase(value) || "1".equals(value)) {
            return "TRUE";
        }
        if ("false".equalsIgnoreCase(value) || "0".equals(value)) {
            return "FALSE";
        }
        throw new IllegalArgumentException(
                "Bind value " + parameterIndex + " is not a valid boolean: " + value
        );
    }

    private static void validateNumericLiteral(
            String value,
            int parameterIndex,
            boolean unsigned,
            boolean allowDecimal
    ) {
        String pattern = allowDecimal
                ? "[+-]?(?:(?:\\d+(?:\\.\\d*)?)|(?:\\.\\d+))(?:[eE][+-]?\\d+)?"
                : "[+-]?\\d+";
        if (!value.matches(pattern) || (unsigned && value.startsWith("-"))) {
            throw new IllegalArgumentException(
                    "Bind value " + parameterIndex + " is not a valid numeric literal: " + value
            );
        }
    }

    private static String escapeMysqlString(String value) {
        return value
                .replace("\\", "\\\\")
                .replace("\u0000", "\\0")
                .replace("\n", "\\n")
                .replace("\r", "\\r")
                .replace("\u001a", "\\Z")
                .replace("'", "''");
    }

    private static List<Integer> findPlaceholders(String sql) {
        List<Integer> placeholders = new ArrayList<>();
        State state = State.NORMAL;

        for (int i = 0; i < sql.length(); i++) {
            char current = sql.charAt(i);
            char next = i + 1 < sql.length() ? sql.charAt(i + 1) : '\0';

            switch (state) {
                case NORMAL:
                    if (current == '\'') {
                        state = State.SINGLE_QUOTE;
                    } else if (current == '"') {
                        state = State.DOUBLE_QUOTE;
                    } else if (current == '`') {
                        state = State.BACKTICK;
                    } else if (current == '#') {
                        state = State.LINE_COMMENT;
                    } else if (current == '-' && next == '-' && startsDashComment(sql, i)) {
                        state = State.LINE_COMMENT;
                        i++;
                    } else if (current == '/' && next == '*') {
                        state = State.BLOCK_COMMENT;
                        i++;
                    } else if (current == '?') {
                        placeholders.add(i);
                    }
                    break;
                case SINGLE_QUOTE:
                    i = movePastQuotedCharacter(sql, i, '\'');
                    if (i < 0) {
                        i = -i - 1;
                        state = State.NORMAL;
                    }
                    break;
                case DOUBLE_QUOTE:
                    i = movePastQuotedCharacter(sql, i, '"');
                    if (i < 0) {
                        i = -i - 1;
                        state = State.NORMAL;
                    }
                    break;
                case BACKTICK:
                    i = movePastQuotedCharacter(sql, i, '`');
                    if (i < 0) {
                        i = -i - 1;
                        state = State.NORMAL;
                    }
                    break;
                case LINE_COMMENT:
                    if (current == '\n' || current == '\r') {
                        state = State.NORMAL;
                    }
                    break;
                case BLOCK_COMMENT:
                    if (current == '*' && next == '/') {
                        state = State.NORMAL;
                        i++;
                    }
                    break;
                default:
                    throw new IllegalStateException("Unknown SQL parser state: " + state);
            }
        }
        return placeholders;
    }

    private static boolean startsDashComment(String sql, int firstDashIndex) {
        int followingIndex = firstDashIndex + 2;
        if (followingIndex >= sql.length()) {
            return true;
        }
        char following = sql.charAt(followingIndex);
        return Character.isWhitespace(following) || Character.isISOControl(following);
    }

    private static int movePastQuotedCharacter(String sql, int index, char quote) {
        char current = sql.charAt(index);
        char next = index + 1 < sql.length() ? sql.charAt(index + 1) : '\0';
        if (current == '\\' && next != '\0') {
            return index + 1;
        }
        if (current == quote) {
            if (next == quote) {
                return index + 1;
            }
            return -index - 1;
        }
        return index;
    }

    private enum State {
        NORMAL,
        SINGLE_QUOTE,
        DOUBLE_QUOTE,
        BACKTICK,
        LINE_COMMENT,
        BLOCK_COMMENT
    }

    @Data
    public static class BindValues {

        private String isSet;

        private Boolean isNull;

        private String mysqlType;

        private String value;
    }
}

接下来我们分析调用链

从dominator tree可以看到第二项是org.apache.tomcat.util.threads.TaskThread
基本也可以确认这个线程就是执行SQL的线程
展开TaskThread也可以看到线程里面的各种对象，包括方法的调用参数对象。选中后可以在左边看到对象的属性值
右键TaskThread -> Java Basics -> Thread Details 就可以看到完整堆栈了

从TaskThread找ClientPreparedStatement则是按下面这个路径

io.seata.rm.datasource.PreparedStatementProxy
  -> targetStatement / statementProxy / preparedStatement
    -> com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl
      -> raw / statement / stmt
        -> com.mysql.cj.jdbc.ClientPreparedStatement