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

This article mainly explains the "MySQL database index can improve data access performance", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL database index can improve data access performance" bar!

Why can indexes improve data access performance?

Many people only know that indexing can improve the performance of the database, but they don't know much about it. In fact, we can use a living example to understand it.

We asked a friend who doesn't know much about computers to go to the library to confirm whether a book called "MySQL performance tuning and Architecture Design" is hidden. We said to him, "Please borrow a computer database book for me, which belongs to the category of MySQL database. It's called MySQL performance tuning and Architecture Design." According to the category, friends will go to the bookshelf in the "computer" book area, then look for the location of the "database" category, find a bunch of books about "MySQL", and finally find that the target is hidden (or it may not be on the shelf).

In this process: "computer"-> "Database"-> "MySQL"-> "in Tibet"-> "MySQL performance tuning and Architecture Design" is actually a typical case of "finding data according to the index". "computer"-> "Database"-> "MySQL"-> "in Tibet" is the index for friends to find books.

Suppose that without this index, what would happen to the process of finding the book? Friends can only access the "traversal" of bookshelves from the library until they find the book "MySQL performance tuning and Architecture Design". If you are lucky, you may find it on the first bookshelf. But if it is unfortunate, it will be miserable. We may have to search all the shelves in the whole library to find the book we want.

Note: the "index" in this example is recorded in a friend's brain. In fact, every library has a very complete actual indexing system (mostly in a conspicuous place at the entrance). It is made up of many small drawers with obvious labels. This index system stores this very complete and detailed index data, indicating that the "target" we are looking for is on a bookshelf in a certain area. And whenever new books are stored, old books are destroyed and secretary information is modified, the index system needs to be revised in time.

Let's analyze the index and see what conclusions can be drawn through the above small example in life.

What are the "side effects" of the index?

The change (add, delete, change) of the book needs to revise the index, and the index has additional maintenance costs.

It takes time to find the flipping index system, and there are additional access costs to the index.

The index system needs a place to store, and there is an additional space cost for the index.

Is it better to have as many indexes?

If our library is just a transit station, and the new books in it will soon be forwarded to other libraries to "remove" from this collection, then our index will only be constantly revised, and will rarely be used to find books.

Therefore, for data like this, which has a very large amount of updates, the cost of maintaining the index will be very high. If there is little need for retrieval and there is no very high requirement for retrieval efficiency, we do not recommend creating an index. Or minimize the index.

If we have only a few books or only one shelf, the index will not help and may even waste some of the time it takes to find the index.

Therefore, for the data that the amount of data is so small that it is not as fast to search through the index as it is to be searched directly, it is not suitable to use the index.

If our library only has an area of 10 square meters, even the shelves are already very crowded, and the collection is still increasing, can we still consider creating an index?

Therefore, when we are short of space to store basic data, we should also try to reduce inefficiency or remove indexes.

How should the index be designed to be efficient?

What if we just told each other, "help me confirm whether a MySQL book about database categories called" MySQL performance tuning and Architectural Design "is hidden? Friends can only look for the "database" category in a large category area, and then find the "MySQL" category, and then see whether what we need is hidden. Since we say less about a "computer class", friends have to look for it in every big category.

Therefore, we should try to make the search conditions in the index as much as possible, complete all filtering through the index as much as possible, and return to the table just to fetch additional data fields.

If we put it this way: "help me identify a computer series about the database category of MySQL, called MySQL performance tuning and Architectural Design, and see if it's hidden." If this friend does not know that the computer is a large category, nor does the database belong to the computer category, then this friend will be tragic. First he has to go through each category to determine which categories "MySQL" exists in, then from the books that contain "MySQL" to see what is in the "database" category (possibly part of it is about PHP or other development languages), and then rule out non-computer categories (although it may not be necessary) before confirming.

Therefore, the order of fields plays an important role in the efficiency of combined indexing, and the better the filtering effect is, the higher the field needs to be.

If we still have such a need (though almost impossible): "help me borrow all the computer books in the library." If friends look through the index, every time they go to the index cabinet to find the area where the computer books are located, and then move a grid from the bookshelf (assuming that they can only be removed from the bookshelf in one unit, and read in block/page in the analogical database), take out the first book, and then find the area where the computer book is located from the index cabinet, move the next frame, and take out a book. Go back and forth until you finish picking up all the books. What if he doesn't look for it through the index? He needs to look back from a bookshelf on the ground. when he finds the books on the computer, move the next frame, take out all the books on the computer, and then read them all over the shelves. In this process, if there are more computer books, it is likely to take more time to retrieve through the index than to traverse directly, because the time taken by constantly flipping through the index will be unusually long. (extended reading: here is a previously written article about Oracle, index scan or full table scan (Index Scan Or Full Table Scan))

Therefore, when the amount of data we need to read accounts for a large proportion of the total data, or the filtering effect of the index is not very good, the use of the index is not necessarily better than the full table scan.

What if our friends don't know that the category of "database" can belong to the category of "computer", or that the attributes of these two categories are not related in the library's index system? In other words, the friend gets two separate indexes, one is to tell the area of the large category of "computer" and the other is the area of the subcategory of "database" (probably multiple areas). Then he can only choose one of the two to search for my needs. Even if a friend can search through two indexes and then find the intersection in the brain, the efficiency will be relatively low in the actual process.

Therefore, in the process of practical use, one data access can only use one index, which must be noted in the process of index creation. It is not enough to say that every condition in the Where clause of a SQL statement can be matched by an index.

Thank you for reading, the above is the content of "why MySQL database index can improve data access performance". After the study of this article, I believe you have a deeper understanding of why MySQL database index can improve data access performance. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.