MySQL EXPLAINの読み方をJavaで理解する(1)

はてなブックマークの人気エントリで「SQLを使うなら理解しておきたいアルゴリズム？…」という記事を眺めていて、実際にSQLのアルゴリズムを説明するにはどうすればよいか考えてみました。そこで、試しにSQLの実行計画をJavaで再現してみることにしました。
今回は索引アクセスとテーブル結合に関する7種類のSQLについて、MySQLのEXPLAIN結果とそれに対応するJavaのコードを記述しました。EXPLAINの読み方を言葉で説明するのは非常に難しいのですが、プログラムを読める方ならこうした説明が一番分かりやすいのではないでしょうか。

import java.util.ArrayList;
import java.util.List;
import java.util.SortedMap;
import java.util.TreeMap;

public class Study {
	public static void main(String[] args) {
		new Study().test();
	}

	private void test() {
// SQL 01
// CREATE TABLE emp (empno INT, ename VARCHAR(100), deptno INT);
		List<Employee> empTable = new ArrayList<Employee>();

// SQL 02
// INSERT INTO emp (empno, ename, deptno) VALUES (xxx, 'yyy', zzz);
		empTable.add(new Employee(7782, "clark", 10));
		empTable.add(new Employee(7788, "scott", 20));
		empTable.add(new Employee(7839, "king", 70));

// SQL 03
// SELECT ename FROM emp;
// +----+-------------+-------+------+---------------+------+---------+------+------+-------+
// | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
// +----+-------------+-------+------+---------------+------+---------+------+------+-------+
// |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |    3 |       |
// +----+-------------+-------+------+---------------+------+---------+------+------+-------+
		System.out.println("-- SQL 03");
		for (Employee emp : empTable) {
			System.out.println(emp.getEname());
		}

// SQL 04
// SELECT ename FROM emp WHERE empno = 7788;
// +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
// | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
// +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
// |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
// +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		System.out.println("-- SQL 04");
		for (Employee emp : empTable) {
			if (emp.getEmpno() == 7788) {
				System.out.println(emp.getEname());
			}
		}

// SQL 05
// CREATE UNIQUE INDEX emp_empno ON emp (empno);
		SortedMap<Integer, Employee> empEmpnoIdx = new TreeMap<Integer, Employee>();
		for (Employee emp : empTable) {
			empEmpnoIdx.put(emp.getEmpno(), emp);
		}

// SQL 06 (SQL 04と同じ)
// SELECT ename FROM emp WHERE empno = 7788;
// +----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
// | id | select_type | table | type  | possible_keys | key       | key_len | ref   | rows | Extra |
// +----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
// |  1 | SIMPLE      | emp   | const | emp_empno     | emp_empno | 5       | const |    1 |       |
// +----+-------------+-------+-------+---------------+-----------+---------+-------+------+-------+
		System.out.println("-- SQL 06");
		if (empEmpnoIdx.containsKey(7788)) {
			Employee emp = empEmpnoIdx.get(7788);
			System.out.println(emp.getEname());
		}

// SQL 07
// CREATE TABLE dept (deptno INT, dname VARCHAR(100));
		List<Department> deptTable = new ArrayList<Department>();

// SQL 08
// CREATE UNIQUE INDEX dept_deptno ON dept (deptno);
		SortedMap<Integer, Department> deptDeptnoIdx = new TreeMap<Integer, Department>();

// SQL 09
// INSERT INTO dept (deptno, dname) VALUES (xxx, 'yyy');
		Department deptTmp = new Department(10, "accounting");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);
		deptTmp = new Department(20, "research");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);
		deptTmp = new Department(30, "sales");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);
		deptTmp = new Department(40, "operations");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);
		deptTmp = new Department(50, "consulting");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);
		deptTmp = new Department(60, "education");
		deptTable.add(deptTmp);
		deptDeptnoIdx.put(deptTmp.getDeptno(), deptTmp);

