当前位置： 首页 > article >正文

深入理解MySQL慢查询优化(2) -- SQL的执行流程

article 2024/11/15 17:51:00

要优化一条SQL语句，要先理解SQL操作的执行流程

1. 不同SQL操作的执行流程

1.1 order by

order by用于排序，如果用于排序的列上没有索引，就需要把整张表加载进内存进行排序，非常耗时。如果有索引，因为B+树存储的数据本就是有序的，所以MySQL可以通过索引直接顺序读取即可，非常高效

1.2 join

前面以及了解过join是通过两层for循环实现。

如果内存表没有索引，那么对于外层表的每一行数据都要进行一次全表扫描，如果有索引，内层表可以使用索引快速定位到匹配的记录。

1.3 where

没有索引只能对表进行扫描，逐一判断是否满足条件，有索引则可以快速定位到满足条件的数据

1.4 group by

分组操作一般有两种实现方案：hash和排序，MySQL使用的是排序：

如果没有索引:

  1. 新建临时表
  2. 扫描数据表，并按插入排序的方式插入临时表，这样就保证了group by列的值相同的会排列在一起
  3. 在临时表上做处理（使用聚合函数，max/min/count/sum/avg）

如果用于分组的列上有索引，那么前两部就直接省略了 

1.5 distinct

在没有索引的情况下，MySQL 主要有以下几种方式来实现 distinct（去重）：

  1. 排序 + 去重：当查询数据量不大或内存足够时，MySQL 可能会选择先对数据进行排序，然后去除重复的行。这种方式通常在数据量适中且排序操作成本低时比较有效。
  2. 哈希去重：在处理大量数据时，如果内存足够，MySQL 可能会选择哈希去重。数据会加载到内存中的哈希表里，然后进行去重。这种方式在没有显式的排序要求时能有效处理大数据量的去重操作。
  3. 临时表：当数据量非常大，或者内存限制导致无法使用哈希表时，MySQL 可能会将结果集写入临时表，然后在临时表中进行去重。这种方式用于处理需要较大内存或存储空间的查询。

有索引则按照索引顺序，不重复的读取即可。

1.6 min/max

没有索引需要逐一对比所有的值，有索引则直接取

1.7 avg/sum/count 

没有索引需要扫描全部的数据计算结果，有索引则扫描索引文件（仅包含索引列和主键列）比主数据（全部列）会小一些，速度稍微快些，表的列越多，效果越明显

1.8 in / exists

select * from salaries where emp_no in (10005, 10006, 10007);
select * from salaries where emp_no in (select emp_no from employees);

in会先把in()中的结果全部查出保存在一个结果集中，再查主查询，对主查询的每一行记录在子查询结果集中查找，找到则把当前行加入结果集，直接进行下一行查询，不会再继续比较。

select * from salaries where exists 
(select 1 from employees where employees.emp_no = salaries.emp_no);

exists则先查主查询，对主查询的每一行数据查一次子查询，跟具返回结果决定是否加入结果集

1.9 not in / not exists

select * from salaries where emp_no not in (10005, 10006, 10007);
select * from salaries where emp_no not in (select emp_no from employees);

与使用in类似，not in会先把in()中的结果全部查出保存在一个结果集中，再查主查询，对主查询的每一行记录在子查询结果集中查找，找到则直接进行下一行查询，不会再继续比较，直到比较完not in中的元素，才会把当前行加入结果集.

select * from salaries where exists 
(select 1 from employees where employees.emp_no = salaries.emp_no);

not exists同样先查主查询，对主查询的每一行数据查一次子查询，跟具返回结果决定是否加入结果集

3. explain命令

explain本质上是一个工具，我们使用它是为了辅助理解给定SQL的底层执行策略 

以下面这条SQL为例，不使用explain，我们试着描述一下它的执行策略

