Rails分页性能优化(MySQL)
1. Background
通过Newrelic监控到某个子应用SQL平均执行时间达到了300ms,痛点如下:
1
|
|
已经分别为tableid和userid建立索引。
EXPLAIN EXTRA结果:
1
|
|
2. Solutions
建立联合索引
1
|
|
1 2 3 4 5 |
|
1
|
|
执行后,开发环境查询时间由40ms提高到8ms。
EXPLAIN EXTRA
1
|
|
will_paginate
对于总量特别大的,可以为willpaginate设置totalentries,以避免count操作。
1
|
|
3. Troubleshooting
deploy至production. 测试了一下,查询时间依然没有优化。
EXPLAIN一下
本地EXTRA: Using index
production EXTRA: Using where; Using index
本地mysql版本为5.6.13, production的版本为5.5.34,于是将production的mysql-server升级至5.6。问题解决!
4. Summary
- 建立合适的index。
- 将mysql-server升级至5.6
- 通过where减少scan的范围。
if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter, it also can’t be solved by simple singe counter for Innodb tables as different transactions may see different number of rows in the table.
If you have query like SELECT COUNT(*) FROM IMAGE WHERE USER_ID=5 this query will be executed same way both for MyISAM and Innodb tables by performing index rage scan. This can be faster or slower both for MyISAM and Innodb depending on various conditions.
So remember Innodb is not slow for ALL COUNT() queries but only for very specific case of COUNT() query without WHERE clause.
References
http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/