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

Ting-yu

于 2024-09-02 16:37:19 发布

阅读量252

点赞数 8

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2303_78892316/article/details/141823498

版权

下面举一个例子，介绍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)，大大提高了速度。