mysql的order by与where出现的好玩事

rr322w

CREATE TABLE employees (
emp_no INT(11) NOT NULL,
birth_date DATE NOT NULL,
first_name VARCHAR(14) NOT NULL,
last_name VARCHAR(16) NOT NULL,
gender ENUM(‘M’,’F’) NOT NULL,
hire_date DATE NOT NULL,
PRIMARY KEY (emp_no)
)
COLLATE=’utf8’
ENGINE=InnoDB;

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10001, ‘1953-09-02’, ‘Georgi’, ‘Facello’, ‘M’, ‘1986-06-26’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10002, ‘1964-06-02’, ‘Bezalel’, ‘Simmel’, ‘F’, ‘1985-11-21’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10003, ‘1959-12-03’, ‘Parto’, ‘Bamford’, ‘M’, ‘1986-08-28’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10004, ‘1954-05-01’, ‘Chirstian’, ‘Koblick’, ‘M’, ‘1986-12-01’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10005, ‘1955-01-21’, ‘Kyoichi’, ‘Maliniak’, ‘M’, ‘1989-09-12’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10006, ‘1953-04-20’, ‘Anneke’, ‘Preusig’, ‘F’, ‘1989-06-02’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10007, ‘1957-05-23’, ‘Tzvetan’, ‘Zielinski’, ‘F’, ‘1989-02-10’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10008, ‘1958-02-19’, ‘Saniya’, ‘Kalloufi’, ‘M’, ‘1994-09-15’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10011, ‘1972-02-29’, ‘He’, ‘Rick’, ‘M’, ‘1991-02-20’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64887, ‘1961-05-15’, ‘Rafols’, ‘Suomi’, ‘F’, ‘1987-06-05’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64888, ‘1962-05-16’, ‘Kristof’, ‘Marchegay’, ‘F’, ‘1988-11-10’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64889, ‘1953-05-08’, ‘Emdad’, ‘Pauthner’, ‘M’, ‘1985-09-07’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64890, ‘1957-08-21’, ‘Leni’, ‘Kilgore’, ‘M’, ‘1995-08-31’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64891, ‘1954-09-27’, ‘Ioana’, ‘Pepe’, ‘F’, ‘1990-08-19’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64892, ‘1964-12-05’, ‘Mayumi’, ‘Tyugu’, ‘F’, ‘1988-03-19’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64893, ‘1953-11-27’, ‘Yoshimitsu’, ‘Billawala’, ‘F’, ‘1986-06-07’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64894, ‘1955-09-19’, ‘Mori’, ‘Weedman’, ‘F’, ‘1999-01-29’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64895, ‘1962-02-24’, ‘Dietrich’, ‘Foote’, ‘M’, ‘1988-08-07’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64896, ‘1960-12-31’, ‘Ingemar’, ‘Schieder’, ‘M’, ‘1995-10-25’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64897, ‘1956-04-24’, ‘Aksel’, ‘Denna’, ‘M’, ‘1986-08-31’);
INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64898, ‘1961-04-13’, ‘Sudhanshu’, ‘Hutton’, ‘M’, ‘1996-02-09’);
1:实现row_number.

2:取前3条：

3：对first_name排序取前三条：

在没有排序时得到的事正确的三条数据，但是排序后得到的是21条（全部数据），奇怪。
猜测：
(1)执行了where但是在排序时是将所有的数据都拿出来排序的
(2)没有执行where，直接排序输出了。即跳过了where.
看explain:

MySQL官方手册解释：
Using where
A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

Using filesort
MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.
然后看到使用了filesort是要使用外部空间进行排序的，这里我是这样想的，sql执行是从右到左的所以应该是order by跳过了where,所以猜测是(2).
因为使用了filesort所以建立first_name索引试下：

MySQL官方手册解释：
Using index
The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
可以看到建立了first_name索引之后没有filesort了，而且数据也正确了。
所以这里没有了filesort，数据正确所以应该是猜测(1)成立。
在高性能mysql第三版中的6.7.9节中有这样一段话：因为order by引入了文件排序，而where条件是在文件排序操作之前取值的，所以这条查询会返回表中的全部记录，解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段：如图：

但是我还是支持做个子查询，这样就不用考虑太多了。哈哈，有点懒了。
不过高性能mysql第三版这个解说和我的想法不一致。先放着，下次深入理解。有知道同学希望讨论下。
感谢群里的大白菜和explain
对于filesort的优化可以在网上找下，这里有比较好的