MYSQL的内连接和外连接的区别

答案

内连接（INNER JOIN）和外连接（OUTER JOIN）是 SQL 中常用于多表查询的两种连接方式，它们的核心区别在于是否保留不匹配的记录。
内连接只会保留两个表都存在的列，而外连接分为两种，一种是左连接和右连接，分别会保留下左表或者右表的的列

类型语义
INNER JOIN只要匹配的，不匹配都不要
LEFT JOIN以左表为主，不匹配补 NULL
RIGHT JOIN以右表为主，不匹配补 NULL

什么是SQL注入

答案

SQL 注入 是一种将恶意 SQL 语句注入应用程序输入，从而欺骗服务器执行攻击者自定义 SQL 命令的安全漏洞。
例如: 后端这么写一条SQL

1
SELECT * FROM users WHERE username = admin AND password = password;

攻击者输入：
用户名：’ OR ‘1’=’1
密码：任意

拼接后的SQL:

1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'xxx';

‘1’=’1’ 永远为真，所以这个 SQL 返回了所有用户，相当于绕过了登录验证。

一般情况下 mybatis会处理参数拼接的情况

UPDATE的子查询特性

在 UPDATE 中不能在子查询里再次引用同一张表。 例如：

1
2
UPDATE  product_collect set salePlats ="marketing_saleplat_swan" 
where uuid in(select uuid from product_collect where productType in("05","08","09"))

  • 为什么不可以呢？

    1. MySQL 的执行方式是“一边读一边写”
      • 而 MySQL 在执行 UPDATE 时是这样工作的：按顺序扫描表 → 找到符合条件的行 → 更新它 。但同时，你又要求 MySQL 在更新过程中，再去读同一张可能正在被更新的表。
    2. MySQL 的扫描和更新顺序会破坏子查询结果
      • 由于 UPDATE 会修改表内容，子查询读取的数据可能：
        • 被 UPDATE 刚刚修改过
        • 还没被更新
        • 扫描顺序变化
        • 甚至行可能被锁住
          这会导致执行结果 不确定、不稳定、不安全。

  • 解决方法：

  1. 用子查询包一层
    1
    2
    3
    4
    5
    6
    7
    8
    9
    UPDATE product_collect
    SET salePlats = 'marketing_saleplat_swan'
    WHERE uuid IN (
        SELECT uuid FROM (
            SELECT uuid
            FROM product_collect
            WHERE productType IN ('05','08','09')
        ) AS tmp
    );
    原因： 最里面的子查询 会形成一个临时表 tem
  2. 使用临时表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- 1. 创建临时表，保存需要更新的 skuNo
    CREATE TEMPORARY TABLE tmp AS
    SELECT uuid, 'marketing_saleplat_swan' AS newSalePlats
    FROM product_collect
    WHERE productType IN ('05', '08', '09');

    -- 2. 用 JOIN 更新
    UPDATE product_collect pc
    JOIN tmp t ON pc.uuid = t.uuid
    SET pc.salePlats = t.newSalePlats;

    -- 3. 更新完成后删除临时表
    DROP TEMPORARY TABLE tmp;

