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

在开发过程中，我们遇到了一段用于从数据库中查询部门信息的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 BY 和 LIMIT 子句，因为这些对于计数操作是不必要的。
• 执行查询并获取所有符合条件的记录数量，存储在 $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. 逻辑清晰：确保代码逻辑清晰，易于理解和维护。

@漏刻有时