今天整理的几个知识点分享
今天整理的几个知识点分享
//
01
远程导出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权限。
02
关于sed的几个命令
去掉以每一行最开始的空格:
sed 's/^ *//' hello.txt > hello.txt
删除以某个单词开头的一行:
sed '/^word/d'
将最后一行的逗号改为半括号+分号（这个功能在MySQL建表语句的处理上有用）：
sed '$s/\(.*\),/\1);/'
其中:
$代表最后一行。
.*表示贪心匹配，就是匹配结果最长的那种情况
，表示匹配的字符是逗号
);表示要用);来代替逗号
\斜杠表示符号转义
\1是固定写法
今天看到一篇文章将这个命令讲的比较好，给大家推荐一下，有兴趣可以看看:
https://www.linuxprobe.com/linux-sed-command.html
03
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 )好，因为耗费的内存空间比较少，避免了冗长的文件排序过程。应该还有更好的方法