MYSQL练习

梦里人
358 阅读9分钟

1.满足 WHERE 条件的数据有10W行，请问这个时候使用 LIMIT 1 来进行查询时，需要扫描多少行数据？为什么？

首先我们先往一个表里添加好10w条数据，这个具体实现就不细说了。。。

1.1然后我们先试试没有用limit 1来查询数据

select name from test as t where name = 'wangwu';

image-20220523141137332

我们可以看到这里直接查询了10w条数据，44ms扫描时间，获取耗时16ms

1.2添加了limit 1，这里的limit 1相当于 limit 1(起始位置),1(条数);

select name from test as t where name = 'wangwu' limit  1;

image-20220523141513026

我们可以看到这里只扫描了1条数据，耗时1ms。

这里我了解到的是：

当有limit存在时，查询的顺序就有可能发生变化，这时并不是从数据库中先通过where过滤再排序再limit。

而是先从name里面拿出1条数据去跟where中的name = "wangwu"去匹配，如果匹配不上，会继续拿出下一条去匹配，直到匹配到为止。

2.判断在不同的 SQL 语句中使用 AS 别名是否正确？为什么？

SELECT order_number AS order_no,
       SUM(price * quantity) AS amount
FROM orderdetails
WHERE order_no = '001'
GROUP BY order_no
HAVING total > 60000;

这里order_number起了个order_no别名，但在where、GROUP BY语句后面使用了别名去操作，这是不可以的。

因为这里的执行顺序是： 1. from 2. where 3. group by 4. select

你在select起了别名，但是在where、GROUP BY语句执行的时候是找不到的。

3.如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1，那肯定是会报“不存在这个列”的错误：“Unknown column 'a' in 'where clause'”。那么这个错误是在我们上面提到的哪个阶段报出来的呢？

来，我们看看下面的这张图，就可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

image-20220523151026152

首先可以是先对我们的sql语句进行语法分析，
  1. MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。
  2. 它也要把 字符串“t” 识别成 "表名 t"
  3. 把 字符串"K" 识别成 "K列 "

答案是：分析器阶段，在分析器阶段解析器会解析sql语句生成一颗解析树，判断表时候存在，列字段是否存在等。

5.为什么要使用数据库连接池？连接池如何保活？

提供连接池与非连接池的性能验证结果。

数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。

如何保活，目前还不知道。。。

6.以下两条SQL，假设字段都不存在索引的前提下：

  1. 哪个性能更好？为什么？

  2. 这两条语句的执行过程。

    select a from t group by a order by null;
select distinct a from t;

    执行过程说明以及原理说明，explain分析截图。

我的理解是第一句sql指令，这里的group by做分组查询功能，它默认会排序，所以我们要不给它进行排序，提高它的性能，还可以在group by 后面的字段加索引，尽量只使用内存临时表，使用SQL_BIG_RESULT，这里推荐去看一篇文章zhuanlan.zhihu.com/p/384840662。

在语义相同，有索引的情况下：

group by和distinct都能使用索引，效率相同。因为group by和distinct近乎等价，distinct可以被看做是特殊的group by。

在语义相同，无索引的情况下：

distinct效率高于group by。原因是distinct 和 group by都会进行分组操作，但group by在Mysql8.0之前会进行隐式排序，导致触发filesort，sql执行效率低下。但从Mysql8.0开始，Mysql就删除了隐式排序，而我们题目这里还主动给它取消掉了排序，所以，此时在语义相同，无排序，无索引的情况下，group by和distinct的执行效率也是近乎等价的。

7.什么样的表，适合使用查询缓存？为什么？

举例具体场景设计以及原理说明。

在建立连接后就可以执行select查询了，以下面的查询为例

select * from test as t where t.id =10

  • MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句；

  • 之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中，key 是查询的语句，value 是查询的结果；如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端

  • 如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

    为什么尽量不使用查询缓存

  • 如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高，但是查询缓存实际上不建议开启使用。

  • 这是因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，因此对于更新压力大的数据库来说，查询缓存很容易失效，命中率非常低。

    什么样的表适合使用查询缓存？
    静态表or更新频率极低的表（比如系统配置表）
    如何设置查询缓存的开启和关闭 - 按需使用查询缓存
    1. 可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存
    2. 对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：
    mysql> select SQL_CACHE * from T where ID=10；

8.以下 SQL 会报什么错？是在分析器哪个步骤抛出的错误？

elect id, name from `user` where `id` = 10;

对原理进行说明。

这里很明显，select没写对，所以是在select语法分析阶段出错了。

