mysql性能优化
索引对性能的影响
设计思路：建一个表，分别测试0个索引，1个索引、2个索引时插入1000000调数据所需时间
建表语句
CREATE TABLE `sql_optimize` (
`id` bigint(255) unsigned NOT NULL AUTO_INCREMENT,
`varchar1` varchar(255) DEFAULT NULL,
`varchar2` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;java类
package top.lishuoboy.sql.mysql;
import top.lishuoboy.util.LogUtil;
import top.lishuoboy.util.TimerUtil;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class SqlInsert {
private static final LogUtil logUtil = new LogUtil(SqlInsert.class);
private static final TimerUtil timerUtil = new TimerUtil();
private static final TimerUtil timerUtilAll = new TimerUtil();
private static String urlMySQLBatch = "jdbc:mysql://localhost:3306/sql_optimize?rewriteBatchedStatements=true";
private static String userMySQL = "root";
private static String passwordMySQL = "1234";
private static String driveClassMySQL = "com.mysql.jdbc.Driver";
public static void main(String[] args) {
SqlInsert.run();
}
private static void run() {
Connection conn = null;
PreparedStatement ps = null;
int count = 5000000;
try {
// 1 加载驱动
Class.forName(driveClassMySQL);
// 2 创建Connection
// MySQL开启批量rewriteBatchedStatements（扩展语句）
conn = DriverManager.getConnection(urlMySQLBatch, userMySQL, passwordMySQL);
conn.setAutoCommit(false); // ***此行对于不支持事物的MyISAM、Memory引擎无效
// 4 开始
String sql = "INSERT INTO sql_optimize(varchar1,varchar2) VALUES (?,?)";
ps = conn.prepareStatement(sql);
timerUtil.getPastTime();
timerUtilAll.getPastTime();
String str;
for (int i = 1; i <= count; i++) {
str = "" + (long) (10000000000000000L * Math.random());
ps.setString(1, str);
ps.setString(2, str);
// logUtil.info(ps);
ps.addBatch();
if (i % 100000 == 0) {
ps.executeBatch(); // 对于不支持事务的引擎，不管是否自动提交，此行代码会数据落库
conn.commit(); // 对于不支持事务的引擎，执行st.executeBatch()已提交，所以此行可以注释掉
logUtil.info("第" + i + "条,用时：" + timerUtil.getPastTime() + "ms");
}
}
ps.executeBatch();
conn.commit();
logUtil.info("共" + count + "条,用时：" + timerUtilAll.getPastTime() + "ms");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null)
ps.close();
if (conn != null)
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}结论
不建索引
2019-10-27 19:43:39.728 +0800 [info] 第100000条,用时：1761ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:40.596 +0800 [info] 第200000条,用时：868ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:41.868 +0800 [info] 第300000条,用时：1272ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:43.661 +0800 [info] 第400000条,用时：1793ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:44.836 +0800 [info] 第500000条,用时：1175ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:47.972 +0800 [info] 第600000条,用时：3136ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:48.761 +0800 [info] 第700000条,用时：789ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:51.046 +0800 [info] 第800000条,用时：2285ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:54.399 +0800 [info] 第900000条,用时：3353ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:43:55.679 +0800 [info] 第1000000条,用时：1280ms @top.lishuoboy.sql.mysql.SqlInsert
字段varchar1建立索引
2019-10-27 18:58:19.059 +0800 [info] 第100000条,用时：8484ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 18:58:36.494 +0800 [info] 第200000条,用时：17435ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 18:58:53.460 +0800 [info] 第300000条,用时：16966ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 18:59:16.693 +0800 [info] 第400000条,用时：23233ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 18:59:40.991 +0800 [info] 第500000条,用时：24298ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:00:14.649 +0800 [info] 第600000条,用时：33658ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:01:27.831 +0800 [info] 第700000条,用时：73182ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:02:04.168 +0800 [info] 第800000条,用时：36337ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:02:57.355 +0800 [info] 第900000条,用时：53187ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:04:37.705 +0800 [info] 第1000000条,用时：100350ms @top.lishuoboy.sql.mysql.SqlInsert
字段varchar1、varchar2建立索引
2019-10-27 19:18:42.275 +0800 [info] 第100000条,用时：20655ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:19:09.639 +0800 [info] 第200000条,用时：27364ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:20:02.973 +0800 [info] 第300000条,用时：53334ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:21:40.717 +0800 [info] 第400000条,用时：97744ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:22:57.005 +0800 [info] 第500000条,用时：76288ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:25:19.725 +0800 [info] 第600000条,用时：142720ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:26:53.281 +0800 [info] 第700000条,用时：93556ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:29:06.209 +0800 [info] 第800000条,用时：132928ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:31:04.235 +0800 [info] 第900000条,用时：118026ms @top.lishuoboy.sql.mysql.SqlInsert
2019-10-27 19:34:08.457 +0800 [info] 第1000000条,用时：184222ms @top.lishuoboy.sql.mysql.SqlInsert
汇总一下
| 条数 | 无索引 | 1个索引 | 2个索引 |
| 100000 | 1761 | 8484 | 20655 |
| 200000 | 868 | 17435 | 27364 |
| 300000 | 1272 | 16966 | 53334 |
| 400000 | 1793 | 23233 | 97744 |
| 500000 | 1175 | 24298 | 76288 |
| 600000 | 3136 | 33658 | 142720 |
| 700000 | 789 | 73182 | 93556 |
| 800000 | 2285 | 36337 | 132928 |
| 900000 | 3353 | 53187 | 118026 |
| 1000000 | 1280 | 100350 | 184222 |
| 总用时ms | 17,712 | 387,130 | 946,837 |
