当前位置：首页 > CMS教程 > Discuz > 列表

discuz热帖翻页的数据库mysql优化

发布:smiling　来源: PHP粉丝网 　添加日期：2015-05-08 09:12:24　浏览: 评论：0　

discuz是一个强大的论坛系统,不过在他的热门帖子翻页的性能不高,SQL没有很好的优化,很容易把数据库服务器直接压垮.

在我们的环境中,使用的是 MySQL-5.6.6 版本,在查看帖子并翻页过程中,会产生类似下面这样的SQL:

  1. mysql> desc SELECT * FROM pre_forum_post WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline DESC LIMIT 15\G 
  3.  *************************** 1. row *************************** 
  4.  id: 1  --phpfensi.com 
  5.  select_type: SIMPLE 
  6.  table: pre_forum_post 
  7.  type: ref 
  8.  possible_keys: tid,displayorder,first 
  9.  key: displayorder 
  10.  key_len: 3 
  11.  ref: const 
  12.  rows: 593371 
  13.  Extra: Using index condition; Using where; Using filesort 

这个SQL执行的代价是:

-- 根据索引访问行记录次数,总体而言算是比较好的状态.

| Handler_read_key           | 16     |

-- 根据索引顺序访问下一行记录的次数,通常是因为根据索引的范围扫描,或者全索引扫描，总体而言也算是比较好的状态

| Handler_read_next          | 329881 |

-- 按照一定顺序读取行记录的总次数,如果需要对结果进行排序,该值通常会比较大,当发生全表扫描或者多表join无法使用索引时,该值也会比较大.

| Handler_read_rnd           | 15     |

而当遇到热帖需要往后翻很多页时,例如:

  1. mysql> desc SELECT * FROM pre_forum_post WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860, 15\G 
  3.  *************************** 1. row *************************** 
  4.  id: 1 
  5.  select_type: SIMPLE 
  6.  table: pre_forum_post 
  7.  type: ref 
  8.  possible_keys: displayorder 
  9.  key: displayorder 
  10.  key_len: 3 
  11.  ref: const 
  12.  rows: 593371 
  13.  Extra: Using where; Using filesort 

这个SQL执行的代价则变成了,可以看到Handler_read_key、Handler_read_rnd大了很多.

  1. | Handler_read_key           | 129876 | -- 因为前面需要跳过很多行记录 
  2. | Handler_read_next          | 329881 | -- 同上 
  3. | Handler_read_rnd           | 129875 | -- 因为需要先对很大一个结果集进行排序 

可见,遇到热帖时,这个SQL的代价会非常高,如果该热帖被大量的访问历史回复,或者被搜素引擎一直反复请求并且历史回复页时,很容易把数据库服务器直接压垮.

小结:这个SQL不能利用 `displayorder` 索引排序的原因是,索引的第二个列 `invisible` 采用范围查询(RANGE),导致没办法继续利用联合索引完成对 `dateline` 字段的排序需求(而如果是 WHERE tid =? AND invisible IN(?, ?)AND dateline =? 这种情况下是完全可以用到整个联合索引的,注意下二者的区别).

知道了这个原因,相应的优化解决办法也就清晰了:

创建一个新的索引 idx_tid_dateline,它只包括 tid、dateline 两个列即可(根据其他索引的统计信息,item_type 和 item_id 的基数太低,所以没包含在联合索引中,当然了,也可以考虑一并加上).

我们再来看下采用新的索引后的执行计划:

  1. mysql> desc SELECT * FROM pre_forum_post WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 15\G 
  3.  *************************** 1. row *************************** 
  4.  id: 1 
  5.  select_type: SIMPLE 
  6.  table: pre_forum_post 
  7.  type: ref 
  8.  possible_keys: tid,displayorder,first,idx_tid_dateline 
  9.  key: idx_tid_dateline 
  10.  key_len: 3 
  11.  ref: const 
  12.  rows: 703892 
  13.  Extra: Using where 

可以看到,之前存在的 Using filesort 消失了,可以通过索引直接完成排序了,不过,如果该热帖翻到较旧的历史回复时,相应的SQL还是不能使用新的索引:

  1. mysql> desc SELECT * FROM pre_forum_post WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15\G 
  3.  *************************** 1. row *************************** 
  4.  id: 1 
  5.  select_type: SIMPLE 
  6.  table: pre_forum_post 
  7.  type: ref 
  8.  possible_keys: tid,displayorder,first,idx_tid_dateline 
  9.  key: displayorder  --phpfensi.com 
  10.  key_len: 3 
  11.  ref: const 
  12.  rows: 593371 
  13.  Extra: Using where; Using filesort 

对比下如果建议优化器使用新索引的话,其执行计划是怎样的:

  1. mysql> desc SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline  LIMIT 129860,15\G 
  3.  *************************** 1. row *************************** 
  4.  id: 1 
  5.  select_type: SIMPLE 
  6.  table: pre_forum_post 
  7.  type: ref 
  8.  possible_keys: idx_tid_dateline 
  9.  key: idx_tid_dateline 
  10.  key_len: 3 
  11.  ref: const 
  12.  rows: 703892 
  13.  Extra: Using where 

可以看到,因为查询优化器认为后者需要扫描的行数远比前者多了11万多,因此认为前者效率更高,事实上,在这个例子里,排序的代价更高,因此我们要优先消除排序,所以应该强制使用新的索引,也就是采用后面的执行计划,在相应的程序中指定索引.

