Rails优化mysql索引

DesignImage.from.available.audited_with_colors

 

# ALWAYS used

 

 

 

tar = DesignImage.from.available.audited_with_colors.first

 

> DesignImage Load (9.3ms) SELECT `design_images`.* FROM `design_images` WHERE (design_images.created_at > ('2013-3-1')) AND (design_images.imageable_id is not null and design_images.imageable_type is not null and design_images.imageable_type <> 'Inspiration' and design_images.user_id is not null) AND (edited_color = 1 and audited = 1) LIMIT 1

 

 

 

======================

 

 

 

考虑建立联合索引

 

 

 

检查design_images.imageable_type <> 'Inspiration'的稀疏程度

 

#ruby

 

has = {all:0, on:0, off:0}

 

has[:all] = DesignImage.where('design_images.created_at > "2013-03-01"').count

 

DesignImage.where('design_images.created_at > "2013-03-01"').find_each{|e| has[:off] = has[:off] + 1 if e.imageable_type != 'Inspiration' }

 

has[:on] = has[:all] - has[:off]

 

has

 

=> {:all=>132099, :on=>245, :off=>131854}

 

结论：

 

绝大部分都是imageable_type <> 'Inspiration'

 

 

 

建立联合索引 edited_color，audited，created_at

 

 

 

======================

 

 

 

mysql> show index from design_images;

 

+---------------+---------------------------------------+--------------+-----------------+-----------+-------------+------+------------+

 

| Table         | Key_name                              | Seq_in_index | Column_name     | Collation | Cardinality | Null | Index_type |

 

+---------------+---------------------------------------+--------------+-----------------+-----------+-------------+------+------------+

 

| design_images | PRIMARY                               |            1 | id              | A         |      176810 |      | BTREE      |

 

| design_images | NewIndex1                             |            1 | user_id         | A         |       19645 | YES  | BTREE      |

 

| design_images | NewIndex4                             |            1 | is_cover        | A         |      176810 | YES  | BTREE      |

 

| design_images | NewIndex5                             |            1 | created_at      | A         |      176810 |      | BTREE      |

 

| design_images | index_design_images_on_file_file_size |            1 | file_file_size  | A         |      176810 | YES  | BTREE      |

 

| design_images | index_design_images_on_imageable_id   |            1 | imageable_id    | A         |       35362 | YES  | BTREE      |

 

| design_images | index_design_images_on_is_cover       |            1 | is_cover        | A         |      176810 | YES  | BTREE      |

 

| design_images | sorts                                 |            1 | sorts           | A         |           6 | YES  | BTREE      |

 

| design_images | audited                               |            1 | audited         | A         |           2 | YES  | BTREE      |

 

| design_images | edited_color                          |            1 | edited_color    | A         |           2 | YES  | BTREE      |

 

| design_images | area_id                               |            1 | area_id         | A         |       58936 | YES  | BTREE      |

 

| design_images | index_design_images_on_timestamp      |            1 | created_at      | A         |      176810 |      | BTREE      |

 

| design_images | index_design_images_on_timestamp      |            2 | file_file_name  | A         |      176810 | YES  | BTREE      |

 

| design_images | index_design_images_on_timestamp      |            3 | file_updated_at | A         |      176810 | YES  | BTREE      |

 

| design_images | index_design_images_on_imageable_type |            1 | imageable_type  | A         |        2996 | YES  | BTREE      |

 

| design_images | index_design_images_on_title          |            1 | title           | A         |       44202 | YES  | BTREE      |

 

| design_images | count_index                           |            1 | created_at      | A         |      176810 |      | BTREE      |

 

| design_images | count_index                           |            2 | imageable_id    | A         |      176810 | YES  | BTREE      |

 

| design_images | count_index                           |            3 | imageable_type  | A         |      176810 | YES  | BTREE      |

 

| design_images | count_index                           |            4 | user_id         | A         |      176810 | YES  | BTREE      |

 

| design_images | count_index                           |            5 | edited_color    | A         |      176810 | YES  | BTREE      |

 

| design_images | count_index                           |            6 | audited         | A         |      176810 | YES  | BTREE      |

 

+---------------+---------------------------------------+--------------+-----------------+-----------+-------------+------+------------+

 

 

 

22 rows in set (0.00 sec)

 

 

 

add 1 => [audited, edited_color, created_at]

 

del 4 => count_index, audited, edited_color, index_design_images_on_timestamp

 

add 1 => updated_at

 

del 4 => index_design_images_on_file_file_size, index_design_images_on_is_cover, NewIndex4, sorts

 

add 1 => no_audited

 

db/migrate/20140716104927_remove_index_of_design_images.rb

class RemoveIndexOfDesignImages < ActiveRecord::Migration

# >show index from design_images;
#
# add 1 => [audited, edited_color, created_at]
# del 4 => count_index, audited, edited_color, index_design_images_on_timestamp
# add 1 => updated_at
# del 4 => index_design_images_on_file_file_size, index_design_images_on_is_cover, NewIndex4, sorts
# add 1 => no_audited

  def up
    remove_index :design_images, name:'count_index'
    remove_index :design_images, name:'audited'
    remove_index :design_images, name:'edited_color'
    remove_index :design_images, name:'index_design_images_on_timestamp'
    remove_index :design_images, :file_file_size
    remove_index :design_images, :is_cover
    remove_index :design_images, name:'NewIndex4'
    remove_index :design_images, name:'sorts'
    add_index :design_images, [:audited,:edited_color,:created_at], name:'from_available_audited_with_colors'
    add_index :design_images, :updated_at
    add_index :design_images, :no_audited
  end

  def down
    remove_index :design_images, name:'from_available_audited_with_colors'
    remove_index :design_images, :updated_at
    remove_index :design_images, :no_audited
    add_index :design_images, [:created_at,:imageable_id,:imageable_type,:user_id,:edited_color,:audited], name:'count_index'
    add_index :design_images, :audited, name:'audited'
    add_index :design_images, :edited_color, name:'edited_color'
    add_index :design_images, [:created_at,:file_file_name,:file_updated_at], name:'index_design_images_on_timestamp'
    add_index :design_images, :file_file_size
    add_index :design_images, :is_cover
    add_index :design_images, :is_cover, name:'NewIndex4'
    add_index :design_images, :sorts, name:'sorts'
  end
end

 

http://guides.rubyonrails.org/v3.2.15/migrations.html#using-the-change-method

针对有名字的索引进行删除  remove_index :design_images, name:'count_index'

 

remove_index方法没有在支持change的函数列表中，所以migration需要写up和down