mysql数据库单表有100万+数据，查询超慢，如何优化呢？

Question

Slow.log：
# Time: 130328 13:06:36
# User@Host: xxx
# Query_time: 104.718750  Lock_time: 0.000000 Rows_sent: 15  Rows_examined: 1303751
SET timestamp=1364447196;SELECT * FROM quotes WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;索引如下：
Indexes  Columns  Index_Type
PRIMARY  itemid   Unique
suoyin   pubdate, itemid, title, group, province字段信息：
Field     Type   Comment
itemid    int(10) unsigned
group     varchar(100)
title     varchar(50)
nickname  varchar(50)
province  varchar(50)
price     varchar(50)
price_min varchar(50)
price_max varchar(50)
unit      varchar(10)
telphone  varchar(50)
email     varchar(100)
message   text
ip        varchar(20)
addtime   int(10) unsigned
open      smallint(1) unsigned
status    smallint(1)
pubdate   int(11)quotes表中有140万条数据，大小920M，索引80M，如果SQL语句换成 SELECT * FROM quotes WHERE title='玻璃' ORDER BY itemid DESC LIMIT 0,15; 网站就可以勉强正常打开，对比之前的SQL语句也就是多了一次排序而已，且这个pubdate字段也做了索引，不知为何效率会差这么悬殊，求教各位高手，这种情况如何解决，索引和SQL语句还有何可优化的地方，谢谢！数据库mysql优化sqlquery

Answer 1

EXPLAIN SELECT * FROM quotes WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;
SELECT * FROM quotes A 
INNER JOIN (SELECT * FROM quotes
ORDER BY pubdate DESC,itemid DESC LIMIT 0,15
) B
ON A.itemid=B.itemid AND A.pubdate=B.pubdate
WHERE A.title='玻璃' ;

Answer 2

SELECT * FROM quotes A FORCE INDEX suoyin
 INNER JOIN (SELECT * FROM quotes
 ORDER BY pubdate DESC,itemid DESC LIMIT 0,15
 ) B
 ON A.itemid=B.itemid AND A.pubdate=B.pubdate
 WHERE A.title='玻璃' ;SELECT * FROM quotes FORCE INDEX suoyin WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;

Answer 3

这语句不行啊，查询不到数据：
/*[14:15:41][  16 ms]*/ SHOW STATUS;
/*[14:15:55][13985 ms]*/ SELECT * FROM quotes WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;
/*[14:15:55][  15 ms]*/ SHOW STATUS;
/*[14:15:55][   0 ms]*/ EXPLAIN EXTENDED SELECT * FROM quotes WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;
/*[14:15:55][  16 ms]*/ SHOW WARNINGS;
=======================================================
/*[14:17:57][  16 ms]*/ SHOW STATUS;
/*[14:17:57][   0 ms]*/ SELECT * FROM lx_quotes_cn A INNER JOIN (SELECT * FROM lx_quotes_cn ORDER BY pubdate DESC,itemid DESC LIMIT 0,15) B ON A.itemid=B.itemid AND A.pubdate=B.pubdate WHERE A.title='玻璃' ;
/*[14:17:57][  15 ms]*/ SHOW STATUS;
/*[14:17:57][   0 ms]*/ EXPLAIN EXTENDED SELECT * FROM lx_quotes_cn A INNER JOIN (SELECT * FROM lx_quotes_cn ORDER BY pubdate DESC,itemid DESC LIMIT 0,15) B ON A.itemid=B.itemid AND A.pubdate=B.pubdate WHERE A.title='玻璃' ;
/*[14:17:57][   0 ms]*/ SHOW WARNINGS;

Answer 4

alter table tbname add index(title,pubdate,itemid)

Answer 5

不行，报错：错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'suoyin WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15' at line 1

Answer 6

title,pubdate,itemid 这三个字段在表创建之初就已经建了索引的

Answer 7

SELECT * FROM quotes A FORCE INDEX (suoyin)
  INNER JOIN (SELECT * FROM quotes
  ORDER BY pubdate DESC,itemid DESC LIMIT 0,15
  ) B
  ON A.itemid=B.itemid AND A.pubdate=B.pubdate
  WHERE A.title='玻璃' ;

Answer 8

你这语句是 先获取出15条不带WHERE条件排序好的数据，然后再筛选Title=‘玻璃’的记录吧

Answer 9

SELECT * FROM quotes FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;

Answer 10

唉，不行，只要多了pubdate排序，立马卡死/*[14:44:24][   0 ms]*/ SHOW VARIABLES LIKE '%profiling%';
/*[14:44:24][  32 ms]*/ SHOW STATUS;
/*[14:44:24][  15 ms]*/ SHOW STATUS;
/*[14:44:39][14360 ms]*/ SELECT * FROM quotes FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;
/*[14:44:39][  16 ms]*/ SHOW STATUS;
/*[14:44:39][  15 ms]*/ EXPLAIN EXTENDED SELECT * FROM quotes FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY pubdate DESC,itemid DESC LIMIT 0,15;
/*[14:44:39][   0 ms]*/ SHOW WARNINGS;
=============================================================
/*[14:45:00][  16 ms]*/ SHOW STATUS;
/*[14:45:01][1406 ms]*/ SELECT * FROM lx_quotes_cn FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY itemid DESC LIMIT 0,15;
/*[14:45:01][  16 ms]*/ SHOW STATUS;
/*[14:45:01][   0 ms]*/ EXPLAIN EXTENDED SELECT * FROM lx_quotes_cn FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY itemid DESC LIMIT 0,15;
/*[14:45:01][   0 ms]*/ SHOW WARNINGS;奔溃了要 

Answer 11

explain SELECT * FROM quotes FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY pubdate DESC LIMIT 0,15;
贴结果SELECT * FROM quotes FORCE INDEX (suoyin ) WHERE title='玻璃' ORDER BY pubdate DESC LIMIT 0,15;
速度如何

Answer 12

/*[14:57:47][   0 ms]*/ EXPLAIN SELECT * FROM lx_quotes_cn FORCE INDEX (suoyin) WHERE title='玻璃' ORDER BY pubdate DESC LIMIT 0,15;/*[14:58:54][12656 ms]*/ SELECT * FROM lx_quotes_cn FORCE INDEX (suoyin) WHERE title='玻璃' ORDER BY pubdate DESC LIMIT 0,15;pubdate数字类型做了索引啊，搞不懂为什么还是这样

Answer 13

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE lx_quotes_cn index \N suoyin 614 \N 15 Using where

Answer 14

从结果上看，索引已经用上了
SHOW　INDEX　FROM XXX

Answer 15

itemid和pubdate都是int，只不过itemid是主键而已，为啥就相差这么多...... 纠结