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
ecs_goods1delete_real_goods_id2is_realA1BTREE
ecs_goods1delete_real_goods_id3goods_idA2302652BTREE
如果要对is_on_sale ,is_best,is_hot,goods_number分别排序。又该如何建索引呢。数据这么大。
[解决办法]
建立联合索引
alter table xxx add index(is_delete,is_real,goods_i)
[解决办法]
LIMIT 2111111,20 偏移量一大, 即使有索引, 也是一样慢的, 索引是用来match值的, 如果是用来顺序扫描,也是很慢的.
还是用6楼的方法吧, 改表一下思路吧.
[解决办法]
支持6楼，这两个字段只有0和1，选择性低，用索引相当于全表扫描
select ..... from `icmall`.`ecs_goods` AS g join
(select goods_id FROM `icmall`.`ecs_goods` WHERE is_delete='0' AND is_real='1' ORDER BY goods_id DESC LIMIT 2111111,20
) as t using (goods_id)
