group by having用法

group by是mysql中用来对by后面的单个或者多个字段进行分组的语法，可以根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表，用法比较灵活，常常和where或者having一起用。

group by的语法：

1
2
3
4
select 字段 from 表名 group by 字段;
#带有过滤条件having、wherer的用法：
select 字段 from 表名 where 条件 group by 字段;
select 字段 from 表名 group by 字段 having 条件;

注意：where 是先过滤，再分组；having 是分组后再过滤

Having子句(是对分组之后的数据进行过滤，所以使用having时必须用group by先分组)，Having中的组函数可以不是select中的组函数
where子句和having子句都是用来筛选条件用的，不同的是，where子句只能对分组前的属性进行删选，而having子句只能对分组后的属性进行筛选。

group by经常会配合聚合函数一起用
比如常用的聚合函数：count() , sum() , avg() , max() , min()，可以这样用

1
select 字段,count(字段2),sum(字段3) from 表名 group by 字段;

select后面的字段必须包含在group by后面字段之内

1
select 字段1,字段2 from 表名 group by 字段1,字段2,字段3

select后有聚合函数例外：

1
select 字段1,count(字段2),sum(字段3) from 表名 group by 字段1;

join连接

mysql种共有7种join()
但实际上只有4种主要的:左外连接,右外连接,内连接,全外连接

内连接

(inner join 可简写为join) 就是交集,也就是两张表的共同数据;

1
select * from a [inner] join b on a.key = b.key

左外连接

1
select * from a left join b on a.key = b.key

右外连接

1
select * from a right join b on a.key = b.key

全外连接

1
select * from a full outer join b on a.key = b.key;

注意:上面的sql只在Oracle中适用,MYSQL中不支持,因此在MYSQL中想要实现全外连接,得用联合查询(union)实现

什么是联合查询(union)

简单来说就是把两个sql语句的结果取并集(也就是去重复值)

1
2
3
select * from a left join b on a.key = b.key 
union
select * from a right join b on a.key = b.key

慢查询日志

慢查询日志（slow query log）:
用来记录在 MySQL 中执行时间超过指定时间的查询语句，在 SQL 优化过程中会经常使用到。
通过慢查询日志，我们可以查找出哪些查询语句的执行效率低，耗时严重。

开启慢查询日志

出于性能方面的考虑，一般只有在排查慢SQL、调试参数时才会开启，默认情况下，慢查询日志功能是关闭的。
可以通过以下命令查看是否开启慢查询日志：

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'slow_query%';
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_query_log      | OFF                                                    |
| slow_query_log_file | /usr/local/mysql/data/iZ2zebfzaequ90bdlz820sZ-slow.log |
+---------------------+--------------------------------------------------------+

通过如下命令开启慢查询日志后，我发现 iZ2zebfzaequ90bdlz820sZ-slow.log 日志文件里并没有内容啊，可能因为我执行的 SQL 都比较简单没有超过指定时间。

1
2
mysql>  SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected

指定超过时间设定

上边提到超过 指定时间 的查询语句才算是慢查询，那么这个时间阈值又是多少嘞？我们通过 long_query_time 参数来查看一下，发现默认是 10 秒。

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

这里我们将 long_query_time 参数改小为 0.001秒再次执行查询SQL，看看慢查询日志里是否有变化。

1
2
mysql> SET GLOBAL long_query_time=0.001;
Query OK, 0 rows affected

bin log(归档日志)

bin log是一种数据库Server层（和什么引擎无关），以二进制形式存储在磁盘中的逻辑日志。
bin log记录了数据库所有DDL和DML操作（不包含 SELECT 和 SHOW等命令，因为这类操作对数据本身并没有修改）。

默认情况下，二进制日志功能是关闭的。可以通过以下命令查看二进制日志是否开启：

1
2
3
4
5
mysql> SHOW VARIABLES LIKE 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | log_bin       | OFF   |

bin log也被叫做归档日志，因为它不会像redo log那样循环写擦除之前的记录，而是会一直记录日志。
一个bin log日志文件默认最大容量1G（也可以通过max_binlog_size参数修改），单个日志超过最大值，则会新创建一个文件继续写。

1
2
3
4
5
6
7
8
mysql> show binary logs;
+-----------------+-----------+
| Log_name        | File_size |
+-----------------+-----------+
| mysq-bin.000001 |      8687 |
| mysq-bin.000002 |      1445 |
| mysq-bin.000003 |      3966 |
| mysq-bin.000004 |       177 |

bin log日志的内容格式其实就是执行SQL命令的反向逻辑，这点和undo log有点类似。
一般来说开启bin log都会给日志文件设置过期时间（expire_logs_days参数，默认永久保存），要不然日志的体量会非常庞大。

1
2
3
4
5
6
7
8
9
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set
mysql> SET GLOBAL expire_logs_days=30;
Query OK, 0 rows affected

bin log主要应用于MySQL主从模式（master-slave）中，主从节点间的数据同步；以及基于时间点的数据还原。

主从同步

通过下图MySQL的主从复制过程，来了解下bin log在主从模式下的应用

用户在主库master执行DDL和DML操作，修改记录顺序写入bin log;
从库slave的I/O线程连接上Master，并请求读取指定位置position的日志内容;
Master收到从库slave请求后，将指定位置position之后的日志内容，和主库bin log文件的名称以及在日志中的位置推送给从库;
slave的I/O线程接收到数据后，将接收到的日志内容依次写入到relay log文件最末端，并将读取到的主库bin log文件名和位置position记录到master-info文件中，以便在下一次读取用;
slave的SQL线程检测到relay log中内容更新后，读取日志并解析成可执行的SQL语句，这样就实现了主从库的数据一致;

基于时间点还原

我们看到bin log也可以做数据的恢复，而redo log也可以，那它们有什么区别？

  • 层次不同：redo log 是InnoDB存储引擎实现的，bin log 是MySQL的服务器层实现的，但MySQL数据库中的任何存储引擎对于数据库的更改都会产生bin log。
  • 作用不同：redo log 用于碰撞恢复（crash recovery），保证MySQL宕机也不会影响持久性；bin log 用于时间点恢复（point-in-time recovery），保证服务器可以基于时间点恢复数据和主从复制。
  • 内容不同：redo log 是物理日志，内容基于磁盘的页Page；bin log的内容是二进制，可以根据binlog_format参数自行设置。
  • 写入方式不同：redo log 采用循环写的方式记录；binlog 通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上。
  • 刷盘时机不同：bin log在事务提交时写入；redo log 在事务开始时即开始写入。
    bin log 与 redo log 功能并不冲突而是起到相辅相成的作用，需要二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。
返回 MySql 系列