Shulou(Shulou.com)05/31 Report--

This article is about what an index is in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In mysql, an index is a special database structure, which is composed of one or more columns in a data table, which can be used to quickly query records with a specific value in the data table. Through the index, when querying the data, you do not need to read all the information of the record, but only query the index column.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Index is a special database structure, which is composed of one or more columns in a data table, which can be used to quickly query records with a specific value in the data table.

Through the index, when querying data, you do not have to read all the information of the record, but only query the index column. Otherwise, the database system will read all the information from each record to match.

The index can be compared to the sequence table of Xinhua Dictionary. For example, to look up the word "library", if you don't use phonetic order, you need to look it up page by page from 400 pages of the dictionary. However, if you extract pinyin to form a sequence table, you only need to find it directly from the sequence table of more than 10 pages. This can save a lot of time.

Therefore, the use of index can greatly improve the query speed of the database, but also effectively improve the performance of the database system.

Why use an index?

An index is a table of corresponding relations between column values and record rows established according to a column or several columns in a table according to a certain order. it is essentially an ordered table that describes the one-to-one correspondence between the column values of the index column and the rows recorded in the original table.

Index is a very important database object in MySQL and the basis of database performance tuning technology. It is often used to realize fast data retrieval.

In MySQL, there are usually two ways to access row data of a database table:

1) sequential access

Sequential access is to perform a full table scan in the table, traversing row by row from beginning to end, until the qualified target data is found in the unordered row data.

Sequential access is relatively simple to implement, but it is very inefficient when there is a large amount of data in the table. For example, when looking for a small amount of data in tens of millions of pieces of data, using sequential access will traverse all the data and spend a lot of time, which will obviously affect the processing performance of the database.

2) Index access

Index access is a way to directly access the rows of records in a table by traversing the index.

The premise of using this method is to establish an index on the table, and after the index is created on the column, the location of the corresponding record row can be found directly according to the index on the column, so that the data can be found quickly. The index stores pointers to the specified column data values, which are sorted according to the specified sort order.

For example, in the student basic information table tb_students, if an index is established based on student_id, the system creates a mapping table from the index column to the actual record. When a user needs to find data with a student_id of 12022, the system first finds the record on the student_id index, then finds the data row directly through the mapping table, and returns that data. Because the speed of scanning the index is generally much faster than the speed of scanning the actual data rows, using the index method can greatly improve the efficiency of the database.

In short, without an index, MySQL must read the entire table from the first record until it finds the relevant row. The larger the table, the more time it takes to query the data. If the query column in the table has an index, MySQL can quickly get to a location to search for data files without having to view all the data, which will save a lot of time.

Advantages and disadvantages of index

Index has its obvious advantages and inevitable disadvantages.

Advantages

The advantages of the index are as follows:

The uniqueness of each row of data in the database table can be guaranteed by creating a unique index.

Indexes can be set on all MySQL column types.

It can greatly speed up the query speed of data, which is the main reason for using indexes.

The connection between tables can be accelerated in terms of achieving the referential integrity of data.

The time of grouping and sorting in a query can also be significantly reduced when using grouping and sorting clauses for data queries.

Shortcoming

Adding an index also has many disadvantages, mainly as follows:

It takes time to create and maintain index groups, and the time spent increases as the amount of data increases.

Indexes need to take up disk space, in addition to data tables occupy data space, each index also takes up a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.

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.

When using an index, you need to consider the advantages and disadvantages of the index.

Thank you for reading! This is the end of this article on "what is an index in mysql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.