9.对以下 SQL 进行优化器分析，会出现哪些情况？

select * from t1 join t2 on t1.c = 10 and t2.d = 20;

设计不同场景以及数据量进行具体分析，对原理进行说明，提供explain分析截图。

image-20220524160842726

image-20220524160811213

image-20220523233039918

image-20220524160646892

我这里的表一满足num = "19"的只有15行，表2满足Column1 = 10的有30行，然后进行联表查询时，竟然查出来了450行，可见出现了笛卡尔积现象，查询出来的数据：15*30=450行。

10.如下表，请编写SQL，完成以下题目：

  1. 从每个分组中找到分数排名第一的用户；

    select id,user,`group`,max(score) from newtable_2  group by `group` order by null;

    image-20220524001152043

  2. 从每个分组中找到分数排名前二的用户；

    SELECT
        id,user,group_name,score 
    FROM
        (
        SELECT
            t.*,
        IF
            ( t.group_name = @GROUP_NAME, @ROWNUM := @ROWNUM + 1, @ROWNUM := 1 ) ROWNUM,
            @GROUP_NAME := t.group_name group_name2 
        FROM
            newtable_2  t,
            ( SELECT @ROWNUM := 0, @GROUP_NAME := NULL ) r 
        ORDER BY
            t.group_name,
            t.score DESC 
        ) temp 
    WHERE
        temp.ROWNUM <= 2

    image-20220524095702116

  3. 获取每个每组下最后两名分数的平均值；

    select id,group_name, avg(score) from (SELECT
        *
    FROM
        (
        SELECT
            t.*,
        IF
            ( t.group_name = @GROUP_NAME, @ROWNUM := @ROWNUM + 1, @ROWNUM := 1 ) ROWNUM,
            @GROUP_NAME := t.group_name group_name2 
        FROM
            newtable_2  t,
            ( SELECT @ROWNUM := 0, @GROUP_NAME := NULL ) r 
        ORDER BY
            t.group_name,
            t.score DESC 
        ) temp 
    WHERE
        temp.ROWNUM >= 2) t1
       group by t1.group_name;

    image-20220524102115057

image-20220523234708733

使用对应岗位语言/MySQL进行建表和插入数据，使用查询SQL以及结果截图。

11.如果用 left join 的话，左边的表一定是驱动表吗？请举例说明。

提供具体SQL以及对应explain分析截图，进行原理说明。

答案是肯定的，left join的意思是左外连接，用于两个以上的表去匹配相关对应的信息，以左边的表为主表。

首先创建一个demo1表，结构如图：

image-20220524110832758

创建一个demo2表，结构如图：

image-20220524110916885

看看我们不使用外连接，去匹配表一、表二中age相同的数据，

select d1.age, d1.name,d2.name from demo1 as d1 join demo2 as d2 on d1.age=d2.age;

image-20220524111026063

这里表一、表二的age为null空，并不会被匹配出来，可以看出两张表是平行关系，没有主次之分，那我们如果使用外连接呢？

select d1.age, d1.name,d2.name from demo1 as d1 left join demo2 as d2 on d1.age=d2.age;

image-20220524111326470

可以看到这里的age为空了，还可以匹配上，而且表一的name不为空，表二的name为空，还可以可以匹配上，可见，这里的left join是一左边的表为主。

课外题

课程中，详细讲述了一条查询SQL是如何执行的，那么，一条更新SQL又是如何执行呢？

一条查询SQL是如何执行的:

image-20220524113434560

1.  客户端发送一条查询给服务器。
2.  服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则 进入下一阶段
3.  服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
4.  MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
5.  将结果返回给客户端

一条更新SQL又是如何执行: image.png

2.  Server层向InnoDB存储引擎要xxx条件的这条记录；
3.  存储引擎先从缓存中查找这条记录，有的话直接返回，没有则从磁盘加载到缓存中然后返回；
4.  Server层执行器修改这条记录的xxx字段值；
5.  存储引擎更新修改到内存中；
6.  存储引擎记录redo日志，并将状态设置为​`​prepare​`​状态；
7.  存储引擎通知执行器，修改完毕，可以进行事务提交；
8.  Server先写了个binlog；
9.  Server提交事务；
10.  存储引擎将redo日志中和当前事务相关的记录状态设置为​`​commit​`​状态。

这里推荐一篇文章:www.cnblogs.com/chanmufeng/…

MySQL 通过哪个参数可以设置不使用查询缓存？
可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存
avatar
文章
阅读
粉丝