What is the principle of mysql index?

This article mainly introduces the principle of mysql index to everyone. I hope it will be helpful to friends in need! (Related recommendation: "mysql tutorial")

The purpose of the index

The purpose of the index is to improve Query efficiency can be compared to a dictionary. If we want to look up the word "mysql", we definitely need to locate the letter m, then find the letter y from bottom to bottom, and then find the remaining sql. If there is no index, then you may need to look through all the words to find what you want. What if I want to find words starting with m? Or what about words starting with ze? Do you feel that without an index, this matter cannot be completed at all?

mysql index principle

In addition to dictionaries, examples of indexes can be seen everywhere in life, such as train schedules at train stations, book catalogs, etc. Their principles are the same. By constantly narrowing the scope of the data you want to obtain, you can filter out the final desired results, and at the same time, turn random events into sequential events, that is, we always use the same search method to lock data.

The same is true for the database, but it is obviously much more complicated, because it not only faces equivalent queries, but also range queries (>, <, between, in), fuzzy queries (like), and union queries ( or) and so on. How should the database choose to deal with all problems?

Let’s think back to the dictionary example. Can we divide the data into segments and then query them in segments? The simplest way is if there are 1,000 pieces of data, 1 to 100 are divided into the first section, 101 to 200 are divided into the second section, 201 to 300 are divided into the third section... In this way, to check the 250th piece of data, you only need to find the third section. The sub-removal of 90% of invalid data.

But if it is a record of 10 million, how many segments should it be divided into? Students with a little bit of algorithm foundation will think of search trees, whose average complexity is lgN and has good query performance. But here we have overlooked a key issue. The complexity model is based on the same operation cost every time. The database implementation is relatively complex, and the data is saved on the disk. In order to improve performance, part of the data can be read in every time. Calculate using memory, because we know that the cost of accessing disk is about 100,000 times that of accessing memory, so a simple search tree cannot meet complex application scenarios.

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