数据库（下）

2.3.8 对MySQL的慢查询了解么？请简单讲讲。

【考点映射】

【出现频度】★★★

【难度】★★☆

【参考答案】

慢查询，顾名思义，执行很慢的查询。一般指超过 long_query_time 参数设定的时间阈值（默认10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。

慢查询日志默认是不开启的，需要手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

开启慢查询日志的方式：

1. 【临时开启】通过SQL语句配置全局变量实现（重启MySQL后失效）

# slow_query_log 默认值是 OFF，当置为1时，开启慢查询
set global slow_query_log = 1;

2. 【永久开启】修改MySQL配置文件my.cnf

slow_query_log = 1
slow_query_log_file = /tmp/mysql_slow.log

修改慢查询记录的时间阈值：

# long_query_time 默认是10秒
set global long_query_time=15

慢查询日志分析工具mysqldumpslow

# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/run/mysqld/mysqld-slow.log
# 按照扫描行数最多的
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log

---

2.3.9 分库分表的好处是什么？谈谈你对分库分表的了解。

【考点映射】

【出现频度】★★

【难度】★★★

【参考答案】

当业务发展到一定阶段时，可能会出现以下情形：

这个时候，就需要进行分库分表，来提升查询性能。

分库分表的拆分思路

1. 垂直拆分

根据数据库里面数据表的相关性进行拆分。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示。

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。

---

2.3.10 手撕SQL（MySQL数据库操作实战）。

【考点映射】

【出现频度】★★★★★

【难度】★★★☆

【题目要求】

给定四个表: student（学生表）、teacher（老师表）、course（课程表）、sc（成绩表）

根据以下题目要求，写出SQL语句。

【参考答案】

1. 查询每个学生的学号、姓名和每门课的成绩

SELECT 
 student.sid AS 学生ID, 
    student.sname AS 学生姓名, 
    (SELECT course.cname FROM course WHERE course.cid=sc.cid) AS 课程名称, 
    sc.score AS 成绩 
FROM student
LEFT JOIN sc
ON student.sid = sc.sid；

SQL执行结果：

SELECT sid, AVG(score) 
FROM `sc` 
GROUP BY sid 
HAVING AVG(score) > 60;

SQL执行结果：

SELECT student.sid,student.sname
FROM student  
WHERE sid NOT IN 
 (
     SELECT DISTINCT(sc.sid) 
     FROM sc, course, teacher 
     WHERE  sc.cid=course.cid AND teacher.tid=course.tid AND teacher.tname='叶平'
    ); 

SQL执行结果：

SELECT sid,sname 
FROM student 
WHERE sid NOT IN 
(
    SELECT student.sid FROM student AS s,sc WHERE s.sid=sc.sid AND score>60
); 

SQL执行结果：无

5. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT cid AS 课程ID, MAX(score) AS 最高分, MIN(score) AS 最低分
FROM sc
GROUP BY cid；

SQL执行结果：

---

2.3.11 什么是内连接、左连接、右连接、外连接和全连接查询？

【考点映射】

【出现频度】★★★★☆

【难度】★★☆

为了更好的说明，这里先创建两个表：tb_staff 和 tb_staff_salary。

tb_staff 是员工表，tb_staff_salary 是员工薪资表。

内连接（INNER JOIN）

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

JOIN ... ON 是 JOIN OUTER ... ON 的简写，以下三种写法等价：

# 写法一： INNER JOIN ... ON
SELECT 
    tb_staff.name AS name, 
    tb_staff.part AS part, 
    tb_staff_salary.salary AS salary 
FROM tb_staff 
INNER JOIN tb_staff_salary 
ON tb_staff.name = tb_staff_salary.name

# 写法二：JOIN ... ON
SELECT 
    tb_staff.name AS name, 
    tb_staff.part AS part, 
    tb_staff_salary.salary AS salary 
FROM tb_staff 
JOIN tb_staff_salary 
ON tb_staff.name = tb_staff_salary.name

# 写法三：FROM tb_a, tb_b WHERE tb_a.name = tb_b.name 
SELECT 
    tb_staff.name