// SQL 10
// SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
// SQL 10'
// SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
// | id | select_type | table | type | possible_keys | key         | key_len | ref            | rows | Extra       |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
// |  1 | SIMPLE      | e     | ALL  | NULL          | NULL        | NULL    | NULL           |    3 |             |
// |  1 | SIMPLE      | d     | ref  | dept_deptno   | dept_deptno | 5       | study.e.deptno |    1 | Using where |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
		System.out.println("-- SQL 10");
		for (Employee emp : empTable) {
			if (deptDeptnoIdx.containsKey(emp.getDeptno())) {
				Department dept = deptDeptnoIdx.get(emp.getDeptno());
				System.out.println(emp.getEname() + "," + dept.getDname());
			}
		}

// SQL 11
// SELECT e.ename, d.dname FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------+
// | id | select_type | table | type | possible_keys | key         | key_len | ref            | rows | Extra |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------+
// |  1 | SIMPLE      | e     | ALL  | NULL          | NULL        | NULL    | NULL           |    3 |       |
// |  1 | SIMPLE      | d     | ref  | dept_deptno   | dept_deptno | 5       | study.e.deptno |    1 |       |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------+
		System.out.println("-- SQL 11");
		for (Employee emp : empTable) {
			if (deptDeptnoIdx.containsKey(emp.getDeptno())) {
				Department dept = deptDeptnoIdx.get(emp.getDeptno());
				System.out.println(emp.getEname() + "," + dept.getDname());
			} else {
				System.out.println(emp.getEname() + "," + "(null)");
			}
		}

// SQL 12
// SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE e.empno = 7788;
// +----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+
// | id | select_type | table | type  | possible_keys | key         | key_len | ref   | rows | Extra |
// +----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+
// |  1 | SIMPLE      | e     | const | emp_empno     | emp_empno   | 5       | const |    1 |       |
// |  1 | SIMPLE      | d     | const | dept_deptno   | dept_deptno | 5       | const |    1 |       |
// +----+-------------+-------+-------+---------------+-------------+---------+-------+------+-------+
		System.out.println("-- SQL 12");
		if (empEmpnoIdx.containsKey(7788)) {
			Employee emp = empEmpnoIdx.get(7788);
			if (deptDeptnoIdx.containsKey(emp.getDeptno())) {
				Department dept = deptDeptnoIdx.get(emp.getDeptno());
				System.out.println(emp.getEname() + "," + dept.getDname());
			}
		}

// SQL 13
// SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno WHERE e.ename = 'scott';
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
// | id | select_type | table | type | possible_keys | key         | key_len | ref            | rows | Extra       |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
// |  1 | SIMPLE      | e     | ALL  | NULL          | NULL        | NULL    | NULL           |    3 | Using where |
// |  1 | SIMPLE      | d     | ref  | dept_deptno   | dept_deptno | 5       | study.e.deptno |    1 | Using where |
// +----+-------------+-------+------+---------------+-------------+---------+----------------+------+-------------+
		System.out.println("-- SQL 13");
		for (Employee emp : empTable) {
			if (emp.getEname().equals("scott")) {
				if (deptDeptnoIdx.containsKey(emp.getDeptno())) {
					Department dept = deptDeptnoIdx.get(emp.getDeptno());
					System.out.println(emp.getEname() + "," + dept.getDname());
				}
			}
		}
	}
}

class Employee {
	private int empno;
	private String ename;
	private int deptno;

	public Employee(int empno, String ename, int deptno) {
		this.empno = empno;
		this.ename = ename;
		this.deptno = deptno;
	}

	public int getEmpno() {
		return empno;
	}

	public String getEname() {
		return ename;
	}

	public int getDeptno() {
		return deptno;
	}
}

class Department {
	private int deptno;
	private String dname;

	public Department(int deptno, String dname) {
		this.deptno = deptno;
		this.dname = dname;
	}

	public int getDeptno() {
		return deptno;
	}

	public String getDname() {
		return dname;
	}
}

-- SQL 03
clark
scott
king
-- SQL 04
scott
-- SQL 06
scott
-- SQL 10
clark,accounting
scott,research
-- SQL 11
clark,accounting
scott,research
king,(null)
-- SQL 12
scott,research
-- SQL 13
scott,research

いかがでしょう。それほど難しくないですよね。
解説を別エントリとして起こしました。あわせてご覧ください。