Index of the database

Today think of the database optimization, the first to think of the index, so want to re-understand the index. First Baidu Encyclopedia, the definition or the first look!

Definition: An index is a separate, physical data structure that is a collection of one or more column values in a table and a logical pointer list corresponding to the data pages that physically identify those values in the table.

I'll go!!! This definition who under, read a mouthful not to say, still the dead understand, right? Let's see what it can do!

Use an index to quickly access specific information in a database table. An index is a structure that sorts a column or columns of values in a database table, such as the Name column in the Employee table, and if you want to find a specific employee by last name, the index will help you get that information faster than all the rows in the table that must be searched; Databases use indexes in much the same way that you use indexes in books. That's easy to understand, isn't it? Another example of such a query: SELECT * FROM table1 where id=10000. If there is no index, you must traverse the entire table until the row with the ID equals 10000 is found, and after the index (which must be an index established on the ID column), you can find it in the index. Because the index value is optimized by some algorithm, the number of lookups is much less, and the visible index is used for locating;

There are three types of database indexes: Unique index, primary key index, and clustered index.

Thus, the database uses indexes to provide the following benefits:

1, greatly accelerate the retrieval speed of data,

2. Create a unique index (a unique index is an index that does not allow any two rows to have the same index value.) ) to ensure uniqueness of each row of data in the database table; (individuals think that using a unique index does not fit the actual)

3, speed up the connection between table and table;

4. When using grouping and sorting clauses for data retrieval, you can significantly reduce the time for grouping and sorting in queries.

Any advantage must have the disadvantage, you KNOW that!

Disadvantages of using indexes:

1, the index needs to occupy the physical space.

2. When the data in the most table is added, deleted and modified, the index page should be maintained dynamically, reducing the maintenance speed of the data;

The above is a general introduction to database indexing.

Here's a look at the clustered index (a table can contain only one clustered index): A clustered index is one of the indexes, and the logical order of the key values in the index determines the physical order of the corresponding rows in the table; The clustered index is particularly effective for columns that are frequently searched for range values.

The application is as follows:

1. Columns containing a large number of distinct values

2. For between,>,>=,<,<= returns a column with a range value

3. Columns that are continuously accessed

4. Queries that return large result sets

5. Columns that are frequently accessed by queries using connections or GROUP BY clauses

The clustered index can improve the speed of multi-row retrieval, and the second non-clustered index is fast for single-line retrieval;

Oh!!!

The purpose of indexing is to speed up the lookup or sorting of records in a table. However, there is a cost to indexing the table: one is to increase the storage space of the database, but rather to spend more time inserting and modifying the data (as the index changes as well). In plain view, database indexing is a directory of values in some fields to improve the search efficiency of a table;

The pros and cons are summarized as follows:

Creating an index can greatly improve the performance of your system. First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table. Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes. Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data. Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries. By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

Because there are many disadvantages to increasing the index. First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases. Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger. Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

Most of the above summary of knowledge from the Baidu Encyclopedia, I just according to their own thoughts to tidy up a bit!

Are some theoretical knowledge, concrete practice or to explore their own learning!