锐客网

  1. 首页 > it技术 > >

mysql的order by与where加了索引与没有加索引的结果集区别

【mysql的order by与where加了索引与没有加索引的结果集区别】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.
mysql的order by与where加了索引与没有加索引的结果集区别
文章图片

2:取前3条：
mysql的order by与where加了索引与没有加索引的结果集区别
文章图片

3：对first_name排序取前三条：
mysql的order by与where加了索引与没有加索引的结果集区别
文章图片

在没有排序时得到的事正确的三条数据，但是排序后得到的是21条（全部数据），奇怪。
猜测：
(1)执行了where但是在排序时是将所有的数据都拿出来排序的
(2)没有执行where，直接排序输出了。即跳过了where.
看explain:
mysql的order by与where加了索引与没有加索引的结果集区别
文章图片

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的order by与where加了索引与没有加索引的结果集区别
文章图片

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的order by与where加了索引与没有加索引的结果集区别
文章图片

但是我还是支持做个子查询，这样就不用考虑太多了。哈哈，有点懒了。
不过高性能mysql第三版这个解说和我的想法不一致。先放着，下次深入理解。有知道同学希望讨论下。
感谢群里的大白菜和explain
对于filesort的优化可以在网上找下，这里有比较好的
http://www.ccvita.com/169.html
http://blog.csdn.net/yangyu112654374/article/details/4251624
http://bbs.chinaunix.net/thread-1020877-1-1.html
在http://www.svnspot.com/database/mysql/39432_1.html中的解释：
mysql的filesort算法有两种，
. 一种是最初的算法，在MySQL 4.1以前只有这种算法，
. 一种是改进的filesort算法，它出现在MySQL 4.1以后(blob和text类型的字段不能采用这种改进算法)
“最初的算法”流程如下：
1.读取所有的满足条件的数据，只包含sort key和row pointer两种数据
2.在buffer中执行qsort排序
3.排完序后，再根据row pointer去读取相应的行数据
从中可以看出，每次排序都需要读两次表，而根据row pointer去读表往往都是随机离散读的，所有其开销非常大。
改进后的filesort算法是：
1.读取所需要的数据，包含sort key,row pointer和查询所需要访问的字段
2.根据sort key排序
3.按排序后的顺序读取数据，由于sort_buffer_size中包含了所需要的字段，因此不需要再回表了，可以直接返回结果给客户端。
很明显，这种改进的方法对sort_buffer_size的需求也大大增加.
所以为了防止性能下降，mysql增加了一个参数max_length_for_sort_data,当第一步中除了sort key以外的字段内容大于max_length_for_sort_data这个参数时，mysql将采用第一种排序算法。
在http://blog.itpub.net/22418990/viewspace-757608/中的解释：
mysql有两种文件排序算法（双路排序和单路排序），如果需要排序的列的总大小加上order by列的大小超过了 max_length_for_sort_data定义的字节，mysql就会使用双路排序，当任何需要的列甚至不是用order by的列（text.blob的时候），也会使用双路排序，（可以使用substtring() 把这些列转化为可以单路排序的列）。
可以通过改变 max_length_for_sort_data变量的值来影响mysql选择的算法。因为单路排序为将要排序的每一行创建了固定的缓冲区，varchar列的最大长度是 max_length_for_sort_data规定的值，而不是排序数据的实际大小。
当mysql不得不对text。blob列进行排序时，它只会使用前缀并忽略剩余的值，这是因为不得不分配固定大小的结构来容纳数据并且从外部存储中将前缀拷贝回结构中，可以使用max_sort_length定义前缀应该是多大。
mysql并不会真正的显示使用的是哪种算法，如果增大了max_length_for_sort_data的值，并且磁盘使用率上升，cpu使用率下降，sort_merge_passes的值比以前增加的更快，也许该强制排序使用单路排序算法。
双路排序：
读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
双路排序的开销可能会非常巨大，因为他会读取表两次，第二次读取会引发大量的随机IO,对于myisam涞说，这个代价尤其昂贵，myisam表利用系统调用去提取每行的数据。
单路排序：
读取查询需要的所有列，按照order by 列对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
还有一个连接：
http://blog.csdn.net/jackem/article/details/3319658
最后：
高性能mysql第三版书籍下载：http://download.csdn.net/detail/u011575570/9224323

    推荐阅读


    上一篇：flink任务提交流程

    下一篇：#|单词接龙（信息学奥赛一本通-T1220）