mysqldump导入慢的解决方法

mysqldump导入慢的解决方法
MySQL导出的SQL语句在导入时有可能会非常非常慢，经历过导入仅45万条记录，竟用了近3个小时。在导出时合理使用几个参数，可以大大加快导 入的速度。

导出
-e 使用包括几个VALUES列表的多行INSERT语法;
–max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小;
–net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行。

注意：max_allowed_packet和net_buffer_length不能比目标数据库的设定数值 大，否则可能出错。

首先确定目标库的参数值

mysql>show variables like “max_allowed_packet”;
mysql>show variables like “net_buffer_length”;
根据参数值书写mysqldump命令，如：

mysqldump -uroot -pxxx app –single-transaction –default-character-set=utf8mb4 –ignore-table=app.app_coin_record –ignore-table=app.app_vip_record –max_allowed_packet=1073741824 –net_buffer_length=1048576 > app.sql
导入
之前2小时才能导入的sql现在几十秒就可以完成了。
mysql中source *sql的步骤导入数据太慢了
把innodb_flush_log_at_trx_commit这个参数改为0 然后重启数据库 应该比你原来速度快100倍
mysql source 命令导入大的sql
在mysql的安装目录下 找到 my.ini文件 加入以下代码：
代码如下

interactive_timeout = 120
wait_timeout = 120
max_allowed_packet = 32M

Mysql Explain重要参数
一、Explain中的“Type”
官网解释：连接类型(the join type)。它描述了找到所需数据使用的扫描方式。

最为常见的扫描方式有：

system：系统表，少量数据，往往不需要进行磁盘IO；
const：常量连接；
eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
ref：非主键非唯一索引等值扫描；
range：范围扫描；
index：索引树扫描；
ALL：全表扫描(full table scan)；
上面各类扫描方式由快到慢：
system > const > eq_ref > ref > range > index > ALL

1.1 system
扫描类型为system，说明数据已经加载到内存里，不需要进行磁盘IO，这类扫描是速度最快的。

1.2 const
const扫描的条件为：
（1）命中主键(primary key)或者唯一(unique)索引；
（2）被连接的部分是一个常量(const)值；

1.3 eq_ref
eq_ref扫描的条件为：对于前表的每一行(row)，后表只有一行被扫描。

1.4 ref
对于前表的每一行(row)，后表可能有多于一行的数据被扫描。

1.5 range
range类型，它是索引上的范围查询，它会在索引上扫码特定范围内的值。

1.6 index
index类型，需要扫描索引上的全部数据。

1.7 ALL
全表扫描。

1.8总结
system最快：不进行磁盘IO
const：PK或者unique上的等值查询
eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
ref：非唯一索引，等值匹配，可能有多行命中
range：索引上的范围扫描，例如：(between/in/)
index：索引上的全集扫描
ALL最慢：全表扫描(full table scan)

二、Explain中的“Extra”
2.1 Using where
Extra为Using where说明，SQL使用了where条件过滤数据。

2.2 Using index
Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

2.3 Using index condition
Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

2.4 Using filesort
Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。
典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

2.5 Using temporary
Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。
这类SQL语句性能较低，往往也需要进行优化。
典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

MySQL mysqldump 导入/导出 结构&数据&存储过程&函数&事件&触发器
一、使用mysqldump导出/导入sql数据文件

———————————————-库操作———————————————-
1.①导出一个库结构

mysqldump -d dbname -u root -p > xxx.sql

②导出多个库结构

mysqldump -d -B dbname1 dbname2 -u root -p > xxx.sql

2.①导出一个库数据

mysqldump -t dbname -u root -p > xxx.sql

②导出多个库数据

mysqldump -t -B dbname1 dbname2 -u root -p > xxx.sql

3.①导出一个库结构以及数据

mysqldump dbname1 -u root -p –default-character-set=字符集> xxx.sql

②导出多个库结构以及数据

mysqldump -B dbname1 dbname2 -u root -p > xxx.sql

———————————————-表操作———————————————-
4.①导出一个表结构

mysqldump -d dbname1 tablename1 -u root -p > xxx.sql

②导出多个表结构

mysqldump -d -B dbname1 –tables tablename1 tablename2 -u root -p > xxx.sql

5.①导出一个表数据

mysqldump -t dbname1 tablename1 -u root -p > xxx.sql

②导出多个表数据

mysqldump -d -B dbname1 –tables tablename1 tablename2 -u root -p > xxx.sql

6.①导出一个表结构以及数据

mysqldump dbname1 tablename1 -u root -p > xxx.sql

②导出多个表结构以及数据

mysqldump -B dbname1 –tables tablename1 tablename2 -u root -p > xxx.sql

————————————–存储过程&函数操作————————————-
7.只导出存储过程和函数(不导出结构和数据，要同时导出结构的话，需要同时使用-d)

mysqldump -R -ndt dbname1 -u root -p > xxx.sql

———————————————-事件操作———————————————-
8.只导出事件

mysqldump -E -ndt dbname1 -u root -p > xxx.sql

—————————————–触发器操作——————————————–
9.不导出触发器（触发器是默认导出的–triggers，使用–skip-triggers屏蔽导出触发器）

mysqldump –skip-triggers dbname1 -u root -p > xxx.sql

————————————————————————————————
10.导入

source xxx.sql

————————————————————————————————
总结一下：

-d 结构(–no-data:不导出任何数据，只导出数据库表结构)

-t 数据(–no-create-info:只导出数据，而不添加CREATE TABLE 语句)

-n (–no-create-db:只导出数据，而不添加CREATE DATABASE 语句）

-R (–routines:导出存储过程以及自定义函数)

-E (–events:导出事件)

–triggers (默认导出触发器，使用–skip-triggers屏蔽导出)

-B (–databases:导出数据库列表，单个库时可省略）

–tables 表列表（单个表时可省略）

①同时导出结构以及数据时可同时省略-d和-t
②同时 不 导出结构和数据可使用-ntd
③只导出存储过程和函数可使用-R -ntd
④导出所有(结构&数据&存储过程&函数&事件&触发器)使用-R -E(相当于①，省略了-d -t;触发器默认导出)
⑤只导出结构&函数&事件&触发器使用 -R -E -d

mysqldump -uroot -ppassword –default-character-set=utf8mb4 app –tables app_users app_vip app_vod_comment app_danmus > app.sql