Why does mysql need indexing?

Because the index can quickly increase the query speed; if the index is not used, mysql must start from the first record and then read the entire table until the relevant rows are found;
The larger the table, the more time it takes, but it also Not entirely.

Recommended course: MySQL tutorial

Index is a data structure;

So , In addition to data, the database system also maintains data structures that satisfy specific search algorithms. These data structures reference (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is an index.

Index data structure analysis

What is the structure of this index? In other words, why can this structure improve retrieval speed?

1. If there is no index, when searching for a certain record (for example, searching for name='wish'), you need to search all records, because there is no guarantee that there is only one wish, and you must search them all.

2. If an index is created on name, mysql will perform a search on the entire table, search the name value of each record in ascending order, then build the index entry (name and row_id), store it in the index segment, and query when name is wish You can directly search for the corresponding place.

3. Creating an index does not necessarily mean that it will be used. After MySQL automatically counts the information of the table, it decides whether to use the index. When there is very little data in the table, the speed of full table scan is already high. Soon, there is no need to use indexes.

Example to illustrate the working mechanism of the index

There are two fields in table A

id,name

There are now 10 million pieces of data in the table

Requirement: Query the corresponding id based on name

If there is no index, then you have to query all the records in the table, and you have to put 10 million records The data has to be checked one by one. It’s up to you whether it’s slow or not.

Now create an index based on name,

index table structure:

id,name,value

where value is the id of table A, stored in a json array (because there will be multiple names The same situation exists);

Then the name can be sorted according to the sorting rules. According to the algorithm, the position of the name in the index table can be directly located, and then the record with the id in table A can be taken out.

In short, by creating an index, you can directly access the records in table A.

Of course it is fast. If you want to query table A, you need to query 10 million pieces of data. By establishing an index, the algorithm greatly reduces the query volume.

The above is the detailed content of Why does mysql need indexing?. For more information, please follow other related articles on the PHP Chinese website!