Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of database indexing, which is very detailed and has a certain reference value. Interested friends must finish reading it!

The index is used to quickly find the row with a specific value in a column. Without using the index, MySQL must read the entire table from the first record until the relevant row is found. The larger the table, the more time it takes to query the data. The steps for indexing are as follows:

1. First we open a data table to be operated on, as shown in the following figure, we need to add an index to the name field.

2. Then we can add the index through create index. As shown in the following figure, don't throw away the content behind on.

Then we expand the index folder under the table and we can see the index created.

4. Next, when we create the index, we can also add the sorting method after the field.

5. Then delete the index using the drop statement, as shown in the following figure, similar to deleting the table.

6. After the drop statement is finally executed, there is no index under the index folder.

Extended data

An index is a decentralized storage structure created to speed up the retrieval of data rows in a table. Indexes are built on tables and are made up of index pages other than data pages, and the rows in each index page contain logical pointers to speed up the retrieval of physical data. [2]

In the database diagram, you can create, edit, or delete each index type in the Index / key property page of the selected table. When you save the table to which the index is attached, or when you save the diagram where the table is located, the index is saved in the database.

In a relational database, an index is a separate, physical storage structure that sorts the values of one or more columns in a database table. it is a list of logical pointers to a collection of one or more column values in a table and the corresponding data pages that physically identify these values in the table. The function of the index is equivalent to the catalogue of the book, and the content you need can be found quickly according to the page number in the catalog.

The index provides pointers to the data values stored in the specified column of the table, and then sorts those pointers according to the sort order you specify. The database uses an index to find a specific value, and then follows the pointer to the row that contains the value. This allows faster execution of SQL statements corresponding to tables and quick access to specific information in database tables.

When there are a large number of records in the table, if you want to query the table, the first way to search for information is a full table search, which takes out all the records one by one, compares them with the query conditions, and then returns the records that meet the conditions. This will consume a lot of database system time and result in a large number of disk I / O operations. The second is to establish an index in the table, then find the index value that meets the query criteria in the index, and finally quickly find the corresponding record in the table through the ROWID (equivalent to the page number) stored in the index.

Advantages

1. Greatly accelerate the speed of data retrieval

two。 Create a uniqueness index to ensure the uniqueness of each row of data in the database table

3. Connection between accelerometer and meter

4. When you use grouping and sorting clauses for data retrieval, you can significantly reduce the time for grouping and sorting in a query.

Shortcoming

1. Indexes need to take up physical space.

two。 When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

The above is all the contents of the database indexing method, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.