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

[飞天猪猪侠]

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语句还有何可优化的地方，谢谢！

[WWWWA]

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='玻璃' ;

[WWWWA]

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;

[飞天猪猪侠]

引用 1 楼 WWWWA 的回复:

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 DE……

这语句不行啊，查询不到数据： /*[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;

[rucypli]

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

[飞天猪猪侠]

引用 2 楼 WWWWA 的回复:

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.……

不行，报错： 错误码: 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

[飞天猪猪侠]

引用 4 楼 rucypli 的回复:

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

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

[WWWWA]

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='玻璃' ;

[飞天猪猪侠]

引用 7 楼 WWWWA 的回复:

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 W……

语句是不报错了，可是仍旧查询不到数据。

[飞天猪猪侠]

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

[WWWWA]

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

[飞天猪猪侠]

引用 10 楼 WWWWA 的回复:

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

唉，不行，只要多了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; 奔溃了要

[WWWWA]

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; 速度如何

[飞天猪猪侠]

引用 12 楼 WWWWA 的回复:

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 ……

/*[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数字类型做了索引啊，搞不懂为什么还是这样

[飞天猪猪侠]

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

[WWWWA]

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

[飞天猪猪侠]

引用 15 楼 WWWWA 的回复:

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

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

[Neptune_yin]

帮顶一个，觉得是很好的问题