关于count*的优化的问题建议

1.关于count*的优化的问题建议。（结合微博的架构进行分析）51Testing软件测试网fSa l\y/v

  通过实验得出几个性能问题：51Testing软件测试网9k[*`UQyq

 复合索引>主键索引>二级索引51Testing软件测试网tu1S3suD4q\Y

目前商务大量的应用了复合索引的方式得出分页的统计数。如果用了二级索引，能使查询速度提高十倍以上。51Testing软件测试网(tnyo%F9x S4b+Y

传统的方式51Testing软件测试网&YP/DH_!nC

SELECT * FROM db_booksir ORDER BY bsid DESC LIMIT 10000, 2051Testing软件测试网xqpb;Gl

***************** 1. row **************51Testing软件测试网/h {;bM0Ha4M^
id: 1
~P;e%QH{&V0select_type: SIMPLE
+X} xA"E3C M!w0table: db_booksir51Testing软件测试网'[dJc:?
type: index
Q[%R8x?z2TZMS0possible_keys: NULL51Testing软件测试网$B'K*aS"R5t?
key: PRIMARY51Testing软件测试网_%kA`p,iY
z)u#^7A
key_len: 451Testing软件测试网,M4QC(NJ/^)F
ref: NULL
r4e,[%n;na xG#t{0rows: 1002051Testing软件测试网3i}yg@L%i#q4?
Extra:51Testing软件测试网Ib9Z^|
1 row in set (0.00 sec)51Testing软件测试网_oXh T

改进后，可以采用的方式可用传递值id的方式来进行翻页。类似雅虎的分页方式或目前流行的sina微博的分页方式。51Testing软件测试网aV:CPa| q]3k]

改进后，例子如下：51Testing软件测试网.K`*e$Q*G0o6j?

SELECT * FROM db_booksir WHERE bsid >1020 ORDER BY id ASC LIMIT 20;//下一页

　　处理下一页的时候SQL语句可以是：

SELECT * FROM db_booksir WHERE bsid<1000 ORDER BY id DESC LIMIT 20;//上一页

不管翻多少页，每次查询只扫描20行。

51Testing软件测试网oGGio"[}

优点：去除了offset。提高翻页速度。并且避免了爬虫重复爬。导致服务器cpu过高。

缺点：只能提供上一页、下一页的链接形式51Testing软件测试网4q|tGs+F

 51Testing软件测试网lF.qZ3B

如果不可避免的按上一页1 2 3 4 5 6 7 8 9下一页”这样的链接方式，可以按前面的方式来扩展。51Testing软件测试网 ~9e5gV"oG0{["n

当是第10页的SQL如下：

SELECT * FROM db_booksir_info WHERE bsid>=2500 ORDER BY bsid ASC LIMIT 0,20

比如要跳到第9页，SQL语句可以这样写：51Testing软件测试网hM?"~6zHt8~\

SELECT * FROM db_booksir_info WHERE bsid <2500 ORDER BY bsid desc LIMIT 0,2051Testing软件测试网%P[6f"]i7kHF

。。。。。

 51Testing软件测试网~(N ?2a_j/y}o9|X

跳转到第11页：

SELECT * FROM db_booksir_info WHERE bsid >2519 ORDER BY bisd asc LIMIT 0,20

 51Testing软件测试网:nua5{"wl7Y gf

其他的原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数51Testing软件测试网5cr9L!C~%l_0G Q,G

总之，该性能问题，归根结底还是存储问题。

下一阶段，将重点对二级索引的方式进行分析和实践。51Testing软件测试网+m YPw.Lk*Nq

2.关于达到百G或T级别快速备份和商务网大表坏掉快速恢复的问题。51Testing软件测试网M J;DA,uu0O

  问题：通过对商务目前情况看，备份数据已经达到了几十G，备份起来已经有相当大。而且在大表挂掉情况下，修表需要很长一段时间。

解决方式：通过对一些逻辑卷的分析，可以通过linux层面解决次问题。通过快照每天凌晨执行计划进行海量数据的备份。能够在几分钟内进行快速备份。并且备份速度和恢复速度相当的快。能让网站快速恢复到快照前的数据库状态。

  缺点：恢复后快照后的数据仍然需要手动同步。51Testing软件测试网 ] y.c4sg Et7V

 51Testing软件测试网^V Ou*_r+H

3.关于用于生产环境的mysql5.6的使用情况。

 通过性能分析对比得出，5.6比5.1版本性能上得到很大的提高，并且增加了部分的功能。

但由于目前用于生产环境的比较少，难以控制BUG的情况。

 需要根据目前我们需要使用的实际情况，进一步深入分析，并扩展每个版本的情况。51Testing软件测试网XP-M%JU%_`P:g

之后会另外给出一份详细的报告。

4.关于排序错误的问题。51Testing软件测试网6PAts$X)`[#yz

 相应调整了sort_buffer_size和read_buffer_size,join_buffer_size.

 并分析得出，有一部分是由于查询被kill，顾产生该问题。51Testing软件测试网7c&E1PM`5L
x"CyZ