mysql limit 扫描行数 & order by 与索引关系

转： 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.