MySql知识

join 组合两个表

SELECT * FROM A JOIN B a ON A.id = B.user_id;

  • inner join 内连接，返回两个表中都存在的行
  • left join 左连接，返回 A 表中所有的行，即使 B 表中没有对应行
  • right join 右连接，返回 B 表中所有的行，即使 A 表中没有对应行
  • full join 全连接，返回 A 和 B 表中都存在的行，以及 A 表中没有，B 表中有的行，以及 B 表中没有，A 表中有的行

limit 限制返回的行数

SELECT * FROM user LIMIT M, 10;跳过 M 行后返回十条数据
SELECT * FROM user LIMIT 10 OFFSET M

having 过滤分组

1
2
3
4
5
select email from Person
-- group by 会把相同的 email 值归为一组
group by email
-- 返回组里行的数量大于一的邮箱
having count(email) > 1

聚合函数

聚合函数的作用就是就是对有多个名字相同的结果聚合
聚合函数要搭配 group by （字段名） 使用
比如这个按商品名聚合，然后把同名商品的销售额全加在一起作为总的销售额

简单来说就是，根据列名分组，把列里面属性相同的值分成组然后压成一条数据，此时保留或计算的这个属性是根据聚合函数指定的，可以是求和 sum，保留最小值 min，保留最大值 max 统计行数 count 求平均值等等

1
2
3
SELECT product, SUM(amount) AS total_sales
FROM sales
GROUP BY product;

假设有个学生表，保留每个学科的最高分

1
2
3
SELECT subject, MAX(score) AS max_score
FROM students
GROUP BY subject;

聚合函数

  • count() 统计行数
  • sum() 求和
  • avg() 平均值
  • max() 最大值
  • min() 最小值

配套语法

  • group by() 分组
  • having() 过滤分组
  • distinct() 去重

执行顺序

先 from 再 where 最后 select

1
2
3
4
5
6
7
8
9
10
11
12
-- 所以这一段的逻辑是，先连接两个表把部门的值接进来
-- 然后按查的表，逐个员工对比
-- 如果他的工资与当前对比员工部门最高工资一致就返回
select d.name as Department, e.name as Employee, Salary
from Employee e left join Department d
on e.departmentId = d.id
where salary = (
    -- 子查询查询某个部门最高工资
    select max(salary)
    from Employee
    where departmentId = e.departmentId
)

变量声明

语句中只能用常量，不能用表达式

1
2
declare M int;
    set M = N-1;

模糊查询

CONCAT()是拼接字符串的函数，本意为连接
SELECT * FROM user WHERE name LIKE CONCAT('%', #{name}, '%')

#{}和${}

  • 绝大部分都用#{}
  • #{}是预编译参数，可以防止 SQL 注入，MyBatis 会把 #{name} 转换成 JDBC 的 ? 占位符
  • ${}直接字符串替换，MyBatis 直接把 ${name} 替换成你传入的值，原样插入 SQL

MySQL 的索引

  • MySQL 的索引都是 B+树的结构，叶子结点每个都是一个页，然后页内的每一条记录就是真实存储的数据（按主键组织的 B+数存真实数据，其他索引需要回表，回到主键索引来查真实数据）

  • 每个页的页号和页的最小记录主键构建 B+树的非叶子结点，也就是索引（主键索引、聚簇索引）

  • 根据索引查找就是从根节点渐渐二分找，到达叶子结点存储的页，从页内再找对应的记录

  • 对应 MySQL 来说，主键索引一定存在，不设置主键 MySQL 会优先选一个 Unique 键作为主键索引，这个也没有就自己生成一个默认主键DB_ROW_ID

普通索引（二级索引）

  • 实际上是另一个 B+树，不过这个每个叶子结点存的不是数据而是索引列的值(下面的 name 就是) + 主键值
  • 假设给 name 建立一个索引，这次不通过主键排序后二分，而是通过 name 排序（字典序）后二分查找，最后到叶子结点的时候拿到对应记录的主键来找真实数据，这样通过 name 找数据就变得非常快

联合索引

  • 也是 B+树结构，假设为 name 列和 phone 列建立索引，叶子结点存储的就是 name 列，phone 列，和主键值，此时的排序就是先按 name 列排序，name 列相同再按 phone 列排序，直接搜索 phone 是乱序的，最后找到主键值后回表查真实数据

  • 就假设联合索引是（a,b,c），在 a 查完之后，此时所有的相同的 a 的索引对应的 b 才是有序的，才能利用到联合索引去查 b，b 查完才能查 c，不查 a 直接查 b 是无效的，因为 b 整体无序，局部有序（这个局部就是相同的 a 的条件下，这个就是最左匹配原则）

  • 如果只查询 a 的条件时，完全可以直接使用这个联合索引，不需要再单独为 a 建立一个索引，相当与 a 的二级索引（普通索引）

  • 只有当 a 是等于时，b 才能利用到联合索引，只有当 a 和 b 都是等于时，c 才能利用到联合索引

减少或避免回表的操作

索引覆盖（覆盖索引）

  • 索引中已经包含了所有查询的数据，不需要回表的查询方式叫做索引覆盖
  • 假设是为 name 和 phone 建立联合索引，又碰巧查的数据就只有 name 和 phone，此时到叶子结点时就根本不需要回表

索引下推（索引条件下推、ICP）

  • 正常索引的使用是在存储引擎，而条件的过滤是在 Server 层

  • 索引下推就是把条件过滤下推到存储引擎，让其通过索引来完成

  • 假设 name 和 phone 建立联合索引，查询语句是 select * from user where name = '张三' and phone = '%1566%'假设查到的叫张三的有一万个人，而手机号实际符合的只有 1 个人，拿就有 9999 条无关数据而且他们都是回表查出来的

  • 索引下推后直接让存储引擎先从这一万条数据中选出合法的那一条，然后再进行回表，就只需要回表一次

索引失效

  • 违反最左匹配原则
  • 聚合函数不能用索引
  • 表达式不能用索引 where id + 1 = 1100，但是修改一下where id = 1100 - 1就可以用索引了
  • 索引类型为字符串时，用数字搜索会失效；但反过来，索引为数字，用字符串来搜索能用到。因为 MySQL 自动把字符串转换成数字
  • or 前是索引列但之后不是索引列，就不能用索引

但是具体用不用索引还是取决于 MySQL 的优化器，根据开销（cost）来判断，选开销最小的，如果都很大就直接全表扫描了

索引创建原则

  • 不为离散度低的列创建索引，比如性别只有男女
  • 只为搜索、排序或分组的列创建索引
  • 用好联合索引
  • 过长的字段创建前缀索引，该列的前几个字符，避免占用过大空间
  • 频繁更新的列不作为主键或索引
知识
MySql知识
http://www.981928.xyz/2025/11/17/MySql知识/
作者
981928
发布于
2025年11月17日
许可协议