mysql一百万数据怎么分页,mysql百万条数据分页,该怎么处理

当前位置:我的异常网» MySQL » mysql百万条数据分页,该怎么处理

mysql百万条数据分页,该怎么处理

www.myexceptions.net  网友分享于：2013-10-22  浏览：46次

mysql百万条数据分页

我的ecs_goods表中有300万数据

我要进行分页

explain SELECT goods_id, goods_name, goods_type, goods_sn, shop_price, is_on_sale, is_best, is_new, is_hot, sort_order, goods_number, integral, (promote_price > 0 AND promote_start_date <= '1349942399' AND promote_end_date >= '1349942399') AS is_promote FROM `icmall`.`ecs_goods` AS g WHERE is_delete='0' AND is_real='1' ORDER BY goods_id DESC LIMIT 2111111,20

结果如下

1SIMPLEgrefdelete_real_goods_iddelete_real_goods_id2const,const2302639Using where

我在is_delete和is_real,goods_id上都建立了索引。但是分页查询。还是用了8.9s

is_delete 存储的字段值只有0和1两种情况

is_real存储的字段值只有0和1两种情况

goods_id是primary key auto_increment

请问该SQL有没优化余地

表结构

CREATE TABLE `ecs_goods` (

`goods_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`cat_id` smallint(5) unsigned NOT NULL DEFAULT '0',

`goods_sn` varchar(60) NOT NULL DEFAULT '',

`goods_name` varchar(120) NOT NULL DEFAULT '',

`goods_name_style` varchar(60) NOT NULL DEFAULT '+',

`click_count` int(10) unsigned NOT NULL DEFAULT '0',

`brand_id` smallint(5) unsigned NOT NULL DEFAULT '0',

`provider_name` varchar(100) NOT NULL DEFAULT '',

`goods_number` smallint(5) unsigned NOT NULL DEFAULT '0',

`goods_weight` decimal(10,3) unsigned NOT NULL DEFAULT '0.000',

`market_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',

`shop_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',

`promote_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',

`promote_start_date` int(11) unsigned NOT NULL DEFAULT '0',

`promote_end_date` int(11) unsigned NOT NULL DEFAULT '0',

`warn_number` tinyint(3) unsigned NOT NULL DEFAULT '1',

`keywords` varchar(255) NOT NULL DEFAULT '',

`goods_brief` varchar(255) NOT NULL DEFAULT '',

`goods_desc` text NOT NULL,

`goods_thumb` varchar(255) NOT NULL DEFAULT '',

`goods_img` varchar(255) NOT NULL DEFAULT '',

`original_img` varchar(255) NOT NULL DEFAULT '',

`is_real` tinyint(3) unsigned NOT NULL DEFAULT '1',

`extension_code` varchar(30) NOT NULL DEFAULT '',

`is_on_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',

`is_alone_sale` tinyint(1) unsigned NOT NULL DEFAULT '1',

`is_shipping` tinyint(1) unsigned NOT NULL DEFAULT '0',

`integral` int(10) unsigned NOT NULL DEFAULT '0',

`add_time` int(10) unsigned NOT NULL DEFAULT '0',

`sort_order` smallint(4) unsigned NOT NULL DEFAULT '100',

`is_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',

`is_best` tinyint(1) unsigned NOT NULL DEFAULT '0',

`is_new` tinyint(1) unsigned NOT NULL DEFAULT '0',

`is_hot` tinyint(1) unsigned NOT NULL DEFAULT '0',

`is_promote` tinyint(1) unsigned NOT NULL DEFAULT '0',

`bonus_type_id` tinyint(3) unsigned NOT NULL DEFAULT '0',

`last_update` int(10) unsigned NOT NULL DEFAULT '0',

`goods_type` smallint(5) unsigned NOT NULL DEFAULT '0',

`seller_note` varchar(255) NOT NULL DEFAULT '',

`give_integral` int(11) NOT NULL DEFAULT '-1',

`rank_integral` int(11) NOT NULL DEFAULT '-1',

`suppliers_id` smallint(5) unsigned DEFAULT NULL,

`is_check` tinyint(1) unsigned DEFAULT NULL

) ENGINE=MyISAM AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

索引

ecs_goods0PRIMARY1goods_idA2302652BTREE

ecs_goods1cat_id1cat_idA850BTREE

ecs_goods1brand_id1brand_idA348BTREE

ecs_goods1goods_type1goods_typeA1BTREE

ecs_goods1goods_name1goods_nameA2302652BTREE

ecs_goods1brand_goods_id1brand_goods_idA2302652BTREE

ecs_goods1delete_real_goods_id1is_deleteA1BTREE

文章评论