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

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

结果如下
1 SIMPLE g ref delete_real_goods_id delete_real_goods_id 2 const,const 2302639 Using 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_goods 0 PRIMARY 1 goods_id A 2302652 BTREE
ecs_goods 1 cat_id 1 cat_id A 850 BTREE
ecs_goods 1 brand_id 1 brand_id A 348 BTREE
ecs_goods 1 goods_type 1 goods_type A 1 BTREE
ecs_goods 1 goods_name 1 goods_name A 2302652 BTREE
ecs_goods 1 brand_goods_id 1 brand_goods_id A 2302652 BTREE
ecs_goods 1 delete_real_goods_id 1 is_delete A 1 BTREE

喜欢 (0)赏

[搞代码]

分享 (0)