记一次生产环境慢SQL优化思路与策略

SELECT
 COUNT(*) AS total_count 
FROM
`product_table`
WHERE
SHOW = 1
AND closed = 0
ANDSTATUS = 0
ANDNAMELIKE'%开源技术小栈%';

$count = self::$tableDb->where($where)->count();

$where = [
    'closed' => 0,
    'status' => 0,
    'show' => 1
];
if ($condition['name']) {
    $where['name'] = ['like', '%' . $condition['name'] . '%'];
}
// 慢查询
$count = self::$tableDb->where($where)->count();
$page = new \Think\PageNew($count, 10);
$list = self::$tableDb->where($where)
    ->limit($page->firstRow . ',' . $page->listRows)
    ->select();

$where = [
    'closed' => 0,
    'status' => 0,
    'show' => 1
];
if ($condition['name']) {
    $where['name'] = ['like', '%' . $condition['name'] . '%'];
}

$p = $condition['p'] ?? 1;
$page = new \Think\PageNew([]);
$list = self::$tableDb->where($where)->limit(($p - 1) * 10, 10)->select();
if (empty($list)) {
    return ['page' => $page->showNotLinkPage(1, 0), 'list' => []];
}

$count = count($list);
return ['page' => $page->showNotLinkPage($p, $count), 'list' => $list];