MySQL数据库中limit语句若何优化_远程数据恢复 方
日期:2014-07-13 / 人气: / 来源:网络
准备数据:tudou@gyyx
mysql> show create table tmpg
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tmp | CREATE TABLE `tmp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`stat` enum('1','0','2') DEFAULT '1',
PRIMARY KEY (`id`),
KEY `ix_ics` (`stat`,`ctime`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1 |
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
存储过程:tudou@gyyx
CREATE PROCEDURE `proc_buildata`(IN loop_times INT)
BEGIN
DECLARE var INT DEFAULT 0;
WHILE var<loop_times DO
SET var=var 1;
INSERT INTO tmp (ctime,stat) VALUES (NOW(),MOD(var,3));
END WHILE;
END
执行查询:tudou@gyyx
mysql> SELECT id FROM tmp WHERE stat=2 ORDER BY ctime LIMIT 900000,20;
---------
| id |
---------
| 2700002 |
| 2700005 |
| 2700008 |
| 2700011 |
| 2700014 |
| 2700017 |
| 2700020 |
| 2700023 |
| 2700026 |
| 2700029 |
| 2700032 |
| 2700035 |
| 2700038 |
| 2700041 |
| 2700044 |
| 2700047 |
| 2700050 |
| 2700053 |
| 2700056 |
| 2700059 |
---------
20 rows in set (0.38 sec)
执行计划:tudou@gyyx mysql> EXPLAIN SELECT id FROM tmp WHERE stat=2 ORDER BY ctime LIMIT 900000,20;
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
| 1 | SIMPLE | tmp | ref | ix_ics | ix_ics | 2 | const | 1500178 | Using where; Using index |
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
1 row in set (0.00 sec)
查询结果:tudou@gyyx mysql> SELECT a.* FROM tmp a WHERE id in(2700002,2700005,2700008,2700011,2700014,2700017,2700020,2700023,2700026,2700029,2700032,2700035,2700038,2700041,2700044,2700047,2700050,2700053,2700056,2700059);
--------- --------------------- ------
| id | ctime | stat |
--------- --------------------- ------
| 2700002 | 2012-04-19 15:52:13 | 0 |
| 2700005 | 2012-04-19 15:52:13 | 0 |
| 2700008 | 2012-04-19 15:52:13 | 0 |
| 2700011 | 2012-04-19 15:52:13 | 0 |
| 2700014 | 2012-04-19 15:52:13 | 0 |
| 2700017 | 2012-04-19 15:52:13 | 0 |
| 2700020 | 2012-04-19 15:52:13 | 0 |
| 2700023 | 2012-04-19 15:52:13 | 0 |
| 2700026 | 2012-04-19 15:52:13 | 0 |
| 2700029 | 2012-04-19 15:52:13 | 0 |
| 2700032 | 2012-04-19 15:52:13 | 0 |
| 2700035 | 2012-04-19 15:52:13 | 0 |
| 2700038 | 2012-04-19 15:52:13 | 0 |
| 2700041 | 2012-04-19 15:52:13 | 0 |
| 2700044 | 2012-04-19 15:52:13 | 0 |
| 2700047 | 2012-04-19 15:52:13 | 0 |
| 2700050 | 2012-04-19 15:52:13 | 0 |
| 2700053 | 2012-04-19 15:52:13 | 0 |
| 2700056 | 2012-04-19 15:52:13 | 0 |
| 2700059 | 2012-04-19 15:52:13 | 0 |
--------- --------------------- ------
20 rows in set (0.00 sec)
优化查询:tudou@gyyx mysql> SELECT a.* FROM tmp a
-> INNER JOIN (SELECT id FROM tmp b WHERE b.stat=2 ORDER BY b.ctime DESC LIMIT 900000,20) c on c.id=a.id
-> ;
-------- --------------------- ------
| id | ctime | stat |
-------- --------------------- ------
| 299999 | 2012-04-19 15:48:48 | 0 |
| 299996 | 2012-04-19 15:48:48 | 0 |
| 299993 | 2012-04-19 15:48:48 | 0 |
| 299990 | 2012-04-19 15:48:48 | 0 |
| 299987 | 2012-04-19 15:48:48 | 0 |
| 299984 | 2012-04-19 15:48:48 | 0 |
| 299981 | 2012-04-19 15:48:48 | 0 |
| 299978 | 2012-04-19 15:48:48 | 0 |
| 299975 | 2012-04-19 15:48:48 | 0 |
| 299972 | 2012-04-19 15:48:48 | 0 |
| 299969 | 2012-04-19 15:48:48 | 0 |
| 299966 | 2012-04-19 15:48:48 | 0 |
| 299963 | 2012-04-19 15:48:48 | 0 |
| 299960 | 2012-04-19 15:48:48 | 0 |
| 299957 | 2012-04-19 15:48:48 | 0 |
| 299954 | 2012-04-19 15:48:48 | 0 |
| 299951 | 2012-04-19 15:48:48 | 0 |
| 299948 | 2012-04-19 15:48:48 | 0 |
| 299945 | 2012-04-19 15:48:48 | 0 |
| 299942 | 2012-04-19 15:48:48 | 0 |
-------- --------------------- ------
20 rows in set (0.60 sec)
mysql> show create table tmpg
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| tmp | CREATE TABLE `tmp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ctime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`stat` enum('1','0','2') DEFAULT '1',
PRIMARY KEY (`id`),
KEY `ix_ics` (`stat`,`ctime`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3000001 DEFAULT CHARSET=latin1 |
------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
存储过程:tudou@gyyx
CREATE PROCEDURE `proc_buildata`(IN loop_times INT)
BEGIN
DECLARE var INT DEFAULT 0;
WHILE var<loop_times DO
SET var=var 1;
INSERT INTO tmp (ctime,stat) VALUES (NOW(),MOD(var,3));
END WHILE;
END
执行查询:tudou@gyyx
mysql> SELECT id FROM tmp WHERE stat=2 ORDER BY ctime LIMIT 900000,20;
---------
| id |
---------
| 2700002 |
| 2700005 |
| 2700008 |
| 2700011 |
| 2700014 |
| 2700017 |
| 2700020 |
| 2700023 |
| 2700026 |
| 2700029 |
| 2700032 |
| 2700035 |
| 2700038 |
| 2700041 |
| 2700044 |
| 2700047 |
| 2700050 |
| 2700053 |
| 2700056 |
| 2700059 |
---------
20 rows in set (0.38 sec)
执行计划:tudou@gyyx mysql> EXPLAIN SELECT id FROM tmp WHERE stat=2 ORDER BY ctime LIMIT 900000,20;
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
| 1 | SIMPLE | tmp | ref | ix_ics | ix_ics | 2 | const | 1500178 | Using where; Using index |
---- ------------- ------- ------ --------------- -------- --------- ------- --------- --------------------------
1 row in set (0.00 sec)
查询结果:tudou@gyyx mysql> SELECT a.* FROM tmp a WHERE id in(2700002,2700005,2700008,2700011,2700014,2700017,2700020,2700023,2700026,2700029,2700032,2700035,2700038,2700041,2700044,2700047,2700050,2700053,2700056,2700059);
--------- --------------------- ------
| id | ctime | stat |
--------- --------------------- ------
| 2700002 | 2012-04-19 15:52:13 | 0 |
| 2700005 | 2012-04-19 15:52:13 | 0 |
| 2700008 | 2012-04-19 15:52:13 | 0 |
| 2700011 | 2012-04-19 15:52:13 | 0 |
| 2700014 | 2012-04-19 15:52:13 | 0 |
| 2700017 | 2012-04-19 15:52:13 | 0 |
| 2700020 | 2012-04-19 15:52:13 | 0 |
| 2700023 | 2012-04-19 15:52:13 | 0 |
| 2700026 | 2012-04-19 15:52:13 | 0 |
| 2700029 | 2012-04-19 15:52:13 | 0 |
| 2700032 | 2012-04-19 15:52:13 | 0 |
| 2700035 | 2012-04-19 15:52:13 | 0 |
| 2700038 | 2012-04-19 15:52:13 | 0 |
| 2700041 | 2012-04-19 15:52:13 | 0 |
| 2700044 | 2012-04-19 15:52:13 | 0 |
| 2700047 | 2012-04-19 15:52:13 | 0 |
| 2700050 | 2012-04-19 15:52:13 | 0 |
| 2700053 | 2012-04-19 15:52:13 | 0 |
| 2700056 | 2012-04-19 15:52:13 | 0 |
| 2700059 | 2012-04-19 15:52:13 | 0 |
--------- --------------------- ------
20 rows in set (0.00 sec)
优化查询:tudou@gyyx mysql> SELECT a.* FROM tmp a
-> INNER JOIN (SELECT id FROM tmp b WHERE b.stat=2 ORDER BY b.ctime DESC LIMIT 900000,20) c on c.id=a.id
-> ;
-------- --------------------- ------
| id | ctime | stat |
-------- --------------------- ------
| 299999 | 2012-04-19 15:48:48 | 0 |
| 299996 | 2012-04-19 15:48:48 | 0 |
| 299993 | 2012-04-19 15:48:48 | 0 |
| 299990 | 2012-04-19 15:48:48 | 0 |
| 299987 | 2012-04-19 15:48:48 | 0 |
| 299984 | 2012-04-19 15:48:48 | 0 |
| 299981 | 2012-04-19 15:48:48 | 0 |
| 299978 | 2012-04-19 15:48:48 | 0 |
| 299975 | 2012-04-19 15:48:48 | 0 |
| 299972 | 2012-04-19 15:48:48 | 0 |
| 299969 | 2012-04-19 15:48:48 | 0 |
| 299966 | 2012-04-19 15:48:48 | 0 |
| 299963 | 2012-04-19 15:48:48 | 0 |
| 299960 | 2012-04-19 15:48:48 | 0 |
| 299957 | 2012-04-19 15:48:48 | 0 |
| 299954 | 2012-04-19 15:48:48 | 0 |
| 299951 | 2012-04-19 15:48:48 | 0 |
| 299948 | 2012-04-19 15:48:48 | 0 |
| 299945 | 2012-04-19 15:48:48 | 0 |
| 299942 | 2012-04-19 15:48:48 | 0 |
-------- --------------------- ------
20 rows in set (0.60 sec)
MySQL数据库中limit语句优化过程
测试环境操作系统: debian linux服务器版本: Mysql 5.0.24Mysql数据库的Qcache缓存关闭数据库表testtable的参数:类型: MyISAM 大小:>80MB 记录规模: >50000 字段数: >25个字段id是主键 lei
MySQL安装
作者:管理员
推荐内容 Recommended
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19
相关内容 Related
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19