索引对性能的影响

设计思路:建一个表,分别测试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