mysql动态性能视图_使用MySQL视图提高性能

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.

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.