1. 执行SQL语句的优化策略
下面举一个例子:
use employees;
SELECT *
FROM departments JOIN dept_emp ON departments.dept_no = dept_emp.dept_no
WHERE departments.dept_name = 'Ting'
LIMIT 10;
如果你是MySQL你会如何执行这条查询语句。
1.1 暴力策略
为了方便演示,这里采用伪代码的形式来描述执行流程
Java伪代码:
public List naiveQuery() {
// 先加载2张表的全量数据
List departmentsList = loadAllFromDisk("departments");
List deptEmpList = loadAllFromDisk("dept_emp");
// 再通过2重for循环做join
List joinedList = new LinkedList();
for(Department department : departmentsList) {
for(DeptEmp deptEmp : deptEmpList) {
//判断join条件
if(department.dept_no == deptEmp.dept_no) {
joinedList.add(new JoinedRow(deptEmp, department));
}
}
}
}
// 最后遍历join结果集,做where + limit
List result = new LinkedList();
for(JoinedRow joinedRow in joinedList) {
if(joinedRow["dept_name"].equals("Ting")) {
result.add(joinedRow);
//数据达到10条提前返回
if(result.size() == 10) {
return result;
}
}
}
return result;
}
为了拿到10条数据,我们对两张表做了全量的join计算,如果每张表有10万条数据,就要计算10万 * 10万 = 100亿次,显然效率非常低,时间复杂度为N(m * n)
1.2 增量查询策略
Java伪代码:
public List incrementalQuery() {
List result = new LinkedList();
Cursor departmentsCursor = openCursor("departments");
while(departmentsCursor.hasMoreRows()) {
// 外层表(外层循环使用的表),每次只读取1行
Department department = departmentsCursor.readNext();
// 判断where条件
if(department["dept_name"].equals("Ting")) {
// 遍历内层表做join
Cursor deptEmpCursor = openCursor("dept_emp");
while(deptEmpCursor.hasMoreRows()){
//每次读取一行
DeptEmp deptEmp = deptEmpCursor.readNext();
//判断join条件
if(department.dept_no == deptEmp.dept_no) {
result.add(new JoinedRow(deptEmp, department));
//数据达到10条提前返回
if(result.size() == 10) {
return result;
}
}
}
}
}
return result;
}
不再一次性加载全部数据和join计算,而是每次加载一行,且只对同时符合where条件和join条件的行做join计算,如果运气好,很快就能查到10条满足的数据返回,如果运气不好,外层表循环到最后才匹配到10条记录,或者满足条件的记录加起来也没有10条,增量查询又退化为了全量查询。最差情况下仍然是N(m * n),不过因为提前判读了where条件,以及每次只加载一条数据,节省了部分的内循环join计算,提前返回还能节省部分读取数据的开销。
增量查询策略的核心是提前返回
在MySQL实际使用场景中,一般一次查询只会有很少的数据符合条件,可以很好地适用增量策略 如果符合条件的数据确实很多,我们会通过分页限制返回条数,使增量策略能继续适用
1.3 索引加速
查看这部分内容需要先了解一下索引:MySQL索引详解
Java伪代码:
public List incrementalQueryWithIndex() {
List result = new LinkedList();
// 通过索引快速定位符合where条件的数据
Cursor departmentsCursor = searchIndex("dept_name", "Ting");
while(departmentsCursor.hasNext()) {
Department department = departmentsCursor.readNext();
// 利用索引快速定位到符合join条件的数据
Cursor deptEmpCursor = searchIndex("dept_no", department.dept_no);
while(deptEmpCursor.hasNext()) {
DeptEmp deptEmp = deptEmpCursor.readNext();
result.add(new JoinedRow(deptEmp, department));
if(result.size() == 10) {
return result;
}
}
//遍历完所有符合条件的内层表数据,开始下一次循环
}
return result;
}
查询外层表时,利用索引快速筛选出符合where条件的记录,内层循环同样利用索引快速定位到匹配的行,直接遍历有效的数据,排除了所有的无效计算。查索引的时间复杂度是O(lgN),大大提高了速度。
1.4 核心策略总结
增量查询:逐行读取,收集到足够的数据直接返回避免了全量计算
索引加速:在查询时利用有索引的序性快速定位到所需数据的区间,最大限度发挥增量查询的优势
2. 不同SQL操作的执行流程
要优化一条SQL语句,要先理解SQL操作的执行流程
2.1 order by
order by用于排序,如果用于排序的列上没有索引,就需要把整张表加载进内存进行排序,非常耗时。如果有索引,因为B+树存储的数据本就是有序的,所以MySQL可以通过索引直接顺序读取即可,非常高效
2.2 join
前面以及了解过join是通过两层for循环实现。
如果内存表没有索引,那么对于外层表的每一行数据都要进行一次全表扫描,如果有索引,内层表可以使用索引快速定位到匹配的记录。
2.3 where
没有索引只能对表进行扫描,逐一判读是否满足,有索引则可以快速定位到满足条件的数据
2.4 group by
分组操作一般有两种实现方案:hash和排序,MySQL使用的是排序:
如果没有索引:
- 新建临时表
- 扫描数据表,并按插入排序的方式插入临时表,这样就保证了group by列的值相同的会排列在一起
- 在临时表上做处理(使用聚合函数,max/min/count/sum/avg)
如果用于分组的列上有索引,那么前两部就直接省略了
2.5 distinct
在没有索引的情况下,MySQL 主要有以下几种方式来实现 distinct(去重):
- 排序 + 去重:当查询数据量不大或内存足够时,MySQL 可能会选择先对数据进行排序,然后去除重复的行。这种方式通常在数据量适中且排序操作成本低时比较有效。
- 哈希去重:在处理大量数据时,如果内存足够,MySQL 可能会选择哈希去重。数据会加载到内存中的哈希表里,然后进行去重。这种方式在没有显式的排序要求时能有效处理大数据量的去重操作。
- 临时表:当数据量非常大,或者内存限制导致无法使用哈希表时,MySQL 可能会将结果集写入临时表,然后在临时表中进行去重。这种方式用于处理需要较大内存或存储空间的查询。
有索引则按照索引顺序,不重复的读取即可。
2.6 min/max
没有索引需要逐一对比所有的值,有索引则直接取
2.7 avg/sum/count
没有索引需要扫描全部的数据计算结果,有索引则扫描索引文件(仅包含索引列和主键列)比主数据(全部列)会小一些,速度稍微快些,表的列越多,效果越明显
2.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则先查主查询,对主查询的每一行数据查一次子查询,跟具返回结果决定是否加入结果集
2.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)
- 根据employees表的主键,直接把主键游标定位到emp_no > 10005的位置
- 开始读取employees表的记录,针对每条读到的记录(employees[i],根据employees[i].emp_no和from_date='1992-08-04'去查salaries表,这里刚好可以根据salaries表的主键索引(emp_no, from_date),唯一定位到一条记录salaries[j]
- 如果salaries[j].salary > 70000,则把JoinedRow(employees[i], salaries[j])加入join结果
- 拿到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)
阅读顺序是由内到外:
- 通过索引查询10005 < emp_no,然后筛选出employees.emp_no > 10005
- 通过索引查emp_no=employees.emp_no, from_date=DATE'1992-08-04',然后筛选出salaries.salary > 70000
- 内连接
- 排序
4. 慢SQL优化
经过前面内容的理解,我们以及了解了MySQL的优化策略,以及SQL语句的执行流程,接下来我们通过一个问题来学习,如何优化SQL
4.1 如何构造一条慢SQL
前面我们介绍了,数据库实现高效查询的核心原则是:增量查询和索引加速,其中索引加速很大程度上也是在为增量查询服务。
要构造一条慢SQL,我们就可以从破坏增量查询入手:
1. 利用join制造大量计算
select * from employees join salaries
on employees.emp_no = salaries.emp_no
limit 10;
优化思路:给内层表的join列(salaries.emp_no)加上索引,避免全表扫描内层表
2. 添加where条件,增加收集10条数据的难度
select * from employees join salaries
on salaries.emp_no = employees.emp_no
where salaries.salary > 60000
limit 10;
优化思路:在salaries.salary和employees.emp_no上各建一个索引,实际执行的时候,MySQL就可以先利用salaries.salary上的索引,定位到大于60000的数据,再把salaries作为外层表和employees表join,同时employees表也可以使用 employees.emp_no 定位到需要的数据,这样就消除了where的难度。
3. 添加多个where条件,增加收集到10条结果的难度
select * from employees join salaries
on salaries.emp_no = employees.emp_no
where salaries.salary > 60000
and salaries.from_date > '1992-07-12'
limit 10;
优化思路:这里where条件都是同一个表的,可以创建salary和from_date的联合索引,查询时MySQL就可以更具salary和from_date上的联合索引找出 salary > 60000并且from_date > '1992-07-01' 的数据,然后再把salaries作为外层表和内存进行join;如果where条件过多或者并非同一个表的字段,则可以对每个where条件的字段都创建一个索引,使用实际效果好的那一个索引,不推荐使用多个不同的索引共同筛选,因为回表操作的开销无法忽略
4. 使用left join限制join的顺序
select * from employees left join salaries
on salaries.emp_no = employees.emp_no
where salaries.salary > 60000
and salaries.from_date > '1992-07-12'
limit 10;
优化思路:这里使用了left join看似强行把employees指定为了外层表,但是,left join 仅比join多了内层表为null的数据,但上述SQL语句中,存在where条件 salaries.salary > 60000 ,null > 60000一定为false,于是这里的left join就和join每什么区别了,MySQL就会把这个left join优化为join,我们就可以继续使用上面第3点的优化思路
5. 引入order by 破坏增量策略
select * from employees join salaries
on salaries.emp_no = employees.emp_no
where salaries.salary > 60000
order by salaries.from_date
limit 10;
这里的where和order by使用了不同的列,会涉及索引的选择问题:
- 如果where筛选效果好,那么就使用where字段上的索引,先筛选得出结果再排序,比如只有几百条记录符合条件,那对几百条数据做个排序开销也不大。
- 如果where筛选效果不好,就使用order by字段上的索引,那么就可以继续使用增量策略,按顺序查找符合条件的数据,收集到10条就返回
- 如果where筛选效果不好不坏,并且表的数据量巨大,如果先筛选后排序,筛选后的数据量多,让 sort buffer放不下,排序开销就足够大;如果使用order by上的索引顺序搜索,搜索的很多数据都不符合where条件,需要大量搜索才能凑出10条数据,至此我们成功构造出了一条慢SQL
通过上面的分析我们可以看到,数据库的增量略是相当成功的, 想制造一条慢SQL是并不容易的。
这里再次提醒一点,MySQL 数据库通常不适合全量扫描的场景,特别是当表的数据量非常大时。全量扫描(即全表扫描)会导致高 I/O 负载和长时间的查询执行,这对性能和资源消耗都有较大影响,所以使用MySQL通常会使用分页查询。如果业务需要大量的全量扫描则可以考虑使用其他数据库,例如NoSQL。同时MySQL也不适合统计类的查询,比如 sum/avg等,这类查询天然和增量策略冲突
4.2 案例练习
相信有了前面的学习,我们已经对如何分析并优化一条SQL语句有了较深的了解,下面我们尝试分析几个案例:
示例1:
-- cost 364ms
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
LIMIT 10;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
-> Limit: 10 row(s)
-> Sort: salaries.salary, limit input to 10 row(s) per chunk
-> Stream results (cost=95578 rows=74834)
-> Nested loop inner join (cost=95578 rows=74834)
-> 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.338 rows=0.5)
-> Single-row index lookup on salaries using PRIMARY
(emp_no=employees.emp_no, from_date=DATE'1992-08-04')
(cost=0.338 rows=1)
这里的执行策略是:
- 1. 通过employees.emp_no上的索引查询 10005 < emp_no,然后筛选出 10005 < emp_no的数据
- 2. 对外层表的每条数据通过salaries.emp_no和salaries.from_data上的联合索引 查符合条件emp_no=employees.emp_no, from_date=DATE'1992-08-04'的数据,然后筛选出 salaries.salary > 70000的数据
- 3. 把内外层表的数据做连接操作
- 4. 匹配完所有数据后,把结果集按照salaries.salary字段进行排序,然后返回前10条
优化思路:上面执行策略显然没有使用增量策略,是匹配完所有的数据后再进行排序后返回,于是我们可以在salaries.salary字段上添加索引,使查询时,可以把salaries作为外层表进行查询,就可继续使用增量策略,查询到10条后返回
优化后的执行策略:
-> Limit: 10 row(s) (cost=443853 rows=10)
-> Nested loop inner join (cost=443853 rows=1.42e+6)
-> Index range scan on salaries using idx_salary over (70000 < salary), with index condition:
((salaries.from_date = DATE'1992-08-04') and (salaries.emp_no > 10005) and (salaries.salary > 70000))
(cost=284192 rows=1.42e+6)
-> Single-row index lookup on employees using PRIMARY (emp_no=salaries.emp_no) (cost=0.25 rows=1)
这里的执行策略是:
- 1. 根据索引 salaries.salary字段上的索引查询 70000 < salary的数据,然后更具条件(salaries.from_date = DATE'1992-08-04') and (salaries.emp_no > 10005) and (salaries.salary > 70000))进行筛选
- 2. 根据employees.emp_no 上的索引,查找符合emp_no=salaries.emp_no 的数据
- 3. 进行join操作,当收集到10条数据后会直接返回
示例2:
-- 耗时4200ms
select * from employees
left join salaries on employees.emp_no = salaries.emp_no
left join dept_emp on dept_emp.emp_no = employees.emp_no
where dept_emp.dept_no not in (select dept_no from departments where dept_name > 'AAA')
order by employees.hire_date
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)
-- departments: UNIQUE KEY (dept_name)
尝试使用增量策略 -- 4500ms
给 employees.hire_date加索引:
-> Limit: 10 row(s) (cost=851920 rows=10)
-> Filter: <in_optimizer>(dept_emp.dept_no,dept_emp.dept_no in (select #2) is false) (cost=851920 rows=10.4)
-> Nested loop left join (cost=851920 rows=10.4)
-> Nested loop left join (cost=135044 rows=9.42)
-> Index scan on employees using idx_hire_date (cost=776e-6 rows=1)
-> Index lookup on salaries using PRIMARY (emp_no=employees.emp_no) (cost=0.451 rows=9.42)
-> Index lookup on dept_emp using PRIMARY (emp_no=employees.emp_no) (cost=0.254 rows=1.1)
-> Select #2 (subquery in condition; run only once)
-> Filter: ((dept_emp.dept_no = `<materialized_subquery>`.dept_no)) (cost=2.9..2.9 rows=1)
-> Limit: 1 row(s) (cost=2.8..2.8 rows=1)
-> Index lookup on <materialized_subquery> using <auto_distinct_key> (dept_no=dept_emp.dept_no)
-> Materialize with deduplication (cost=2.8..2.8 rows=9)
-> Filter: (departments.dept_name > 'AAA') (cost=1.9 rows=9)
-> Covering index scan on departments using dept_name (cost=1.9 rows=9)
可以看到索引生效了,但是耗时确没减少还略有增加,这是因为,符合条件的数据,没有10条,这种情况下,增量和全量没区别,走索引还会增加回表的开销所以,走索引不一定快,需要具体分析执行逻辑。
拆分sql
在上面的sql的执行策略中,对于每个可能的结果都是最后判断是否满足,not in,并且所有满足条件的数据又不超过10条,就会大致大量的数据都会去使用dept_emp.dept_no和not in中的值比较,又因为dept_emp在第三层循环,所以回导致同一个dept_emp.dept_no会去not in中比较很多次,这里我们可以通过把dept_emp当作外层表,先满足 not in 条件再向后匹配。当not in中包含的是具体的常量值,MySQL通常就会在查询中首先处理这些常量值
-- 耗时2ms,返回 'd009','d005','d002','d003','d001','d004','d006','d008','d007'
select dept_no from departments where dept_name > 'AAA';
-- 把第一步返回的结果直接做为第二步查询的条件,耗时3ms
select * from employees
left join salaries on employees.emp_no = salaries.emp_no
left join dept_emp on dept_emp.emp_no = employees.emp_no
where dept_emp.dept_no not in ('d009','d005','d002','d003','d001','d004','d006','d008','d007')
order by employees.hire_date
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)
-- departments: UNIQUE KEY (dept_name)