rails mysql优化_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