耗1分钟的sql 语句怎么优化

Question

SELECT `Cmbook`.`id` AS `id`, `Cmbook`.`bid` AS `bid`, `Cmbook`.`name` AS `name`, `Cmbook`.`author` AS `author`, `Cmbook`.`introduction` AS `introduction`, `Cmbook`.`cover_url` AS `cover_url`, `NewBook`.`come` FROM `new_books` AS `NewBook` LEFT JOIN cmbooks AS `Cmbook` ON (`Cmbook`.`bid` = `NewBook`.`bid`) WHERE `NewBook`.`class_id` = 3007 AND `NewBook`.`come` = 0 ORDER BY `NewBook`.`position` desc, `NewBook`.`id` ASC LIMIT 20 我只是取20条数据 可是竟然用了1分钟
EXPLAIN SELECT `Cmbook`.`id` AS `id` , `Cmbook`.`bid` AS `bid` , `Cmbook`.`name` AS `name` , `Cmbook`.`author` AS `author` , `Cmbook`.`introduction` AS `introduction` , `Cmbook`.`cover_url` AS `cover_url` , `NewBook`.`come`
FROM `new_books` AS `NewBook`
LEFT JOIN cmbooks AS `Cmbook` ON ( `Cmbook`.`bid` = `NewBook`.`bid` )
WHERE `NewBook`.`class_id` =3007
AND `NewBook`.`come` =0
ORDER BY `NewBook`.`position` DESC , `NewBook`.`id` ASC
LIMIT 20 的结果如下id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  NewBook  ref  class_id  class_id  4  const  1026  Using where; Using temporary; Using filesort
1  SIMPLE  Cmbook  ALL  bid  NULL  NULL  NULL  24497  CREATE TABLE IF NOT EXISTS `cmbooks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bid` varchar(16) DEFAULT NULL,
  `cid` varchar(30) DEFAULT NULL COMMENT '第一章的cid',
  `name` varchar(128) DEFAULT NULL,
  `author` varchar(64) DEFAULT NULL,
  `introduction` varchar(512) DEFAULT NULL,
  `cover_url` varchar(128) DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0' COMMENT '0:unknown 1:updateing 2:finished',
  `click_num` varchar(40) NOT NULL DEFAULT '' COMMENT '点击',
  `word_num` varchar(40) NOT NULL DEFAULT '' COMMENT '字数',
  `class` int(10) NOT NULL DEFAULT '0' COMMENT '分类',
  `word_scope` int(2) NOT NULL DEFAULT '0' COMMENT '字数范围 1（0-20）2（20-50）3（50-100）4（100-200）5（200-300）6 （300以上）',
  `like` varchar(500) NOT NULL DEFAULT '' COMMENT '猜你喜欢',
  PRIMARY KEY (`id`),
  UNIQUE KEY `bid` (`bid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24863 ;CREATE TABLE IF NOT EXISTS `new_books` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `class_id` int(5) NOT NULL DEFAULT '0' COMMENT '三极分类id',
  `bid` int(15) NOT NULL DEFAULT '0' COMMENT '书的bid',
  `come` int(1) NOT NULL DEFAULT '0' COMMENT '0代表采集的1代表自定义',
  `position` int(5) NOT NULL DEFAULT '0' COMMENT '位置',
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`),
  KEY `bid` (`bid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='新书库表' AUTO_INCREMENT=32837 ;
 索引我也加过了 可是为什么这么慢怎么优化 cmbooks 表有25000条记录 new_books 表有30000条记录
 

Answer 1

create index xxx on NewBook(class_id,come,position,id);

Answer 2

别惊讶  取20条也是全部数据排序了才能给你limit 20

Answer 3

我添加了一个复合索引EXPLAIN SELECT `Cmbook`.`id` AS `id` , `Cmbook`.`bid` AS `bid` , `Cmbook`.`name` AS `name` , `Cmbook`.`author` AS `author` , `Cmbook`.`introduction` AS `introduction` , `Cmbook`.`cover_url` AS `cover_url` , `NewBook`.`come`
FROM `new_books` AS `NewBook`
FORCE INDEX ( class_id_come )
LEFT JOIN cmbooks AS `Cmbook` ON ( `Cmbook`.`bid` = `NewBook`.`bid` )
WHERE `NewBook`.`class_id` =3007
AND `NewBook`.`come` =0
ORDER BY `NewBook`.`position` DESC , `NewBook`.`id` ASC
LIMIT 20
[ 编辑 ] [ 略过解释 SQL ] [ 创建 PHP 代码 ]
+ 选项
部分文字
完整文字
显示二进制内容
显示 BLOB 内容
隐藏 浏览器转换
id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1  SIMPLE  NewBook  ref  class_id_come  class_id_come  8  const,const  981  Using where; Using filesort
1  SIMPLE  Cmbook  ref  bid  bid  33  ebook.NewBook.bid  1  Extra  里面说有Using filesort 还可以优化 请问怎么优化啊

Answer 4

贴出 show index from ..

Answer 5

using filesort 仅仅表明你的排序走的不是索引、也不一定还有优化空间！你的sql：同时使用了排序和分页【 order by和limit】、如果没有索引会很慢、不过、即使有索引、如果用户界面上需要翻页、并且翻页翻到比较靠后时查询也可能非常慢所以、无论如何创建索引、这种查询都是个严重的问题、因为随着偏移量的增加、mysql需要花费大量的时间来扫描需要丢弃的数据方案如下、仅供参考1）限制用户能够翻页的数量、即limit
2）重写这条sql、使用延迟关联：通过使用覆盖索引查询返回需要的主键、再依据这些主键关联原表获得需要的行