今天整理的几个知识点分享

AsiaYe

发布于 2020-03-11 18:08:41

6580

发布于 2020-03-11 18:08:41

文章被收录于专栏：DBA随笔

今天整理的几个知识点分享

远程导出MySQL为CVS文件

在使用MySQL的过程中，我们知道可以通过mysqldump或者select into outfile的方法将MySQL中的数据给转换成csv文件，这里简单介绍一下：

如果我们使用mysqldump来导出CVS格式的文件，那么我们需要使用下面的命令：

mysqldump  -uuser  -ppassword  -Pport -hlocalhost  --no-create-info  --tab /backupfile_dir/  --databases db_name --tables table_name --fields-enclosed-by='\"' --fields-terminated-by=','

其中，--no-create-info参数是指不需要导出表结构文件，

--tab的意思是使用tab分隔的方法来进行创建文件，该参数后面需要跟生成的文件路径。该命令会生成两个文件，一个是.sql文件，一个是.txt文件，其中.sql文件保存表的结构，如果定义了--no-create-info,则该文件的大小为0Kb，什么内容都没有，.txt文件是数据文件，里面包含了我们导出的数据。

如果我们用select into outfile的方法来导出cvs格式的文件，那么我们需要使用如下命令：

select * from table_name into outfile '/tmp/table_name.cvs' fields terminated by ',' optionally enclosed by '\"';

注意，这两种方法有一个缺点，那就是:导出的数据文件只能在MySQL服务器所在的机器，不能远程传输。

官方文档针对mysqldump方法的参数--tab的说明如下:

select into outfile的说明如下:

这么一看，缺点很明显，不能远程传输这个CVS文件，如果我们需要远程得到这个文件，怎么办呢？还有没有别的方法？

MySQL解决不了的问题，Linux系统能解决:

我们可以使用mysql -uuser_name -ppassword -Pport -hlocalhost -D db_name -Ne "select * from table " > /tmp/table.cvs

这种方法去将所有的数据导出到CVS文件，为了格式化成逗号分隔符的格式，可以配合sed这个Linux命令，如下：

mysql -uuser_name -ppassword -Pport -hlocalhost

-D db_name -Ne "select * from table " |

sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/table.cvs

其中sed部分是对结果利用正则的办法进行格式化。这样就成功的将数据导入到本地了。而且整个过程只需要对表的select权限，而不需要file权限。

关于sed的几个命令

去掉以每一行最开始的空格:

sed 's/^ *//' hello.txt > hello.txt

删除以某个单词开头的一行:

sed '/^word/d'

将最后一行的逗号改为半括号+分号（这个功能在MySQL建表语句的处理上有用）：

sed '$s/$.*$,/\1);/'

其中:

$代表最后一行。

.*表示贪心匹配，就是匹配结果最长的那种情况

，表示匹配的字符是逗号

);表示要用);来代替逗号

\斜杠表示符号转义

\1是固定写法

今天看到一篇文章将这个命令讲的比较好，给大家推荐一下，有兴趣可以看看:

https://www.linuxprobe.com/linux-sed-command.html

count (distinct *)的SQL语法重写

这个是今天在工作中的一个真实的案例，某个业务SQL使用了count(distinct 字段)的方法来统计某个场景中的人数，发现一直报内存不足的问题。我大概模拟了一个相同的测试数据，说明一下这个SQL，如下：

首先来看表中的数据:

mysql> select * from test0;
+------+------+-------+
| id   | age  | score |
+------+------+-------+
|    1 |    2 |     3 |
|    2 |    2 |     2 |
|    2 |    2 |     3 |
|    3 |    2 |     3 |
|    2 |    2 |     3 |
+------+------+-------+
5 rows in set (0.00 sec)

如果我们要求的是统计age和score这个组合，到底有几种？每种有几条记录？这个可能比较容易，我们针对age和score做一个分组聚合就行，SQL如下：

mysql> select count(*),age,score from test0 group by age,score;
+----------+------+-------+
| count(*) | age  | score |
+----------+------+-------+
|        1 |    2 |     2 |
|        4 |    2 |     3 |
+----------+------+-------+
2 rows in set (0.00 sec)

这样的结果说明，age和score的组合有两种情况：

分别是age=2，score=2以及age=2，score=3

这两种情况中，第一种有一条记录符合，第二种有4条记录符合。

我们可以看到，第二种age=2，score=3的组合中，一共有4条记录：

mysql> select * from test0 where age=2 and score=3;
+------+------+-------+
| id   | age  | score |
+------+------+-------+
|    1 |    2 |     3 |
|    2 |    2 |     3 |
|    3 |    2 |     3 |
|    2 |    2 |     3 |
+------+------+-------+
4 rows in set (0.00 sec)

这4条记录中有2条是重复的，就是id=2的记录有2条。

如果此时需要对整个表中以age和score进行分组，然后统计每个组内不重复的id值的记录分别有多少条？那么我们如何操作，来看业务SQL：

mysql> select count(distinct id),age,score from test0 group by age,score;
+--------------------+------+-------+
| count(distinct id) | age  | score |
+--------------------+------+-------+
|                  1 |    2 |     2 |
|                  3 |    2 |     3 |
+--------------------+------+-------+
2 rows in set (0.00 sec)

这个SQL使用了count(distinct )的方法，乍一看满足了我们的需求，来看这个SQL的执行计划（为了避免group by操作后的排序对执行计划进行干扰，我特意加了order by null语法来分析）:

mysql> explain select count(distinct id),age,score from test0 group by age,score order by null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test0
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

可以看到，这个SQL使用了filesort的排序方法，这种排序方法，如果数据量比较小，可能没有影响，但是线上的库数据量很大，这样的一条SQL就导致MySQL报内存不足的错误。

当时为了快速解决问题，就将上面的count(distinct )的方法进行了修改，改成了下面的方法:

先进行去重，然后再统计:

mysql> select count(*),age,score from (select distinct id,age,score from test0) tmp group by tmp.age,tmp.score;
+----------+------+-------+
| count(*) | age  | score |
+----------+------+-------+
|        1 |    2 |     2 |
|        3 |    2 |     3 |
+----------+------+-------+
2 rows in set (0.00 sec)

这个SQL的执行计划如下:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using temporary
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: test0
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using temporary
2 rows in set, 1 warning (0.00 sec)

虽然引入了子查询，但是由于在实际的查询语句中有where条件，所以子查询中查出来的记录数比较有限，在这种情况下再去做聚合，会比直接在表上使用count(distinct )好，因为耗费的内存空间比较少，避免了冗长的文件排序过程。应该还有更好的方法

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-03-03，如有侵权请联系 cloudcommunity@tencent.com 删除

linux