快捷搜索:

大批量插入数据,却不会优化怎么办

1、大年夜批量插入数据优化

(1)对付MyISAM存储引擎的表,可以应用:DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非独一索引的更新。

ALTER TABLE tbl_name DISABLE KEYS;

loading the data

ALTER TABLE tbl_name ENABLE KEYS;

(2)对付InnoDB引擎,有以下几种优化步伐:

① 导入的数据按照主键的顺序保存:这是由于InnoDB引擎表示按照主键顺序保存的,假如能将插入的数据提前按照排序好自然能省去很多光阴。

比如bulk_insert.txt文件因此表user主键的顺序存储的,导入的光阴为15.23秒

mysql》 load data infile ‘mysql/bulk_insert.txt’ into table user;

Query OK, 126732 rows affected (15.23 sec)

Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0

没有按照主键排序的话,光阴为:26.54秒

mysql》 load data infile ‘mysql/bulk_insert.txt’ into table user;

Query OK, 126732 rows affected (26.54 sec)

Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0

② 导入数据前履行SET UNIQUE_CHECKS=0,关闭独一性校验,带导入之后再打开设置为1:校验会耗损光阴,在数据量大年夜的环境下必要斟酌。

③ 导入前设置SET AUTOCOMMIT=0,关闭自动提交,导入后停止再设置为1:这是由于自动提交会耗损部分光阴与资本,虽然耗损不是很大年夜,然则在数据量大年夜的环境下照样得斟酌。

2、INSERT的优化

(1)只管即便应用多个值表的 INSERT 语句,这种要领将大年夜大年夜缩减客户端与数据库之间的连接、关闭等耗损。(同一客户的环境下),即:

INSERT INTO tablename values(1,2),(1,3),(1,4)

实验:插入8条数据到user表中(应用navicat客户端对象)

