mysql limit 扫描行数 & order by 与索引关系_select a, d where a =1 order by 2 limit 10 扫描多少行

作者：Monodyee | 2024-04-26 18:04:12

踩

select a, d where a =1 order by 2 limit 10 扫描多少行

转： http://hi.baidu.com/dbaeyes/blog/item/eaf04a878bc6f326c65cc3c3.html

小测一下 mysql的limit 的扫描行数和order by和索引的关系

--查询条件字段都在索引里，数据都已经在数据库cache中
索引如下
KEY `IDX_USER_ID` USING BTREE (`user_id`,`do_date`,`SUS`,`r_type`,`rat`)

root@my_db 11:25:04>explain SELECT t2.* from
-> (select id
->    FROM my_test_table FF
->   WHERE user_id = 888
->     AND SUS = 0
->     AND r_type = 0
->     AND do_date > '2009-09-21 23:59:59'
->     AND do_date <= '2010-5-17 23:59:59'
->     AND rat = 1
->   ORDER BY do_date DESC
->   LIMIT 8000, 20) t1, my_test_table t2
->   where t1.id = t2.id/G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 20
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: t1.id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: FF
type: range
possible_keys: IDX_FDATE,IDX_USER_ID
      key: IDX_USER_ID
key_len: 24
ref: NULL
rows: 1187276
Extra: Using where; Using index
3 rows in set (0.01 sec)

--
| Sending data       | 0.007728 |
--

root@my_db 11:27:14>
root@my_db 11:27:27>explain SELECT t2.* from
-> (select id
->    FROM my_test_table FF
->   WHERE user_id = 888
->     AND SUS = 0
->     AND r_type = 0
->     AND do_date > '2009-09-21 23:59:59'
->     AND do_date <= '2010-5-17 23:59:59'
->     AND rat = 1
->   ORDER BY do_date DESC
->   LIMIT 80000, 20) t1, my_test_table t2
->   where t1.id = t2.id/G
*************************** 1. row ***************************
。。。。省略。。。。
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: FF
type: range
possible_keys: IDX_FDATE,IDX_USER_ID
key: IDX_USER_ID
key_len: 24
ref: NULL
rows: 1187278
Extra: Using where; Using index
3 rows in set (0.08 sec)

--
| Sending data       | 0.076469 |
--

可以看到在使用 LIMIT 8000, 20 和 LIMIT 80000, 20 的执行计划完全一样，都使用索引扫描并用索引进行排序。虽然在执行计划中看到的扫描行数都是相同的 1187263，但从实际测试来看，当使用LIMIT 8000, 20 用的时间（0.007728）是 LIMIT 80000, 20 （0.076469）的10倍

所以可以确定使用limit 扫描数据的时候，当扫描到满足条件的记录之后，不会再继续扫描下去，而是直接返回数据结果
--这个是和oracle的rownum < 的执行计划是类似的

-------------------------
--再看一种情况，

root@my_db 11:55:51>explain SELECT t2.* from
-> (select id
->    FROM my_test_table FF
->   WHERE user_id = 888
->     AND SUS = 0
->     AND r_type = 0
    ->     AND do_date > '2010-4-21 23:59:59'
->     AND do_date <= '2010-5-17 23:59:59'
->     AND rat = 1
->   ORDER BY id DESC --使用ID排序
->   LIMIT 8000, 40) t1, my_test_table t2
->   where t1.id = t2.id/G
*************************** 1. row ***************************
...
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: FF
type: index
possible_keys: IDX_FDATE,IDX_USER_ID
     key: PRIMARY
key_len: 8
ref: NULL
rows: 246140
Extra:
3 rows in set (0.15 sec)

--索引直接走错，
| Sorting result     | 0.152575 |
| Sending data       | 0.002491 |

相同的查询条件如果根据do_date排序，那么时间在0.001以下

--去掉时间范围
SELECT t2.* from
(select id
FROM my_test_table FF
WHERE user_id = 888
AND SUS = 0
AND r_type = 0
AND rat = 1
ORDER BY id DESC
LIMIT 8000, 40) t1, my_test_table t2
where t1.id = t2.id;

...
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: FF
type: ALL
possible_keys: IDX_USER_ID
   key: IDX_USER_ID
key_len: 9
ref:
rows: 1188230
Extra: Using filesort
...
3 rows in set (0.92 sec)

| Sorting result     | 0.931782 |
| Sending data       | 0.002151 |

虽然走对了索引，但是主键ID从索引组织结构看，在索引的最后一位，所以排序消耗了相当大的资源
而且因为ID是主键，所以在存在其他查询条件（存在索引）的情况下，也可能也会走上主键索引导致错误的执行计划

----------

对于数据量比较大，而且执行量很高的分页sql，尽可能将所有的查询字段包括在索引中，同时使用索引来消除排序。

-- 关于mysql limit的具体信息可以参考手册
http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html

7.2.17. `LIMIT` Optimization

In some cases, MySQL handles a query differently when you are using LIMIT row_count and not using HAVING:

If you are selecting only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.
If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause must be selected, and most or all of them must be sorted, before it can be ascertained that the first row_count rows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.
When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.
In some cases, a GROUP BY can be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.
As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS.
LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor (the mysql program), which merely displays Empty set in such cases; you should instead use SHOW COLUMNS or DESCRIBE for this purpose.)
When the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/492395

mysql limit 扫描行数 & order by 与索引关系_select a, d where a =1 order by 2 limit 10 扫描多少行

7.2.17. LIMIT Optimization

7.2.17. `LIMIT` Optimization