使用MySQL视图提高性能 [英] Using MySQL views to increase performance

问题描述

让我们假设我有一个products表，其中包含deleted字段.当删除产品时，我只是将deleted设置为1，而不是删除实际记录.这样，我将获得日常使用中不需要的大量数据.创建仅显示未删除产品的视图并查询该视图而不是products表会提高我的应用程序的性能吗?

Let's assume I have a products table with deleted field. when a product is deleted, instead of deleting the actual record, I simply set deleted to 1. By doing this, I will have loads of data that I will not need in day to day use. Will creating a view showing only not deleted products and query this view instead of products table increase the performance of my app?

推荐答案

使用视图不会显着提高性能.
但是，由于视图的语法是固定的，因此它将在第一次选择后缓存查询结果.

Using a view does not dramatically increase performance.
However because the syntax of the view is fixed it will cache the result of your query after the first select.

但是查询的结果不再索引了，因此，如果视图选择了很多行而您确实选择了视图，则实际上可能会使速度变慢.

However the result of the query is not indexed anymore, so if the view selects many rows and you do selects against the view you might actually slow things down.

替代
另一种选择是将已删除的产品行移到单独的deleted_products表中.
或者，您可以将表划分为deleted和undeleted部分.

Alternative
An alternative might be to move deleted product rows to a separate deleted_products table.
Or you can partition the table into a deleted and undeleted part.

链接:
http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

这篇关于使用MySQL视图提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！