简述mysql问题处理

最近，有一位同事，咨询我mysql的一点问题， 具体来说， 是如何很快的将一个mysql导出的文件快速的导入到另外一个mysql数据库。我学习了很多mysql的知识， 使用的时间却并不是很多， 对于mysql导入这类问题，我更是头一次碰到。询问我的原因，我大致可以猜到，以前互相之间有过很多交流，可能觉得我学习还是很认真可靠的。

首先，我了解了一下大致的情况， （1）这个文件是从mysql导出的，文件是运维给的，具体如何生成的，他不知道，可以询问运维 （2）按照当前他写的代码来看， 每秒可以插入几百条数据 （3）按照他们的要求， 需要每秒插入三四千条数据。（4）他们需要插入的数据量达到几亿条数据， 当前有一个上千万的实验数据

根据我学习的知识，在《高性能mysql》上面有过描述， load data的速度，比插入数据库快得多， 所以，我先通过他们从运维那里获取了生成数据的代码，通过向他们寻求了大约几千条数据。运维生成数据的方式是：select ... into outfile, 根据mysql官方文档上的说明，正好可以通过load data加载回数据库，load data使用的fields和lines等参数， 可以通过运维给出的语句得到，测试一次，成功。在我的推荐下，我们先使用innodb引擎根据测试的结果显示，大约每秒1千多。这个，我是通过date; mysql -e "load data ..."; date; 来大致得到。考虑到innodb中存储带索引的数据，插入速度会随着数据量的增加而变慢，那么我们使用几千条数据测试的结果，应该超过1千多。我们又在目标电脑上进行测试，得到的结果基本一致，速度会略快，具体原因当时没有细查。向远程mysql导入数据，需要进行一定的配置，具体配置的说明，在这里省略。因为目标电脑有额外的用处，所以，我们决定现在本机电脑上进行测试。

 

进行了初步的准备工作之后，我决定获取更多的数据，这次我打算下拉10万条数据。可是我的那位同事，通过对csv文件进行剪切，得到的结果，不能在我的mysql上进行很好的加载（load data)。于是，我们打算从有上千万条数据的测试mysql服务器上进行拉取。可是select ... into outfile只会将文件下拉到本地，我们没有本地的权限，除非找运维。为此，我们换了一种思路，使用select语句，模拟select ... into outfile语句，具体做法如下：

(1) 将数据select到本地，这里需要注意一件事，order by后面应该跟一个索引，这样可以避免排序操作。我进行过测试，如果不显示制定order by index, 下拉所用的时间会明显增加。

mysql -h 192.168.9.10 -u root -p -e "select * from edu.olog order by idd limit 100000;" > /home/sun/data.txt

(2) 使用sed命令移除输出中的第一行

sed -i '1d' /home/sun/data.txt

(3) 对远程数据库表格调用show create table指令得到表格的创建指令。在本机mysql的一个数据库中调用上述的创建表格的指令，这里可能需要做修改引擎一类的操作, 例如使用myisam作为表格的引擎，这样对于我们的加载任务来说，load data的速度明显更快。然后，对生成的文件调用load data记载到本机数据库， 不过没有fields, lines一类的后缀。

这样，我们就可以对本机表格调用select ... into outfile生成对应的csv文件。

我们对十万条数据进行了测试，测试的结果显示，加载速度大约每秒1千多，速度比之前几千条时略慢。

 

我们已经知道了目标，也知道了当前的状态，那么下一步就是优化。首先，我查找了一些资料，这里以mysql8.0为例。首先查看了load data的文档，具体网页为：https://dev.mysql.com/doc/refman/8.0/en/load-data.html。考虑到load data类似于insert，我又在优化的相关模块中进行查找，大的目录为：https://dev.mysql.com/doc/refman/8.0/en/optimization.html（优化），紧接着查找的网页是：https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html（插入优化）， 根据这个网页的提示，最后查看的网页是：https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html（优化innodb表格的加载大量数据）。根据这里面的提示，我对我自己构建的测试数据进行了测试，使用的优化方法如下：

（1）临时修改自动提交的方式：

set autocommit=0;
... sql import statements ...
commit;

 (2)临时取消unique索引的检查：

set unique_checks=0;
... sql import statements ...
set unique_checks=1;

这里简单介绍一下，我的测试使用表格和数据，我的create table如下：