-- 查询编号大于10005的员工信息，按工资升序排列
SELECT * 
FROM
        employees JOIN salaries 
        ON employees.emp_no = salaries.emp_no 
        AND salaries.from_date = '1992-08-04' 
WHERE
        employees.emp_no > 10005 
        AND salaries.salary > 70000 
ORDER BY
        salaries.salary;

-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
  1. 根据employees表的主键，直接把主键游标定位到emp_no > 10005的位置
  2. 开始读取employees表的记录，针对每条读到的记录（employees[i]，根据employees[i].emp_no和from_date='1992-08-04'去查salaries表，这里刚好可以根据salaries表的主键索引(emp_no, from_date)，唯一定位到一条记录salaries[j]
  3. 如果salaries[j].salary > 70000，则把JoinedRow(employees[i], salaries[j])加入join结果
  4. 拿到join结果集后，根据salaries.salary排序

Java伪代码：

// 获取表和索引
Table employeesTable = getEmployeesTable();
Table salariesTable = getSalariesTable();
Index empNoIndex = employeesTable.getPrimaryKeyIndex(); // 主键索引 (emp_no)
Index salaryIndex = salariesTable.getPrimaryKeyIndex(); // 主键索引 (emp_no, from_date)

// 结果集
List<JoinedRow> resultSet = new ArrayList<>();

// 从 employees 表的主键索引定位到 emp_no > 10005 的位置
Cursor employeesCursor = empNoIndex.getCursor();
employeesCursor.setPositionGreaterThan(10005);

// 遍历符合条件的员工记录
while (employeesCursor.hasNext()) {
    // 读取一条员工记录
    Employee employee = employeesCursor.next();
    
    // 使用 salaries 表的主键索引查找对应的薪资记录
    Cursor salariesCursor = salaryIndex.getCursor();
    salariesCursor.setPosition(employee.getEmpNo(), "1992-08-04");
    
    if (salariesCursor.hasNext()) {
        // 读取薪资记录
        Salary salary = salariesCursor.next();
        
        // 检查薪资是否大于阈值
        if (salary.getSalary() > 70000) {
            // 创建连接行并加入结果集
            JoinedRow joinedRow = new JoinedRow(employee, salary);
            resultSet.add(joinedRow);
        }
    }
}

// 根据薪资升序排序结果集
resultSet.sort(Comparator.comparingInt(JoinedRow::getSalary));

// 输出或返回结果集
return resultSet;

使用explain:

-- 查询编号大于10005的员工信息，按工资升序排列
SELECT * 
FROM
        employees JOIN salaries 
        ON employees.emp_no = salaries.emp_no 
        AND salaries.from_date = '1992-08-04' 
WHERE
        employees.emp_no > 10005 
        AND salaries.salary > 70000 
ORDER BY
        salaries.salary;

-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)

输出结果有2行，分别对应emplyees表和salaries表，这个也可以从table这一列看出来

我们了解一下表格中的字段含义：

  • type：访问类型或连接类型，表示 MySQL 使用的访问方法。例如，ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（按索引查找）
  • key：实际用于查询的索引
  • key_len：使用的索引的字段长度（字节数）。这里salaries表中同时使用了emp_no和from_date字段所以是7
  • ref：显示哪些列或常量与索引的列匹配。第二行中表示索引字段与employees数据库中的employees表中的emp_no字段匹配。
  • rows：预计扫描的行数
  • filtered：符合条件的数据率
  • extra：额外的信息，比如是否使用了文件排序、临时表等。

查询employees表时，用了主键索引查找where条件，并且预估通过where条件查询出149667条数据，因为没有其他where条件了，所以100%符合条件，filtered为100，extra中显示使用了where和临时表，以及排序。

查询salaries表时也用了主键索引，这里显示有两个索引，RPIMARY和idx_emp_no都可用，实际使用的是PRIMARY；ref显示用来和索引比较的值有两个，emp_no和一个const（常量值，1992-08-04）emp_no占4个字节，from_data占3个字节，这里都用到了，所以key_len是7；rows表示只会定位到1条数据，这条记录还需要满足salaries.salary > 70000，explain预估满足这个条件的概率为33.33%

