首页 > SQL数据库管理 > mysql分页的limit优化

mysql分页的limit优化

2015年9月20日

mysql分页的limit优化

http://www.cnblogs.com/xuxiang/p/3983973.html

1、很多新人都会很纳闷,为什么我建了索引使用分页还是这么卡。好,现在让我们一步一步去找原因吧。

首先limit本身跟索引没有直接关系。

先建一张商品sku表

create table goods_sku
(
id int(10) unsigned not null auto_increment comment ‘自增ID’,
goods_id varchar(20) not null comment ‘商品id’,
sale_status tinyint comment ‘上下架状态(0下架,1上架)’,
added_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘上家日期’,
drop_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘下架时间’,
`is_del` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘删除标记(0未删除 1删除)’,
KEY `index_goods_id` (`goods_id`),
KEY `index_sale_status` (`sale_status`),
KEY `index_added_time` (`added_time`),
primary key (id)
) comment = ‘商品SKU表’ ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> explain select * from goods_sku limit 0,10;
+—-+————-+———–+——+—————+——+———+——+——–+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——+—————+——+———+——+——–+——-+
| 1 | SIMPLE | goods_sku | ALL | NULL | NULL | NULL | NULL | 107950 | |
+—-+————-+———–+——+—————+——+———+——+——–+——-+
1 row in set (0.00 sec)

ps: 因为没走索引,所以进行了全表扫描,现在是10万条数据,试想一下100万的情况下是怎么样。这么简单的一条sql就会让你机器卡爆。我现在就想一条数据,使用索引看看

mysql> explain select * from goods_sku where sale_status=1 limit 0,10;
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
| id | select_type | table     | type | possible_keys     | key               | key_len | ref   | ro
ws | Extra       |
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
|  1 | SIMPLE      | goods_sku | ref  | index_sale_status | index_sale_status | 2       | const | 46
25 | Using where |
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
1 row in set (0.10 sec)

虽然走了索引,但是受影响的条数还是4000多条

mysql> explain select * from goods_sku order by id desc limit 0,10;
+—-+————-+———–+——-+—————+———+———+——+——+——-+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+—-+————-+———–+——-+—————+———+———+——+——+——-+
|  1 | SIMPLE      | goods_sku | index | NULL          | PRIMARY | 8       | NULL |   10 |       |
+—-+————-+———–+——-+—————+———+———+——+——+——-+
1 row in set (0.00 sec)

这个受影响的条件为10条,看来limit和order by 联用可以真正限制输出的数量,但是order by 后面的字段一定是建了索引的

通过上面我们可能得出一个结论,limit前加一个order by 就可以,但事实是否如此呢,再看一个例子

mysql> explain select * from brand order by english_name limit 0,10;
+—-+————-+——-+——+—————+——+———+——+——+—————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+—————-+
| 1 | SIMPLE | brand | ALL | NULL | NULL | NULL | NULL | 581 | Using filesort |
+—-+————-+——-+——+—————+——+———+——+——+—————-+
1 row in set (0.00 sec)

注:type为all,天呀,虽然english_name建了索引,再了order by竟然没走索引,这跟上面所说的加个order by就走索引不是矛盾吗。我们再看一个例子

mysql> explain SELECT english_name FROM brand ORDER BY english_name LIMIT 0,10;
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ex
tra |
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+
| 1 | SIMPLE | brand | index | NULL | index_english_name | 302 | NULL | 10 | Us
ing index |
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+

注: 虽然*包含english_name,但加和不加是不一样的,尤其后面加了order by,由此可知,order by 的东西,前面select一定要出现,除非是主鍵id

分类: SQL数据库管理 标签:
本文的评论功能被关闭了.