优化一：批量插入 （实际业务中，耗时从400s左右降到7s左右）
伪代码
@Cleanup Connection conn = DaoUtils.getConnection(false);
String sql = "INSERT INTO xx (column1, column2) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < 1000000; i++) {
ps.setString(1,"column1");
ps.setString(2,"column2");
ps.addBatch();
// 每1000条记录插入一次
if (i % 1000 == 0) {
ps.executeBatch();
conn.commit();
ps.clearBatch();
}
}
// 剩余数量不足1000
ps.executeBatch();
conn.commit();
ps.clearBatch();默认情况下，有多少条数据，就会执行多少条sql。可以在mysql链接加上rewriteBatchedStatements=true，将多条插入sql重写为1条，可以有效减少sql执行次数。按照上面的代码，执行sql条数为执行executeBatch的次数。
jdbc:mysql://127.0.0.1:3306?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true
官方文档：
rewriteBatchedStatements
Should the driver use multi-queries (regardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements. Please be aware using rewriteBatchedStatements=true with INSERT .. ON DUPLICATE KEY UPDATE that for rewritten statement server returns only one value as sum of all affected (or found) rows in batch and it isn't possible to map it correctly to initial statements; in this case driver returns 0 as a result of each batch statement if total count was 0, and the Statement.SUCCESS_NO_INFO as a result of each batch statement if total count was > 0.
Default Value false
Since Version 3.1.13
https://dev.mysql.com/doc/connectors/en/connector-j-connp-props-performance-extensions.html
优化二：添加索引（分析sql，扫描数量、索引命中情况）
表结构
mysql> desc detection_plains;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| .. | ....... | NO | | NULL | |
| peopleId | bigint(20) | NO | | NULL | |
| plainType | int(2) | NO | | 0 | |
+-------------------+--------------+------+-----+---------+----------------+
xx rows in set (0.02 sec)已有索引
mysql> show index from t_name;
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_name | 0 | PRIMARY | 1 | id | A | 169267 | NULL | NULL | | BTREE | | |
+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)这张表目前仅有主键
查询sql
mysql> select no from t_name where peopleId=9379 and plainType=1 order by schedule DESC limit 1;
+----+
| no |
+----+
| 10 |
+----+
1 row in set (2.23 sec)这里花了2s+,不太符合预期
explain:查询执行sql情况，扫了多少条数据、有没有使用索引
mysql> explain select no from t_name where peopleId=9379 and plainType=1 order by schedule DESC limit 1;
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| 1 | SIMPLE | detection_plains | NULL | ALL | NULL | NULL | NULL | NULL | 169301 | 1.00 | Using where; Using filesort |
+----+-------------+------------------+------------+------+---------------+------+---------+------+--------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
目前表数据量
mysql> select count(id) from t_name;
+-----------+
| count(id) |
+-----------+
| 175575 |
+-----------+
1 row in set (5.45 sec)
通过explain结果看，目前查询sql并未使用索引，几乎扫了整张表的数据。我们在peopleId,plainType列添加索引
mysql> alter table t_name add index peopleIdPlainType(`peopleId`,`plainType`);
Query OK, 0 rows affected (8.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次explain可知，扫描数据下降到10条，命中了创建的peopleIdPlainType的索引。
mysql> explain select no from t_name where peopleId=9379 and plainType=1 order by schedule DESC limit 1;
+----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+---------------------------------------+
| 1 | SIMPLE | detection_plains | NULL | ref | peopleIdPlainType | peopleIdPlainType | 12 | const,const | 10 | 100.00 | Using index condition; Using filesort |
+----+-------------+------------------+------------+------+-------------------+-------------------+---------+-------------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> select no from t_name where peopleId=9379 and plainType=1 order by schedule DESC limit 1;
+----+
| no |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------------------------------------------------------------------------------------+
| 3 | 0.00723975 | select no from t_name where peopleId=9379 and plainType=1 order by schedule DESC limit 1 |
+----------+------------+------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
查询时间从2s+降到了7ms左右
其他
- 不使用“大事务"：如果执行操作耗时高且开启了事务，容易导致
Lock wait timeout exceeded; try restarting transaction异常 - 使用
SELECT * FROM information_schema.INNODB_TRX;可查询当前事务数量、执行状态和执行的sql等信息 - 尽量不并行操作db，会增加单个事务耗时
评论 (0)