What is the use of mysql index?

In mysql, indexes can be used to quickly query records with a specific value in the data table, greatly speeding up data query; after creating an index on a column, you can directly search for data based on the column. The index on the file finds the location of the corresponding record row, thereby quickly finding the data. If the queried column in the table has an index, MySQL can quickly reach a location to search the data file without having to view all the data, which will save a lot of time; and by creating a unique index, each row of data in the database table can be guaranteed uniqueness.

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 the data table. It can be used to quickly query records with a specific value in the data table.

Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

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

Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

Why use indexes

The index is the relationship between the column values ​​and the record rows established in a certain order based on one or several columns in the table. The correspondence table is essentially an ordered table describing the one-to-one correspondence between the column values ​​of the index columns and the record rows in the original table.

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

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

1) Sequential access

Sequential access is in the table Perform a full table scan, traversing row by row from beginning to end until you find target data that meets the conditions in the unordered row data.

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

2) Index access

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

The premise of using this method is to create an index on the table. After creating the index on the column, when searching for data, you can directly find the location of the corresponding record row based on the index on the column, so as to quickly find the data. The index stores pointers to the data values ​​of the specified columns, sorting these pointers 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 will create a mapping table from the index column to the actual record. When the user needs to find the data with student_id 12022, the system first finds the record on the student_id index, then directly finds the data row through the mapping table, and returns the row of data. Because the speed of scanning indexes is generally much greater than the speed of scanning actual data rows, using indexes can greatly improve the efficiency of the database.

In short, without using an index, MySQL must read the entire table starting from the first record until the relevant rows are found. The larger the table, the more time it takes to query the data. If the queried column in the table has an index, MySQL can quickly get to a location to search the data file without having to look at all the data, which will save a lot of time.

The advantages of indexes are as follows:

• By creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

• You can set indexes for all MySQL column types.

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

• It can speed up the connection between tables in terms of achieving referential integrity of data.

• When using grouping and sorting clauses for data query, the time of grouping and sorting in the query can also be significantly reduced

Note : Indexes can improve query speed, but will affect the speed of inserting records. Because when inserting records into an indexed table, the database system will sort according to the index, which reduces the speed of inserting records. The speed impact will be more obvious when inserting a large number of records. In this case, the best way is to delete the index in the table first, then insert the data, and then create the index after the insertion is completed.

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the use of mysql index?. For more information, please follow other related articles on the PHP Chinese website!