最后,我们来看下热帖翻到很老的历史回复时,两个执行计划分别的profiling统计信息对比.

1、采用旧索引(displayorder):

  1. ​mysql> SELECT * FROM pre_forum_post WHERE 
  2.  
  3.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15; 
  4.  
  5. #查看profiling结果 
  6.  | starting             | 0.020203 | 
  7.  | checking permissions | 0.000026 | 
  8.  | Opening tables       | 0.000036 | 
  9.  | init                 | 0.000099 | 
  10.  | System lock          | 0.000092 | 
  11.  | optimizing           | 0.000038 | 
  12.  | statistics           | 0.000123 | 
  13.  | preparing            | 0.000043 | 
  14.  | Sorting result       | 0.000025 | 
  15.  | executing            | 0.000023 | 
  16.  | Sending data         | 0.000045 | 
  17.  | Creating sort index  | 0.941434 | 
  18.  | end                  | 0.000077 | 
  19.  | query end            | 0.000044 | 
  20.  | closing tables       | 0.000038 | 
  21.  | freeing items        | 0.000056 | 
  22.  | cleaning up          | 0.000040 | 

2、如果是采用新索引(idx_tid_dateline):

  1. mysql> SELECT * FROM pre_forum_post use index(idx_tid_dateline) WHERE 
  2.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY dateline LIMIT 129860,15; 
  3.  
  4. #对比查看profiling结果 
  5.  | starting             | 0.000151 | 
  6.  | checking permissions | 0.000033 | 
  7.  | Opening tables       | 0.000040 | 
  8.  | init                 | 0.000105 | 
  9.  | System lock          | 0.000044 | 
  10.  | optimizing           | 0.000038 | 
  11.  | statistics           | 0.000188 | 
  12.  | preparing            | 0.000044 | 
  13.  | Sorting result       | 0.000024 | 
  14.  | executing            | 0.000023 | 
  15.  | Sending data         | 0.917035 | 
  16.  | end                  | 0.000074 | 
  17.  | query end            | 0.000030 | 
  18.  | closing tables       | 0.000036 | 
  19.  | freeing items        | 0.000049 | 
  20.  | cleaning up          | 0.000032 | 

可以看到,效率有了一定提高,不过不是很明显,因为确实需要扫描的数据量更大,所以 Sending data 阶段耗时更多,然后可以将这个SQL改写成下面这样:

  1. mysql> EXPLAIN SELECT * FROM pre_forum_post t1 INNER JOIN ( 
  2.  SELECT id FROM pre_forum_post use index(idx_tid_dateline) WHERE 
  3.  tid=8201301 AND `invisible` IN('0','-2') ORDER BY 
  4.  dateline  LIMIT 129860,15) t2 
  5.  USING (id)\G 
  6.  *************************** 1. row *************************** 
  7.  id: 1 
  8.  select_type: PRIMARY 
  9.  table:  
  10.  type: ALL 
  11.  possible_keys: NULL 
  12.  key: NULL 
  13.  key_len: NULL 
  14.  ref: NULL 
  15.  rows: 129875 
  16.  Extra: NULL 
  17.  *************************** 2. row *************************** 
  18.  id: 1 
  19.  select_type: PRIMARY 
  20.  table: t1 
  21.  type: eq_ref 
  22.  possible_keys: PRIMARY 
  23.  key: PRIMARY 
  24.  key_len: 4 
  25.  ref: t2.id 
  26.  rows: 1 
  27.  Extra: NULL 
  28.  *************************** 3. row *************************** 
  29.  id: 2 
  30.  select_type: DERIVED 
  31.  table: pre_forum_post 
  32.  type: ref 
  33.  possible_keys: idx_tid_dateline 
  34.  key: idx_tid_dateline 
  35.  key_len: 3 
  36.  ref: const 
  37.  rows: 703892 
  38.  Extra: Using where 

再看下这个SQL的 profiling 统计信息:

  1. | starting             | 0.000209 | 
  2. | checking permissions | 0.000026 | 
  3. | checking permissions | 0.000026 | 
  4. | Opening tables       | 0.000101 | 
  5. | init                 | 0.000062 | 
  6. | System lock          | 0.000049 | 
  7. | optimizing           | 0.000025 | 
  8. | optimizing           | 0.000037 | 
  9. | statistics           | 0.000106 | 
  10. | preparing            | 0.000059 | 
  11. | Sorting result       | 0.000039 | 
  12. | statistics           | 0.000048 | 
  13. | preparing            | 0.000032 | 
  14. | executing            | 0.000036 | 
  15. | Sending data         | 0.000045 | 
  16. | executing            | 0.000023 | 
  17. | Sending data         | 0.225356 | 
  18. | end                  | 0.000067 | 
  19. | query end            | 0.000028 | 
  20. | closing tables       | 0.000023 | 
  21. | removing tmp table   | 0.000029 | 
  22. | closing tables       | 0.000044 | 
  23. | freeing items        | 0.000048 | 
  24. | cleaning up          | 0.000037 | 

可以看到,效率提升了1倍以上,还是挺不错的,最后说明下,这个问题只会在热帖翻页时才会出现,一般只有1,2页回复的帖子如果还采用原来的执行计划,也没什么问题.

因此,建议discuz!官方修改或增加下新索引,并且在代码中判断是否热帖翻页,是的话,就强制使用新的索引,以避免性能问题.

Tags: discuz热帖翻页 mysql优化

分享到：

推荐文章

热门文章

最新评论文章