mysql这么慢？？？select * from list ORDER BY id DESC LIMIT 49990,10 竟然需要7秒？？！！

select * from list ORDER BY id DESC LIMIT 49990,10 一条语句竟然需要7秒的时间！！！？？？虽然是在奔腾200的机器上测试，但未必也太慢了吧？

下面表结构和程序体。还有服务器配置。
请各位帮忙看看到底是什么原因？，谢谢各位啦！

# 表结构 --------------------------------------------------
CREATE TABLE `list` (
 `id` int(10) unsigned auto_increment,
 `forum_id` int(10) unsigned NOT NULL default '0',
 `subject_id` int(10) unsigned default '0',
 `parents_id` int(10) unsigned default '0',
 `class` tinyint(3) unsigned default '0',
 `title` char(255) NOT NULL default '',
 `content_id` int(10) unsigned NOT NULL default '1',
 `content_length` mediumint(8) unsigned default '0',
 `user_id` int(10) unsigned NOT NULL default '0',
 `username` char(20) NOT NULL default '',
 `click_count` int(10) unsigned default '0',
 `reply_count` int(10) unsigned default '0',
 `post_time` int(10) unsigned NOT NULL default '0',
 `last_user_id` int(10) unsigned default NULL,
 `last_username` char(20) default NULL,
 `last_reply_time` int(10) unsigned NOT NULL default '0',
 `is_elite` enum('0','1') default '0',
 `is_close` enum('0','1') default '0',
 `is_del` enum('0','1') default '0',
 `is_authentication` enum('0','1') default '1',
 `upload_file_name` varchar(255) default NULL,
 `upload_file_newname` varchar(255) default NULL,
 `upload_pic_name` varchar(255) default NULL,
 `upload_pic_newname` varchar(255) default NULL,
 `master_score` smallint(5) unsigned default '0',
 `user_score` smallint(5) unsigned default '0',
 `edit_user_id` int(10) unsigned default NULL,
 `edit_user_name` char(20) default NULL,
 `edit_time` int(10) unsigned NOT NULL default '0',
 PRIMARY KEY (`id`),
 KEY `edit_time` (`edit_time`)
) TYPE=MyISAM COMMENT='文章数据';

# 程序体(测试时用这段程序随机灌了50万条数据) -----------------------
//config ----
$_CONFIG['SQL']['server'] = 'localhost';
$_CONFIG['SQL']['port'] = '3006';
$_CONFIG['SQL']['db'] = 'test';
$_CONFIG['SQL']['username'] = 'test';
$_CONFIG['SQL']['password'] = 'test';
$_CONFIG['SQL']['prefix'] = 'test_';
$_CONFIG['SQL']['persistency'] = true;

if ($_CONFIG['SQL']['persistency'] == true) {
$connect_id = @mysql_pconnect($_CONFIG['SQL']['server'].':'.$_CONFIG['SQL']['port'], $_CONFIG['SQL']['username'], $_CONFIG['SQL']['password']);
} else {
$connect_id = @mysql_connect($_CONFIG['SQL']['server'].':'.$_CONFIG['SQL']['port'], $_CONFIG['SQL']['username'], $_CONFIG['SQL']['password']);
}

if (!$connect_id) {
print 'Error(' . mysql_errno() . ') ' . mysql_error();
exit;
} else {
print '连接MySQL服务器成功！ ';
}

$select_db = @mysql_select_db($_CONFIG['SQL']['db'], $connect_id);
if (!$select_db) {
print 'Error(' . mysql_errno() . ') ' . mysql_error();
exit;
} else {
print '选择数据库成功！ ';
}

function rText($length = 10)
{
$pool = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz123456789";
srand ((double)microtime()*1000000);
for ($i=0; $i<$length; $i++) {
$rid .= substr($pool,(rand()%(strlen($pool))),1);
}
return $rid;
}

function rID($length = 1, $pool = '123456789')
{
srand ((double)microtime()*1000000);
for ($i = 0; $i < $length; $i++) {
$rid .= substr($pool,(rand()%(strlen($pool))),1);
}
return $rid;
}

// post ----
$id = 1;
$lock = mysql_query('LOCK TABLES list WRITE', $connect_id);
for ($i=0; $i<500000; $i++) {
$time = time();
$title = rText(100);
$forum_id = rID(1);
if ($i) {
$subject_id = rID(1, '111110');
} else {
$subject_id = 0;
}
if (!$subject_id) {
$class = 0;
$parents_id = 0;

$is_elite = rID(1, '0000000000000000000000000000000000000000000000000000000000000000000000010000000000000000000000000000');
if (!$is_elite) {
$is_close = rID(1, '0000000000000000000000000000000000000000000000000000000000010000000000000000000000000000000000000000');
$is_del = rID(1, '0000000000000000000000000000000000000000000000000000000000000000100000000000000000000000000000000000');
} else {
$is_close = 0;
$is_del = 0;
}
} else {
$class = 1;
$parents_id = $id;

$is_elite = 0;
$is_close = 0;
$is_del = rID(1, '0000000000000000000000000000000000000000000000000000000000000000100000000000000000000000000000000000');
}

$query = "
INSERT INTO
list( forum_id, subject_id, parents_id, class, title, content_id, content_length, user_id, username, click_count, reply_count, post_time, last_user_id, last_username, last_reply_time, is_elite, is_close, is_del, is_authentication, upload_file_name, upload_file_newname, upload_pic_name, upload_pic_newname, master_score, user_score, edit_user_id, edit_user_name, edit_time)
VALUES( '$forum_id', '$subject_id', '$parents_id', '$class', '$title', '1', '0', '1', 'user', '20', '30', '$time', '1', 'user', '$time', '$is_elite', '$is_close', '$is_del', '1', '', '', '', '', '0', '0', '0', '', '$time')
";

$sql = mysql_query($query, $connect_id);
if (!$subject_id) {
$id = mysql_insert_id($connect_id);
}
}
$lock = mysql_query('UNLOCK TABLES', $connect_id);