有时MySQL的优化器会重写SQL，可以通过show warnings查看重写后的SQL，比如：

explain select * from employees where emp_no in (select emp_no from salaries);
show warnings;

/* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`birth_date` AS `birth_date`,`employees`.`employees`.`first_name` AS `first_name`,`employees`.`employees`.`last_name` AS `last_name`,`employees`.`employees`.`gender` AS `gender`,`employees`.`employees`.`hire_date` AS `hire_date` 
from `employees`.`employees` semi join (`employees`.`salaries`) 
where (`employees`.`salaries`.`emp_no` = `employees`.`employees`.`emp_no`)

 这里简化一下重写后的sql:

select employees.* from employees semi join salaries on employees.emp_no = salaries.emp_no;

如果感觉执行计划很奇怪，有可能是MySQL优化器重写了SQL，可以执行show warnings，查看重写后的SQL

也可以使用新版本的执行计划：

explain format=tree
SELECT * FROM employees JOIN salaries 
    ON employees.emp_no = salaries.emp_no AND salaries.from_date = '1992-08-04' 
WHERE employees.emp_no > 10005 AND salaries.salary > 70000 
ORDER BY salaries.salary;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)                                
                                
-> Sort: salaries.salary
    -> Stream results  (cost=82354 rows=49884)
        -> Nested loop inner join  (cost=82354 rows=49884)
            -> Filter: (employees.emp_no > 10005)  (cost=29971 rows=149667)
                -> Index range scan on employees using PRIMARY over (10005 < emp_no)  (cost=29971 rows=149667)
            -> Filter: (salaries.salary > 70000)  (cost=0.25 rows=0.333)
                -> Single-row index lookup on salaries using PRIMARY (emp_no=employees.emp_no, from_date=DATE'1992-08-04')  (cost=0.25 rows=1)

阅读顺序是由内到外：

  1. 通过索引查询10005 < emp_no，然后筛选出employees.emp_no > 10005
  2. 通过索引查emp_no=employees.emp_no, from_date=DATE'1992-08-04'，然后筛选出salaries.salary > 70000
  3. 内连接
  4. 排序


http://www.kler.cn/a/289078.html

相关文章：

  • web实操5——http数据详解，request对象功能
  • 会话信息处理： HttpSession、token序列化、收集登录设备信息、基于`spring-session-data-redis`实现session共享。
  • 卷积神经网络之Yolo详解
  • vue面试题7|[2024-11-14]
  • python制作一个简单的端口扫描器，用于检测目标主机上指定端口的开放状态
  • 成都睿明智科技有限公司解锁抖音电商新玩法
  • OCI编程高级篇（十八） OCI连接池概念
  • 如何打造一个成功的直播矩阵
  • 【科研新人必看】什么是期刊等级，SCI、核心期刊、省刊
  • qt6 socket 不使用代理 socket error: The proxy type is invalid for this operation
  • 8.29 C++
  • 常用Pandas操作（笔记整理）
  • 前端学习笔记-Web APIs篇-02
  • 基于机器学习的工业制造缺陷分析预测系统
  • 运动多线激光三维重建
  • 解决bug: RuntimeError: Address already in use，一个linux下pytorch多卡训练tcp端口占用的bug
  • SpringCloudGateway网关技术
  • 笔记整理—uboot番外（2）find_cmd函数
  • Selenium+Python自动化测试环境搭建
  • SAP自动化操作
  • L1-084 拯救外星人
  • Python 数据分析— Pandas 基本操作（上）
  • SprinBoot+Vue健康管管理微信小程序的设计与实现
  • 代码随想录训练营day51|图论part2
  • 解锁.NET安全奥秘：敏感数据加密与哈希的深度揭秘
  • C++系列-const所有用法总结