我们在使用数据库时,为了使业务系统性能达到最优,往往都需要避免慢SQL查询,不能等到线上告警了再排查是否为慢SQL导致。在开发阶段,每个开发人员就应该针对自己写的SQL看是否可能为慢SQL,从而进行相应的SQL优化和索引优化等。项目中可能用的是mybatis,也可能用的jpa,针对写的SQL如何能自动执行explain sql语句来查看每条SQL的执行计划呢?于是有了本篇文章,带你实现这样一个简单好用通用的插件。
针对这个问题,一个最基本的思路就是:修改底层执行sql的代码,在sql执行前先explain sql,输出执行计划,然后再继续执行原本的逻辑。如下图:
以下针对使用了Java+MySQL的项目进行实现讲解,其他技术方式有兴趣的小伙伴可以自己实现哈。
操作MySQL,项目中一般会引入依赖:
<!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> <scope>runtime</scope> </dependency>
执行SQL时,经过跟踪代码执行路径,最终会到达这个方法: com.mysql.jdbc.PreparedStatement#executeInternal
/** * Actually execute the prepared statement. This is here so server-side * PreparedStatements can re-use most of the code from this class. * * @param maxRowsToRetrieve the max number of rows to return * @param sendPacket SQL语句packet * @param createStreamingResultSet should a 'streaming' result set be created? * @param queryIsSelectOnly is this query doing a SELECT? */ protected ResultSetInternalMethods executeInternal(int maxRowsToRetrieve, Buffer sendPacket, boolean createStreamingResultSet, boolean queryIsSelectOnly, Field[] metadataFromCache, boolean isBatch) throws SQLException {}
其中,参数sendPacket就是最终要执行的完整sql语句。我们需要在这个方法中加入explain sql逻辑。
我们所写的插件应该是通用的,不能侵入现有代码。不限于以下两种实现方式:
回看标题,本篇仅讲解基于jrebel平台来实现这么一个神奇的插件。关于jrebel自定义插件开发,请参阅官方文档: Custom JRebel plugins
实现过程还是非常轻松的,一起来吧!
pom依赖如下:
<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <sdk.version>7.0.0</sdk.version> </properties> <repositories> <repository> <id>zt-public</id> <url>https://repos.zeroturnaround.com/nexus/content/groups/zt-public</url> </repository> </repositories> <dependencies> <dependency> <groupId>org.zeroturnaround</groupId> <artifactId>jr-sdk</artifactId> <version>${sdk.version}</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.zeroturnaround</groupId> <artifactId>jr-utils</artifactId> <version>${sdk.version}</version> <scope>provided</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> <scope>provided</scope> </dependency> </dependencies> View Code
继承org.zeroturnaround.javarebel.integration.support.JavassistClassBytecodeProcessor
package com.example.plugin.mysql.cbp; public class MySQLExplainCBP extends JavassistClassBytecodeProcessor { @Override public void process(ClassPool cp, ClassLoader cl, CtClass ctClass) throws Exception { LoggerFactory.getInstance().echo("MySQLExplainCBP..."); // 自定义处理类所在包 cp.importPackage("com.example.plugin.mysql.explain"); // 找到方法:com.mysql.jdbc.PreparedStatement#executeInternal // 在该方法第一行之前插入自定义逻辑 CtMethod m = ctClass.getDeclaredMethod("executeInternal"); m.insertBefore( "MySQLExplain.explainSql(this.connection, sendPacket);" ); } } View Code
查看MySQL执行计划
package com.example.plugin.mysql.explain; public final class MySQLExplain { private static final Logger logger = LoggerFactory.getInstance(); /** * 调用者:MySQLExplainCBP#process */ public static void explainSql(com.mysql.jdbc.MySQLConnection conn, com.mysql.jdbc.Buffer sendPacket) { byte[] bytes = new byte[sendPacket.getPosition()]; System.arraycopy(sendPacket.getByteBuffer(), 0, bytes, 0, bytes.length); String sql = new String(bytes, 5, bytes.length - 5); // 前置检查,检查是否执行计划 if (!processBefore(sql)) { return; } try { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("EXPLAIN " + sql); List<ExplainResultVo> explainResultList = new ArrayList<>(); while (rs.next()) { ExplainResultVo explainResultVo = new ExplainResultVo(); explainResultVo.setId(rs.getString("id")); explainResultVo.setSelectType(rs.getString("select_type")); explainResultVo.setTable(rs.getString("table")); explainResultVo.setPartitions(rs.getString("partitions")); explainResultVo.setType(rs.getString("type")); explainResultVo.setPossibleKeys(rs.getString("possible_keys")); explainResultVo.setKey(rs.getString("key")); explainResultVo.setKeyLen(rs.getString("key_len")); explainResultVo.setRef(rs.getString("ref")); explainResultVo.setRows(rs.getString("rows")); explainResultVo.setFiltered(rs.getString("filtered")); explainResultVo.setExtra(rs.getString("Extra")); explainResultList.add(explainResultVo); } rs.close(); // 打印结果 analyzeResult(sql, explainResultList); } catch (Exception se) { logger.errorEcho("EXPLAIN SQL异常", se); } } /** * 前置检查,检查是否执行计划,自定添加逻辑 */ private static boolean processBefore(String sql) { return true; } /** * 分析结果,打印执行计划,可自定添加过滤条件 */ private static void analyzeResult(String sql, List<ExplainResultVo> explainResultList) { ExplainHelper.printExplainResult(sql, explainResultList, showSQL); } } View Code
实现接口:org.zeroturnaround.javarebel.Plugin,需在pom中配置该类
package com.example.plugin.mysql; /** * 插件注册,需在pom中配置该类 */ public class MySQLExplainPlugin implements Plugin { private static final Logger logger = LoggerFactory.getInstance(); @Override public void preinit() { Integration integration = IntegrationFactory.getInstance(); ClassLoader cl = getClass().getClassLoader(); logger.echo("MySQL执行计划插件启用, 配置详情: /n" + Config.getMySQLConfTable()); integration.addIntegrationProcessor( cl, "com.mysql.jdbc.PreparedStatement", new MySQLExplainCBP()); } // 指定依赖条件,依赖存在时才启用本插件 @Override public boolean checkDependencies(ClassLoader cl, ClassResourceSource crs) { return crs.getClassResource("com.mysql.jdbc.PreparedStatement") != null; } @Override public String getId() { return "mysql-explain-jr-plugin"; } // 省略其余方法,默认返回null即可 } View Code
<build> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <version>3.6.1</version> <configuration> <source>8</source> <target>8</target> </configuration> </plugin> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-jar-plugin</artifactId> <version>3.0.2</version> <configuration> <archive> <manifestEntries> <JavaRebel-Plugin>com.example.plugin.mysql.MySQLExplainPlugin</JavaRebel-Plugin> </manifestEntries> </archive> </configuration> </plugin> </plugins> </build> View Code
mvn clean package -Dmaven.test.skip=true
项目需要使用jrebel启动,并且指定该插件所在路径,通过jvm参数指定
-Drebel.plugins=/your/path/mysql-explain-jr-plugin.jar
若生效,控制台会输出“MySQLExplainCBP...”等字样。
此处加了一些自定义逻辑
基本实现了功能,可以结合idea插件达到更加便捷友好的使用体验!
暂无,稍后补充
Custom JRebel plugins