面试总结

  • 面试官: 怎么查询慢SQL
  • 我:
    • 查看慢查询日志（MySQL）
      • MySQL 的 慢查询日志（slow query log） 会记录执行时间超过 long_query_time 的 SQL 语句，默认是关闭的。
        打开配置文件（如 /etc/my.cnf 或 /etc/mysql/my.cnf）：
        1
        2
        3
        4
        5
        6
        7
        8
        9
        [mysqld]
        slow_query_log = 1                     # 开启慢查询日志
        slow_query_log_file = /var/log/mysql/mysql-slow.log  # 日志保存路径
        long_query_time = 1                   # 超过 1 秒的 SQL 会被记录
        log_queries_not_using_indexes = 1     # 是否记录未使用索引的查询（可选）

           
        SHOW VARIABLES LIKE 'slow_query_log_file'; # 可通过该语句查看

  • 面试官: 如何分析慢SQL的原因
  • 我:
      1. 用 EXPLAIN 分析执行计划 例如
        1
        EXPLAIN SELECT * FROM user WHERE age > 18;
        • 重点看这些字段：
    字段说明
    type访问类型，越靠近 const 越好；ALL 是全表扫描（最差）
    key实际使用的索引
    rows扫描的行数（预估）
    Extra是否出现 Using temporary、Using filesort（表示排序/中间表开销）

    如果看到 type=ALL 且 key=NULL：就说明没有使用索引，SQL 慢就是因为全表扫描！

      1. 查看字段是否有索引
      1. 对应数据库表的数据量
      1. SOL书写
    1
    SHOW INDEX FROM 表名;
      1. 总结
        SQL 慢 ≠ 语句长，通常是没走索引、扫描数据多、排序分页大、锁住了，建议用 EXPLAIN + 慢日志 + PROFILE 三板斧排查，再结合业务改写 SQL 或加索引。
  • 面试官: 如何优化慢SQL

  • 我:

    1. 尽可能走索引
    2. 避免 SELECT *，只取需要字段
    3. 用子查询 + 分页方式代替大范围 Join
    4. 数据量太大，建议分表减少数据量
      总结：大部分情况 都是加索引为主

  • 面试官: mysql假如有一个用户表，有一个字段是name，并且为其创建了非唯一索引，并且当前表中有三条记录都是name=xx,那么开启一个事务后，并且执行select*from这个表where name=’XXX’for upd ate，加的什么锁？ 如果此时又来一个insert into这个表values(….),其中name也是’XXX’,能插入的了嘛?

  • 我：
    问题答案
    FOR UPDATE 加了什么锁？所有 name='XXX' 记录的 排他锁（X 锁），+ 对 name='XXX' 范围的 间隙锁（Gap Lock）
    能不能插入 name=’XXX’ 的记录？❌不能，会被阻塞，因为被间隙锁锁住了（防止幻读）

    • 加锁原因分析:

      • 🔍 关键点：
        • name 是 非唯一索引。所以 WHERE name=’XXX’ 会匹配多条记录。
        • FOR UPDATE 表示当前事务要对匹配的记录加排他锁（X锁）。
        • 在使用非唯一索引时，InnoDB 为了防止幻读，不仅会锁住实际匹配的记录，还会对其所在的索引范围加 间隙锁（Gap Lock）。
          所以: 排他锁+间隙锁

    • 插入失败的原因:
      因为你前一个事务对 name=’XXX’ 的索引范围加了 间隙锁（Gap Lock），插入同一个 name 的值时，会命中这个锁定范围。

    • 扩展: 如果不加 FOR UPDATE 的结果是什么?
      • 答: 如果你不加 FOR UPDATE，只是普通的 SELECT 查询，另一个事务是可以正常 INSERT 插入的，即使插入的是相同的 name = ‘XXX’。
  • 面试官: 有如下三张表
    歌曲表 song (song_id,song_name,singer_id)
    歌曲点唱信息表 song_status (song_id,listen_count)
    歌手表 singer(singer_id,singer_name)
    某歌手下歌曲数为n，这些歌曲点听数为m
    写一个SQL，按照点听数之和m排序，查询m>10000的歌手
    输出格式：singer_id，singer_name，歌曲数，歌曲点听数之和
  • 我：

    我是傻子。面试的时候没写出来

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    -- 选择歌手的ID、姓名、歌曲数、总点听数
    SELECT 
        s.singer_id,                                     -- 歌手ID
        s.singer_name,                                   -- 歌手姓名
        COUNT(DISTINCT so.song_id) AS song_count,        -- 去重后的歌曲数量
        SUM(ss.listen_count) AS total_listen_count       -- 该歌手所有歌曲的点听总数
    FROM 
        singer s                                          -- 从歌手表开始查起，起别名 s
    JOIN 
        song so ON s.singer_id = so.singer_id            -- 连接歌曲表：匹配歌手ID，找出该歌手的所有歌曲
    JOIN 
        song_status ss ON so.song_id = ss.song_id        -- 连接歌曲点听信息表：匹配歌曲ID，获取每首歌的点听数
    GROUP BY 
        s.singer_id, s.singer_name                       -- 按歌手ID和姓名分组，统计每个歌手的数据
    HAVING 
        SUM(ss.listen_count) > 10000                     -- 只保留点听总数大于10000的歌手（聚合结果过滤）
    ORDER BY 
        total_listen_count DESC;                         -- 按点听总数从高到低排序