从MYSQL搜索日期间缓慢的PHP查询
问题描述：
我正在使用Codeigniter，当我尝试通过我的搜索引擎（php系统）查询数据库列表时，目前正面临极其缓慢的搜索结果。我相信这个问题与我的查询编码有很大关系，因为其余的页面显示数据库加载速度很快，但是当我在日期之间搜索数据库时，并不是这样。当我执行搜索时，该页面就像加载超过10分钟一样。请检查是什么毛病我的编码，也可以任何专家，请给我提供一个更好的建议，以提高我的编码：从MYSQL搜索日期间缓慢的PHP查询
public function get_transaction_record($search,$per_pg,$offset){
$fill = array((int)1);
$temp = array();
$data = array();
$sQuery = "SELECT t.*,p.product_name,d.deposit_id,w.withdrawal_id,m.username,m.lucky_id,m.contact_no,u.username as admin,
b.name as bankname,b.holder_name, d.promo as prm, m.fullname, d.turnover as tover
FROM tbl_transaction t
INNER JOIN tbl_product p ON t.product_id = p.product_id
INNER JOIN tbl_member m ON m.member_id = t.member_id
LEFT JOIN tbl_deposit d ON t.transaction_id = d.transaction_id
LEFT JOIN tbl_withdrawal w ON t.transaction_id = w.transaction_id
INNER JOIN tbl_banks b ON t.bank_id = b.bankID
LEFT JOIN tbl_promotion pro ON pro.promotion_id = d.promotion_id
LEFT JOIN tbl_user u ON u.user_id = t.admin_id WHERE 1 = ? AND t.status != 9";
if(strlen($search['date_from']) > 0 && strlen($search['date_to']) > 0 ){
$sQuery .= ' AND date(t.date_create) BETWEEN ? ';
array_push($fill,$search['date_from']);
$sQuery .= ' AND ? ';
array_push($fill,$search['date_to']);
}else{
if(strlen($search['date_from']) > 0){
$sQuery .= ' AND date(t.date_create) = ? ';
array_push($fill,$search['date_from']);
}
if(strlen($search['date_to']) > 0){
$sQuery .= ' AND date(t.date_create) = ? ';
array_push($fill,$search['date_to']);
}
}
if(strlen($search['status']) > 0){
$sQuery .= ' AND t.status = ? ';
array_push($fill,$search['status']);
}
if(strlen($search['contact_no']) > 0){
$sQuery .= ' AND m.contact_no LIKE "%"?"%" ';
array_push($fill,$search['contact_no']);
}
if(strlen($search['fullname']) > 0){
$sQuery .= ' AND m.fullname LIKE "%"?"%" ';
array_push($fill,$search['fullname']);
}
if(strlen($search['username']) > 0){
$sQuery .= ' AND m.username LIKE "%"?"%" ';
array_push($fill,$search['username']);
}
if(strlen($search['lucky_id']) > 0){
$sQuery .= ' AND m.lucky_id = ? ';
array_push($fill,$search['lucky_id']);
}
if(strlen($search['email']) > 0){
$sQuery .= ' AND m.email LIKE "%"?"%" ';
array_push($fill,$search['email']);
}
if(strlen($search['user_id']) > 0){
$sQuery .= ' AND t.admin_id = ? ';
array_push($fill,$search['user_id']);
}
if(strlen($search['product_id']) > 0){
$sQuery .= ' AND t.product_id = ? ';
array_push($fill,$search['product_id']);
}
if(strlen($search['bankID']) > 0){
$sQuery .= ' AND t.bank_id = ? ';
array_push($fill,$search['bankID']);
}
if(strlen($search['type']) > 0){
$sQuery .= ' AND t.type = ? ';
array_push($fill,$search['type']);
}
if(strlen($search['bankid2']) > 0){
$sQuery .= ' AND w.bankid2 = ? ';
array_push($fill,$search['bankid2']);
}
// $sQuery .= " ORDER BY t.date_create DESC LIMIT ?,? ";
$sQuery .= " ORDER BY t.date_create DESC LIMIT ?,? ";
// $sQuery .= " ORDER BY CASE t.status WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN -1 THEN 9 END, t.date_create DESC LIMIT ?,? ";
array_push($fill,(int)$offset,(int)$per_pg);
$query = $this->db->query($sQuery, $fill);
//echo $this->db->last_query();
if($query->num_rows() > 0){
foreach ($query->result() as $rows):
$temp['bankname'] = $rows->bankname;
$temp['holder_name'] = $rows->holder_name;
$temp['ref'] = $rows->ref;
$temp['username'] = $rows->username;
$temp['fullname'] = $rows->fullname;
$temp['lucky_id'] = $rows->lucky_id;
$temp['admin'] = $rows->admin;
$temp['contact_no'] = $rows->contact_no;
$temp['product_name'] = $rows->product_name;
$temp['promo'] = $rows->promo;
$temp['transaction_id'] = $rows->transaction_id;
$temp['member_id'] = $rows->member_id;
$temp['type'] = $rows->type;
$temp['product_id'] = $rows->product_id;
$temp['deposit_id'] = $rows->deposit_id;
$temp['withdrawal_id'] = $rows->withdrawal_id;
$temp['amount'] = $rows->amount;
$temp['turnover'] = $rows->turnover;
$temp['status'] = $rows->status;
$temp['remark'] = $rows->remark;
$temp['day'] = $rows->day;
$temp['month'] = $rows->month;
$temp['year'] = $rows->year;
$temp['date_create'] = $rows->date_create;
$temp['date_update'] = $rows->date_update;
$temp['create_by'] = $rows->create_by;
$temp['prm'] = $rows->prm;
$temp['tover'] = $rows->tover;
if($rows->promotion_id > 0){
$temp['promo'] = $this->get_promotion_details($rows->promotion_id);
}else{
$temp['promo'] = NULL;
}
array_push($data, $temp);
endforeach;
}
return $data;
}
更新代码如上
IMG FOR tbl_transaction SHOW CREATE TABLE
感谢。
答
首先，您没有为您的交易表定义索引。所以任何搜索都被迫做一次表扫描。使用索引来加速搜索很重要。
您可能会喜欢我的演示文稿How to Design Indexes, Really。或视频：https://www.youtube.com/watch?v=ELR7-RdU9XU
这可能是最重要的指标，为您打造：
ALTER TABLE tbl_transaction ADD INDEX (create_date);
但是你有一个指数，即使后，当您尝试对表达式搜索类似下面，它可以不使用索引。
date(t.date_create) BETWEEN ? AND ?
你必须重写它使索引列是孤独：
t.date_create BETWEEN ? AND ?
但是，这意味着你要第一个参数是“地板”到时间00:00:00，第二个参数在23:59:59时间内“到达”，以确保任何时间组件的date_create与您希望匹配的日期范围相匹配。
if(strlen($search['date_from']) > 0 && strlen($search['date_to']) > 0 ){
$from = search['date_from'] . ' 00:00:00';
$to = search['date_to'] . ' 23:59:59';
$sQuery .= ' AND t.date_create BETWEEN ? AND ?';
array_push($fill,$from);
array_push($fill,$to);
}
同样，即使是搜索一个特定日期，你还要做一个范围：
else if(strlen($search['date_from']) > 0){
$from = search['date_from'] . ' 00:00:00';
$to = search['date_from'] . ' 23:59:59';
$sQuery .= ' AND t.date_create BETWEEN ? AND ?';
array_push($fill,$from);
array_push($fill,$to);
}
如果你读过我在设计指标的介绍，你会注意到一个范围条件就像create_date上的那个意味着索引中的任何后续列都无助于搜索。您可以使用多列索引，但等于条件中涉及的所有列必须位于索引左侧。
此外，根据用户的搜索条件，您有多个条件可能存在也可能不存在。这也很难优化，因为索引搜索仅使用索引的列从左到右。如果因为搜索不需要而忽略列，则后续列将无济于事。
您搜索的另一种条件是与LIKE '%'?''匹配的文本模式，而不是相等条件。这些LIKE搜索不能使用传统索引，但它们可以使用特殊的fulltext search index。我也有一个演示文稿：Full Text Search Throwdown。视频：https://www.youtube.com/watch?v=V8yA8C3CZOc
当您询问查询优化帮助时，请始终在查询中为所有表发布“SHOW CREATE TABLE”，为查询发布“EXPLAIN”。并且不要让我们跟踪你的代码来找出最终的SQL。向我们展示SQL，而不是构建SQL字符串的代码。 –
@Randall一个很长的列表，因为系统每天涉及超过10k行数据库条目。但是，如果我查看页面而不搜索某个日期，它的加载速度相当快。 –
'date（）'不能使用索引 – Strawberry