php网页访问导致数据库CPU爆满

Question

我用PHP做了联合两个表为一个逻辑的查询 作为网页的每一页的翻页博客展示

SELECT id,title,time,flag FROM (
select id,title,time,'zhuan' flag from zhuan 
UNION ALL
select id,title,time,'post' from post) a GROUP BY time DESC LIMIT 0,10;

我服务器使用的是双核CPU 我在访问一次页面mysql的CPU占用率达到10-20%
现在就造成一个困扰 假如我反复刷新页面 CPU直接飙升到 100%以上
我应该从哪个方面解决 优化SQL语句 还是把整个页面初次全部缓存到redis里面?

Answer 1

你这个每次查询都要生成一个临时表,等于两次以上全表扫描,占用大量IO.
我的优化思路是,既然每次都要生成一张临时表,还不如自己新建一张表,只保留必要的信息,列表查询的时候只查这张表就够了.例如:

CREATE TABLE post_union{
`id` bigint(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`record_type` tinyint(2) unsigned NOT NULL default 1 COMMENT '类型,1表示文章,2表示专题',
`origin_id` bigint(32) unsigned NOT NULL COMMENT '在原表的原始ID',
`title` varchar(255) NOT NULL default COMMENT '标题',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `index_time` (`time`)
} ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个思路很简单,做好索引的话,列表页能用比较简明的代码达到比较合理性能,而且扩展起来也方便,比如你以后又多一个类型的文章想要加入到列表索引中的话,只要新增一个record_type即可.
缺点就是要再加一个表,维护起来会麻烦点(维护另外几张表的增删改时要同步到这张表上).
当然这个表也可以写到redis上.这样性能会更好.

Answer 2

看不大出来你要实现什么功能，但单看sql，里面的union，两个表都是全表查询出来再过滤！如果这两个表再很大，当然很慢。

话说GROUP BY time desc？什么意思，你time仅到年月日？

如果是这样，建议把group by和limit放到每个union查询里

Answer 3

首先,分组是GROUP BY,排序是用ORDER BY.

你把两个表的数据(30万行)全部查询出来作为一个临时表,然后还要对这个临时表进行ORDER BY time排序操作,肯定慢呀.

想把专题(zhuan)和文章(post)在前端混合在一起展示并分页,你完全可以换个思路,比如:
从专题中取2篇,从文章中取8篇,然后让PHP对这2+8=10篇内容按时间进行排序并输出.

SQL分页公式: $offset = ($page-1) * $page_size;
SELECT * FROM zhuan ORDER BY id DESC LIMIT 2 OFFSET $offset;
SELECT * FROM post  ORDER BY id DESC LIMIT 8 OFFSET $offset;

因为自增ID就反映了时间先后,所以可以直接按自增ID这个主键进行排序,对于聚簇索引的InnoDB来说性能更好.

另外,PHP数组合并排序操作举例如下:

$post = array(
    0 => array(
        'time' => 8,
    ),
    1 => array(
        'time' => 7,
    ),
    3 => array(
        'time' => 6,
    ),
);
$zhuan = array(
    0 => array(
        'time' => 7,
    )
);
$arr = array_merge($post, $zhuan);
uasort($arr, function($a, $b) {
    if($a['time'] === $b['time']) return 0;
    else return ($a['time'] < $b['time']) ? 1 : -1;
});
var_export($arr);
//输出
array (
  0 => 
  array (
    'time' => 8,
  ),
  3 => 
  array (
    'time' => 7,
  ),
  1 => 
  array (
    'time' => 7,
  ),
  2 => 
  array (
    'time' => 6,
  ),
)

最后,如果早知有这个要求,当初就应该把"专题"当做一种特殊的"文章"存储在同一张表.

Answer 4

查询数据量大而且用过多的union，还有子查询这些都是效率比较低的

Answer 5

看不出你这连表的意义在哪里，感觉你只是想从这两个表里取出来最近的十条记录，limit哪里优化一下先。

Answer 6

我不清楚你的UNION ALL 想要做什么，但是我感觉问题出在这里。
你不放把需求说出来。或许可以用别的sql去处理这个问题或者可以改一下数据库scheme。