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

This article shows you how to optimize MySQL performance index optimization, concise and easy to understand, absolutely can make you shine, through the detailed introduction of this article I hope you can gain something.

We all know that indexes play a very critical role in the performance of data access, and we all know that indexes can improve the efficiency of data access.

Why does indexing improve data access performance? Will he have "side effects"? Is it true that the more indexes you create, the better the performance? How to design the index to maximize its effectiveness?

Here is mainly to do a brief analysis with the above questions, and at the same time exclude the particularity brought by the business scenario, please do not entangle the impact of the business scenario.

Why does indexing improve data access performance?

Many people only know that indexes can improve database performance, but they don't particularly understand how they work. In fact, we can use an example from life to understand them.

We asked a friend who didn't know much about computers to go to the library to confirm whether a book called MySQL Performance Tuning and Architecture Design was in storage, and said to him,"Please lend me a computer database book, which belongs to the MySQL database category, called MySQL Performance Tuning and Architecture Design." Depending on the category, the friend will go to the bookshelf where the "computer" books are stored, then look for the "database" category, find a bunch of books about "MySQL," and finally find that the target is hidden (or may not be on the bookshelf).

In this process: "computer"->"database"->"MySQL"->"in storage"->"MySQL performance tuning and architecture design" is actually a typical case of "searching data according to index","computer"->"database"->"MySQL"->"in storage" is the index for friends to find books.

Assuming there is no index, what would happen to the process of finding the book? Friends can only "traverse" one bookshelf at the library entrance until they find MySQL Performance Tuning and Architecture Design. If you're lucky, you might find it on the first shelf. But unfortunately, we may have to search all the shelves in the library to find the book we want.

Note: The "index" in this example is recorded in the friend's brain. In fact, every library has a very complete physical index system (mostly prominently located in the entrance), consisting of many small drawers with obvious labels. This indexing system stores this very detailed index data, identifying that the "target" we need to find is on a bookshelf in a certain area. Moreover, whenever new books are put into storage, old books are destroyed and clerical information is modified, the index system needs to be modified in time.

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

What are the "side effects" of indexing?

Changes (additions, deletions, changes) to books require revision of the index, which has additional maintenance costs

Searching through index systems takes time, and indexes have additional access costs

This indexing system requires a place to store, and there is an additional space cost for indexing

Is more indexing better?

If our library is just a transit point, where new books come in and are quickly forwarded to other libraries and "purged" from the collection, then our index will only be constantly modified and rarely used to find books.

Therefore, for such data with very large updates, the maintenance cost of the index will be very high. If the retrieval requirements are small and the retrieval efficiency is not very high, we do not recommend creating an index, or minimize the index.

If we have only a few books or a single bookshelf, indexing does little good and may even waste some of the time spent searching for it.

Therefore, it is not suitable to use indexes for data that is too small to be retrieved through indexes as fast as direct traversal.

If our library was only 10 square feet, and the shelves were already crowded, and the collection was still growing, would we even consider creating an index?

So, when we are running out of space to store the underlying data, we should also minimize inefficiencies or remove indexes.

How should indexes be designed to be efficient?

What if we just told each other,"Help me check if a database category book on MySQL called MySQL Performance Tuning and Architecture Design is in storage?" Friends can only find a large category area to find the "database" category, and then find the "MySQL" category, and then see if we need to hide. Since we miss one "computer class," friends have to look in every big class.

Therefore, we should try to make the search conditions as much as possible in the index, as far as possible through the index to complete all the filtering, back to the table just to take out the extra data fields.

If we say something like,"Help me identify a computer book on MySQL's database category called MySQL Performance Tuning and Architecture Design, see if it's in storage." If this friend doesn't know that computers are a big class and that databases belong to a big class of computers, then this friend is tragic. First he has to go through each category to see which ones "MySQL" exists in, then look at books containing "MySQL" to see which ones are in the "database" category (possibly partly PHP or other development languages), and then exclude non-computer categories (although perhaps not necessarily) before he can confirm.

Therefore, the order of the fields is crucial to the efficiency of the composite index, and the better the filtering effect, the higher the filtering effect.

What if we had a request (although it is almost impossible):"Help me borrow all the computer books in the library." If friends through the index to find, each time to the index cabinet to find the computer books in the area, and then move down from the bookshelf (assuming that only one grid can be removed from the bookshelf as a unit, analog database to block/page as a unit to read), take out the first book, and then from the index cabinet to find the computer books in the area, and then move down a grid, take out a book…and so on until all the books are taken. What if he doesn't search through the index? He needed to search from one bookshelf to the next. When he found the computer book, he moved down one shelf and took out all the computer books. Then, he went back until he had read all the bookshelves. In this process, if there are many computer books, the time spent by indexing is likely to be greater than that of direct traversal, because the time spent by constantly flipping through the index will be very long. (Further reading: here's a previously written article on Oracle, Index Scan Or Full Table Scan)

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

What if our friends didn't know that the category "database" could belong to the general category "computer," or that there was no correlation between the two categories in the library indexing system? In other words, my friend gets two separate indexes, one that tells me where the big class "Computer" is located, and one that tells me where the small class "Database" is located (probably multiple), so he can only choose one of them to search for my needs. Even if friends can search through two indexes separately and then find the intersection in their heads, such efficiency will be relatively inefficient in practice.

Therefore, in the actual use process, a data access can generally only use 1 index, this point must be noted in the index creation process, not to say that each condition in a SQL statement has an index corresponding to it.

The above content is how to perform index optimization in MySQL performance optimization. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

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.