Rails分页性能优化(MySQL)

1. Background

通过Newrelic监控到某个子应用SQL平均执行时间达到了300ms，痛点如下：

1

SELECT COUNT(*) FROM `table_rows` WHERE `table_rows`.`table_id` = 1 AND `table_rows`.`user_id` = 98

已经分别为tableid和userid建立索引。

EXPLAIN EXTRA结果:

1

Using intersect(index_table_rows_on_user_id,index_table_rows_on_table_id); Using where

2. Solutions

建立联合索引

1

rails g migration add table_id_and_user_id_index_to_table_rows

1
2
3
4
5

class AddTableIdAndUserIdIndexToTableRows < ActiveRecord::Migration
  def change
    add_index :table_rows, [:table_id, :user_id]
  end
end

1

rake db:migrate

执行后，开发环境查询时间由40ms提高到8ms。

EXPLAIN EXTRA

1

Using index

will_paginate

对于总量特别大的，可以为willpaginate设置totalentries，以避免count操作。

1

scope.paginate(:page=>params[:page], :total_entries=>1000)

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

1. 建立合适的index。
2. 将mysql-server升级至5.6
3. 通过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/