下面举一个例子,介绍MySQL中的优化策略
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. 暴力策略
为了方便演示,这里采用伪代码的形式来描述执行流程
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)
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实际使用场景中,一般一次查询只会有很少的数据符合条件,可以很好地适用增量策略 如果符合条件的数据确实很多,我们会通过分页限制返回条数,使增量策略能继续适用
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),大大提高了速度。
4 核心策略总结
增量查询:逐行读取,收集到足够的数据直接返回避免了全量计算
索引加速:在查询时利用有索引的序性快速定位到所需数据的区间,最大限度发挥增量查询的优势