mysql limit工作原理及order by效率分析
MySQL的limit m n工作原理就是先读取前面m+n条记录,然后抛弃前m条,读后面n条想要的,所以m越大,偏移量越大,性能就越差。
推荐分页查询方法:
1、尽量给出查询的大致范围
SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;
2、子查询法
SELECT c1,c2,cn... FROM table WHERE id>=
(
SELECT id FROM table LIMIT 20000,1
)
LIMIT 10;
3、高性能MySQL一书中提到的只读索引方法
SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5
优化后SQL:
SELECT c1, c2, cn .. .
FROM member
INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
USING (member_id)
分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。
order by和limit
如果你order by和limit一起使用,那么mysql在排序结果中找到最初的row_count行之后就会完成这条语句,而不是对整个结果集进行排序。如果使用了索引排序,它就非常快地完成。如果整个filesort必须都做完的话,那么在找到最初的row_count行之前,匹配该查询的所有行都将被select,并且做sort操作。如果这些行找到了,mysql将不会对剩余的结果集进行排序。
Order by和Limit混合使用引起的问题
如果在order by语句中返回的结果集有很多行,那么非排序的列的返回结果是不确定的,即随机的,所以如果配合limit的话每次返回的结果集的顺序是不固定的,比如下面这个例子
mysql> SELECT * FROM ratings ORDER BY category;
±—±---------±-------+
| id | category | rating |
±—±---------±-------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
±—±---------±-------+
使用了limit以后,可发现id列和rating列和之前的结果集顺序有出入:
mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
±—±---------±-------+
| id | category | rating |
±—±---------±-------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 4 | 2 | 3.5 |
| 3 | 2 | 3.7 |
| 6 | 2 | 3.5 |
±—±---------±-------+
如果你有必要保证每次有相同的结果集,则需要order by你需要的那几列了:
mysql> SELECT * FROM ratings ORDER BY category, id;
±—±---------±-------+
| id | category | rating |
±—±---------±-------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
| 2 | 3 | 5.0 |
| 7 | 3 | 2.7 |
±—±---------±-------+
mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
±—±---------±-------+
| id | category | rating |
±—±---------±-------+
| 1 | 1 | 4.5 |
| 5 | 1 | 3.2 |
| 3 | 2 | 3.7 |
| 4 | 2 | 3.5 |
| 6 | 2 | 3.5 |
±—±---------±-------+
Order by和limit一起使用的优化原理
从MySQL5.6.2版本以后,优化器将更加智能地处理下面形式的查询了
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
这种在很大的结果集中只返回很少的行数的查询类型在web应用中非常常见,比如
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
排序缓存有一个参数是sort_buffer_size,如果这个参数大小足够上面范例中的N行的排序结果集(如果M也被定义,那就是M+N行的结果集大小),那么服务器将会避免一个文件排序操作,使得排序完全在内存中完成。
(1)内存排序+limit原理
1 扫描表,在内存中插入那些被选择排序的列的数据到一个排好序的队列中,比如order by col1,col2,则插入col1和col2列的数据。如果队列满了,则挤出排序在末尾的数据。
2 返回队列中的前N行记录,如果M也被定义,则调到第M行开始返回后续的N行记录。
(2)文件排序+limit原理
1扫描表,重复步骤2和3,直到表的结尾
2选中这些行数直到排序缓存被填满
3在排序缓存中写入第一个N行(如果M被定义,则M+N行)到一个排序文件中。
(3)两者比较
在内存中排序和使用文件排序相比,扫描表的代价几乎是一样的,不同的是其他的开销:
内存排序的方法在插入数据到一个有序队列中会牵扯到更多的cpu资源,而文件排序会消耗更多的磁盘IO,优化器在考虑两者的平衡性上会主要考虑N的值大小。
本文转载自:https://cloud.tencent.com/developer/article/1505252