php开发实战分析(8):优化MySQL分页查询与数量统计,提升数据库性能

发布于:2024-11-03 ⋅ 阅读:(40) ⋅ 点赞:(0)

在开发过程中,我们遇到了一段用于从数据库中查询部门信息的PHP代码。该代码负责根据不同的条件(如部门名称和来源)筛选数据,并返回分页结果及总记录数。然而,原始代码存在一些问题,包括重复的查询条件构建逻辑、缺乏对SQL注入的防护以及代码逻辑不够清晰。
如:


$sql = "select depart_id,md5(concat(depart_id,'LOCKDATAV')) AS depart_code,depart_name,depart_origin,depart_sort FROM " . $db->table("depart") . " WHERE 1";
if ($depart_name != "") {
    $sql .= " AND depart_name like '%" . $depart_name . "%'";
}

if ($depart_origin != "") {
    $sql .= " AND depart_origin = " . $depart_origin;
}

$sql .= " ORDER BY depart_id DESC LIMIT " . $limit . "," . $pagesize;
$row = $db->queryall($sql);

//获取总记录;
$sql_c = "select depart_id FROM " . $db->table('depart') . " WHERE 1";
if ($depart_name != "") {
    $sql_c .= " AND depart_name like '%" . $depart_name . "%'";
}
if ($depart_origin != "") {
    $sql_c .= " AND depart_origin = " . $depart_origin;
}
$sql .= " ORDER BY depart_id";
$row_c = $db->queryall($sql_c);

/*信息输出*/
$res['code'] = 0;
$res['msg'] = 0;
$res['count'] = count($row_c);
$res["data"] = $row;
die(json_encode_lockdata($res));

上面代码是用于从数据库中查询部门(depart)的信息,并返回一个JSON格式的结果:

数据查询部分

  1. SQL语句构建:

    • 初始的SQL语句选择了depart_id, depart_code (通过MD5哈希函数计算出的值), depart_name, depart_origin, 和 depart_sort 字段。
    • depart_code 是通过将 depart_id 和字符串 'LOCKDATAV' 连接后进行MD5哈希计算得到的。这可能用于生成唯一的标识符或密钥。
    • SQL语句使用了表别名 $db->table("depart"),这通常是一个方法调用,用来确保表名的正确性,比如添加前缀等。
  2. 条件过滤:

    • 如果变量 $depart_name 不为空,则在SQL语句中加入一个条件,使结果只包含名称中包含 $depart_name 的记录。
    • 同样地,如果 $depart_origin 不为空,则进一步限制结果为特定来源的部门。
  3. 排序与分页:

    • 结果按照 depart_id 降序排列。
    • 使用 LIMIT 子句实现分页功能,$limit$pagesize 变量定义了从哪一行开始以及返回多少行数据。
  4. 执行查询:

    • 使用 $db->queryall($sql) 执行SQL查询,获取所有匹配的记录。这里假设 $db 是一个数据库连接对象,提供了查询数据库的方法。

获取总记录数

  • 构建了一个类似的SQL语句来计算符合条件的总记录数,但是没有包括 ORDER BYLIMIT 子句,因为这些对于计数操作是不必要的。
  • 执行查询并获取所有符合条件的记录数量,存储在 $row_c 中。

结果组装

  • 创建了一个数组 $res,其中包含了响应的状态码 (code)、消息 (msg)、总记录数 (count) 以及查询到的数据 (data)。
  • 使用 json_encode_lockdata 函数将结果转换为JSON格式,并输出。

安全性和性能考虑

  • SQL注入风险:直接将用户输入(如 $depart_name$depart_origin)拼接到SQL语句中存在SQL注入的风险。建议使用预处理语句或者参数化查询来避免这个问题。
  • 性能优化:在获取总记录数时,可以考虑使用 COUNT(*) 而不是 SELECT * 来减少数据传输量,提高效率。
// 构建基本查询条件
$base_sql = "SELECT depart_id, MD5(CONCAT(depart_id, 'LOCKDATAV')) AS depart_code, depart_name, depart_origin, depart_sort 
             FROM " . $db->table("depart") . " WHERE 1";

// 构建计数查询条件
$count_sql = "SELECT COUNT(*) AS total_count 
              FROM " . $db->table("depart") . " WHERE 1";

// 添加查询条件
$conditions = [];
$params = [];

if (!empty($depart_name)) {
    $conditions[] = "depart_name LIKE ?";
    $params[] = '%' . $depart_name . '%';
}

if (!empty($depart_origin)) {
    $conditions[] = "depart_origin = ?";
    $params[] = $depart_origin;
}

if (!empty($conditions)) {
    $base_sql .= ' AND ' . implode(' AND ', $conditions);
    $count_sql .= ' AND ' . implode(' AND ', $conditions);
}

// 构建完整的查询语句
$base_sql .= " ORDER BY depart_id DESC LIMIT :limit, :pagesize";
$params[] = (int)$limit;
$params[] = (int)$pagesize;

// 执行查询
$row = $db->queryall($base_sql, $params);

// 执行计数查询
$row_c = $db->queryone($count_sql, $params);

/* 信息输出 */
$res = [
    'code' => 0,
    'msg' => '',
    'count' => $row_c['total_count'],
    'data' => $row
];

echo json_encode_lockdata($res);
  1. 减少重复代码:查询条件部分在两个SQL语句中是相同的,可以将其提取出来,避免重复编写。
  2. 安全性和性能:使用预编译语句来防止SQL注入攻击,并可能提高查询性能。
  3. 逻辑清晰:确保代码逻辑清晰,易于理解和维护。

@漏刻有时