# 查询用的语句 ---------------------------------------------
#因为只是做测试用，所以只简单的写了一条语句，而且还是在MySQL-Front中执行的。
select * from list ORDER BY id DESC LIMIT 49990,10

# 服务器配置 -------------------------------------------------
奔腾200*2 96M IBM20G(7200) FreeBSD+PHP4.2.2+MySQL3.23

18 个解决方案

#1

这么惨，还有更惨的：
http://www.csdn.net/Expert/TopicView1.asp?id=982294

#2

很急，请帮帮我，解决后马上给分！

#3

1、当记录很多的时候，在查询的时候千万不要用select *,因为这样查出来的数据集一般很大的。即使你加的是limit子句。
2、当数据记录很多时，最好是手动的创建索引，并且创建索引一定要选一个最适合的键。当索引选择的不适合的时候比让系统自动创建索引更差劲。
3、在数据库设计的时候最好还是注意一些优化。
用mysql好久了，觉得它还是一个挺不错的数据库。我们一个系统是用SQL Server+asp，另几套系统是用MySQL+php,感觉还是MySQL+php更好一些。当然，MySQL+php是得用在linux下的，用apache作web服务器。

#4

丢丢：照你第一条所说，除select以外还有没有读取表数据的其它语句？
能否告诉我应该如何优化这个表？请据个例子。我对数据库不是很熟，请多帮忙啦。

#5

我必须使用select *，因为所有字段我都需要读取，这是没办法的。

#6

关注

#7

不妨试一下这个：

select SQL_BIG_RESULT * ...

SQL_BIG_RESULT 明确指示 MYSQL 该查询的结果集很“大”，有利于 MYSQL 进行优化

俺也很想知道 SQL_BIG_RESULT 到底有多大效果

#8

我做数据库的经验也不是很多，所有说一些也不知道对不对。如果不对请多多包涵。
首先是设计数据库时要尽可能坚持实体表与关系表相分离。你字段很多，但是冗余很大，当数据库大的时候你就得想法减小它的冗余。
我说的不要用select *不是指你不要用select语句,而是我想你这么多字段不会是同时使用吧？所以只查你需要某时候需要使用的字段。比如select last_user_id,last_username,last_reply_time ……
我一般在这种时候都是很用心的去设计数据库，然后查询通过建立临时表来实现。我发觉这种方法也挺有效的。
上面alexxing说的用"SQL_BIG_RESULT"，我也只听说过，没有使用过。
象你上面，数据库在实现的时候，事实上也相当于是先建立了一个临时表，然后取从49990开始的10条记录。时间主要是花在建立50000条记录集上。
说的不对的，请多多指教。

#9

SQL_BIG_RESULT应该是用在select * from table这样的大查询上的吧？
现在只需要选择10条记录。应该不算这个情况吧。

#10

squiral(丢丢)，如果像你所说，那mysql这个速度实在没法用，它不可能这么笨，还要建立50000条数据的临时表。
如果让我设计数据库(其实我自己做过将所有数据存入一个定长字段的文件中)。我通过索引直接从硬盘某个点读到另外一个点，这就读取了一条记录，然后多次循环得到10条。我想mysql也是这样的。

所谓临时表在我概念中应该是另外一种表类型(也就是内存表)，这个需要单独设置才行。

继续~~~继续~~~
大家踊跃发言~~~分不够再加！

#11

我也准备测测看。不过不知道我的测试机器P133/32M RAM的内存够不够。

#12

我说的不是指它真正建立了一个临时表，我是打了这样一个比方，可能不太准确吧。但是一条查询语句它不可能是一下子得到结果的，它是经过多个步骤进行筛选的。它当然不可能在内存中建立一个50000条的临时数据表，但是它处理的经理就和这类似。这就好比以前不推荐用A.id=B.id进行连接一样，因为它运用的是笛卡尔集，但是现在没有这种限制了吧？因为数据库实现时进行了优化，但它的机理还是这样的。

创建索引的目的就和你说的一样，如果能从索引找到记录它就可以直接从硬盘上取得数据，但是如果索引做得不合适的话，就不能运用索引了。这就是我为什么说要创建一个合适的索引的原因。

#13

在数据库方面我也不敢称高手，虽然也做过一些数据库方面的开发，大家刚够入门水平吧（绝对还有一只脚在门外）。所以得请各位朋友不吝赐教。

#14

squiral(丢丢) 依照搂住的设计,该如何创建合适的索引呢?

#15

跟主频无关，内存太小了……

#16

上面发错了一个字，应该是“大概刚够入门水平吧”，不是“大家”，特此更正。要不我可以犯众怒了。呵呵

#17

我的测试结果是：
select * from list ORDER BY id DESC LIMIT 49990,10
2.18 sec

select * from list ORDER BY id LIMIT 0,10
0.03 sec

你不如把排序改一下？

#18

这个问题我也遇到了，就算你把语句改成
select * from list ORDER BY id DESC LIMIT 0,10 还是会很慢，你知道为什么吗，你只要加上desc就会很慢，原因是因为你的PHP版本和mysql版本不一致造成的

#1