网站老是卡死 原因是mysql排序问题 用show processlist 查看发现有很多进程都是“Sorting result”状态 多的时候上百个 导致服务器内存飙升 首先是用户查询的表卡死 接着 整个网站都无法访问!下面是PHP代码 数据显示 和 分页部分 高手帮我看看 是什么问题 表数据7W左右 表已经加了索引 查询速度很快 都是0.00几秒 因网站需要 排序时按照时间倒序排列的<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}$currentPage = $_SERVER["PHP_SELF"];$maxRows_treat = 15;
$pageNum_treat = 0;
if (isset($_GET['pageNum_treat'])) {
$pageNum_treat = $_GET['pageNum_treat'];
}
$startRow_treat = $pageNum_treat * $maxRows_treat;mysql_select_db($database_ws, $ws);
$query_treat = "SELECT `id`, `image_thum`,`title`,`content`,`number_processing`,`valid_date`,`province`,`city`,`date`FROM `treat` WHERE `lock_state` = '1' AND `Categories` = '加工'ORDER BY date DESC ";
$query_limit_treat = sprintf("%s LIMIT %d, %d", $query_treat, $startRow_treat, $maxRows_treat);
$treat = mysql_query($query_limit_treat, $ws) or die(mysql_error());
$row_treat = mysql_fetch_assoc($treat);if (isset($_GET['totalRows_treat'])) {
$totalRows_treat = $_GET['totalRows_treat'];
} else {
$all_treat = mysql_query($query_treat);
$totalRows_treat = mysql_num_rows($all_treat);
}
$totalPages_treat = ceil($totalRows_treat/$maxRows_treat)-1;$queryString_treat = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_treat") == false &&
stristr($param, "totalRows_treat") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_treat = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_treat = sprintf("&totalRows_treat=%d%s", $totalRows_treat, $queryString_treat);
}
?>数据显示*********************************************<?php do { ?>
<a href="w_info.php?id=<?php echo $row_treat['id']; ?>" <img src="<?php echo empty($row_treat['image_thum']);?>" </a>
<a href="w_info.php?id=<?php echo $row_treat['id']; ?><?php echo $row_treat['title']; ?>
<?php echo $row_treat['number_processing']; ?><?php echo $row_treat['province']; ?> <?php echo $row_treat['city']; ?><?php echo $row_treat['valid_date']; ?><?php echo $row_treat['date']; ?> <?php echo $row_treat['content'; ?><?php } while ($row_treat = mysql_fetch_assoc($treat)); ?>
数据分页*************************************************** 当前第<?php echo $pageNum_treat + 1 ?> ]页 共[ <?php echo ceil($totalRows_treat/$maxRows_treat) ?> ]页<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, 0, $queryString_treat); ?>" >首页<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, max(0, $pageNum_treat - 1), $queryString_treat); ?>" class="STYLE3">[ 上一页 ]
<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, min($totalPages_treat, $pageNum_treat + 1), $queryString_treat); ?>" >[ 下一页 ]<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, $totalPages_treat, $queryString_treat); ?>" class="STYLE3">最后一页
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue); switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}$currentPage = $_SERVER["PHP_SELF"];$maxRows_treat = 15;
$pageNum_treat = 0;
if (isset($_GET['pageNum_treat'])) {
$pageNum_treat = $_GET['pageNum_treat'];
}
$startRow_treat = $pageNum_treat * $maxRows_treat;mysql_select_db($database_ws, $ws);
$query_treat = "SELECT `id`, `image_thum`,`title`,`content`,`number_processing`,`valid_date`,`province`,`city`,`date`FROM `treat` WHERE `lock_state` = '1' AND `Categories` = '加工'ORDER BY date DESC ";
$query_limit_treat = sprintf("%s LIMIT %d, %d", $query_treat, $startRow_treat, $maxRows_treat);
$treat = mysql_query($query_limit_treat, $ws) or die(mysql_error());
$row_treat = mysql_fetch_assoc($treat);if (isset($_GET['totalRows_treat'])) {
$totalRows_treat = $_GET['totalRows_treat'];
} else {
$all_treat = mysql_query($query_treat);
$totalRows_treat = mysql_num_rows($all_treat);
}
$totalPages_treat = ceil($totalRows_treat/$maxRows_treat)-1;$queryString_treat = "";
if (!empty($_SERVER['QUERY_STRING'])) {
$params = explode("&", $_SERVER['QUERY_STRING']);
$newParams = array();
foreach ($params as $param) {
if (stristr($param, "pageNum_treat") == false &&
stristr($param, "totalRows_treat") == false) {
array_push($newParams, $param);
}
}
if (count($newParams) != 0) {
$queryString_treat = "&" . htmlentities(implode("&", $newParams));
}
}
$queryString_treat = sprintf("&totalRows_treat=%d%s", $totalRows_treat, $queryString_treat);
}
?>数据显示*********************************************<?php do { ?>
<a href="w_info.php?id=<?php echo $row_treat['id']; ?>" <img src="<?php echo empty($row_treat['image_thum']);?>" </a>
<a href="w_info.php?id=<?php echo $row_treat['id']; ?><?php echo $row_treat['title']; ?>
<?php echo $row_treat['number_processing']; ?><?php echo $row_treat['province']; ?> <?php echo $row_treat['city']; ?><?php echo $row_treat['valid_date']; ?><?php echo $row_treat['date']; ?> <?php echo $row_treat['content'; ?><?php } while ($row_treat = mysql_fetch_assoc($treat)); ?>
数据分页*************************************************** 当前第<?php echo $pageNum_treat + 1 ?> ]页 共[ <?php echo ceil($totalRows_treat/$maxRows_treat) ?> ]页<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, 0, $queryString_treat); ?>" >首页<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, max(0, $pageNum_treat - 1), $queryString_treat); ?>" class="STYLE3">[ 上一页 ]
<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, min($totalPages_treat, $pageNum_treat + 1), $queryString_treat); ?>" >[ 下一页 ]<a href="<?php printf("%s?pageNum_treat=%d%s", $currentPage, $totalPages_treat, $queryString_treat); ?>" class="STYLE3">最后一页
解决方案 »
- 如何提取两个字段里的交集?
- mysql 崩溃,Incorrect information in file: '.\pas_system\sys_downloadconfig.frm'怎么恢
- 这两个的SQL的效率谁好点?
- MySQL 问题,急求
- 用VB怎么向mysql存储图片,我用下面的语句,出现的错误是...是什么原因?急!!!!!!!!!
- [Mysql]谁帮我解决这个中文问题,100分相送!不够还可以再加!
- 为什么SELECT 0 = 'x6'结果为true?
- C#代码实现 mysql数据库备份
- mysql浮点数计算
- MySQL运行语句报错
- 求一段MYSQL搜索修改语句
- select问题,搜了很久都没搜到,求教,谢谢!
已经搞定了 确实是索引建立不当引起的 对lock_state和date 建立了复合索引 问题解决了