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

深入理解MySQL慢查询优化(1) -- 优化策略

news 2025/5/23 22:06:09

下面举一个例子，介绍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循环做joinList 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 + limitList 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")) {// 遍历内层表做joinCursor 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)，大大提高了速度。