create table `tick` (
  `id` int(11) not null,
  `val` int(11) default null,
  primary key (`id`)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci

行数为1775232，插入时间大约在十几秒。在测试过程中，因为设计频繁的清空表格，清空表格需要花好几秒钟的时间，建议做类似操作的可以考虑，先drop table，然后再create table，这样速度会明显更快。按照在我的电脑上的测试结果，两者基本没有什么区别，大约都在18秒左右。使用的mysql语句为：

date; mysql -u root -pmysql -e "load data infile './tick.txt' into table test.tick;";date;

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0;load data infile '/home/sun/tick.txt' into table test.tick;set unique_checks=1;set autocommit=1;";date;

为了确保我的设置生效，我检查了status输出：

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0; show variables like '%commit';set unique_checks=1;set autocommit=1;";date;

date; mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0; show variables like 'unique_checks';set unique_checks=1;set autocommit=1;";date;

输出结果如下：

autocommit为off, unique_checks为off。

其实我还设置过很多配置，例如插入缓冲区大小一类的，其中比较有效的是innodb_flush_log_at_trx_commit设置为2，对于我们这个问题，这个设置是可以考虑的。

我一直有一个疑问，就是说，load data是否为多线程运行的，按照我以往使用mysql的经验，和《高性能mysql》对load data的论断（远比insert快），如果多线程加载，不至于速度如此之慢。后来，我尝试使用mysqlimport，经过查看mysqlimport上面指定的--use-threads为多线程读取文件，以及在使用mysqlimport加载时，cpu利用率最多只有百分之两百多一点，这个让我觉得很有可能是单线程执行的。通过在运行load data的同时，调用show full processlist，可以清楚的看到，load data是单线程运行的。结果如下：

*************************** 1. row ***************************
     id: 4
   user: event_scheduler
   host: localhost
     db: null
command: daemon
   time: 2835
  state: waiting on empty queue
   info: null
*************************** 2. row ***************************
     id: 17
   user: root
   host: localhost
     db: test
command: query
   time: 0
  state: starting
   info: show full processlist
*************************** 3. row ***************************
     id: 39
   user: root
   host: localhost
     db: null
command: query
   time: 13
  state: executing
   info: load data infile '/home/sun/tick.txt' into table test.tick
3 rows in set (0.00 sec)

由上可以看出，load data是单线程执行的。

我知道，使用多线程执行，会是一个很好的办法，只是，我还是有想法提高单线程的效率，我想到了set profiling. 通过在一个session中设置set profiling = 1; 可以查看一个语句详细的时间消耗。我使用了如下的mysql语句：

set profiling=1;
load data infile '/home/sun/tick.txt' into table test.tick;
show profiles;
show profile for query 1;

可以得到如下的结果：

+----------------------+-----------+
| status               | duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000016 |
| opening tables       |  0.001377 |
| system lock          |  0.000024 |
| executing            | 17.476604 |
| query end            |  0.233398 |
| closing tables       |  0.000045 |
| freeing items        |  0.000039 |
| cleaning up          |  0.000047 |
+----------------------+-----------+
可见执行时间占据了最长的时间，而执行时间应该主要是cpu密集型的（这个我没有足够的把握），从cpu的使用来看，这个论断应该是比较合理的。对于一些比较复杂的问题，我不建议这样分析，当然，这里也可以考虑不这样分析，可以借鉴《高性能mysql》中的做法，参考如下的脚本：

#!/bin/sh

interval=5
prefix=$interval-sec-status
runfile=/home/sun/benchmarks/running
mysql -e 'show global variables' >> mysql-variables
while test -e $runfile; do
    file=$(date +%f_%i)
    sleep=$(date +%s.%n | awk "{print $interval - (\$1 % $interval)}")
    sleep $sleep
    ts="$(date +"ts %s.%n %f %t")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $prefix-${file}-status
    mysql -e 'show global status' >> $prefix-${file}-status &
    echo "$ts $loadavg" >> $prefix-${file}-innodbstatus
    mysql -e 'show engine innodb status\g' >> $prefix-${file}-innodbstatus &
    echo "$ts $loadavg" >> $prefix-${file}-processlist
    mysql -e 'show full processlist\g' >> $prefix-${file}-processlist &
    echo $ts
done
echo exiting because $runfile does not exist

调整中间的时间，打印出详细的信息，进行分析。我这里就没有做这件事。

下面考虑了多线程处理，查看select ... into outfile的文件，即tick.txt可知，文件中的每行对应于表中的一条数据，想把文件切分，可以使用wc -l得出行数，然后使用head与tail得到两个文件，然后对这两个文件再次使用head和tail，得到测试用的四个文件。进行这个测试，我写了很简单的两个shell脚本：

#!/bin/bash
date;mysql -u root -pmysql -e "load data infile '/home/sun/$1' into table test.tick;";date;
exit 0;

#!/bin/bash
./mysql-load.sh data1 &
./mysql-load.sh data2 &
./mysql-load.sh data3 &
./mysql-load.sh data4 &
exit 0;

通过测试，可以发现加载这些文件所用的时间由18秒降低到10秒。我还测试了加载两个文件，所用时间由18秒降低到12.5秒，可见多线程加载可以明显提高加载速度。因为我的电脑是4线程的，所以我决定使用真正的测试数据进行测试。

按照我们之间出现的一个小插曲，发现使用myisam的插入速度会明显快于innodb，使用myisam插入速度可以达到每秒八千左右。我的同事认为这个可以满足他的需求，所以就采用了myisam。那时，我的同事发现他的插入速度明显我之前测试的要快，希望我能找出原因，基于没有mysql没有进行什么特殊的配置，他的电脑配置和我的电脑配置基本一致，我觉得应该是表格的问题，可能性最大的就是引擎，后来发现他无意间使用了myisam作为存储引擎。关于两个电脑配置的差别，可以通过show variables将配置变量导入到文件中进行对比得到，这个属于后话。既然我的同事觉得那样可行，我就没有进一步测试多线程的效果，只是这个经历可以记录下来，留以后借鉴。