MySQL MySQL中INDEX和VIEW有什么区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24197856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is difference between INDEX and VIEW in MySQL
提问by Ashutosh SIngh
Which one is fast either Index or View both are used for optimization purpose both are implement on table's column so any one explain which one is more faster and what is difference between both of them and which scenario we use view and index.
索引或视图哪个快，都用于优化目的，两者都在表的列上实现，因此任何人都解释了哪个更快，它们之间有什么区别以及我们使用视图和索引的场景。
回答by Nishanthi Grashia
VIEW
看法
- View is a logical table. It is a physical object which stores data logically. View just refers to data that is tored in base tables.
- A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.
- 视图是一个逻辑表。它是逻辑上存储数据的物理对象。视图只是指在基表中被撕裂的数据。
- 视图是一个逻辑实体。它是存储在系统表空间中的数据库中的 SQL 语句。视图的数据构建在数据库引擎在 TEMP 表空间中创建的表中。
INDEX
指数
- Indexes are pointres that maps to the physical address of data. So by using indexes data manipulation becomes faster.
- An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.
- 索引是映射到数据物理地址的指针。所以通过使用索引数据操作变得更快。
- 索引是一种允许更快检索记录的性能调整方法。索引为索引列中出现的每个值创建一个条目。
ANALOGY:
类比：
Suppose in a shop, assume you have multiple racks. Categorizing each rack based on the items saved is like creating an index. So, you would know where exactly to look for to find a particular item. This is indexing.
假设在一家商店，假设您有多个货架。根据保存的项目对每个机架进行分类就像创建索引。因此，您将知道确切地寻找特定项目的位置。这是索引。
In the same shop, you want to know multiple data, say, the Products, inventory, Sales data and stuff as a consolidated report, then it can be compared to a view.
在同一家商店中，您想了解多个数据，例如，产品、库存、销售数据等作为综合报告的内容，然后可以将其与视图进行比较。
Hope this analogy explains when you have to use a view and when you have to use an index!
希望这个比喻能解释什么时候必须使用视图，什么时候必须使用索引！
回答by Ankit Bajpai
Both are different things in the perspective of SQL.
从 SQL 的角度来看，两者都是不同的东西。
VIEWS
视图
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query. Views, which are kind of virtual tables, allow users to do the following:
视图只不过是存储在数据库中并具有关联名称的 SQL 语句。视图实际上是预定义 SQL 查询形式的表的组合。视图是一种虚拟表，允许用户执行以下操作：
- A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data such that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables which can be used to generate reports.
- 视图可以包含表的所有行或从表中选择行。可以从一个或多个表创建视图，这取决于编写的 SQL 查询来创建视图。
- 以用户或用户类别认为自然或直观的方式构建数据。
- 限制对数据的访问，以便用户可以查看和（有时）修改他们需要的内容，而不是更多。
- 汇总可用于生成报告的各种表格中的数据。
INDEXES
索引
While Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
索引是数据库搜索引擎可以用来加速数据检索的特殊查找表。简单地说，索引是指向表中数据的指针。数据库中的索引与书后的索引非常相似。
For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.
例如，如果您想参考一本书中讨论某个主题的所有页面，您首先参考索引，该索引按字母顺序列出所有主题，然后参考一个或多个特定页码。
An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.
索引有助于加快 SELECT 查询和 WHERE 子句的速度，但它会减慢数据输入，使用 UPDATE 和 INSERT 语句。可以在不影响数据的情况下创建或删除索引。
回答by Mahesh Patil
view:
看法：
1) view is also a one of the database object. view contains logical data of a base table.where base table has actual data(physical data).another way we can say view is like a window through which data from table can be viewed or changed.
1) 视图也是数据库对象之一。视图包含基表的逻辑数据。其中基表具有实际数据（物理数据）。另一种方式我们可以说视图就像一个窗口，通过它可以查看或更改表中的数据。
2) It is just simply a stored SQL statement with an object name. It can be used in any SELECT statement like a table.
2) 它只是一个简单的带有对象名称的存储 SQL 语句。它可以在任何 SELECT 语句中使用，如表。
index:
指数：
1) indexes will be created on columns.by using indexes the fetching of rows will be done quickly.
1) 将在列上创建索引。通过使用索引可以快速完成行的获取。
2) It is a way of cataloging the table-info based on 1 or more columns. One table may contain one/more indexes. Indexes are like a 2-D structure having ROWID & indexed-column (ordered). When a table-data is retrieved based on this column (col. which are used in WHERE clause), this index gets into the picture automatically and it's pointer search the required ROWIDs. These ROWIDs are now matched with actual table's ROWID and the records from table are shown.
2) 这是一种基于 1 个或多个列对 table-info 进行编目的方法。一张表可能包含一个/多个索引。索引就像一个具有 ROWID 和索引列（有序）的二维结构。当基于该列（WHERE 子句中使用的列）检索表数据时，该索引会自动进入图片并通过指针搜索所需的 ROWID。这些 ROWID 现在与实际表的 ROWID 匹配，并显示表中的记录。
