我用WIN98 CPU600 MEMORY 64 LIST 有580000条记录 ,ID为PRIMARY KEY select * from list ORDER BY id DESC LIMIT 49990,10 大概用了 2-3秒考虑到你的硬件情况,我想你的问题应该是正常的。本身用LIMIT X,Y定位,肯定比具体的值定位慢, 具体的值定位,用2分法查找,不出10次就可以找到, 因为索引树上只有“键值”和“该键所对应记录的物理位置” 没有“该键值在索引树中的顺序值”, 所以用LIMIT定位,要在索引树顺序查找,加上记录那么多 查找索引树也要花一定的时间。很多网页设计者 ,很喜欢用以上语句, 我建议用 SELECT * FROM LIST WHERE ID>=XXX ORDER BY ID DESC LIMIT 10 肯定会快很多
它的最大优点可是速度呀,
有硬盘整理过吗??
mysql的测试人员用5000万条记录的表都没有说慢慢的原因有很多,从磁盘的寻道能力,读写能力,cpu的周期,内存的限制--这些是系统瓶颈,没有办法逾越。
还有另外其他的因素,比如说一个数据库的表是不是过多?查询语句是否不够高效?连接是否过多?是否有过多的判断?太多了,最好有代码和测试环境的数据
请帮我看看是什么问题,谢谢各位啦!
# 表结构 --------------------------------------------------
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 '<b>Error(' . mysql_errno() . ')</b> ' . mysql_error();
exit;
} else {
print '连接MySQL服务器成功!<br>';
}$select_db = @mysql_select_db($_CONFIG['SQL']['db'], $connect_id);
if (!$select_db) {
print '<b>Error(' . mysql_errno() . ')</b> ' . mysql_error();
exit;
} else {
print '选择数据库成功!<br>';
}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
你的服务器,装了PHP与MYSQL
而你的内存!!再则,
你通过网络访问数据库,用的是select * 数据量有多大,算过吗,
我通过本机访问一个3M的图像还用了一两秒呢
LIST 有580000条记录 ,ID为PRIMARY KEY
select * from list ORDER BY id DESC LIMIT 49990,10
大概用了 2-3秒考虑到你的硬件情况,我想你的问题应该是正常的。本身用LIMIT X,Y定位,肯定比具体的值定位慢,
具体的值定位,用2分法查找,不出10次就可以找到,
因为索引树上只有“键值”和“该键所对应记录的物理位置”
没有“该键值在索引树中的顺序值”,
所以用LIMIT定位,要在索引树顺序查找,加上记录那么多
查找索引树也要花一定的时间。很多网页设计者 ,很喜欢用以上语句,
我建议用
SELECT * FROM LIST WHERE ID>=XXX ORDER BY ID DESC LIMIT 10
肯定会快很多