Mysql之limit优化
2017-04-20 本文已影响0人
1519f8ccc7b0
1 limit的工作原理
limit的使用方式比较简单select * from table order by id limit m,n ,其工作原理就是，服务器从存储引擎取出m+n条数据，然后丢弃掉m条数据，只保留最后的n条。
显然，当m比较大的时候，如此使用limit会造成巨大的浪费（无效的数据传输）
2 limit的正确打开方式
了解了它的工作原理之后，我们就可以采用扬长避短的方式来使用它，当m比较小的时候，如何使用都不成问题；但是当m比较大的时候，我们就应该考虑性能问题了。
解决思路有两种：
- 就是改写sql，降低m的值。
- 降低无效的m的数据传输量，如至传id而不是全部字段。
常用的方法有两种：
2.1 采用更精确的查询条件，降低m的值
如我们可以通过某些标记字段，如id来代替m，将sql改写为:
select * from table where id>m order by id limit n
2.2 采用内连接的方式，降低数据传输量
select * from table inner join (select id from table order by id limit m,n ) as b using(id);
3 案例验证
3.1 案例命令
mysql> show create table testdb.user\G;
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`firstname` varchar(60) NOT NULL DEFAULT '',
`lastname` varchar(60) DEFAULT NULL,
`age` int(11) NOT NULL,
`province` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fisrt_last_idx` (`firstname`,`lastname`),
KEY `age_idx` (`age`),
KEY `p` (`province`),
CONSTRAINT `fk_p` FOREIGN KEY (`province`) REFERENCES `province` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100015 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show profiles;
Empty set, 1 warning (0.00 sec)
mysql> select count(1) from testdb.user;
+----------+
| count(1) |
+----------+
| 100013 |
+----------+
1 row in set (0.02 sec)
mysql> select * from testdb.user limit 100000,1;
+--------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack | 9000 | 40 | 2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.05 sec)
mysql> select * from testdb.user where id > 100000 limit 1;
+--------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack | 9000 | 40 | 2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.00 sec)
mysql> select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+-------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+-------+-----------+----------+-----+----------+
| 99460 | Jack | 4655 | 49 | 2 |
+-------+-----------+----------+-----+----------+
1 row in set (0.03 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------------------------------------+
| 1 | 0.02759500 | select count(1) from testdb.user |
| 2 | 0.05340900 | select * from testdb.user limit 100000,1 |
| 3 | 0.00030100 | select * from testdb.user where id > 100000 limit 1 |
| 4 | 0.02979400 | select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id) |
+----------+------------+-------------------------------------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000045 |
| checking permissions | 0.000006 |
| Opening tables | 0.000016 |
| init | 0.000016 |
| System lock | 0.000008 |
| optimizing | 0.000004 |
| statistics | 0.000019 |
| preparing | 0.000010 |
| executing | 0.000002 |
| Sending data | 0.053204 |
| end | 0.000010 |
| query end | 0.000004 |
| closing tables | 0.000008 |
| freeing items | 0.000017 |
| logging slow query | 0.000026 |
| cleaning up | 0.000014 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 3;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000053 |
| checking permissions | 0.000006 |
| Opening tables | 0.000016 |
| init | 0.000022 |
| System lock | 0.000007 |
| optimizing | 0.000008 |
| statistics | 0.000056 |
| preparing | 0.000012 |
| executing | 0.000002 |
| Sending data | 0.000035 |
| end | 0.000004 |
| query end | 0.000006 |
| closing tables | 0.000007 |
| freeing items | 0.000016 |
| logging slow query | 0.000039 |
| cleaning up | 0.000012 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 4;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000078 |
| checking permissions | 0.000003 |
| checking permissions | 0.000005 |
| Opening tables | 0.003272 |
| init | 0.000433 |
| System lock | 0.000017 |
| optimizing | 0.000005 |
| optimizing | 0.000004 |
| statistics | 0.000016 |
| preparing | 0.000130 |
| statistics | 0.000136 |
| preparing | 0.000015 |
| executing | 0.000011 |
| Sending data | 0.000016 |
| executing | 0.000003 |
| Sending data | 0.025462 |
| end | 0.000011 |
| query end | 0.000006 |
| closing tables | 0.000002 |
| removing tmp table | 0.000007 |
| closing tables | 0.000008 |
| freeing items | 0.000073 |
| logging slow query | 0.000063 |
| cleaning up | 0.000018 |
+----------------------+----------+
24 rows in set, 1 warning (0.00 sec)
mysql> explain select * from testdb.user limit 100000,1;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 99933 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)
mysql> explain select * from testdb.user where id > 100000 limit 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | user | range | PRIMARY | PRIMARY | 4 | NULL | 13 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 99933 | NULL |
| 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | NULL |
| 2 | DERIVED | user | index | NULL | age_idx | 4 | NULL | 99933 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------+-------+-------------+
3 rows in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.user limit 100000,1;
+--------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack | 9000 | 40 | 2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.04 sec)
mysql> show status like '%handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 100001 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+--------+
18 rows in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.user where id > 100000 limit 1;
+--------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+--------+-----------+----------+-----+----------+
| 100001 | Jack | 9000 | 40 | 2 |
+--------+-----------+----------+-----+----------+
1 row in set (0.00 sec)
mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
+----------------------------+-------+
18 rows in set (0.00 sec)
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.user inner join (select id from testdb.user limit 100000,1) as b using(id);
+-------+-----------+----------+-----+----------+
| id | firstname | lastname | age | province |
+-------+-----------+----------+-----+----------+
| 99460 | Jack | 4655 | 49 | 2 |
+-------+-----------+----------+-----+----------+
1 row in set (0.03 sec)
mysql> show status like '%handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 4 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 100000 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 1 |
+----------------------------+--------+
18 rows in set (0.00 sec)
3.2 案例分析
通过案例，可以看到：
采用方式1改写的sql由于减少了从存储引擎层至服务器层的数据传输条数（从10001减少到1），间接减少了数据传输量，最后查询时间大为降低（0.05秒减少至0.00秒）；
采用方式2改写的sql，虽然没有减少数据传输条数（依然是10000条），但每条的数据量大为减少（从*到id），所以也减少了数据传输量，最后查询时间也大为降低（0.05秒减少至0.03秒）；
