千万级数据批量导入mysql的优化
1.数据库引擎:
常见的索引引擎
InnoDB,现在默认的引擎,支持外键、行锁、事务。默认是一句一事务,一句一提交。事务是需要开启的,并提交或回滚来关闭的。批量录入数据,手动开启事务,并手动提交。开启事务:start transaction;提交事务:commit;回滚事务:rollback。读写效率均等,没有特别突出的地方。InnoDB对表格的管理是基于但文件的。文件包括表头、约束、数据等信息。
MyISM,推荐的读库引擎,没有事务,支持索引。写效率相对于InnoDB较低,读效率相对于InnoDB高很多。MyISAM对表格的管理是基于多文件的。有表头文件、约束文件、数据文件。
MGR_MyISM,对MyISAM的一个变形优化,对表的信息做了一个额外的索引管理。
MEMORY,内存引擎,数据只存储在内存,不落地到磁盘。通常应用在启动数据库的同时创建若干视图,视图使用内存引擎管理(不安全操作,对视图可写,会同步到表格)。也应用在,应用启动时,提供一个临时的表格,存储应用常用的,几乎不变的数据(如:电商中的商品类型)。
sql解析效率低,与Nosql比较
2.批量sql
insert into table_name() values();----单条数据录入
insert into table_name() values(),(),();—多条数据录入。带有缓存的。可以通过命令配置,也可以通过配置文件配置。单条sql不要录入过多的数据。通常不超过3M~10M.
3.数据库配置:DBA处理
配置SQL批处理缓存:
配置是否记录binlog,不推荐关闭
配置IO缓存
4.本地数据库导入:DBA处理—效率高
通过txt或csv文件做本地导入,mysqlimport xxx文件
5.代码级开发
batch批处理。找临界值,循环多次访问数据库,批量写入。
如:临界值是2000条数据。
int i = 0;
while(){
if(i++ % 2000 == 0){
executeBatch();
}
addBatch();
}
executeBatch();
6.索引问题
索引只提升读效率,会降低写效率。
降低写效率的原因:
索引是写入数据过程中维护的,将索引字段的值进行比较处理,并保存在一个树下,树是B[+]Tree。平衡树,查询效率高,维护效率低。
推荐是索引使用方式是:建表时,先不创建索引,当数据相对趋于稳定,或正式商业发布时,创建索引。
索引是先内存维护,索引内存空间不足,需要持久化到磁盘。