insert into user values(1,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(2,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(3,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(4,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(5,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(6,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(7,‘test’,replace(uuid(),‘-’,‘’));

insert into user values(8,‘test’,replace(uuid(),‘-’,‘’));

获得反馈:

[SQL] insert into user values(1,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.033s

[SQL]

insert into user values(2,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.034s

[SQL]

insert into user values(3,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.056s

[SQL]

insert into user values(4,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.008s

[SQL]

insert into user values(5,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.008s

[SQL]

insert into user values(6,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.024s

[SQL]

insert into user values(7,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.004s

[SQL]

insert into user values(8,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 1

光阴: 0.004s

统共的光阴为0.171秒,接下来应用多值表形式:

insert into user values

(9,‘test’,replace(uuid(),‘-’,‘’)),

(10,‘test’,replace(uuid(),‘-’,‘’)),

(11,‘test’,replace(uuid(),‘-’,‘’)),

(12,‘test’,replace(uuid(),‘-’,‘’)),

(13,‘test’,replace(uuid(),‘-’,‘’)),

(14,‘test’,replace(uuid(),‘-’,‘’)),

(15,‘test’,replace(uuid(),‘-’,‘’)),

(16,‘test’,replace(uuid(),‘-’,‘’));

获得反馈:

[SQL] insert into user values

(9,‘test’,replace(uuid(),‘-’,‘’)),

(10,‘test’,replace(uuid(),‘-’,‘’)),

(11,‘test’,replace(uuid(),‘-’,‘’)),

(12,‘test’,replace(uuid(),‘-’,‘’)),

(13,‘test’,replace(uuid(),‘-’,‘’)),

(14,‘test’,replace(uuid(),‘-’,‘’)),

(15,‘test’,replace(uuid(),‘-’,‘’)),

(16,‘test’,replace(uuid(),‘-’,‘’));

受影响的行: 8

光阴: 0.038s

获得光阴为0.038,这样一来可以很显着节约光阴优化SQL

(2)假如在不合客户端插入很多行,可应用INSERT DELAYED语句获得更高的速率,DELLAYED含义是让INSERT语句顿时履行,着实数据都被放在内存的行列步队中。并没有真正写入磁盘。LOW_PRIORITY刚好相反。

(3)将索引文件和数据文件分在不合的磁盘上寄放(InnoDB引擎是在同一个表空间的)。

(4)假如批量插入,则可以增添bluk_insert_buffer_size变量值供给速率(只对MyISAM有用)

(5)当从一个文本文件装载一个表时,应用LOAD DATA INFILE,平日比INSERT语句快20倍。

3、GROUP BY的优化

在默认环境下,MySQL中的GROUP BY语句会对其后呈现的字段进行默认排序(非主键环境),就好比我们应用ORDER BY col1,col2,col3…以是我们在后面跟上具有相同列(与GROUP BY后呈现的col1,col2,col3…相同)ORDER BY子句并没有影响该SQL的实际履行机能。

那么就会有这样的环境呈现,我们对查询到的结果是否已经排序不在乎时,可以应用ORDER BY NULL禁止排序达到优化目的。下面应用EXPLAIN敕令阐发SQL。Java知音"民众,"号内回覆“口试题聚合”,送你一份口试题宝典

在user_1中履行select id, sum(money) form user_1 group by name时,会默认排序(留意group by后的column长短index才会表现group by的排序,假如是primary key,那之前说过了InnoDB默认是按照主键index排好序的)

mysql》 select*from user_1;

+----+----------+-------+

| id | name | money |

+----+----------+-------+

| 1 | Zhangsan | 32 |

| 2 | Lisi | 65 |

| 3 | Wangwu | 44 |

| 4 | Lijian | 100 |

+----+----------+-------+

4 rows in set

不禁止排序,即不应用ORDER BY NULL时:有显着的Using filesort。

当应用ORDER BY NULL禁止排序后,Using filesort不存在

4、ORDER BY 的优化

MySQL可以应用一个索引来满意ORDER BY 子句的排序,而不必要额外的排序,然则必要满意以下几个前提:

(1)WHERE 前提和OREDR BY 应用相同的索引:即key_part1与key_part2是复合索引,where中应用复合索引中的key_part1

SELECT*FROM user WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

(2)而且ORDER BY顺序和索引顺序相同:

SELECT*FROM user ORDER BY key_part1, key_part2;

(3)并且要么都是升序要么都是降序:

SELECT*FROM user ORDER BY key_part1 DESC, key_part2 DESC;

但以下几种环境则不应用索引:

(1)ORDER BY中混杂ASC和DESC:

SELECT*FROM user ORDER BY key_part1 DESC, key_part2 ASC;

(2)查询行的关键字与ORDER BY所应用的不相同,即WHERE 后的字段与ORDER BY 后的字段是不一样的

SELECT*FROM user WHERE key2 = ‘xxx’ ORDER BY key1;

(3)ORDER BY对不合的关键字应用,即ORDER BY后的关键字不相同

SELECT*FROM user ORDER BY key1, key2;

5、OR的优化

当MySQL应用OR查询时,假如要使用索引的话,必须每个前提列都使自力索引,而不是复合索引(多列索引),才能包管应用到查询的时刻应用到索引。

比如我们新建一张用户信息表user_info

mysql》 select*from user_info;

+---------+--------+----------+-----------+

| user_id | idcard | name | address |

+---------+--------+----------+-----------+

| 1 | 111111 | Zhangsan | Kunming |

| 2 | 222222 | Lisi | Beijing |

| 3 | 333333 | Wangwu | Shanghai |

| 4 | 444444 | Lijian | Guangzhou |

+---------+--------+----------+-----------+

4 rows in set

之后创建ind_name_id(user_id, name)复合索引、id_index(id_index)自力索引,idcard主键索引三个索引。

mysql》 show index from user_info;

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| user_info | 0 | PRIMARY | 1 | idcard | A | 4 | NULL | NULL | | BTREE | | |

| user_info | 1 | ind_name_id | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | |

| user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | | |

| user_info | 1 | id_index | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | |

+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set

测试一:OR连接两个有零丁索引的字段,全部SQL查询才会用到索引(index_merge),并且我们知道OR实际上是把每个结果着末UNION一路的。

mysql》 explain select*from user_info where user_id=1 or idcard=‘222222’;

+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+

| id | select_type | table | parTITIons | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+

| 1 | SIMPLE | user_info | NULL | index_merge | PRIMARY,ind_name_id,id_index | ind_name_id,PRIMARY | 4,62 | NULL | 2 | 100 | Using sort_union(ind_name_id,PRIMARY); Using where |

+----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+

1 row in set

测试二:OR应用复合索引的字段name,与没有索引的address,全部SQL都是ALL全表扫描的

mysql》 explain select*from user_info where name=‘Zhangsan’ or address=‘Beijing’;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | parTITIons | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 43.75 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

互换OR位置并且应用别的的复合索引的列,也是ALL全表扫描:

mysql》 explain select*from user_info where address=‘Beijing’ or user_id=1;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 43.75 | Using where |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

6、优化嵌套查询

应用嵌套查询无意偶尔候可以应用更有效的JOIN连接代替,这是由于MySQL中不必要在内存中创建临时表完成SELECT子查询与主查询两部分查询事情。然则并不是所有的时刻都成立,最好是在on关键字后面的列有索引的话,效果会更好!

比如在表major中major_id是有索引的:

select * from student u left join major m on u.major_id=m.major_id where m.major_id is null;

而经由过程嵌套查询时,在内存中创建临时表完成SELECT子查询与主查询两部分查询事情,会有必然的耗损

select * from student u where major_id not in (select major_id from major);

7、应用SQL提示

SQL提示(SQL HINT)是优化数据库的一个紧张手段,便是往SQL语句中加入一些工资的提示来达到优化目的。下面是一些常用的SQL提示:

(1)USE INDEX:应用USE INDEX是盼望MySQL去参考索引列表,就可以让MySQL不必要斟酌其他可用索引,着实也便是possible_keys属性下参考的索引值

mysql》 explain select* from user_info use index(id_index,ind_name_id) where user_id》0;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

mysql》 explain select* from user_info use index(id_index) where user_id》0;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | id_index | NULL | NULL | NULL | 4 | 100 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

(2)IGNORE INDEX轻忽索引

我们应用user_id判断,用不到其他索引时,可以轻忽索引。即与USE INDEX相反,从possible_keys中减去不必要的索引,然则实际情况中很少应用。

mysql》 explain select* from user_info ignore index(primary,ind_name_id,id_index) where user_id》0;

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set

(3)FORCE INDEX强制索引

比如where user_id 》 0,然则user_id在表中都是大年夜于0的,自然就会进行ALL全表搜索,然则应用FORCE INDEX虽然履行效率不是最高(where user_id 》 0前提抉择的)但MySQL照样应用索引。

mysql》 explain select* from user_info where user_id》0;

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where |

+----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+

1 row in set

之后强制应用自力索引id_index(user_id):

mysql》 explain select* from user_info force index(id_index) where user_id》0;

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

| 1 | SIMPLE | user_info | NULL | range | id_index | id_index | 4 | NULL | 4 | 100 | Using index condition |

+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

1 row in set

总结

(1)很多时刻数据库的机能是因为分歧适(是指效率不高,可能会导致表等)的SQL语句造成,本篇博文只是先容简单的SQL优化

(2)此中有些优化在真正开拓中是用不到的,然则一旦出问题机能下降的时刻必要去逐一阐发。

您可能还会对下面的文章感兴趣: